Case insensitive regex with Postgres and JOOQ?
I am currently using this code with JOOQ:
Condition condition = DSL.trueCondition();
if( isNotBlank(email) ){
condition = condition.and(APP_USER.EMAIL.likeRegex(email));
}
This creates a custom Postgres SQL address that executes the regular expression on the database:
app_user.email ~ '{email regex}'
Is it possible for JOOQ to release case insensitive version app_user.email ~* '{email regex}'
:?
My current workaround is to use this code:
if( isNotBlank(email) ){
condition = condition.and(
APP_USER.EMAIL.lower().likeRegex(email.toLowerCase()) );
}
source to share
If there is no jOOQ API, it is vendor specific, the response is "plain SQL" . In your case, just write this:
PostgreSQL specific solution
Condition condition = DSL.trueCondition();
if( isNotBlank(email) ){
condition = condition.and("{0} ~* {1}", APP_USER.EMAIL, DSL.val(email));
}
The method Condition.and(String, QueryPart...)
is just a convenience for creating explicit plain SQL Condition
via DSL.condition(String, QueryPart...)
:
Condition condition = DSL.trueCondition();
if( isNotBlank(email) ){
condition = condition.and(DSL.condition("{0} ~* {1}", APP_USER.EMAIL, DSL.val(email)));
}
Supplier agnostic
If you want to be an agro agent, you will have to wrap the above code in your own utility and resort to using CustomCondition
public static Condition caseInsensitiveLikeRegex(Field<String> field, String regex) {
return new CustomCondition() {
@Override
public void accept(Context<?> ctx) {
if (ctx.family() == POSTGRES)
ctx.visit(DSL.condition("{0} ~* {1}", field, DSL.val(regex));
else
ctx.visit(field.lower().likeRegex(regex.toLowerCase()));
}
}
}
The call is regex.toLowerCase()
not 100% correct, of course, as it shrinks both the content of the regex and escaped patterns such as \B
for backslash, but you get the idea.
source to share