Java学习历程(数据库与JDBC)

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class TestJDBC2 {
	
	public static void main(String[] args) {
		test_Transaction();
	}
	
	//下面是一个JDBC连接的基本步骤
	public static void test_JDBC() {
		Connection conn = null;
		Statement stmt = null;
		
		try{
		
			Class.forName("oracle.jdbc.driver.OracleDriver");			//向DriverManager注册驱动
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:oacl","zhangrui","zhangrui"); 		//连接数据库
			stmt = conn.createStatement();				//创建对话描述
			
		} catch(ClassNotFoundException ae) {
			ae.printStackTrace();
		} catch(SQLException ae) {
			ae.printStackTrace();
		} finally {
			try {	
				if(stmt != null) {
					stmt.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch(SQLException ae) {
				ae.printStackTrace();
			}
		}
		
		
	}
	
	public static void test_DML_Batch() {
		Connection conn = null;			//用于取得连接
		Statement stmt = null;			//用于取得对话描述
		ResultSet rs = null;			//用于获取结果
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","zhangrui","zhangrui");
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery("select * from emp");				//取得查询结果
			while(rs.next()) {
				System.out.println(rs.getString("sal"));				//打印查询结果
			}
			
			stmt.addBatch("insert into dept2 values(60,'SC','AAA')");			//将命令添加到批处理
			stmt.addBatch("insert into dept2 values(61,'AM','BBB')");
			stmt.addBatch("insert into dept2 values(62,'XK','CCC')");
			stmt.executeBatch();							//执行批处理
			
		} catch(ClassNotFoundException ae) {
			System.out.println("this driver not found!");
		} catch(SQLException ae) {
			ae.printStackTrace();
		} finally {
				try {	
					if(rs != null) {
						rs.close();
					}
					if(stmt != null) {
						stmt.close();
					}
					if(conn != null) {
						conn.close();
					}
				} catch(SQLException ae) {
					ae.printStackTrace();
				}
		}
		
	}
	
	public static void test_Prepare_Batch() {
		Connection conn = null;
		PreparedStatement psmt = null;
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","zhangrui","zhangrui");
			psmt = conn.prepareStatement("insert into dept2 values (? , ? , ?)");				//准备执行的命令，其中数据由问号替代
			
			psmt.setInt(1, 63);				//设置第一个问号为整数63
			psmt.setString(2, "BJ");		//第二个为字符串"BJ"
			psmt.setString(3, "DDD");		//第三个为字符串"DDD"
			psmt.addBatch();				//添加到批处理
			
			psmt.setInt(1, 64);
			psmt.setString(2, "SH");
			psmt.setString(3, "EEE");
			psmt.addBatch();
			
			psmt.setInt(1, 65);
			psmt.setString(2, "TJ");
			psmt.setString(3, "FFF");
			psmt.addBatch();
			
			psmt.executeBatch();			//执行批处理
		
		} catch(ClassNotFoundException ae) {
			ae.printStackTrace();
		} catch(SQLException ae) {
			ae.printStackTrace();
		} finally {
			try {
				if(psmt != null) {
					psmt.close();
				}
				if(conn != null) {
					conn = null;
				}
			} catch(SQLException ae) {
				ae.printStackTrace();
			}
		}
	}
	
	public static void test_Transaction() {
		Connection conn = null;
		Statement stmt = null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","zhangrui","zhangrui");
			
			conn.setAutoCommit(false);					//取消自动提交,以便创建Transaction
			
			stmt = conn.createStatement();
			//stmt.executeUpdate("delete from dept2 where dname = 'Game'");
			stmt.executeUpdate("insert into dept2 values (34,'Game','Beijing')");
			stmt.executeUpdate("update dept52 set loc = 'SHANGHAI' where dname = 'Game'");
			conn.commit();					//执行完所有后提交
			
		} catch(ClassNotFoundException ae) {
			ae.printStackTrace();
		} catch(SQLException ae) {
			try {
				conn.rollback();				//若提交过程有任何误差,则进行回滚
				conn.setAutoCommit(true);			//恢复为自动提交
				ae.printStackTrace();				//打印错误
			} catch(SQLException a) {
				a.printStackTrace();
			}
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch(SQLException ae) {
				ae.printStackTrace();
			}
		}
		
	}
	
}