一、前言

基于dbcon-5.0.1.jar.zip开源包的数据库工具类uk.ac.sanger.cgp.dbcon.util.DatabaseUtils对数据连接有效关闭closeDbObject、数据库类型判断isOracle、数据库账号登录验证isUrlValid及获取验证后有效连接配置getValidConnectionURL等，具体见如下源码说明。

二、源码说明

1.DatabaseUtils工具类

package uk.ac.sanger.cgp.dbcon.util;@b@@b@import java.sql.Connection;@b@import java.sql.DatabaseMetaData;@b@import java.sql.DriverManager;@b@import java.sql.ResultSet;@b@import java.sql.SQLException;@b@import java.sql.Statement;@b@import org.apache.commons.logging.Log;@b@import org.apache.commons.logging.LogFactory;@b@import uk.ac.sanger.cgp.dbcon.config.Config;@b@import uk.ac.sanger.cgp.dbcon.exceptions.DbConException;@b@@b@public class DatabaseUtils@b@{@b@  private static final Log LOG = LogFactory.getLog(DatabaseUtils.class);@b@@b@  public static void connectionSettings(Connection conn)@b@  {@b@    try@b@    {@b@      conn.setAutoCommit(false);@b@      conn.rollback();@b@    }@b@    catch (SQLException e) {@b@      throw new DbConException("Error whilst setting autoCommit off and rollbacking the connection", e);@b@    }@b@  }@b@@b@  public static void closeDbObject(Connection conn)@b@  {@b@    if (conn != null)@b@      try {@b@        conn.rollback();@b@      }@b@      catch (SQLException e) {@b@        LOG.warn("Could not rollback java.sql.Connection", e);@b@      }@b@      finally {@b@        try {@b@          conn.close();@b@        }@b@        catch (SQLException e) {@b@          LOG.warn("Could not close java.sql.Connection", e);@b@        }@b@      }@b@  }@b@@b@  public static void closeDbObject(Statement st)@b@  {@b@    if (st != null)@b@      try {@b@        st.close();@b@      }@b@      catch (SQLException e) {@b@        LOG.warn("Could not close java.sql.Statement", e);@b@      }@b@  }@b@@b@  public static void closeDbObject(ResultSet rs)@b@  {@b@    if (rs != null)@b@      try {@b@        rs.close();@b@      }@b@      catch (SQLException e) {@b@        LOG.warn("Could not close java.sql.ResultSet", e);@b@      }@b@  }@b@@b@  public static boolean isOracle(Connection conn)@b@    throws SQLException@b@  {@b@    return conn.getMetaData().getDriverName().matches("^Oracle.+");@b@  }@b@@b@  public static boolean isFixRequiredForOracleVersion(Connection conn)@b@    throws SQLException@b@  {@b@    int majorVersion = conn.getMetaData().getDriverMajorVersion();@b@    if (majorVersion > 10) throw new DbConException("Unknown Oracle driver major version: cannot determine whether to apply V8 compatibility");@b@    return (majorVersion == 10);@b@  }@b@@b@  public static boolean setOraclePropertyForBugFix(Connection conn)@b@    throws SQLException@b@  {@b@    if ((!(Boolean.getBoolean("oracle.jdbc.V8Compatible"))) && (isOracle(conn)) && (isFixRequiredForOracleVersion(conn)))@b@    {@b@      System.setProperty("oracle.jdbc.V8Compatible", "true");@b@      return true;@b@    }@b@@b@    return false;@b@  }@b@@b@  public static String getValidConnectionURL(Config config)@b@    throws DbConException@b@  {@b@    String validUrl = "";@b@@b@    if (isUrlValid(config.getUrl(), config.getUsername(), config.getPassword(), config.getDriver())) {@b@      validUrl = config.getUrl();@b@    }@b@    else if ((config.getBackupUrl() != null) && (isUrlValid(config.getBackupUrl(), config.getUsername(), config.getPassword(), config.getDriver()))) {@b@      validUrl = config.getBackupUrl();@b@    }@b@    else {@b@      throw new DbConException("No valid URL could be found for DB " + config.getName());@b@    }@b@@b@    config.setWorkingUrl(validUrl);@b@@b@    return validUrl;@b@  }@b@@b@  public static boolean isUrlValid(String url, String username, String password, String driver)@b@  {@b@    boolean okay = false;@b@    Connection conn = null;@b@    try {@b@      Class.forName(driver);@b@      conn = DriverManager.getConnection(url, username, password);@b@      okay = true;@b@    }@b@    catch (ClassNotFoundException e) {@b@      if (LOG.isDebugEnabled()) LOG.debug("Cannot find class", e);@b@      okay = false;@b@    }@b@    catch (SQLException e) {@b@      if (LOG.isDebugEnabled()) LOG.debug("SQLException issue", e);@b@      okay = false;@b@    }@b@    finally {@b@      closeDbObject(conn);@b@    }@b@@b@    return okay;@b@  }@b@}

2.Config数据连接配置类

