Admin: 1 версия

2010-06-01T05:05:55Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== 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.
<p>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)</p>
<p>In addition, JDBC defines an additional constant, TRANSACTION_NONE, which is used to indicate that the driver does not support transactions.</p>

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



<OL><LI>Connection.releaseSavepoint() is used for removing a savepoint from a transaction.</LI><LI>A rollback implicitly releases any savepoints that were defined after it.</LI><LI>Once a transaction is committed or completely rolled back, all defined savepoints are released.</LI><LI>Once a savepoint has been removed, any reference to it will cause a SQLException to be thrown.</LI><LI>It is also possible to nest savepoints within transactions.</LI></OL>

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



<pre class=codeResult>ID NAME
----------------------
1 nameValue
2 nameValue</pre>

← Предыдущая	Версия 05:05, 1 июня 2010
(нет различий)

Java Tutorial/Database/Transation - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010