Ok that makes total sense. It's clear now why that wasn't working as
expected.
So if I understand correctly, when I need a PLAIN SQL String that contains
a full query with everything inlined I need to first build my query parts
and then use the right dialect to produce the complete by calling DSL.sql
and wrapping that in DSLContext.render?
Like so:
DSLContext create = DSL.using(SQLDialect.POSTGRES_9_5);
String sqlString = create.renderInlined(DSL.sql("SELECT * FROM FOO WHERE
{0} AND {1}", condition1, condition2));
Thanks for your help Lukas.
I'm actually refactoring a lot of code to be less stringy and instead pass
around Jooq QueryParts....
On Monday, May 15, 2017 at 2:41:24 AM UTC-4, Lukas Eder wrote:
>
> Hi Max
>
> Your first approach is the correct one, because it runs through the
> "normal" query rendering lifecycle governed by your Configuration, which
> contains Settings and more importantly, a SQLDialect.
>
> When you call QueryPart.toString(), there is no such Configuration
> available, so the DefaultConfiguration and the SQLDialect.DEFAULT applies,
> which mostly renders a SQL standard version of a QueryPart (with no
> guarantee).
>
> If you want to work with String templates, we do have a feature for this.
> Wrap your SQL query in any of these:
>
> DSL.sql():
> https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#sql-java.lang.String-org.jooq.QueryPart...-
> DSLContext.query():
> https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#query-java.lang.String-org.jooq.QueryPart...-
> DSLContext.resultQuery():
> https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#resultQuery-java.lang.String-org.jooq.QueryPart...-
>
> For example:
>
> ResultQuery<?> q = ctx.resultQuery(
> "SELECT * FROM posts WHERE {0}",
> c
> );
>
> More info here:
> https://www.jooq.org/doc/latest/manual/sql-building/plain-sql
>
> In any case, you should never rely QueryPart.toString(). I guess we could
> improve the Javadoc:
> https://www.jooq.org/javadoc/latest/org/jooq/QueryPart.html#toString--
>
> I've created an issue for this:
> https://github.com/jOOQ/jOOQ/issues/6217
>
> Hope this helps,
> Lukas
>
> 2017-05-12 22:11 GMT+02:00 Max Kremer <[email protected] <javascript:>>
> :
>
>> Hi,
>>
>> I have two different code paths that make use of org.jooq.Condition
>> and add it to the where clause of a query. The first path is all JOOQ in
>> that it creates a query using mostly JOOQ DSL, the second code path is more
>> "stringy" and creates the SQL in a String buffer and adds to it various
>> JOOQ conditions.
>>
>> I'm experiencing an error in the second code path because the condition
>> is rendered differently than in the first case and PostgreSQL complains
>> about casting.
>>
>> To illustrate, we have a column called TAGS of type varchar[ ] on a table
>> named POSTS
>>
>> org.jooq.Condition c = DSL.field("tags",String[].class).contains(
>> aStringArray);
>>
>> The above condition can be used two different ways in our code:
>>
>> 1. Like this:
>>
>> d.select(...).from(table("posts").where( c );
>>
>>
>>
>> the WHERE clause renders:
>> WHERE tags @> '{"tag2"}'
>>
>> 2. Or like this
>>
>> String sql = "Select * FROM posts where " + c.toString();
>>
>> the WHERE clause renders:
>> WHERE tags @> ARRAY['tag9']
>>
>> and I get the following error from postgresql
>>
>> ERROR: operator does not exist: character varying[] @> text[] at
>> character 387
>> HINT: No operator matches the given name and argument type(s). You might
>> need to add explicit type casts.
>>
>> Postgres is being picky and wants the string 'tag9' cast to type varchar.
>>
>> Why is the jooq condition being rendered two different ways? How can I
>> fix this?
>>
>>
>>
>>
>> --
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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.
