Spring Boot之Jpa动态SQL语句

标签： Springboot 分类： Java 创建时间：2020-07-01 05:26:26 更新时间：2024-11-15 10:49:43

1.在新建Repository时继承 JpaSpecificationExecutor 接口：

1
2
3

public interface SiteinfoRepo extends JpaRepository<SiteInfo,Long>, JpaSpecificationExecutor {
    List<SiteInfo> findAll();
}

2.在Service层时实现自定义的find函数，

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

@Service
public class SiteinfoService {
    @Autowired
    private SiteinfoRepo siteinfoRepo；
    @PersistenceContext
    private EntityManager entityManager;

    public List<SiteInfo> findSiteInfo(String minDate,String maxDate,Sting nickname){
        List<SiteInfo> resultList = null;
        Specification querySpecifi = new Specification<SiteInfo>() {
            @Override
            public Predicate toPredicate(Root<SiteInfo> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> predicates = new ArrayList<>();
                if(null != minDate){
                    predicates.add(criteriaBuilder.greaterThan(root.get("subscribeTime"), minDate));
                    /*
                    Expression<Integer> expression=root.get("MediumType");
                    Predicate predicate=expression.in(mediumTypes);
                    predicates.add(predicate);*/
                }
                if(null != maxDate){
                    predicates.add(criteriaBuilder.lessThan(root.get("subscribeTime"), maxDate));
                }
                if(null != nickname){
                    predicates.add(criteriaBuilder.like(root.get("nickname"), "%"+nickname+"%"));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        resultList =  this.siteinfoRepo.findAll(querySpecifi);
    }
}

参考文章：
1.Hibernate使用原生的动态sql实现带条件的查询分页功能
2.EntityManager
3.spring Boot整合jpa实例化EntityManager？
4.JPA - EntityManager详解
5.Hibernate动态条件查询(Criteria Query)
6.Spring Data JPA，一种动态条件查询的写法 (这个特别有用，可以找到CriteriaQuery的用法)
7.spring data jpa 利用JpaSpecificationExecutor做复杂查询
8.JPA CriteriaBuilder - How to use “IN” comparison operator (这里给了动态生成in的灵感)
9.JPA Criteria Query API and order by two columns

3.Specification排序

public List<Client> findClient(List<Integer> siteListNo, String orderName, String orderContent){
        List<Client> resultList = null;
        Specification querySpecifi = new Specification<Client>() {
            @Override
            public Predicate toPredicate(Root<Client> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> predicates = new ArrayList<>();
                if(null != siteListNo&&siteListNo.size()>0){
                    //predicates.add(criteriaBuilder.in(root.get("subscribeTime"), groups));
                    Expression<Integer> expression=root.get("SiteNo");
                    Predicate predicate=expression.in(siteListNo);
                    predicates.add(predicate);
                }
                // 条件查询
                Predicate p=criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
                criteriaQuery.where(p);
                // 排序
                if(orderName!=null&&orderContent!=null){
                    if(orderContent.toLowerCase().equals("asc")){
                        criteriaQuery.orderBy(criteriaBuilder.asc(root.get(orderName)));
                    }else {
                        criteriaQuery.orderBy(criteriaBuilder.desc(root.get(orderName)));
                    }
                }else {
                    criteriaQuery.orderBy(criteriaBuilder.desc(root.get("SiteNo")));
                }
                return criteriaQuery.getRestriction();
            }
        };
        resultList =  this.clientRepo.findAll(querySpecifi);
        return resultList;
    }

参考文章：
1.JPA Specification常用查询+排序
2.Sorting with JPA

3.Specification自定义选择sql语句

使用Specification自定义选择，即又要创建sql语句，又要进行条件查询。
(1) 创建原生查询的方式
使用正确的entityManage，就可以使用entityManager创建原生的查询，并和实体类进行关联。

@Service
public class SecHisService {
    @Autowired
    private SecHisRepository secHisRepository;

    @PersistenceContext
    private EntityManager entityManager;

    public List<SecHis> findAlarmRecrder(Integer sectionId, Date start_date,Date end_date,
                                         Double velocity_min,Double velocity_max,Double heat_min,
                                         Double heat_max,Double hLoss_min,Double hLoss_max) {
        List<SecHis> resultList=null;
        entityManager.getProperties();
        Query query= entityManager.createNativeQuery("select row_number() over (order by RecordTime ) as id, * from secHis",SecHis.class);
        resultList=query.getResultList();
        return resultList;
    }
}

参考文章：
1.JPA的查询语言—使用原生SQL (写了查询单个属性，查询多个属性和查询一个类的原生sql创建方法)
2.createNativeQuery set parameter (给NativeQuery加参数的方法)

(2) CriteriaQuery API
使用安全查询工厂API，但是没有找到如何创建原生查询的方法，就像上面的第一和第二种问题，以及下面的多篇参考文章，都是介绍了如何使用Criteria API进行查询。其中CriteriaQuery 安全查询主语句；Root 定义查询的From子句中能出现的类型；Predicate 过滤条件；而CriteriaBuilder就是将上面的三者进行组合，最后组成了一个完整的查询。

Specification自定义选择sql语句，最终的代码

import com.proheng.gis.sqlserverEntity.SecHis;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;

@Service
public class SecHisService {
    @Autowired
    private SecHisRepository secHisRepository;

    @Autowired
    @Qualifier("secondEntityManager")
    private EntityManager entityManager;

    public List<SecHis> findAlarmRecrder(Integer sectionId, Date start_date,Date end_date,
                                         Double velocity_min,Double velocity_max,Double heat_min,
                                         Double heat_max,Double hLoss_min,Double hLoss_max) {
        List<SecHis> resultList=null;
        String sql="";
        if(null != sectionId){
            sql+=" sectionId ="+sectionId;
        }
        if(null != start_date&&end_date!=null){
            sql+=" recordTime between "+start_date+" and "+end_date;
        }
        // 如果velocity、heat、hLoss的最大值最小值都为空，则返回空记录
        if(velocity_min==null&&velocity_max==null&&heat_min==null&&heat_max==null&&hLoss_max==null&&hLoss_min==null) {
            return null;
        }
        // 进行条件查询
        Query query= entityManager.createNativeQuery(sql,SecHis.class);
        resultList=query.getResultList();


        return resultList;
    }

}

参考文章：
1.【一目了然】Spring Data JPA使用Specification动态构建多表查询、复杂查询及排序示例 (定以复杂查询的方法,使用了Specification接口)
2.SpringDataJpa的Specification查询
3.Java CriteriaQuery.select方法代碼示例
4.Creating Queries Using the Java Persistence Query Language (创建query的方法)
5.使用 Query 物件
6.JPA执行原生SQL返回指定对象 (这里指定原生的sql创建)
7.了解如何利用 Java 持久性查询语言和原生 SQL 查询 JPA 实体。
8.java-jpa-criteriaBuilder使用入门 (这里讲了CriteriaBuilder、CriteriaQuery、Predicate 过滤条件的联系)
9.Native Queries – How to call native SQL queries with JPA & Hibernate (讲了如何创建native sql)
10.JPA标准API 动态sql和分页实现 (这里讲了如何使用Criteria API等)
11.JPA Criteria API Queries (这篇文章讲了多个使用Criteria的例子，也讲了如何使用JPA Criteria API进行查询)

问题

(1) Unknown entity: com.proheng.gis.sqlserverEntity.SecHis
这个问题和下一个问题一起解决了，参考文章都没有用。

参考文章：
1.hibernate Unknown entity异常解决方案
2.hibernate 报 Unknown entity错误，问题解决方法
3.spring data jpa createNativeQuery 错误 Unknown entity

(2) 错误: 关系 “sechis” 不存在
在执行sql查询的时候，我明明定义了表名，但是最后执行的结果却和我的表名不相同，于是就报关系不存在，因为我数据库中的表的表名是secHis，而不是sechis。

我也同时定义了hibernate的命名策略。

jpa:
  hibernate:
    naming:
      implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
      physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

于是我查了EntityManager相关资料

我突然想到了，我使用了多数据的配置，所以我这里的entityManager,应该使用我自定义的那个Bean,而不是系统自带的Bean

@Bean(name = "secondEntityManager")
public EntityManager entityManager() {
    return entityManagerFactoryBean().getObject().createEntityManager();
}

使用@Qualifier进行命名Bean的调用。

1
2
3

@Autowired
@Qualifier("secondEntityManager")
private EntityManager entityManager;

参考文章：
1.Using JPQL in Java EE applications
2.SpringBoot配置实体管理器EntityManager
3.Spring Boot常用注解（二） - 注入Bean的注解 (这里有@Qualifier注解的用法)
4.Querying JPA Entities with JPQL and Native SQL (讲了简单的例子以及使用entityManager管理类)

往期推荐

文章目录

微信公众号

广告位

诚心邀请广大金主爸爸洽谈合作

每日一省

isNaN 和 Number.isNaN 函数的区别？

1.函数 isNaN 接收参数后，会尝试将这个参数转换为数值，任何不能被转换为数值的的值都会返回 true，因此非数字值传入也会返回 true ，会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字，如果是数字再继续判断是否为 NaN ，不会进行数据类型的转换，这种方法对于 NaN 的判断更为准确。

每日二省

为什么0.1+0.2 ! == 0.3，如何让其相等?

一个直接的解决方法就是设置一个误差范围，通常称为“机器精度”。对JavaScript来说，这个值通常为2-52，在ES6中，提供了Number.EPSILON属性，而它的值就是2-52，只要判断0.1+0.2-0.3是否小于Number.EPSILON，如果小于，就可以判断为0.1+0.2 ===0.3。

每日三省

== 操作符的强制类型转换规则？

1.首先会判断两者类型是否**相同，**相同的话就比较两者的大小。

2.类型不相同的话，就会进行类型转换。

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。

每日英语

Happiness is time precipitation, smile is the lonely sad.

幸福是年华的沉淀，微笑是寂寞的悲伤。