Modify INSERT to UPSERT

[sto...@gmail.com]

Hi

I'm trying to make a slight modification to the generated SQL from INSERT to UPSERT but can't make it happen with ExecutionListeners (which works fine for SELECT).

The ExecutionListener is called but the changes does not affect the actual statement that later get executed.

Cheers,

-Kristoffer

[Lukas Eder]

Hi Kristoffer,

We're more than happy to help, but it would be easier if you show us what you've tried so far... :)

Cheers

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.

[Kristoffer Sjögren]

Sorry, I was a bit hasty on the send button I must admit :-)

... these are the insert statements ...

DSL.using(getConf(con)).executeInsert(record);

DSL.using(getConf(con))

 .insertInto(TABLE)

 .set(record)

.execute();

... using the following configuration ...

Configuration configuration = new DefaultConfiguration().set(con).set(SQLDialect.MYSQL);

configuration.set(new DefaultExecuteListenerProvider(new PhoenixListener()));

... with the following listener ...

public class PhoenixListener extends DefaultExecuteListener {

  @Override

  public void start(ExecuteContext ctx) {

    String sql = ctx.sql();

    System.out.println(sql);

  }

  @Override

  public void renderEnd(ExecuteContext ctx) {

    String sql = ctx.sql();

    sql = sql.replace("insert", "UPSERT");

    sql = sql.replace("`", "");

    ctx.sql(sql);

  }

  @Override

  public void prepareEnd(ExecuteContext ctx) {

    ctx.query();

    String sql = ctx.sql();

    sql = sql.replace("insert", "UPSERT");

    sql = sql.replace("`", "");

    ctx.sql(sql);

  }

  @Override

  public void bindEnd(ExecuteContext ctx) {

    String sql = ctx.sql();

    System.out.println(sql);

  }

}

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/-gI2yrEuaik/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hello,

Your listener is correct and it works for me, but I just noticed that perhaps you meant that the patched SQL doesn't appear in jOOQ's default log output, which is logged by the LoggerListener. That's true, because the LoggerListener is the first one that is applied, and I wonder if that is the correct behaviour. Perhaps we should log stuff only after all the custom ExecuteListeners have been invoked... I have registered an issue for this:

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

You can work around this issue by manually adding the org.jooq.tools.LoggerListener to your listeners.

Note that this:

    sql = sql.replace("`", "");

... is not necessary if you use the RenderNameStyle.AS_IS Setting:

- http://www.jooq.org/javadoc/latest/org/jooq/conf/RenderNameStyle.html#AS_IS

- http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/

Hope this helps,

Lukas

[Kristoffer Sjögren]

Thanks for the tips Lukas. 

After looking more closely I can see that the JOOQ code seems to do the right thing actually. It more looks like the DB may have problems with PreparedStatements.

But as a workaround a prerolled statement like this will work.

con.createStatement().execute("UPSERT into TABLE (A, B, C) values (1, 2, 3)");

Is it possible to create such a query using JOOQ records?

[Lukas Eder]

2015-01-07 14:33 GMT+01:00 Kristoffer Sjögren <sto...@gmail.com>:

Thanks for the tips Lukas. 

After looking more closely I can see that the JOOQ code seems to do the right thing actually. It more looks like the DB may have problems with PreparedStatements.

Interesting, what database are you using?

 

But as a workaround a prerolled statement like this will work.

con.createStatement().execute("UPSERT into TABLE (A, B, C) values (1, 2, 3)");

Is it possible to create such a query using JOOQ records?

You can specify the jOOQ Settings to generate StatementType.STATIC_STATEMENT. An example is also given on this page:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings

The effect is that all bind values will be escaped and inlined by jOOQ, and only "static" java.sql.Statements are executed, as opposed to PreparedStatements.

[Kristoffer Sjögren]

I found an error on my part that had nothing to do with either database or jooq, sorry for the confusion.

We run phoenix.apache.org which is a SQL skin over HBase. It works quite well with the MySQL dialect of JOOQ.

I have created a very simple org.jooq.util.Database implementation that read Phoenix DDL statements from file and transform them into JOOQ classes, if you might be interested in a contribution of some sort.

--

[Lukas Eder]

Hello,

Interesting. We've seen a couple of references of people using jOOQ with Phoenix / HBase in the past. So far, we have been a bit reluctant of providing a formal integration, as these "Somewhat-SQL" Implementations tend to be very limited in scope, and the jOOQ API might seem to be overkill.

But perhaps we should start looking into a formal integration, nonetheless.

I'd be curious about your org.jooq.util.Database implementation. Perhaps, this would indeed be a basis for a contribution!

Cheers

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.