There are two ways to connect to Oracle in JSP:
1, Oracle JDBC OCI8 mode
2, Oracle JDBC's Thin mode
I prefer the second type, because the web publishing server and database server generally will not be placed in the same computer, and when using the Thin mode, the web server is not required to install Oracle clients.
We first configure the environment before you first pass the code. In the Database server installed in the Oracle, find the Oracle installation directory, then copy the JDBC / lib / class12.jar file in the directory to a directory of the web publishing server. Suppose it is placed directly in the C: / root directory, then add the path to the value called 'ClassPath' in the 'System - Advanced - Environment Variable', such as: D: / Program Files / Sqllib / Java / db2java.zip; d: / program files / sqllib / java / runtime.zip; c: classes12.jar; i.e., Java can find this package.
After configuring the environment, we start to start writing the code. With regard to the code of the database connection, you should write a specialized connection class to call, it is not necessary to write some articles on the network, write directly to the JSP code.
The code about the connection is simple.
Private connection newconnection (String User, String Password) {
Connection con = NULL;
Try {
Class.Forname ("Oracle.jdbc.driver.OracleDriver"). NewInstance ();
Con = DriverManager.getConnection ("JDBC: Oracle: Thin: @ 192.168.96.1: 1521: Oracle9i", User, Password);
}
Catch (SQLException E) {
Return NULL;
}
Return con;
}
If the account password is not wrong, the function should be able to return a available connection. But so simple connection is used in a project, it is far from being achieved. We can add more features in this database connection class, such as connecting pools, and more. Below I will list the code of the database connection class, you can refer to the reference.
/ *
* @Title Company website
* @Author: zf
* @Version 1.0
* @Memo: Define database connection and its database connection pool, etc.
* /
Package com.kingson.db;
Import java.io. *;
Import java.sql. *;
Import java.util. *;
Import java.util.date;
Public class dbconnectionManager {
Static private dbconnectionManager instance; // unique example
Static Private Int Clients;
Private vector drivers = new vector ();
Private printwriter log;
Private hashtable pools = new hashtable ();
/ **
* Return the only instance. If this method is called for the first time, an instance is created.
*
* @Return DBConnectionManager Unique instance
* /
Static synchronized public dbconnectionManager getInstance () {
IF (instance == null) {
Instance = new dbconnectionManager ();
CLIENTS ;
Return Instance;
}
/ **
* Structure function private to prevent other objects from creating this type of instance
* /
Private dbconnectionmanager () {
INIT ();
}
/ **
* Return the connection object to the connection pool specified by the name
*
* @Param Name The connection pool name defined in the properties file
* @PARAM CON connection object
* /
Public void freeconnection (string name, connection con) {
DBConnectionPool Pool = (dbconnectionpool) pools.get (name);
IF (pool! = null) {
Pool.FreeConnection (con);
}
}
/ **
* Get a available (idle) connection. If no connection is available, the number of connections is less than the maximum connection
* Restriction, create and return new connection
*
* @Param Name The connection pool name defined in the properties file
* @Return Connection Available Connection or NULL
* /
Public Connection getConnection (String name) {
DBConnectionPool Pool = (dbconnectionpool) pools.get (name);
IF (pool! = null) {
Return pool.getConnection ();
}
Return NULL;
}
/ **
* Get a available connection. If there is no connection available, and the existing number of connections is less than the maximum number of connections,
* Create and return new connections. Otherwise, wait for other thread release connections within the specified time.
*
* @Param Name connection pool name
* @Param Time Waiting time in millisecond
* @Return Connection Available Connection or NULL
* /
Public Connection getConnection (String Name, long time) {
DBConnectionPool Pool = (dbconnectionpool) pools.get (name);
IF (pool! = null) {
Return Pool.getConnection (TIME);
}
Return NULL;
}
/ **
* Close all connections, revoke the registration of the driver
* /
Public synchronized void release () {
// Wait until the last client program is called
IF (--Clients! = 0) {
Return;
}
Enumeration allpools = pools.ements ();
While (allpools.hasmorelements ()) {
DBConnectionPool Pool = (dbconnectionpool) Allpools.nexTelement ();
pool.release ();
}
Enumeration alldrivers = drivers.ements ();
While (alldrivers.hasmorelements ()) {
Driver driver = (driver) alldrivers.nextelement ();
Try {
Drivermanager.deregisterDriver (driver);
LOG ("Undo JDBC Driver" Driver.getClass (). GetName () "Registration");
}
Catch (SQLException E) {
Log (e, "Unable to revoke the following JDBC driver registration:" Driver.getClass (). getname ());}
}
}
/ **
* Create a connection pool instance based on the specified property.
*
* @Param PROPS connection pool properties
* /
Private void createpools (property prots) {
ENUMERATION PROPNAMES = props.propertynames ();
While (propNames.haASMoreElements ()) {
String name = (string) propNames.nexTelement ();
IF (name.endswith (". URL")) {
String poolname = name.substring (0, name.lastIndexof ("."));
String url = props.getproperty (poolname ".url);
IF (URL == Null) {
LOG ("No Pool" PoolName "Specify URL");
CONTINUE;
}
String user = props.getProperty (poolname ".user");
String password = props.getproperty (poolname ".password");
String dbip = props.getProperty (poolname ".db_ip", "192.168.96.1);
String dbport = props.getProperty (poolname ".db_port", "1521");
String dbuid = props.getProperty (poolname ".db_uid", "oracle9i");
String MaxConn = Props.getProperty (poolname ".maxconn", "0");
// Connection information
String dbinfo = user "/" password "@" dbip ":" DBPORT ":" DBUID;
Int max;
Try {
Max = integer.Valueof (MaxConn) .intValue ();
}
Catch (NumberFormatexception E) {
LOG ("Error's maximum connection limit:" MaxConn ". Connection pool:" poolname);
MAX = 0;
}
DBConnectionPool Pool = New DBConnectionPool (PoolName, URL, DBINFO, MAX);
Pools.Put (PoolName, Pool);
LOG ("successfully created a connection pool" poolname);
}
}
}
/ **
* Read attribute complete initialization
* /
Private vidinit () {
INPUTSTREAM IS = getClass (). GetResourceAsStream ("db.properties"); Properties dbprops = new property ();
Try {
DBProps.Load (IS);
}
Catch (Exception E) {
System.err.Println ("You cannot read the property file."
"Please make sure DB.Properties in the path specified by classpath");
Return;
}
String logfile = dbprops.getProperty ("logfile", "newslog.txt");
Try {
LOG = New PrintWriter (New Filewriter (Logfile, True), TRUE
}
Catch (IOException E) {
System.err.Println ("Unable to open the log file:" logfile);
Log = New PrintWriter (System.err);
}
LoadDrivers (dbprops);
CreatePools (dbprops);
}
/ **
* Load and register all JDBC drivers
*
* @Param PROPS attribute
* /
Private void loadingDrivers (property props) {
String driverclasses = props.getProperty ("driver");
StringTokenizer ST = New StringTokenizer (DriverClasses);
while (st.hasmorelements ()) {
String driverclassname = st.nextToken (). TRIM ();
Try {
Driver driver = (driver)
Class.Forname (driverclassname) .newinstance ();
Drivermanager.RegisterDriver (driver);
Drivers.addeElement (driver);
LOG ("successfully registered JDBC driver" driverclassname);
}
Catch (Exception E) {
LOG ("Unable to register the JDBC driver:"
Driverclassname ", error:" e);
}
}
}
/ **
* Write text information to log files
* /
Private void log (String MSG) {
Log.println (New Date () ":" MSG);
}
/ **
* Write text information with exceptions to log files
* /
Private Void log (Throwable E, STRING MSG) {
Log.println (New Date () ":" MSG);
E.PrintStackTrace (LOG);
}
/ ************** Contact pool class *************************************** ****************** /
/ **
* This internal class defines a connection pool. It can create a new connection as required until the predetermined most
* Dalian Digital. Before returning to the client, it can verify the validity of the connection.
* /
Class dbconnectionpool {
PRIVATE INT CHECKEDOUT;
Private vector freeconnections = new vector (); private int mapconn;
PRIVATE STRING NAME;
Private string URL;
PRIVATE STRING DBINFO;
/ **
* Create a new connection pool
*
* @Param Name connection pool name
* @Param URL database JDBC URL
* @PARAM DBINFO Database Connection Information
* @Param MaxConn This connection pool allows the maximum number of connections to be established
* /
Public dbconnectionPool (String Name, String Url, String Dbinfo, Int Maxconn) {
THIS.NAME = Name;
this.url = URL;
THIS.DBINFO = DBINFO;
THIS.MAXCONN = MaxConn;
}
/ **
* Return to the connection pool will not be used.
*
* @Param CON client release connection
* /
Public Synchronized Void FreeConnection (Connection Con) {
// Add the specified connection to the end of the vector
FreeConnections.AddeElement (con);
Checkedout -;
NotifyAll ();
}
/ **
* A available connection from the connection pool. If there is no idle connection, the current connection is less than the maximum connection
* Summary, create a new connection. If the connection to the available is no longer valid, it is deleted from the vector.
* Then recursively call yourself to try new available connections.
* /
Public synchronized connection getConnection () {
Connection con = NULL;
IF (FreeConnections.Size ()> 0) {
// Get the first available connection in the vector
Con = (connection) freeConnections.FirstEth ();
FreeConnections.RemoveElementat (0);
Try {
IF (con?isclosed ()) {
LOG ("From the connection pool" Name "deletes an invalid connection");
// Remissive call yourself, try again to get available connections
CON = getConnection ();
}
}
Catch (SQLException E) {
LOG ("From the connection pool" Name "deletes an invalid connection");
// Remissive call yourself, try again to get available connections
CON = getConnection ();
}
}
Else IF (MaxConn == 0 || Checkedout CON = NewConnection (); } IF (con! = null) { CHECKEDOUT ; } Return con; } / ** * Get available connections from the connection pool. You can specify the maximum time you can wait for the client. * See the previous getConnection () method. * * @Param Timeout Limits in milliseconds * / Public synchronized connection getConnection (long timeout) { Long StartTime = New Date (). gettime (); CONNECTION CON; While (con = getConnection ()) == null) { Try { WAIT (TIMEOUT); } Catch (InterruptedException E) {} IF ((New Date (). gettime () - starttime)> = timeout) { The reason for the returning of // Wait () is timeout Return NULL; } } Return con; } / ** * Close all connections * / Public synchronized void release () { Enumeration allConnections = freeConnections.efficient (); While (allConnections.haASMoreElements ()) { Connection Con = (Connection) AllConnections.nexTelement (); Try { C. close (); LOG ("Close a connection in the connection pool" Name "); } Catch (SQLException E) { LOG (e, "cannot close the connection in the connection pool" Name "); } } FreeConnections.RemoveAllelements (); } / ** * Create a new connection * / PRIVATE connection newconnection () { Connection con = NULL; Try { Con = DriverManager.getConnection (URL DBINFO); LOG ("Connection pool" Name "creates a new connection"); } Catch (SQLException E) { Log (e, "Unable to create the connection:" URL); Return NULL; } Return con; } } } See the full code: http://www.vchelp.net/asp/ibr_upload/1299.rar
