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

First, the business

A transaction is a unit of work that includes a set of data manipulation commands, such as adding, deleting, altering, and so on, which are run as a whole to the system, either running successfully, or all recovering

Using Transactions in JDBC

1) Con.setautocommit (false), cancel your own unsolicited submission

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

3) Con.commit (). Commit a transaction (multiple operations on the second part of the above are run as an overall commit)

4) Assume that an operation failed. Rollback all operations via Con.rollback () (undo the above operation and restore the data to a pre-run 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 ();
Demo Sample:
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 the operation of different transactions on the same part of the data

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

Problems caused by database concurrency easy

1) Read dirty data: Transaction T1 changes a certain data. and writes it back to the database, after the transaction T2 reads the same data. T1 was revoked for some reason. At this point T1 has changed the data recovery original value. T2 Read The data is inconsistent with the data in the database, we call T2 read the data is "dirty" data, that is, the wrong data.

2) do not read repeatedly: non-repeatable read refers to the transaction T1 read the data, the transaction T2 run the update operation, so that T1 cannot reproduce the previous read results.

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 again reads the data in the same condition, 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 operation of one user's transaction is not disturbed by other transactions, so as to avoid inconsistent data.

In JDBC. Control concurrency with connection ability to set TRANSACTION isolation levels

2) The Transaction isolation level is the degree to which the transaction is disturbed at runtime, and the level of interference at different isolation levels is higher. The better the consistency of data, the worse the concurrency. 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 agrees that a transaction reads data that has not been changed by another transaction.

Consent to the occurrence of dirty reads, non-repetition and phantom reading.

Transaction_read_committed: This level requires that a transaction can only be read by other firms after they have changed their abilities.

Can prevent dirty reads, but can not be repeated read and phantom read may occur.
Transaction_repeatable_read: This level requires that a transaction can only be read by other firms that have changed their abilities and are forbidden to be read again.

That is, it is possible to prevent dirty reading and non-repetition, but phantom reading can occur.
Transaction_serializable: This level prevents dirty reads, non-repeatable reads, and Phantom reads, and the transaction can only run one after the other. and cannot run concurrently.

3) syntax
Con.settransactionisolation (Connection.isolationlevel);
Demo Sample:
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, the connection to the database cannot be broken when using the result set. The rowset is able to exist if the connection is closed, and the rowset is typically used in the case of a closed connection. There are only a few special operations in place

When you need 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) Ability to disconnect database connection operation data

2) ability to transfer between different components in a distributed system

3) Default updatable, scrollable, serializable, easy to transfer between networks

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. There are several private properties in the class, followed by the 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. You do not need to connect to the data source when you operate on the data. The ability to change the data in the CachedRowSet object, which can then be updated to the database.

At the same time, it is also a javabean component. Scrollable, updatable, serializable.
Java.sql.rowset.JDBCRowSet: Data row container. can be scrolled and updatable.

Always connect to the database.
Java.sql.rowset.WebRowSet: The set of rows that are cached, which 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. Rowset objects that have connections and are not connected can be part of a join.

Java.sql.rowset.FilteredRowSet: A data row container that enables conditional filtering of data.

Row Set padding:

1) Traditional JDBC approach
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 filling methods:
Xml
Excel
......

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

Use of Rowset:

1 , the following rowset class uses Java's default implementation class
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. Use the method inherited from the 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 rowset churn data is independent of how the data is populated in the rowset. However, make sure that the AcceptChanges () method runs with a connection object available, assuming that there are available connection objects in the rowset that can call acceptChanges (). If there are no connection objects available in the rowset, you need to call the AcceptChanges (Connection) method.

When you use a rowset to add, change, or delete data, you must ensure that the transaction is committed as a non-self-commit (the AcceptChanges () method calls the Commit () method).

3,//Add data
Crs.settablename ("Student"); The join data must be specified
Crs.movetoinsertrow (); Identifies the position of the pointer
Crs.updateint ("id", 33); The primary key must be specified when adding data
Crs.updatestring ("name", "Shakespeare");
Crs.updateshort ("Age", 58);
Crs.insertrow (); Update a row set
Crs.movetocurrentrow (); Get the pointer back to the location of the logo
Crs.acceptchanges (conn); Update Database

Delete data
Crs.first ();
Crs.deleterow (); Delete rowset Data
Crs.acceptchanges (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 (); Get the page data, assuming the data returns true
...

Attention:

At this point resultset,statement (preparedstatement), connection cannot be closed. Otherwise, the rowset cannot get the 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. Be sure to set before the Execute () method runs. otherwise invalid
Crs.execute ();
......
Crs.nextpage (); Get the next page of data. Unrelated to result set, Connection object
......

Here is the relevant test code:

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 a non-self-commit conn.setautocommit (false), rs=pst.executequery ();//Create a 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