JDBC基本操作示例代码详解编程语言

package test.jdbc; 
 
import java.sql.*; 
 
public class JDBCTest { 
 
    private static Connection conn = null; 
    static{ 
        try { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger"); 
        } catch (Exception e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        } 
    } 
    //最基本的操作读取 
    public void testStmt(){ 
          ResultSet rs = null;   
          Statement stmt = null;   
          try { 
             stmt = conn.createStatement();   
             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();  
        } catch (Exception e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }   
    } 
    //PreparedStatement读取  
    public void testPrepStmt(){ 
        PreparedStatement pstmt = null;   
        try { 
            pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");   
            pstmt.setInt(1, 33);   
            pstmt.setString(2, "name");   
            pstmt.setString(3, "loc");   
            pstmt.executeUpdate();   
            pstmt.close();   
            conn.close();   
        } catch (Exception e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }   
    } 
    //调用存储过程  
    public void testProc(){ 
        CallableStatement cstmt; 
        try { 
            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();   
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }   
    } 
    //执行批处理 
    public void testBatch(){ 
         /*  
        9.          Statement stmt = conn.createStatement();  
        10.         stmt.addBatch("insert into dept2 values (51, '500', 'haha')");  
        11.         stmt.addBatch("insert into dept2 values (52, '500', 'haha')");  
        12.         stmt.addBatch("insert into dept2 values (53, '500', 'haha')");  
        13.         stmt.executeBatch();  
        14.         stmt.close();  
        15.         */   
 
         try { 
            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();   
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }   
    } 
 
    //将整批分批执行批处理 
    //这才是理想的解决方案，它避免了SQL注入和内存不足的问题。看看我们如何递增计数器计数，一旦BATCHSIZE 达到 1000，我们调用executeBatch 
    public void testBatchs(){ 
        String sql = "insert into employee (name, city, phone) values (?, ?, ?)"; 
        PreparedStatement ps = conn.prepareStatement(sql); 
        final int batchSize = 1000; 
        int count = 0; 
        for (Employee employee: employees) { 
            ps.setString(1, employee.getName()); 
            ps.setString(2, employee.getCity()); 
            ps.setString(3, employee.getPhone()); 
            ps.addBatch(); 
            if(++count % batchSize == 0) { 
                ps.executeBatch(); 
            } 
        } 
        ps.executeBatch(); // insert remaining records 
        ps.close(); 
        conn.close(); 
 
    } 
    //执行事务 
    public void testTransaction(){  
         Statement stmt = null; 
         try { 
            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();   
        } catch (SQLException e) { 
            try { 
                conn.rollback(); 
                conn.setAutoCommit(true);  
            } catch (SQLException e1) { 
                // TODO Auto-generated catch block 
                e1.printStackTrace(); 
            }   
            e.printStackTrace(); 
        }finally{ 
            try {   
               if(stmt != null)   
                   stmt.close();   
               if(conn != null)   
                   conn.close();   
             } catch (SQLException e) {   
                 e.printStackTrace();   
             }   
        } 
    } 
 
}