本文转载自:http://my.oschina.net/huangyong/blog/162447
在项目中,可用该 DBUtil 类封装 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);
}
}
}