Summary of Jsp database connection

When looking for rs. absolute (), I saw this article, which is very good .. It's a pity .. I have to copy and paste it...

JDBC interfaces:
The java. SQL. DriverManager class is used to process driver calls and support new database connections.
Java. SQL. Connection refers to the Connection between an application and a specific database.
Java. SQL. Statement, used for the execution of General SQL statements (it can be the execution process of queries, updates, or even database creation)
Java. SQL. ResultSet, The results returned by the query are saved in this object, which allows you to browse and access records in the database.

1. Use the odbc database through the jdbc-odbc bridge (jdbc Drivers is not required)

Set pubs sysDSN, sa as username, and password as pwd in odbc DSN (Data Source Name) settings.
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver"); // load the driver

Con = DriverManager. getConnection ("jdbc: odbc: pubs", "sa", "pwd"); // jdbc: odbc: pubs
Con. close ();
// Catch ClassNotFoundException and SQLException
The getWarning method of Connection returns a SQLWarning object. Check Before Connection.
The biggest benefit of using jdbc-odbc is: free. However, the performance is limited by odbc, and odbc drivers are generally expensive.

2. Use a dedicated jdbc driver. // Mm jdbc Driver
Put the jar file in ClassPath first.
Class. forName ("org. gjt. mm. mysql. Driver ");
Con = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/dbname", "root ","");
Con. close ();

It can be seen that the method used to connect to which database is irrelevant to database operations and database connection.

Iii. query Databases
Statement stmt = con. createStatement ();
Stmt. setMaxRows () can control the maximum number of output records;
ResultSet rs1_stmt.exe cuteQuery ("select .....");

ResultSet points to the current record:
Int userId = rs. getInt ("userid ");
String userName = rs. getString ("username ");
... Or use the serial number (starting from 1)
Int userId = rs. getInt (1 );
Stirng userName = rs. getString (2 );

ClassNotFoundException is triggered by the failure of Class. forName () to load the jdbc driver.
SQLException is generated when a problem occurs during jdbc execution. There is an additional method getNextException ()
Catch (SQLException e ){
Out. println (e. getMessage ());
While (e = e. getNextException ()){
Out. println (e. getMessage ());
}
}

In general, it is not recommended to write database access programs in jsp, and database access can be encapsulated in a javabean.

Iv. In-depth ResultSet

1. ResultSetMetaData
ResultSet rs1_stmt.exe cuteQuery ("select ....");
ResultSetMetaData rsmd = rs. getMetaData (); // gets the ResultSetMateData object
Int numberOfColumns = rsmd. getColumnCount (); // number of returned Columns
Boolean B = rsmd. isSearchable (int I); // returns whether column I can be used in the where clause
String c = rsmd. getColumnLabel (int I); // obtain the column ID of column I
Objcet obj = rs. getObject ();
If (obj! = Null) out. println (obj. toString ());
Else println ("");

2,
Rs. absolute (...) you can specify the absolute position of the result set. If you do not have it, you can only set rs if you want to retrieve 1,000th records. next () 1000 times :)
Rs. isAfterLast () is a special State that indicates that it has reached the end of the result set. If you call rs. next (), an exception is thrown.
Rs. next (); // scroll backward
Rs. getRow (); // obtain the current row number.
Rs. absolute (n); // The cursor locates n rows.
Rs. relative (int n); // relatively move n rows

