Apache Commons DbUtils 封装类库
jopen
11年前
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); } } }