package uk.ac.sanger.cgp.dbcon.config;@b@@b@import java.io.Serializable;@b@@b@public class Config@b@  implements Serializable, Cloneable@b@{@b@  private static final long serialVersionUID = 8255104234840048031L;@b@  public static final byte FAIL = 0;@b@  public static final byte BLOCK = 1;@b@  public static final byte GROW = 2;@b@  private int maxActive = 0;@b@  private int maxIdle = 0;@b@  private int numTestsPerEvictionRun = 0;@b@  private byte exhausted = 0;@b@  private long maxWait = 0L;@b@  private long timeBetweenEvictRun = 0L;@b@  private long minEvictTime = 0L;@b@  private int cachedPreparedStatements = 0;@b@  private boolean testOnBorrow = false;@b@  private boolean testOnReturn = false;@b@  private boolean testWhileIdle = false;@b@  private String url = null;@b@  private String backupUrl = null;@b@  private String username = null;@b@  private String password = null;@b@  private String name = null;@b@  private String validationQuery = null;@b@  private String driver = null;@b@  private String workingUrl = null;@b@@b@  private Config()@b@  {@b@  }@b@@b@  public static Config createEmptyConfig()@b@  {@b@    return new Config();@b@  }@b@@b@  public static Config createBasicPoolConfig(int maxActive, byte exhausted, long maxWait, int maxIdle, boolean testOnBorrow, boolean testOnReturn, long timeBetweenEvictRun, int numTestsPerEvictionRun, long minEvictTime, boolean testWhileIdle, String url, String username, String password, String name, String validationQuery, String driver, int cachedPreparedStatements)@b@  {@b@    Config config = new Config(maxActive, exhausted, maxWait, maxIdle, testOnBorrow, testOnReturn, timeBetweenEvictRun, numTestsPerEvictionRun, minEvictTime, testWhileIdle, url, username, password, name, validationQuery, driver, null, cachedPreparedStatements);@b@@b@    return config;@b@  }@b@@b@  public static Config createBasicPoolConfigWithBackup(int maxActive, byte exhausted, long maxWait, int maxIdle, boolean testOnBorrow, boolean testOnReturn, long timeBetweenEvictRun, int numTestsPerEvictionRun, long minEvictTime, boolean testWhileIdle, String url, String username, String password, String name, String validationQuery, String driver, String backupUrl, int cachedPreparedStatements)@b@  {@b@    Config config = new Config(maxActive, exhausted, maxWait, maxIdle, testOnBorrow, testOnReturn, timeBetweenEvictRun, numTestsPerEvictionRun, minEvictTime, testWhileIdle, url, username, password, name, validationQuery, driver, backupUrl, cachedPreparedStatements);@b@@b@    return config;@b@  }@b@@b@  private Config(int maxActive, byte exhausted, long maxWait, int maxIdle, boolean testOnBorrow, boolean testOnReturn, long timeBetweenEvictRun, int numTestsPerEvictionRun, long minEvictTime, boolean testWhileIdle, String url, String username, String password, String name, String validationQuery, String driver, String backupUrl, int cachedPreparedStatements)@b@  {@b@    this.maxActive = maxActive;@b@    this.exhausted = exhausted;@b@    this.maxWait = maxWait;@b@    this.maxIdle = maxIdle;@b@    this.testOnBorrow = testOnBorrow;@b@    this.testOnReturn = testOnReturn;@b@    this.timeBetweenEvictRun = timeBetweenEvictRun;@b@    this.numTestsPerEvictionRun = numTestsPerEvictionRun;@b@    this.minEvictTime = minEvictTime;@b@    this.testWhileIdle = testWhileIdle;@b@    this.url = url;@b@    this.username = username;@b@    this.password = password;@b@    this.name = name;@b@    this.validationQuery = validationQuery;@b@    this.driver = driver;@b@    this.backupUrl = backupUrl;@b@    this.cachedPreparedStatements = cachedPreparedStatements;@b@  }@b@@b@  public Object clone()@b@  {@b@    Config clone = new Config(this.maxActive, this.exhausted, this.maxWait, this.maxIdle, this.testOnBorrow, this.testOnReturn, this.timeBetweenEvictRun, this.numTestsPerEvictionRun, this.minEvictTime, this.testWhileIdle, this.url, this.username, this.password, this.name, this.validationQuery, this.driver, this.backupUrl, this.cachedPreparedStatements);@b@@b@    return clone;@b@  }@b@@b@  public int getMaxActive() {@b@    return this.maxActive;@b@  }@b@@b@  public void setMaxActive(int maxActive) {@b@    this.maxActive = maxActive;@b@  }@b@@b@  public byte getExhausted() {@b@    return this.exhausted;@b@  }@b@@b@  public void setExhausted(byte exhausted) {@b@    this.exhausted = exhausted;@b@  }@b@@b@  public long getMaxWait() {@b@    return this.maxWait;@b@  }@b@@b@  public void setMaxWait(long maxWait) {@b@    this.maxWait = maxWait;@b@  }@b@@b@  public int getMaxIdle() {@b@    return this.maxIdle;@b@  }@b@@b@  public void setMaxIdle(int maxIdle) {@b@    this.maxIdle = maxIdle;@b@  }@b@@b@  public boolean isTestOnBorrow() {@b@    return this.testOnBorrow;@b@  }@b@@b@  public void setTestOnBorrow(boolean testOnBorrow) {@b@    this.testOnBorrow = testOnBorrow;@b@  }@b@@b@  public boolean isTestOnReturn() {@b@    return this.testOnReturn;@b@  }@b@@b@  public void setTestOnReturn(boolean testOnReturn) {@b@    this.testOnReturn = testOnReturn;@b@  }@b@@b@  public long getTimeBetweenEvictRun() {@b@    return this.timeBetweenEvictRun;@b@  }@b@@b@  public void setTimeBetweenEvictRun(long timeBetweenEvictRun) {@b@    this.timeBetweenEvictRun = timeBetweenEvictRun;@b@  }@b@@b@  public int getNumTestsPerEvictionRun() {@b@    return this.numTestsPerEvictionRun;@b@  }@b@@b@  public void setNumTestsPerEvictionRun(int numTestsPerEvictionRun) {@b@    this.numTestsPerEvictionRun = numTestsPerEvictionRun;@b@  }@b@@b@  public void setMinEvictTime(long minEvictTime) {@b@    this.minEvictTime = minEvictTime;@b@  }@b@@b@  public long getMinEvictTime() {@b@    return this.minEvictTime;@b@  }@b@@b@  public boolean isTestWhileIdle() {@b@    return this.testWhileIdle;@b@  }@b@@b@  public void setTestWhileIdle(boolean testWhileIdle) {@b@    this.testWhileIdle = testWhileIdle;@b@  }@b@@b@  public String getUrl() {@b@    return this.url;@b@  }@b@@b@  public void setUrl(String url) {@b@    this.url = url;@b@  }@b@@b@  public String getName() {@b@    return this.name;@b@  }@b@@b@  public void setName(String name) {@b@    this.name = name;@b@  }@b@@b@  public String getValidationQuery() {@b@    return this.validationQuery;@b@  }@b@@b@  public void setValidationQuery(String validationQuery) {@b@    this.validationQuery = validationQuery;@b@  }@b@@b@  public String getDriver() {@b@    return this.driver;@b@  }@b@@b@  public void setDriver(String driver) {@b@    this.driver = driver;@b@  }@b@@b@  public String getUsername() {@b@    return this.username;@b@  }@b@@b@  public void setUsername(String username) {@b@    this.username = username;@b@  }@b@@b@  public String getPassword() {@b@    return this.password;@b@  }@b@@b@  public void setPassword(String password) {@b@    this.password = password;@b@  }@b@@b@  public String getBackupUrl() {@b@    return this.backupUrl;@b@  }@b@@b@  public void setBackupUrl(String backupUrl) {@b@    this.backupUrl = backupUrl;@b@  }@b@@b@  public String getWorkingUrl() {@b@    return this.workingUrl;@b@  }@b@@b@  public void setWorkingUrl(String workingUrl) {@b@    this.workingUrl = workingUrl;@b@  }@b@@b@  public int getCachedPreparedStatements() {@b@    return this.cachedPreparedStatements;@b@  }@b@@b@  public void setCachedPreparedStatements(int cachedPreparedStatements) {@b@    this.cachedPreparedStatements = cachedPreparedStatements;@b@  }@b@@b@  public String toString()@b@  {@b@    StringBuffer sb = new StringBuffer();@b@@b@    sb.append("name: " + this.name + "\n");@b@    sb.append("driver: " + this.driver + "\n");@b@    sb.append("url: " + this.url + "\n");@b@    sb.append("backupUrl: " + this.backupUrl + "\n");@b@    sb.append("workingUrl: " + this.workingUrl + "\n");@b@    sb.append("username: " + this.username + "\n");@b@    sb.append("password: " + this.password + "\n");@b@    sb.append("validationQuery: " + this.validationQuery + "\n");@b@    sb.append("maxActive: " + this.maxActive + "\n");@b@    sb.append("exhausted: " + this.exhausted + "\n");@b@    sb.append("maxWait: " + this.maxWait + "\n");@b@    sb.append("maxIdle: " + this.maxIdle + "\n");@b@    sb.append("testOnBorrow: " + this.testOnBorrow + "\n");@b@    sb.append("testOnReturn: " + this.testOnReturn + "\n");@b@    sb.append("timeBetweenEvictRun: " + this.timeBetweenEvictRun + "\n");@b@    sb.append("numTestsPerEvictionRun: " + this.numTestsPerEvictionRun + "\n");@b@    sb.append("minEvictTime: " + this.minEvictTime + "\n");@b@    sb.append("testWhileIdle: " + this.testWhileIdle + "\n");@b@    sb.append("cachedPreparedStatements: " + this.cachedPreparedStatements + "\n");@b@@b@    return sb.toString();@b@  }@b@}