Dao封装

一、java.util.Date和java.sql.Date的区别

从数据库中获得的结果集rs.getDate()获得的是java.sql.Date

将一个日期存储到数据库中，stmt.setDate(date) date 是java.sql.Date

java.sql.Date是java.util.Date的子类，直接把java.sql.Date当成util.Date使用

java.util.Date对象变成java.sql.Date,通过时间量转换：

new java.sql.Date(java.util.Date对象.getTime())

 二、Dao封装

1. 继承BaseDao ，是所有Dao的父类，所有Dao要使用的属性，调用的执行SQL语句方法都封装在BaseDao

2. 实现IDao接口，是用来实现访问数据库方法的标准的。

public interface IDao<Entity,PK> {
    /**
    * 将实体对象的属性数据插入到 对应的表中
    * @param entity 实体对象
    * @return 受影响的行数
    */
    public int insert(Entity entity);
    public int update(Entity entity);
    /**
    * 按主键删除
    * @param id
    * @return
    */
    public int deleteByPrimaryKey(PK[] id);
    /**
    * 按主键查询
    * @param id
    * @return
    */
    public Entity selectByPrimaryKey(PK id);
    /**
    * 按条件查询
    * @param map 传递查询条件的map对象，key尽量与表的列名相同
    * @return
    */
    public List<Entity> selectByCondition(Map<String,Object> map);
    /**
    * 按条件分页查询
    * @param map 查询条件
    * @param pageNo 当前页数
    * @param pageSize 每页显示的记录数
    * @return
    */
    public List<Entity> selectForPage(Map<String,Object> map,int pageNo,int pageSize);
    /**
    * 按条件获得记录数
    * @param map
    * @return
    */
    public int count(Map<String,Object> map);
}

