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