Java odjc resultset

I remember writing an article before, but it seems to be a problem. It should be the number of columns. Here, I will correct myself and record it, because I will always forget it, I used it today, so I quickly sent it...

CodeAs follows:

 Statement Stmt = Conn. Createstatement  (  Resultset . Type_scroll_insensitive , Resultset . Concur_read_only  )  ;  Resultset Rs = stmt.Executequery  ( SQL )  ; Rs. Last  (  )  ;  Int Length = Rs. Getrow  (  )  ; 

As shown above, the value of length is the number of rows. If you still need to use the current dataset RS after obtaining the number of rows, you need Rs. beforefirst (). Once, the cursor is returned to the initial position.
The cursor of the resultset. type_scroll_insensitive result set can be moved up or down. When the database changes, the current result set remains unchanged.
Resultset. concur_read_only cannot use the result set to update Tables in the database.

In addition, other descriptions of statement creation are provided:
Common Format: Statement stmt = con. createstatement (INT type, int concurrency); when we access the database, we may move the pointer before and after reading the returned results. For example, we first calculate how many pieces of information there are, this is where we need to move the pointer to the end for calculation, and then move the pointer to the front to read one by one. Sometimes we only need to read one by one. In addition, we only need to read the data. In order not to destroy the data, we can adopt the read-only mode. Sometimes we need to look at the database to add records. This is the mode where we need to adopt the updatable database.
The following describes all parameters:
Int type

The cursor of the resultset. type_forword_only result set can only scroll down.
The cursor of the resultset. type_scroll_insensitive result set can be moved up or down. When the database changes, the current result set remains unchanged.
Resultset. type_scroll_sensitive returns a scrollable result set. When the database changes, the current result set changes synchronously.

Int concurrency

Resultset. concur_read_only cannot use the result set to update Tables in the database.
Resultset. concur_updatetable can use the result set to update Tables in the database.

In addition, when we use resultset re=stmt.exe cutequery (SQL statement) to query, we can use the following methods to obtain information:

Public Boolean previous () moves the cursor up. This method returns boolean data. If it is moved to the first row of the result set, false is returned.
Public void beforefirst moves the cursor to the initial position of the result set, that is, before the first row.
Public void afterlast () moves the cursor after the last row of the result set.
Public void first () moves the cursor to the first row of the result set.
Public void last () moves the cursor to the last row of the result set.
Public Boolean isafterlast () determines whether the cursor is behind the last row.
Public Boolean isbeforefirst () determines whether the cursor is before the first row.
Public Boolean iffirst () determines whether the cursor points to the first row of the result set.
Public Boolean islast () determines whether the cursor points to the last row of the result set.
Public int getrow () gets the row number pointed to by the current cursor. The row number starts from 1. If no row exists in the result set, 0 is returned.
Public Boolean absolute (INT row) moves the cursor to the row specified by the row parameter. If the row is negative, it is the number of rows to be counted. Absolute (-1) indicates moving to the last row, while absolute (-2) indicates moving to the last 2nd rows. When moving to the front of the first line or the end of the last line, this method returns false.

 

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

This articleArticleIt does not show a specific example of how to use the resultset, but details the functionality of the resultset. I hope this article will help you understand resultset. The following is the specific content of this article.

The resultset is an object returned by the query results in the data. It can be said that the result set is an object that stores the query results, but the result set not only has the storage function, it also has the data manipulation function and may update the data.

The primary method for reading data from the result set is getxxx (). Its parameters can make integer values indicate the column number (starting from 1) or column name. The return value belongs to the xxx type. If XXX is an object corresponding to the Space-Time Value of the column, a null value of the XXX type is returned. If XXX is a number, such as float, 0 is returned, and Boolean returns false. You can use getstring () to return the values of all columns, but the returned values are of the string type. Xxx can represent the following types: basic data types such as INTEGER (INT), Boolean (Boolean), float (float, double), bit (byte ), it also includes some special types, such as date type (Java. SQL. date), time type (Java. SQL. time), timestamp type (Java. SQL. timestamp), large numbers (bigdecimal, biginteger, etc. You can also use getarray (INT colindex/string columnname) to obtain an array of objects consisting of elements in the column where colindex is located in the current row. Use getasciistream (INT colindex/string colname) to obtain the ASCII stream of the current row corresponding to this column. That is to say, all getxxx Methods Operate on the current row.

Result sets can be divided into four types based on their usage characteristics. The features of these four types of result sets are related to the creation of statement statements, because the result set is generated after execution of the statement, it can be said that the characteristics of the result set depend entirely on statement. Of course, I mean the following four features, three types are available when statement is created. First, there is no parameter type, which corresponds to the statement corresponding to the basic resultset described below. The connection used in the following code is not initialized. The variable conn represents the object corresponding to the connection. Sqlstr indicates the response SQL statement.
1. The most basic resultset.

The most basic resultset is that the resultset is used to store the query results. It can only be read once and cannot be read back and forth. You can create a result set as follows:

Statement ST = conn. createstatement
Resultset rs = Statement. excutequery (sqlstr );

Because this type of result set is not supported, you can only use the next () method in it to read data one by one if you obtain such a result set.

2. The resultset type that can be rolled.

This type supports rolling back and forth to get the record next (), previous (), back to the first line first (), and also supports the row absolute (int n) in the resultset ), and the number of rows relative to the current row in relative (int n). To implement this resultset, use the following method when creating the statement.

