首页 > 代码库 > JDBC编程
JDBC编程
JDBC是数据库连接纽带,统一数据库接口。
JDBC编程步骤：
1.Load the Driver
<1.Class.forName()|Class.forname().newinstance()|new DirverName()
<2.实例化时自动向DirverManager注册，不需显式调用DriverManger.registerDriver()方法
2.Connect to the DataBase
<1.DriverManager.getConnection()
3.Execute the SQL
<1.Connection CreateStatement()
<2.Statement.excuteQuery() --用于select语句
<3.Statement.executeUpdate() --用于insert,update,delete语句
4.Retrieve the result data
<1.循环取得结果while(rs.next())
5.Show the result data
<1.将数据库中的各种类型转换为JAVA中的类型(getXXX)方法
6.Close
<1.close the resultset./close the statement/close the connection
JDBC连接oracle例子程序：
import java.sql.*;public class TestJDBC { public static void main(String[] args) { ResultSet rs = null; Statement stmt = null; Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); //new oracle.jdbc.driver.OracleDriver();另一种new Driver的方式 conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123"); stmt = conn.createStatement(); rs = stmt.executeQuery("select * from dept"); while(rs.next()) { System.out.println(rs.getString("deptno")); System.out.println(rs.getInt("deptno")); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { 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(); } } }}
利用命令行向oracle中插入记录：
import java.sql.*;public class TestDML2 { public static void main(String[] args) { if(args.length != 3) { System.out.println("Parameter Error! Please Input Again!"); System.exit(-1); } int deptno = 0; try { deptno = Integer.parseInt(args[0]); } catch (NumberFormatException e) { System.out.println("Parameter Error! Deptno should be Number Format!"); System.exit(-1); } String dname = args[1]; String loc = args[2]; Statement stmt = null; Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123"); stmt = conn.createStatement(); String sql = "insert into dept values (" + deptno + ",‘" + dname + "‘,‘" + loc + "‘)"; //注意sql格式System.out.println(sql); //将打印出的语句放到sql中执行，看哪里出错是最常利用的方法 stmt.executeUpdate(sql); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if(stmt != null) { stmt.close(); stmt = null; } if(conn != null) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); } } }}
用PrepareStatement灵活的指定sql（防止sql注入的一种方法）：
import java.sql.*;public class TestPreStmt { public static void main(String[] args) { if(args.length != 3) { System.out.println("Parameter Error! Please Input Again!"); System.exit(-1); } int deptno = 0; try { deptno = Integer.parseInt(args[0]); } catch (NumberFormatException e) { System.out.println("Parameter Error! Deptno should be Number Format!"); System.exit(-1); } String dname = args[1]; String loc = args[2]; PreparedStatement pstmt = null; Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123"); pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)"); //用prepareStatement灵活指定sql,?为占位符 pstmt.setInt(1, deptno); //设定第1个值为deptno pstmt.setString(2, dname); //设定第2个值为dname pstmt.setString(3, loc); //设定第3个值为loc pstmt.executeUpdate(); //注意与上面的stmt.executeUpdate(sql);不同 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if(pstmt != null) { pstmt.close(); pstmt = null; } if(conn != null) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); } } }}
JAVA通过JDBC调用存储过程（通过CallableStatement）：
先在ORACLE中定义这个存储过程:
create or replace procedure P (v_a in number,v_b number,v_ret out number,v_temp in out number)isbegin if(v_a > v_b) then v_ret := v_a; else v_ret := v_b;end if; v_temp := v_temp +1;end;/
import java.sql.*;public class TestProc { public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123"); CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}"); /*通过conn.prepareCall创建CallableStatement对象并调用存储过程p，该存储过程有四个参数，第1、2个参数为输入参 数，第三个参数是输入输出参数，第四个参数为输出参数*/ cstmt.registerOutParameter(3, Types.INTEGER); //声明第三个参数是输入参数，且类型是Types.INTEGER cstmt.registerOutParameter(4, Types.INTEGER); //声明第四个参数是输入参数，且类型是Types.INTEGER 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)); cstmt.close(); conn.close(); }}
JDBC批处理：
import java.sql.*;public class TestBatch { public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123"); //第一种批处理方法 /* Statement stmt = conn.createStatement(); //一个Statement只可以处理一条aql语句 stmt.addBatch("insert into dept values (51, ‘500‘, ‘haha‘)"); //将语句加入批处理 stmt.addBatch("insert into dept values (52, ‘500‘, ‘haha‘)"); //将语句加入批处理 stmt.addBatch("insert into dept values (53, ‘500‘, ‘haha‘)"); //将语句加入批处理 stmt.executeBatch(); //Statement一次执行批处理 stmt.close(); */ //第二种批处理方法 PreparedStatement ps = conn.prepareStatement("insert into dept values (?, ?, ?)"); ps.setInt(1, 61); ps.setString(2, "haha"); ps.setString(3, "bj"); ps.addBatch(); ps.setInt(1, 62); ps.setString(2, "haha"); ps.setString(3, "bj"); ps.addBatch(); ps.setInt(1, 63); ps.setString(2, "haha"); ps.setString(3, "bj"); ps.addBatch(); ps.executeBatch(); ps.close(); conn.close(); }}
处理Tracnsaction:
import java.sql.*;public class TestTransaction { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger"); conn.setAutoCommit(false); //取消自动提交 stmt = conn.createStatement(); stmt.addBatch("insert into dept2 values (51, ‘500‘, ‘haha‘)"); stmt.addBatch("insert into dept2 values (52, ‘500‘, ‘haha‘)"); stmt.addBatch("insert into dept2 values (53, ‘500‘, ‘haha‘)"); stmt.executeBatch(); conn.commit(); //自己提交 conn.setAutoCommit(true); //恢复现场 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); try { if(conn != null) { conn.rollback(); //有(SQLException时rollback conn.setAutoCommit(true); } } catch (SQLException e1) { e1.printStackTrace(); } }finally { try { if(stmt != null) stmt.close(); if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }}
JDBC编程
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。