INSERT + SELECT + ON DUPLICATE KEY UPDATE

[jdoe]

Hello!

First of all my sincere congratulations to the new Java Champion!!!

And back to the jOOQ. I'm wondering if it allows to combine the three mentioned clauses in a single query, something like:

DSL.insertInto(table, fieldsToInsert).select(select)).onDuplicateKeyUpdate(updateMap)

Now I can use  onDuplicateKeyUpdate only after values(). Any way to acomplish it with jOOQ?

[Lukas Eder]

Hi Sergey,

Thanks for your nice words. It has been an exciting week indeed, and I'm honoured to have received this title from Oracle. I'll do my best to keep up with the expectations that go along with it :)

Regarding your question, this is a missing feature in jOOQ right now:

https://github.com/jOOQ/jOOQ/issues/1560

You'll have to work around this lack of functionality by using plain SQL, I suspect.

Best Regards,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jdoe]

Thank you for confirming that adding some plain SQL was inevitable!

By the way, how can I take advantage of the automatic field name wrapping when constructing this plain "extension"? I mean jOOQ figures out that my fields in query have to be named: `db`.`table`.`field`. Is it possible to achieve some level of flexibility beyond the simple DSL.query(almostCompleteQuery.getSql() + missingSqlFeature)?

пятница, 20 ноября 2015 г., 19:05:41 UTC+2 пользователь Lukas Eder написал:

[Lukas Eder]

Hi Sergey,

Sure, jOOQ's plain SQL templating "language" allows for embedding QueryParts in SQL strings. The DSL#query(String,QueryPart...) method has an example in the Javadoc:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#query-java.lang.String-org.jooq.QueryPart...-

Create a new query holding plain SQL.

Unlike query(String, Object...), the SQL passed to this method should not contain any bind variables. Instead, you can pass QueryPart objects to the method which will be rendered at indexed locations of your SQL string as such:

 // The following query
 query("select {0}, {1} from {2}", val(1), inline("test"), name("DUAL"));

 // Will render this SQL on an Oracle database with RenderNameStyle.QUOTED:
 select ?, 'test' from "DUAL"

Hope this helps,

Let me know if you have any additional questions,

Lukas