On-machine topics (beginner)-Database transactions (Java)

/* * File name: Jdbctestcase.java * Copyright 2006-2011 Huawei Tech Co., Ltd.  All rights Reserved. * Description: Jdbctestcase.java * Modified by: z00106659 * Modified: 2011-12-2 * Modified content: Added */?import java.sql.connection;import java.sql.DriverMa Nager;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 a JDBC operation, corresponding to the seven steps of the film tutorial, Use the Derby database that comes with the JDK; * Derby is a pure Java database of DB projects donated by IBM to Apache, two usage patterns, one as an embedded database and the other as a network database * This use case reference of the Derby comes with a detailed comment on the usage of the embedded scene, minus Less trouble installing the database; * * * @author z00106659 * @version onip BME v300r001 2011-12-2 * @since onip BME v300r001c00 */public class J dbctestcase {/** * Driver class name */private String Driver = "Org.apache.derby.jdbc.EmbeddedDriver";/** * Derby Driver Protocol Header */Priva Te 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'll be using the Statement and PreparedStatement objects for * executing SQL. These objects, as well as Connections and resultsets, * is resources that should is released explicitly after use, Henc E The * try-catch-finally pattern used below.   We are storing the Statement * and Prepared Statement object references a array list for * convenience.  */Connection conn = null; /* * This ArrayList usage is cause a warning when compiling this class * with a compiler for J2SE 5.0 or newer.   We is not using a 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 are 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 with//default in JDBC.   /** * Support 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.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 ();//here will operate submit/Let's update some rows as well ...//parameter 1 and 3 is 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 ();//rollback here, you can delete the 2014 rollback back} 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 Grand");   Conn.commit ();    try {psupdate.setint (1, 300);    Psupdate.setstring (2, "Lakeshore Ave.");    Psupdate.setint (3, 180);    Psupdate.executeupdate ();    System.out.println ("Updated Grand to 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'll is 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-specific database only, but keep the/engine running (for example-connecting to other//dat abases), specify a database in the connection URL://Drivermanager.getconnection ("Jdbc:derby:" + dbName +//"; shut   Down=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: Shutdown, the expected//SQL state is "08006", and the error code is 45000. } else {//If the error code or SQLState is different, we had//an unexpected exception (shutdown failed) S     Ystem.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.clo     SE ();    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 is 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 is in an embedded environment, we load Derby ' s embedded * Driver, <code>org.apache.derby.jdb  C.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); } }}

Implementing Database Transactions

First set:

Conn.setautocommit (FALSE);

The action between the commit () method and the Rollback method is rolled back, and we do the experiment:

Conn.commit ()///here will operate submit/Let's   update some rows   as well ... Parameter 1 and 3 are num (int.), Parameter 2 is addr (varchar)   try {    psdelete = conn      . Preparestatement ("Del Ete from location where num=? ");    Statements.add (psdelete);    Psdelete.setint (1);    Psdelete.executeupdate ();    Conn.rollback ();//rollback here, you can remove the 2014 rollback back

When the deleted 2014 is rolled back, rollback is generally used for exceptions, so it is generally possible to write in a catch, and when a nonexistent number is deleted, the rollback works.

On-machine topics (beginner)-Database transactions (Java)