JDBC "Transaction, metadata, transformation JDBC Tool class"

1. Business

All update operations performed by a session either succeed together or fail together

For example:A to B transfer, transfer this process if there is a problem, the transaction can let the data back to the original "a account of the money has not changed, B account of the money has not changed."

Case Description:

 /*   * we To simulate the scenario of A to B account transfer   * A and B accounts have 1000 dollars, now I let a account to B account 500 dollars   *  
    
      * */ 
     //jdbc the default is to turn off transactions, let's see what's wrong with closing transactions to operate a transfer operation             
     //a account minus 500 blocks             String sql = 
      "UPDATE a SET money=money-500" ; PreparedStatement = connection. 
     preparestatement             (SQL); PreparedStatement. 
     executeupdate             ();            
     //b account for 500 more blocks             String sql2 = 
      "UPDATE b SET money=money+500" ; PreparedStatement = connection. 
     preparestatement             (SQL2); PreparedStatement. 
     executeupdate  ();
    

From the above, we can indeed find A to B transfer, success. But what if there is a problem with the transfer of A to B? let's simulate this.

            //A账户减去500块            "UPDATE a SET money=money-500 ";            preparedStatement = connection.prepareStatement(sql);            preparedStatement.executeUpdate();                        //这里模拟出现问题            int30;            "UPDATE b SET money=money+500";            preparedStatement = connection.prepareStatement(sql2);            preparedStatement.executeUpdate();

Obviously, the above code throws an exception , so let's look at the data again. a account has less than 500 dollars, B account money does not increase . This is obviously unreasonable.

We can solve the problems above through transaction.

            //Open transaction, the operation of the data will not take effect immediately. Connection.Setautocommit(false);//a account minus 500 bucks .String sql ="UPDATE a SET money=money-500"; PreparedStatement = connection.preparestatement(SQL); PreparedStatement.executeupdate();//problems occurred during the transfer process            intA =3/0;500 more//b accountsString SQL2 ="UPDATE b SET money=money+500"; PreparedStatement = connection.preparestatement(SQL2); PreparedStatement.executeupdate();//If the program can execute here without throwing an exception, we will submit the dataConnection.Commit();//Close Transaction "Autocommit"Connection.Setautocommit(true); }Catch(SQLException e) {Try{//If an exception occurs, it will come in here and we'll roll back the transaction "turn the data into the same"Connection.rollback();//Close Transaction "Autocommit"Connection.Setautocommit(true); }Catch(SQLException E1) {E1.Printstacktrace(); }

The above program also throws the exception, a account money is not reduced, B account of the money has not increased.

Note: When connection encounters an unhandled SqlException, the system exits abnormally and the transaction is automatically rolled back, but if the program catches an exception, the transaction needs to be explicitly rolled back in the catch.

Savapoint

We can also use SavePoint to set intermediate points. If something goes wrong somewhere, we set the middle point and roll it back to the error.

Application scenario: Now we're going to calculate a math problem, and we'll find the wrong number after the calculation. The previous operations are correct, we can not start again to calculate the "direct rollback", the best way is to ensure that the previous calculation of the case, set a savepoint. Recalculate from the point of savepoint.

Note:SavePoint does not end the current transaction, and normal commit and rollback will end the current transaction

Isolation level of a transaction

The database defines 4 isolation levels:

1. Serializable "can avoid dirty reading, non-repeatable reading, false reading"
2. REPEATABLE read "can avoid dirty reading, non-repeatable read"
3. Read Committed "can avoid dirty reading"
4. Read uncommitted "lowest level, nothing to avoid"

Corresponds to 4 constants in the connection class, respectively

1. transaction_read_uncommitted
2. transaction_read_committed
3. Transaction_repeatable_read
4. Transaction_serializable

Dirty reads: one transaction reads uncommitted data to another transaction

Example: A to B transfer,a executed the transfer statement, but a has not committed transactions, B read the data, found that their accounts more money ! B and a said, I have received the money. A roll back the transaction "rollback", and then B again look at the account of the money, found that the money is not much.

Non-repeatable READ: One transaction reads data that has been committed by another transaction, which means that a transaction can see changes made by other firms

Note:a query the database to get the data, B to modify the database data, resulting in a multiple query database results are not the same "harm: a each query results are affected by B, then a query out of the information will be no meaning."

Virtual Read (Phantom Read): refers to the data that is inserted into another transaction within a transaction, resulting in inconsistent read and backward reads.

