spring data jpa基本增删改查，复杂动态查询及分页

1、select

@Query("from User where id = ?1")
User queryById(Integer id);

@Query(" from User where id = ?1 and name = ?2")
User queryByIdAndName(Integer id,String name);

@Query("from User where id in (:ids)")
List<User> findByIds(@Param("ids") List<Integer> ids);

2、delete

@Modifying
@Query("delete from User where id = ?1")
void deleteByID(Integer id);

3、update

@Modifying
@Query("update User set name = :name where id = :id")
void updateByID(@Param("name") String name,@Param("id") Integer id);

4、动态查询（in、and、or、like、日期范围）

sql语句：select * from User  where (name like ?) and id = 1 and str in (1,2,3)
//查询条件拼接
private Specification<User> createSpecification(Map searchMap) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                if (searchMap.get("id")!=null && !"".equals(searchMap.get("id"))) {
                    predicates.add(cb.equal(root.get("id"),searchMap.get("id")));
                }
                if (searchMap.get("name")!=null && !"".equals(searchMap.get("name"))) {
                    predicates.add(cb.like(root.get("name").as(String.class), "%"+(String)searchMap.get("name")+"%"));
                }
                String str = "1,2,3";
                String[] strs = str.split(",");
                CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("str"));
                for (String s:strs) {
                    in.value(s);
                }
                predicates.add(in);
                return cb.and( predicateList.toArray(new Predicate[predicates.size()]));
            }
        };
}

 

sql语句： select * from User  where (name like ? or address is null) and id = 1
//查询条件拼接
private Specification<User> createSpecification(Map searchMap) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                List<Predicate> predicateList = new ArrayList<Predicate>();
                if (searchMap.get("id")!=null && !"".equals(searchMap.get("id"))) {
                    predicates.add(cb.equal(root.get("id"),searchMap.get("id")));
                }
                if (searchMap.get("name")!=null && !"".equals(searchMap.get("name"))) {
                    predicateList.add(cb.like(root.get("name").as(String.class), "%"+(String)searchMap.get("name")+"%"));
                }
                if (searchMap.get("address")!=null && !"".equals(searchMap.get("address"))) {
                    predicateList.add(cb.isNull(root.get("address").as(String.class)));
                }
                Predicate predicateAnd = cb.and(predicateList.toArray(new Predicate[predicateList.size()]));
                predicateAnd = cb.and(predicateAnd);
                Predicate predicateOr = cb.or(predicates.toArray(new Predicate[predicates.size()]));
                predicateOr = cb.and(predicateOr);
                return query.where(predicateOr,predicateAnd).getRestriction();
            }
        };
}

sql语句： select * from User  where time>=? and time<=? and id = 1
//查询条件拼接
private Specification<User> createSpecification(Map searchMap) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                if (searchMap.get("id")!=null && !"".equals(searchMap.get("id"))) {
                    predicates.add(cb.equal(root.get("id"),searchMap.get("id")));
                }
                if (searchMap.get("time")!=null && !"".equals(searchMap.get("time"))) {
                    //开始时间
                    Date date = user.getTime();
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(date);
                    calendar.set(Calendar.HOUR_OF_DAY,0);
                    calendar.set(Calendar.MINUTE, 0);
                    calendar.set(Calendar.SECOND, 0);
                    calendar.set(Calendar.MILLISECOND, 0);
predicates.add(cb.greaterThanOrEqualTo(root.get("time").as(Date.class), calendar.getTime()));
                    //结束时间
                    calendar.set(Calendar.HOUR_OF_DAY,23);
                    calendar.set(Calendar.MINUTE, 59);
                    calendar.set(Calendar.SECOND, 59);
                    calendar.set(Calendar.MILLISECOND, 999);
predicates.add(cb.lessThanOrEqualTo(root.get("time").as(Date.class), calendar.getTime()));
                }
                Predicate predicate = cb.and(list.toArray(new Predicate[predicates.size()]));
                return query.where(predicate).getRestriction();
            }
        };
}

5、实现分页、排序

不分页：
Map<String,Object> searchMap = new HashMap();
searchMap.put("id",1);
searchMap.put("name","zs");
searchMap.put("address",null);
//searchMap:传参
Specification<User> spec = createSpecificationLogin(searchMap);
List<User> list = userDao.findAll(spec);


分页、排序
Integer pageNo = 0; //页码
Integer pageSize = 10; //每页展示条数
if (!StringUtils.isEmpty(vo.getPageNo())) pageNo = vo.getPageNo() - 1;
if (!StringUtils.isEmpty(vo.getPageSize())) pageSize = vo.getPageSize();
Pageable page = new PageRequest(pageNo,pageSize,Sort.by("id").descending());//id为排序字段
Specification<User> spec = createSpecificationLogin(searchMap);
Page<User> pageList = userDao.findAll(spec, page);