Programming technology of SQL database in JSP

js| Programming | data | database

Programming technology of SQL database in JSP
One, the SQL review
The 1,sql statements are divided into two categories: DDL (Data Definition Language) and DML (Dat manipulation languge, data manipulation language). The former primarily defines the logical structure of data, including the definition of tables, views, and indexes; DML is primarily a query and update operation on a database.
2,create Table (DDL):
Create Table TabName (
ColName1 ColType1 [Else],
ColName2 ColType2 [Else],
...,
Colnamen Coltypen [Else]
);
For example: Cteate Table Pjoiner (
Pno Char (6) NOT NULL,
Eno char (6) Nut null
);
char int varchar, and so on, are reserved words used to define the column data type, where varchar represents a mutable character type.
3,select <col1>,<col2>,..., <coln>
From <tab1>,<tab2>,..., <tabm>
[where< conditions]

Subqueries in criteria:
Where not Exists (
Select * from TaB2 Where col1=col2
///When the query result is empty, the condition is true.

4,insert into <tab1> VALUES (<col1>, ...<coln>)
5,delete from <tab1> [where< conditions]
6,update <tab1>
SET <tab1>=<vlu1>
...
<tabn>=<vlun>
[where< conditions]
For example:
Update Exployee
Set age=27
Where name= ' Zhao Yi '
Second, JDBC main interface:
The Java.sql.DriverManager class is used to process driver transfer and support for new database connections.
Java.sql.Connection, refers to the connection of an application to a specific database.
Java.sql.Statement, for the execution of General SQL statements (can be queries, updates, or even the process of creating a database)
Java.sql.ResultSet, the results returned by the query are saved in this object and can be used to browse and access records within the database.

1, using the ODBC database via the Jdbc-odbc Bridge (no JDBC Drivers required)

Set the pubs Sysdsn,sa to username at the ODBC DSN (Data Source Name) setting, with an empty password
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");//Load Driver
Con=drivermanager.getconnection ("Jdbc:odbc:pubs", "sa", "");//jdbc:odbc:pubs
Con.close ();
Should catch ClassNotFoundException and SqlException

The connection Getwarning method returns a SQLWarning object that should be checked before it is connected.
The biggest advantage of using JDBC-ODBC is that it's free. However, performance is limited by ODBC, and the general ODBC driver is more expensive.
2, use a dedicated JDBC driver. Here is the mm JDBC Driver
First put the jar file inside the classpath.
Class.forName ("Org.gjt.mm.mysql.Driver");
Con=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/dbname", "Root", "");
Con.close ();

It is not relevant to see how the database is connected to the operation of the database and the connection database.
Third, query the database
Statement stmt=con.createstatement ();
Stmt.setmaxrows () can control the maximum number of output records;
ResultSet rs=stmt.executequery ("SELECT ...");

ResultSet point to current record:
int Userid=rs.getint ("userId");
String username=rs.getstring ("UserName");
... Or with the serial number (starting from 1)
int Userid=rs.getint (1);
Stirng username=rs.getstring (2);

ClassNotFoundException is due to Class.forName () failed to load the JDBC driver trigger
SqlException is generated when a problem occurs during the execution of JDBC. There is an additional method of getnextexception ()
catch (SQLException e) {
Out.println (E.getmessage ());
while (E=e.getnextexception ()) {
Out.println (E.getmessage ());
}
}

In general, it is not recommended to write the database access program in the JSP, you can encapsulate the access of the database in a JavaBean.
Four, resultset in-depth
1,resultsetmetadata
ResultSet rs=stmt.executequery ("SELECT ...");
ResultSetMetaData Rsmd=rs.getmetadata (); Get Resultsetmatedata Object
int Numberofcolumns=rsmd.getcolumncount ()//return number of columns
Boolean b=rsmd.issearchable (int i);//Returns whether column I can be used for a WHERE clause
String C=rsmd.getcolumnlabel (int i);//Get column label of column I
Objcet Obj=rs.getobject ();
if (obj!=null) out.println (obj.tostring ());
else println ("");
GetObject return type of 2,sql type and resultset and corresponding XXX getxxx () method
SQL type JSP type corresponding to the GetXXX () method
————————————————————————————————————————————
CHAR string String getString ()
VARCHAR string String getString ()
LongVarChar String inputstream Getasciistream ()/getunicodestream ()
NUMERIC java.math.BigDecimal java.math.BigDecimal Getbigdecimal ()
DECIMAL ditto
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 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 for null
4, access to large strings and binary text
Streaming operations for LongVarChar and Langvarbinary in a database
ResultSet rs=stmt.executequerystring ("SELECT ...");
BufferedReader br=new BufferedReader (New InputStream ("Rs.getasciistream"));/Long text string
BufferedReader br=new BufferedReader (New InputStream) (Rs.getunicodestream ("vol1"));
BufferedReader br=new BufferedReader (New InputStream) (Rs.getbinarystream ("vol2"));//Long Binary text
Data must be taken immediately after Rs.getasciistream (), Rs.getunicodestream (), Rs.getbinarystream (), etc.

Five, browse resultset
1,JDBC2.0 provides more ways to browse resultset
First, identify your JDBC driver support jdbc2.0
Second, you specify parameters when generating statement from connection
Statement stmt=con.getstatement ("Cursor type", "Record update Permissions");
Cursor Type:
Resultset.type_forword_only: can only move forward
Resultset.type_scroll_insensitive: Can be scrolled. But not affected by other users ' changes to the database.
Resultset.type_scroll_sensitive: Can be scrolled. This record also changes when other users change the database.
Record Update permissions:
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 scrolling, the following functions can be used:
Rs.absolute ()//absolute position, negative number representing from the back
Rs.first () the first article
Rs.last () The last one
Rs.previoust () Previous
Rs.next () the latter
Rs.beforefirst () before the first one
Rs.afterlast () after last
Rs.isfirst (), Rs.islast (), Rs.isbeforefirst (), Rs.isafterlast
Notice that when you first open it, it's before the record.

Vi. Updating the database
1,stmt.executeupdate ("strSQL"), strSQL is an SQL UPDATE statement. Update,insert,delete returns the number of bars affected
The 2,stmt.execute () method is used when it is not known whether the SQL statement is a query or an update. Returns true if more than one object is generated, at which time Stmt.getresultset () and Stmt.getupdatecount () are available to obtain the execute result, or False if the ResultSet object is not returned.
3, in addition to statement executeupdate can also use resultset:
Rs.updateint (1,10);
Rs.updatestring (2, "sfafd");
Rs.updaterow ();

Seven, using precompiled PreparedStatement
PreparedStatement objects are similar to statement objects and can be used to execute SQL statements. The difference is that the database compiles PreparedStatement SQL statements, and still can enter parameters and repeat the compiled query faster than the precompiled.
PreparedStatement stmt=con.preparedstatement ("Insert into Users (userid, username) VALUES (?,?)");
Stmt.clearparameters ();
Stmt.setint (1,2);
Stmt.setstring (2, "big");
Stmt.executeupdate ();

Eight, execute the stored procedure
1,JDBC invokes the stored procedure and uses the return value of the stored procedure. This can be divided into the service side and the client two parts, and greatly accelerate the system design and development time. For example, you can reuse components on the server. After using stored procedures, a lot of computing work can be done to the database server, which will reduce the load on the Web server, thereby improving the performance of the entire system.
2, there are two tables Usermain{userid,username,usertype},userref{brefid, UserID, userbrief}
The following stored procedures can accept the parameters from JDBC, add new content to Usermain and userref, and output a outuserid.
CREATE PROCEDURE Ap_adduser
(
@OutUserID int output,//This is an output parameter.
@UserName varchar (25),//Parameter representation method: "@XXX" for variable name, "variable name type [Output]"
@UserType tinyint,
@UserBrief varchar (255),
)
As
Declare @UserID int//define local variables
Insert into Usermain (UserName, usertype)
VALUES (@UserName, @UserType)
Select @UserID =@ @IDENTITY//assignment with SELECT, Get ID automatically here
Insert into Userref (UserID, Userbrief)
Select @OutUserID = @UserID
go/* end, basic structure:
CREATE PROCEDURE procedurename (
Parameters
)
As
Actions
Go
*/


This is used in JSP pages:
CallableStatement Stmt=con.preparecall ("{Call Ap_adduser (?,?,?,?)}");
Stmt.registeroutparameter (1,types.integer,1);//Register Output variable
Stmt.setstring (2, "Edmund");
Stmt.setint (3,1);
Stmt.setstring (4, "description");
Stmt.execute ();
int Userid=stmt.getint (1);
Stmt.close ()

Viii. Use of transactions
1, the operation in a transaction is a whole, either successful or unsuccessful: After the transaction starts, if all the changes are correct, then use the Commit method to put all of these actions into the database, or use rollback to cancel all the change actions, The data in the database is the same as before the transaction is performed.
2, use the transaction should first use Con.setautocommit (false), and finally use a commit or rollback
3,rollback typically executed in a catch segment
Nine, database connection pool
1, if there is a database connection request and there is no connection in the connection, a new connection is generated. This connection is not closed when it is used, but rather it is put into the connection pool. In this process, you also need to determine whether connections in the connection pool are overdue. Close it if it is extended.
2, there are many existing connection pool packs that can be used.
3, the connection pool is generally used as a variable of 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-->
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 Rsmd=rs.getmetadata ();
}
.....
finally{
Pool.releaseconnection (con);
}

You can also initialize a connection pool with a servlet