Java Tutorial/Database/Transation

Материал из Java эксперт
Перейти к: навигация, поиск

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>



  1. Connection.releaseSavepoint() is used for removing a savepoint from a transaction.
  2. A rollback implicitly releases any savepoints that were defined after it.
  3. Once a transaction is committed or completely rolled back, all defined savepoints are released.
  4. Once a savepoint has been removed, any reference to it will cause a SQLException to be thrown.
  5. 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