重造DAO车轮

[xqh2168]

重造DAO车轮，不知道是否正确？设计模式不知道是否正确？欢迎大家讨论。

//获取连接
package connection;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Stack;

public class ConnectionPool {

	private static Stack<Connection> stack = new Stack<Connection>();
	private static final int MAX = 10;
	private Connection con;
	private static Properties p;
	
	static{
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("dataSource.properties");
		try {
			p.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public synchronized Connection getConnection(){
		if(stack.isEmpty()){
			con = createConnection();
			if(con != null){
				stack.push(con);
			}
		}else{
			con = stack.pop();
		}
		//代理模式
		con = (Connection)Proxy.newProxyInstance(Connection.class.getClassLoader(), new Class[]{Connection.class }, new CloseInvocationHandler(con));
		return con;
	}
	
	private class CloseInvocationHandler implements InvocationHandler{
		Connection con = null;
		public CloseInvocationHandler(Connection con){
			this.con = con;
		}
		@Override
		public Object invoke(Object proxy, Method method, Object[] args)
				throws Throwable {
			Object obj = null;
			if(method.getName().equals("close")){
				releaseConnection(con);
			}else{
				obj = method.invoke(con, args);
			}
			return obj;
		}
	}
	
	public synchronized void releaseConnection(Connection con){
		if(stack.size() > MAX){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			stack.push(con);
		}
	}
	
	private synchronized Connection createConnection(){
		Connection con = null;
		try {
			Class.forName(p.getProperty("class"));
			con = DriverManager.getConnection(p.getProperty("driver"),p.getProperty("userName"),p.getProperty("pass"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
}

package connection;

import java.sql.PreparedStatement;
import java.sql.SQLException;

public interface PreparedStatementCallback {

	Object doInPreparedStatement(PreparedStatement st) throws SQLException;
}


package connection;

import java.sql.SQLException;
import java.sql.Statement;

public interface StatementCallback {

	Object doInStatement(Statement st) throws SQLException;
}

//主要DAO操作实现，其他的不写
package connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public abstract class DaoImpl{
	
	private static ConnectionPool cp;

	public abstract List rowMapping(ResultSet rs) throws SQLException;
	
	//单例模式
	private synchronized Connection getCon(){
		if(cp == null){
			cp = new ConnectionPool();
		}
		return cp.getConnection();
	}
	
	private Statement getStatement(){
		Statement st = null;
		try {
			st = getCon().createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return st;
	}
	
	public List getObjectStaList(final String sql){
		//内部类
		class executeStatementCallback implements StatementCallback{
			List list = new ArrayList();
			public Object doInStatement(Statement st) throws SQLException{
				ResultSet rs = st.executeQuery(sql);
				//模板模式:提供一个运算的模板，然后使用这个模板
				list = rowMapping(rs);
				return list;
			}
		}
		List res = new ArrayList();
		try {
			res = (List)execute(new executeStatementCallback());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}
	
	public List getObjectPreStaList(final String sql, final String... params){
		try {
			//匿名内部类
			return (List) execute(new PreparedStatementCallback() {
				public Object doInPreparedStatement(PreparedStatement pst)
						throws SQLException {
					List list = new ArrayList();
					for (int i = 0; i < params.length; i++) {
						pst.setString(1, params[i]);
					}
					ResultSet rs = pst.executeQuery();
					//模板模式
					list = rowMapping(rs);
					return list;
				}
			}, sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new ArrayList();
	}
	
	//从集合中获取一条数据
	public Object getUnique(List list){
		if(list.isEmpty()){
			return null;
		}
		return list.get(0);
	}
	
	//用statement来查询
	public Object execute(StatementCallback action)throws SQLException{
		Connection con = getCon();
		Statement st = null;
		Object obj = null;
		try {
			st = con.createStatement();
			//门面（facade）模式：为子系统中的一组接口提供一个一致的界面.
			obj = action.doInStatement(st);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			st.close();
			con.close();
		}
		return obj;
	}
	
	//用预处理来查询
	public Object execute(PreparedStatementCallback action,String sql)throws SQLException{
		Connection con = getCon();
		PreparedStatement pst = null;
		Object obj = null;
		try {
			pst = con.prepareStatement(sql);
			//门面（facade）模式：为子系统中的一组接口提供一个一致的界面.
			obj = action.doInPreparedStatement(pst);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			pst.close();
			con.close();
		}
		return obj;
	}
}

package connection;

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

public class UserDao extends DaoImpl{

	public List rowMapping(ResultSet rs) throws SQLException{
		List<User> list = new ArrayList<User>();
		while(rs.next()){
			User u = new User();
			u.setId(rs.getString("id"));
			u.setName(rs.getString("name"));
			u.setPassword(rs.getString("password"));
			list.add(u);
		}
		return list;
	}
	
	public List getUser(){
		String sql = "select * from t_user";
		return getObjectStaList(sql);
	}
	
	public User getUserById(String id){
		String sql = "select * from t_user t where t.id='" + id + "'";
		return (User)getUnique(getObjectStaList(sql));
	}
	
	public List getUserByParam(){
		String sql = "select * from t_dept t where t.dept_code = ?";
		String[] param = {"123"};
		return getObjectPreStaList(sql,param);
	}
}


package connection;

public class User {
	
	private String id;
	private String name;
	private String password;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
}

[xiaozejun]

不错 楼主代码中的4中模式 和内部类等这些东西
在项目中用的很多 呢 楼主结合的还是蛮好啊 学习了

[qiubojuncode]

代码太长,没有时间去仔细看

[meadking]

还行吧!看了你的ConnectionPool.
以前用MySQL的时候,connection过几个小时会自动关闭,是MySQL掉了!产生异常!!!

我看有些开源的数据库连接池有探测器,每隔90分钟,探测数据库是否关闭ing........
这样比较可靠!!!

[kbyst]

从资源文件中读
易于维护
安全性不保证

[火龙果被占用了]

还自己写连接池？从这一点来看就不是很好。

另外，DaoImpl 中 SQLException 已经捕获了，我也不知道为什么还会抛出 SQLException 异常，
希望能在异常处理中改进。