1：在repository接口上注解@Query参数
1：@Query("select o from AgentInfo o where o.userId = ?1 and o.balance<0")
2：@Query(value = "SELECT * FROM fl_agentinfo a inner join(SELECT id FROM fl_agentinfo where user_id = ?1 and device_wxid = ?2 order by id desc limit ?3,15) b on a.id = b.id",nativeQuery = true)
加上 nativeQuery = true 字段名称就要对应数据库，可以实现稍微复杂一些的连表查询
修改的话注意要加@Modifying 和 @Transactional注解
2.第二种 实现Specification可以用来做一些需要过滤条件的查询
agentInfoRepository.findAll(new Specification<AgentInfo>() {
@Override
public Predicate toPredicate(Root<AgentInfo> root,
CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(root.get("userId"), userId));
if(StringUtils.isNotEmpty(deviceWxId)){
predicates.add(builder.equal(root.get("deviceWxId"), deviceWxId));
}
if(StringUtils.isNotEmpty(wxName)){
predicates.add(builder.equal(root.get("wxName"), wxName));
}
query.where(predicates.toArray(new Predicate[predicates.size()]));
return null;
}
}, pageable);
3.使用entityManager完全自定义的拼接sql
StringBuilder datasql = new StringBuilder("SELECT * FROM fl_handlercash a inner join (select id from fl_handlercash where user_id ="+userId); StringBuilder countSql = new StringBuilder("select count(*) from fl_handlercash where user_id ="+userId); Date startTime = null; Date endTime = null; if (StringUtils.isNotEmpty(startTimeStr)) { startTime = new Date(NumberUtils.toLong(startTimeStr)); endTime = new Date(NumberUtils.toLong(endTimeStr)); datasql.append(" and createTime between ‘"+DateTimeUtil.dateToStr(startTime)+"‘ and ‘"+DateTimeUtil.dateToStr(endTime)+"‘"); countSql.append(" and createTime between ‘"+DateTimeUtil.dateToStr(startTime)+"‘ and ‘"+DateTimeUtil.dateToStr(endTime)+"‘"); } if (status != -1) { if (status == 0) { datasql.append(" and status = 0"); countSql.append(" and status = 0"); } else { datasql.append(" and status <> 0"); countSql.append(" and status <> 0"); } } if (StringUtils.isNotEmpty(wxId)) { datasql.append(" and cash_wxid =‘"+ wxId+"‘"); countSql.append(" and cash_wxid =‘"+ wxId+"‘"); } if (StringUtils.isNotEmpty(deviceWxId)) { datasql.append(" and device_wxId =‘"+ deviceWxId+"‘"); countSql.append(" and device_wxId =‘"+ deviceWxId+"‘"); } datasql.append(" order by id desc limit "+(page-1)*10+",10) b on a.id = b.id"); List<HandlerCash> handlerCashes = entityManager.createNativeQuery(datasql.toString(),HandlerCash.class).getResultList(); BigInteger count = (BigInteger)entityManager.createNativeQuery(countSql.toString()).getSingleResult(); PageBean<HandlerCash> pageBean = new PageBean(page+1, count.intValue(), 0,handlerCashes);
本文是个人代码总结，方便以后查找。如果各位有其他方式欢迎分享