Java database Programming-read-write lob, scrollable, and updatable result sets

1. Read and write LOB

In addition to numbers, strings, and dates, many databases can store large objects, samples, or other data. In SQL, a binary large object is called a blob, and a large object of characters is called a clob.

To read the LOB, the SELECT statement needs to be executed, and then the GetBlob or Getclob method is raised on resultset, so that objects of the BLOB or CLOB type can be obtained. To get binary data from a blob, you can call GetBytes or getInputStream. For example, if you have a table that holds the book cover graphic, you can get an image like this:

PreparedStatement stat = conn.preparestatement ("Select Cover from Bookcovers WHERE isbn=?" ); Stat.set (1= stat.executequery (); if (Result.next ()) {    = Result.getblob (1);     = Imageio.read (Coverblob.getbinarystream ());}

Similarly, if you get the Clob object, you can get the character data in it by calling the Getsubstring or Getcharacterstream method.

To place the LOB in the database, you need to call Createblob or Createclob on the Connection object, and then get an output stream or a writer for that lob, write out the data, and store the object in the database. For example, the following shows how to store an image:

Blob Coverblob = connection.createblob (); int offset = 0="PNG"= conn.preparestatement ("INSERT into Cover VALUES (?,?)" ); Stat.set (1, ISBN); Stat.set (2, Coverblob); Stat.executeupdate ();

2. Scrollable and updatable result sets

For resultset to scroll and update, you must specify the corresponding parameters when you create the statement object using the following method:

Statement stmt = conn.createstatement (type, concurrency);

For PreparedStatement, specify the parameters in the following way:

PreparedStatement pstmt = conn.preparestatement (sql, type, concurrency);

Where type represents the type of resuleset, and concurrency indicates whether the database can be updated with Resuleset.

The values and meanings of type and concurrency are as follows:

resultset class
value	explanation
resultset.type_forward_only	result set cannot scroll (default)
resultset.type_scroll_insensitive

Concurrency value of the ResultSet class
Value	Explain
Resultset.concur_read_only	Result set cannot be used to update the database (default value)
Resultset.concur_updatable	The result set can be used to update the database

There are many types of result sets for JDBC. These result sets have different characteristics to meet a variety of needs. This is an important application in high-performance JDBC data operations. Here is an example of an application:

 PackageLavasoft.common;Importjava.sql.Connection;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement;/*** JDBC Scrollable updatable sense update result set Test * *@authorleizhimin 2009-12-8 20:09:03*/  Public classTestresultset { Public Static voidMain (string[] args) {testscrollresultset ();         Testupdateresultset (); }         /*** Updatable result set update test*/          Public Static voidTestupdateresultset () {Connection conn=dbtoolkit.getconnection (); String SQL= "SELECT * FROM book"; Try{Statement stmt=conn.createstatement (resultset.type_scroll_sensitive, resultset.concur_updatable); ResultSet RS=stmt.executequery (SQL); System.out.println ("---------The original result set--------");  while(Rs.next ()) {System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); } System.out.println ("---------Insert a record--------");                         Rs.first (); //move the cursor over the Insert lineRs.movetoinsertrow (); //Building Row DataRs.updatestring (2, "XXXX"); Rs.updatestring (3, "X"); //Insert a rowRs.insertrow (); System.out.println ("-------------update a record-------------"); Rs.absolute (3); //Building Row DataRs.updatestring (2, "uuuu"); Rs.updatestring (3, "U");                         Rs.updaterow (); System.out.println ("---------Insert the updated result set--------"); RS=stmt.executequery (SQL);  while(Rs.next ()) {System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3));                         } rs.close ();                 Stmt.close (); } Catch(SQLException e) {e.printstacktrace (); } finally{dbtoolkit.closeconnection (conn); }         }         /*** Scrollable result set scrolling test*/          Public Static voidTestscrollresultset () {Connection conn=dbtoolkit.getconnection (); String SQL= "SELECT * FROM book"; Try{Statement stmt=conn.createstatement (resultset.type_scroll_sensitive, resultset.concur_read_only); ResultSet RS=stmt.executequery (SQL);  while(Rs.next ()) {System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); } System.out.println ("------Roll forward Operation-----"); //move the cursor to the previous line of this ResultSet objectrs.previous ();                         Rs.previous (); System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); System.out.println ("------Absolute Positioning-----"); //moves the cursor to the given line number for this ResultSet object. Rs.absolute (3); System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); System.out.println ("------move to the first line-----"); //move the cursor to the first line of this ResultSet object.                         if(Rs.first ()) {System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); } System.out.println ("------move to the last line-----"); //move the cursor to the first line of this ResultSet object.                         if(Rs.last ()) {System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); } System.out.println ("------before moving to the first line-----"); //move the cursor to the beginning of this ResultSet object, just before the first lineRs.beforefirst ();                         Rs.next (); System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); System.out.println ("------moved to the last row after-----"); //move the cursor to the end of this ResultSet object, just after the last line. Rs.afterlast ();                         Rs.previous (); System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3)); System.out.println ("------Move-----relative to the current line"); Rs.relative (-2); System.out.println ("[Line number:" + rs.getrow () + "]\t" + rs.getstring (1) + "\ T" + rs.getstring (2) + "\ T" + rs.getstring (3));                         Rs.close ();                 Stmt.close (); } Catch(SQLException e) {e.printstacktrace (); } finally{dbtoolkit.closeconnection (conn); }         } } 

Console output:

[Line No.: 1] 1AAA A [line number:2] 2BBB b [line number:3] 3CCC C [line number:4] 4DDD D [line number:5] 5Eee E [line number:6] 6FFF F [line number:7] 7GGG G [Line number:8] 8hhh H------Roll Forward Operation-----[line number:7] 7GGG G------Absolute Positioning-----[line number:3] 3CCC C------move to the first row-----[line number:1] 1AAA a------move to the last row-----[line number:8] 8hhh H------before moving to the first line-----[line number:1] 1AAA a------moves to the last row-----[line number:8] 8hhh H------Move-----Relative to the current row[line number:6] 6FFF F---------The original result set--------[line number:1] 1AAA A [line number:2] 2BBB b [line number:3] 3CCC C [line number:4] 4DDD D [line number:5] 5Eee E [line number:6] 6FFF F [line number:7] 7GGG G [Line number:8] 8hhh H---------Insert a record---------------------update a record----------------------insert the updated result set--------[line number:1] 1AAA A [line number:2] 2BBB b [line number:3] 3uuuu U [line number:4] 4DDD D [line number:5] 5Eee E [line number:6] 6FFF F [line number:7] 7GGG G [Line number:8] 8HHH H [Line No.:9] 9xxxx Xprocess finished with exit code0

Storage: Sets whether to close the result set at the time of submission.

Resultset.hold_cursors_over_commit: Result set is also available after commit

Resultset.close_cursors_at_commit: Close result set at commit time

Because these features are more advanced, different database drivers are not the same for this implementation. Therefore, when using the JDBC advanced features, it is best to do a test to ensure the reliability of the program.

When TYPE is set to: Resultset.type_scroll_insensitive or resultset.type_scroll_insensitive, the cursor can be moved, but the position moved is [1,count], Remember not to start from 0, or you will get an error.

Now that you can move, explain a few ways to move:

rs = Statement.executequery (); The cursor points to the position in front of the first line, where the data cannot be retrieved or an error occurs: The result set does not go forward

Rs.next (); The cursor moves down one position and returns true if there is a result set in place, otherwise false

Rs.previous (); The cursor moves up one position and returns true if there is a result set in place, otherwise false

Rs.first (); Where the cursor points to the first row

Rs.last (); Where the cursor points to the last row

Rs.beforefirst (); The cursor points to the position in front of the first row, where the data cannot be obtained

Rs.afterlast (); The cursor points to the position following the last line, which cannot fetch data

Rs.absolute (index); Cursor moves to index position, index is any number of [1,count], but cannot exceed, otherwise error

Rs.relative (index); The cursor moves the index position from the current position, that is, relative movement, index can be negative, but the result is also within [1,count]

Isafterlast (); Determines whether the cursor is after the last row.

Isbeforefirst ();//Determines whether the cursor precedes the first row.

Iffirst (); Determines whether the cursor points to the first row of the result set.

Islast (); Determines whether the cursor points to the last row of the result set.

GetRow ();//Gets the line number of the row to which the current cursor is pointing, starting with line number 1, or 0 if the result set has no rows.

Java database Programming-read-write lob, scrollable, and updatable result sets