JDBC's self-growth and transaction

JDBC's self-growth and transaction

1. Self-growth

There is a phenomenon: there are two tables in the database (student name, Class), Class table ( class number (self-growing primary key), class number).

Now I insert a message into the class table, only provide class size, class number from self-growth automatically generated. Then we insert a student information into this new class, which requires the class number of the new class, but this class number is automatically generated from self-growth, then how can we correctly insert this student's information?

Look at the following code demo ~

 Public classAutoIncrement {Connection Connection=NULL; @Test Public voidTestauto ()throwsException {//Get ConnectionsConnection =sqlutil.getconnection (); //Preparing SQL statementsString classsql = "INSERT into Class (Ccount) VALUES (?)"; String Studentsql= "INSERT into student values (?,?)"; //gets the object, first inserts a data into the class table, using a method with two parameters. //The second parameter Statement.return_generated_keys can get the self-growing valuePreparedStatement PreparedStatement =connection.preparestatement (Classsql,statement.return_generated_keys); //Setting ParametersPreparedstatement.setobject (1, 66); //Executionpreparedstatement.executeupdate (); //Here 's how to get this self-growing ID valueInteger cid =NULL; //use Getgeneratedkeys () to get a result set of self-growth parametersResultSet res =Preparedstatement.getgeneratedkeys (); //iterate to get that value, I have only one self-growing parameter here        if(Res.next ()) {CID= Res.getint (1); }                //****** get the value of the increase, we'll insert the employee information ********Preparedstatement=connection.preparestatement (Studentsql); //set the parameter to take the self-increment value as the parameterPreparedstatement.setobject (1, "Zhang San"); Preparedstatement.setobject (2, CID); //Executionpreparedstatement.executeupdate (); //Close ResourceSqlutil.close (PreparedStatement, connection); }}

2. Business

As we all know, the database is often used in the transaction, then how to use Java code to operate it ~ See the following code and instructions ~

There is a table: Bank (Bname,money), used to record the names of people and the number of money

@Test Public voidDocommit ()throwsException {//Get ConnectionsConnection =sqlutil.getconnection (); //Jack Transfers 100 yuan to mark. String SQL1 = "Update bank set money=money-100 where bname= ' Jack '"; //Mark's account gets 100 yuanString sql2 = "Update bank set money=money+100 where bname= ' Mark '"; //start transaction, false means turn off autocommit, that is, open transaction//true indicates that SQL is automatically committedConnection.setautocommit (false); //Jack executes the transferPreparedStatement state =connection.preparestatement (SQL1);        State.executeupdate (); //Mark wonState =connection.preparestatement (SQL2);        State.executeupdate (); //Commit a transactionConnection.commit (); //Close ResourceSqlutil.close (State, connection); }

This allows you to create and commit a transaction.

Then, if the "Jack execution transfer 100" after the operation, an exception occurred, did not execute: SQL2 statement, that two users do not have a loss? At this point, the importance of the transaction, the transaction also has a function: rollback

The following is intentionally creating an exception, and then rolling back the transaction ~

 Public classcommittest {Connection Connection=NULL; @Test Public voidDocommit () {//Jack executes the transferPreparedStatement state =NULL; Try {            //Get ConnectionsConnection =sqlutil.getconnection (); //Jack Transfers 100 yuan to mark. String SQL1 = "Update bank set money=money-100 where bname= ' Jack '"; //Mark's account gets 100 yuanString sql2 = "Update bank set money=money+100 where bname= ' Mark '"; //start transaction, false means turn off autocommit, that is, open transaction//true indicates that SQL is automatically committedConnection.setautocommit (false); //Jack executes the transferState =connection.preparestatement (SQL1);                        State.executeupdate (); //intentionally creating anomalies            intA = 1/0; //Mark wonState =connection.preparestatement (SQL2);            State.executeupdate (); //Commit a transactionConnection.commit (); } Catch(SQLException e) {e.printstacktrace (); Try {                //If any exceptions are encountered, roll back the transactionConnection.rollback (); } Catch(SQLException E1) {e1.printstacktrace (); Throw NewRuntimeException (e); }        }finally{            //Close ResourceSqlutil.close (State, connection); }    }}

　　

JDBC's self-growth and transaction