上机题目（0基础）- 数据库事务（Java）
/*
* 文件名称：JDBCTestCase.java
* 版权：Copyright 2006-2011 Huawei Tech. Co. Ltd. All Rights Reserved.
* 描写叙述： JDBCTestCase.java
* 改动人：z00106659
* 改动时间：2011-12-2
* 改动内容：新增
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
/**
* 这个是一个简单演示JDBC操作的实例。相应胶片解说的七个操作步骤, 使用JDK 自带的Derby数据库;
*
* Derby 是由IBM捐献给Apache的DB项目的一个纯Java数据库，两种使用模式。 一种是作为嵌入式数据库,还有一种是作为网络数据库
*
* 此用例參考的Derby自带的Demo 在嵌入式 场景的使用有非常具体的凝视，在使用时降低安装数据库的麻烦。
*
*
* @author z00106659
* @version ONIP BME V300R001 2011-12-2
* @since ONIP BME V300R001C00
*/
public class JDBCTestCase {
/**
* 驱动类名称
*/
private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
/**
* derby驱动协议头
*/
private String protocol = "jdbc:derby:";
public static void main(String[] args) {
new JDBCTestCase().go();
System.out.println("SimpleApp finished");
}
@SuppressWarnings("unchecked")
void go() {
/* load the desired JDBC driver */
loadDriver();
/*
* We will be using Statement and PreparedStatement objects for
* executing SQL. These objects, as well as Connections and ResultSets,
* are resources that should be released explicitly after use, hence the
* try-catch-finally pattern used below. We are storing the Statement
* and Prepared statement object references in an array list for
* convenience.
*/
Connection conn = null;
/*
* This ArrayList usage may cause a warning when compiling this class
* with a compiler for J2SE 5.0 or newer. We are not using generics
* because we want the source to support J2SE 1.4.2 environments.
*/
ArrayList statements = new ArrayList(); // list of Statements,
// PreparedStatements
PreparedStatement psInsert = null;
PreparedStatement psUpdate = null;
PreparedStatement psDelete = null;
Statement s = null;
ResultSet rs = null;
try {
Properties props = new Properties(); // connection properties
// providing a user name and password is optional in the embedded
// and derbyclient frameworks
props.put("user", "user1");
props.put("password", "user1");
String dbName = "derbyDB"; // the name of the database
conn = DriverManager.getConnection(protocol + dbName
+ ";create=true", props);
System.out.println("Connected to and created database " + dbName);
// We want to control transactions manually. Autocommit is on by
// default in JDBC.
/**
* 支持事物
*/
conn.setAutoCommit(false);
/*
* Creating a statement object that we can use for running various
* SQL statements commands against the database.
*/
s = conn.createStatement();
statements.add(s);
// We create a table...
s.execute("create table location(num int, addr varchar(40))");
System.out.println("Created table location");
// and add a few rows...
psInsert = conn
.prepareStatement("insert into location values (?, ?)");
statements.add(psInsert);
psInsert.setInt(1, 2014);
psInsert.setString(2, "zhangyaun");
psInsert.executeUpdate();
psInsert.setInt(1, 1956);
psInsert.setString(2, "Webster St.");
psInsert.executeUpdate();
System.out.println("Inserted 1956 Webster");
psInsert.setInt(1, 180);
psInsert.setString(2, "Union St.");
psInsert.executeUpdate();
System.out.println("Inserted 1910 Union");
conn.commit();//这里将操作提交
// Let's update some rows as well...
// parameter 1 and 3 are num (int), parameter 2 is addr (varchar)
try {
psDelete = conn
.prepareStatement("delete from location where num=?");
statements.add(psDelete);
psDelete.setInt(1, 2014);
psDelete.executeUpdate();
conn.rollback();//这里回滚。能够将删除的2014 回滚回来
} catch (RuntimeException e1) {
e1.printStackTrace();
}
psUpdate = conn
.prepareStatement("update location set num=?, addr=?
where num=?
");
statements.add(psUpdate);
psUpdate.setInt(1, 180);
psUpdate.setString(2, "Grand Ave.");
psUpdate.setInt(3, 1956);
psUpdate.executeUpdate();
System.out.println("Updated 1956 Webster to 180 Grand");
conn.commit();
try {
psUpdate.setInt(1, 300);
psUpdate.setString(2, "Lakeshore Ave.");
psUpdate.setInt(3, 180);
psUpdate.executeUpdate();
System.out.println("Updated 180 Grand to 300 Lakeshore");
conn.commit();
} catch (RuntimeException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/*
* We select the rows and verify the results.
*/
rs = s.executeQuery("SELECT num, addr FROM location ORDER BY num");
while (rs.next()) {
System.out.println(rs.getInt(1));
}
int number; // street number retrieved from the database
boolean failure = false;
if (!failure) {
System.out.println("Verified the rows");
}
// delete the table
s.execute("drop table location");
System.out.println("Dropped table location");
/*
* We commit the transaction. Any changes will be persisted to the
* database now.
*/
conn.commit();
System.out.println("Committed the transaction");
try {
// the shutdown=true attribute shuts down Derby
DriverManager.getConnection("jdbc:derby:;shutdown=true");
// To shut down a specific database only, but keep the
// engine running (for example for connecting to other
// databases), specify a database in the connection URL:
// DriverManager.getConnection("jdbc:derby:" + dbName +
// ";shutdown=true");
} catch (SQLException se) {
if (((se.getErrorCode() == 50000) && ("XJ015".equals(se
.getSQLState())))) {
// we got the expected exception
System.out.println("Derby shut down normally");
// Note that for single database shutdown, the expected
// SQL state is "08006", and the error code is 45000.
} else {
// if the error code or SQLState is different, we have
// an unexpected exception (shutdown failed)
System.err.println("Derby did not shut down normally");
printSQLException(se);
}
}
} catch (SQLException sqle) {
printSQLException(sqle);
} finally {
// release all open resources to avoid unnecessary memory usage
// ResultSet
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
try {
if (st != null) {
st.close();
st = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
}
// Connection
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
}
}
/**
* Reports a data verification failure to System.err with the given message.
*
* @param message
* A message describing what failed.
*/
private void reportFailure(String message) {
System.err.println("\nData verification failed:");
System.err.println('\t' + message);
}
/**
* Prints details of an SQLException chain to <code>System.err</code>.
* Details included are SQL State, Error code, Exception message.
*
* @param e
* the SQLException from which to print details.
*/
public static void printSQLException(SQLException e) {
// Unwraps the entire exception chain to unveil the real cause of the
// Exception.
while (e != null) {
System.err.println("\n----- SQLException -----");
System.err.println(" SQL State: " + e.getSQLState());
System.err.println(" Error Code: " + e.getErrorCode());
System.err.println(" Message: " + e.getMessage());
// for stack traces, refer to derby.log or uncomment this:
// e.printStackTrace(System.err);
e = e.getNextException();
}
}
/**
* Loads the appropriate JDBC driver for this environment/framework. For
* example, if we are in an embedded environment, we load Derby's embedded
* Driver, <code>org.apache.derby.jdbc.EmbeddedDriver</code>.
*/
private void loadDriver() {
try {
Class.forName(driver).newInstance();
System.out.println("Loaded the appropriate driver");
} catch (ClassNotFoundException cnfe) {
System.err.println("\nUnable to load the JDBC driver " + driver);
System.err.println("Please check your CLASSPATH.");
cnfe.printStackTrace(System.err);
} catch (InstantiationException ie) {
System.err.println("\nUnable to instantiate the JDBC driver "
+ driver);
ie.printStackTrace(System.err);
} catch (IllegalAccessException iae) {
System.err.println("\nNot allowed to access the JDBC driver "
+ driver);
iae.printStackTrace(System.err);
}
}
}
实现数据库事务
首先设置：
conn.setAutoCommit(false);在commit()方法和rollback方法之间的操作会被回滚。我们做实验：
conn.commit();//这里将操作提交
// Let's update some rows as well...
// parameter 1 and 3 are num (int), parameter 2 is addr (varchar)
try {
psDelete = conn
.prepareStatement("delete from location where num=?");
statements.add(psDelete);
psDelete.setInt(1, 2014);
psDelete.executeUpdate();
conn.rollback();//这里回滚，能够将删除的2014 回滚回来这时删除的2014将回滚回来。回滚一般用于发生异常时，因此一般能够写在catch中，当删除不存在的编号时。回滚就起作用了。