JDBC事务确保一组SQL语句作为一个单元执行,所有语句要么成功执行,要么不执行任何语句(回滚所有更改)。
1.没有JDBC事务
1.1一个插入两行并更新一行的JDBC示例。
TransactionExample.java
package com.mkyong.jdbc;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
public class TransactionExample {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
Statement statement = conn.createStatement();
PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT);
PreparedStatement psUpdate = conn.prepareStatement(SQL_UPDATE)) {
statement.execute(SQL_TABLE_DROP);
statement.execute(SQL_TABLE_CREATE);
// Run list of insert commands
psInsert.setString(1, "mkyong");
psInsert.setBigDecimal(2, new BigDecimal(10));
psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
psInsert.execute();
psInsert.setString(1, "kungfu");
psInsert.setBigDecimal(2, new BigDecimal(20));
psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
psInsert.execute();
// Run list of update commands
// below line caused error, test transaction
// org.postgresql.util.PSQLException: No value specified for parameter 1.
psUpdate.setBigDecimal(2, new BigDecimal(999.99));
//psUpdate.setBigDecimal(1, new BigDecimal(999.99));
psUpdate.setString(2, "mkyong");
psUpdate.execute();
} catch (Exception e) {
e.printStackTrace();
}
}
private static final String SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?,?,?)";
private static final String SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?";
private static final String SQL_TABLE_CREATE = "CREATE TABLE EMPLOYEE"
+ "("
+ " ID serial,"
+ " NAME varchar(100) NOT NULL,"
+ " SALARY numeric(15, 2) NOT NULL,"
+ " CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,"
+ " PRIMARY KEY (ID)"
+ ")";
private static final String SQL_TABLE_DROP = "DROP TABLE EMPLOYEE";
}
输出,更新失败,并引发异常,最后插入2行,但跳过了更新。
org.postgresql.util.PSQLException: No value specified for parameter 1.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:292)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
at com.mkyong.jdbc.TransactionExample.main(TransactionExample.java:41)
![输出](https://i-blog.csdnimg.cn/blog_migrate/8e49f92e9d23e8e4ddec4e706126f50d.png)
2.使用JDBC事务
2.1要启用事务,请将auto commit设置为false。
conn.setAutoCommit(false); // default true
// start transaction block
// insert
// update
// if any errors within the start and end block,
// rolled back all changes, none of the statements are executed.
// end transaction block
conn.commit();
2.2与JDBC事务相同的示例。
TransactionExample.java
package com.mkyong.jdbc;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
public class TransactionExample {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
Statement statement = conn.createStatement();
PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT);
PreparedStatement psUpdate = conn.prepareStatement(SQL_UPDATE)) {
statement.execute(SQL_TABLE_DROP);
statement.execute(SQL_TABLE_CREATE);
// start transaction block
conn.setAutoCommit(false); // default true
// Run list of insert commands
psInsert.setString(1, "mkyong");
psInsert.setBigDecimal(2, new BigDecimal(10));
psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
psInsert.execute();
psInsert.setString(1, "kungfu");
psInsert.setBigDecimal(2, new BigDecimal(20));
psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
psInsert.execute();
// Run list of update commands
// error, test roolback
// org.postgresql.util.PSQLException: No value specified for parameter 1.
psUpdate.setBigDecimal(2, new BigDecimal(999.99));
//psUpdate.setBigDecimal(1, new BigDecimal(999.99));
psUpdate.setString(2, "mkyong");
psUpdate.execute();
// end transaction block, commit changes
conn.commit();
// good practice to set it back to default true
conn.setAutoCommit(true);
} catch (Exception e) {
e.printStackTrace();
}
}
//...
}
输出,不执行任何语句,回滚插入语句。
org.postgresql.util.PSQLException: No value specified for parameter 1.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:292)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
at com.mkyong.jdbc.TransactionExample.main(TransactionExample.java:41)
![输出](https://i-blog.csdnimg.cn/blog_migrate/09dae3a8a9ccf55f6e67b69a0598366e.png)
3.额外…
修复parameter 1
错误并查看预期结果。
//psUpdate.setBigDecimal(2, new BigDecimal(999.99));
psUpdate.setBigDecimal(1, new BigDecimal(999.99));
psUpdate.setString(2, "mkyong");
psUpdate.execute();
输出量
2 rows are inserted and 1 row is updated.
![输出](https://i-blog.csdnimg.cn/blog_migrate/3027c2f37b73942b4725ffbf19f6fcd7.png)
下载源代码
$ git clone https://github.com/mkyong/java-jdbc.git
$ cd PostgreSQL