采用Apache dbutils操作数据库的DAO类

 采用Apache dbutils操作数据库的DAO类,采用proxool数据库连接池
/*
 *
 * DBConn.java, Created on 2005-5-4
 *
 */
package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.DbUtils;

/**
 * Function Description Here
 *
 * @author Yin xuegang
 */

public class DBConn implements IDBConn{
   
    public  synchronized Connection getConnection() {
        Connection conn = null;
        try {
        conn = DriverManager.getConnection("proxool.mysql");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters and
     * place the column values from the first row in an Object[].
     *
     * Usage Demo:
     * <pre>
     *      Object[] result = searchToArray(sql);
     *      if (result != null) {
     *          for (int i = 0; i < result.length; i++) {
     *              System.out.println(result[i]);
     *          }
     *      }
     * </pre>
     *
     * @param sql The SQL to execute.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public  Object[] searchToArray(String sql) throws Exception {
        Connection conn = this.getConnection();
        Object[] result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayHandler();
    try {
        result = (Object[]) run.query(conn,sql, h);
    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter and
     * place the column values from the first row in an Object[].
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public  Object[] searchToArray(String sql, Object param)
    throws Exception {
        Connection conn = this.getConnection();
        Object[] result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayHandler();
    try {
        result = (Object[]) run.query(conn,sql, param, h);
    } finally {
        DbUtils.close(conn);
    }


        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and place the
     * column values from the first row in an Object[].
     *
     * @param sql The SQL statement to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public  Object[] searchToArray(String sql, Object[] params)
    throws Exception {
        Connection conn = this.getConnection();
        Object[] result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayHandler();
    try {

        result = (Object[]) run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }


        return result;
    }
   
    /**
     * Execute an SQL SELECT query without any replacement parameters and
     * place the ResultSet into a List of Object[]s
     *
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToArrayList(sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *          Object[] temp = (Object[])iterator.next();
     *          for (int i = 0; i < temp.length; i++) {
     *              System.out.println(temp[i]);
     *          }
     *      }
     * </pre>
     * @param sql The SQL statement to execute.
     * @return A List of Object[]s, never null.
     */
    public  ArrayList searchToArrayList(String sql) throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayListHandler();
    try {
        result = (ArrayList) run.query(conn,sql, h);
    } finally {

        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and place the ResultSet into a List of Object[]s
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @return A List of Object[]s, never null.
     */
    public  ArrayList searchToArrayList(String sql, Object param)
    throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayListHandler();
    try {

        result = (ArrayList) run.query(conn,sql, param, h);

    } finally {
        DbUtils.close(conn);
    }


        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and place
     * the ResultSet into a List of Object[]s
     *
     * @param sql The SQL statement to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of Object[]s, never null.
     */
    public  ArrayList searchToArrayList(String sql, Object[] params)
    throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayListHandler();
    try {

        result = (ArrayList) run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and converts the first ResultSet into a Map object.
     *
     * Usage Demo:
     * <pre>
     *      Map result = searchToMap(sql);
     *      System.out.println(map.get(columnName));
     * </pre>
     * @param sql The SQL to execute.
     * @return A Map with the values from the first row or null if there
     * are no rows in the ResultSet.
     */
    public  Map searchToMap(String sql) throws Exception {
        Connection conn = this.getConnection();
        Map result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapHandler();
    try {

        result = (Map) run.query(conn,sql, h);

    } finally {
        DbUtils.close(conn);
    }


        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and converts the first ResultSet into a Map object.
     *
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return A Map with the values from the first row or null if there
     * are no rows in the ResultSet.
     */
    public  Map searchToMap(String sql, Object param) throws Exception {
        Connection conn = this.getConnection();
        Map result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapHandler();
    try {
        result = (Map) run.query(conn,sql, param, h);

    } finally {
        DbUtils.close(conn);
    }
        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and converts
     * the first ResultSet into a Map object.
     *
     * @param sql The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A Map with the values from the first row or null if there
     * are no rows in the ResultSet.
     */
    public  Map searchToMap(String sql, Object[] params) throws Exception {
        Connection conn = this.getConnection();
        Map result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapHandler();
    try {

        result = (Map) run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and converts the ResultSet into a List of Map objects.
     *
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToMapList(sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *           Map map = (Map)iterator.next();
     *           System.out.println(map.get(columnName));
     *      }
     * </pre>
     * @param sql The SQL to execute.
     * @return A List of Maps, never null.
     */
    public  ArrayList searchToMapList(String sql) throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapListHandler();
    try {

        result = (ArrayList) run.query(conn,sql, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and converts the ResultSet into a List of Map objects.
     *
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return A List of Maps, never null.
     */
    public  ArrayList searchToMapList(String sql, Object param)
    throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapListHandler();
    try {

        result = (ArrayList) run.query(conn,sql, param, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and converts
     * the ResultSet into a List of Map objects.
     *
     * @param sql The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of Maps, never null.
     */
    public  ArrayList searchToMapList(String sql, Object[] params)
    throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapListHandler();
    try {

        result = (ArrayList) run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and Convert the first row of the ResultSet into a bean with the
     * Class given in the parameter.
     *
     * Usage Demo:
     * <pre>
     *      String sql = "SELECT * FROM test";
     *      Test test = (Test)searchToBean(Test.class, sql);
     *      if (test != null) {
     *          System.out.println("test:" + test.getPropertyName());
     *      }
     * </pre>
     * @param type The Class of beans.
     * @param sql The SQL to execute.
     * @return An initialized JavaBean or null if there were no rows in
     * the ResultSet.
     */
    public  Object searchToBean(Class type, String sql) throws Exception {
        Connection conn = this.getConnection();
        Object result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanHandler(type);
    try {

        result = run.query(conn,sql, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and Convert the first row of the ResultSet into a bean with the
     * Class given in the parameter.
     *
     * @param type The Class of beans.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return An initialized JavaBean or null if there were no rows in
     * the ResultSet.
     */
    public  Object searchToBean(Class type, String sql, Object param)
    throws Exception {
        Connection conn = this.getConnection();
        Object result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanHandler(type);
    try {

        result = run.query(conn,sql, param, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and Convert the first row of
     * the ResultSet into a bean with the Class given in the parameter.
     *
     * @param type The Class of beans.
     * @param sql The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return An initialized JavaBean or null if there were no rows in
     * the ResultSet.
     */
    public  Object searchToBean(Class type, String sql, Object[] params)
    throws Exception {
        Connection conn = this.getConnection();
        Object result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanHandler(type);
    try {

        result = run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and Convert the ResultSet rows into a List of beans with the
     * Class given in the parameter.
     *
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToBeanList(Test.class, sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *           Test test = (Test)iterator.next();
     *           System.out.println(test.getPropertyName());
     *      }
     * </pre>
     * @param type The Class that objects returned from handle() are created from.
     * @param sql The SQL to execute.
     * @return A List of beans (one for each row), never null.
     */
    public  ArrayList searchToBeanList(Class type, String sql)
    throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanListHandler(type);
    try {

        result = (ArrayList) run.query(conn,sql, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and Convert the ResultSet rows into a List of beans with the
     * Class given in the parameter.
     *
     * @param type The Class that objects returned from handle() are created from.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return A List of beans (one for each row), never null.
     */
    public  ArrayList searchToBeanList(Class type, String sql,
                       Object param) throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanListHandler(type);
    try {

        result = (ArrayList) run.query(conn,sql, param, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Executes the given SELECT SQL query and Convert the ResultSet rows
     * into a List of beans with the Class given in the parameter.
     *
     * @param type The Class that objects returned from handle() are created from.
     * @param sql The SQL to execute.
     * @param params  Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of beans (one for each row), never null.
     */
    public  ArrayList searchToBeanList(Class type, String sql,
                       Object[] params) throws Exception {
        Connection conn = this.getConnection();
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new BeanListHandler(type);
    try {

        result = (ArrayList) run.query(conn,sql, params, h);

    } finally {
        DbUtils.close(conn);
    }

        return result;
    }
   
    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
     *
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters. Each row
     * in this array is one set of batch replacement values.
     * @return The number of rows updated per statement.
     */
    public  int[] batch(String sql, Object[][] params) throws Exception {
        Connection conn = this.getConnection();
        int[] rows = null;
        QueryRunner run = new QueryRunner();
        try {
            rows = run.batch(conn,sql, params);
        } finally {
            DbUtils.close(conn);
        }
       
        return rows;
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without any
     * replacement parameters.
     *
     * @param sql The SQL statement to execute.
     * @return The number of rows updated.
     */
    public  int update(String sql) throws Exception {
        Connection conn = this.getConnection();
        int rows = 0;
        QueryRunner run = new QueryRunner();
    try {

        rows = run.update(conn,sql);

    } finally {
        DbUtils.close(conn);
    }

        return rows;
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with a single
     * replacement parameter.
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     */
    public  int update(String sql, Object param) throws Exception {
        Connection conn = this.getConnection();
        int rows = 0;
        QueryRunner run = new QueryRunner();
    try {

        rows = run.update(conn,sql, param);

    } finally {
        DbUtils.close(conn);
    }

        return rows;
    }
   
    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?') parameters.
     * @return The number of rows updated.
     */
    public  int update(String sql, Object[] params) throws Exception {
        Connection conn = this.getConnection();
        int rows = 0;
        QueryRunner run = new QueryRunner();
    try {

        rows = run.update(conn,sql, params);

    } finally {
        DbUtils.close(conn);
    }

        return rows;
    }

   
    /**
     * Get Maximun Field Value Named 'sFldName' of Table 'sTblName'
     * @param sTblName The Table Name
     * @param sFldName The Field Name
     * @return Maximum Id
     */
    public  int getMaxId(String sTableName, String sFieldName) {
        int iRes = 1;
        String sql = "SELECT MAX(" + sFieldName + ") AS maxid FROM " + sTableName;
        Map result;
        try {
            result = searchToMap(sql);
            if (result.get("maxid") != null) {
                iRes = Integer.parseInt(result.get("maxid").toString()) + 1;
            }
        } catch (Exception e) {
        }
        return iRes;
    }
}