Hi Lukas,
I'm using postgresql9.5
I ran into a stumbling block with trying to create a generic where clause
that intelligently uses the CONTAINS operator. As described in the docs
contains can be a convenience method for like and it can also render as the
array contains operator @> for postgresql.
The problem I ran into is trying to do this dynamically. The issue can be
described by the following pseudo code.
String[] array = {"foo", "bar", "baz"};
Map<Field<Object>, Object> map = new HashMap<>();
map.put(DSL.field("a"), 1);
map.put(DSL.field("b"), array);
map.put(DSL.field("c"), 2);
Condition condition = map
.entrySet()
.stream()
.reduce(
DSL.trueCondition(),
(c, e) ->
c.and(e.getKey().contains(inline(e.getValue()))),
(c1, c2) -> c1.and(c2)
);
System.out.println(condition);
The above returns:
*(*
* 1 = 1*
* and a like ('%' || cast(1 as varchar) || '%') escape '!'*
* and b like ('%' || cast(ARRAY['foo', 'bar', 'baz'] as varchar) || '%')
escape '!'*
* and c like ('%' || cast(2 as varchar) || '%') escape '!'*
*)*
What I want is
*(*
* 1 = 1*
* and a like ('%' || cast(1 as varchar) || '%') escape '!'*
* and b @> ARRAY['foo', 'bar', 'baz']*
* and c like ('%' || cast(2 as varchar) || '%') escape '!'*
*)*
Looking at the jooq source it looks the issue is line 96-97 class
Contains<T>:
// [#1107] Some dialects support "contains" operations for ARRAYs
if (lhs.getDataType().isArray()) {
return new PostgresArrayContains();
}
The above code demands that the type of Field be defined. So Field<?> or
Field<Object> won't work. When dynamically building SQL and mapping my
specific domain model to Jooq I'm materializing all sort of clauses that
are based on types determined at runtime. So potentially this is also an
issue with Java Generics....
What would happen if the above were changed to :
// [#1107] Some dialects support "contains" operations for ARRAYs
if (lhs.getDataType().isArray() || rhs.getDataType().isArray()) {
return new PostgresArrayContains();
}
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
