JDBC事务

事物

准备：导入数据库Utils

 1.简单的一般回滚操作

package demo2;

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

public class Test {
	public static void main(String[] args) throws SQLException {
		testaaa();
	}

	private static void testaaa() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			conn.setAutoCommit(false);//关闭自动提交
			
			// 对ID为1的用户进行减操作
			String sql = "update user set money=money-10 where id=1";
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			// 如果ID为2的用户金额大于2000抛异常
			sql = "select money from user where id = 2";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 2000) {
				throw new RuntimeException("太大了");
			}
			// 对ID为2的用户进行减操作
			sql = "update user set money=money+10 where id=2";
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			
			conn.commit();//一起执行，或者一起撤销任务
		} catch (SQLException e) {
			if(conn != null){ //当执行了操作，→抛出了异常，进行回滚操作
				conn.rollback();
				throw e;
			}
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}

}

2.事物的保存点(不全部回滚，回滚到保存点)

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;


public class SavePointTest {

	public static void main(String[] args) throws SQLException {
		test();
	}

	static void test() throws SQLException {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		Savepoint sp = null;
		try {
			conn = JdbcUtils.getConnection();
			conn.setAutoCommit(false);			
			st = conn.createStatement();
			String sql = "update user set money=money-10 where id=1";
			st.executeUpdate(sql);

//定一个保存点，将回滚到这里，从这里往上的代码依旧执行
			sp = conn.setSavepoint();

			sql = "update user set money=money-10 where id=3";
			st.executeUpdate(sql);

			sql = "select money from user where id=2";
			rs = st.executeQuery(sql);
			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 300)
				throw new RuntimeException("超出范围！");

			sql = "update user set money=money+10 where id=2";
			st.executeUpdate(sql);

			conn.commit();
		} catch (RuntimeException e) {
			if (conn != null && sp != null) {
				conn.rollback(sp);
				conn.commit();
			}
			throw e;
		} catch (SQLException e) {
			if (conn != null)
				conn.rollback();
			throw e;
		} finally {
			JdbcUtils.free(rs, st, conn);
		}
	}
}