Statement ST = conn. createstatement (INT resultsettype, int resultsetconcurrency)
Resultset rs = st.exe cutequery (sqlstr)

The meanings of the two parameters are as follows:

Resultsettype indicates whether the type of the resultset object can be rolled or not. The values are as follows:

Resultset. type_forward_only can only scroll forward

Both resultset. type_scroll_insensitive and result. type_scroll_sensitive can implement any front-and-back scrolling and use various moving resultset pointer methods. The difference between the two lies in that the former is not sensitive to modification, while the latter is sensitive to modification.

Resultsetconcurency indicates that the resultset object can be modified. The values are as follows:

Resultset. concur_read_only is set as a read-only parameter.
Resultset. concur_updatable is set to a modifiable parameter.

So if you only want the result of the type that can be rolled, you just need to assign the following value to the statement.

Statement ST = conn. createstatement (result. type_scroll_insenitive, resultset. concur_read_only );
Resultset rs = ST. excutequery (sqlstr );

The query statement executed with this statement produces a rolling resultset.

3. updatable resultset

Such a resultset object can be used to modify the tables in the database, but I know that the resultset object is only equivalent to the view of the tables in the database, so from time to time, all the resultsets can be updated as long as they are set to update, the SQL statement of the resultset that can be updated must have the following attributes:

A. Only a single table is referenced.
B. It does not include join or group by clauses.
C. The columns must contain primary keywords.

If the above conditions are met, the updatable resultset can modify the data. The updatable result set can be created:

Statement ST = createstatement (result. type_scroll_insensitive, result. concur_updatable)

The execution result of such statement is an updatable result set. The update method is to move the resultset cursor to the row you want to update and then call updatexxx (). The xxx method has the same meaning as getxxx. The updatexxx () method has two parameters. The first one is the column to be updated, which can be the column name or serial number. The second is the data to be updated. The data type must be the same as that of XXX. Updaterow () is called to write data to the database for each update of a row. The modification is not submitted until the resultset cursor does not leave the row.

You can use the updatexxx method to complete the insert operation. But first, we need to introduce two methods:

Movetoinsertrow () is to move the resultset to the inserted row, which is a special row in the table and does not need to specify the specific row. The system will automatically move the row by calling this method.
Movetocurrentrow () This is to move the resultset to a row in memory, usually the current row. If the insert operation is not used, this method has no effect. If the insert operation is used, this method is used to return the row before the insert operation and leave the insert row, of course, you can also use the next (), previous () and other methods to leave the inserted row.

To complete database insertion, first call movetoinsertrow () to move to the inserted row, and then call the updatexxx method to complete the synchronization. After the data in each column is updated, it is the same as the update operation, to write data to the database, but insertrow () is used here. Make sure that the resultset does not leave the insert column before the method is executed. Otherwise, the insert is not executed, and the update of the inserted rows will be lost.

4. retainable resultset

Under normal circumstances, if you use statement to execute a query and then execute another query, the result set of the first query will be disabled, that is, the result set corresponding to all statement queries is one. If you call the Commit () method of connection, the result set is closed. Persistence means whether the resultset result is closed or not closed when it is submitted. After both jdbc2.0 and 1.0 are submitted, the resultset will be disabled. However, in jdbc3.0, we can set whether the resultset is disabled. To complete the creation of such a resultset object, the statement to be used must have three parameters. The statement creation method is the third method of statement. As follows:

Statement ST = createstatement (INT resultsetscrollable, int resultsetupdateable, int resultsetholdability)
Resultset rs = ST. excutequery (sqlstr );

The parameters in the first two parameters and the createstatement method of the two parameters are exactly the same. Here we only introduce the third parameter:

Resultsetholdability indicates whether to enable the result set after the result set is submitted. The values include:
Resultset. hold_cursors_over_commit: indicates that the database is not closed when the modification is submitted.
Resultset. close_cursors_at_commit: indicates that the resultset is disabled when the modification is submitted.

