模糊查询实现的分页和删除多条或者单条数据的dao层实现

工具类DButil（导入对应的jar包）

public class DbUtils {
    public static QueryRunner qr;
    static {
        DataSource da=new ComboPooledDataSource();
        qr=new QueryRunner(da);
    }
}

分页数据查询

 @Override
    public List<Map<String, Object>> selectAll(String p_name, Integer is_state, String start_time, String end_time, Integer currentPageNo, Integer pageSize) {//模糊查询的分页条目数量
        String sql_ad = "select * from (select  pid ,(select `name` from category as cate where cate.cid=product.cid  ) as cid , `name`,color,price, description,pic,\n" +
                "state,`version`,product_date\n" +
                "from product ) as pro  where 1=1";//模糊查询sql语句
        StringBuffer sql = new StringBuffer(sql_ad);
        List<Object> list = new ArrayList<Object>();//生成集合进行存数据
        if (p_name != null && p_name != "") {//也就是存在数据执行模糊查询
            //给sql语句追加内容
            sql.append(" and pro.name like ?");
            list.add("%" + p_name + "%");//给对应的sql赋值
        }
        if (is_state != null ) {
            sql.append(" and pro.state=?");
            list.add(is_state);
        }
        if (start_time != null && start_time != "") {
            sql.append(" and pro.product_date>=? ");
            list.add(start_time);
        }
        if (end_time != null && end_time != "") {
            sql.append(" and pro.product_date<=? ");
            list.add(end_time);
        }
        sql.append(" limit ?,?");
        list.add((currentPageNo-1)*pageSize);
        list.add(pageSize);
        /* Object[] objects = {(currentPageNo - 1)*pageSize, pageSize};*/
        try {
        //因为分页查询的数据的列名和实体类的不一定对应，
            //多表联查返回的数据使用map的list集合接收，再取值时，必须按照map的键来取值
            List<Map<String, Object>> mapList = DbUtils.qr.query(sql.toString(), new MapListHandler(), list.toArray());
            return mapList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

查询总记录数

 @Override
 public Integer selectCount(String p_name, Integer is_state, String start_time, String end_time) {//获取此时分页条件的总的条目数
        String sql_All = "select count(*) from (select  pid ,(select `name` from category as cate where cate.cid=product.cid  ) as cid , `name`,color,price, description,pic,\n" +
                "state,`version`,product_date\n" +
                "from product ) as pro  where 1=1";
        StringBuffer sql_Count=new StringBuffer(sql_All);
        List<Object> list= new ArrayList<Object>();//表示可以存任何类型的的数据作为集合的内容
        if (p_name != null && p_name != "") {//也就是存在数据执行模糊查询
            //给sql语句追加内容
            sql_Count.append(" and pro.name like ?");
            list.add("%" + p_name + "%");//给对应的sql赋值
        }
        if (is_state != null) {
            sql_Count.append(" and pro.state=?");
            list.add(is_state);
        }
        if (start_time != null && start_time != "") {
            sql_Count.append(" and pro.product_date>=? ");
            list.add(start_time);
        }
        if (end_time != null && end_time != "") {
            sql_Count.append(" and pro.product_date<=? ");
            list.add(end_time);
        }
        try {
            //记得转化为long类型，再返回是转换integer
            Long query = (Long) DbUtils.qr.query(sql_Count.toString(), new ScalarHandler(),list.toArray());
            return Integer.parseInt(query + "");//注意这里把long转换为字符串再转换为integer
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

删除一条或多条数据

  @Override
    public Integer deleteAll(String AllId) {//删除一条或者多条数据
        String sql = "delete from product where pid in(" + AllId + ")";
        try {
            int update = DbUtils.qr.update(sql);
            return update;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }