本例以JDBC 和 MY SQL DataBase为基础，演示执行SQL时对事物的支持，使用有事物和没有事物两种方式。
JDBC事务确保事务块中的SQL语句都执行成功，如果事务块中的任何一个SQL语句失败，则中止并回滚事务块中的所有内容。
通常在执行一条就会提交一条数据进数据库，这里我们可以使用

1
2
3
dbConnection.setAutoCommit(false); //关闭自动提交
dbConnection.commit(); //提交
dbConnection.rollback(); //回滚

来完成JDBC对事物的支持。

一，示例代码

连接数据库uilt: MySqlJDBSUtil.java 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.devnp.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySqlJDBSUtil {
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8";
	private static String userName = "root"; // base on your login user name
	private static String password = "!qaz2wsx"; // base on your login user

	public static Connection getConnection() {
		
		Connection connection = null;
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, userName, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static void connectionClose(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

操作代码：
JDBCTransaction.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
package com.devnp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class JDBCTransaction {

	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		updateWithOutTransaction();
		updateWithTransaction();
	}
	
	/**
	 * 不采用事物的方式, 发生异常操作不会回滚
	 * @throws SQLException
	 */
	public static void updateWithOutTransaction() throws SQLException {
		Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
		PreparedStatement preparedStatementUpdate = null;

		String insertTableSQL = "INSERT INTO USER_INFO"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";

		String updateTableSQL = "UPDATE USER_INFO SET USERNAME =? "
				+ "WHERE USER_ID = ?";

		try {
			dbConnection = MySqlJDBSUtil.getConnection();

			preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
			preparedStatementInsert.setInt(1, 8);
			preparedStatementInsert.setString(2, "devnp.com");
			preparedStatementInsert.setString(3, "system");
			preparedStatementInsert.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
			preparedStatementInsert.executeUpdate();

			//update the user name length is more than max
			preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
			preparedStatementUpdate.setString(1, "update the user name length is more than max");
			preparedStatementUpdate.setInt(2, 7);
			preparedStatementUpdate.executeUpdate();

			System.out.println("Insert Success and Updated will be failed!");

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}

			if (preparedStatementUpdate != null) {
				preparedStatementUpdate.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}
	}
	
	/**
	 * 采用事物的方式，发生异常操作会回滚
	 * @throws SQLException
	 */
	public static void updateWithTransaction() throws SQLException {
		Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
		PreparedStatement preparedStatementUpdate = null;

		String insertTableSQL = "INSERT INTO USER_INFO"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";

		String updateTableSQL = "UPDATE USER_INFO SET USERNAME =? "
				+ "WHERE USER_ID = ?";

		try {
			dbConnection = MySqlJDBSUtil.getConnection();
			
			dbConnection.setAutoCommit(false);

			preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
			preparedStatementInsert.setInt(1, 9);
			preparedStatementInsert.setString(2, "devnp.com");
			preparedStatementInsert.setString(3, "system");
			preparedStatementInsert.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
			preparedStatementInsert.executeUpdate();

			//update the user name length is more than max
			preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
			preparedStatementUpdate.setString(1, "update the user name length is more than max");
			preparedStatementUpdate.setInt(2, 8);
			preparedStatementUpdate.executeUpdate();

			dbConnection.commit();
			
			System.out.println("Insert and Updated all will be failed!");

		} catch (SQLException e) {

			dbConnection.rollback();
			
			e.printStackTrace();

		} finally {

			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}

			if (preparedStatementUpdate != null) {
				preparedStatementUpdate.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}
	}

}

二，测试

运行结果： 
Insert Success!
 Data truncation: Data too long for column 'USERNAME' at row 1
 Data truncation: Data too long for column 'USERNAME' at row 1
数据库截图： 运行前 运行后