数据库工具类

package cn.sunny.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;


/**
 * 数据库CRUD工具br>
 * 数据库配置db.properties文件
 * @author chenyangguang
 * @date 2015-5-27上午09:13:48
 */
public class DBUtil {
    
    private static Logger log = Logger.getLogger(DBUtil.class);
    
    private static String username;
    private static String password;
    
    private static String driver;
    private static String url;
    
    private static BasicDataSource ds = null;
    
    static{
        Properties prop = new Properties();
        InputStream in = DBUtil.class.getResourceAsStream("/db.properties");
        try {
            prop.load(in);
            username = prop.getProperty("db.username");
            password = prop.getProperty("db.password");
            String dbType = prop.getProperty("db.type");
            String host = prop.getProperty("db.ip");
            String port = prop.getProperty("db.port");
            String dbName = prop.getProperty("db.name");
            
            if ("MySQL".equalsIgnoreCase(dbType)) {
                driver = "com.mysql.jdbc.Driver";
                url = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
            } else if ("Oracle".equalsIgnoreCase(dbType)) {
                driver = "oracle.jdbc.driver.OracleDriver";
                url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
            } else if ("SQLServer".equalsIgnoreCase(dbType)) {
                driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + host + ":" + port + ";databaseName=" + dbName;
            } else if ("PostgresSQL".equalsIgnoreCase(dbType)) {
                driver = "org.postgresql.Driver";
                url = "jdbc:postgresql://"+host+":"+port+"/"+dbName;
            }
        } catch (IOException e) {
            log.error("获取数据库连接失败",e);
        }
    }
    
    private static BasicDataSource getDataSource(){
        if (ds == null) {
            ds = new BasicDataSource();
            ds.setDriverClassName(driver);  
            ds.setUsername(username);  
            ds.setPassword(password);  
            ds.setUrl(url);  
        }
        return ds;
    }
    
    private static QueryRunner getQueryRunner(){
        return new QueryRunner(getDataSource());
    }
    
    
    public static Connection openConn(){
         Connection conn = null;
         try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            log.error("数据库驱动加载失败",e);
        } catch (SQLException e) {
            log.error("获取数据库连接失败",e);
        }
        return conn;
    }


    // 查询（返回Array结果）
    public static Object[] queryArray(String sql, Object... params) throws SQLException {
        Object[] result = null;
        try {
            result = getQueryRunner().query(sql, new ArrayHandler(), params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    // 查询（返回ArrayList结果）
    public static List<Object[]> queryArrayList(String sql, Object... params) throws SQLException {
        List<Object[]> result = null;
        try {
            result = getQueryRunner().query(sql, new ArrayListHandler(), params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    // 查询（返回Map结果）
    public static Map<String, Object> queryMap(String sql, Object... params) {
        Map<String, Object> result = null;
        try {
            result = getQueryRunner().query( sql, new MapHandler(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    
    /**
     * 查询（返回MapList结果）
     * @param sql
     * @return
     */
    public static List<Map<String, Object>> queryMapList(String sql) {
        List<Map<String, Object>> result = null;
        try {
            result = getQueryRunner().query( sql, new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    

    /**
     * 查询（返回MapList结果）
     * @param sql
     * @param params
     * @return
     */
    public static List<Map<String, Object>> queryMapList(String sql, Object... params) {
        List<Map<String, Object>> result = null;
        try {
            result = getQueryRunner().query( sql, new MapListHandler(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 查询（返回Bean结果）
     * @param <T>
     * @param cls
     * @param map
     * @param sql
     * @param params
     * @return
     */
    public static <T> T queryBean(Class<T> cls, Map<String, String> map, String sql, Object... params) {
        T result = null;
        try {
            if (MapUtils.isNotEmpty(map)) {
                result = getQueryRunner().query( sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
            } else {
                result = getQueryRunner().query( sql, new BeanHandler<T>(cls), params);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    // 查询（返回BeanList结果）
    public static <T> List<T> queryBeanList(Class<T> cls, Map<String, String> map, String sql, Object... params) throws SQLException {
        List<T> result = null;
        try {
            if (MapUtils.isNotEmpty(map)) {
                result = getQueryRunner().query( sql, new BeanListHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
            } else {
                result = getQueryRunner().query( sql, new BeanListHandler<T>(cls), params);
            }
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    // 查询指定列名的）（单条数据）
    public static <T> T queryColumn(String column, String sql, Object... params) throws SQLException {
        T result = null;
        try {
            result = getQueryRunner().query(sql, new ScalarHandler<T>(column), params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    // 查询指定列名的）（多条数据）
    public static <T> List<T> queryColumnList(String column, String sql, Object... params) throws SQLException {
        List<T> result = null;
        try {
            result = getQueryRunner().query(sql, new ColumnListHandler<T>(column), params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    // 查询指定列名对应的记录映）
    public static <T> Map<T, Map<String, Object>> queryKeyMap(String column, String sql, Object... params) throws SQLException {
        Map<T, Map<String, Object>> result = null;
        try {
            result = getQueryRunner().query( sql, new KeyedHandler<T>(column), params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }
    
    
    
    public static int update(String sql) throws SQLException{
        int result = 0;
        try {
            result = getQueryRunner().update( sql);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }
    
    public static int update(String sql,Object param) throws SQLException{
        int result = 0;
        try {
            result = getQueryRunner().update(sql, param);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }

    /**
     *  更新（包括UPDATE、INSERT、DELETE，返回受影响的行数）
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int update(String sql, Object... params) throws SQLException {
        int result = 0;
        try {
            result = getQueryRunner().update(sql, params);
        } catch (SQLException e) {
            throw e;
        }
        return result;
    }
    
    public static void main(String[] args) {
        QueryRunner q = getQueryRunner();
        System.out.println(q.getDataSource());
        List<Map<String, Object>> list = queryMapList("select * from ac_device_basic");
        System.out.println(list.toString());
    }
    
}

db.type=PostgresSQL
db.ip=127.0.0.1
db.port=5432
db.name=cms_db
db.username=postgres
db.password=88075998