JDBC操作数据库汇总.doc

一、SQL语言回顾1、Select select * from T where 2、Insert insert into T values()3、Create create table T()4、Delete delete from T where5、Update update T set t1= and t2=. where t3=6、Drop drop table T二、JDBC编程步骤 1、Load the DriverClass.forName( )|Class.forName( ).newInstance( )|new DriverName( )实例化时自动向DriverManager注册，不需显式调用DriverManager.registerDriver方法2、Connect to the DataBase DriverManager.getConnection( )3、Execute the 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 resultset ./ close the statement / close the connection 三、JDBC操作oracle数据库1、 JDBC连接oracle数据库：import java.sql.*;public class TestJDBC public static void main(String args) throws ClassNotFoundException, SQLException Class.forName(oracle.jdbc.driver.OracleDriver); /new oracle.jdbc.driver.OracleDriver(); Connection conn = DriverManager.getConnection(jdbc:oracle:thin::1521:solid, scott, tiger); 2、 向数据库中取字段，遍历数据库，并显示出相应字段：import java.sql.*;public class TestJDBC public static void main(String args) throws ClassNotFoundException, SQLException Class.forName(oracle.jdbc.driver.OracleDriver); /new oracle.jdbc.driver.OracleDriver(); Connection conn = DriverManager.getConnection(jdbc:oracle:thin::1521:solid, scott, tiger); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(select * from dept); while(rs.next() System.out.println(rs.getString(deptno); System.out.println(rs.getInt(deptno); rs.close(); stmt.close(); conn.close(); 3、 捕捉相关异常，完善JDBC编程：import java.sql.*;public class TestJDBC public static void main(String args) throws ClassNotFoundException, SQLException ResultSet rs = null; Statement stmt = null; Connection conn = null; try Class.forName(oracle.jdbc.driver.OracleDriver); conn = DriverManager.getConnection(jdbc:oracle:thin::1521:solid, scott, tiger); 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(); 四、JDBC编程高级1、 JDBC处理DML语句：import java.sql.*;public class TestDML 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::1521:solid, scott, tiger); stmt = conn.createStatement(); String sql = insert into dept2 values (99,develop,changsha); 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(); 2、 JDBC处理DML语句2：import java.sql.*;public class TestDML2 public static void main(String args) if (args.length != 3) System.out.println(Arguments error,please enter again!); System.exit(-1); int deptno = 0; try deptno = Integer.parseInt(args0); catch (NumberFormatException e) System.out.println(Arguments error); System.exit(-1); String dname = args1; String loc = args2; Connection conn = null; Statement stmt = null; try Class.forName(oracle.jdbc.driver.OracleDriver); conn = DriverManager.getConnection(jdbc:oracle:thin::1521:solid, scott, tiger); stmt = conn.createStatement(); String sql = insert into dept2 values ( + deptno + , + dname + , + loc + );System.out.println(sql); stmt.executeUpdate(sql); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace(); finally try if (conn != null) conn.close(); conn = null; if (stmt != null) stmt.close(); stmt = null; catch (SQLException e) e.printStackTrace(); 3、 JDBC处理PreparedStatement，可以灵活指定SQL语句中的变量import java.sql.*;public class TestPrepStmt public static void main(String args) if (args.length != 3) System.out.println(Arguments error,please enter again!); System.exit(-1); int deptno = 0; try deptno = Integer.parseInt(args0); catch (NumberFormatException e) System.out.println(Arguments error); System.exit(-1); String dname = args1; String loc = args2; Connection conn = null; PreparedStatement pstmt = null; try Class.forName(oracle.jdbc.driver.OracleDriver); conn = DriverManager.getConnection(jdbc:oracle:thin::1521:solid, scott, tiger); pstmt = conn.prepareStatement(insert into dept2 values (?, ?, ?); pstmt.setInt(1, deptno); pstmt.setString(2, dname); pstmt.setString(3, loc); pstmt.executeUpdate(); catch (ClassNotFoundException e) e.printStackTrace(); catch (SQLException e) e.printStackTrace(); finally try if (conn != null) conn.close(); conn = null; if (pstmt != null) pstmt.close(); pstmt = null; catch (SQLException e) e.printStackTrace(); 4、 JDBC处理储存过程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::1521:SXT, scott, tiger); CallableStatement cstmt = conn.prepareCall(call p(?, ?, ?, ?); cstmt.registerOutParameter(3, Types.INTEGER); cstmt.registerOutParameter(4, 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(); 5、 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::1521:SXT, scott, tiger); /* Statement 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(); stmt.close(); */ PreparedStatement ps = conn.prepareStatement(insert into dept2 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(); 6、 JDBC处理Transactionimport 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::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(); mit(); conn.setAutoCommit(true); catch (ClassNotFoundException e) e.printStackTrace(); catch(SQLException e) e.printStackTrace(); try if(conn != null) conn.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.printStac