JDBC: self-growth and transactions, jdbc growth transactions

JDBC: self-growth and transactions, jdbc growth transactions
JDBC self-growth and transactions

 

1. Self-Growth

There are two table student tables (Student name, class), Class table (class number (self-increasing primary key), and class number) in the database ).

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

See the following code for demonstration ~

Public class AutoIncrement {Connection connection = null; @ Test public void TestAuto () throws Exception {// get connection = sqlUtil. getconnection (); // prepare the SQL statement String classSQL = "insert into class (ccount) values (?) "; String studentSQL =" insert into student values (?,?) "; // Get the object. Insert a data entry to the CLASS table first using two parameters. // The second parameter Statement. RETURN_GENERATED_KEYS can get the self-increasing value PreparedStatement preparedStatement = connection. prepareStatement (classSQL, Statement. RETURN_GENERATED_KEYS); // you can specify the preparedStatement parameter. setObject (1, 66); // execute preparedStatement.exe cuteUpdate (); // the following code obtains the auto-increment ID value Integer cid = null; // use getGeneratedKeys () obtain a result set ResultSet res = preparedStatement of the auto-increment parameter. getGeneratedKeys (); // get this value through iteration. Here I only have one self-increasing parameter if (res. next () {cid = res. getInt (1);} // ****** after this value is obtained, we will insert the employee information ********* preparedStatement = connection. prepareStatement (studentSQL); // sets the parameter and uses the obtained auto-increment value as the parameter preparedStatement. setObject (1, "Zhang San"); preparedStatement. setObject (2, cid); // execute preparedStatement.exe cuteUpdate (); // close the resource sqlUtil. close (preparedStatement, connection );}}

 

2. Transactions

As we all know, transactions are often used in databases. How can we use java code to operate transactions ~ See the following code and instructions ~

There is a table: bank (bname, money), used to record the name and amount of money

@ Test public void doCommit () throws Exception {// get connection = sqlUtil. getconnection (); // Jack transfers 100 yuan to Mark. String sql1 = "update bank set money = money-100 where bname = 'jack '"; // The account of Mark gets 100 yuan String sql2 = "update bank set money = money + 100 where bname = 'mark'"; // starts the transaction. false indicates that automatic commit is disabled, enable transaction // true indicates that SQL connection is automatically submitted. setAutoCommit (false); // Jack executes the transfer 100 PreparedStatement state = connection. prepareStatement (sql1); state.exe cuteUpdate (); // Mark to get 100 state = connection. prepareStatement (sql2); state.exe cuteUpdate (); // submit the transaction connection. commit (); // close the resource sqlUtil. close (state, connection );}

In this way, you can create and submit a transaction.

If an exception occurs after the "Jack transfers 100" operation and the sql2 statement is not executed, will both users suffer losses? At this time, the importance of the transaction is reflected. The transaction also has a function: rollback

Let's create an exception and roll back the transaction ~

Public class CommitTest {Connection connection = null; @ Test public void doCommit () {// Jack executes the transfer 100 PreparedStatement state = null; try {// get connection Connection = sqlUtil. getconnection (); // Jack transfers RMB 100 to Mark. String sql1 = "update bank set money = money-100 where bname = 'jack '"; // The account of Mark gets 100 yuan String sql2 = "update bank set money = money + 100 where bname = 'mark'"; // starts the transaction. false indicates that automatic commit is disabled, enable transaction // true indicates that SQL connection is automatically submitted. setAutoCommit (false); // Jack transfers 100 state = connection. prepareStatement (sql1); state.exe cuteUpdate (); // deliberately create an exception int a = 1/0; // Mark to get 100 state = connection. prepareStatement (sql2); state.exe cuteUpdate (); // submit the transaction connection. commit ();} catch (SQLException e) {e. printStackTrace (); try {// in case of any exception, roll back the transaction connection. rollback ();} catch (SQLException e1) {e1.printStackTrace (); throw new RuntimeException (e) ;}} finally {// close the resource sqlUtil. close (state, connection );}}}