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 number, ResultSet can only scroll forward

In the JDBC maybe version number, 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);
Demo Sample:
The default is to scroll back and forth. Recordset type: Affected by database data churn.
Result set Concurrency type: concurrency type 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 the result set type. The result set concurrency type. Result set preparedstatament and CallableStatement are equally applicable
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

Demo sample

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

Method of locating the result set:

3 Updatable ResultSet

1) Result set holds query results, default is not updatable
2) can set the parameters 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 when you get the statement object through connection to determine whether the data can be updated through the result set

Assuming that the result set concurrency type is set to Concur_updatable, you can change the data through the result set

After setting. Ability to update, delete, and insert data in the result set

When you use a result set to change data, position the cursor position and then run update, delete, or insert data

Update line:

Grammar
Resultsetvar.updatexxx (columnindex, value);
Resultsetvar.updatexxx ("ColumnName", value);
Resultsetvar.updaterow (); Two methods to work with
Cancelrowupdates (); Cancels the update. Must be used before Updaterow ()
Demo sample
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 ();
Demo sample
Sql= "Select id,name,password,age from Person";
Rs=stmt.executequery (SQL);
Rs.absolute (10); Position the pointer to the appropriate row
Rs.deleterow (); Deletes the current row data. Delete the underlying database data at the same time

Insert New Line:

Grammar
Resultsetvar.movetoinsertrow ();
Resultsetvar.updatexxx (columnindex, value);
Resultsetvar.updatexxx ("ColumnName", value);
Resultsetvar.insertrow ();
Demo sample
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 only refers to a single data table
Query does not include whatever join operation
Include primary key in query results
All columns of the query result cannot be empty. No default value

Second, batch update

1 Statement Batch Update

Grammar:
Statement stm = Con.createstatement ();
Stm.addbatch (sqlString); Stm.addbatch (sqlString);
Stm.executebatch ();
Demo Sample:
Con.setautocommit (FALSE); Set up a transaction not to commit itself voluntarily
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 Run
Con.commit (); Commits the transaction. Make a change a persistent change

Batch update be sure to set the transaction commit as not self-committed

Con.setautocommit (FALSE);
Con.commit ();
The statement ExecuteBatch () method submits multiple commands to the database to run. Returns an array of each command update row count
If some of the commands in the bulk update do not run correctly, a Batchupdateexception exception is thrown
The Batchupdateexception getupdatecounts () method can return an array of update counts for each UPDATE statement that was successfully run in a bulk update before this exception occurred

2 PreparedStatement Batch Update

PreparedStatement precompiled SQL statements. Run after number of incoming references
PreparedStatement batch feeds incoming parameters into the database to run the appropriate SQL statement

Grammar:
PreparedStatement pstm = con.preparestatement (sqlString);
Pstm.setxxx (..); Pstm.addbatch ();
Pstm.setxxx (..); Pstm.addbatch ();
Pstm.excutebatch ();
Demo sample
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 and PreparedStatement batch mode are also

Batches incoming parameters into the database to run the appropriate stored procedures

JDBC Advanced Features (i) result set, batch update