JDBC&连接池 数据交互改造之旅- 数据库操作辅助类

package com.landraydev.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBdetailTools {
 private static DBConnectionPoolManager dbcpm = DBConnectionPoolManager
   .getInstance();
 private static String poolname = "";
 private static Connection conn = null;
 private static PreparedStatement preparedSta = null;
 private static CallableStatement callableSta = null;

 /**
  * 业务表结构查询，返回业务表的属性列表
  *
  * @param poolname
  *            连接池名称
  * @param tablename
  *            业务表名称
  * @return 业务表属性列表
  * @throws SQLException
  */
 public static List TableColumnName(String poolname, String tablename)
   throws SQLException {
  List columns = new ArrayList();
  ResultSet rs = null;
  try {
   conn = dbcpm.getPoolConnection(poolname, 5000);
   if (conn != null) {
    DatabaseMetaData dbmd = conn.getMetaData();
    rs = dbmd.getColumns(null, null, tablename, null);
    while (rs.next()) {
     columns.add(rs.getString("COLUMN_NAME"));
    }
   }
   return columns;
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }

 }

 /**
  * 用于查询，返回Map值对结果集
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @return 结果集
  * @throws SQLException
  */
 public static Map<String, String> queryToMap(String poolname, String sql)
   throws SQLException {

  ResultSet rs = null;
  try {
   getPreparedStatement(poolname, sql);
   rs = preparedSta.executeQuery();

   return ResultToMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }

 }

 /**
  * 用于带参数的查询，返回Map值对结果集
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @param paramters
  *            参数集合
  * @return 结果集
  * @throws SQLException
  */
 public static Map<String, String> queryToMap(String poolname, String sql,
   String[] paramters) throws SQLException {

  ResultSet rs = null;
  try {
   getPreparedStatement(poolname, sql);
   for (int i = 0; i < paramters.length; i++) {
    System.out.println(paramters[i]);
    preparedSta.setObject(i + 1, paramters[i]);
   }
   rs = preparedSta.executeQuery();
   return ResultToMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }
 }

 /**
  * 用于查询，返回结果集
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @return 结果集
  * @throws SQLException
  */
 public static List query(String poolname, String sql) throws SQLException {

  ResultSet rs = null;
  try {
   getPreparedStatement(poolname, sql);
   rs = preparedSta.executeQuery();

   return ResultToListMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }

 }

 /**
  * 用于带参数的查询，返回结果集
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @param paramters
  *            参数集合
  * @return 结果集
  * @throws SQLException
  */
 public static List query(String poolname, String sql, String[] paramters)
   throws SQLException {

  ResultSet rs = null;
  try {
   getPreparedStatement(poolname, sql);
   for (int i = 0; i < paramters.length; i++) {
    System.out.println(paramters[i]);
    preparedSta.setObject(i + 1, paramters[i]);
   }
   rs = preparedSta.executeQuery();
   return ResultToListMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }
 }

 /**
  * 用于增删改
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @return 影响行数
  * @throws SQLException
  */
 public static int update(String poolname, String sql) throws SQLException {

  try {
   getPreparedStatement(poolname, sql);

   return preparedSta.executeUpdate();
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, null);
  }
 }

 /**
  * 用于增删改（带参数）
  *
  * @param poolname
  *            连接池名称
  * @param sql
  *            sql语句
  * @param paramters
  *            sql语句
  * @return 影响行数
  * @throws SQLException
  */
 public static int update(String poolname, String sql, String[] paramters)
   throws SQLException {
  try {
   getPreparedStatement(poolname, sql);

   for (int i = 0; i < paramters.length; i++) {
    preparedSta.setObject(i + 1, paramters[i]);
   }
   return preparedSta.executeUpdate();
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, null);
  }
 }

 /**
  * 调用存储过程执行查询
  *
  * @param poolname
  *            连接池名称
  * @param procedureSql
  *            存储过程
  * @return
  * @throws SQLException
  */
 public static List callableQuery(String poolname, String procedureSql)
   throws SQLException {
  ResultSet rs = null;
  try {
   getCallableStatement(poolname, procedureSql);
   rs = callableSta.executeQuery();
   return ResultToListMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }
 }

 /**
  * 调用存储过程（带参数）,执行查询
  *
  * @param poolname
  *            连接池名称
  * @param procedureSql
  *            存储过程
  * @param paramters
  *            参数表
  * @return
  * @throws SQLException
  */
 public static List callableQuery(String poolname, String procedureSql,
   String[] paramters) throws SQLException {
  ResultSet rs = null;
  try {
   getCallableStatement(poolname, procedureSql);

   for (int i = 0; i < paramters.length; i++) {
    callableSta.setObject(i + 1, paramters[i]);
   }
   rs = callableSta.executeQuery();
   return ResultToListMap(rs);
  } catch (SQLException e) {
   throw new SQLException();
  } finally {
   free(poolname, null, rs);
  }
 }

 /**
  * 批量更新数据
  *
  * @param sqlarr
  *            一组sql
  * @param poolname
  *            连接池名称
  * @return
  *
  * @throws SQLException
  */
 public static int[] batchUpdate(String[] sqlarr, String poolname) {

  int[] result = new int[] {};
  Statement sta = null;
  try {
   conn = dbcpm.getPoolConnection(poolname, 5000);
   conn.setAutoCommit(false);
   sta = conn.createStatement();
   for (String sql : sqlarr) {
    sta.addBatch(sql);
   }
   result = sta.executeBatch();
   conn.commit();
  } catch (SQLException e) {
   try {
    conn.rollback();
   } catch (SQLException e1) {
    // TODO Auto-generated catch block
    throw new ExceptionInInitializerError(e1);
   }
   throw new ExceptionInInitializerError(e);
  } finally {
   free(poolname, sta, null);
  }
  return result;
 }

 /**
  * 将查询结果转换为Map
  *
  * @param rs
  *            数据库查询结果
  * @return List
  *
  *         用于关键字值对信息返回 如 key为编号 ，value为 自定义的串 a+"@"+b
  */
 private static Map<String, String> ResultToMap(ResultSet rs) {
  // TODO Auto-generated method stub
  Map<String, String> map = new HashMap();
  String keyWord = "";
  String valueWord = "";
  try {
   while (rs.next()) {
    keyWord = rs.getString(1);
    valueWord = rs.getString(2);
    map.put(keyWord, valueWord);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   free(poolname, null, rs);
  }

  return map;
 }

 /**
  * 将查询结果转换为List
  *
  * @param rs
  *            数据库查询结果
  * @return List
  *
  */
 private static List ResultToListMap(ResultSet rs) {
  // TODO Auto-generated method stub
  List list = new ArrayList();
  try {
   while (rs.next()) {
    ResultSetMetaData md;
    md = rs.getMetaData();
    Map map = new HashMap();
    for (int i = 1; i < md.getColumnCount(); i++) {
     map.put(md.getColumnLabel(i), rs.getObject(i));
    }
    list.add(map);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   free(poolname, null, rs);
  }

  return list;
 }

 /*
  * 获取连接资源，进行查询语句的封装
  *
  * @param poolname 连接池名称
  *
  * @param sql 查询语句
  *
  * @return
  */
 private static void getPreparedStatement(String poolname, String sql) {
  conn = dbcpm.getPoolConnection(poolname, 5000);
  try {
   preparedSta = conn.prepareStatement(sql);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

 /*
  * 获取连接资源，进行存储过程执行的封装
  *
  * @param poolname 连接池名称
  *
  * @param sql 查询语句
  *
  * @return
  */
 private static void getCallableStatement(String poolname,
   String procedureSql) {
  // TODO Auto-generated method stub
  conn = dbcpm.getPoolConnection(poolname, 5000);
  try {
   callableSta = conn.prepareCall(procedureSql);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

 /*
  * 关闭结果集 注释：每次结果集处理后需要关闭，避免内线溢出
  *
  * @param poolname 连接池名称
  *
  * @param sta 查询对象
  *
  * @param rs 结果集
  *
  * @return
  */
 private static void free(String poolname, Statement sta, ResultSet rs) {
  // TODO Auto-generated method stub
  dbcpm.freePoolConnection(poolname, conn);
  try {
   if (sta != null)
    sta.close();
   if (rs != null)
    rs.close();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

}