Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to use the database in JSP", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use the database in JSP" this article.

First, SQL review

There are two types of SQL statements: DDL (Data Definition Language) and DML (Dat Manipulation Languge, data manipulation language). The former mainly defines the logical structure of data, including tables, views and indexes, while DML mainly queries and updates the database.

2Magne 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 that define column data types, where varchar represents a variable character type.

3,Select,...,From,..., [Where]

Subquery in the condition:

Where Not Exists (Select * From tab2 Where col1=col2) / / the condition is true when the query result is empty.

4. Insert INTOVALUES (,...)

5,DELETE FROM [WHERE]

6,UPDATE

SET =... = [WHERE] for example: Update exployee Set age=27 Where name= Zhao Yi

Second, the main interfaces of JDBC:

The java.sql.DriverManager class is used to handle driver calls and provide support for new database connections.

Java.sql.Connection, which refers to the connection between an application and a specific database.

Java.sql.Statement, for the execution of general sql statements (which can be queries, updates, or even create databases)

Java.sql.ResultSet, the results returned by the query are saved in this object, which can browse and access records in the database.

1, use the odbc database through the jdbc-odbc bridge (without jdbc Drivers)

First set pubs sysDSN,sa to username at the odbc DSN (Data Source Name) setting, and the password is empty.

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 getWarning method of Connection returns a SQLWarning object that should be checked before connecting.

The benefit of using jdbc-odbc is that it's free. However, performance is limited by odbc, and odbc drivers are generally more expensive.

2. Use a special jdbc driver. / / this is mm jdbc Driver

First put the jar file in the ClassPath.

Class.forName ("org.gjt.mm.mysql.Driver"); con=DriverManager.getConnection ("jdbc:mysql://localhost:3306/dbname", "root", ""); con.close ()

It can be seen that how to connect what kind of database and the operation of the database has nothing to do with connecting to the database.

Third, query the database

Statement stmt=con.createStatement ()

Stmt.setMaxRows () can control the number of output records.

ResultSet rs=stmt.executeQuery ("select.")

ResultSet points to the current record:

Int userId=rs.getInt ("userid")

String userName=rs.getString ("username")

... Or use a serial number (starting with 1)

Int userId=rs.getInt (1)

Stirng userName=rs.getString (2)

ClassNotFoundException is triggered because Class.forName () cannot load the jdbc driver

SQLException is generated when there is a problem with jdbc during execution. There's an extra way.

GetNextException () catch (SQLException e) {out.println (e.getMessage ()); while (ee=e.getNextException ()) {out.println (e.getMessage ());}}

Generally speaking, it is not recommended to write a database access program in jsp, which can encapsulate the database access in a javabean.

Fourth, ResultSet in-depth

1,ResultSetMetaData

ResultSet rs=stmt.executeQuery ("select....")

ResultSetMetaData rsmd=rs.getMetaData (); / / get the ResultSetMateData object

Int numberOfColumns=rsmd.getColumnCount (); / / returns the number of columns

Boolean b=rsmd.isSearchable (int I); / / returns whether column I can be used in the where clause

String c=rsmd.getColumnLabel (int I); / / get the column label of column I.

Objcet obj=rs.getObject ()

If (objacent null) out.println (obj.toString ())

Else println ("")

2the getObject return type of SQL type and ResultSet and the corresponding XXX getXXX () method

SQL type getXXX () method corresponding to JSP type

?

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 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 for longvarchar and langvarbinary in the database

