JDBC Advanced Features (ii) Transactions, concurrency control, and rowsets

First, the business

A transaction is a unit of work that contains a set of data manipulation commands, such as adding, deleting, modifying, and committing to the system as a whole, either executing successfully or recovering all

Using Transactions in JDBC

1) Con.setautocommit (false), cancel auto-commit

2) perform one or more operations on the database (one or more SQL statements)

3) Con.commit (), Commit the transaction (multiple operations on the second part of the above as a whole commit execution)

4) If an operation fails, rollback all operations via Con.rollback () (undo the above operation and restore the data to the pre-execution state)

Transaction processing relies on the underlying database implementation, and different drivers may have different levels of support for transaction processing

Grammar
Connection con = drivermanger.getconnection (urlstring);
Con.setautocommit (FALSE);
Statement stm = Con.createstatement ();
Stm.executeupdate (sqlString);
Con.transactionendmethod; Con.commit () or con.rollback ();
Example:
try{Con.setautocommit (FALSE);
STM = Con.createstatement ();
Stm.executeupdate (
"INSERT into T_user (ID, name, password) VALUES (1, ' admin ', ' admin ')");
Con.commit (); ...... Data manipulation code ... con.commit ();}
catch (SQLException e) {
try {con.rollback ();} catch (Exception e) {}
}

Second, the database concurrency

Database concurrency is where different transactions perform operations on the same part of the data

Transaction T1 and T2 (or multiple transactions) to change the same part of the data, delete, check operation: T1 to modify and T2 query.

Problems caused by database concurrency

1) Read dirty data: Transaction T1 Modify a data, and write it back to the database, transaction T2 read the same data, T1 for some reason was revoked, then T1 modified data Recovery original value, T2 read to the data is inconsistent with the data in the database, we call T2 read the data is "dirty" data, that is, incorrect data.

2) Non-repeatable READ: Non-repeatable read means that after the transaction T1 reads the data, the transaction T2 perform the update operation so that T1 cannot reproduce the previous read result.

3) Phantom read: Transaction T1 after reading some data records from the database according to certain conditions, the transaction T2 inserts some records, and when T1 reads the data again by the same conditions, it finds some more records.

concurrency control

1) The concurrency control of the database is to dispatch the concurrency operation in the correct way, so that the execution of one user's transaction is not disturbed by other transactions, so as to avoid inconsistent data.

In JDBC, the transaction isolation level can be set through connection to control concurrency

2) The Transaction isolation level is the degree to which the transaction is being disturbed, the different isolation levels correspond to different degrees of interference, the higher the isolation level, the better the data consistency, but the worse the concurrency, the less efficient.

Ways to view and set isolation levels in connection
Gettransactionisolation (): View Isolation Level
settransactionisolation (int level): setting Isolation Levels
Transaction isolation level in connection (constant in connection interface)
Transaction_none: Transaction is not supported at this level
Transaction_read_uncommitted: This level allows a transaction to read data that has not been changed by another transaction. Dirty reads, non-repeatable reads, and Phantom reads are allowed.
Transaction_read_committed: This level requires that a transaction can only be read after all other transactions have been changed. You can prevent dirty reads, but non-repeatable reads and phantom reads can occur.
Transaction_repeatable_read: This level requires that a transaction can be read only after all other transactions have been changed, and that non-repeatable reads are forbidden. That is, you can prevent dirty reads and non-repeatable reads, but Phantom reads can occur.
Transaction_serializable: This level prevents dirty reads, non-repeatable reads, and Phantom reads, and transactions can only be performed one at a time, not concurrently.

3) syntax
Con.settransactionisolation (Connection.isolationlevel);
Example:
Connection con = drivermanager.getconnection
("Jdbc:mysql://localhost:3306/test", "root", "root");
Con.settransactionisolation (connection.transaction_read_committed);

Three, the rowset

1) A rowset is one or more rows of data retrieved from a tabular data source:

Similar to result set (ResultSet) (the rowset interface inherits the ResultSet interface)

However, when using the result set, the connection to the database cannot be broken, and the rowset can exist in the case of closing the connection, and the rowset is typically used in the case of a closed connection, only for some special operations

When needed to establish a connection

2) data sources in the rowset:

Data retrieved from a database using the JDBC driver
Data obtained from other data sources, such as file data

The advantages of the rowset (row set):

1) Can disconnect database connection operation data

2) can be passed between different components in a distributed system

