Connect to MySQL database using JDBC-typical case study (vi)----Implementing account transfer operations

Reprint Please specify: HTTP://BLOG.CSDN.NET/UNIQUEWONDERQ

Problem: Use JDBC to connect to the database and realize the account transfer business. Transfer from a account to B account $500

Scheme:

In this case, in order to realize the transfer business, we need to perform two update operations, one is to update the amount of a account for the original amount of 500 yuan minus;

The second is to update the amount of account B to increase the original amount of $500. These two update operations either all succeed or fail all (transactions) to indicate the success or failure of the transfer. If the amount of a account is updated to the original amount minus $500, and the B account does not increase by $500 on the original amount, this results in inconsistent data. We can use transactions to control the two updates either all succeed or all fail.

In this case, you can use a transaction to encapsulate the two update operation as a logical wish, either all executed or completely non-executed, guaranteeing the integrity of the data.

The following methods implement the basic operation of JDBC transaction;

Connection.setautocommit (Boolean) when the argument is false, turn off autocommit

Connection.commit () COMMIT Transaction

Connection.rollback () ROLLBACK TRANSACTION

The core code for using JDBC to control transactions is as follows:

try {con=connectionsource.getconnection (); stmt=con.createstatement ();//SQL statement to update data string sql1= "update account set amount=amount-"+amount+" where id= "+" ' "+from+" ' "; String sql2= "Update account set amount=amount+" +amount+ "where id=" + "'" +to+ "'";//Turn off autocommit con.setautocommit (false);// Execute SQL Statement stmt.executeupdate (SQL1); stmt.executeupdate (SQL2);//Submit Con.commit ();} catch (SQLException e) {try {con.rollback ();} catch (SQLException E1) {System.out.println ("ROLLBACK TRANSACTION Exception! "); throw new RuntimeException (e);} SYSTEM.OUT.PRINTLN ("Database access Exception! "); throw new RuntimeException (e);} Finally{try {if (stmt!=null) {stmt.close ();} if (con!=null) {con.close ();}} catch (SQLException e) {System.out.println ("an exception occurred while releasing the resource! ");}}

Steps:

implementing This scenario requires the following steps:

Step One: Create the Account table and insert the test data

In the MySQL database, create the table and insert the test data, the SQL statement looks like this:

CREATE TABLE account (ID CHAR (1), Amount NUMERIC (10,2)), insert into account values (' A ', +); INSERT into account values (' B ', 2000);

Step two: Prepare the basic code for the JDBC operations database

First, create a new class trans and create a new transfer method in the class, with the declaration of the method as follows:

public void Transfer (String from,string to, double amount) {}

This method means that the transfer amount from the Zhuang Hu to the account to the amount.

Then, prepare the database connection connection object, manipulate the statement object of the SQL statement, and perform exception handling, as shown in the following code:

Package account;import java.sql.connection;import java.sql.sqlexception;import java.sql.statement;import DAO. Connectionsource;public class Trans {public static void main (string[] args) {}public void transfer (String from,string to,d ouble amount) {Connection con=null; Statement stmt=null;try {con=connectionsource.getconnection (); Stmt=con.createstatement ();} catch (SQLException e) { SYSTEM.OUT.PRINTLN ("Database access Exception! "); throw new RuntimeException (e);} Finally{try {if (stmt!=null) {stmt.close ();} if (con!=null) {con.close ();}} catch (SQLException e) {System.out.println ("an exception occurred while releasing the resource! ");}}}}

Step three: Realize the transfer function

Using the connection Setautocommit method, the Commit method, and the rollback method to control transactions to ensure that the transfer function is implemented correctly, the code looks like this:

package account;import java.sql.connection;import Java.sql.sqlexception;import Java.sql.statement;import DAO. Connectionsource;public class Trans {public static void main (string[] args) {}public void transfer (String from,string to,d ouble amount) {Connection con=null; Statement stmt=null;try {con=connectionsource.getconnection (); stmt=con.createstatement ();//SQL statement to update data string sql1= "Update account set amount=amount-" +amount+ "where id=" + "'" +from+ "'"; String sql2= "Update account set amount=amount+" +amount+ "where id=" + "'" +to+ "'";//Turn off autocommit con.setautocommit (false);// Execute SQL Statement stmt.executeupdate (SQL1); stmt.executeupdate (SQL2);//Submit Con.commit ();} catch (SQLException e) {try {con.rollback ();} catch (SQLException E1) {System.out.println ("ROLLBACK TRANSACTION Exception! "); throw new RuntimeException (e);} SYSTEM.OUT.PRINTLN ("Database access Exception! "); throw new RuntimeException (e);} Finally{try {if (stmt!=null) {stmt.close ();} if (con!=null) {con.close ();}} catch (SQLException e) {System.out.println ("an exception occurred while releasing the resource! ");}}}}

Step Four: Test

In the main method of the Trans class, the About figurines transfer method, the code is as follows:

public static void Main (string[] args) {trans trans=new trans (); Trans.transfer ("A", "B", 500);}

Run the Trans class, the console has no output, indicating success, and then look at the account table in the MySQL database, you will find that the amount of a accounts decreased by 500 yuan, the amount of the B account increased by 500 yuan.

Before running:

After running:

End of this section ~

Next time, continue, please keep on watching oh ~

Connect to MySQL database using JDBC-typical case study (vi)----Implementing account transfer operations