最近搜索
Jpa 实现 in查询 JPA in 查询
管理员
2022-08-24 11:54
1 2 3 4 5 6 7 8 9 10 11 12 13 | import javax.annotation.Resource;import javax.persistence.criteria.CriteriaBuilder;import javax.persistence.criteria.CriteriaQuery;import javax.persistence.criteria.Expression;import javax.persistence.criteria.Predicate;import javax.persistence.criteria.Root;import javax.transaction.Transactional;import org.springframework.data.domain.Page;import org.springframework.data.domain.PageRequest;import org.springframework.data.domain.Pageable;import org.springframework.data.domain.Sort;import org.springframework.data.jpa.domain.Specification;import org.springframework.stereotype.Service; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Page<WorkWeight> page = workWeightRepository.findAll(new Specification<WorkWeight>() { @Override public Predicate toPredicate(Root<WorkWeight> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> list = new ArrayList<>(); if(null != subDepartmentId && !"".equals("subDepartmentId")){ list.add(cb.equal(root.<String>get("subDepartmentId"), subDepartmentId)); } if(null != status && !"".equals("status")){ list.add(cb.equal(root.<String>get("status"), status)); } if (null != startTime && !"".equals("startTime")) { list.add(cb.greaterThanOrEqualTo(root.<Date>get("startTime"), DateUtil.stringToDate(startTime))); } if (null != endTime && !"".equals("endTime")) { list.add(cb.lessThanOrEqualTo(root.<Date>get("endTime"), DateUtil.stringToDate(endTime))); } Expression<String> exp = root.<String>get("departmentId"); list.add(exp.in(idList)); // 往in中添加所有id 实现in 查询 if (list.size() != 0) { Predicate[] p = new Predicate[list.size()]; return cb.and(list.toArray(p)); } else { return null; } } }, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort())); |
本地执行sql in查询
1 2 | @Query(value="select * from t_student where id not in (?1)",nativeQuery = true)public List<Student> listNotInIds(List<Integer> ids); |
测试通过的in查询
1 2 3 4 5 6 7 8 9 10 11 12 13 | import javax.persistence.criteria.Path;// 过虑状态集合 相关于in操作if (map.get("id_list") != null) { List<Integer> id_list = (List<Integer>) map.get("id_list"); Path<Object> path = root.get("id"); CriteriaBuilder.In<Object> in = cb.in(path); for (Integer id : id_list) { in.value(id);// 存入值 } predicate.getExpressions().add(cb.and(cb.and(in)));}// 过虑状态集合 相关于in操作 |
IN查询 in查询 对象
1 2 3 4 5 6 7 8 9 | if (map.get("caigou_list") != null) { List<CaiGou> caigou_list = (List<CaiGou>) map.get("caigou_list"); Path<Object> path = root.get("caiGou"); CriteriaBuilder.In<Object> in = cb.in(path); for (CaiGou caiGou : caigou_list) { in.value(caiGou);// 存入值 } predicate.getExpressions().add(cb.and(cb.and(in)));} |
查询对象。role对象。
1 2 3 4 5 6 7 8 9 10 11 | //in查询 Path<Object> path = root.get("role"); CriteriaBuilder.In<Object> in = cb.in(path); Role role1 = new Role(); Role role2 = new Role(); role1.setId(3); role2.setId(6); in.value(role1);//存入值 in.value(role2);//存入值 predicate.getExpressions().add(cb.and(cb.and(in))); //in查询 |
查询id。
、
1 2 3 4 5 6 | Path<Object> path = root.get("id");CriteriaBuilder.In<Object> in = cb.in(path);in.value(1);//存入值in.value(3);//存入值predicate.getExpressions().add(cb.and(cb.and(in))); |
查询 字符串
1 2 3 4 5 | Path<Object> path = root.get("name"); CriteriaBuilder.In<Object> in = cb.in(path); in.value("admin");//存入值 in.value("23");//存入值 predicate.getExpressions().add(cb.and(cb.and(in))); |
完整测试
加上别的条件也是可以的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | List<Integer> idList = new ArrayList<Integer>(); idList.add(1); idList.add(2); idList.add(3); idList.add(4); idList.add(5); idList.add(6); idList.add(7); idList.add(8); idList.add(9); idList.add(10); map.put("id_list", idList); // 过虑状态集合 相关于in操作 if (map.get("id_list") != null) { List<Integer> id_list = (List<Integer>) map.get("id_list"); Path<Object> path = root.get("id"); CriteriaBuilder.In<Object> in = cb.in(path); for (Integer id : id_list) { in.value(id);// 存入值 } predicate.getExpressions().add(cb.and(cb.and(in))); } // 过虑状态集合 相关于in操作 控制台输出如下：Hibernate: select user0_.id as id1_3_, user0_.bianhao as bianhao2_3_, user0_.birth_date as birth_da3_3_, user0_.dept as dept4_3_, user0_.gongzhong as gongzhon5_3_, user0_.minzu as minzu6_3_, user0_.name as name7_3_, user0_.pwd as pwd8_3_, user0_.remark as remark9_3_, user0_.role_id as role_id17_3_, user0_.sex as sex10_3_, user0_.true_name as true_na11_3_, user0_.work_date as work_da12_3_, user0_.xueli as xueli13_3_, user0_.zhicheng as zhichen14_3_, user0_.zhiwu as zhiwu15_3_, user0_.zu as zu16_3_ from t_a_user user0_ where (user0_.id in (1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10)) and (user0_.name like ? or user0_.true_name like ?) order by user0_.id desc limit ?Hibernate: select count(user0_.id) as col_0_0_ from t_a_user user0_ where (user0_.id in (1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10)) and (user0_.name like ? or user0_.true_name like ?) |
联系站长
站长微信:xiaomao0055
站长QQ:14496453
按分类分组
按日期分组