JSP programming skills: Database Utilization

I. SQL Review

1. SQL statements are divided into two types: DDL (Data Definition Language) and DML (Dat Manipulation Languge, Data operation Language ). The former mainly defines the logical structure of data, including the definition of tables, views and indexes. DML mainly queries and updates databases.

2. Create Table (DDL ):

 
 

  
  
Create TableTabName (
  
  
ColName1 colType1 [Else],
  
  
ColName2 colType2 [Else],
  
  
...,
  
  
ColNamen colTypen [Else]
  
  
);
  
  
Example: CteateTablePJoiner (
  
  
PnoChar(6)Not Null,
  
  
EnoChar(6) nutNull 
  
  
);
 
 

Char int varchar and so on are reserved words used to define the column data type. varchar indicates a variable character type.

3, Select , ,..., From , ,..., [Where <condition>]

Subquery in the condition:

 
 

  
  
Where NotExists (
  
  
Select*FromTab2WhereCol1 = col2
  
  
) // When the query result is null, the condition is true.
 
 

4. INSERT VALUES ( ,... )

5. DELETE FROM [WHERE <condition>]

6. UPDATE

 
 

  
  
SET=
  
  
...
  
  
=
  
  
[WHERE<Condition>]
  
  
For example:
  
  
UpdateExployee
  
  
SetAge = 27
  
  
Where Name= Zhao Yi
 
 

2. Main 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 SQL statements that can be queries, updates, or even database creation)

Java. SQL. ResultSet, The results returned by the query are saved in this object, and can be used to browse and access the records in the database.

1. Using an odbc database through the jdbc-odbc bridge does not require jdbc Drivers)

Set pubs sysDSN in odbc DSN (Data Source Name), sa as username, and password as null.

 
 

  
  
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver"); // load the driver
  
  
Con=DriverManager. GetConnection ("jdbc: odbc: pubs", "sa", ""); // 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, 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 and database operations are irrelevant to connecting to the database.

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 start with the serial number 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(ee=e.getNextException()){  
  
  
out.println(e.getMessage());  
  
  
}  
  
  
} 
 
 

Generally, it is not recommended to write database access programs in jsp to encapsulate database access 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. the SQL type and the getObject return type of the ResultSet and the corresponding XXX getXXX () method

GetXXX () method corresponding to SQL type JSP

???????????????????????????????????????? ????

CHAR String getString ()

VARCHAR String getString ()

LONGVARCHAR String InputStream getAsciiStream ()/getUnicodeStream ()

NUMERIC java. math. BigDecimal java. math. BigDecimal getBigDecimal ()

DECIMAL same as above

BIT Boolean boolean getBoolean ()

TINYINT Integer byte getByte ()

SMALLINT Integer short getShort ()

INTEGER Integer int getInt ()

BIGINT Long long getLong ()

REAL Float float getFloat ()

FLOAT Double double getDouble ()

DOUBLE Double double getDouble ()

BINARY byte [] byte [] getBytes ()

VARBINARY byte [] byte [] getBytes ()

LONGVARBINARY byte [] InputStream getBinaryStream ()

DATE java. SQL. Date java. SQL. Date getDate ()

TIME java. SQL. Time java. SQL. Time getTime ()

TIMESTAMP java. SQL. Timestamp java. SQL. Timestamp getTimestamp ()

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 to the database by other users.

ResultSet. TYPE_SCROLL_SENSITIVE: Roll. This record will also be changed 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 R & D time are greatly accelerated. For example, you can reuse the components on the server. After using the stored procedure, a large amount of computing work 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 PROCEDUREAp_adduser
  
  
(
  
  
@ OutUserIDInt Output, // This is the output parameter,OutputMark
  
  
@ UserNameVarchar(25), // Parameter Representation Method:"@ XXX"Is the variable name,"Variable name type [output]" 
  
  
@ UserType tinyint,
  
  
@ UserBriefVarchar(255 ),
  
  
)
  
  
AS 
  
  
Declare@ UserIDInt// Define local variables
  
  
Insert IntoUserMain (UserName, UserType)
  
  
Values(@ UserName, @ UserType)
  
  
Select@ UserID = @ IDENTITY // used for value assignmentSelect, The ID is automatically obtained here
  
  
Insert IntoUserRef (UserID, UserBrief)
  
  
Select@ OutUserID = @ UserID
  
  
GO/* ends. Basic Structure:
  
  
CREATE PROCEDUREProcedureName (
  
  
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 ();
  
  
IntUserid = stmt. getInt (1 );
  
  
Stmt. close ()
 
 

8. Use transactions

1. The operations in the transaction are both successful or fail: after the transaction starts, if all the changes are correct, use the commit method to store all these actions in the database, otherwise, rollback is used to cancel all the changes, and the data in the database is the same as that before the transaction is executed.

2. When using transactions, use con. setAutoCommit (false) first, 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, it will be disabled.

2. There are many existing Connection Pool packages available.

3. Generally, the Connection Pool is used as a variable in the application scope.

 
 

  
  
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 rs=stml.executeQuery(query);  
  
  
ResultSetMetaData rsrsmd=rs.getMetaData();  
  
  
}  
  
  
.....  
  
  
finally{  
  
  
pool.releaseConnection(con);  
  
  
} 
 
 

You can also use a Servlet to initialize the connection pool.

1. JSP Database Operations routine JDBC-ODBC)
2. Java Servlets (JSP) Development Environment
3. Develop jsp http Server
4. Select JSP development tools
5. Servlet and JSP paths