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.idrue)

加上 nativeQuery = true 字段名称就要对应数据库，可以实现稍微复杂⼀些的连表查询 修改的话注意要加@Modifying 和 @Transactional注解2.第⼆种 实现Specification可以⽤来做⼀些需要过滤条件的查询

agentInfoRepository.findAll(new Specification() { @Override

public Predicate toPredicate(Root root,

CriteriaQuery query, CriteriaBuilder builder) { List predicates = new ArrayList(); 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+\

List handlerCashes = entityManager.createNativeQuery(datasql.toString(),HandlerCash.class).getResultList(); BigInteger count = (BigInteger)entityManager.createNativeQuery(countSql.toString()).getSingleResult(); PageBean pageBean = new PageBean(page+1, count.intValue(), 0,handlerCashes);

本⽂是个⼈代码总结，⽅便以后查找。如果各位有其他⽅式欢迎分享