dbutils +dbcp 连接池构建dao层

前些日子，公司经过需求调研后决定开一个新项目。我新入不久，目测原来的工程都是servlet+jdbc构建。以前那种痛苦的感觉顿时回忆起来，经过商讨决定引入框架，费了点时间把mybatis引入后大伙说jar包太大（精简后加了2.5m,泪奔！还不让用spring，白衣老大我好想跟你混啊）而且仍然感觉繁琐。于是决定用dbutils 搭建一套。无spring。增删改查只需要传递model，也可自己定义sql。dbcp 我打印了datasource 的hasecode  销毁后code相同，这里claose 就是dbcp里的伪关闭啦。下面是代码

 

package com.navitek.yq.dao;

import java.util.List;
import java.util.Map;

public interface Dao {
	/**
	 * @param <T>
	 *            查询list<entry>
	 * @param clazz
	 * @return
	 */
	public <T> List<T> findAll(Class<T> clazz,String where);

	/**
	 * @param <T>
	 *            根据主键查找entry
	 * @param clazz
	 * @param id
	 * @return
	 */
	public <T> T findById(Class<T> clazz, int id);

	/**
	 * @param <T>
	 *            仅仅适合存在一个主键，且主键递增的情况
	 * @param clazz
	 * @param map
	 * @return
	 */
	public  boolean insert(Object obj);

	/**
	 * @param <T>
	 *            根据条件更新entry
	 * @param clazz
	 * @param map
	 * @return
	 */
	public <T> int update(Object obj, String where);

	/**
	 * @param <T>
	 *            根据条件更新entry
	 * @param clazz
	 * @param map
	 * @return
	 */
	public <T> int delete(Class<T> clazz, String where);

	// 以下为自定义
	public int insertSql(String sql, Object... args);

	public int updateSql(String sql, Object... args);

	public void deleteSql(String sql, Object... args);

	public List<Map<String, Object>> executeQuerySql(String sql, Object... args);

	/**
	 * @param sql
	 *            批量更新
	 * @param objs
	 * @return
	 */
	public void batchUpdateSql(String sql, Object[][] objs);
}

 

 dao的实现 利用泛型判断

 

package com.navitek.yq.dao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.mysql.jdbc.JDBC4Connection;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
 */
public class DaoImpl implements Dao {

	private MysqlDataSource dataSource = new MysqlDataSource();

	private QueryRunner qryRun = null;

	private java.sql.DatabaseMetaData dbmb;

	public DaoImpl() {
		dataSource.setUrl("jdbc:mysql://localhost:3306/gdyh");
		dataSource.setUser("root");
		dataSource.setPassword("root");
		// qryRun = new QueryRunner(dataSource);
		qryRun = DbHelper.getQueryRunner();
		dbmb = getDatabaseMetaData();
	}

	private java.sql.DatabaseMetaData getDatabaseMetaData() {
		Properties info = new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "root");
		java.sql.DatabaseMetaData metaData = null;
		try {
			metaData = new JDBC4Connection("localhost", 3306, info, "gdyh",
					null).getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return metaData;
	}

