jpa自定义sql的三种方式

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可以用来做一些需要过滤条件的查询

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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);