Database Technology-PreparedStatement and Transaction in JDBC

Database Technology-PreparedStatement and TransactionPreparedStatement in JDBC first introduce PreparedStatement: 1. PreparedStatement is a kind of Statement2, which provides more methods than the parent interface. prepstmtconn. prepareStatement (INSERTINTOanimalVALUES (?,?,?))

Database Technology-PreparedStatement and Transaction PreparedStatement in JDBC first introduces PreparedStatement: 1. PreparedStatement is a kind of Statement 2. It provides more methods than the parent interface. prepstmt = conn. prepareStatement (insert into animal VALUES (?,?,?))

Database Technology-PreparedStatement and TransactionPreparedStatement in JDBC
First, we will introduce PreparedStatement: 1. PreparedStatement is a Statement 2. It provides more methods than the parent interface. prepstmt = conn. prepareStatement ("insert into animal VALUES (?,?,?) "); Prepstmt. setInt (1, 1 );
Prepstmt. setString (2, "pig ");
Prepstmt. setInt (3, 10 );
Prepstmt.exe cute ();
Three? It indicates three placeholders and sets the values at three positions. 1 indicates the value of the first question mark, 2 indicates the value of the second question mark, and 3 indicates the value of the third question mark.
It can be understood as follows: I first prepare an SQL statement, which has three values waiting for confirmation. Next, I will determine the types and values of the three values in sequence.

package myjdbc;import java.sql.*;public class PreparedJdbc {private static String driver = "com.mysql.jdbc.Driver";private static String url = "jdbc:mysql://localhost:3308/zoo";private static String user = "root";private static String password = "123";private static Connection conn = null;private static PreparedStatement prepstmt = null;private static ResultSet rs = null;public static void main(String[] args) {try {Class.forName(driver);//conn = DriverManager.getConnection(url,user,password);prepstmt = conn.prepareStatement("INSERT INTO animal VALUES(?,?,?)");prepstmt.setInt(1, 1);prepstmt.setString(2, "pig");prepstmt.setInt(3, 10);prepstmt.execute();System.out.println("finish!");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {try {if (rs != null) {rs.close();}if (prepstmt != null) {prepstmt.close();}if (conn != null) {conn.close();}} catch (Exception e2) {// TODO: handle exception}}}}

MySQL: Transaction

For example, if an account A transfers money to an account B, two SQL statements are required, one for updating the money in the account A and the other for updating the money in the account B. In the past two days, the update statement must be successfully executed at the same time, or the statement fails at the same time. No intermediate condition may occur, but one successful statement cannot be executed.

These two statements constitute transaction.

Conn. setAutoCommit (false); // set automatic submission to false first.
Stmt = conn. createStatement ();
Stmt. addBatch ("insert into animal values (51, '000000', 3 )");
Stmt. addBatch ("insert into animal values (52, '123', 4 )");
Stmt. addBatch ("insert into animal values (53, '000000', 5 )");
Stmt.exe cuteBatch (); // batch processing of three statements
Conn. commit ();
Conn. setAutoCommit (true); // restore automatically submit by default true

Set rollback

Catch (SQLException e ){
E. printStackTrace ();
Try {
If (conn! = Null ){
Conn. rollback ();
Conn. setAutoCommit (true );
}
}

The following code:

package myjdbc;import java.sql.*;public class TestTransaction {public static void main(String[] args) {Connection conn = null;Statement stmt = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/zoo", "root", "123");conn.setAutoCommit(false);stmt = conn.createStatement();stmt.addBatch("insert into animal values (51, '500', 3)");stmt.addBatch("insert into animal values (52, '500', 4)");stmt.addBatch("insert into animal values (53, '500', 5)");stmt.executeBatch();conn.commit();conn.setAutoCommit(true);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();try {if (conn != null) {conn.rollback();conn.setAutoCommit(true);}} catch (SQLException e1) {e1.printStackTrace();}} finally {try {if (stmt != null)stmt.close();if (conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}} 

Mysql:

Words in the end

In JDBC applications, if you are already a relatively level developer, you should always replace Statement with PreparedStatement. That is to say, do not use Statement at any time.
For the following reasons:
I. code readability and maintainability.
Although the use of PreparedStatement instead of Statement will lead to several more lines of code, such code is much higher than the use of Statement code in terms of readability and maintainability:

Stmt.exe cuteUpdate ("insert into tb_name (col1, col2, col2, col4) values ('" + var1 + "', '" + var2 + "'," + var3 + ", '"+ var4 + "')");

Perstmt = con. prepareStatement ("insert into tb_name (col1, col2, col2, col4) values (?,?,?,?) ");
Perstmt. setString (1, var1 );
Perstmt. setString (2, var2 );
Perstmt. setString (3, var3 );
Perstmt. setString (4, var4 );
Perstmt.exe cuteUpdate ();

2. PreparedStatement to maximize performance.
Each database tries its best to optimize the performance of precompiled statements. because pre-compiled statements may be called repeatedly. therefore, the Execution Code of statements compiled by the DB compiler is cached. Therefore, if the statements are the same pre-compiled statements in the next call, compilation is not required, you only need to pass the parameters directly into the compiled statement Execution Code (equivalent to a number of Han) and the code will be executed. this does not mean that only the pre-compiled statements executed multiple times in a Connection are cached. Instead, if the pre-compiled statement syntax matches the cache. at any time, you can directly execute it without re-compiling. in statement statements, even if they are the same operation, the chance of matching the entire statement is very small because the data of each operation is different, and it is almost impossible to match the statement. for example:
Insert into tb_name (col1, col2) values ('11', '22 ');
Insert into tb_name (col1, col2) values ('11', '23 ');
Even if the operation is the same, but because the data content is different, the entire statement itself cannot match and has no significance for cache statements. The fact is that no database will cache the code executed after compilation of common statements.

3. Greatly improved security.

Transaction is very important when data needs to be synchronized. It ensures that the execution of several batch processing statements is successful at the same time.

2014.1.9