Jpa动态多条件查询,普通的在dao层写sql语句是没有办法实现的,我们换种思路,如果这个字段为空可以对其进行判断,然后进行条件字段的拼接,所以下面我写了一个扩展类。
public class UserMEXT {
@PersistenceContext(unitName = "oracleuusPersistenceUnit")
EntityManager em;
public Page<Map> findByMany(Map<String, String> parameterMap){
String userid = parameterMap.get("userid");
String clientUse = parameterMap.get("clientUse");
String systemKey = parameterMap.get("systemKey");
int page = Integer.parseInt(parameterMap.get("page"));
int size = Integer.parseInt(parameterMap.get("size"));
if(page<0){
page = 0;
}
if(size<1){
size = 10;
}
Map<String, String> map = new HashMap<String, String>();
StringBuffer sql = new StringBuffer("select * from TB_UIM_USER t where 1=1");
if (!StringUtils.isEmpty(userid)){
sql.append(" and t.USERID like :userid");
map.put("userid","%"+userid+"%");
}
if(!StringUtils.isEmpty(clientUse)){
sql.append(" and t.CLIENTUSE =:clientUse");
map.put("clientUse",clientUse);
}
if(!StringUtils.isEmpty(systemKey)){
sql.append(" and t.SYSTEM_KEY =:systemKey");
map.put("systemKey",systemKey);
}
sql.append(" order by t.ADD_TIME");
Query dataQuery = em.createNativeQuery(sql.toString(), User2DTO.class);
for(String key : map.keySet()){
dataQuery.setParameter(key, map.get(key));
}
String countSQL = "select count(*) from ("+sql+")";
Query countQuery = em.createNativeQuery(countSQL);
for(String key : map.keySet()) {
countQuery.setParameter(key, map.get(key));
}
Pageable pageable = new PageRequest(page,size);
BigDecimal count = (BigDecimal) countQuery.getSingleResult();//查询总条数
dataQuery.setFirstResult(size * (page));//设置要检索的第一个结果的位置(从0开始)
dataQuery.setMaxResults(size);//设置要检索的最大结果数
List<User2DTO> resultList = dataQuery.getResultList();
List<UserDto> dto = find2Dto(resultList);
Page<Map> findByPage = new PageImpl(dto, pageable,count.intValue());
return findByPage;
}
}
Service实现层:
public Page<Map> findByMany(Map<String, String> parameterMap) {
Page<Map> byMany = userMEXT.findByMany(parameterMap);
return byMany;
}
Controller层:
@PostMapping("/findByMany")
public String findByMany(@RequestBody String json){
Map<String, String> parameterMap= (Map<String, String>) JSON.parse(json);
Page<Map> byMany = userMService.findByMany(parameterMap);
return ResponseWrapperUtil.executeResultPageMap(byMany);//这用的是我自己的工具类,返回这里根据你设定的返回类型具体情况具体分类。
}