3, null
Int I = rs. getInt ("age ");
If (! Rs. wasNull ()... // RecordSet: wasNull () is used to check null

4. Access large strings and binary text
Stream operations on longvarchar and langvarbinary in the database
ResultSet rs1_stmt.exe cuteQueryString ("select ...");
BufferedReader br = new BufferedReader (new InputStream (rs. getAsciiStream ("vol1"); // long text string
BufferedReader br = new BufferedReader (new InputStream (rs. getUnicodeStream ("vol1 ")));
BufferedReader br = new BufferedReader (new InputStream (rs. getBinaryStream ("vol2"); // long binary text
// Obtain data immediately after rs. getAsciiStream (), rs. getUnicodeStream (), rs. getBinaryStream (), etc.

5. View ResultSet
1. JDBC2.0 provides more methods to browse the ResultSet.
First, make sure that your jdbc driver supports jdbc2.0
Second, the parameter must be specified when Statement is generated by Connection.
Statement stmt = con. getStatement ("cursor type", "Record Update permission ");
Cursor type:
ResultSet. TYPE_FORWORD_ONLY: can only move forward
ResultSet. TYPE_SCROLL_INSENSITIVE: Scroll. However, it is not affected by changes made to the database by other users.
ResultSet. TYPE_SCROLL_SENSITIVE: Roll. This record also changes when other users change the database.
Record Update permission:
ResultSet. CONCUR_READ_ONLY, read-only
ResultSet. CONCUR_UPDATABLE, updatable

GetStatement () default parameter: getStatement (ResultSet. TYPE_FORWORD_ONLY, ResultSet. CONCUR_READ_ONLY)

2. If the ResultSet is volatile, the following functions can be used:
Rs. absolute () // absolute position. A negative number indicates the number from the end.
Rs. first () Article 1
Rs. last () last
Rs. previust ()
Rs. next ()
Rs. beforeFirst () before the first
Rs. afterLast () after
Rs. isFirst (), rs. isLast (), rs. isBeforeFirst (), rs. isAfterLast
Note: It is before the first record.

6. update the database
1. stmt.exe cuteUpdate ("strSql"). strSql is an SQL update statement. Number of affected results returned by update, insert, and delete
The 2, stmt.exe cute () method is used when the SQL statement is queried or updated. If more than one object is generated, true is returned. You can use stmt. getResultSet () and stmt. getUpdateCount () to obtain the execute result. If no ResultSet object is returned, false is returned.
3. In addition to the executeUpdate of Statement, you can also use ResultSet:
Rs. updateInt (1, 10 );
Rs. updateString (2, "sfafd ");
Rs. updateRow ();

7. Use precompiled PreparedStatement
The PreparedStatement object is similar to the Statement object and can be used to execute SQL statements. The difference is that the database will pre-compile the SQL statement of PreparedStatement, and still be able to input parameters and execute the compiled query repeatedly faster than the uncompiled query.
PreparedStatement stmt = con. preparedStatement ("Insert Into users (userid, username) values (?,?) ");
Stmt. clearParameters ();
Stmt. setInt (1, 2 );
Stmt. setString (2, "Big ");
Stmt.exe cuteUpdate ();

8. Execute the Stored Procedure
1. JDBC calls the stored procedure and uses the returned values of the stored procedure. In this way, the processing work can be divided into two parts: server and client, and the system design and development time can be greatly accelerated. For example, you can reuse the components on the server. A large amount of computing work after the stored procedure can be handed over to the database server for processing. This reduces the load on the Web server and improves the performance of the entire system.
2. There are two tables: UserMain {UserID, UserName, UserType}, UserRef {BrefID, UserID, UserBrief}
The following stored procedure can accept parameters sent from jdbc, add content to UserMain and UserRef, and output an OutUserID.
Create procedure ap_adduser
(
@ OutUserID int output, // This is the output parameter, which is marked by output
@ UserName varchar (25), // Parameter Representation: "@ XXX" is the variable name, "variable name type [output]"
@ UserType tinyint,
@ UserBrief varchar (255 ),
)
AS
Declare @ UserID int // defines a local variable
Insert into UserMain (UserName, UserType)
Values (@ UserName, @ UserType)
Select @ UserID = @ IDENTITY // use select to assign values. The ID is automatically obtained here.
Insert into UserRef (UserID, UserBrief)
Select @ OutUserID = @ UserID
GO/* ends. Basic Structure:
Create procedure procedureName (
Parameters
)
AS
Actions
GO
*/

The JSP page is used as follows:
CallableStatement stmt = con. prepareCall ("{call ap_adduser (?,?,?,?)} ");
Stmt. registerOutParameter (1, Types. INTEGER, 1); // register the output variable
Stmt. setString (2, "edmund ");
Stmt. setInt (3, 1 );
Stmt. setString (4, "description ");
Stmt.exe cute ();
Int userid = stmt. getInt (1 );
Stmt. close ()

8. Use transactions
1. The operations in the transaction are a whole, either successfully or not: after the transaction starts, if all the changes are correct, use the commit method to store all these actions into the database, otherwise, rollback is used to cancel all change actions, and the data in the database is the same as that before the transaction is executed.
2. When using transactions, use con. setAutoCommit (false), and use commit or rollback.
3. rollback is generally executed in the catch section.
9. Database Connection Pool
1. If there is a database connection request and there is no connection in the connection, a new connection is generated. After the connection is used, it is not closed, but put into the connection pool. In this process, you also need to determine whether the connection in the connection pool is out of date. If it is out of date, disable it.
2. There are many existing Connection Pool packages available.
3. Generally, the Connection Pool is used as a variable in the application scope.
<Jsp: useBean id = "pool" scope = "application" class = "javastart. tools. ConnectionPool"/>
<% @ Page import = "java. SQL. *" %>
<% @ Page import = "javastart. tools. *" %>
<! -- Javastart. tools is where your Connection Pool is located -->
DBConnection con = null;
Try {
Con = pool. getConnection ("sun. jdbc. odbc. JdbcOdbcDriver", "jdbc: odbc: access ","","");
Statement stmt = con. createStatement ();
Stmt. setMaxRows (10 );
String query = request. getParameter ("quey ");
ResultSet rs1_stml.exe cuteQuery (query );
ResultSetMetaData rsmd = rs. getMetaData ();
}
.....
Finally {
Pool. releaseConnection (con );
}
You can also use a Servlet to initialize the connection pool.

If something is wrong, come and try again ....

Wandering Xiao's column