insert into values

[Mohit Jaggi]

I am new to jooq and trying to start with the first step of using the DSL to avoid SQL strings in my java code. I used the insertInto() API to create an INSERT statement. I want to "cache" the statement but whenever I add a values() to it, it adds a new VALUES to the statement instead of replacing the existing one. Is there a clear() or reset() function?

Also, doesn't jooq have create table and drop table in its DSL?

[Mohit Jaggi]

Is this a known bug? PID field has duplicates in this generated code. (and getPid() as well)

public class PgStatGetActivity extends org.jooq.impl.AbstractRoutine<java.lang.Void> {

private static final long serialVersionUID = -2107175210;

/**

* The parameter <code>pg_catalog.pg_stat_get_activity.pid</code>.

*/

public static final org.jooq.Parameter<java.lang.Integer> PID = createParameter("pid", org.jooq.impl.SQLDataType.INTEGER);

/**

* The parameter <code>pg_catalog.pg_stat_get_activity.datid</code>.

*/

public static final org.jooq.Parameter<java.lang.Long> DATID = createParameter("datid", org.jooq.impl.SQLDataType.BIGINT);

/**

* The parameter <code>pg_catalog.pg_stat_get_activity.pid</code>.

*/

public static final org.jooq.Parameter<java.lang.Integer> PID = createParameter("pid", org.jooq.impl.SQLDataType.INTEGER);

On Mon, Mar 31, 2014 at 1:51 PM, Mohit Jaggi <mohit...@gmail.com> wrote:

I am new to jooq and trying to start with the first step of using the DSL to avoid SQL strings in my java code. I used the insertInto() API to create an INSERT statement. I want to "cache" the statement but whenever I add a values() to it, it adds a new VALUES to the statement instead of replacing the existing one. Is there a clear() or reset() function?

Also, doesn't jooq have create table and drop table in its DSL?

--
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/YIGSzwInQkE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hello Mohit,

2014-03-31 22:51 GMT+02:00 Mohit Jaggi <mohit...@gmail.com>:

I am new to jooq and trying to start with the first step of using the DSL to avoid SQL strings in my java code. I used the insertInto() API to create an INSERT statement. I want to "cache" the statement but whenever I add a values() to it, it adds a new VALUES to the statement instead of replacing the existing one. Is there a clear() or reset() function?

Yes, this is how jOOQ manages multi-record insert statements.

Why do you want to cache the INSERT statement? I suspect that you want to implement some memory optimisation, although I think that short-lived jOOQ statements shouldn't really be a problem. More info on that subject here:

http://www.jooq.org/doc/latest/manual/sql-execution/performance-considerations/

 

Also, doesn't jooq have create table and drop table in its DSL?

We're working on that for jOOQ 3.4:

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

Cheers

Lukas

[Mohit Jaggi]

Thanks Lukas. What happens if I call execute() on the insertInto stmt? Does that "clear" the previous values? Yes, I agree that this level of optimization is probably not needed for my application.

--

[Lukas Eder]

2014-04-01 19:43 GMT+02:00 Mohit Jaggi <mohit...@gmail.com>:

Thanks Lukas. What happens if I call execute() on the insertInto stmt? Does that "clear" the previous values?

No, that would be quite unexpected in my opinion.

Could you maybe shed some light on why you would want to reset / re-initialise values in an INSERT statement rather than creating a new statement afresh? Maybe we're talking about "symptoms" rather than trying to solve your actual problem...

[Mohit Jaggi]

I wanted to "cache" the stmt, then do values().execute(). No worries, I can create a new stmt when I need it. I think a clearValues() would be nice to have but it is not a big deal.

--

[Lukas Eder]

Hello Mohit,

2014-04-02 15:55 GMT+02:00 Mohit Jaggi <mohit...@gmail.com>:

I wanted to "cache" the stmt, then do values().execute(). No worries, I can create a new stmt when I need it. I think a clearValues() would be nice to have but it is not a big deal.

Hmm, I'm not sure if clearValues() makes much sense in the context of the DSL API, or even the model API. In the case of an INSERT statement, would clearValues() just reset all bind values to NULL, or would it remove the VALUES clause? What about multi-record INSERTs? Would all records be removed? And what would happen with the PostgreSQL INSERT .. RETURNING statement, or the MySQL INSERT .. ON DUPLICATE KEY UPDATE statement? What would happen with the SQL standard INSERT .. SELECT statement? I think you might see my reservations, now and why I wanted to understand your actual use-case first...

Note, if this is just about inserting and re-inserting a single record into a single table, you might also want to look into jOOQ's CRUD features:

http://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/

UpdatableRecord.store() etc might be more along the lines of what you're looking for.

Cheers

Lukas