51工具盒子英文:
Spring Data JDBC dynamic where clauses
问题 {#heading}
-
有没有一种方法可以使用Spring Data JDBC编写动态的
WHERE查询？ -
我如何访问底层的
DataSource（或者也许是JdbcTemplate）以直接执行此查询？
例如：
this.jdbcTemplate.query = query // 具有6个或更少的`WHERE`子句的动态查询;
List<Result> results = this.jdbcTemplate.run();
谢谢！
编辑
有一个JdbcAggregateTemplate，带有一个<T> Iterable<T> findAll(Query query, Class<T> domainType); 查询。这是正确的地方吗？
与其他帖子相反，我无法自动装配EntityManager bean。
英文:
I have a custom search page, where i can enter up to 6 search options. This options are combined with AND so by using all filters i have 6 where clauses and so on.
Two questions:
-
Is there a way to code this dynamic where query with spring data jdbc?
-
How can i access the underlying
DataSource(or maybe aJdbcTemplate) to fire this query directly?
For example:
this.jdbcTemplate.query = query // the dynamic query with 6 or less where clauses;
List<Result> results = this.jdbcTemplate.run();
Thank you!
EDIT
There is a JdbcAggregateTemplate with a <T> Iterable<T> findAll(Query query, Class<T> domainType); query. Is this the right place?
In contrary to other posts stated, i cannot autowire an EntityManager bean.
答案1 {#1}
得分: 2
以下是翻译好的部分：
-
你可以使用按示例查询。
-
你可以创建一个
Example，从一个已填充了部分条目的实体中创建：Person person = new Person();
person.setFirstname("Dave");Example
example = Example.of(person);
然后可以将其与扩展了QueryByExampleExecutor的存储库一起使用：
public interface QueryByExampleExecutor<T> {
<S extends T> S findOne(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example);
// ...省略了更多功能。
}
你可以微调示例的使用方式：
Person person = new Person();
person.setFirstname("Dave");
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("lastname")
.withIncludeNullValues()
.withStringMatcher(StringMatcher.ENDING);
Example<Person> example = Example.of(person, matcher);
- 使用
JdbcAggregateTemplate。
它具有接受Query的半公共特性。它没有很好的文档说明。查看源代码以了解如何使用它。
- 使用
JdbcTemplate。
你需要手动构建你的SQL。
- 你不能注入
EntityManager。
它是一个JPA组件，与Spring Data JDBC无关。 英文:
You have multiple options:
You can use Query By Example. {#you-can-use-query-by-example1}
You'd create an Example from an entity where some entries are filled:
Person person = new Person();
person.setFirstname("Dave");
Example<Person> example = Example.of(person);
And then using it with your repository, which would extend QueryByExampleExecutor:
public interface QueryByExampleExecutor<T> {
<S extends T> S findOne(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example);
// ... more functionality omitted.
}
and you can fine tune how the example is used:
Person person = new Person();
person.setFirstname("Dave");
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("lastname")
.withIncludeNullValues()
.withStringMatcher(StringMatcher.ENDING);
Example<Person> example = Example.of(person, matcher);
Use the JdbcAggregateTemplate {#use-the-jdbcaggregatetemplate}
It has the semi public feature of accepting a Query.
It is not properly documented. Checkout the source code for how to use it.
Use a JdbcTemplate {#use-a-jdbctemplate}
You'll have to construct your SQL manually
You can NOT inject an EntityManger {#you-can-not-inject-an-entitymanger}
It is a JPA artefact and Spring Data JDBC has nothing to do with JPA.
答案2 {#2}
得分: 0
Jens的答案绝对正确。
在与同事讨论后，我得出了与他的建议相对的解决方案。
由于我有6个搜索选项，查询构建可能会有很多切换块。
简而言之，我做了这个（未编辑或缩短以显示查询的复杂性）：
@Query("""
select p.projectname, p.unit, p.cost_unit as costUnit, p.chapter, u.username, w.firstname, w.lastname, e.comment, e.value, e.entry_date as entry,
w.active_from as activeFrom, w.active_to as activeTo, w.type, w.hwl, w.further, w.compensation, w.delegation, w.amount
from projects p
left join users u on p.users_id = u.id
left join workers w on w.projects_id = p.id
left join entries e on e.workers_id = w.id
where trim(lower(p.projectname)) like concat('%', lower(:projectName))
and trim(lower(concat(w.firstname, w.lastname))) like concat('%', lower(:workerName))
and trim(lower(u.username)) like concat('%', lower(:userName))
and extract(year from e.entry_date) >= :year
and trim(lower(p.cost_unit)) like concat('%', lower(:costUnit), '%')
and trim(lower(p.unit)) like concat('%', lower(:unit), '%')""")
List<SearchResultDto> search(String projectName, String workerName, String userName, Integer year, String costUnit, String unit);
诀窍是对字符串查询使用%运算符。当参数为null或空字符串时，我会将其传递给查询。
因此，％会返回给定列的所有条目。
而％projectName会返回包含projectName的所有条目。
年份也是同样的原理。如果在用户界面中选择了年份，它将传递给查询。如果没有选择，我会使用0作为年份，以便选择所有年份。
英文:
Jens` answer is absolutely right.
After talks with collegues i ended up with an alternative to his tips.
Since i have 6 search options the query building will be likely a lot of switch blocks.
In short: I did this (not edited or shortened to sketch the complexity of the query):
@Query("""
select p.projectname, p.unit, p.cost_unit as costUnit, p.chapter, u.username, w.firstname, w.lastname, e.comment, e.value, e.entry_date as entry,
w.active_from as activeFrom, w.active_to as activeTo, w.type, w.hwl, w.further, w.compensation, w.delegation, w.amount
from projects p
left join users u on p.users_id = u.id
left join workers w on w.projects_id = p.id
left join entries e on e.workers_id = w.id
where trim(lower(p.projectname)) like concat('%', lower(:projectName))
and trim(lower(concat(w.firstname, w.lastname))) like concat('%', lower(:workerName))
and trim(lower(u.username)) like concat('%', lower(:userName))
and extract(year from e.entry_date) >= :year
and trim(lower(p.cost_unit)) like concat('%', lower(:costUnit), '%')
and trim(lower(p.unit)) like concat('%', lower(:unit), '%')""")
List<SearchResultDto> search(String projectName, String workerName, String userName, Integer year, String costUnit, String unit);
The trick is to use the % operator for string queries. When the parameter is null or empty string, i'll pass it on to the query.
So % yields all entries for the given column.
Whereas %projectName yields all entries with projectName in it.
The same works on year. If a year is selected in the UI, it goes to the query, If not, i use 0 as year so all years will be selected.
