JDBC基础学习（二）事务 2019.7.1

•事务是构成单一逻辑工作单位的操作集合。已提交事务是指成功执行完毕的事务，未能成功完成的事务称为中止事务，对中止事务造成的变更需要进行撤销处理，称为事务回滚。

//STEP 1. Import required packages
//See more detail at http://www.yiibai.com/jdbc/

import java.sql.*;

public class JdbcSavepoint {
// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost:3306/samp_db?serverTimezone=GMT";

//  Database credentials
	static final String USER = "root";
	static final String PASS = "zyl123";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			// STEP 2: Register JDBC driver
			Class.forName("com.mysql.cj.jdbc.Driver");

			// STEP 3: Open a connection
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);

			// STEP 4: Set auto commit as false.
			conn.setAutoCommit(false);

			// STEP 5: Execute a query to delete statment with
			// required arguments for RS example.
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			
			String SQL = "delete from person2";
			stmt.executeUpdate(SQL);
		
			
			SQL = "insert into person2(id, name, pid, createtime) values('101', 'name1', '2', '12')";
			stmt.executeUpdate(SQL);
			SQL = "insert into person2(id, name, pid, createtime) values('104', 'name1', '2', '12')";
			stmt.executeUpdate(SQL);
			SQL = "insert into person2(id, name, pid, createtime) values('107', 'name1', '2', '12')";
			stmt.executeUpdate(SQL);

			// STEP 6: Now list all the available records.
			String sql = "SELECT * FROM person2";
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println("List result set for reference....");
			printRs(rs);

			// STEP 7: delete rows having ID grater than 104
			// But save point before doing so.
			Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1");
			System.out.println("Deleting row....");
			SQL = "DELETE FROM person2 " + "WHERE ID = 104";
			stmt.executeUpdate(SQL);
			// oops... we deleted too wrong employees!
			// STEP 8: Rollback the changes afetr save point 2.
			conn.rollback(savepoint1);

			// STEP 9: delete rows having ID grater than 104
			// But save point before doing so.
			Savepoint savepoint2 = conn.setSavepoint("ROWS_DELETED_2");
			System.out.println("Deleting row....");
			SQL = "DELETE FROM person2 " + "WHERE ID = 107";
			stmt.executeUpdate(SQL);

			// STEP 10: Now list all the available records.
			sql = "SELECT * FROM person2";
			rs = stmt.executeQuery(sql);
			System.out.println("List result set for reference....");
			printRs(rs);

			// STEP 10: Clean-up environment
			rs.close();
			
			conn.commit();
			// stmt.close();
			// conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
			// If there is an error then rollback the changes.
			System.out.println("Rolling back data here....");
			try {
				if (conn != null)
					conn.rollback();
			} catch (SQLException se2) {
				se2.printStackTrace();
			} // end try

		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			} // nothing we can do
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			} // end finally try
		} // end try
		System.out.println("Goodbye!");
	}// end main

	public static void printRs(ResultSet rs) throws SQLException {
		// Ensure we start with first row
		rs.beforeFirst();
		while (rs.next()) {
			// Retrieve by column name
			int id = rs.getInt("id");
			String age = rs.getString("name");
			String first = rs.getString("pid");
			String last = rs.getString("createtime");

			// Display values
			System.out.print("id: " + id);
			System.out.print(", name: " + age);
			System.out.print(", pid: " + first);
			System.out.println(", createtime: " + last);
		}
		System.out.println();
	}// end printRs()
}// end JDBCExample

1.事务操作缺省是自动提交：

  1）一条对数据库的更新操作成功后，系统将自动调用commit()方法提交

  2）失败调用rollback()方法来回滚。

   

2.为了能将多个JDBC语句组合成一个操作单元，以保证数据的一致性。可以禁止自动提交，之后把多个数据库操作的表达式作为一个事务，在操作完成后调用commit()来进行整体提交，倘若其中一个表达式操作失败，可以在异常捕获时调用rollback()进行回滚。

 

3.利用Connection对象的setAutoCommit()方法，可以开启或者关闭自动提交方式。

 

小结：

1.JDBC是Java应用与数据库通信的基础
2.JDBC包含一组类与接口，用于连接到任何数据库
3.JDBC访问数据库的一般步骤是：
    1）加载JDBC驱动程序
    2）建立数据库连接
    3）创建Statement对象
    4）执行SQL语句
    5）处理返回结果
    6）关闭创建的对象 
4.JDBC通过ResultSet维持查询结果集，并提供游标进行数据操作
5.通过Statement实现静态SQL查询
6.使用PreparedStatement实现动态SQL查询
7.使用CallableSatement实现存储过程的调用
8.DatabaseMetaData接口用于得到关于数据库的信息
9.ResultSetMetaData接口主要用来获取结果集的结构
10.JDBC默认的事务提交模式是自动提交
通过setAutoCommit()方法控制自动提交模式，使用rollback()方法实现事务回滚