Digest for jooq-user@googlegroups.com - 10 updates in 4 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 10 updates in 4 topics

http://groups.google.com/group/jooq-user/topics mailing list
Lukas Eder <[hidden email]>: Nov 28 11:39AM +0100

Hi Michael,
 
I see, thanks for the clarification.
 
*A note on selectFrom()*
 
The usage of selectFrom() can lead to such confusion, because the inferred
type of <R> in the resulting Select<R> is the type of the table, e.g.
HomeEmailRecord or WorkEmailRecord (I'm assuming that ATTENDEES is your
schema). In that case, due to how generics work in Java, union cannot be
used, because those two records are not compatible.
 
This doesn't work exactly as in SQL, where it is totally possible to write
 
SELECT * FROM home_email
UNION
 
SELECT * FROM work_email
 
 
iff both tables have the same row type. Usually, selectFrom() is used to
fetch an "active record" (or UpdatableRecord) for modification and later
storing it back to the database:
https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords
 
When you write a union, however, you usually do not want to fetch active
records, but simply tuples, so you will list all of your columns explicitly.
 
*Regarding your specific query:*
 
I've just noticed from your description, that ATTENDEES is not your schema,
but your table. In that case, you've attempted to put a **column** (HOME_EMAIL
or WORK_EMAIL) in the FROM clause of your statement, which doesn't work.
Here's a translation of your intended SQL statement to jOOQ:
 
context.select(
concat(ATTENDEES.LAST_NAME, inline(", "), ATTENDEES.FIRST_NAME,
inline(" (home)")).as("name"),
 
ATTENDEES.HOME_EMAIL)
 
.from(ATTENDEES)
 
.where(ATTENDEES.LIST.eq(1))
 
.union(
 
select(
concat(ATTENDEES.LAST_NAME, inline(", "), ATTENDEES.FIRST_NAME,
inline(" (work)")).as("name"),
 
ATTENDEES.WORK_EMAIL)
 
.from(ATTENDEES)
 
.where(ATTENDEES.LIST.eq(1)))
 
.fetch();
 
 
The above is assuming, you have this import
 
import static org.jooq.impl.DSL.*;
 
 
More info about the inline() method (as well as bind values in general)
here:
https://www.jooq.org/doc/latest/manual/sql-building/bind-values
 
I hope this helps,
Lukas
 
Michael Redlich <[hidden email]>: Nov 28 09:32AM -0800

Hi Lukas:
 
Thanks for helping me out with this. I certainly appreciate it! It worked
like a charm. Here's the final method:
 
public void testUnion(DSLContext context) {
// "SELECT CONCAT(last_name,', ',first_name,' (home)') AS name,home_email FROM attendees WHERE list = 1 UNION SELECT CONCAT(last_name,', ',first_name,' (work)') AS name,work_email FROM attendees WHERE list = 1";
 
Result<Record2<String,String>> result = context.select(
concat(ATTENDEES.LAST_NAME,inline(", "),ATTENDEES.FIRST_NAME,inline(" (home)")).as("name"),ATTENDEES.HOME_EMAIL)
.from(ATTENDEES)
.where(ATTENDEES.LIST.eq(Boolean.TRUE))
.orderBy(ATTENDEES.LAST_NAME)
.union(select(concat(ATTENDEES.LAST_NAME,inline(", "),ATTENDEES.FIRST_NAME,inline(" (work)")).as("name"),ATTENDEES.WORK_EMAIL)
.from(ATTENDEES)
.where(ATTENDEES.LIST.eq(Boolean.TRUE)).orderBy(ATTENDEES.LAST_NAME))
.fetch();
 
for(Record2<String,String> record : result) {
String name = record.value1();
String email = record.value2();
if(email != null) {
System.out.println("\"" + name + "\"" + " <" + email + ">");
}
}
}
 
 
 
And the output is:
...
"Redlich, Michael (home)" <[hidden email]>
...
 
On Sunday, November 26, 2017 at 8:47:17 PM UTC-5, Michael Redlich wrote:
Lukas Eder <[hidden email]>: Nov 28 06:40PM +0100

Hi Michael,
 
Thanks for the feedback. I'm glad it helped.
 
