Connection between J2EE applications and Oracle databases (OCI, thin, and JdbcOdbc)

In J2EE application development, establishing a connection between an application and a database is a common problem. Here I will talk about connecting to the Oracle database through OCI, thin, and JdbcOdbc in local applications, configure the Oracle database connection pool in iPlanet Application Server 6.5 and Sun Java System Application Server 7 and how to obtain connections from the connection pool in the Application

1. Obtain the Oracle database connection through JDBC locally

You can use JDBC to obtain Oracle database connections in three ways: OCI, thin, and JdbcOdbc. The OCI method depends on the local dynamic link library. This method can be used if the Oracle database client is installed locally. The thin method is a java-only database connection method; the JdbcOdbc Bridge Mode depends on the configuration of the local ODBC database source, which is generally not used.

1. OCI Mode

First, install the Oracle client locally. After installation, find... The/jdbc/lib/classes12.zip file sets the path of classes12.zip in the environment variable classpath.

Then, use the following database connection classes to locally obtain the Oracle database connection through OCI

/**
* Obtain a local database connection
*/

Package com. j2ee. db;

Import java. util .*;
Import java. SQL .*;
Import javax. SQL .*;
Import java. io .*;
Import oracle. jdbc. driver .*;
Import javax. naming .*;

/**
* Obtain the Oracle database connection through OCI
*/
Public class DbConnection
{
Final static String sDBDriver = "oracle. jdbc. driver. OracleDriver ";
Final static String sConnStr = "jdbc: oracle: oci8: sr/sr @ ora199 ";

/**
*
*/
Public DbConnection ()
{
}

/**
* Obtain the Oracle database connection
*/
Public java. SQL. Connection connectDbByOci ()
{
Java. SQL. Connection conn = null;
Try
{
Class. forName (sDBDriver );
Conn = DriverManager. getConnection (sConnStr );
}
Catch (Exception e)
{
System. out. println ("ERROR:" + e. getMessage ());
}
Return conn;
}
}
In the connection string "jdbc: oracle: oci8: sr/sr @ ora199", "sr/sr" is the username and password of the Oracle user, and "ora199" is the database service name.

2. thin Mode

Go to Oracle technical network (http://otn.oracle.com/global/cn/software/tech/java/sqlj_jdbc/index.html) to download Oracle JDBC Drivers, also set the path of the downloaded zip file to the environment variable classpath.

Then, use the following database connection classes to locally obtain the Oracle database connection through thin.

/**
* Obtain a local database connection
*/

Package com. j2ee. db;

Import java. util .*;
Import java. SQL .*;
Import javax. SQL .*;
Import java. io .*;
Import oracle. jdbc. driver .*;
Import javax. naming .*;

/**
* Obtain the Oracle database connection through thin
*/
Public class DbConnection
{
Private String sConnStr = "";

/**
* Default constructor
*/
Public DbConnection ()
{
SConnStr = "jdbc: oracle: thin: @ 10.1.4.199: 1521: ora199 ";
}

/**
* @ Param ip, serviceName
*/
Public DbConnection (String ip, String serviceName)
{
SConnStr = "jdbc: oracle: thin: @" + ip + ": 1521:" + serviceName;
}

/**
* Use thin to obtain the connection to the Oracle database.
*/
Public java. SQL. Connection connectDbByThin ()
{
Java. SQL. Connection conn = null;
Try
{
Class. forName (sDBDriver );
Conn = DriverManager. getConnection (sConnStr, "sr", "sr ");
}
Catch (Exception e)
{
System. out. println ("ERROR:" + e. getMessage ());
}
Return conn;
}

/**

* Use thin to obtain the connection to the Oracle database.
* @ Param userId, password
*/
Public java. SQL. Connection connectByJdbc (String userId, String password)
{
Java. SQL. Connection conn = null;
Try
{
Class. forName (sDBDriver );
Conn = DriverManager. getConnection (sConnStr, userId, password );
}
Catch (Exception e)
{
System. out. println ("ERROR:" + e. getMessage ());
}
Return conn;
}
}
This method is flexible, simple, and has high portability and applicability. Pay attention to the specific parameter settings in the connection string "jdbc: oracle: thin: @ 10.1.4.199: 1521: ora199 ".

3. JdbcOdbc Bridge

First, add a local connection to the Oracle database through the data source in the management tool, and then obtain the Oracle database connection locally through the JdbcOdbc bridge through the following database connection classes.

/**
* Obtain a local database connection
*/

Package com. j2ee. db;

Import java. util .*;
Import java. SQL .*;
Import javax. SQL .*;
Import java. io .*;
Import oracle. jdbc. driver .*;
Import javax. naming .*;

/**
* Obtain Oracle database connection through JdbcOdbc Bridge
*/
Public class DbConnection
{
/**
*
*/
Public DbConnection ()
{
}

/**
* Obtain the Oracle database connection
*/
Public java. SQL. Connection connectDbByJdbcOdbcBridge ()
{
Java. SQL. Connection conn = null;
Try
{
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
Con = DriverManager. getConnection ("jdbc: odbc: ora199", "sr", "sr ");
}
Catch (Exception e)
{
System. out. println ("ERROR:" + e. getMessage ());
}
Return conn;
}
}
In the getConnection method, "ora199" in the first parameter "jdbc: odbc: ora199" is the name of the local ODBC data source, the second and third parameters are the username and password of Oracle respectively.

2. Obtain the Oracle database connection through the connection pool

This section describes how to configure the Oracle database connection pool in iPlanet Application Server 6.5 and Sun Java System Application Server 7, and how to obtain the database connection through the connection pool in applications.

1. iPlanet Application Server 6.5 connection pool Configuration

Open the Management Console of iPlanet Application Server 6.5, select the "database" Panel, select the "External JDBC Drivers" option, and click "Add... "Button, in the pop-up dialog box, add a JDBC Driver named" ora-type4.

Driver Classpath: Enter the physical path of the classes12.zip file.

Then, in "External JDBC DataSources", select "Add... ", Add a data source with the JNDI name" credit2 "in the pop-up dialog box.

DriverType: select the newly added "ora-type4 ";

Datasource: ora199, which is the Oracle Database Service name;

Datasource: ora199, which is the Oracle Database Service name;

Connection Pool Parameters: the default settings are displayed in the figure. You can change these settings according to your environment.

After saving the settings, in "DataSource Selection Box", select the newly added "credit2" data source, and then select "Vendor Specific Properties. Add a URL attribute in the dialog box.

So far, the database connection pool configuration in iPlanet Application Server 6.5 is complete, and the service is restarted to take effect.

2. Sun Java System Application Server 7 connection pool Configuration

Put the classes12.zip file in... /Server1/lib directory. Open the management interface of Sun Java System Application Server 7 through port 4848 of the browser, and select "New…" under "server1"-> "JDBC"-> "Connection Pools... "

Add an Oracle database connection pool named "MyConnectionPool. "Next.

Enter "Datasource Classname" in "General ".

Delete unnecessary Properties in "Properties" and add the "URL" attribute.

Enter the Oracle Database Service name in "CENAME.

The default settings of the following connection pool can be adjusted based on your own environment.

Select "Finish" to complete the connection pool settings.

Next, create a JNDI for the "MyConnectionPool" connection pool so that the application can obtain the connection in the connection pool through this name. "Server1"-> "JDBC"-> "New…" under "JDBC Resources... "

So far, the database connection pool configuration in Sun Java System Application Server7 has been completed, and the service is restarted to take effect.

3. Obtain the connection through the connection pool

The connection pools configured in iPlanet Application Server 6.5 and Sun Java System Application Server7 can use the following database connection classes to obtain Oracle database connections from the connection pool.

/**
* Obtain a database connection from the connection pool
*/

Package com. j2ee. db;

Import java. util .*;
Import java. SQL .*;
Import javax. SQL .*;
Import java. io .*;
Import oracle. jdbc. driver .*;
Import javax. naming .*;

/**
* Obtain the Oracle database connection through the connection pool
*/
Public class DbConnection
{
/**
*
*/
Public DbConnection ()
{
}

/**
* Obtain the Oracle database connection
*/
Public java. SQL. Connection connectDbByConnectionPool ()
{
Java. SQL. Connection conn = null;
Try
{
Context ctx = new InitialContext ();
DataSource ds = (DataSource) ctx. lookup ("jdbc/credit2 ");
Conn = ds. getConnection ();
}
Catch (Exception e)
{
System. out. println ("ERROR:" + e. getMessage ());
}
Return conn;
}
}
4. advantages of using a connection pool the advantages of using a connection pool are mainly reflected in two aspects: Unified configuration, management, monitoring of database connections, and optimization and adjustment of database connection pool parameters,
At the same time, the connection pool manages database connections that are not closed in applications or are not closed for other reasons. This facilitates application migration and backend database switching,
The connection to the database is obtained through the uniform JNDI in the application, but the database connected to the specific machine is irrelevant to the application.