Computer questions (Elementary)-database transactions (Java)

Computer questions (Elementary)-database transactions (Java)

/** File name: JDBCTestCase. java * Copyright: Copyright 2006-2011 Huawei Tech. co. ltd. all Rights Reserved. * Description: JDBCTestCase. java * modifier: z000000659 * modification time: 2011-12-2 * modification content: Add */? 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;/*** this is a simple example of JDBC operations. corresponding to the seven operation steps described on the film, use the Derby database that comes with JDK; ** Derby is a pure Java Database donated by IBM to the Apache DB project. It can be used as an embedded database, the other is as a network database ** the Derby built-in Demo referenced in this case has detailed comments on the use of embedded scenarios. Reduce the trouble of installing databases during use; * ** @ author z000000659 * @ version onip bme V300R001 2011-12-2 * @ since onip bme V300R001C00 */public class JDBCTestCase {/*** driver class name */private String driver = "org. apache. derby. jdbc. embeddedDriver ";/*** derby driver protocol header */private String protocol =" jdbc: derby: "; public static void main (String [] args) {new JDBCTestCase (). go (); System. out. println ("SimpleApp finished");} @ SuppressWar Nings ("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 shoshould 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; PreparedS Tatement 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 = DriverManage R. 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. /*** supports things */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.exe cute ("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.exe cuteUpdate (); psInsert. setInt (1, 1956); psInsert. setString (2, "Webster St. "); psInsert.exe cuteUpdate (); System. out. println ("Inserted 1956 Webster"); psInsert. setInt (1,180); psInsert. setString (2, "Union St. "); psInsert.exe cuteUpdate (); System. out. println ("Inserted 1910 Union"); conn. commit (); // here Submit the operation // 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.exe cuteUpdate (); conn. rollback (); // roll back the deleted 2014.} catch (RuntimeException e1) {e1.printStackTrace ();} psUpdate = conn. prepareStatement ("update location set num = ?, Addr =? Where num =? "); Statements. add (psUpdate); psUpdate. setInt (1, 1,180); psUpdate. setString (2, "Grand Ave. "); psUpdate. setInt (3, 1956); psUpdate.exe cuteUpdate (); System. out. println ("Updated 1956 Webster to 180 Grand"); conn. commit (); try {psUpdate. setInt (1, 1,300); psUpdate. setString (2, "Lakeshore Ave. "); psUpdate. setInt (3,180); psUpdate.exe cuteUpdate (); 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.exe cuteQuery ("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.exe cute ("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 (sq Le);} 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 stement 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 chainSystem.err. * 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,org.apache.derby.jdbc.EmbeddedDriver. */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 );}}}

Implement database transactions

First set:

conn.setAutoCommit(false);

The operation between the commit () method and the rollback method will be rolled back. Let's do the experiment:

Conn. commit (); // submit the operation here // 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.exe cuteUpdate (); conn. rollback (); // roll back the deleted 2014

At this time, the deleted 2014 will be rolled back. rollback is generally used when an exception occurs, so it can be written in catch. When deleting a nonexistent number, rollback will take effect.