dbmanager.java

数据库通用适配器：实现行粒度的数据库的转换

package hdaty.prtds.rdgadaptor.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbManager {
    public final static String ClassString = "oracle.jdbc.OracleDriver";
    public String connectionString = null;
    public String username = null;
    public String password = null;
    public Connection conn;
    
    public DbManager(String connString) {
        this.connectionString = connString;
    }

    public DbManager(String connString,String username,String password) {
        this.connectionString = connString;
        this.username =username;
        this.password = password;
    }
       
    /**
     * 返回一个Connection，如果错误则返回null
     * 如果连接已经建立，则不会重新建立连接
     */
    public Connection getConnection() {     
        
        try {
            if(conn==null ||conn.isClosed())
            {
                Class.forName(ClassString);
                if ( username == null &&  password== null )
                    conn = DriverManager.getConnection(connectionString);
                else 
                    conn = DriverManager.getConnection(connectionString, username, password);   

                System.out.println("getConnection: " + connectionString);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return conn;         
    }

    /**
     * 执行一个select语句，返回一个记录集ResultSet
     * 使用完毕请使用closeResultSet方法释放资源
     */
    public ResultSet executeQuery(String selectSql) {     
        
        ResultSet rs=null;
        Statement stmt=null;
        getConnection();
        if(conn==null)
            return null;

        try {
            stmt=conn.createStatement();
            rs=stmt.executeQuery(selectSql);            
        } catch (SQLException e) {
            System.out.println("["+this.connectionString+"] Error: "+selectSql);
            e.printStackTrace();
            closeResultSet(rs);
            rs=null;
            stmt=null;
        }
       
        return rs;         
    }

    /**
     * 执行一个insert,update,delete或者DDL语句，返回影响的行数
     * 如果返回行数为负数，表示出错
     */
    public int executeUpdate(String updateSql) {     
        
        int ret=-1;
        Statement stmt=null;

        getConnection();
        if(conn==null)
            return ret;

        try {
             stmt=conn.createStatement();
            ret=stmt.executeUpdate(updateSql);    
            stmt.close();
        } catch (Exception e) {
            System.out.println( "["+this.connectionString+"] Error: "+updateSql);
            e.printStackTrace();    
            try {
                if(stmt!=null && ! stmt.isClosed())
                    stmt.close();
            }
            catch (Exception err) {                
            }
            stmt=null;
            ret=-1;
        }
       
        return ret;         
    }
    
    /**
     * 安全关闭ResultSet及其Statement
     */
    public void closeResultSet(ResultSet rs) {
        
        try {
            if (rs != null ) {
                rs.close();
                    
                if(rs.getStatement()!=null)
                    rs.getStatement().close();  
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            rs=null;
        }

    }
    
    /**
     * 关闭数据库连接
     */
    public void closeConnection() {
 
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
                System.out.println("closeConnection:" + this.connectionString);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{            
            conn=null;            
        }

    }

    //下面是一些可以简化代码的方法
    
    public Object getObject(String select_sql)
    {
        Object ret=null ;

        Statement stmt=null;
        ResultSet result=null;
        
        getConnection();
        try{
            stmt=conn.createStatement();           
            result = stmt.executeQuery(select_sql);
            if (result.next()) {
                ret = result.getObject(1);
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        finally{
            closeResultSet(result);
        }
        
        return ret;
    }
    
    public String getString(String select_sql)
    {
        String ret=null ;

        Statement stmt=null;
        ResultSet result=null;
        
        getConnection();
        try{
            stmt=conn.createStatement();           
            result = stmt.executeQuery(select_sql);
            if (result.next()) {
                ret = result.getString(1);
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        finally{
            closeResultSet(result);
        }
        
        return ret;
    }
    
    public int getInt(String select_sql)
    {
        int ret=-1 ;

        Statement stmt=null;
        ResultSet result=null;
        
        getConnection();
        try{
            stmt=conn.createStatement();           
            result = stmt.executeQuery(select_sql);
            if (result.next()) {
                ret = result.getInt(1);
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        finally{
            closeResultSet(result);
        }
        
        return ret;
    }
    
    public int getMaxInt(String tableName, String fieldName) {
        String sql = "select max(" + fieldName + ") from " + tableName;
        return getInt(sql);
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
    
    }

}