dbutils.java

自用的一个简单的数据库连接池

    return str;
  }

  /**
   * 构建获取最前面N条的SQL
   * @param topn int         n值
   * @param strSql String    原始SQL
   * @return String          包装后的SQL
   */
  public static String getTopNSql(int topn, String strSql){
    String str = "select * from (select t$_table.*,rownum from(" + strSql +
                 ") t$_table where rownum <= " + topn + ")";
    return str;
  }

  /**
   * 构建获取最前面N条的DB2格式SQL
   * @param topn int          n值
   * @param strSql String     原始SQL
   * @return String           包装后的SQL
   */
  public static String getDB2TopNSql(int topn, String strSql){
    String str = strSql + " fetch first " + topn + " rows only";
    return str;
  }

  /**
   * 构建获取指定索引范围的SQL
   * @param min int            最小索引(从1开始)
   * @param max int            最大索引
   * @param strSql String      原始SQL
   * @return String            包装后的SQL
   */
  public static String getIndexScopeSql(int min, int max, String strSql){
    String str = "select * from (select t$_table.*,rownum as t$_rownum from(" +
        strSql + ") t$_table where rownum <= " + max + ") where t$_rownum >= " + min;
    return str;
  }

  /**
   * 构建获取指定索引范围的DB2格式SQL
   * @param min int            最小索引(从1开始)
   * @param max int            最大索引
   * @param strSql String      原始SQL
   * @return String            包装后的SQL
   */
  public static String getDB2IndexScopeSql(int min, int max, String strSql){
    String str = "select * from (select t$_table.*,rownumber() over() as t$_rownum from (" +
                 strSql + ") t$_table) as t$_table2 where t$_table2.t$_rownum between " +
                 min + " and " + max;
    return str;
  }

  /**
   * 获取Clob字段的字串值
   * @param rs ResultSet
   * @param clobName String      字段名
   * @return String
   * @throws SQLException
   */
  public static String getClobText(ResultSet rs, String clobName) throws SQLException{
    Clob clob = rs.getClob(clobName);
    return getClobText(clob);
  }

  /**
   * 获取Clob字段的字串值
   * @param rs ResultSet
   * @param idx int            字段序号(从1开始)
   * @return String
   * @throws SQLException
   */
  public static String getClobText(ResultSet rs, int idx) throws SQLException{
    Clob clob = rs.getClob(idx);
    return getClobText(clob);
  }

  /**
   * 获取Clob字段的字串值
   * @param clob Clob        Clob字段对象
   * @return String
   * @throws SQLException
   */
  public static String getClobText(Clob clob) throws SQLException{
    if(clob != null)
      return clob.getSubString(1L, (int)clob.length());
    else
      return null;
  }

  /**
   * 获取Blob字段的字节数组
   * @param rs ResultSet
   * @param colName String      字段名
   * @return byte[]
   * @throws SQLException
   * @throws IOException
   */
  public static byte[] getBlobBytes(ResultSet rs, String colName) throws SQLException, IOException{
    Blob blob = rs.getBlob(colName);
    return getBlobBytes(blob);
  }

  /**
   * 获取Blob字段的字节数组
   * @param rs ResultSet
   * @param idx int           字段序号(从1开始)
   * @return byte[]           字节数组
   * @throws SQLException
   * @throws IOException
   */
  public static byte[] getBlobBytes(ResultSet rs, int idx) throws SQLException, IOException{
    Blob blob = rs.getBlob(idx);
    return getBlobBytes(blob);
  }

  /**
   * 获取Blob字段的字节数组
   * @param blob Blob         Blob对象
   * @return byte[]
   * @throws SQLException
   * @throws IOException
   */
  public static byte[] getBlobBytes(Blob blob) throws SQLException, IOException{
    if(blob != null){
      InputStream is = blob.getBinaryStream();
      byte data[] = new byte[(int)blob.length()];
      is.read(data);
      is.close();
      return data;
    } else{
      return null;
    }
  }

  /**
   * 输出Blob内容到指定的输出流
   * @param rs ResultSet
   * @param colName String      字段名
   * @param os OutputStream     输出流
   * @throws SQLException
   * @throws IOException
   */
  public static void outputBlob(ResultSet rs, String colName, OutputStream os) throws SQLException,
      IOException{
    Blob blob = rs.getBlob(colName);
    outputBlob(blob, os);
  }

  /**
   * 输出Blob内容到指定的输出流
   * @param rs ResultSet
   * @param idx int            字段序号(从1开始)
   * @param os OutputStream    输出流
   * @throws SQLException
   * @throws IOException
   */
  public static void outputBlob(ResultSet rs, int idx, OutputStream os) throws SQLException,
      IOException{
    Blob blob = rs.getBlob(idx);
    outputBlob(blob, os);
  }

  /**
   * 输出Blob内容到指定的输出流
   * @param blob Blob          blob对象
   * @param os OutputStream    输出流
   * @throws SQLException
   * @throws IOException
   */
  public static void outputBlob(Blob blob, OutputStream os) throws SQLException, IOException{
    if(blob != null){
      InputStream is = blob.getBinaryStream();
      BufferedInputStream input = new BufferedInputStream(is);
      byte buff[] = new byte[2048];
      for(int readLen = 0; -1 != (readLen = input.read(buff, 0, buff.length)); )
        os.write(buff, 0, readLen);
      os.flush();
    }
  }

  /**
   * 获取数据库的当前时间
   * @return Timestamp
   */
  public static Timestamp getSysDate() throws Exception{
    return getSysDate(DEF_POOL_NAME);
  }

  /**
   * 获取指定连接池中数据库的系统时间
   * @param poolName String
   * @return Timestamp
   */
  public static Timestamp getSysDate(String poolName) throws Exception{
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    Timestamp sysdate = null;
    try{
      conn = getDBConn(poolName);
      st = conn.createStatement();
      rs = st.executeQuery("select sysdate from dual");
      if(rs.next())
        sysdate = rs.getTimestamp(1);
    } catch(Exception ex){
      log.error("无法获取数据库当前时间,连接池=" + poolName, ex);
      throw ex;
    } finally{
      closeRS(rs);
      closeStmt(st);
      returnDBConn(poolName, conn);
    }
    return sysdate;
  }

  /**
   * 获取数据库系统时间字符串 （日期+时间）
   * @return String
   */
  public static String getSysDateStr() throws Exception{
    return getSysDateStr(DEF_POOL_NAME, DEF_DATETIME_FORMAT);
  }

  /**
   * 获取指定格式的数据库系统时间字符串
   * @param dateFmt String
   * @return String
   */
  public static String getSysDateStr(String dateFmt) throws Exception{
    return getSysDateStr(DEF_POOL_NAME, dateFmt);
  }

  /**
   * 获取指定连接池与指定格式的数据库系统时间字符串
   * @param poolName String
   * @param dateFmt String
   * @return String
   */
  public static String getSysDateStr(String poolName, String dateFmt) throws Exception{
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    String sysdate = null;
    try{
      conn = getDBConn(poolName);
      st = conn.createStatement();
      rs = st.executeQuery("select to_char(sysdate,'" + dateFmt + "') curdate from dual");
      if(rs.next())
        sysdate = rs.getString(1);
    }catch(Exception ex){
      log.error("无法获取数据库时间,连接池=" + poolName + ",格式=" + dateFmt, ex);
      throw ex;
    }finally{
      closeRS(rs);
      closeStmt(st);
      returnDBConn(poolName, conn);
    }
    return sysdate;
  }

  /**
   * 获取指定Sequence的下一取值
   * @param seqName String     Sequence名称
   * @return Long
   */
  public static long getNextSeq(String seqName) throws Exception{
    return getNextSeq(DEF_POOL_NAME, seqName);
  }

  /**
   * 获取指定连接池中指定Sequence的下一取值
   * @param poolName String
   * @param seqName String
   * @return Long
   */
  public static long getNextSeq(String poolName, String seqName) throws Exception{
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    long seq = 0;
    try{
      conn = getDBConn(poolName);
      st = conn.createStatement();
      rs = st.executeQuery("select " + seqName + ".nextval from dual");
      if(rs.next())
        seq = rs.getLong(1);
    }catch(Exception ex){
      log.error("无法获取连接池=" + poolName + ",序列名=" + seqName + "的下一取值",ex);
      throw ex;
    }finally{
      closeRS(rs);
      closeStmt(st);
      returnDBConn(poolName, conn);
    }
    return seq;
  }

  /**
   * 获取指定Sequence的当前取值
   * @param seqName String     Sequence名称
   * @return Long
   */
  public static long getCurrSeq(String seqName) throws Exception{
    return getCurrSeq(DEF_POOL_NAME, seqName);
  }

  /**
   * 获取指定连接池中指定Sequence的当前取值
   * @param poolName String
   * @param seqName String
   * @return long
   * @throws Exception
   */
  public static long getCurrSeq(String poolName, String seqName) throws Exception{
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    long seq = 0;
    try{
      conn = getDBConn(poolName);
      st = conn.createStatement();
      rs = st.executeQuery("select " + seqName + ".currval from dual");
      if(rs.next())
        seq = rs.getLong(1);
    }catch(Exception ex){
      log.error("无法获取连接池=" + poolName + ",序列名=" + seqName + "的当前取值",ex);
      throw ex;
    }finally{
      closeRS(rs);
      closeStmt(st);
      returnDBConn(poolName, conn);
    }
    return seq;
  }

  public static boolean isTableExist(String tabName){
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try{
      conn = DBUtils.getDBConn();
      ps = conn.prepareStatement("select 1 from " + tabName + " where 1=2");
      rs = ps.executeQuery();
      return true;
    } catch(Exception ex){
      return false;
    } finally{
      DBUtils.mopUpDBConn(rs, ps, conn);
    }
  }

  public static boolean isSequenceExist(String seqName){
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try{
      conn = DBUtils.getDBConn();
      ps = conn.prepareStatement("select " + seqName + ".currval from dual");
      rs = ps.executeQuery();
      return true;
    } catch(Exception ex){
      return false;
    } finally{
      DBUtils.mopUpDBConn(rs, ps, conn);
    }
  }

}