sqlbridge.java

用java实现的一个bbs的portal

package DBConnection;

import java.lang.* ;
import java.sql.* ;

public class SQLBridge {
    private ConnPool  connPool ;
    private Connection  conn ;
    private ResultSet  rs ;
    private ResultSetMetaData  rsmd ;
    private Statement  stmt ;
    private String  driverName ;
    private String  jdbcURL ;
    private String  username ;
    private String  password ;


    public ResultSet getResultSet()
    {
        return rs;
    }

// -------------------------------------- Constructor --------------------------------------
    public SQLBridge() {
        connPool=null ;
        conn=null ;
        rs=null ;
        rsmd=null ;
        stmt=null ;
    }

// -----------------------------------------------------------------------------------------
    private void clearResult() throws SQLException {
        if( rs!=null ) rs.close() ;
        rs=null ;
        if( stmt!=null ) stmt.close() ;
        stmt=null ;
        rsmd=null ;
    }


    public void closeDB() throws SQLException {
        clearResult() ;
        if( connPool!=null ) {
            connPool.returnConnection() ;
            connPool=null ;
        }
        else {
            if( conn==null )
                throw new SQLException( "This connection has been closed already." ) ;
            if( conn.isClosed() )
                throw new SQLException( "This connection has been closed." ) ;
            conn.close() ;
        }
        conn=null ;
    }