Note: similar to non-repeatable reads, but virtual read (phantom Read) reads the inserted data of other transactions, resulting in inconsistent read and backward reads

Simple summary: dirty reading is not tolerated, non-repeatable reading and virtual reading in certain circumstances is possible "do statistics certainly not."

2. Meta-data What is metadata

Metadata is actually database, table, column definition information

Why do we need to use meta data

Even if we write a simple tool class, our code is very redundant. for additions and deletions, only SQL and parameters are different , why don't we extract the same code into a single method? For queries , the result sets queried by different entities are not the same . We want to use metadata to get information about the result set in order to manipulate the result set .

• Parametermetadata--meta-data for parameters
• ResultSetMetaData--Metadata for the result set
• DatabaseMetaData--Metadata for the database

3. Transforming the JDBC Tool class

Problem: Our database of additions and deletions are connected to the database, close the resources, get preparedsteatment objects, get connection objects such operations, so the code repetition rate is very high , so we want to enhance the tool class

Delete and change

    //We found that only the SQL statement and the parameters passed in are not known, so let the person calling the method pass in .        //Because the parameters passed in are of various types and the number is indeterminate, use object[]         Public Static void Update(String sql, object[] objects) {Connection Connection =NULL; PreparedStatement PreparedStatement =NULL; ResultSet ResultSet =NULL;Try{connection =getconnection(); PreparedStatement = connection.preparestatement(SQL);set the value of the SQL placeholder according to the parameters passed in             for(inti =0; I < objects.length; i++) {PreparedStatement.SetObject(i +1, Objects[i]); }//Execute SQL statementPreparedStatement.executeupdate(); }Catch(Exception e) {e.Printstacktrace();

Inquire

    /*1: For query statements, we do not know what to do with the result set "is often used to encapsulate data as a bean object, encapsulated as a list collection"2: We can define an interface that allows the caller to pass in the implementation class of the interface3: The method by which the interface is called is the method that the caller passes in to implement the class. "Policy Mode"    */    //The return value of this method is any type, so it is defined as object.      Public StaticObjectQuery(String sql, object[] objects, Resultsethandler rsh) {Connection Connection =NULL; PreparedStatement PreparedStatement =NULL; ResultSet ResultSet =NULL;Try{connection =getconnection(); PreparedStatement = connection.preparestatement(SQL);set the value of the SQL placeholder according to the parameters passed in            if(Objects! =NULL) { for(inti =0; I < objects.length; i++) {PreparedStatement.SetObject(i +1, Objects[i]); }} ResultSet = PreparedStatement.ExecuteQuery();//Call the caller to pass in a method that implements the class, manipulating the result set            returnRsh.Hanlder(ResultSet); }

Interface:

    /*    * 定义对结果集操作的接口，调用者想要对结果集进行什么操作，只要实现这个接口即可    * */    publicinterface ResultSetHandler {         hanlder(ResultSet resultSet);        }

Implementation class:

//Interface implementation class, encapsulating the result set as a Bean object Public classBeanhandlerImplementsResultsethandler {//To encapsulate a bean object, first know what the bean is and this is what the caller is passing in.     PrivateClass Clazz; Public Beanhandler(Class clazz) { This.Clazz= Clazz; }@Override     PublicObjectHanlder(ResultSet ResultSet) {Try{//Create an instantiation of a passed-in objectObject bean = Clazz.newinstance();if(ResultSet.Next()) {//Get result set meta dataResultSetMetaData ResultSetMetaData = ResultSet.GetMetaData(); for(inti =0; I < ResultSetMetaData.getColumnCount(); i++) {//Gets the column name to each columnString columnName = ResultSetMetaData.getColumnName(I+1);//Get data to each columnString columndata = ResultSet.getString(I+1);//Set bean propertiesField field = Clazz.Getdeclaredfield(ColumnName); Field.setaccessible(true); Field.Set(Bean,columndata); }//Return Bean Object                returnBean }

The "policy mode" is simple to understand:

• We do not know what the caller wants to do with the result set, so the caller passes the object of the action that they want to do.
• All we need to do is encapsulate the result set with the object passed over .
  • As for what object the caller will pass, what method the object implements. We can use interfaces to regulate
  To my own understanding, the strategy pattern is when we use other people's APIs, when we can use anonymous internal classes. The other person is using the strategy mode.

If the article is wrong, welcome to correct, we communicate with each other. Students who are accustomed to reading technical articles can pay attention to the public number: Java3y

JDBC "Transaction, metadata, transformation JDBC Tool class"