The third chapter of API interpretation--the core object (JDBC) of processing result set

Core Objects

The core objects that handle the result set are resultset and rowset. Where resultset specifies the result set of a relational database, rowset is more abstract, and the data that is composed of rows and columns can be.

ResultSet

The primary purpose of the ResultSet object is to obtain query results from the database. It consists mainly of three parts, the query result and the result set object's relation, the result set object's type, the result set object's operation.

Relationship

Each result set corresponds to query result one by one, and the result set contains all the columns in the query results, but contains only some rows in the query results. If the results of the query are cross-table, then the result set is also cross-table, and the result set of this type cannot be updated, the insert operation.

To get the object in the result set, if you know the column name of the query result and the data type of the column, you can get the value of the data by name, and if you understand the order of the columns in the query results and the data type for each column, you can get the value of the data by index. If you do not understand the structure of the query results, you can obtain the relevant information in the query results by getting the ResultSetMetaData object.

ResultSetMetaData

ResultSetMetaData contains information about the results of the query and can be broadly divided into four categories.

Database

// A catalog contains multiple schemas, one schema contains multiple tables, and the query result set can come from more than one table SYSTEM.OUT.PRINTLN ("Query result" + i + "column of the Catalog name:" + Rsmetadata.getcatalogname (i)); System.out.println ("Shcema name of the" + i + "column of the" Query result ":" + Rsmetadata.getschemaname (i)); System.out.println ("The table name of the" + i + "column of the" Query result ":" + rsmetadata.gettablename (i));

Columns of information

// column information, getting the name of the column, type SYSTEM.OUT.PRINTLN ("Query result" + i + "column name:" + Rsmetadata.getcolumnname (i)); SYSTEM.OUT.PRINTLN ("Query result" + i + "column label:" + Rsmetadata.getcolumnlabel (i)); SYSTEM.OUT.PRINTLN ("Query result" + i + "column of type name:" + Rsmetadata.getcolumntype (i)); System.out.println ("The database data type of the query result section" + i + "column:" + Rsmetadata.getcolumntypename (i)); System.out.println (        "columndisplaysize of the query result" + i + "column:" + rsmetadata.getcolumndisplaysize (i)); SYSTEM.OUT.PRINTLN ("Query result" + i + "column corresponding to the Java data type:" + rsmetadata.getcolumnclassname (i));

Properties of columns

// the attributes of the column, such as whether it is null. SYSTEM.OUT.PRINTLN ("Query result" + i + "column allows null values:" + rsmetadata.isnullable (i)); SYSTEM.OUT.PRINTLN ("Query result" + i + "column is currency:" + booleantostr (rsmetadata.iscurrency (i))); System.out.println (    "Query Result" + i + "column is a self-increment column:" + booleantostr (rsmetadata.isautoincrement (i))); System.out.println (    "Query Results section" + i + "column is case sensitive:" + booleantostr (rsmetadata.iscasesensitive (i)));

Permissions

// Permissions for Columns System.out.println (        "Query Result" + i + "column has Write permission:" + booleantostr (rsmetadata.isdefinitelywritable (i))); SYSTEM.OUT.PRINTLN ("Query Results section" + i + "column is read-only permission:" + booleantostr (rsmetadata.isreadonly (i))); SYSTEM.OUT.PRINTLN ("Query Results section" + i + "column is allowed to search:" + booleantostr (rsmetadata.issearchable (i)));

Type

The type of the result set can be specified by setting its synchronization mode, traverse mode, and shutdown mode.

/*** Print result set type related information, set the result set type information is specified when creating statement related object. ResultSet itself does not have these methods * output results need to refer to the Java API Constant Field Values *@paramRS *@throwsSQLException*/ Public Static voidPrintresulttypeinfo (ResultSet rs)throwsSQLException {System.out.println ("-------------------type-related information for the result set-------------------"); System.out.println ("Traversal type of result set:" +Rs.gettype ()); System.out.println ("Synchronization type for result set:" +rs.getconcurrency ()); System.out.println ("How the result set is closed:" +rs.getholdability ());}

Synchronization mode

1. Concur_readonly: The result set is read-only and can only be traversed. The read lock is obtained when the user reads the data, which is also the default way
2. Concur_updatable: The result set can be updated, the user will get the Read-write lock when reading the data, there are two kinds of locking mechanism, pessimistic lock and optimistic lock when inserting, updating and deleting the database. Pessimistic locks allow only a single user to access the data, and other users can access it only after the current user releases the lock. Optimistic locking allows multiple users to access data at the same time, and when a conflict occurs, the transaction that causes the conflict is rolled back to ensure the integrity of the data.

Traversal mode

1. Forward_only: can only traverse 1 from the Go
2. Type_scroll_sensitive: The result set is scrollable, can be traversed from the front to the back, or can be specified from any location, but the data in the result set is specified at creation time, and if the query results change, the data in the result set will not be altered.
3. Type_scroll_insensitive: The result set is scrollable, the traversal is arbitrary, and when the query results are changed, the results set contains the new query results.

Closed mode

1. Close_cursors_at_commit: The result set object is closed when the transaction commits
2. Hold_cursors_over_commit: The result set object is not closed when a transaction commits.

Operation Traversal