    public int execSQL( String sqlStmt )
            throws SQLException {
        if( conn==null || conn.isClosed() )
            throw new SQLException( "This connection has not been established yet." ) ;
        if( sqlStmt==null )
            throw new SQLException( "SQL-statement is null." ) ;
        clearResult() ;
        conn.setAutoCommit( true ) ;
        stmt=conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY) ;
        if( sqlStmt.toUpperCase().startsWith( "SELECT" ) ) {
            rs=stmt.executeQuery( sqlStmt ) ;
            rsmd=rs.getMetaData() ;
            return -1 ;
        }
        else {
            int numRow=stmt.executeUpdate( sqlStmt ) ;
            clearResult() ;
            return numRow ;
        }
    }


    public void execUpdate( String[] sqlStmts )
            throws SQLException {
        if( conn==null || conn.isClosed() )
        {
            //throw new SQLException( "The connection has not been established yet." );
            System.err.println("The connection has not been established yet." );
        }
        if( sqlStmts==null || sqlStmts.length==0 )
        {
            //throw new SQLException( "SQL-statement is null." );
            System.err.println("SQL-statement is null.");
        }
        clearResult() ;
        conn.setAutoCommit( false ) ;
        try {
            for( int i=0 ; i<sqlStmts.length ; i++ ) {
                stmt=conn.createStatement();
                stmt.executeUpdate( sqlStmts[i] ) ;
                stmt.close() ;
            }
            conn.commit() ;
        }
        catch( SQLException ex ) {
            conn.rollback() ;
            throw ex ;
        }
    }


    public int getColumnCount()
            throws SQLException {
        if( rsmd==null )
            throw new SQLException( "ResultSet is null." ) ;
        return  rsmd.getColumnCount() ;
    }


    public String[] getColumnNames()
            throws SQLException {
        if( rsmd==null )
            throw new SQLException( "ResultSet is null." ) ;
        String[] columnNames=new String[ getColumnCount() ] ;
        for( int i=1 ; i<=columnNames.length ; i++ )
            columnNames[i-1]=rsmd.getColumnName( i ) ;
        return  columnNames ;
    }

    public int getRowCount(String sqlStmt)throws SQLException{
        int count=0;
        stmt=conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
        rs=stmt.executeQuery( sqlStmt ) ;
        while (rs.next())
            count++;
        return count;
    }

    protected Object getField( int column, boolean convertToString )
            throws SQLException {
        if( rs==null || rsmd==null )
            throw new SQLException( "ResultSet is null." ) ;

        switch( rsmd.getColumnType( column ) ) {
            case Types.BIGINT :
                if( convertToString )
                    return String.valueOf( rs.getLong(column) ) ;
                else
                    return new Long( rs.getLong(column) ) ;

            case Types.BINARY :
                if( convertToString )
                    return Byte.toString( rs.getByte(column) ) ;
                else
                    return new Byte( rs.getByte(column) ) ;

            case Types.BIT :
                if( convertToString )
                    return String.valueOf( rs.getBoolean(column) ) ;
                else
                    return new Boolean( rs.getBoolean(column) ) ;

            case Types.CHAR :
                return rs.getString(column) ;

            case Types.DATE :
                if( convertToString )
                    return ( rs.getDate(column) ).toString() ;
                else
                    return rs.getDate(column) ;

            case Types.DECIMAL :
                if( convertToString )
                    return ( rs.getBigDecimal( column, rsmd.getScale(column) ) ).toString() ;
                else
                    return rs.getBigDecimal( column, rsmd.getScale(column) );

            case Types.DOUBLE :
                if( convertToString )
                    return String.valueOf( rs.getDouble(column) ) ;
                else
                    return new Double( rs.getDouble(column) ) ;

            case Types.FLOAT :
                if( convertToString )
                    return String.valueOf( rs.getDouble(column) ) ;
                else
                    return new Float( rs.getDouble(column) ) ;

            case Types.INTEGER :
                if( convertToString )
                    return String.valueOf( rs.getInt(column) ) ;
                else
                    return new Integer( rs.getInt(column) ) ;

            case Types.LONGVARBINARY :
                if( convertToString )
                    return ( rs.getBinaryStream(column) ).toString() ;
                else
                    return rs.getBinaryStream(column) ;

            case Types.LONGVARCHAR :
                return rs.getString(column) ;

            case Types.NULL :
                if( convertToString )
                    return "NULL" ;
                else
                    return null ;

            case Types.NUMERIC :
                if( convertToString )
                    return ( rs.getBigDecimal( column, rsmd.getScale(column) ) ).toString() ;
                else
                    return rs.getBigDecimal( column, rsmd.getScale(column) ) ;

            case Types.REAL :
                if( convertToString )
                    return String.valueOf( rs.getFloat(column) ) ;
                else
                    return new Float( rs.getFloat(column) ) ;

            case Types.SMALLINT :
                if( convertToString )
                    return String.valueOf( rs.getShort(column) ) ;
                else
                    return new Short( rs.getShort(column) ) ;

            case Types.TIME :
                if( convertToString )
                    return ( rs.getTime(column) ).toString() ;
                else
                    return rs.getTime(column) ;

            case Types.TIMESTAMP :
                if( convertToString )
                    return ( rs.getTimestamp(column) ).toString() ;
                else
                    return rs.getTimestamp(column) ;

            case Types.TINYINT :
                if( convertToString )
                    return String.valueOf( rs.getByte(column) ) ;
                else
                    return new Byte( rs.getByte(column) ) ;

            case Types.VARBINARY :
                if( convertToString )
                    return ( rs.getBytes(column) ).toString() ;
                else
                    return rs.getBytes(column) ;

            case Types.VARCHAR :
                return rs.getString(column) ;

            default :
                if( convertToString )
                    return ( rs.getObject(column) ).toString() ;
                else
                    return rs.getObject(column) ;
        }
    }


    public Object getField( int column )
            throws SQLException {
        return getField( column, false ) ;
    }


    public Object getField( String fieldName )
            throws SQLException {
        return getField( rs.findColumn( fieldName ) ,false ) ;
    }


    public String getFieldString( int column )
            throws SQLException {
        return (String)getField( column, true ) ;
    }


    public String getFieldString( String fieldName )
            throws SQLException {
        return (String)getField( rs.findColumn( fieldName ) ,true ) ;
    }


    public boolean nextRow()
            throws SQLException {
        if( rs==null )
            throw new SQLException( "ResultSet is null." ) ;
        return rs.next() ;
    }

    public void openDB( String drvName ,String url ,
                        String uname ,String passwd )
            throws SQLException {
        if( conn!=null && !conn.isClosed() )
            throw new SQLException( "The connection has been established already." ) ;
        clearResult() ;
        try {
            Class.forName( drvName ) ;
        }
        catch( ClassNotFoundException ex ) {
            throw new SQLException( ex.toString() ) ;
        }
        conn=DriverManager.getConnection( url ,uname ,passwd ) ;
    }


    public void openDB( )
            throws SQLException {
        clearResult() ;
        connPool=ConnPool.getInstance() ;
        conn=connPool.getConnection() ;
    }


    public void setConnectionSwitch( String on_off )
            //throws ServletException {
            throws Exception {
        try {
            if( on_off.equalsIgnoreCase( "ON" ) ) {
                openDB( ) ;
            }
            else if( on_off.equalsIgnoreCase( "OFF" ) )
                closeDB() ;
        }
        catch( SQLException ex ) {
            //throw new ServletException( ex.toString() ) ;
            throw new Exception( ex.toString() ) ;
        }
    }


    public int getResultCount(){
        try {
            rs.last();
            return rs.getRow();
        }
        catch (Exception ex) {
            ex.printStackTrace();
            return -1;
        }
    }

    public void setCursorFirst(int intPage,int intPageSize){
        //将记录指针定位到待显示页的第一条记录上
        try {
            rs.absolute((intPage-1) * intPageSize+1);
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public boolean RsIsAfterLast(){
        try {
            return rs.isAfterLast();
        }
        catch (Exception ex) {
            ex.printStackTrace();
            return true;
        }
    }

    public void setConnPool( ConnPool pool ) {
        connPool=pool ;
    }


    public void setDriverName( String drvName ) {
        driverName=drvName ;
    }


    public void setJdbcURL( String url ) {
        jdbcURL=url ;
    }


    public void setUserName( String uname ) {
        username=uname ;
    }


    public void setPassword( String passwd ) {
        password=passwd ;
    }


    static public void main(String arg[]){

        ConnPool connPool=new ConnPool();
        /*connPool.setDriverName("sun.jdbc.odbc.JdbcOdbcDriver");
        connPool.setJdbcURL("jdbc:odbc:db1");
        connPool.setPassword("");
        connPool.setUserName("");
        */
        connPool.setPropsFileName("db.properties");
        try{
            connPool.setConnectionSwitch("ON");
        }
        catch (Exception e){
        }
        SQLBridge sqlBridge=new SQLBridge();
        sqlBridge.setConnPool(connPool);
        try{
            sqlBridge.setConnectionSwitch("ON");
        }
        catch (Exception e){
        };
    }
}