Java JDBC基本操作代码示范

包含数据读取、调用存储过程、执行批处理、事务等
转自：http://blog.csdn.net/jzhf2012/article/details/8458738

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