Java Tutorial/Database/Transation

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

Commit or rollback transaction in JDBC

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();
      }
    }
  }
}





Committing and Rolling Back Updates to a Database

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();
  }
}





Determine if a Database Supports Transactions

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", "");
  }
}





Determining If a Database Supports Transactions

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
    }
  }
}





Disable auto commit mode in JDBC

import java.sql.Connection;
public class Main {
  public static void main(String[] args) throws Exception{
    Connection connection = null;
    connection.setAutoCommit(false);
    connection.rumit();
  }
}





If database support transaction

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();
  }
}





JDBC Transaction Isolation Levels

Connection.setTransactionIsolation (level)



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

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", "");
  }
}



  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

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", "");
  }
}





Turning On Autocommit Mode

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", "");
  }
}





Using a database transaction with JDBC

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();
    }
  }
}





Using a Transaction in JDBC with Exception catching

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", "");
  }
}



ID   NAME   
----------------------
1   nameValue   
2   nameValue
Источник — «http://jexp.ru/index.php?title=Java_Tutorial/Database/Transation&oldid=4778»