Use of Oracle Database Connection Pool

Database connection is a key, limited, and expensive resource.ProgramIs particularly prominent. Database connection management can significantly affect the scalability and robustness of the entire application, and affect the performance indicators of the program. The database connection pool is designed to address this issue.

The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to repeat an existing database connection instead of reestablishing one; release the database connection with idle time exceeding the maximum idle time to avoid database connection omissions caused by the absence of the database connection. This technology can significantly improve the performance of database operations.

During database connection pool initialization, a certain number of database connections are created in the connection pool. The number of connections to these databases is set by the minimum number of database connections. Whether or not these database connections are used, the connection pool will always have at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue. The following factors must be taken into account when setting the minimum number of connections and maximum number of connections in the database connection pool:

1) The minimum number of connections is the database connection that has been maintained by the connection pool. Therefore, if the application uses a small amount of database connections, a large amount of database connection resources will be wasted;

2) the maximum number of connections is the maximum number of connections that can be applied by the connection pool. If the number of database connection requests exceeds this limit, the subsequent database connection requests will be added to the waiting queue, which will affect subsequent database operations.

3) if the difference between the minimum number of connections and the maximum number of connections is too large, the first connection request will be profitable. A connection request that exceeds the minimum number of connections is equivalent to creating a new database connection. However, these database connections greater than the minimum number of connections will not be released immediately after use. They will be placed in the connection pool for reuse or released after idle timeout.

In the traditional two-layer structure, the client opens the database connection at startup and closes the database connection when exiting the program. In this way, during the entire program running, each client always occupies a database connection, even if there is a large amount of free time without database operations, such as when the user inputs data, resulting in inefficient database connection.

In the three-tier structure mode, database connections are managed through the connection pool of the middle layer. Only when the user really needs to perform database operations, the middle layer applies for a connection from the connection pool. After the database operation is completed, the connection is immediately released to the connection pool for other users to use. This not only greatly improves the efficiency of database connections, but also allows a large number of users to share less database connections, saving the time for establishing connections.

Connection Pool configuration and usage

The database connection pool is a basic function of the application server. We use apusic application server as an example to describe how to configure and use the JDBC connection pool.

Apusic JDBC connection pool supports multiple databases, such as Oracle, Ms sqlserver, Sybase, Informix, and DB2.

The apusic JDBC connection pool can be connected to the database through the JDBC driver of the database itself, or through the JDBC-ODBC bridge. The following uses oracle as an example to describe how to configure the connection pool:

The JDBC driverpackage file classes111.zip of the Oracle database is stored in the/usr/Oracle/jdbc/lib directory (assuming that the Oracle installation directory is/usr/oracle1_directory, add classes111.zip to the classpath of the system first. Then make the following settings in apusic/config/apusic. conf (assuming the installation directory is apusic:

<Service

Class = "com. apusic. JDBC. poolmanager"

Name = "jdbcpool: Name = JDBC/sample"

>

<Attribute name = "expirationtime" value = "300"/>

<Attribute name = "mincapacity" value = "5"/>

<Attribute name = "url" value = "JDBC: oracle: thin: @ 192.168.19.136: 1521: orcl"/>

<Attribute name = "connectionproperties" value = "user = gtj, password = ABC123"/>

<Attribute name = "driverclassname" value = "oracle. JDBC. Driver. oracledriver"/>

<Attribute name = "maxcapacity" value = "30"/>

</Service>

Expirationtime: the timeout time in seconds. When a database connection exceeds the expirationtime, it is not used.

The system automatically closes the database connection. The default value is 300 seconds.

Mincapacity: Minimum number of connections

URL: Database URL

Connectionproperties: Connection property, where: User username and password

Driverclassname: JDBC Driver Class Name

Maxcapacity: Maximum number of connections

192.168.19.136: IP address of the Oracle computer.

Call connection pool

Let's take a JSP program as an example to illustrate how to use the connection pool. First obtain the datasource through JNDI, and then obtain the connection, as shown in the following example:

<HTML>

<Head>

<Title> jsp sample </title>

</Head>

<Body>

<P>

<% @ Page contenttype = "text/html; charset = gb2312" %>

<% @ Page import ="

Java. SQL .*,

Javax. Naming .*,

Javax. SQL .*

"%>

<%

Try {

Context CTX = new initialcontext ();

Datasource DS = (datasource) CTX. Lookup ("JDBC/sample ");

Connection con = Ds. getconnection ();

Statement stmt = con. createstatement ();

Resultset rs = stmt.exe cutequery ("select ename from EMP ");

While (Rs. Next ()){

Out. println ("<p>" + Rs. getstring (1 ));

}

Rs. Close ();

Stmt. Close ();

} Catch (exception e ){

System. Out. println ("JSP:" + E. getmessage ());

} Finally {

Try {

Con. Close ();

} Catch (exception E1 ){}

}

%>

</Body>

</Html>