dbproxy.java~4~

超值周末现场管理软件,用Excel存储数据

package service;

import java.sql.*;
import java.util.*;

/**
 *
 * <p>Title: DBProxy</p>
 * <p>Description: 建立与释放数据库连接</p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class DBProxy {

  /**
   * 按驱动程序的类名和URL得到Connection
   * @param jdbc_driver_name 驱动程序的类名
   * @param jdbc_url URL
   * @return Connection
   * @throws java.lang.ClassNotFoundException
   * @throws SQLException
   */
  public static Connection getConnection(String jdbc_driver_name,
                                         String jdbc_url) throws
      ClassNotFoundException, SQLException {
    Class.forName(jdbc_driver_name);
    Connection conn = DriverManager.getConnection(jdbc_url);
    return conn;
  }

  /**
   * Close Connection and/or Statement and/or ResultSet
   * @param conn Connection
   * @param stmt Statement
   * @param rs ResultSet
   * @throws SQLException
   */
  public static void release(Connection conn, Statement stmt, ResultSet rs) throws
      SQLException {

    if (rs != null) {
     rs.close();
   }


    if (stmt != null) {
      stmt.close();
    }

    if (conn != null) {
     conn.close();
   }

  }

  /**
   * 按SQL直接生成用Map数组包装的ResultSet
   * @param conn Connection
   * @param sql SQL
   * @return @see RSUtil.toMaps()
   * @throws SQLException
   */
  public static List getMapsBySql(Connection conn, String sql) throws
      SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    List list = null;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      list = RSUtil.toMaps(rs);
    }
    finally {
      release(null, stmt, rs);
    }
    return list;
  }

  /**
   * 按SQL直接生成用Map数组包装的ResultSet(使用PreparedStatement)
   * @param conn Connection
   * @param sqlForPS Sql for PreparedStatement
   * @param params PreparedStatement将用到的参数列表
   * @return @see RSUtil.toMaps()
   * @throws SQLException
   */
  public static List getMapsBySql(Connection conn, String sqlForPS, List params) throws
      SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List list = null;
    try {
      ps = conn.prepareStatement(sqlForPS);
      if (params != null) {
        for (int i = 1; i <= params.size(); i++) {
          ps.setObject(i, params.get(i - 1));
        }
      }
      rs = ps.executeQuery();
      list = RSUtil.toMaps(rs);
    }
    finally {
      release(null, ps, rs);
    }
    return list;
  }

  /**
   * 按SQL直接生成用二维数组包装的ResultSet
   * @param conn Connection
   * @param sql SQL
   * @return @see RSUtil.toValueList()
   * @throws SQLException
   */
  public static List getValueListBySql(Connection conn, String sql) throws
      SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    List list = null;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      list = RSUtil.toValueList(rs);
    }
    finally {
      release(null, stmt, rs);
    }
    return list;
  }

  /**
   * 按SQL直接生成用二维数组包装的ResultSet(使用PreparedStatement)
   * @param conn Connection
   * @param sqlForPS Sql for PreparedStatement
   * @param params PreparedStatement将用到的参数列表
   * @return @see RSUtil.toValueList()
   * @throws SQLException
   */
  public static List getValueListBySql(Connection conn, String sqlForPS,
                                       List params) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List list = null;
    try {
      ps = conn.prepareStatement(sqlForPS);
      if (params != null) {
        for (int i = 1; i <= params.size(); i++) {
          ps.setObject(i, params.get(i - 1));
        }
      }
      rs = ps.executeQuery();
      list = RSUtil.toValueList(rs);
    }
    finally {
      release(null, ps, rs);
    }
    return list;
  }

  /**
   * 执行SQL(使用PreparedStatement)
   * @param conn Connection
   * @param sqlForPS Sql for PreparedStatement
   * @param params PreparedStatement将用到的参数列表
   * @throws SQLException
   */
  public static void execSql(Connection conn, String sqlForPS, List params) throws
      SQLException {
    PreparedStatement ps = null;
    try {
      ps = conn.prepareStatement(sqlForPS);
      if (params != null) {
        for (int i = 1; i <= params.size(); i++) {
          ps.setObject(i, params.get(i - 1));
        }
      }
      ps.execute();
    }
    finally {
      release(null, ps, null);
    }

  }

  /**
   * 执行一系列SQL
   * @param conn 数据库连接
   * @param sqlAndParams Sql和它的参数。它是一个List，List的每个元素是一个Map, Map包括的键名为"sql"和"params"
   * @throws SQLException
   */
  public static void execSqls(Connection conn, List sqlAndParams) throws SQLException{
    try {
      conn.setAutoCommit(false);
    }
    catch (SQLException ex) {
      throw ex;
    }

  try {
    for (int i = 0; i < sqlAndParams.size(); i++) {
      Map sp = (Map) sqlAndParams.get(i);
      String sql = (String) sp.get("sql");
      List params = (List) sp.get("params");
      PreparedStatement ps = null;

        ps = conn.prepareStatement(sql);
        if (params != null) {
          //test
          //System.out.println("params="+params);


          for (int j = 1; j <= params.size(); j++) {
            ps.setObject(j, params.get(j - 1));
          }
        }
        ps.execute();
        release(null, ps, null);
      }
      conn.commit();
    }catch(SQLException ex){
        conn.rollback();
        throw ex;
      }


  }

}