Servlet & JSP events (11)

As part of web development, database access is also essential. Let's take a look at JDBC first.

JDBC

JDBC (Java database connectivity) is an application programming interface (API). It describes a set of standard Java class libraries that access relational databases. Using these APIs, you can connect to the database and then execute SQL statements to operate the database. JDBC also provides a standard architecture for database vendors. Vendors can provide JDBC drivers for their own products. These drivers allow Java applications to directly access their database products, so as to improve the efficiency of Java program access to the database.

JDBC drivers can be divided into four types: JDBC-ODBC bridge, some local APIs, some java drivers, JDBC network pure Java drivers and local protocol pure Java drivers.

1) JDBC-ODBC bridge as the name suggests, is to use jdbc api to call ODBC to access the database, because ODBC appeared earlier than JDBC, this is suitable for database products only provide ODBC driver, because of its low execution efficiency.
2) some local APIs and some java Drivers call local APIs through Java to perform database operations. The jdbc api first calls the local API to execute the operation. The execution result is returned to the JDBC driver through the local API, and then the JDBC driver converts the result to the JDBC standard form and returns it to the user.
3) The JDBC network pure Java driver uses it as the middleware application server to access the database.
4) The local protocol pure Java driver establishes a direct socket connection with the database and converts jdbc api calls to direct network calls using the network protocol specific to the tangle. This type of driver is the most efficient.

How to install the database is not described here. This document uses the MySQL database. Is:Click Open Link

There is a direct installer in windows, and you also need to download the connector. After the download, install and configure it. (If not, Google ~)

JDBC API

JDBC APIs are included in JDK and are divided into two packages: Java. SQL and javax. SQL. The Java. SQL package defines the interfaces and classes used to access the database. Let's first look at the database access process:

1) load and register the database driver
2) establish a connection to the database
3) access the database

Sample Code:

Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManger.getConnection("jdbc:mysql://localhost:3306/book","root","shan");Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select * form books");

Load and register the database driver

The Java. SQL. Driver Interface is the interface that all JDBC drivers need to implement. It is used by database vendors. The class names for different vendors to implement this interface are different. The class for implementing the driver interface in MySQL is com. MySQL. JDBC. Driver. To load a JDBC driver, call the static method forname () of the class and pass the name of the JDBC driver to be loaded to it. During runtime, the Class Loader locates and loads the JDBC driver class from the classpath environment variable. After the driver class is loaded, an instance of the driver class will be registered.

Establish a connection to the database

We do not need to directly access the driver class implementing the driver interface in the program, but the driver Manager manages these drivers. The driver interface provides a connect () method to establish a connection to the database. When establishing a connection, we use the getconnection () method of the drivermanager class (Driver Management Program) to establish a connection to the database. It returns a connection object. The drivermanager class provides three overloaded getconnection () methods. As follows:

public static Conection getConnection(String url) throws SQLException;public static Conection getConnection(String url, String user, String passwd) throws SQLException;public static Conection getConnection(String url, Properties info) throws SQLException;

The URL is the URL of the database. The entire URL is divided into three parts: Protocol, sub-protocol, and sub-name. The syntax is JDBC: subprotocol: subname. The JDBC identity protocol. A sub-protocol is used to identify a database driver. The sub-name syntax is related to a specific driver. The URL of the MySQL database is JDBC: mysql: // localhost: 3306/databasename.

User is the user name and passwd is the password. Properties contains the parameters required for a specific database, and the connection parameters are given in the form of key-value pairs.

Access Database

After the connection is established, we can access the database. The Java. SQL package provides three interfaces: Statement, preparedstatement, and callablestatement. These three interfaces define different ways to access the database.

1) Statement

The createstatement () method is defined in connection to create a statement object. The statement object is used to execute static SQL statements and return the execution results. The statement interface defines executequery (string SQL) to execute SQL statements and returns a result of the resultset type. Executeupdate (string SQL) is used to execute insert, update, or delete statements. Execute (string SQL) method to execute the SQL statement that returns multiple result sets.

2) resultset

