java 连接 pi 数据库驱动

转载

mob64ca14154457 2024-08-27 10:04:27

文章标签 java 连接 pi 数据库驱动 oracle bc System 文章分类 Java 后端开发

一、JDBC编程步骤

所有的JDBC都要遵循如下6个步骤：

1.Load the Driver 加载驱动
①.Class.forName("类全名") | Class.forName().newInstance() | new DriverName()
②.实例化时自动向DriverManager注册，不需显式调用DriverManager.registerDriver方法
2.Connect  to the DataBase连接数据库
①.DriverManager.getConnection()
3.Execute the SQL执行SQL语句
①.Connection.CreateStatement() 
②.Statement.executeQuery() 执行查询
③.Statement.executeUpdate() 执行更新
4.Retrieve the result data 取得查询结果
①.循环取得结果while(rs.next())
5.Show the result data 展示结果数据
①.将数据库中的各种类型转换成JAVA中的类型(getXxx())方法
6.Close 关闭连接资源
①.close the result./close the statement/close the connection
二、程序示例：所有示例是一个递进学习过程。
示例一：连接数据库，查询用户下的dept表信息
public class TestJDBC {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");

//4.循环取得结果集
while(rs.next()){

//5.显示数据，把oracle中的数据类型转化成java中的类型(getXxx()方法)
//rs.getString("deptno")
System.out.println(rs.getInt("deptno"));
System.out.println(rs.getString("dname"));
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(rs != null){
rs.close();
rs = null;//便于垃圾回收器回收
}

if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }示例二：测试DML语句，没有返回的结果集   
改备注：   window-show views-others-My Eclipse DataBase-DB Browser,
  * 通过Derby可直接连接数据库，查看数据库中的表内容，查看DML语句的结果public class TestDML {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;


try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();

String sql = "insert into dept values(60,'教学部','BJ')";

stmt.executeUpdate(sql);

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }

示例三：测试DML语句，通过args数组传入3个参数，并向表中添加一条记录；

在run configuration 的 Arguments - program Arguments中输入这三个参数
缺点：sql语句太麻烦，在java中需要“ ‘dname’ ”表示，太过麻烦
public class TestDML2 {
public static void main(String[] args) {
int deptno = 0;
//可以统一声明，也可以用的时候再声明
Connection conn = null;
Statement stmt = null;


if(args.length != 3){
System.out.println("参数不正确，请输入三个参数：deptno,dname,loc");
System.exit(-1);
}

try{
deptno = Integer.parseInt(args[0]);
//考虑到用户可能不输入数字的情况
}catch(NumberFormatException e){
System.out.println("参数错误，请输入整数！");
System.exit(-1);
}

String dname = args[1];
String loc = args[2];

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();

String sql = "insert into dept values("+deptno+",'"+dname+"','"+loc+"')";
//为保证正确，在控制台打印一下接收的sql语句，看是否有语法错误
System.out.println(sql);

stmt.executeUpdate(sql);

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }

示例四：基于示例三的缺点，使用PreparedStatement可以灵活指定SQL语句中的变量 ,普通的Statement太麻烦

public class TestPreparedStmt {
public static void main(String[] args) {
int deptno = 0;
//可以统一声明，也可以用的时候再声明
Connection conn = null;
PreparedStatement pstmt = null;


if(args.length != 3){
System.out.println("参数不正确，请输入三个参数：deptno,dname,loc");
System.exit(-1);
}

try{
deptno = Integer.parseInt(args[0]);
//考虑到用户可能不输入数字的情况
}catch(NumberFormatException e){
System.out.println("参数错误，请输入整数！");
System.exit(-1);
}

String dname = args[1];
String loc = args[2];

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
//PreparedStatement可以灵活指定SQL语句中的变量
pstmt = conn.prepareStatement("insert into dept values(?,?,?)");

//指定三个占位符?的类型
pstmt.setInt(1, deptno);
pstmt.setString(2,dname);
pstmt.setString(3,loc);

pstmt.executeUpdate();

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(pstmt != null){
pstmt.close();
pstmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }示例五：测试JDBC如何调用oracle中的存储过程procedure  
   
 存储过程： 

  * 
 CREATE OR REPLACE PROCEDURE p 

  * 
 (v_a IN NUMBER,v_b NUMBER,v_ret OUT NUMBER,v_temp IN OUT NUMBER) 

IS  

 

BEGIN 

 IF (v_a > v_b) THEN 

   v_ret := v_a; 

 ELSE 

   v_ret := v_b; 

 END IF; 

 v_temp := v_temp + 1; 

END; 

/ 

  */ 

 public class TestProcedure { 

public static void main(String[] args) { 

Connection conn = null; 

CallableStatement cstmt = null; 



try { 

//1.加载驱动load the Driver 

Class.forName("oracle.jdbc.driver.OracleDriver"); 

//new oracle.jdbc.driver.OracleDriver(); 

 

//实例时自动向DriverManager申请 

//2.获得连接(路径，用户名，密码) 

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456"); 

 

//3.执行SQL语句 

//前两个是输入参数，后两个是输出参数，为输出参数注册指定类型 

cstmt = conn.prepareCall("{call p(?,?,?,?)}"); 

 

cstmt.registerOutParameter(3, Types.INTEGER); 

cstmt.registerOutParameter(4, Types.INTEGER); 

 

//传入int类型的实际参数,第三个参数是out类型，不传入参数，只带出值 

cstmt.setInt(1,3); 

cstmt.setInt(2,4); 
 

cstmt.setInt(4,5); 

 

cstmt.execute(); 



System.out.println(cstmt.getInt(3)); 

System.out.println(cstmt.getInt(4)); 

 

} catch (ClassNotFoundException | SQLException e) { 

e.printStackTrace(); 

}finally{ 

//6.关闭连接 

try { 

if(cstmt != null){ 

cstmt.close(); 

cstmt = null; 

} 

 

if(conn != null){ 

conn.close(); 

conn = null; 

} 

} catch (SQLException e) { 

e.printStackTrace(); 

} 

} 

 

 

 

} 

 }

示例六：测试批处理，处理SQL语句集

public class TestBatch {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;


try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
//第一种方式执行批处理
/* stmt = conn.createStatement();

String sql1 = "insert into dept values(61,'教学部','BJ')";
String sql2 = "insert into dept values(62,'教学部','BJ')";
String sql3 = "insert into dept values(63,'教学部','BJ')";

stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.executeBatch();*/

//第二种方式

pstmt = conn.prepareStatement("insert into dept values(?,?,?)");

pstmt.setInt(1, 64);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.setInt(1, 65);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.setInt(1, 66);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.executeBatch();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }

示例七：测试可滚动的结果集

控制台结果：

* 7369
7839
true
false
14
7654public class TestResultSetScroll {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement(
//加上 滚动不敏感 和 只读 这两个参数，结果集就可以使用除了next()之外的指定行的方法
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from emp order by sal");

rs.next();
System.out.println(rs.getInt(1));//第一个字段empno
System.out.println(rs.getRow());//得到当前行数

rs.last();//滚到最后一个
System.out.println(rs.getString("empno"));//与上同
System.out.println(rs.isLast());//是不是最后一行
System.out.println(rs.isAfterLast());//是不是最后一行的下一行
System.out.println(rs.getRow());//得到总行数

rs.absolute(5);//绝对定位到第五行
System.out.println(rs.getString(1));
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(rs != null){
rs.close();
rs = null;//便于垃圾回收器回收
}

if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
 }

示例八：仅作了解，可更改的结果集，不支持，不推荐使用，没有效率上的提升，了解即可

控制台报错：

java.sql.SQLException: 对只读结果集的无效操作: updateString
at oracle.jdbc.driver.BaseResultSet.updateString(BaseResultSet.java:419)
at oracle.jdbc.driver.OracleResultSet.updateString(OracleResultSet.java:819)
at jdbc.TestUpdateRS.main(TestUpdateRS.java:44)public class TestUpdateRS {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement(
//加上 滚动不敏感 和 只读 这两个参数，结果集就可以使用除了next()之外的指定行的方法
//更新
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from emp");

rs.next();
rs.updateString("empno", "9999");
rs.updateRow();

//插入新行
rs.moveToInsertRow();
rs.updateInt(1, 9999);
rs.updateString("ename", "wang");
rs.insertRow();

//将光标移动到当前的行
rs.moveToCurrentRow();

//删除行
rs.absolute(5);
rs.deleteRow();

//取消更新
//rs.cancelRowUpdates();


} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(rs != null){
rs.close();
rs = null;//便于垃圾回收器回收
}

if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}

}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。