3) The default can be updated, scrollable, serializable, can be conveniently transmitted across the network

4) The ability to easily convert data between rowset and JavaBean objects

A row of data in a rowset can be encapsulated as a JavaBean object

JavaBean is a class that has several private properties in it, and then has public get and set methods associated with the property. In this way, we can store a row of data in a JavaBean object

Rowset-related interfaces:

Javax.sql.RowSet: Parent interface for all rowsets
Java.sql.rowset.CachedRowSet: A data row container that can cache rows of data in memory and do not need to connect to the data source when manipulating data. You can modify the data in the CachedRowSet object, which can then be updated to the database. At the same time, it is also a javabean component that can be scrolled, updatable, serializable.
Java.sql.rowset.JDBCRowSet: Data row container, scrollable, updatable. Always connect to the database.
Java.sql.rowset.WebRowSet: The rowset data that is cached can be saved as an XML file.
Java.sql.rowset.JoinRowSet: Data row container, which is derived from the rowset object that forms the SQL join relationship, and both connected and disconnected rowset objects can be part of a join.
Java.sql.rowset.FilteredRowSet: A data row container that allows you to conditionally filter data.

Row Set padding:

1) traditional JDBC mode
Class.forName ("Com.mysql.jdbc.Driver");  
String  connectionurl = 
"Jdbc:mysql://localhost : 3306/test?user=root&password=root ";  
Connection  connection = drivermanager.getconnection ( Connectionurl);
Statement  statement = Connection.createstatement ();
ResultSet  rs = statement.executequery (sql);
Cachedrowsetimpl  rowset = new Cachedrowsetimpl ();
Rowset.populate (RS);  
Rs.close (); Statement.close (); Connection.close ();

2) Set the rowset properties to connect to the database and retrieve the data
CACHEDROWSETIMPL rowset = new Cachedrowsetimpl ();
Rowset.seturl ("Jdbc:mysql://127.0.0.1:3306/test");
Rowset.setusername ("root");
Rowset.setpassword ("test");
Rowset.setcommand ("SELECT * from student");
Rowset.execute ();
Other ways to fill:
Xml
Excel
......

--------------------------------------------------------------------------------------------------------------- ----------------

Use of Rowset:

1 The following rowset class uses the default implementation class for Java
CACHEDROWSETIMPL rowset = new Cachedrowsetimpl ();  
// Cachedrowsetimpl is Sun-defined Cachedrow interface default implementation class
Statement Statement = Connection.createstatement ();
ResultSet  rs = Statement.executequery ("Select  *  from  table1");
Rowset.populate (RS);        //populate rowset
Rs.close (); Statement.close (); Connection.close ();    //Close Connection
//display cachedrow data, using methods inherited from result set
while (Rowset.next ()) {
System.out.print (rowset.getstring (1) + ":"); 　　 System.out.println (rowset.getstring ("CompanyName"));
}

2,//update cachedrow data (Conn.setautocommit (false);)
Crs.last ();
Crs.updateshort (3, 58);
Crs.updateint (4, 150000);
Crs.updaterow (); Update a row set
Crs.acceptchanges (conn); Update Database
Precautions:

using rowsets to modify data is independent of how the data is populated in the rowset. However, to ensure that the AcceptChanges () method executes with a connection object available, if there is a connection object available in the rowset that can call acceptChanges (), you need to call acceptChanges if there are no connection objects available in the rowset ( Connection) method.

3,/ /Add Data
Crs.settablename ("student");    //Add data must be specified
Crs.movetoinsertrow ();         & nbsp        //Identify the position of the pointer
Crs.updateint ("id"),            //When adding data, the primary key must specify the Br>crs.updatestring ("name", "Shakespeare");
Crs.updateshort ("age", 58);
Crs.insertrow ()                             &NB Sp  //UPDATE Rowset
Crs.movetocurrentrow ();            //get the pointer back to the location identified
Crs.acceptchanges ( conn);                //Update database

//delete data
Crs.first ();
Crs.deleterow ();                            //DELETE rowset data
CRS.A Cceptchanges (conn);              //Update database

4,//page 1: Populating rowsets with result sets
rs = Stm.executequery ("SELECT * from student");
Crs.setpagesize (4); Set the number of rows per page
Crs.populate (RS, 10); Populate the rowset with 4 rows of data starting from line tenth of the result set
...
Crs.nextpage (); Gets the subsequent page data if there is data returned true
...

