模糊分页查询语句提示!

JAVA ZMAS 90℃ 0评论
@Override
public List<User> findUserByPage(int start, int rows,QueryUserRequestParams params) {

    //定义一个基础的sql语句

    String sql = "select * from user WHERE 1=1 ";

    StringBuffer stringBuffer = new StringBuffer(sql);

    //参数集合

    List list = new ArrayList();

    //拼接第一个条件
    String name = params.getName();
    if(name!=null&&!"".equals(name)){
        stringBuffer.append(" and name like ? ");
        list.add("%"+name+"%");
    }
    //拼接第二个条件
    String address = params.getAddress();
    if(address!=null&&!"".equals(address)){
        stringBuffer.append(" and address like ? ");
        list.add("%"+address+"%");
    }
    //拼接第三个条件
    String email = params.getEmail();
    if(email!=null&&!"".equals(email)){
        stringBuffer.append(" and email like ? ");
        list.add("%"+email+"%");
    }

    //拼接分页条件
    stringBuffer.append(" limit ?,?");
    list.add(start);
    list.add(rows);
    return jdbcTemplate.query(stringBuffer.toString(),new BeanPropertyRowMapper<User>(User.class),list.toArray());

}

@Override

public int findCountByParams(QueryUserRequestParams params) {

    String sql = "select count(*) from user where 1=1 ";

    // 准备拼接动态sql

    StringBuffer stringBuffer = new StringBuffer(sql);

    List list = new ArrayList();

    //拼接name条件

    String name = params.getName();

    if(name!=null&&!"".equals(name)){

        stringBuffer.append(" and name like ? ");

        list.add("%" + name + "%");

    }

    //拼接address条件

    String address = params.getAddress();

    if(address!=null&&!"".equals(address)){

        stringBuffer.append(" and address like ? ");

        list.add("%" + address + "%");

    }

    //拼接email条件

    String email = params.getEmail();

    if(email!=null&&!"".equals(email)){

        stringBuffer.append(" and email like ? ");

        list.add("%" + email + "%");

    }

    return jdbcTemplate.queryForObject(stringBuffer.toString(),Integer.class,list.toArray());

}

转载请注明:ZMAS‘S blog » 模糊分页查询语句提示!

喜欢 (5)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址