JDBC工具类

三、定义DAO实现模板类简化DAO实现类
3.1  定义和使用抽象化DAO类（模板模式）
使用继承模板DAO类来优化DAO实现类的编写：
	优化查询操作，区分开变化和不变的部分：
	sql和ResultSet的处理是变化部分，创建和释放资源部分是不变部分。
	提取超类，将不变部分放入超类，变化部分留给子类实现。
	超类的主要代码：
	public Object find(String sql, Object[] args) {
	  …
	  rs = ps.executeQuery();
	  if (rs.next()) return rowMapper(rs); 
	  …
	}
	protected abstract Object rowMapper(ResultSet rs);

package org.com.dao.refactor.template;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.com.dao.DAOException;
import org.com.lin.JdbcUtilsSing;

public abstract class AbstractDao{
	public Object find(String sql, Object[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			Object obj = null;
			if (rs.next()) {
				obj = rowMapper(rs);
			}
			return obj;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(rs, ps, conn);
		}
	}
	
	abstract protected Object rowMapper(ResultSet rs) throws SQLException;
	
	
	public int update(String sql, Object[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(null, ps, conn);
		}
	}
	public ResultSet insertBackPrimaryKey(String sql, Object[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			ps.executeUpdate();
			return ps.getGeneratedKeys();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(null, ps, conn);
		}
	}
}

package org.com.dao.refactor.template;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.com.dao.DAOException;
import org.com.domain.User;
public class UserDAOImpl extends AbstractDao{
	public User getUser(int userId) {
		String sql="select id,name,birthday,money from user  where id=?";
		Object[] args=new Object[]{userId};
		return (User) super.find(sql, args);
	}
	@Override
	protected Object rowMapper(ResultSet rs) throws SQLException {
		User user = new User();
		user.setId(rs.getInt("id"));
		user.setName(rs.getString("name"));
		user.setMoney(rs.getFloat("money"));
		user.setBirthday(rs.getDate("birthday"));
		return user;
	}
	//如下两个方法要么用原来的实现方法，要么在另一个AbstractDao子类中定义重新实现rowMapper方法
	//所以，这样仍然不够通用与灵活
	public List findByName(String name) {
		return null;
	}
	public String getUserName(int userId) {
		return null;
	}
	
	public int update(User user) {
		String sql="update user set name=?,birthday=?,money=? where id=?";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney(),user.getId()};
		return super.update(sql, args);
	}
	public int delete(User user) {
		String sql="delete from user where id=?";
		Object[] args=new Object[]{user.getId()};
		return super.update(sql, args);
	}
	public int insert(User user){
		String sql="insert into user (name,birthday,money) values(?,?,?)";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney()};
		return super.update(sql, args);
	}
	public int insertBackPrimaryKey(User user){
		String sql="insert into user (name,birthday,money) values(?,?,?)";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney()};
		ResultSet rs=super.insertBackPrimaryKey(sql, args);
		if(rs!=null){
			try {
				user.setId(rs.getInt(0));
			} catch (SQLException e) {
				throw new DAOException(e.getMessage(), e);
			}
		}
		return user.getId();
	}
}

3.2  定义和使用抽象化DAO类（策略模式）
使用组合DAO模板类来优化DAO实现类的编写：
	优化查询操作，区分开变化和不变的部分：
	sql和ResultSet的处理是变化部分，创建和释放资源部分是不变部分。
	提取接口封装变化部分。
	JDBC操作主要代码：
	public Object find(String sql, Object[] args, RowMapper rowMapper) {
		…
		rs = ps.executeQuery();
		if (rs.next())return rowMapper.mapRow(rs);
		…
	}
	回调接口：
	public interface RowMapper {
		public Object mapRow(ResultSet rs) throws SQLException;
	}


package org.com.dao.refactor.strategy;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface RowMapper {
	Object rowMapper(ResultSet rs) throws SQLException;
}

package org.com.dao.refactor.strategy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.com.dao.DAOException;
import org.com.lin.JdbcUtilsSing;

public class MyDaoTemplate{
	public Object find(String sql, Object[] args,RowMapper rowMapper) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			Object obj = null;
			if (rs.next()) {
				obj = rowMapper.rowMapper(rs);
			}
			return obj;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(rs, ps, conn);
		}
	}
	
	public int update(String sql, Object[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(null, ps, conn);
		}
	}
	public ResultSet insertBackPrimaryKey(String sql, Object[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JdbcUtilsSing.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			int len=0;
			if(args!=null){
				len=args.length;
			}
			for (int i = 0;i<len; i++){
				ps.setObject(i + 1, args[i]);
			}
			ps.executeUpdate();
			return ps.getGeneratedKeys();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage(), e);
		} finally {
			JdbcUtilsSing.getInstance().free(null, ps, conn);
		}
	}
}

package org.com.dao.refactor.strategy;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.com.dao.DAOException;
import org.com.domain.User;

public class UserDAOImpl{
	private MyDaoTemplate template;
	
	public User getUser(int userId) {
		String sql="select id,name,birthday,money from user  where id=?";
		Object[] args=new Object[]{userId};
		User user=(User) this.template.find(sql, args,new RowMapper(){

			public Object rowMapper(ResultSet rs) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setMoney(rs.getFloat("money"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
			}
		});
		return user;
	}
	
	public List findByName(String name) {
		String sql="select id,name,birthday,money from user  where name=?";
		Object[] args=new Object[]{name};
		List all=(List) this.template.find(sql, args,new RowMapper(){

			public Object rowMapper(ResultSet rs) throws SQLException {
				List all=new ArrayList();
				while(rs.next()){
					User user = new User();
					user.setId(rs.getInt("id"));
					user.setName(rs.getString("name"));
					user.setMoney(rs.getFloat("money"));
					user.setBirthday(rs.getDate("birthday"));
					all.add(user);
				}
				return all;
			}
		});
		return all;
	}
	
	public String getUserName(int userId) {
		String sql="select name from user  where id=?";
		Object[] args=new Object[]{userId};
		String name=(String) this.template.find(sql, args,new RowMapper(){

			public Object rowMapper(ResultSet rs) throws SQLException {
				return rs.getObject("name");
			}
			
		});
		return name;
	}
	
	public int update(User user) {
		String sql="update user set name=?,birthday=?,money=? where id=?";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney(),user.getId()};
		return this.template.update(sql, args);
	}
	
	public int delete(User user) {
		String sql="delete from user where id=?";
		Object[] args=new Object[]{user.getId()};
		return this.template.update(sql, args);
	}
	
	public int insert(User user){
		String sql="insert into user (name,birthday,money) values(?,?,?)";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney()};
		return this.template.update(sql, args);
	}
	
	public int insertBackPrimaryKey(User user){
		String sql="insert into user (name,birthday,money) values(?,?,?)";
		Object[] args=new Object[]{user.getName(),user.getBirthday(),user.getMoney()};
		ResultSet rs=this.template.insertBackPrimaryKey(sql, args);
		if(rs!=null){
			try {
				user.setId(rs.getInt(0));
			} catch (SQLException e) {
				throw new DAOException(e.getMessage(), e);
			}
		}
		return user.getId();
	}
}