Java Tutorial/Database/Transation
Содержание
- 1 Commit or rollback transaction in JDBC
- 2 Committing and Rolling Back Updates to a Database
- 3 Determine if a Database Supports Transactions
- 4 Determining If a Database Supports Transactions
- 5 Disable auto commit mode in JDBC
- 6 If database support transaction
- 7 JDBC Transaction Isolation Levels
- 8 Rollback to savepoint
- 9 Transaction Isolation Level supported
- 10 Turning On Autocommit Mode
- 11 Using a database transaction with JDBC
- 12 Using a Transaction in JDBC with Exception catching
Commit or rollback transaction in JDBC
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { String url = "jdbc:mysql://localhost/testdb"; String username = "root"; String password = ""; Class.forName("com.mysql.jdbc.Driver"); Connection conn = null; try { conn = DriverManager.getConnection(url, username, password); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.execute("INSERT INTO orders (username, order_date) VALUES ("java", "2007-12-13")", Statement.RETURN_GENERATED_KEYS); ResultSet keys = st.getGeneratedKeys(); int id = 1; while (keys.next()) { id = keys.getInt(1); } PreparedStatement pst = conn.prepareStatement("INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)"); pst.setInt(1, id); pst.setString(2, "1"); pst.setInt(3, 10); pst.setDouble(4, 100); pst.execute(); conn.rumit(); System.out.println("Transaction commit..."); } catch (SQLException e) { if (conn != null) { conn.rollback(); System.out.println("Connection rollback..."); } e.printStackTrace(); } finally { if (conn != null && !conn.isClosed()) { conn.close(); } } }
}</source>
Committing and Rolling Back Updates to a Database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; public class Main {
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName); String serverName = "127.0.0.1"; String portNumber = "1433"; String mydatabase = serverName + ":" + portNumber; String url = "jdbc:JSQLConnect://" + mydatabase; String username = "username"; String password = "password"; Connection connection = DriverManager.getConnection(url, username, password); // Disable auto commit connection.setAutoCommit(false); // Do SQL updates... // Commit updates connection.rumit(); }
}</source>
Determine if a Database Supports Transactions
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {
public static void main(String[] a) throws Exception { Connection conn = getConnection(); DatabaseMetaData dbMetaData = conn.getMetaData(); System.out.println(dbMetaData.supportsTransactions()); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
Determining If a Database Supports Transactions
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName); String serverName = "127.0.0.1"; String portNumber = "1433"; String mydatabase = serverName + ":" + portNumber; String url = "jdbc:JSQLConnect://" + mydatabase; String username = "username"; String password = "password"; Connection connection = DriverManager.getConnection(url, username, password); DatabaseMetaData dmd = connection.getMetaData(); if (dmd.supportsTransactions()) { // Transactions are supported } else { // Transactions are not supported } }
}</source>
Disable auto commit mode in JDBC
<source lang="java">
import java.sql.Connection; public class Main {
public static void main(String[] args) throws Exception{ Connection connection = null; connection.setAutoCommit(false); connection.rumit(); }
}</source>
If database support transaction
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {
private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost/yourDatabase"; private static final String USERNAME = "root"; private static final String PASSWORD = ""; public static void main(String[] args) throws Exception { Class.forName(DRIVER); Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); DatabaseMetaData metadata = connection.getMetaData(); boolean isSupportTransaction = metadata.supportsTransactions(); System.out.println("Support Transaction = " + isSupportTransaction); connection.close(); }
}</source>
JDBC Transaction Isolation Levels
<source lang="java">
Connection.setTransactionIsolation (level)</source>
JDBC Defined ConstantDescriptionTRANSACTION_READ_UNCOMMITTEDAllows dirty reads, non-repeatable reads, and phantom reads to occur.TRANSACTION_READ_COMMITTEDEnsures only committed data can be read.TRANSACTION_REPEATABLE_READIs close to being "serializable," however, "phantom" reads are possible.TRANSACTION_SERIALIZABLEDirty reads, non-repeatable reads, and phantom reads are prevented. Serializable.
A "phantom" read occurs when one transaction reads all rows that satisfy a WHERE condition, and a second transaction inserts a row that satisfies that WHERE condition, the first transaction then rereads for the same condition, retrieving the additional "phantom" row in the second read. (from book: JDBC Recipes A Problem-Solution Approach)
In addition, JDBC defines an additional constant, TRANSACTION_NONE, which is used to indicate that the driver does not support transactions.
Rollback to savepoint
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Savepoint; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,myURL CHAR);"); st.executeUpdate("insert into survey(id) values(01)"); st.executeUpdate("insert into survey(id) values(02)"); Savepoint mySavepoint = conn.setSavepoint("MYSAVEPOINT"); st.executeUpdate("insert into survey(id) values(03)"); conn.rumit(); conn.rollback (mySavepoint); st.close(); conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
- Connection.releaseSavepoint() is used for removing a savepoint from a transaction.
- A rollback implicitly releases any savepoints that were defined after it.
- Once a transaction is committed or completely rolled back, all defined savepoints are released.
- Once a savepoint has been removed, any reference to it will cause a SQLException to be thrown.
- It is also possible to nest savepoints within transactions.
Transaction Isolation Level supported
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); DatabaseMetaData dbMd = conn.getMetaData(); if (dbMd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)) { System.out.println("Transaction Isolation level " + "TRANSACTION_READ_COMMITTED is supported."); conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); } conn.close(); } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
Turning On Autocommit Mode
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); conn.setAutoCommit(true); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")"); st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); System.out.println("resultSet MetaData column Count=" + numberOfColumns); rs.close(); st.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
Using a database transaction with JDBC
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection connection = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); connection = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD"); connection.setAutoCommit(false); Statement statement = connection.createStatement(); statement.executeUpdate("UPDATE Table1 SET Value = 1 WHERE Name = "foo""); statement.executeUpdate("UPDATE Table2 SET Value = 2 WHERE Name = "bar""); connection.rumit(); } catch (SQLException ex) { connection.rollback(); } }
}</source>
Using a Transaction in JDBC with Exception catching
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); try { st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")"); st.executeUpdate("insert into survey (id,name ) values (2,"nameValue")"); // commits all the transactions conn.rumit(); } catch (Exception e){ //cancel (roll back) all the transactions conn.rollback(); // to see what went wrong e.printStackTrace(); } st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
ID NAME ---------------------- 1 nameValue 2 nameValue