As a SQL performance person, here's one minor nit pick:
 
 
> if(email != null) {
 
> How about moving that check into the query? You could add a
ATTENDEES.WORK_EMAIL.isNotNull() and ATTENDEES.HOME_EMAIL.isNotNull()
checks to each individual subquery :)
 
Cheers,
Lukas
Michael Redlich <[hidden email]>: Nov 28 10:24AM -0800

Hi Lukas:
 
Great, thanks! I will indeed give that a try!
 
Mike.
 
 
On Sunday, November 26, 2017 at 8:47:17 PM UTC-5, Michael Redlich wrote:
Lukas Eder <[hidden email]>: Nov 28 05:10PM +0100

Hi Joe,
 
Thank you very much for reporting this. That's a very interesting edge case
that arises from the fact that jOOQ:
 
- Calls all applicable "setters" according to the specification of the
DefaultRecordMapper, in no particular order
- Accepts both JavaBeans compliant setters (setId() for the ID column) as
well as "jOOQ style" setters (setId() or SET_ID() for the SET_ID column)
 
In a way, this "works as designed", even if in this particular case, it's
obvious that the design is flawed. We'll have to fix this in a future minor
release, by adding some flags that allow for turning off either one of the
above "setter" styles:
https://github.com/jOOQ/jOOQ/issues/6870
 
Until then, workarounds include:
 
- Renaming columns as you've noticed
- Working around this particular issue on a per-table and per-into() call
basis
- Implementing your own mapper and registering that as a
RecordMapperProvider [1]
 
I hope this helps,
Lukas
 
[1]:
https://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos-with-recordmapper-provider
 
Mohan <[hidden email]>: Nov 27 10:25PM -0800

Hi,
 
I need help in the below to code to generate right select from values with
the values I am passing instead of parameters.
 
Below is the sample code i am trying to execute.
 
DSLContext dsl;
Settings settings = new Settings()
.withRenderNameStyle(RenderNameStyle.AS_IS).withRenderFormatted(true);
dsl = DSL.using(SQLDialect.DEFAULT, settings);
String sql = dsl.select().from(DSL.values(DSL.row(43,1234,"value1"),DSL.row(43,1235,"value2"))).getSQL();
System.out.println(sql);
 
 
Output is generating as below.
 
 
select
v.c1,
v.c2,
v.c3
from (values
(?, ?, ?),
(?, ?, ?)
) v(c1, c2, c3)
 
 
So I need help in fixing the above to generate as below because my database
doesn't support column aliases
 
select
column1,
column2,
column3
from values (43,1234,'value1'),(43,1235,'value2')
 
Is there any way I can achieve the above format?
 
Regards,
Mohan
Lukas Eder <[hidden email]>: Nov 28 11:30AM +0100

Hi Mohan,
 
You chose the SQLDialect.DEFAULT dialect, which is a dialect that can be
used for documentation purposes (e.g. when calling toString(), etc.), and
usually generates SQL standard compliant SQL, if possible, although there
is no such guarantee.
 
You should replace that dialect by the dialect of your target database, and
then jOOQ will either generate the derived column list as you got it
already"v(c1, c2, c3)", or it will emulate derived column lists using UNION
ALL. This is documented here:
https://blog.jooq.org/2013/01/07/simulating-the-sql-standard/
 
I hope this helps,
Lukas
 
Mohan <[hidden email]>: Nov 28 05:39AM -0800

Hi,
 
Thanks for the reply. My target database is Snowflake. So which dialect I should use. Also I am using jooq as sql query generator.
Lukas Eder <[hidden email]>: Nov 28 02:46PM +0100

Hi Mohan,
 
Well, that's a good question :) We don't officially support Snowflake, so
it may well be that this particular feature cannot be properly emulated.
You could use a dialect like Oracle for this particular query, but then
other queries might stop working...
 
I suggest you don't use the VALUES() clause, but manually write your own
SELECT .. UNION ALL SELECT .. UNION ALL SELECT .. derived table.
 
I hope this helps,
Lukas
 
Lukas Eder <[hidden email]>: Nov 28 12:53PM +0100

Hi Manuel,
 
Thank you very much for your message.
 
In the generated SQL, there's a double whitespace, which seems to indicate
that the name of your constraint was simply empty (""). I wonder why that
is. Are the names registered correctly in the generated Keys.java class?
Can you share the constraint definition here?
 
Thanks,
Lukas
 
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].