There are four ways to traverse the result set, from front to back, from back to front, to specify absolute position, relative position. The default result set is the forward_only type, which can only be traversed from front to back, and other traversal methods require that the result set be set to Type_scroll_sensitive or type_scroll_insensitive.

Front to back

/*** Traverse result set object from front to back * *@paramRS *@throwsSQLException*/Private Static voidPrinttoptobottom (ResultSet rs)throwsSQLException {rs.beforefirst ();  while(Rs.next ()) {System.out.print ("Student's name:" + rs.getstring (1) + "\ T"); System.out.print ("Student's School Number:" + rs.getint (2) + "\ T"); System.out.print ("Student's class:" + rs.getstring (3) + "\ T"); System.out.print ("Student's Course:" + rs.getstring (4) + "\ T"); System.out.println ("The student's birthday is:" + rs.getdate (5) + "\ T"); }}

From the back to the front

/*** Traverse result set object, from back to front * *@paramRS *@throwsSQLException*/Private Static voidPrintbottomtotop (ResultSet rs)throwsSQLException {rs.afterlast ();  while(Rs.previous ()) {System.out.print ("Student's name:" + rs.getstring (1) + "\ T"); System.out.print ("Student's School Number:" + rs.getint (2) + "\ T"); System.out.print ("Student's class:" + rs.getstring (3) + "\ T"); System.out.print ("Student's Course:" + rs.getstring (4) + "\ T"); System.out.println ("The student's birthday is:" + rs.getdate (5) + "\ T"); }}

Absolute position

/*** Traverse result set, use absolute mode * *@paramRS *@paramPOS starts from the first line with a starting value of 1 *@throwsSQLException*/Private Static voidPrintbyabsolutepos (ResultSet RS,intPosthrowsSQLException {rs.absolute (POS);  while(Rs.next ()) {System.out.print ("Student's name:" + rs.getstring (1) + "\ T"); System.out.print ("Student's School Number:" + rs.getint (2) + "\ T"); System.out.print ("Student's class:" + rs.getstring (3) + "\ T"); System.out.print ("Student's Course:" + rs.getstring (4) + "\ T"); System.out.println ("The student's birthday is:" + rs.getdate (5) + "\ T"); }}

Relative position

/*** Traverse result set, use relative position * *@paramRS *@paramPOS *@throwsSQLException*/Private Static voidPrintbyrelativepos (ResultSet RS,intPosthrowsSQLException {//specify to last rowRs.last (); //result set bar number    intTotal =Rs.getrow (); //specify to First rowRs.beforefirst ();  while(Rs.next ()) {//determines whether the current line has not exceeded bounds after moving the POS position in the past or from the rear        if(Rs.getrow () + pos <Total )        {rs.relative (POS); } System.out.print ("Student's name:" + rs.getstring (1) + "\ T"); System.out.print ("Student's School Number:" + rs.getint (2) + "\ T"); System.out.print ("Student's class:" + rs.getstring (3) + "\ T"); System.out.print ("Student's Course:" + rs.getstring (4) + "\ T"); System.out.println ("The student's birthday is:" + rs.getdate (5) + "\ T"); }}

Update

The following prerequisites are required to synchronize the result set update data to the database:

1. The type of the result set is concur_updateable.
2. The result set contains data that is the data in a single table
3. Query statements corresponding to the result set cannot contain join,group by and * numbers
4. The result set contains the primary key field

/*** Data is updated in the result set and synchronized to the database. * Prerequisites: * 1. The result set type is concur_updatable * 2. The object in the result sets is a single table, * 3. The query that corresponds to the result set does not contain join and group by statements, and cannot contain * number * 4. The result set contains the primary key field * *@paramRS *@throwsSQLException*/ Public Static voidUpdateData (ResultSet rs)throwssqlexception{//demo to update last piece of dataRs.last (); //Update nameRs.updatestring (1, "name after Update"); //Update Study NumberRs.updateint (2, 500); //Update ClassRs.updatestring (3, "Class after Update"); //Update CourseRs.updatestring (4, "post-update Course")); //Update BirthdayRs.updatedate (5,NewDate (2017, 11, 12)); //updating result sets and databasesRs.updaterow ();}

Insert

The result set inserts data in addition to the conditions that need to be updated, the following conditions must be met

1. User has permissions on the table
2. The result set contains all columns that are NOT null values at the time of insertion

/*** Result set insert data, synchronize to database. * In the case of update operation, there are additional requirements * 1. The user has permission to read and write to the table * 2. The result set contains all columns that are not allowed non-nullable *@paramRS *@throwsSQLException*/ Public Static voidInsertData (ResultSet rs)throwssqlexception{//ready to insert a piece of dataRs.movetoinsertrow (); //Insert NameRs.updatestring (1, "inserted name"); //Insert Study NumberRs.updateint (2, 501); //Insert ClassRs.updatestring (3, "inserted class"); //Insert CourseRs.updatestring (4, "inserted course"); //Insert BirthdayRs.updatedate (5,NewDate (2017,11,12)); //The data is inserted because the maximum number of bars is reached, so the result set does not contain the data just inserted, but can be queried from the databaseRs.insertrow ();}

Delete

/**@param @throws */public static void throws sqlexception{    //  Delete result set     rs.last ();    Rs.deleterow ();}

ROWSET

Todo

The third chapter of API interpretation--the core object (JDBC) of processing result set