如何在Spring Data JPA中编写动态的本机SQL查询? [英] How to write dynamic native SQL Query in spring data JPA?
问题描述
我需要在Spring Boot Web应用程序中的数据库中的多个表上编写搜索查询.
I need to write a search query on multiple tables in database in spring boot web application.
它使用spring数据jpa.我知道我们可以使用@Query批注和native = true标志在spring数据jpa中编写本机查询.
It uses spring data jpa. I know we can write native query in spring data jpa using @Query annotation and native = true flag.
有什么方法可以在存储库类中编写查询,而不是@Query注释,因为查询非常复杂且动态.
Is there any way I can write query in repository class and instead of the @Query annotation as the Query is very complex and dynamic.
推荐答案
您需要执行CustomRepository
并添加带有本机查询的方法.
You need to do a CustomRepository
and add a method with native query.
我这样做:
-
创建您的自定义存储库:
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.
这篇关于如何在Spring Data JPA中编写动态的本机SQL查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!