使用dbutils封装的一个数据库操作类

使用commons-dbutils-1.3.jar,请自行下载.

?

关于Connection对象和事务在业务层作了处理.

?

主要代码如下:

?

?

package keel.util;import java.sql.Connection;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.log4j.Logger;/** * 操作数据库方法 * @author 蔡治平 * */public class SqlUtil {private static Logger logger = Logger.getLogger(SqlUtil.class);private static QueryRunner qr = new QueryRunner();/** * 查询 * @param conn * @param sql * @param params * @return List<Map<String, Object>> */@SuppressWarnings("deprecation")public static List<Map<String, Object>> search(Connection conn, String sql, Object[] params){List<Map<String, Object>> mapList = null;try {if(params==null){mapList = qr.query(conn, sql, new MapListHandler());}else{mapList = qr.query(conn, sql, params, new MapListHandler());}} catch (Exception e) {logger.error("查询出现异常!原因:"+e);}return mapList;}/** * 查询,将结果返回到bean中,多个bean通过List包装返回 * @param <T> * @param conn * @param entityClass * @param sql * @param params * @return List<T> */@SuppressWarnings("unchecked")public static <T>List<T> search(Connection conn, Class<T> entityClass, String sql, Object[] params){List<T> list = null;try{if (params == null) {              list = (List<T>) qr.query(conn, sql, new BeanListHandler(entityClass));          } else {              list = (List<T>) qr.query(conn, sql, new BeanListHandler(entityClass), params);          }}catch(Exception e){logger.error("查询出现异常!原因:"+e);} return list;}/** * 通过主键查找记录 * @param conn * @param sql * @param params * @return Map<String, Object> */public static Map<String, Object> getById(Connection conn, String sql, Object[] params){Map<String, Object> map = null;try{if (params == null) {  map = qr.query(conn, sql, new MapHandler());          } else {          map = qr.query(conn, sql, new MapHandler(), params);          }}catch (Exception e) {logger.error("查询出现异常!原因:"+e);}return map;}/** * 查询表的记录总数 * @param conn * @param table * @return int */public static int getTotal(Connection conn, String table){int total = 0;String field = "TOTAL";if(conn!=null){String sql = "SELECT COUNT(*) AS "+field+" FROM "+table;try {Map<String, Object> map = (Map<String, Object>) qr.query(conn, sql, new MapHandler());String n = map.get(field).toString();total = Integer.parseInt(n);} catch (Exception e) {logger.error("查询["+table+"]表记录总数出现异常!原因:"+e);}}else{logger.error("查询["+table+"]表记录总数出现异常!原因:连接为空!");}return total;}/** * 更新,返回更新记录条数 * @param conn * @param sql * @param params * @return int */public static int update(Connection conn, String sql, Object[] params){int n = 0;try{if(params==null){n = qr.update(conn, sql);}else{n = qr.update(conn, sql, params);}}catch(Exception e){logger.error("更新出现异常!原因:"+e);}return n;}}

?

测试代码片段:

?

?

List<Map<String, Object>> list = SqlUtil.search(conn, sql, new Object[]{"1001","admin"});for(int i=0;i<list.size();i++){System.out.println(list.get(i).get("id"));System.out.println(list.get(i).get("userName"));}List<User> userList = SqlUtil.search(conn, User.class, sql, new Object[]{"1001","admin"});for(int i=0;i<userList.size();i++){System.out.println(userList.get(i).getId());System.out.println(userList.get(i).getUserName());}String sql1 = "update rg_user set flag = 1 where id=?";int n = SqlUtil.update(conn, sql1, new Object[]{"1001"});System.out.println(n);String sql2 = "select * from rg_user where id ='1001'";Map<String,Object> map = SqlUtil.getById(conn, sql2, null);System.out.println(map.get("id"));