	public <T extends Object> List<T> findAll(Class<T> clazz, String where) {
		if (where == null) {
			where = "";
		}
		ResultSetHandler<List<T>> rsh = new BeanListHandler<T>(clazz);
		List<T> result = null;
		try {
			result = qryRun.query("select * from " + clazz.getSimpleName()
					+ " " + where, rsh);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return result;
	}

	public <T> T findById(Class<T> clazz, int id) {
		ResultSetHandler<T> rsh = new BeanHandler<T>(clazz);
		T result = null;
		try {
			ResultSet rs = dbmb.getPrimaryKeys(null, null, clazz
					.getSimpleName());

			String primary_key = null;
			while (rs.next()) {
				primary_key = rs.getString("Column_name");
			}
			if (!"".equals(primary_key) || null != primary_key) {
				result = qryRun.query("select * from " + clazz.getSimpleName()
						+ " where " + primary_key + "=?", rsh,
						new Object[] { id });

			} else {
				throw new SQLException("This table has not primary key");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return result;
	}

	public List<Map<String, Object>> executeQuery(String sql, Object... args) {
		MapListHandler rsh = new MapListHandler();
		List<Map<String, Object>> result = null;
		try {
			result = qryRun.query(sql, rsh, args);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return result;
	}

	public int insertSql(String sql, Object... args) {
		return executeUpdateSql(sql, args);
	}

	public int updateSql(String sql, Object... args) {
		return executeUpdateSql(sql, args);
	}

	public void deleteSql(String sql, Object... args) {
		executeUpdateSql(sql, args);
	}

	public int executeUpdateSql(String sql, Object... args) {
		int id = 0;
		try {
			id = qryRun.update(sql, args);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return id;
	}

	public void batchUpdateSql(String sql, Object[][] objs) {
		try {
			qryRun.batch(sql, objs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
	}

	public boolean insert(Object obj) {
		try {
			Map<String, Object> map = new HashMap<String, Object>();
			Field[] field = obj.getClass().getDeclaredFields();
			Field.setAccessible(field, true);
			for (Field f : field) {
				Object o = f.get(obj);
				map.put(f.getName(), o);
			}
			Class clazz = obj.getClass();
			String table = clazz.getSimpleName();
			ResultSet rs1;
			// 得到主键
			ResultSet rs = dbmb.getPrimaryKeys(null, null, table);
			String primary_key = null;
			while (rs.next()) {
				primary_key = rs.getString("Column_name");
			}
			rs1 = dbmb.getColumns(null, null, table, null);
			StringBuffer s = new StringBuffer("insert into " + table + "(");
			StringBuffer sv = new StringBuffer(" values(");
			rs1.last();
			int m = rs1.getRow();
			Object[] objs = new Object[m - 1];
			int count = 0;
			rs1.first();
			while (rs1.next()) {
				String column = rs1.getString("Column_name");
				if (!column.equals(primary_key)) {
					boolean b = rs1.isLast();
					if (b) {
						s.append(column + ")");
						sv.append("?)");
					} else {
						s.append(column + ",");
						sv.append("?,");
					}
					objs[count] = map.get(column);
					count++;
				}
			}
			String sql = s.append(sv).toString();
			int mount = qryRun.update(sql, objs);
			if (mount > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return false;
	}

	public int update(Object obj, String where) {
		if (where == null) {
			where = "";
		}
		Class clazz = obj.getClass();
		String table = clazz.getSimpleName();
		ResultSet rs1;
		try {
			Map<String, Object> map = new HashMap<String, Object>();
			Field[] field = obj.getClass().getDeclaredFields();
			Field.setAccessible(field, true);
			for (Field f : field) {
				Object o = f.get(obj);
				map.put(f.getName(), o);
			}
			// 得到主键
			ResultSet rs = dbmb.getPrimaryKeys(null, null, clazz
					.getSimpleName());
			String primary_key = null;
			while (rs.next()) {
				primary_key = rs.getString("Column_name");
			}
			rs1 = dbmb.getColumns(null, null, clazz.getSimpleName(), null);
			StringBuffer s = new StringBuffer("update " + table + " set ");
			rs1.last();
			int m = rs1.getRow();
			Object[] objs = new Object[m - 1];
			int count = 0;
			rs1.first();
			while (rs1.next()) {
				String column = rs1.getString("Column_name");
				if (!column.equals(primary_key)) {
					boolean b = rs1.isLast();

					if (!b) {
						s.append(column + "=?, ");
					} else {
						s.append(column + "=? ");
					}
					objs[count] = map.get(column);
					count++;
				}
			}
			String sql = s.append(where).toString();
			System.out.println("=======" + sql);
			return qryRun.update(sql, objs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	public <T> int delete(Class<T> clazz, String where) {
		String table = clazz.getSimpleName();
		try {
			StringBuffer s = new StringBuffer("delete  " + table + " " + where);
			String sql = s.toString();
			System.out.println("=======" + sql);
			return qryRun.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return 0;
	}

	public List<Map<String, Object>> executeQuerySql(String sql, Object... args) {
		MapListHandler rsh = new MapListHandler();
		List<Map<String, Object>> result = null;
		try {
			result = qryRun.query(sql, rsh, args);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbHelper.closeDataSource();
		}
		return result;
	}

}

 

 下面是测试类 

 

import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class Test {
	/**
	 * @param args
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	public static void main(String[] args) throws IllegalArgumentException,
			IllegalAccessException {
		Dao dao = new DaoImpl();
		// 查找list<model>
		List<Story> result = dao.findAll(Story.class, null);
		// 查找model
		// Story s = dao.findById(Story.class, 1);
		// 测试
		Man man = new Man();
		man.setAge(11);
		man.setId(1);
		man.setName("ymc11");
		// dao.insert(man);
		dao.update(man, null);

		// dao.insert(Man.class,m1);
		// m1.put("name","hh");
		// dao.update(Man.class,m1,"");

		// dao.insert(Story.class,map);
		// Story ss=new Story();
		// Story s2 = dao.findById(Story.class, 1);
		// 自己传入sql 返还list<map> 字段是表中字段不是实体的属性
		List<Map<String, Object>> m = dao.executeQuerySql(
				"select * from story where id=?", new Object[] { 1 });

		// for (Map<String, Object> list : m) {
		// Set<Map.Entry<String, Object>> set = m1.entrySet();
		// for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it
		// .hasNext();) {
		// Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
		// .next();
		// System.out.println(entry.getKey() + "--->" + entry.getValue());
		// }
		
	}

	// 插入数据
	// long id = dao.insertSql("insert into man (n1ame) values (?)",
	// new Object[] { "男" });
	// System.out.println(id);
	// 批量插入
	Object[][] objs = new Object[][] { { "man" }, { "women" }, { "man" } };
	// dao.batchUpdateSql("insert into man (name) values(?)", objs);

}

 

 可以选择是否使用连接池

 

package com.navitek.yq.dao;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;

public class DbHelper {
	private static DataSource dataSource;

	private DbHelper() {
	}

	public static QueryRunner getQueryRunner() {
		if (DbHelper.dataSource == null) {
			BasicDataSource dbcpDataSource = new BasicDataSource();
			dbcpDataSource
					.setUrl("jdbc:mysql://localhost:3306/gdyh?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull");
			dbcpDataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dbcpDataSource.setUsername("root");
			dbcpDataSource.setPassword("root");
			dbcpDataSource.setDefaultAutoCommit(true);
			dbcpDataSource.setMaxActive(100);
			dbcpDataSource.setMaxIdle(30);
			dbcpDataSource.setMaxWait(500);
			
			DbHelper.dataSource = (DataSource) dbcpDataSource;
			
			System.out.println("Initialize dbcp...");
		}
		return new QueryRunner(DbHelper.dataSource);
	}

	public final static void closeDataSource() {
		try {
			System.out.println(dataSource.hashCode());
			System.out.println(dataSource.getClass().getName());
			dataSource.getClass().getMethod("close").invoke(dataSource);
			System.out.println(" dbcp has closed...");
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

见附件 所需jar

评论

2 楼 yuanmengchong 2014-01-06  

ninthbar 写道

事务如何处理？

事务你可以弄一个service层，然后调用基层。

1 楼 ninthbar 2013-12-06  

事务如何处理？