EhCache + JDBC + CommonsDBUtil + Commons DBCP

Account.java

package com.kaishengit.entity;

import java.io.Serializable;

public class Account implements Serializable{

  private static final long serialVersionUID = 1L;
	private int id;
	private String username;
	private int age;
	private String address;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
}

AccountDao.java

package com.kaishengit.dao;

import java.util.List;


import com.kaishengit.entity.Account;
import com.kaishengit.util.DBHelp;

public class AccountDao {

  public void save(Account account){
		String sql="insert into t_user(username,age,address) values(?,?,?)";
		DBHelp.update(sql, account.getUsername(),account.getAge(),account.getAddress());
		
	}
	
	public Account findById(int id){
		String sql="select id,username,age,address from t_user where id=?";
		return DBHelp.queryForObject("user"+id,"users",Account.class, sql, id);
	}
	
	public List<Account> findAll(){
		String sql="select * from t_user";
		return DBHelp.queryForList("userLisst","users",Account.class, sql);
	}
	
	public long findAccountCount(){
		String sql="select count(*) from t_user";
		return DBHelp.queryForObject(Long.class, sql);
		
	}
	
	public List<Integer> findIds(){
		String sql="select id from t_user";
		return DBHelp.queryForList(Integer.class, sql);
		
	}
}

Cache.java

package com.kaishengit.util;

import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Ehcache;
import net.sf.ehcache.Element;

public class Cache {

  private static final CacheManager manager=new CacheManager();
	
	public static Ehcache getEhcache(String cacheName){
		return manager.getEhcache(cacheName);
	}
	
	public static void set(String key,Object value,String cacheName){
		Element element=new Element(key,value);
		getEhcache(cacheName).put(element);
	}
	
	public static Object get(String key,String cacheName){
		Element element=getEhcache(cacheName).get(key);
		if(element!=null){
			return element.getObjectValue();
		}
		return null;
	} 
	
	public static void remove(String key,String cacheName){
		getEhcache(cacheName).remove(key);
		
	}
}

ConnectionFilter.java

package com.kaishengit.web.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

import com.kaishengit.util.ConnectionManager;

public class ConnectionFilter implements Filter{

  public void destroy() {
		
	}

	public void doFilter(ServletRequest request, ServletResponse response,
			FilterChain chain) throws IOException, ServletException {
		chain.doFilter(request, response);
		ConnectionManager.close();
		
	}

	public void init(FilterConfig filterConfig) throws ServletException {
		
	}

}

ConnectionManager.java

package com.kaishengit.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

import com.kaishengit.exception.DataAcessException;

public class ConnectionManager {
  private static String DRIVER;
	private static String URL;
	private static String USERNAME;
	private static String PASSWORD;
	private static DataSource dataSource=builderDataSource();
	private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();
	
	private static DataSource builderDataSource() {
		
		Properties pro=new Properties();
		try {
			//获取db.properties文件的数据
			pro.load(ConnectionManager.class.getClassLoader().getResourceAsStream("db.properties"));
			DRIVER=pro.getProperty("jdbc.driver");
			URL=pro.getProperty("jdbc.url");
			USERNAME=pro.getProperty("jdbc.username");
			PASSWORD=pro.getProperty("jdbc.password");
		} catch (IOException e) {
			throw new DataAcessException(e,"加载db.properties文件异常");
		}
		//使用缓冲池
		BasicDataSource ds=new BasicDataSource();
		ds.setDriverClassName(DRIVER);
		ds.setUrl(URL);
		ds.setUsername(USERNAME);
		ds.setPassword(PASSWORD);
		ds.setInitialSize(5);
		ds.setMaxWait(5000);
		ds.setMaxActive(20);
		ds.setMinIdle(5);
		return ds;
	}
	
	/**
	 * 使用线程连接数据库
	 * @return
	 */
	public static Connection getConnection(){
		try {
			Connection conn=threadLocal.get();
			if(conn==null){
				conn=dataSource.getConnection();
				threadLocal.set(conn);
				System.out.println("获取数据库连接成功");
			}
			return conn;
		} catch (SQLException e) {
			throw new DataAcessException(e,"获取数据库连接异常");
		}
		
	}

	/**
	 * 关闭数据库连接
	 */
	public static void close() {
		Connection conn=threadLocal.get();
		try {
			if(conn!=null&&!conn.isClosed()){
				threadLocal.remove();
				conn.close();
				System.out.println("数据库连接正常关闭");
			}
		} catch (SQLException e) {
			throw new DataAcessException(e,"数据库连接关闭异常");
		}
	}
	
}

