且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何在Spring Data JPA中编写动态的本机SQL查询?

更新时间:2023-02-03 17:11:39

您需要执行CustomRepository并添加带有本机查询的方法.

You need to do a CustomRepository and add a method with native query.

我这样做:

  1. 创建您的自定义存储库:

  1. Create your custom repository:

public interface OcorrenciaRepositoryCustom {
   List<Object[]> getStrings(List<String> valores);
}

  • 实施您的自定义存储库: (实现的名称必须是原始存储库的名称,并以Impl作为后缀.)

  • Implement your custom repository: (The name of the implemantation must be the name of original repository add Impl as suffix.)

    public class OcorrenciaRepositoryImpl implements OcorrenciaRepositoryCustom {
        @PersistenceContext
        private EntityManager entityManager;
    
        @Override
        public List<Object[]> getStrings(List<String> strings) {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT count(o.id) FROM soebm.ocorrencia o WHERE 1=1 ");
    
            if(strings.get(0)!=null && StringUtils.isNotEmpty(strings.get(0))) {
                sb.append(" AND to_char(o.date, 'YYYY-MM-DD') >= :dataInicio ");
            }
    
            Query query = entityManager.createNativeQuery(sb.toString());
    
            if(strings.get(0)!=null && StringUtils.isNotEmpty(strings.get(0).toString())) {
                query.setParameter("dataInicio", strings.get(0));
            }
            return query.getResultList();
        }
    }
    

  • 从主存储库扩展您的自定义存储库:

  • Extend your custom repository from main repository:

    public interface OcorrenciaRepository extends JpaRepository<Ocorrencia, Long>, OcorrenciaRepositoryCustom {
        Ocorrencia findByPosto(Posto posto);
    }
    

  • 现在,在服务中,您可以从主存储库调用新方法.

  • Now, in the service, you can call your new method from the main repository.

    @Autowired
    private OcorrenciaRepository repository;
    
    public List<Object[]> findOcorrenciaCustom(String str) {
        List<String> strings = new ArrayList<String>() {{add(dataInicio);}};
        return repository.getStrings(strings);
    }
    

  • 自定义存储库位于JpaRepositories搜索的软件包下很重要

    It is important that the custom repository is under the package searched by JpaRepositories

    @EnableJpaRepositories("com.test.my.repository")

    在此示例中,我使用了Spring-Data-Jpa 1.9.在我的项目中效果很好.

    I used Spring-Data-Jpa 1.9 in this example. It worked perfectly in my project.