The executequery (string SQL) method in the statement interface returns a resultset type result. The resultset object maintains a cursor pointing to the current data row. Initially, the cursor can be moved to the next row through the next () method of the resultset object before the first row. The resultset interface also defines methods such as getint () and getstring. You can obtain the data of columns in the current row.

3) preparedstatement

The SQL statements passed in the program must be pre-compiled before they can be executed by the database engine. It is inefficient to execute SQL statements with different parameters repeatedly. If you want to use different parameters to execute the same SQL statement multiple times, you can use the preparedstatement statement. The usage is as follows:

PreparedStatement pstmt = conn.prepareStatement("insert student values (?,?)");pstmt.setInt(1,1);pstmt.setString(2,"shan");pstmt.executeUpdate();pstmt.setInt(1,2);pstmt.setString(2,"dong");pstmt.executeUpdate();

4) callablestatement

Callablestatement is used to execute SQL stored procedures. Callablestatement is inherited from preparedstatement. Before executing a stored procedure, all parameters with the out type in the stored procedure must be registered. You can use the registeroutparameter () method. The usage is as follows:

CallableStatement cstmt = conn.prepareCall("call p_changesal(?,?)");cstmt.registerOutParameter(2,java.sql.Types.INTEGER);cstmt.setInt(1,1234);cstmt.execute();int price = cstmt.getInt(2);

5) Metadata

We may need to obtain information about the database table itself. You can use the getmetadata () method of the resultset object to obtain the resultsetmetadata object. The resultsetmetadata object defines some common methods, such as getcolumncount (), getcolumndisplaysize (), and getcolunmname.

Transaction Processing

If the online shopping payment function is enabled, the user enters the settlement center. After confirming the order, the program obtains the unit price and quantity, calculates the total price, calculates whether the user balance is sufficient for payment, and then updates the goods data. However, at this point, the server crashed. After the restart, it was found that the number of goods was reduced, but the income did not increase. Because the operation to update the user balance is not performed due to a fault. To solve this problem, a transaction processing mechanism is required.

A transaction is a set of operations that constitute a single logical unit. Transaction Processing ensures that all transactions are executed as a unit of work. Even if a hardware fault or system failure occurs, this execution method cannot be changed. To process multiple SQL statements as one transaction, call the setautocommit () method of the connection object and pass in false to cancel the automatic transaction commit. After all SQL statements are successfully executed, call the Commit () method for submission, or call the rollback () method to roll back (that is, return to the initial state) When an error occurs ). For more information about the concept of transaction processing, see the database book.

JDBC data source and Connection Pool

We have discussed a way to connect to a database. Now let's continue with another method. In the javax. SQL package, the datasource interface is defined, which provides us with another connection method. Datasource is used to establish a database connection. No driver needs to be loaded, and no management class is required. In the program, the datasource object is obtained by querying a JNDI (Java name and directory) server, and the getconnection () method of the datasource object is called to establish a database connection. The usage is as follows:

Javax. nameing. context context = new javax. naming. initialcontext (); // lookup is used to query a named object javax. SQL. datasource DS = (javax. SQL. datasource) context. lookup ("Java: COMP/ENV/jdbc/book"); Java. SQL. connection conn = Ds. getconnection (); Conn. close ();

Java: COMP/ENV is the environment naming context (Environment naming context, ENC). It is introduced to solve the problem of JNDI naming conflicts. ENC separates the resource reference name from the actual JNDI name, which improves the portability of J2EE applications.

What is a connection pool?

Since establishing a database connection is time-consuming and resource-consuming, and the number of connections that a database server can establish at the same time is limited, in large Web applications, there may be thousands of database access requests at the same time. If the database establishes a connection for each user, the performance will drop dramatically. Connection Pool technology is used to reuse database connections and improve request performance. It establishes multiple database connection objects in advance and saves the connection objects to the connection pool. When a user request arrives, it retrieves a connection object from the pool to serve the user. After the request is complete, the user program calls the close () method to put the connection object back into the pool.

Tomcat provides the implementation of data sources and connection pools. In tomcat, you can use the <resource> element in the content of the <context> element to configure the JDBC data source.

Reprinted please indicate the source: http://blog.csdn.net/iAm333