db_sql.java

数据库连接包括连接池。很有用的资料

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 == null)
            stmt = conn.createStatement();
        if(result != null)
        {
            result.close();
            result = null;
        }
        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 == null)
            return null;
        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 != null)
        {
            result.close();
            result = null;
        }
        if(stmt != null)
        {
            stmt.close();
            stmt = null;
        }
        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"), "GB2312") 处理
	 */
    public static String getParameter(HttpServletRequest httpservletrequest, String s)
    {
        try
        {
            if(httpservletrequest.getParameter(s.trim()) != null)
                return new String(httpservletrequest.getParameter(s.trim()).trim().getBytes("ISO8859_1"), "GB2312");
            else
                return null;
        }
        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;
}