模拟Spring的JdbcTemplate实现DAO

通常情况下，普通DAO的实现大概是这样的：

 

1、实体类：

public class Emp {
	private Integer id;
	private String name;
	private String sex;
	private Float sal;
                //getter and setter methods...
}

 2、DAO接口：

public interface EmpDAO {
	public int insert(Emp emp)throws SQLException;
	public int delete(Emp emp)throws SQLException;
	public int update(Emp emp)throws SQLException;
	public Emp findById(Emp emp)throws SQLException;
	public String findName(Emp emp)throws SQLException;
}

 

3、DAO实现类：

public class EmpDAOImpl implements EmpDAO {
	public int delete(Emp emp) throws SQLException {
		String sql = "delete from emp where id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, emp.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}
	public Emp findById(Emp emp) throws SQLException {
		String sql = "select id,name,sal,sex from emp where id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Emp currEmp = null;

		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, emp.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				currEmp = new Emp();
				currEmp.setId(rs.getInt("id"));
				currEmp.setName(rs.getString("name"));
				currEmp.setSal(rs.getFloat("sal"));
				currEmp.setSex(rs.getString("sex"));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return currEmp;
	}
	public String findName(Emp emp) throws SQLException {
		String sql = "select name from emp where id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String name = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, emp.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				name = rs.getString("name");
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return name;

	}
	public int insert(Emp emp) throws SQLException {
		String sql = "insert into emp(name,sal,sex) values(?,?,?)";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.setFloat(2, emp.getSal());
			ps.setString(3, emp.getSex());
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}
	public int update(Emp emp) throws SQLException {
		String sql = "update emp set name=?,sal=?,sex=? where id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.setFloat(2, emp.getSal());
			ps.setString(3, emp.getSex());
			ps.setInt(4, emp.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}
}

 

以下是改造后的实现方式：

 

1、实体类同上

2、接口同上

3、用于处理查询结果的接口：

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

 

4、模拟JdbcTemplate类

public class JdbcTemplate {
	public int update(String sql, Object[] args) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}
	public Object find(String sql, Object[] args, RowMapper rowMapper)
			throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object obj = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				obj = rowMapper.mapperRow(rs);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return obj;
	}

 5、DAO实现类：

public class EmpDAOJdbcTemplateImpl implements EmpDAO {
	private JdbcTemplate jdbcTemplate = new JdbcTemplate();
	public int delete(Emp emp) throws SQLException {
		String sql = "delete from emp where id=?";
		Object[] args = new Object[] { emp.getId() };
		return jdbcTemplate.update(sql, args);
	}
	public Emp findById(Emp emp) throws SQLException {
		String sql = "select id,name,sal,sex from emp where id=?";
		Object[] args = new Object[] { emp.getId() };
		Object obj = jdbcTemplate.find(sql, args, new RowMapper() {
			@Override
			public Object mapperRow(ResultSet rs) throws SQLException {
				Emp emp = new Emp();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setSal(rs.getFloat("sal"));
				emp.setSex(rs.getString("sex"));
				return emp;
			}
		});
		return (Emp) obj;
	}
	public String findName(Emp emp) throws SQLException {
		String sql = "select name from emp where id=?";
		Object[] args = new Object[] { emp.getId() };
		Object obj = jdbcTemplate.find(sql, args, new RowMapper() {
			@Override
			public Object mapperRow(ResultSet rs) throws SQLException {
				return rs.getString("name");
			}
		});
		return (String) obj;

	}
	public int insert(Emp emp) throws SQLException {
		String sql = "insert into emp(name,sal,sex) values(?,?,?)";
		Object[] args = new Object[] { emp.getName(), emp.getSal(),
				emp.getSex() };
		return jdbcTemplate.update(sql, args);

	}
	public int update(Emp emp) throws SQLException {
		String sql = "update emp set name=?,sal=?,sex=? where id=?";
		Object[] args = new Object[] { emp.getName(), emp.getSal(),
				emp.getSex(), emp.getId() };
		return jdbcTemplate.update(sql, args);
	}

}