However, this function can only be set up with the jdbc3.0 driver.

The connection object represents the connection between Java and the database. to execute an SQL statement, the statement object must be obtained. It is used to execute the SQL statement and obtain the execution result, you can use createstatement () of connection to create a statement object:

Connection conn = drivermanager. getconnection (URL, user, password );
Statement stmt = conn. createstatement ();

After obtaining the statement object, we can use executeupdate (), executequery (), and other methods to execute SQL, executeupdate () it is mainly used to execute SQL statements that change the database content, such as CREATE TABLE, insert, drop table, and alter table. For example:

Stmt.exe cuteupdate ("insert into message values ('liangge'," + "'caterpillar @ mail.com ', 'login', '2017-5-26 ', "+" 'here for a tour ')");

The executequery () method is used to query the database SQL statements such as select. Both executeupdate () and executequery () will return the resultset object, representing the result of change or query, the query result is a piece of data. You use next () to move to the next piece of data. It returns true or false to indicate whether there is any next piece of data, then, you can use getxxx () to obtain information, such as getstring (), getfloat (), and getdouble () to obtain the corresponding column type data, respectively. getxxx () the methods provide methods for obtaining data by column name or by column order. An example is as follows:

Resultset result = stmt.exe cutequery ("select * From message ");
While (result. Next ())...{
System. Out. Print (result. getstring ("name") + "");
System. Out. Print (result. getstring ("email") + "");
System. Out. Print (result. getstring ("subject") + "");
System. Out. Print (result. getstring ("time") + "");
System. Out. println (result. getstring ("memo") + "");
}

You can use the column sequence of the queried results to display the results as follows:

Resultset result = stmt.exe cutequery ("select * From message ");
While (result. Next ())...{
System. Out. Print (result. getstring (1) + "");
System. Out. Print (result. getstring (2) + "");
System. Out. Print (result. getstring (3) + "");
System. Out. Print (result. getstring (4) + "");
System. Out. println (result. getstring (5) + "");
}

Statement execute () can be used to execute SQL statements and test whether the executed SQL statements are query or update. If true is returned, the SQL statement execution result is returned to resultset, in this case, you can use getresultset () to obtain the resultset object. If execute () returns false, the SQL statement returns the updated number or no result. In this case, you can use getupdatecount () to obtain the updated number of records. You can use execute () If you cannot know whether it is a query or update ().

The following is a complete example. After the query is complete, you can use the close () method of statement to release the database resources and JDBC resources of statement, in the end, close () of connection is also used to close the connection when no connection is used:

Dbtest. Java

Package onlyfun. Caterpillar;
Import java. SQL .*;
Public class dbtest ...{
Public static void main (string [] ARGs )...{
String driver = "com. MySQL. JDBC. Driver ";
String url = "JDBC: mysql: // localhost: 3306/guestbook? "+" Useunicode = true & characterencoding = big5 ";
String user = "caterpillar ";
String Password = "123456 ";
Connection conn = NULL;
Statement stmt = NULL;

Try ...{
Class. forname (driver );
Conn = drivermanager. getconnection (URL, user, password );
Stmt = conn. createstatement ();
Stmt.exe cute ("insert into message values ('lianggege" +
"', 'Caterpillar @ mail.com', 'Leave a message '," + "'2014-5-26', 'here A you ')");
Resultset result = stmt.exe cutequery ("select * From message ");
While (result. Next ())...{
System. Out. Print (result. getstring (1) + "");
System. Out. Print (result. getstring (2) + "");
System. Out. Print (result. getstring (3) + "");
System. Out. Print (result. getstring (4) + "");
System. Out. println (result. getstring (5) + "");
}
} Catch (classnotfoundexception e )...{
System. Out. println ("driver not found ");
E. printstacktrace ();
} Catch (sqlexception e )...{
E. printstacktrace ();
} Finally ...{
If (stmt! = NULL )...{
Try ...{
Stmt. Close ();
} Catch (sqlexception e )...{
E. printstacktrace ();
}
}
If (Conn! = NULL )...{
Try ...{
Conn. Close ();
} Catch (sqlexception e )...{
E. printstacktrace ();
}
}
}
}
}

finally, we noticed that the connection object is set to automatically "approve" (COMMIT), that is, after the statement SQL statement is executed, the database operation is changed immediately, if you want to debug the SQL statement to be executed in statement, you can use setautocommit (false) to automatically cancel the request. After the SQL statement is executed, call the Commit () method of connection to approve the change, use getautocommit () of connection to test whether it is set to automatic approval.
however, no matter whether or not the Commit () method is executed, as long as the SQL statement is not correct, the database is changed before the statement or connection is disabled.