public class StudentDao extends BaseDao implements IDao<Student,Integer>{
    @Override
    public int insert(Student entity) {
    //拼SQL语句 全列插入
    String sql = "insert into student(id,sno,sname,birthday,tel,height,amt) values (default,?,?,?,?,?,?)";
    //准备值的Object[]
    Object[] values = new Object[]{entity.getSno(),entity.getSname(),entity.getBirthday(),entity.getTel(),entity.getHeight(),entity.getAmt()};
        //调用BaseDao的insert方法
        return insert(sql,values);
        }
        @Override
    public int update(Student entity) {
        //全列的更新
        String sql = "update student set sno=?,sname=?,birthday=?,tel=?,height=?,amt=? where id=?";
        Object[] values = new Object[] {entity.getSno(),entity.getSname(),entity.getBirthday(),
        entity.getTel(),entity.getHeight(),entity.getAmt(),entity.getId()};
        return update(sql,values);
        }
    @Override
    public int deleteByPrimaryKey(Integer[] ids) {
        String sql = "delete from student where id in (";
        for(int id:ids) {
        sql += "?,";
        }
    //截取最后一个逗号
        sql = sql.substring(0, sql.length()-1);
        sql += ")";
        return delete(sql,ids);
        }
    @Override
    public Student selectByPrimaryKey(Integer id) {
        String sql = "select id,sno,sname,birthday,tel,height,amt from student where id = ?";
        Object[] values = new Object[] {id};
        return selectById(sql, values, new StudentConvert());
        }
        //{"amt1"=100}
    @Override
    public List<Student> selectByCondition(Map<String, Object> map) {
        String sql = "select id,sno,sname,birthday,tel,height,amt from student where 1=1 ";
        if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
        sql +=" and sno = ? ";
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        sql +=" and sname like ? ";
        }
        if(map.get("amt1")!=null) {
        sql +=" and amt > ?";
        }
        if(map.get("amt2")!=null) {
        sql +=" and amt < ?";
        }
        //准备Object[]
        List list = new ArrayList();
        if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
            list.add(map.get("sno"));
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        list.add("%"+map.get("sname")+"%");
        }
        if(map.get("amt1")!=null) {
        list.add(map.get("amt1"));
        }
        if(map.get("amt2")!=null) {
        list.add(map.get("amt2"));
        }
        return select(sql, list.toArray(), new StudentConvert());
        }
    @Override
    public List<Student> selectForPage(Map<String, Object> map, int pageNo, int pageSize) {
        String sql = "select id,sno,sname,birthday,tel,height,amt from student where 1=1 ";
        if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
        sql +=" and sno = ? ";
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        sql +=" and sname like ? ";
        }
        if(map.get("amt1")!=null) {
        sql +=" and amt > ?";
        }
        if(map.get("amt2")!=null) {
        sql +=" and amt < ?";
        }
        //拼 limit语句
        sql += "limit ?,?";
        //准备Object[]
        List list = new ArrayList();
        if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
        list.add(map.get("sno"));
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        list.add("%"+map.get("sname")+"%");
        }
        if(map.get("amt1")!=null) {
        list.add(map.get("amt1"));
        }
        if(map.get("amt2")!=null) {
        list.add(map.get("amt2"));
        }
        list.add((pageNo-1)*pageSize);
        list.add(pageSize);
        return select(sql, list.toArray(), new StudentConvert());
        }
    @Override
    public int count(Map<String, Object> map) {
        String sql = "select count(0) from student where 1=1 ";
if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
        sql +=" and sno = ? ";
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        sql +=" and sname like ? ";
        }
        if(map.get("amt1")!=null) {
        sql +=" and amt > ?";
        }
        if(map.get("amt2")!=null) {
        sql +=" and amt < ?";
        }
        //准备Object[]
        List list = new ArrayList();
        if(map.get("sno")!=null&&!"".equals(map.get("sno"))) {
        list.add(map.get("sno"));
        }
        if(map.get("sname")!=null&&!"".equals(map.get("sname"))) {
        list.add("%"+map.get("sname")+"%");
        }
        if(map.get("amt1")!=null) {
        list.add(map.get("amt1"));
        }
        if(map.get("amt2")!=null) {
        list.add(map.get("amt2"));
        }
        Integer result = selectById(sql,list.toArray(),new IntegerConvert());
        return result;
        }
    class StudentConvert implements Convert<Student>{
    @Override
    public Student convert(ResultSet rs) {
        Student s = new Student();
        try {
            s.setId(rs.getInt("id"));
            s.setSno(rs.getString("sno"));
            s.setTel(rs.getString("tel"));
            s.setHeight(rs.getDouble("height"));
            s.setAmt(rs.getInt("amt"));
            s.setSname(rs.getString("sname"));
            s.setBirthday(rs.getDate("birthday"));
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return s;
    }
}
class IntegerConvert implements Convert<Integer>{
    @Override
    public Integer convert(ResultSet rs) {

        try {
            return rs.getInt(1);
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }
}
public static void main(String[] args) {
        StudentDao dao = new StudentDao();
/*Student s = new Student();
s.setSno("110110");
s.setSname("张飞");
try {
s.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("2000-01-01"));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
s.setAmt(100);
s.setHeight(1.7);
s.setTel("10011111111");
dao.insert(s);*/
/* Student s = dao.selectByPrimaryKey(36);
s.setSname("张大飞");
dao.update(s);*/
//dao.deleteByPrimaryKey(new Integer[]{33,34});
/* Map<String,Object> map = new HashMap<String,Object>();
map.put("sname", "o");
map.put("amt1", "100");
List<Student> list = dao.selectByCondition(map);
for(Student s:list) {
System.out.println(s.getSname()+","+s.getAmt());
}*/
/*Map<String,Object> map = new HashMap<String,Object>();
map.put("amt1", "0");
List<Student> list = dao.selectForPage(map, 2, 5);
for(Student s:list) {
System.out.println(s.getSname()+","+s.getAmt());
}*/
/* Map<String,Object> map = new HashMap<String,Object>();
map.put("amt1", "0");
System.out.println(dao.count(map));*/
        }
}