英文:
DSL optional WHERE clause based on a parameter
问题
以下是您要翻译的内容：
public List<User> getUser(String status) {
return dsl.select()
.from(Users.USERS)
.where(Users.USERS.STATUS.eq(status))
.fetch()
.map(…);
}
public List<User> getUser(String status, String name) {
return dsl.select()
.from(Users.USERS)
.where(Users.USERS.STATUS.eq(status))
.and(仅在'NAME'不为空时考虑的可选WHERE子句)
.fetch()
.map(…);
}
在IDE建议的DSL方法中，我没有找到任何看起来可以在这种情况下帮助的内容。
英文:
I am new to JOOQ and DSL, this is the problem that I’m currently trying to solve. I have a simple method that returns a list of users with a certain status from a database table:
public List<User> getUser (String status) {
return dsl.select()
.from(Users.USERS)
.where(Users.USERS.STATUS.eq(status))
.fetch()
.map(…);
In my request, I can also specify the name of the users that I want to be included in that list, so the “name” parameter is not mandatory.
Is it possible to add an optional WHERE clause to my database query, which will only be considered if the “name” parameter is not null?
public List<User> getUser (String status, String name) {
return dsl.select()
.from(Users.USERS)
.where(Users.USERS.STATUS.eq(status))
.and(OPTIONAL WHERE CLAUSE DEPENDENT ON WHETHER ‘NAME’ IS NULL OR NOT)
.fetch()
.map(…);
Among the DSL-methods suggested by the IDE I didn’t find anything that looked like it would help in this situation.
答案1
得分: 1
每个 jOOQ 查询都是动态 SQL 查询，正如手册中所述，因此您可以动态地将谓词添加到各种子句中：
使用局部变量：
Condition condition = USERS.STATUS.eq(status);
if (name != null)
condition = condition.and(USERS.NAME.eq(name));
dsl.select()
.from(USERS)
.where(condition)
.fetch()
使用表达式：
dsl.select()
.from(USERS)
.where(USERS.STATUS.eq(status))
.and(name != null ? USERS.NAME.eq(name) : noCondition())
.fetch()
其他方式
有许多其他方式，jOOQ 并不关心您如何构建 jOOQ 查询。一个查询只是一堆方法调用，看起来很像一个静态查询，但它不是静态的，您可以决定以什么顺序和什么参数调用哪些方法。
英文:
Every jOOQ query is a dynamic SQL query, as stated in the manual, so you can just dynamically add your predicates to your various clauses:
Using local variables:
Condition condition = USERS.STATUS.eq(status);
if (name != null)
condition = condition.and(USERS.NAME.eq(name));
dsl.select()
.from(USERS)
.where(condition)
.fetch()
Using expressions:
dsl.select()
.from(USERS)
.where(USERS.STATUS.eq(status))
.and(name != null ? USERS.NAME.eq(name) : noCondition())
.fetch()
See the example about optional conditions in the manual.
Other ways
There are many other ways, it doesn't really matter to jOOQ how you construct a jOOQ query. A query is just a bunch of method calls that happens to look quite a bit like a static query, but it isn't static, and you decide which methods to call in what order and with what arguments.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论