JDBC Advanced Features (i) result set, batch update

First, the advanced features of ResultSet

1 scrollable ResultSet

1) roll forward and backward

Scrolling properties

In the initial JDBC version, ResultSet can only scroll forward

In subsequent versions of JDBC, ResultSet can scroll forward or backward by default

Lazy scrolling: The recordset can be scrolled back and forth, not affected by database data updates

Sensitive scrolling: Record sets can be scrolled back and forth, affected by database data updates

Set by result set type

Con.createstatement ()

Con.createstatement (Result set type, result set concurrency type)

Con.createstatement (Result set type, result set concurrency type, result set is persisted)

Result set attribute settings:

Grammar
Statement stm = con.createstatement (int resultsettype, int resultsetconcurrency,int resultsetholdability);
Example:
The default is to scroll back and forth, Recordset type: Affected by database data modification,
Result set concurrency type: concurrency types that can be updated
Result set can be persisted: The result set is closed at the time of the current transaction commit
Statement stm = con. Createstatement (
Resultset.type_scroll_sensitive, Resultset.concur_updatable,
Resultset.close_cursors_at_commit);
ResultSet rs = stm.executequery ("SELECT * from student");

ResultSet result set type, result set concurrency type, result set-preserving properties are equally applicable for preparedstatament and CallableStatement
Con.preparestatement (SQL, result set type, result set concurrency type)
Con.preparestatement (SQL, result set type, result set concurrency type, result set is persisted)

Con.preparecall (SQL, result set type, result set concurrency type)
Con.preparecall (SQL, result set type, result set concurrency type, result set is persisted)

2 resultset positioning

1) Let the cursor point to a row of data
2) result set supports relative positioning and absolute positioning

Result set positioning is to make a cursor point to a row of data in a recordset

ResultSet supports absolute and relative positioning

Cursor positioning using the absolute () and next () methods

Example

Sql= "Select id,name,password,age from Person";
Rs=stmt.executequery (SQL);
while (Rs.next ()) {...} Move down before the first line
...
Rs.absolute (5); Point to line fifth, no matter where you point.
...

Method of locating the result set:

3 Updatable ResultSet

1) Result set holds query results, default is not updatable
2) parameters can be set when the statement object is obtained through connection to determine whether the data can be updated through the result set

Result set holds query results, default is not updatable

You can set the result set concurrency type parameter to determine whether the data can be updated through the result set when you get the statement object through connection

If the result set concurrency type is set to Concur_updatable, you can modify the data through the result set

Once set up, you can update, delete, and insert data in the result set

When modifying data using a result set, first position the cursor position and then perform an update, delete, or insert data

Update line:

Grammar
Resultsetvar.updatexxx (columnindex, value);
Resultsetvar.updatexxx ("ColumnName", value);
Resultsetvar.updaterow (); Two methods to work with
Cancelrowupdates (); To cancel an update, you must use it before Updaterow ()
Example
Sql= "Select id,name,password,age from Person";
Rs=stmt.executequery (SQL);
Rs.last (); Positioning
Rs.updatestring ("Name", "Oracle");
Rs.updatestring (3, "Oracle"); Updatexxx () method
Rs.updateint ("Age", 21); These update methods do not update the underlying database
Rs.updaterow (); Update the underlying database

To delete a row:

Grammar
Resultsetvar.deleterow ();
Example
Sql= "Select id,name,password,age from Person";
Rs=stmt.executequery (SQL);
Rs.absolute (10); Position the pointer to the corresponding row
Rs.deleterow (); Delete the current row data while deleting the underlying database data

Insert New Line:

Grammar
Resultsetvar.movetoinsertrow ();
Resultsetvar.updatexxx (columnindex, value);
Resultsetvar.updatexxx ("ColumnName", value);
Resultsetvar.insertrow ();
Example
Rs.movetoinsertrow (); Remember the pointer position
Rs.updatestring (2, "Rose");
Rs.updatestring ("Password", "Rose");
Rs.updateint ("Age", 11);
Rs.insertrow (); Inserting data, updating the underlying database
Rs.movetocurrentrow (); Move the pointer to the location of the pointer you remember

Requirements to update the result set:

Not all result sets can be updated

The updatable result set must meet the following criteria

The query references only one data table
Query does not contain any join operations
The primary key is included in the query result
Query results All columns cannot be empty, no default values

Second, batch update

1 Statement Batch Update

Grammar:
Statement stm = Con.createstatement ();
Stm.addbatch (sqlString); Stm.addbatch (sqlString);
Stm.executebatch ();
Example:
Con.setautocommit (FALSE); Set transaction non-auto commit
Statement stm = Con.createstatement ();
Stm.addbatch ("INSERT into T_user (ID, name, password) values (one, ' Rose ', ' Rose ')");
Stm.addbatch ("INSERT into T_user (ID, name, password) VALUES (' Mary ', ' Mary ') ');
Int[] results = Stm.executebatch (); Submit execution
Con.commit (); Commit a transaction to make the change persistent

Batch update be sure to set the transaction commit to non-auto-commit

Con.setautocommit (FALSE);
Con.commit ();
Statement's ExecuteBatch () method submits multiple commands to the database execution, returning an array of the number of rows per command update
If some commands in the bulk update do not execute correctly, the Batchupdateexception exception is thrown
The Batchupdateexception getupdatecounts () method can return an array of update counts for each UPDATE statement that was successfully executed in the bulk update before this exception occurred

2 PreparedStatement Batch Update

PreparedStatement precompiled SQL statements, executing after passing in parameters
PreparedStatement batch feeds incoming parameters into the database to execute corresponding SQL statements

Grammar:
PreparedStatement pstm = con.preparestatement (sqlString);
Pstm.setxxx (..); Pstm.addbatch ();
Pstm.setxxx (..); Pstm.addbatch ();
Pstm.excutebatch ();
Example
Con.setautocommit (FALSE);
PreparedStatement pstm = con.preparestatement ("INSERT into T_user (name) VALUES (?)");
Pstm.setstring (1, "Rose"); Pstm.addbatch ();
Pstm.setstring (1, "John"); Pstm.addbatch ();
Pstm.setstring (1, "Mary"); Pstm.addbatch ();
Int[] counts = Pstm.excutebatch (); Con.commit ();

3 callablestatement Batch Update

CallableStatement processing the database storage process

CallableStatement interface Inherits PreparedStatement interface

The related batch method is inherited from the PreparedStatement interface.

CallableStatement batch processing is the same as PreparedStatement batch mode

Batches incoming parameters into the database to execute the corresponding stored procedure

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

JDBC Advanced Features (i) result set, batch update