CODEWITHSUNDEEP
javajooq
res1
res1

Reputation: 3640

jOOQ - query builder with IN predicate

I am trying to build a query like this:

List<Integer> ids = ...

String query = DSL.select(TABLE.SOMETHING).from(TABLE).where(TABLE.ID.in(ids)).
getSQL();

But I am not able to get the generated query with the values, just the placeholders. I tried DSL.inline(ids) but it doesnt' work.

How can I do this?

I am using jOOQ 3.4.2.

Thanks for the help.

UPDATE:

Seems I can do this with:

    Configuration configuration = new DefaultConfiguration();
    configuration.set(SQLDialect.DERBY);
    Settings settings = new Settings()
    .withStatementType(StatementType.STATIC_STATEMENT);
    configuration.set(settings);
    DSLContext create = DSL.using(configuration);

    String query = create.select(TABLE.SOMETHING).from(TABLE).where(TABLE.ID.in(ids)).getSQL();

If someone can confirm that is th right way, thanks.

Upvotes: 3

Views: 1673

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

You cannot inline a list with jOOQ's DSL.inline() because if you could, the semantics of such a value would be that of a list/array literal in the database, not of a list of individual values.

Correct way to use DSL.inline():

Here's one correct way to pass a list of inlined values to the Field.in(Field<?>...):

List<Integer> ids = ...

String query = DSL.using(configuration) // Use a Configuration or at least a SQLDialect!
                  .select(TABLE.SOMETHING)
                  .from(TABLE)
                  .where(TABLE.ID.in(ids.stream().map(DSL::inline).collect(toList())))
                  .getSQL();

Inline all bind values on a per-getSQL() basis:

Use Query.getSQL(ParamType)

List<Integer> ids = ...

String query = DSL.using(configuration)
                  .select(TABLE.SOMETHING)
                  .from(TABLE)
                  .where(TABLE.ID.in(ids))
                  .getSQL(ParamType.INLINED);

Inline all bind values on a per-Configuration basis:

The solution you've mentioned in your question edit is valid as well, of course:

List<Integer> ids = ...
Configuration configuration = new DefaultConfiguration();
configuration.set(new Settings().withStatementType(StatementType.STATIC_STATEMENT));

String query = DSL.using(configuration)
                  .select(TABLE.SOMETHING)
                  .from(TABLE)
                  .where(TABLE.ID.in(ids))
                  .getSQL();

Upvotes: 2

Related Questions