DataAcessException.java

package com.kaishengit.exception;

public class DataAcessException extends RuntimeException{

  private static final long serialVersionUID = 1L;

	public DataAcessException(){}
	public DataAcessException(String message){
		super(message);
	}
	public DataAcessException(Exception ex){
		super(ex);
	}
	public DataAcessException(Exception ex,String message){
		super(message,ex);
	}
}

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

DBHelp.java

package com.kaishengit.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.kaishengit.exception.DataAcessException;

public class DBHelp {

  private static QueryRunner db=new QueryRunner();
	/**
	 * 定义基本数据类型
	 */
	private static List<Class<?>> list=new ArrayList<Class<?>>();
	static{
		list.add(Integer.class);
		list.add(Long.class);
		list.add(Float.class);
		list.add(String.class);
		list.add(Boolean.class);
		list.add(Double.class);
		
	}
	
	/**
	 * 执行insert，update，delete语句
	 * @param sql
	 * @param params
	 */
	public static void update(String sql,Object...params){
		try {
			db.update(ConnectionManager.getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DataAcessException(e);
		}
	}
	
	/**
	 * 查询一个对象
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T queryForObject(Class<T> clazz,String sql,Object...params){
		
		try {
			return db.query(ConnectionManager.getConnection(), sql,isPrimitive(clazz) ? new ScalarHandler<T>() : new BeanHandler<T>(clazz), params);
		} catch (SQLException e) {
			throw new DataAcessException(e);
		}
	}
	
	/**
	 * 从缓存中查询一个对象
	 * @param cacheKey
	 * @param cacheName
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> T queryForObject(String cacheKey,String cacheName,Class<T> clazz,String sql,Object...params){
		
		try {
			T t=(T) Cache.get(cacheKey, cacheName);
			if(t==null){
				t=db.query(ConnectionManager.getConnection(), sql,isPrimitive(clazz) ? new ScalarHandler<T>() : new BeanHandler<T>(clazz), params);
				Cache.set(cacheKey, t, cacheName);
				System.out.println("SQL"+sql);
			}
			return t;
		} catch (SQLException e) {
			throw new DataAcessException(e);
		}
	}
	
	/**
	 * 查询对象集合
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> queryForList(Class<T> clazz,String sql,Object...params){
		try {
			return db.query(ConnectionManager.getConnection(), sql, isPrimitive(clazz) ? new ColumnListHandler<T>() : new BeanListHandler<T>(clazz));
		} catch (SQLException e) {
			throw new DataAcessException(e);
		}
	}
	
	/**
	 * 从缓存中查询对象集合
	 * @param cacheKey
	 * @param cacheName
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForList(String cacheKey,String cacheName,Class<T> clazz,String sql,Object...params){
		try {
			List<T> list=(List<T>) Cache.get(cacheKey, cacheName);
			if(list==null){
				list= db.query(ConnectionManager.getConnection(), sql, isPrimitive(clazz) ? new ColumnListHandler<T>() : new BeanListHandler<T>(clazz));
				Cache.set(cacheKey, list, cacheName);
				System.out.println("SQL"+sql);
			}
			return list;
		} catch (SQLException e) {
			throw new DataAcessException(e);
		}
	}
	
	/**
	 * 判断是否是基本数据类型
	 * @param clazz
	 * @return
	 */
	private static boolean isPrimitive(Class<?> clazz){
		if(clazz.isPrimitive()){
			return true;
		}else if(list.contains(clazz)){
			return true;
		}
		return false;
	}
}

ehcache.xml

<?xml version="1.0" encoding="UTF-8"?>
<ehcache>
  <diskStore path="java.io.tmpdir"/>
	<defaultCache
		maxElementsInMemory="10000"
		eternal="false"
		timeToIdleSeconds="120"
		timeToLiveSeconds="120"
		overflowToDisk="true"
	/>
	<cache name="users"
		maxElementsInMemory="100"
		eternal="false"
		timeToIdleSeconds="10"
		timeToLiveSeconds="10"
		overflowToDisk="true"	
	/>

</ehcache>

web.xml

<filter>
    <filter-name>ConnectionFilter</filter-name>
  	<filter-class>com.kaishengit.web.filter.ConnectionFilter</filter-class>
  </filter>
  <filter-mapping>
  	<filter-name>ConnectionFilter</filter-name>
  	<url-pattern>/*</url-pattern>
  </filter-mapping>