| | |
| | |
import java.io.Reader;
import java.sql.*;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
public class Db_sql
{
/**
* 构造函数，new Db_sql 的时候执行，调用 connect() 连接ORACLE数据库
*/
public Db_sql(String s, String s1, String s2, String s3, String s4)
throws Exception
{
isClosed = false;
host = s.trim();
port = s1.trim();
sid = s2.trim();
user = s3.trim();
password = s4.trim();
connMgr = DBConnectionManager.getInstance();
connect();
}
/**
* 连接ORACLE数据库
*/
public boolean connect()
throws Exception
{
String s = "jdbc:oracle:thin:@" + host + ":" + port + ":" + sid;
conn = connMgr.getConnection(user, s, user, password);
return true;
}
/**
* 是否自动 COMMIT
*/
public void setAutoCommit(boolean flag)
throws SQLException
{
bAutoCommit = flag;
conn.setAutoCommit(flag);
}
/**
* 没有设置成自动 COMMIT ，调用该方法才会 COMMIT
*/
public void commit()
throws SQLException
{
if(!bAutoCommit)
conn.commit();
}
/**
* 没有设置成自动 COMMIT ，调用该方法才会 ROLL BACK
*/
public void rollback()
throws SQLException
{
if(!bAutoCommit)
conn.rollback();
}
/**
* 执行 SQL ，返回执行结果 TRUE/FALSE
*/
public ResultSet query(String s)
throws Exception
{
if(stmt == )
stmt = conn.createStatement();
if(result != )
{
result.close();
result = ;
}
result = stmt.executeQuery(s);
return result;
}
public void queryLarge(String s, String s1)
throws Exception
{
stmt.execute(s);
ResultSet resultset = stmt.getResultSet();
if(resultset.next())
{
CLOB clob = ((OracleResultSet)resultset).getCLOB(1);
clob.putChars(1L, s1.toCharArray());
}
resultset.close();
}
/**
* 把结果集里的指针下移一位
*/
public boolean next()
throws SQLException
{
return result.next();
}
/**
* 取得当前记录的 INT 类型字段值，前后去空格
*/
public int getInt(String s)
throws SQLException
{
return result.getInt(s.trim());
}
/**
* 取得当前记录的 STRING 类型字段值，前后去空格
*/
public String getString(String s)
throws SQLException
{
return result.getString(s.trim());
}
/**
* 取得当前记录的 SHORT 类型字段值，前后去空格
*/
public short getShort(String s)
throws SQLException
{
return result.getShort(s.trim());
}
/**
* 取得当前记录的 LONG 类型字段值，前后去空格
*/
public long getLong(String s)
throws SQLException
{
return result.getLong(s.trim());
}
/**
* 取得当前记录的 DATE 类型字段值，前后去空格
*/
public Date getDate(String s)
throws SQLException
{
return result.getDate(s.trim());
}
/**
* 取得当前记录的 TIME 类型字段值，前后去空格
*/
public Time getTime(String s)
throws SQLException
{
return result.getTime(s.trim());
}
/**
* 取得当前记录的 FLOAT 类型字段值，前后去空格
*/
public float getFloat(String s)
throws SQLException
{
return result.getFloat(s.trim());
}
/**
* 取得当前记录的 DOUBLE 类型字段值，前后去空格
*/
public double getDouble(String s)
throws SQLException
{
return result.getDouble(s.trim());
}
/**
* 取得当前记录的 BOOLEAN 类型字段值，前后去空格
*/
public boolean getBoolean(String s)
throws SQLException
{
return result.getBoolean(s.trim());
}
/**
* 取得当前记录的 CLOB 类型字段值
*/
public String getText(String s)
throws SQLException
{
String s1 = "";
char ac[] = new char[200];
CLOB clob = (CLOB)result.getObject(s);
if(clob == )
return ;
Reader reader = clob.getCharacterStream();
int i;
try
{
while((i = reader.read(ac, 0, 200)) != -1)
s1 = s1 + new String(ac, 0, i);
}
catch(Exception exception1)
{
throw new SQLException(exception1.getMessage());
}
finally
{
try
{
reader.close();
}
catch(Exception _ex) { }
}
return s1;
}
/**
* 关闭数据库连接，执行 COMMIT，RELEASE 动作
*/
public boolean close()
throws SQLException
{
if(result != )
{
result.close();
result = ;
}
if(stmt != )
{
stmt.close();
stmt = ;
}
conn.setAutoCommit(true);
connMgr.freeConnection(user, conn);
connMgr.release(user);
isClosed = true;
return true;
}
/**
* 没有调用 close() 时，执行 close()
*/
protected void finalize()
throws SQLException
{
if(!isClosed)
close();
}
/**
* 取得 HTTP 参数值，所有得到的值都做了
* String (request.getParameter(s.trim()).trim().getBytes("ISO8859_1"),
*/
public static String getParameter(HttpServletRequest httpservletrequest,
{
try
{
if(httpservletrequest.getParameter(s.trim()) != )
return new String(httpservletrequest.getParamete
r(s.trim()).trim().getBytes("ISO8859_1"), "GB2312");
else
return ;
}
catch(Exception _ex)
{
return httpservletrequest.getParameter(s.trim());
}
}
private String host;
private String port;
private String database;
private String user;
private String password;
private String sid;
private boolean bAutoCommit;
public ResultSet result;
public Statement stmt;
public int affectedRows;
public Connection conn;
private boolean isClosed;
private DBConnectionManager connMgr;
}
--------------------------------------------------------
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
public class DBConnectionManager {
static private DBConnectionManager instance; // The single instance
private Vector drivers = new Vector();
private PrintWriter log;
private Hashtable pools = new Hashtable();
private Hashtable clients = new Hashtable();
/**
* A private constructor since this is a Singleton
*/
private DBConnectionManager()
throws SQLException {
init();
}
/**
* Returns the single instance, creating one if it's the
* first time this method is called.
*
* @return DBConnectionManager The single instance.
*/
public static synchronized DBConnectionManager getInstance()
throws SQLException
{
if (instance == ) {
instance = new DBConnectionManager();
}
return instance;
}
/**
* Returns a connection to the named pool.
*
* @param name The pool name as defined in the properties file
* @param con The Connection
*/
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != ) {
pool.freeConnection(con);
}
}
/**
* Returns an open connection. If no one is available, and the max
* number of connections has not been reached, a new connection is
* created.
*
* @param name The pool name as defined in the properties file
* @return Connection The connection or
*/
public Connection getConnection(String name,String url,
String username,String password) throws SQLException {
if (!clients.containsKey(name))
{
createPools(name,url,username,password,0);
clients.put(name,new Integer(0));
}
int iCurr = ((Integer)clients.get(name)).intValue();
iCurr++;
clients.put(name,new Integer(iCurr));
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != ) {
return pool.getConnection();
}
return ;
}
/**
* Closes all open connections and deregisters all drivers.
*/
public synchronized void release(String name) throws SQLException {
// Wait until called by the last client
if (!clients.containsKey(name)) return;
int iCurr = ((Integer)clients.get(name)).intValue();
iCurr--;
clients.put(name,new Integer(iCurr));
if (iCurr > 0) return;
clients.remove(name);
DBConnectionPool pool = (DBConnectionPool)pools.get(name);
pool.release();
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver) allDrivers.nextElement();
DriverManager.deregisterDriver(driver);
}
}
private void createPools(String poolName,String url,
String username,String password,int max) {
DBConnectionPool pool =
new DBConnectionPool(poolName,url,username,password, max);
pools.put(poolName, pool);
log("Initialized pool " + poolName);
}
/**
* Loads properties and initializes the instance with its values.
*/
private void init() throws SQLException {
log = new PrintWriter(System.err);
loadDrivers();
//createPools(0);
}
/**
* Loads and registers all JDBC drivers. This is done by the
* DBConnectionManager, as opposed to the DBConnectionPool,
* since many pools may share the same driver.
*
* @param props The connection pool properties
*/
private void loadDrivers() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
}
/**
* Writes a message to the log file.
*/
private void log(String msg) {
log.println(new Date() + ": " + msg);
}
/**
* Writes a message with an Exception to the log file.
*/
private void log(Throwable e, String msg) {
log.println(new Date() + ": " + msg);
e.printStackTrace(log);
}
/**
* This inner class represents a connection pool. It creates new
* connections on demand, up to a max number if specified.
* It also makes sure a connection is still open before it is
* returned to a client.
*/
class DBConnectionPool {
private int checkedOut;
private Vector freeConnections = new Vector();
private int maxConn;
private String name;
private String password;
private String URL;
private String user;
/**
* Creates new connection pool.
*
* @param name The pool name
* @param URL The JDBC URL for the database
* @param user The database user, or
* @param password The database user password, or
* @param maxConn The maximal number of connections, or 0
* for no limit
*/
public DBConnectionPool(String name, String URL, String user,
String password,int maxConn) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
/**
* Checks in a connection to the pool. Notify other Threads that
* may be waiting for a connection.
*
* @param con The connection to check in
*/
public synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
freeConnections.addElement(con);
checkedOut--;
notifyAll();
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
*/
public synchronized Connection getConnection() throws SQLException {
Connection con = ; if (freeConnections.size() > 0) {
// Pick the first Connection in the Vector
// to get round-robin usage
con = (Connection) freeConnections.firstElement();
freeConnections.removeElementAt(0);
if (con.isClosed()) {
log("Removed bad connection from " + name);
// Try again recursively
con = getConnection();
}
} else if (maxConn == 0 || checkedOut < maxConn) {
con = newConnection();
}
if (con != ) {
checkedOut++;
}
return con;
}
/**
* Closes all available connections.
*/
public synchronized void release() throws SQLException {
Enumeration allConnections = freeConnections.elements();
while (allConnections.hasMoreElements()) {
Connection con = (Connection) allConnections.nextElement();
con.close();
}
freeConnections.removeAllElements();
}
/**
* Creates a new connection, using a userid and password
* if specified.
*/
private Connection newConnection() throws SQLException {
Connection con = ;
if (user == ) {
con = DriverManager.getConnection(URL);
}
else {
con = DriverManager.getConnection(URL, user, password);
}
log("Created a new connection in pool " + name);
return con;
}
}
}
| |
|
|
| |
| |
|
