MySQL——JDBC工具类

线程安全分离配置手动事务 API

获取PreparedStatement

getStatement(String sql);

提交事务，关闭连接

commitAndClose(Statement... stmt);

回滚事务，关闭连接

rollbackAndClose(Statement... stmt);

public class JDBCUtil {
	private static final ThreadLocal local = new ThreadLocal<>();

	//获取Coon
	private static Connection getConn() {
		Connection conn1 = local.get();
		if (conn1 == null) {
			Connection conn2 = newConnection();
			local.set(conn2);
			start();
			return conn2;
		} else {
			return conn1;
		}
	}

	//新Coon
	private static Connection newConnection() {
		try {
			//获取配置文件信息
			Properties info = new Properties();
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("db.properties");
			info.load(is);
			String driver = info.getProperty("driver");
			String url = info.getProperty("url");
			String user = info.getProperty("user");
			String password = info.getProperty("password");
			//获取驱动，连接
			Class.forName(driver);
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			throw new RuntimeException("获取连接失败");
		}
	}

	//获取PS
	public static PreparedStatement getStatement(String sql) throws SQLException {
		return getConn().prepareStatement(sql);
	}

	//关闭Coon,PS
	private static void close(Connection conn, Statement... stmt) {
		try {
			for (Statement statement : stmt) {
				if (statement != null) {
					statement.close();
				}
			}
			if (conn != null) {
				conn.close();
				local.remove();
			}
		} catch (Exception e) {
			throw new RuntimeException("关闭异常");
		}
	}

	//事务的开启提交回滚
	private static void start() {
		try {
			getConn().setAutoCommit(false);
		} catch (SQLException e) {
			throw new RuntimeException("事务异常");
		}
	}

	public static void commitAndClose(Statement... stmt) {
		try {
			Connection conn = getConn();
			conn.commit();
			close(conn, stmt);
		} catch (SQLException e) {
			throw new RuntimeException("事务异常");
		}
	}

	public static void rollbackAndClose(Statement... stmt) {
		try {
			Connection conn = getConn();
			conn.rollback();
			close(conn, stmt);
		} catch (SQLException e) {
			throw new RuntimeException("事务异常");
		}
	}
}

pom

    mysql
    mysql-connector-java
    8.0.25

resources下db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xxx
user=xxx
password=xxx

测试代码

@Test
public void fun1() {
	PreparedStatement ps1 = null;
	PreparedStatement ps2 = null;
	String sql1 = "update student set sage=sage+1 where sid=1";
	String sql2 = "update student set sage=sage+10 where sid=1";
	try {
		//自动开启连接，开启事务
		ps1 = JDBCUtil.getStatement(sql1);
		ps1.executeUpdate();
		//Integer error = 1 / 0;
		ps2 = JDBCUtil.getStatement(sql2);
		ps2.executeUpdate();
		//提交
		JDBCUtil.commitAndClose(ps1, ps2);
	} catch (SQLException e) {
		e.printStackTrace();
		//回滚
		JDBCUtil.rollbackAndClose(ps1, ps2);
	}
}

运行结果：
数据库中的值加了11

手动产生一个异常，运行结果：
数据库中没有发生变化