Apache Commons DbUtils 封裝類庫

public class DBUtil {


private static final Logger logger = Logger.getLogger(DBUtil.class);

// 打開數據庫連接（type: MySQL，Oracle，SQLServer）
public static Connection openConnection(String type, String host, String port, String name, String username, String password) {
    Connection conn = null;
    try {
        String driver;
        String url;
        if (type.equalsIgnoreCase("MySQL")) {
            driver = "com.mysql.jdbc.Driver";
            url = "jdbc:mysql://" + host + ":" + port + "/" + name;
        } else if (type.equalsIgnoreCase("Oracle")) {
            driver = "oracle.jdbc.driver.OracleDriver";
            url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + name;
        } else if (type.equalsIgnoreCase("SQLServer")) {
            driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
            url = "jdbc:sqlserver://" + host + ":" + port + ";databaseName=" + name;
        } else {
            throw new RuntimeException();
        }
        Class.forName(driver);
        conn = DriverManager.getConnection(url, username, password);
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    return conn;
}

// 關閉數據庫連接
public static void closeConnection(Connection conn) {
    try {
        if (conn != null) {
            conn.close();
        }
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
}

// 查詢（返回 Array）
public static Object[] queryArray(QueryRunner runner, String sql, Object... params) {
    Object[] result = null;
    try {
        result = runner.query(sql, new ArrayHandler(), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢（返回 ArrayList）
public static List<Object[]> queryArrayList(QueryRunner runner, String sql, Object... params) {
    List<Object[]> result = null;
    try {
        result = runner.query(sql, new ArrayListHandler(), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢（返回 Map）
public static Map<String, Object> queryMap(QueryRunner runner, String sql, Object... params) {
    Map<String, Object> result = null;
    try {
        result = runner.query(sql, new MapHandler(), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢（返回 MapList）
public static List<Map<String, Object>> queryMapList(QueryRunner runner, String sql, Object... params) {
    List<Map<String, Object>> result = null;
    try {
        result = runner.query(sql, new MapListHandler(), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢（返回 Bean）
public static <T> T queryBean(QueryRunner runner, Class<T> cls, Map<String, String> map, String sql, Object... params) {
    T result = null;
    try {
        if (MapUtil.isNotEmpty(map)) {
            result = runner.query(sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
        } else {
            result = runner.query(sql, new BeanHandler<T>(cls), params);
        }
        printSQL(sql);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    return result;
}

// 查詢（返回 BeanList）
public static <T> List<T> queryBeanList(QueryRunner runner, Class<T> cls, Map<String, String> map, String sql, Object... params) {
    List<T> result = null;
    try {
        if (MapUtil.isNotEmpty(map)) {
            result = runner.query(sql, new BeanListHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
        } else {
            result = runner.query(sql, new BeanListHandler<T>(cls), params);
        }
        printSQL(sql);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    return result;
}

// 查詢指定列名的值（單條數據）
public static Object queryColumn(QueryRunner runner, String column, String sql, Object... params) {
    Object result = null;
    try {
        result = runner.query(sql, new ScalarHandler<Object>(column), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢指定列名的值（多條數據）
public static <T> List<T> queryColumnList(QueryRunner runner, String column, String sql, Object... params) {
    List<T> result = null;
    try {
        result = runner.query(sql, new ColumnListHandler<T>(column), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 查詢指定列名對應的記錄映射
public static <T> Map<T, Map<String, Object>> queryKeyMap(QueryRunner runner, String column, String sql, Object... params) {
    Map<T, Map<String, Object>> result = null;
    try {
        result = runner.query(sql, new KeyedHandler<T>(column), params);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    printSQL(sql);
    return result;
}

// 更新（包括 UPDATE、INSERT、DELETE，返回受影響的行數）
public static int update(QueryRunner runner, Connection conn, String sql, Object... params) {
    int result = 0;
    try {
        if (conn != null) {
            result = runner.update(conn, sql, params);
        } else {
            result = runner.update(sql, params);
        }
        printSQL(sql);
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    return result;
}

private static void printSQL(String sql) {
    if (logger.isDebugEnabled()) {
        logger.debug("SQL: " + sql);
    }
}

}</pre>