Geek Answers Handbook

How to write EXTRA where articles in JOOQ

List<MyTable> result = DSL.using(configuration()) .select() .from(MY_TABLE) .where(MY_TABLE.ID1.equal(pk_id1)) .and(MY_TABLE.ID2.equal(fk_id2)) .and(MY_TABLE.ID3.equal(fk_id3)) .orderBy(MY_TABLE.ID.asc()) .limit(limit) .fetchInto(MY_TABLE) .map(mapper());

I'm trying to write some code that will allow my request to take three OPTIONAL id, for example, I would like the request to be

 select * from my_table where ID1=5 and ID2=6 and ID3=7 .... etc

However, I would also like to be able to exclude any of

 select * from my_table where ID2=6 and ID3=7

or

 select * from my_table where ID3=7

The problem is that the first phrase "where" belongs to id one, and the rest belongs to ands, so if I made an if statement and I deleted it to where I would just stay with

 List<MyTable> result = DSL.using(configuration()) .select() .from(MY_TABLE) .and(MY_TABLE.ID2.equal(fk_id2)) .and(MY_TABLE.ID3.equal(fk_id3)) .orderBy(MY_TABLE.ID.asc()) .limit(limit) .fetchInto(MY_TABLE) .map(mapper());

and it won’t work.

I tried to find something like where id = * , where * is essentianlly there is no filter, but I could not find anything like it.

+5
source share
2 answers

jOOQ makes writing SQL feel like static, embedded SQL. But this is not so. Each jOOQ query is a dynamic SQL query, consisting of a tree of expressions - you simply do not notice it.

The SelectWhereStep.where(Condition) method takes a Condition argument, which you do not need to place right there with the WHERE , you can create it before the request:

 Condition condition = DSL.trueCondition(); if (something) condition = condition.and(MY_TABLE.ID1.equal(pk_id1)); if (somethingElse) condition = condition.and(MY_TABLE.ID2.equal(fk_id2)); if (somethingOther) condition = condition.and(MY_TABLE.ID3.equal(fk_id3));

Now you can pass this into your request:

 List<MyTable> result = DSL.using(configuration()) .select() .from(MY_TABLE) .where(condition) .orderBy(MY_TABLE.ID.asc()) .limit(limit) .fetchInto(MY_TABLE) .map(mapper());

DSL also has utilities such as:

This is also described here in the manual: http://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/

+6
source

What you ask for sometimes refers to the "wildcard" of the SQL number, or at least you can find comments on the Internet if you are looking for it.

SQL does not allow you to write "where id = *", if you need your DDL to remain static, you can emulate it by performing a range check, for example

 select * from table where (my_table.id1 >= fk_id1_low and my_table.id1 <= fk_id1_high) and (my_table.id2 >= fk_id2_low and my_table.id2 <= fk_id2_high) and (my_table.id3 >= fk_id3_low and my_table.id3 <= fk_id3_high)

So, now you pass 6 parameters to the request, if you want to match id1, you will set both fk_id1_low and fk_id1_high to the value you want to match. If you do not want to match id1, you will set fk_id1_low to the lowest possible value and fk_id1_high to the highest possible value. One thing you should consider is how the resulting result query is being processed by the database engine, as a lot of extra work can be done.

With JOOQ, another possible answer is to switch from a free interface so that you can build a query using if .. then conditional parts.

+2
source

More articles:


All Articles