The connection with Oracle database in Java application

j2ee|oracle| Data | Database in the Java EE application development, the application and the database connection establishment is one of the problems that we often encounter. Here I talk about connecting Oracle databases through OCI, thin, and jdbcodbc bridges in local applications, iplanet application Server 6.5 and Sun Java System application The configuration of the Oracle database connection pool in Server 7 and how to obtain a connection from the connection pool in the application.

First, local access to Oracle database connections via JDBC

Oracle database connections are available through JDBC in three ways: OCI, thin, and Jdbcodbc bridge mode. The OCI approach relies on a local dynamic-link library, which can be used if Oracle database clients are installed locally, whereas thin is a pure Java database connection, and Jdbcodbc Bridge relies on the configuration of local ODBC database sources, which is generally not used.

1, OCI Way

To install the Oracle client locally first, after installation, you can find the .../jdbc/lib/classes12.zip file in the installation path, where we set the Classes12.zip path in the environment variable CLASSPATH.

The Oracle database connection is then OCI locally through the following database connection classes.

/**
* Get a database connection locally
*/

Package com.j2ee.db;

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

/**
* Access to Oracle database connections via OCI method
*/
public class DbConnection
{
Final static String Sdbdriver = "Oracle.jdbc.driver.OracleDriver";
Final static String sConnStr = "jdbc:oracle:oci8:sr/sr@ora199";

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

/**
* Access to Oracle database connections
*/
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 user name and password for the Oracle user, and "ora199" is the database service name.

2, Thin way

First to the Oracle Technology Network (http://otn.oracle.com/global/cn/software/tech/java/sqlj_jdbc/index.html) to download the Oracle JDBC Drivers, Similarly, the path to the downloaded ZIP file is set in the environment variable CLASSPATH.

The Oracle database connection is then thin locally through the following database connection classes.

/**
* Get a database connection locally
*/

Package com.j2ee.db;

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

/**
* Access to Oracle database connections via thin method
*/
public class DbConnection
{
Privatestring sConnStr = "";

/**
* Default Builder
*/
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;
}

/**
* Access to Oracle database connections via thin method.
*/
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;
}

/**
* Access to Oracle database connections via thin method.
* @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 more flexible, simple, and has strong portability and applicability. Just pay attention to the setting of the specific parameters in the connection string "jdbc:oracle:thin:@10.1.4.199:1521:ora199".

3, JDBCODBC Bridge mode

The local connection to the Oracle database is added first through the data source in the Administration tool, and then the Oracle database connection is obtained locally via the Jdbcodbc Bridge through the following database connection classes.

/**
* Get a database connection locally
*/

Package com.j2ee.db;

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

/**
* Access to Oracle database connections via JDBCODBC Bridge
*/
public class DbConnection
{
/**
*
*/
Public DbConnection ()
{
}

/**
* Access to Oracle database connections
*/
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;
}
}

The "ora199" in the first parameter "jdbc:odbc:ora199" in the Getconnection method is the data source name for the local ODBC data source, and the second and third parameters are Oracle's username and password, respectively.

Second, access to Oracle database connection via connection pool

This section focuses on the configuration of the Oracle database connection pool in iplanet Application Server 6.5 and Sun Java System Application Server 7, and how to obtain a database connection through a connection pool in the application.

1, iPlanet application Server 6.5 Connection pool configuration

To open the Iplanet application Server 6.5 Management console, select the database panel, select the External JDBC Drivers option, click the "Add ..." button, and in the pop-up dialog box, add a name called " Ora-type4 "the JDBC Driver.



Driver Classpath: This parameter fills in the physical path of the Classes12.zip file.

Then select "Add ..." in the "External JDBC datasources" and add a jndi name called "Credit2" in the pop-up dialog box.



Drivertype: Choose just add Good "ora-type4";

datasource:ora199, for Oracle database service name;

Connection Pool Parameters: The default settings are shown in the diagram, and you can change these settings according to your environment.

After you save the settings, in DataSource Selection box, select the Credit2 data source that you just added, and then select the Vendor specific Properties button. Adds a URL property to the dialog.



At this point, the database connection pool in IPlanet application Server 6.5 is configured, and the service is reset to take effect.

2. Configuration of the Sun Java System application Server 7 Connection pool

Place the Classes12.zip file in the .../server1/lib directory before configuring it. Open the management interface of the Sun Java System application Server 7 through the 4848 port of the browser, select "Server1"-> "JDBC"-> "Connection Pools" under "New ..."



Add a pool of Oracle database connections called "Myconnectionpool." "Next" next.



In "General" fill in "Datasource Classname".



Delete unwanted attributes in properties and add the URL property.

The Oracle database service name is filled in "datasourcename".

The default settings for the following connection pools can be adjusted according to the circumstances of your environment.



Select Finish to complete the connection pool settings.

The next step is to create a jndi for the "Myconnectionpool" connection pool so that the application can obtain the connection in the connection pool through that name. "Server1"-> "jdbc"-> "New ..." under "JDBC Resources".



At this point, the database connection pool in the Sun Java System application Server7 is configured, and the service is reset to take effect.

3, through the connection pool to obtain the connection

These connection pools, which are configured in iplanet application Server 6.5 and Sun Java System application Server7, can obtain an Oracle database connection from the connection pool through the following database connection classes.

/**
* Get the 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.*;

/**
* Access to Oracle database connections via connection pooling
*/
public class DbConnection
{
/**
*
*/
Public DbConnection ()
{
}

/**
* Access to Oracle database connections
*/
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, use the advantages of connection pool

The advantages of using connection pooling are mainly embodied in two aspects:

The database connection is unified to configure, manage, monitor, and optimize the parameters of database connection pool, meanwhile, the database connection without shutdown or other reasons is managed by the connection pool.
Easy to use porting and back-end database switching because in the application the database connection is obtained through a unified jndi, and the database on which machine is connected is not relevant to the application.