Attention:

At this point resultset,statement (preparedstatement), connection cannot be closed, otherwise the rowset cannot get subsequent page data

5.//Page 2: Rowset establish connection read data from database
Cachedrowsetimpl crs= new Cachedrowsetimpl ();
Crs.seturl ("Jdbc:mysql://127.0.0.1:3306/test");
Crs.setusername ("root");
Crs.setpassword ("root");
Crs.setcommand ("SELECT * from student");
Crs.setpagesize (4); Number of rows per page, must be set before execute () method is executed, otherwise invalid
Crs.execute ();
......
Crs.nextpage (); Get the next page of data, regardless of the result set, Connection object
......

The following are the relevant test codes:

Package Com;import Java.sql.sqlexception;import Com.sun.rowset.cachedrowsetimpl;public class JDBC4 {public static void Main (string[] args) {try {Cachedrowsetimpl rowset=new cachedrowsetimpl (); Rowset.seturl ("jdbc:mysql://localhost:3308 /test "); Rowset.setusername (" root "); Rowset.setpassword (" 123456 "); Rowset.setcommand (" Select*from score; "); Rowset.execute ();//output Rowset data System.out.println ("Id\tchinese\tenglish\thistory"); while (Rowset.next ()) { System.out.print (Rowset.getint ("id") + "\ T"); System.out.print (Rowset.getint ("Chinese") + "\ T"); System.out.print (Rowset.getint ("中文版") + "\ T"); System.out.println (Rowset.getint ("History"));}} catch (SQLException e) {e.printstacktrace ();}}}

Package Com;import Java.sql.sqlexception;import Com.sun.rowset.cachedrowsetimpl;public class JDBC6 {public static void Main (string[] args) {try {Cachedrowsetimpl rowset=new cachedrowsetimpl (); Rowset.seturl ("jdbc:mysql://localhost:3308 /test "); Rowset.setusername (" root "); Rowset.setpassword (" 123456 "); Rowset.setcommand (" Select*from score; "); /Set the number of data bars displayed per page rowset.setpagesize (3); Rowset.execute (); int i=2; System.out.println ("1th page"); System.out.println ("Id\tchinese\tenglish\thistory"), while (Rowset.next ()) {System.out.print (Rowset.getint ("id") + "\ t"); System.out.print (Rowset.getint ("Chinese") + "\ T"); System.out.print (Rowset.getint ("中文版") + "\ T"); System.out.println (Rowset.getint ("History")); while (Rowset.nextpage ()) {System.out.println ("page" +i+ "); i++; System.out.println ("Id\tchinese\tenglish\thistory"), while (Rowset.next ()) {System.out.print (Rowset.getint ("id") + "\ t"); System.out.print (Rowset.getint ("Chinese") + "\ T"); System.out.print (Rowset.getint ("中文版") + "\ T"); System.out.println (Rowset.getint ("History"));}}} catch (SQLException e) {e.printstacktrace ();}}} 

Package Com;import Java.io.ioexception;import Java.sql.connection;import java.sql.preparedstatement;import Java.sql.resultset;import java.sql.sqlexception;import com.sun.rowset.cachedrowsetimpl;import util. Dbutil;public class JDBC3 {public static void main (string[] args) {Connection conn=null; PreparedStatement Pst=null; ResultSet rs=null;dbutil util=new dbutil (); String sql= "Select*from score"; try {conn=util.getconn ();p st=conn.preparestatement (SQL);// You must set non-autocommit conn.setautocommit (false); Rs=pst.executequery ();//CREATE Rowset instance Cachedrowsetimpl rowset=new Cachedrowsetimpl () ;//Fill rowset.populate (RS); Rs.close ();p st.close (); Rowset.absolute (5);//rowset.updateint ("id", 5); rowset.updateint ("中文版", 55);//update Rowset.updaterow ();//Submit rowset.acceptchanges (conn);//output result set, close connection Conn.close ();// Output rowset Data System.out.println ("Id\tchinese\tenglish\thistory"), while (Rowset.next ()) {System.out.print (Rowset.getint ( "id") + "\ T"); System.out.print (Rowset.getint ("Chinese") + "\ T"); System.out.print (Rowset.getint ("中文版") + "\ T"); System.oUt.println (Rowset.getint ("History"));}} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} catch ( SQLException e) {e.printstacktrace ();}}}

JDBC Advanced Features (ii) Transactions, concurrency control, and rowsets