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();  
         }  
    }
}


}</pre>