ResultSet rs=stmt.executeQueryString ("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

/ / data must be fetched immediately after rs.getAsciiStream (), rs.getUnicodeStream (), rs.getBinaryStream (), etc.

Fifth, browse ResultSet

1J ResultSet 2.0 provides more ways to browse JDBC

First, make sure your jdbc driver supports jdbc2.0

Second, parameters should be specified when Statement is generated by Connection

Statement stmt=con.getStatement ("cursor type", "record update permissions")

Cursor type:

ResultSet.TYPE_FORWORD_ONLY: can only move forward

ResultSet.TYPE_SCROLL_INSENSITIVE: scrollable. However, it is not affected by other users' changes to the database.

ResultSet.TYPE_SCROLL_SENSITIVE: scrollable. 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 ResultSet is scrollable, the following functions can be used:

Rs.absolute () / / absolute position. A negative number represents a number from behind.

Rs.first () * *

Rs.last () *

Previous rs.previoust ()

The last one after rs.next ()

Before rs.beforeFirst () *

After rs.afterLast () *

Rs.isFirst (), rs.isLast (), rs.isBeforeFirst (), rs.isAfterLast

Note that when you first opened it, it was in front of the * record.

Sixth, update the database

1memt. ExecuteUpdate ("strSql"), strSql is a sql update statement. Update,insert,delete returns the number of entries affected

2the sql. Execute () method is used when you don't know whether the statement is a query or an update. If true is returned when more than one object is generated, stmt.getResultSet () and stmt.getUpdateCount () can be used to get the execute result, and false is returned if the ResultSet object is not returned.

3, in addition to Statement's executeUpdate, you can also use ResultSet:

Rs.updateInt (1 dint 10)

Rs.updateString (2, "sfafd")

Rs.updateRow ()

Seventh, use precompiled PreparedStatement

PreparedStatement objects, like Statement objects, can be used to execute SQL statements. The difference is that the database precompiles PreparedStatement's SQL statements and can still enter parameters and repeat compiled queries faster than uncompiled ones.

PreparedStatement stmt=con.preparedStatement ("Insert Into users (userid, username) values")

Stmt.clearParameters ()

Stmt.setInt (1 dint 2)

Stmt.setString (2, "Big")

Stmt.executeUpdate ()

Eight, execute the stored procedure

1. JDBC calls the stored procedure and uses the return value of the stored procedure. In this way, the processing work can be divided into two parts: the server side and the client side, and greatly speed up the time of system design and development. For example, you can reuse components on the server. After using stored procedures, a large amount of computing work can be handed over to the database server, which will reduce the load of the Web server and improve the performance of the whole system.

2, there are two tables UserMain {UserID,UserName,UserType}, UserRef {BrefID, UserID, UserBrief}

The following stored procedure can accept the parameters from jdbc, add content to UserMain and UserRef, and output an OutUserID.

CREATE PROCEDURE ap_adduser (@ OutUserID int output, / / this is the output parameter, output tag @ UserName varchar (25), / / parameter representation method: "@ XXX" is the variable name "variable name type [output]" @ UserType tinyint, @ UserBrief varchar,) AS Declare @ UserID int / / defines the local variable insert into UserMain (UserName, UserType) values (@ UserName,@UserType) select @ UserID=@@IDENTITY / / assignment with select, where ID insert into UserRef (UserID, UserBrief) select @ OutUserID=@UserID GO/* ends with the basic structure: CREATE PROCEDURE procedureName (parameters) AS actions GO * /

Use the following in the JSP page:

CallableStatement stmt=con.prepareCall ("{call ap_adduser (,)}"); stmt.registerOutParameter (1); register the output variable stmt.setString (2, "edmund"); stmt.setInt (3); stmt.setString (4, "description"); stmt.execute (); int userid=stmt.getInt (1); stmt.close ()

Eight, use transactions

1, the operation in the transaction is a whole, either successful or unsuccessful: after the transaction starts, if all the changes are correct, use the commit method to store all these actions in the database, otherwise, use rollback to cancel all the change actions, and the data in the database is the same as before the transaction is executed.

2. When using transactions, you should first use con.setAutoCommit (false), and * * use commit or rollback

3The catch rollback is usually executed in the section.

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. After the connection is used, it does not close him, but puts him in the connection pool. In this process, it is also necessary to determine whether the connection in the connection pool is out of date. If it overruns, shut him down.

2, there are many existing Connection Pool packages that can be used.

3. Connection Pool is generally used as an application-scoped variable

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);}

Connection pooling can also be initialized with a Servlet

The above is all the contents of the article "how to use databases in JSP". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.