Databases
StackOverflow2013
Postsdbo
POJOOQ sql generation for where in quotation seems inconsistent for multiple Objects
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJOOQ sql generation for where in quotation seems inconsistent for multiple Objects
    primarykey
    data
    text
    <p>I have been working with JOOQ to generate my SQL, but I have been having trouble with the "in" clause, I wrote a simple Test that shows what I mean:</p> <pre class="lang-java prettyprint-override"><code>@Test public void testWhereInBehaviorJOOQ() { String expectedSingleObjectSQL = "select distinct \"Business Group\" \"Business_Group\" from \"SOME_TABLE\" where \"Business Group Process\" in ('SomeValue')"; String expectedMultipleObjectSQL = "select distinct \"Business Group\" \"Business_Group\" from \"SOME_TABLE\" where \"Business Group Process\" in ('SomeValue','AnotherValue')"; String actualSingleObjectSQL = generateSQLWithWhereInInline("SomeValue"); String actualTwoObjectSQL = generateSQLWithWhereInInline("SomeValue,AnotherValue"); String actualTwoObjectWithQuotesSQL = generateSQLWithWhereInInline("'SomeValue','AnotherValue'"); String actualTwoObjectWithMiddleQuotesSQL = generateSQLWithWhereInInline("SomeValue','AnotherValue"); LOGGER.info("JOOQ Generated query for single object \"someValue\" = {}",actualSingleObjectSQL ); LOGGER.info("JOOQ Generated query for two objects without single quotes \"someValue,AnotherValue\" = {}",actualTwoObjectSQL ); LOGGER.info("JOOQ Generated query for two objects with single quotes \"'someValue','AnotherValue'\" = {}",actualTwoObjectWithQuotesSQL); LOGGER.info("JOOQ Generated query for two objects with single quotes only at the middle \"someValue','AnotherValue\" = {}",actualTwoObjectWithMiddleQuotesSQL ); Assert.assertEquals(expectedSingleObjectSQL,actualSingleObjectSQL); Assert.assertEquals(expectedMultipleObjectSQL,actualTwoObjectSQL); Assert.assertEquals(expectedMultipleObjectSQL,actualTwoObjectWithQuotesSQL); Assert.assertEquals(expectedMultipleObjectSQL,actualTwoObjectWithMiddleQuotesSQL); } private String generateSQLWithWhereInInline(String businessGroupProcess) { DSLContext create = DSL.using(SQLDialect.ORACLE); return create. selectDistinct( DSL.fieldByName("Business Group").as("Business_Group")) .from(DSL.tableByName("SOME_TABLE")).where(DSL.fieldByName("Business Group Process").in(DSL.inline(businessGroupProcess))).getSQL(); } </code></pre> <p>but the generated values are:</p> <p>JOOQ Generated query for single object "someValue" = </p> <pre class="lang-sql prettyprint-override"><code>select distinct "Business Group" "Business_Group" from "SOME_TABLE" where "Business Group Process" in ('SomeValue') </code></pre> <p>JOOQ Generated query for two objects without single quotes "someValue,AnotherValue" = </p> <pre class="lang-sql prettyprint-override"><code>select distinct "Business Group" "Business_Group" from "SOME_TABLE" where "Business Group Process" in ('SomeValue,AnotherValue') </code></pre> <p>JOOQ Generated query for two objects with single quotes "'someValue','AnotherValue'" =</p> <pre class="lang-sql prettyprint-override"><code>select distinct "Business Group" "Business_Group" from "SOME_TABLE" where "Business Group Process" in ('''SomeValue'',''AnotherValue''') </code></pre> <p>JOOQ Generated query for two objects with single quotes only at the middle "someValue','AnotherValue" = </p> <pre class="lang-sql prettyprint-override"><code>select distinct "Business Group" "Business_Group" from "SOME_TABLE" where "Business Group Process" in ('SomeValue'',''AnotherValue') </code></pre> <p>so when there is only one object it works fine, for two it only appends quotes to the beginning and the end, if i add quotes to them before it adds quotes to the beginning, end and each object, and if i try and "trick" it by adding quotes in the middle hoping it will try to wrap the variables in quotes everything get quotes. Spaces do not seem to help.</p> <p>has anyone encountered this before? I want to know if I am calling it wrong or if its a bug.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLukas Eder
    1. USGMelo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJOOQ sql generation for where in quotation seems inconsistent for multiple Objects
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload