Enums and Batch queries

[bsha...@gmx.com]

Hello there,

I'm attempting to write some code to build a development environment from a template database using Jooq 3.4.4.  I couldn't figure out how to re-use the same generated schema objects for both the input and the output databases, so I created two of them.  Mysql Enums appear to have been created by the generator and work perfectly serially through the create record interface.  However that doesn't seem to be the case with bind within batches.  I haven't yet got to JodaTime fields but that now has me worried.

I'm not sure if it's a problem with Jooq, or a problem with me.  I'm betting on me.

Exception in thread "main" org.jooq.exception.DataTypeException: Cannot convert from 1 (class java.lang.Integer) to class sourcedb.enums.ObjectType

at org.jooq.tools.Convert$ConvertAll.fail(Convert.java:855)

at org.jooq.tools.Convert$ConvertAll.from(Convert.java:793)

at org.jooq.tools.Convert.convert0(Convert.java:299)

at org.jooq.tools.Convert.convert(Convert.java:291)

at org.jooq.tools.Convert.convert(Convert.java:352)

at org.jooq.impl.DefaultDataType.convert(DefaultDataType.java:608)

at org.jooq.impl.DSL.val(DSL.java:12149)

at org.jooq.impl.Utils.field(Utils.java:740)

at org.jooq.impl.Utils.fields(Utils.java:876)

at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:145)

at org.jooq.impl.BatchSingle.execute(BatchSingle.java:116)

public static void transferRecords() {

    try (Connection connection = sourceConnectionPool.getConnection();) {

        DSLContext create = DSL.using(connection, SQLDialect.MYSQL);

        // Create object table entries

        BatchBindStep objectBatch = create.batch(create.insertInto(targetdb.Tables.OBJTABLE, OBJTABLE.TYPE, OBJTABLE.PARENT).values(null, (ULong)null));

        for (sourcedb.tables.records.ObjectRecord record : sourceDB.fetch(sourcedb.Tables.OBJTABLE, OBJTABLE.TYPE.equal(ObjectType.account))) {
            currentUUID = ULong.valueOf(currentUUID.longValue() + 1);

            uuidMap.put(record.getUid().intValue(), currentUUID.intValue());
            System.out.println("oldUUID:" + record.getUid() + " currentUUID: " + currentUUID.longValue());

            objectBatch.bind(1, targetDB.enums.ObjectType.account);
            objectBatch.bind(2, record.getParent());

        }

        objectBatch.execute();

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

[bsha...@gmx.com]

Attempting to reduce the problem I dropped down to a single database.  I've tried the enum itself and every permutation of name/literal and ordinal.

objectBatch.bind(1, ObjectType.account.getLiteral());

objectBatch.bind(1, ObjectType.account.getName());

objectBatch.bind(1, ObjectType.account.ordinal());

 All end with the same exception generated not upon the bind on execute.  The bind method appears to be asking for a generic object.  The way I would normally do this with MySQL is to bind a string name of the enumeration to the statement parm.

[Lukas Eder]

Hello,

Thanks for your enquiry. Batch execution should work just the same as regular execution. We keep track of all bind variable data types via the original INSERT statement's columns.

The problem is the fact that unlike JDBC each bind method call binds a full record, not a specific bind variable. Your original code should read:

public static void transferRecords() {

    try (Connection connection = sourceConnectionPool.getConnection();) {

        DSLContext create = DSL.using(connection, SQLDialect.MYSQL);

        // Create object table entries

        BatchBindStep objectBatch = create.batch(create.insertInto(targetdb.Tables.OBJTABLE, OBJTABLE.TYPE, OBJTABLE.PARENT).values(null, (ULong)null));

        for (sourcedb.tables.records.ObjectRecord record : sourceDB.fetch(sourcedb.Tables.OBJTABLE, OBJTABLE.TYPE.equal(ObjectType.account))) {
            currentUUID = ULong.valueOf(currentUUID.longValue() + 1);

            uuidMap.put(record.getUid().intValue(), currentUUID.intValue());
            System.out.println("oldUUID:" + record.getUid() + " currentUUID: " + currentUUID.longValue());

            objectBatch.bind(targetDB.enums.ObjectType.account, record.getParent());

        }

        objectBatch.execute();

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

More details about jOOQ's batch feature can be seen here:

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

I guess the manual could be a bit more clear about the 1, 2, 3, 4 being values for the ID column, not bind value indexes :-)

Hope this helps,

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.

[bsha...@gmx.com]

Thank you for the reply.

Hah!  Yes I had assumed from the example that it was generic jdbc positioning based binding.  Tried every relevant method on the classes involved.  Can't say I have any recollection of seeing getParent().

I plan on giving Jooq another try, but refactored to something called QueryDSL to move this project forward.  It appears to be a lot like Jooq, only a bit slower with less features.

[Lukas Eder]

See you back here, soon, then :-)

[Lukas Eder]

On a more serious note (sorry, couldn't resist 5 minutes ago)

I'm sorry to see you go because of this. I do believe that you will find the missing features limiting after a while, but I certainly understand that moving a project forward can be essential.

I'm curious about the "bit slower" part. How does this manifest?

[bsha...@gmx.com]

On Thursday, November 27, 2014 7:31:14 AM UTC-5, Lukas Eder wrote:

On a more serious note (sorry, couldn't resist 5 minutes ago)

I'm sorry to see you go because of this. I do believe that you will find the missing features limiting after a while, but I certainly understand that moving a project forward can be essential.

I'm curious about the "bit slower" part. How does this manifest?

This project was just a database migration, so QueryDsl worked.  Less features true, but the overall interface was simpler as a result.  QueryDSL doesn't really have a manual like Jooq does, but I didn't need one.

I hope you find time to make a pass over the manual by the time I come back.  I really want to use Jooq for crud, but most of the examples look like Java 5-6 era and are in serious need of being brought up to date.  Other than my binding issue, I couldn't find an example that used try with resources in the manual, as everything is missing surrounding boilerplate code.  I also couldn't find an example that leveraged lambda expressions.

Nor could I find out the best practices in using the same schema for two different databases.  It worked out of the box with querydsl, but jooq didn't appear to like it at all.   Unlike QueryDSL, the jooq generated java objects appeared inherently tied to the source database  My workaround was to generate two separate schemas, renaming the mysql enum fields on the target db.  Not pretty, but it did work!

In terms of the 'bit slower part: I haven't captured metrics, but it's just my perceived experience based upon dumping oodles of records through each over many iterations.  The difference is that I'm not using foreign keys to lookup child records in querydsl, but an actual secondary query.  I imagine that might be one of the missing features, as it wasn't at all clear how to do it from the interface.

[Lukas Eder]

Hello,

Thank you very much for your valuable feedback. I will comment to your different points inline

2014-12-02 15:39 GMT+01:00 <bsha...@gmx.com>:

On Thursday, November 27, 2014 7:31:14 AM UTC-5, Lukas Eder wrote:

On a more serious note (sorry, couldn't resist 5 minutes ago)

I'm sorry to see you go because of this. I do believe that you will find the missing features limiting after a while, but I certainly understand that moving a project forward can be essential.

I'm curious about the "bit slower" part. How does this manifest?

This project was just a database migration, so QueryDsl worked.  Less features true, but the overall interface was simpler as a result.  QueryDSL doesn't really have a manual like Jooq does, but I didn't need one.

Fair enough

 

I hope you find time to make a pass over the manual by the time I come back.

We constantly pass over the manual. Anything you find is greatly appreciated. We hardly need to read our own manual anymore, so feedback from users who do read it for the first time is very useful!

 

 I really want to use Jooq for crud, but most of the examples look like Java 5-6 era and are in serious need of being brought up to date.  Other than my binding issue, I couldn't find an example that used try with resources in the manual, as everything is missing surrounding boilerplate code.

Because there isn't any surrounding boilerplate code! :-)

With jOOQ, you don't need any try-with-resources statements. jOOQ manages all JDBC resources for you, internally (except the JDBC Connection, if you want to manage that).

Perhaps this page might help understand what I'm trying to say:

http://www.jooq.org/doc/latest/manual/sql-execution/comparison-with-jdbc/

We have inversed all default behaviour compared to JDBC. We believe that most of the time, you don't want to keep open PreparedStatements or ResultSets. You want to dispose of those things immediately at execution time, keeping reference only to a Query object and the Result data. Of course, if you intend to keep those objects open, you can do so explicitly, but 95% of the time, the boilerplate that is produced because of this is tedious and unnecessary.

Let me know if this makes sense, or if you were looking for something else.

 

I also couldn't find an example that leveraged lambda expressions.

There are a couple of ways to leverage lambdas once you have materialised a jOOQ Result. We've blogged about that a couple of times. Some examples:

- http://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/

- http://blog.jooq.org/2014/07/11/java-8-friday-more-functional-relational-transformation/

Other examples would involve CompletableFutures, if you want asynchronous query execution:

- http://blog.jooq.org/2014/09/23/asynchronous-sql-execution-with-jooq-and-java-8s-completablefuture/

But those usages are merely possible because jOOQ Result or ResultQuery implement standard JDK APIs, such as List (Result) or Iterable (ResultQuery). There is not really any other explicit use for lambdas in the jOOQ API, except perhaps when using RecordMapper:

http://www.jooq.org/doc/latest/manual/sql-execution/fetching/recordmapper/

Is that what you had in mind? Or in what context would you expect us to provide lambda examples in the manual?

Nor could I find out the best practices in using the same schema for two different databases.  It worked out of the box with querydsl, but jooq didn't appear to like it at all.   Unlike QueryDSL, the jooq generated java objects appeared inherently tied to the source database  My workaround was to generate two separate schemas, renaming the mysql enum fields on the target db.  Not pretty, but it did work!

I'm not 100% sure what you mean by this. You have installed two different logical databases in the same schema? The best way to deal with this sort of multi-tenancy via the jOOQ code generator is by using schema mapping:

http://www.jooq.org/doc/latest/manual/code-generation/schema-mapping/

And perhaps includes / excludes patterns. jOOQ is a database-first API, so yes, it's true that generated objects are inherently tied to the source database. We see this as a feature, but perhaps we've missed something - e.g. a specific use-case that we cannot cover yet?

Best Regards,

Lukas 

[bsha...@gmx.com]

Thanks for the responses, they really help a great deal.

We constantly pass over the manual. Anything you find is greatly appreciated. We hardly need to read our own manual anymore, so feedback from users who do read it for the first time is very useful!

Manuals should assume no tribal knowledge.  People with tribal knowledge don't read them!

Because there isn't any surrounding boilerplate code! :-)  With jOOQ, you don't need any try-with-resources statements. jOOQ manages all JDBC resources for you, internally (except the JDBC Connection, if you want to manage that).

My confusion with the try-with-resources partially was that some jooq objects didn't appear to have implemented closeable.  If all I need to do is manage the connection object then that's perfect and makes me want to use jooq even more.  Imho they should shoot the guy who invented finally blocks.  I was doing backflips in and out of cubicles when they came out with try-with-resources.

Is that what you had in mind? Or in what context would you expect us to provide lambda examples in the manual?

In terms of lambdas, a lot of db work obviously involves processing sets of records which they fit well.  I starting to see them employed in framework documentation everywhere, but when I read the jooq manual I just have this sense of reading Java 6 code instead of Java 8 code.  Felt... dusty?

And perhaps includes / excludes patterns. jOOQ is a database-first API, so yes, it's true that generated objects are inherently tied to the source database. We see this as a feature, but perhaps we've missed something - e.g. a specific use-case that we cannot cover yet?

Perhaps I'm missing the 'feature' aspect of it.

A schema is not inherently tied to any physical database but a record mapping with added validation.  Conceptually, I think of them as data structures in languages such as C, RPG IV or COBOL that you could use as a template moving it around in memory.  There is no reason I can see where a person should be forcibly restricted from pointing a valid schema abstraction at multiple connections. I'm sure I'm not the only person in the world doing a data migration.  QueryDSL was able to handle this use case out of the box, without any further XML configuration.  You simply reference the schema object and the connection when creating the query.

[Lukas Eder]

2014-12-02 21:54 GMT+01:00 <bsha...@gmx.com>:

Thanks for the responses, they really help a great deal.

We constantly pass over the manual. Anything you find is greatly appreciated. We hardly need to read our own manual anymore, so feedback from users who do read it for the first time is very useful!

Manuals should assume no tribal knowledge.  People with tribal knowledge don't read them! 

Because there isn't any surrounding boilerplate code! :-)  With jOOQ, you don't need any try-with-resources statements. jOOQ manages all JDBC resources for you, internally (except the JDBC Connection, if you want to manage that).

My confusion with the try-with-resources partially was that some jooq objects didn't appear to have implemented closeable.

We are still supporting Java 6, which is why we don't have access to AutoCloseable. We've contemplated implementing Closeable before, but the Closeable contract allows for throwing IOException, which could lead to very weird corner cases. Besides, we couldn't "upgrade" to AutoCloseable any longer, once we unsupport Java 6.

 

If all I need to do is manage the connection object then that's perfect and makes me want to use jooq even more.

I'm glad to hear that!

Imho they should shoot the guy who invented finally blocks.  I was doing backflips in and out of cubicles when they came out with try-with-resources.

I would have loved to see that! Personally, I wouldn't want to do without finally blocks, but I guess the life of an API implementation developer is different from the API consumer's...

 

Is that what you had in mind? Or in what context would you expect us to provide lambda examples in the manual?

In terms of lambdas, a lot of db work obviously involves processing sets of records which they fit well.  I starting to see them employed in framework documentation everywhere, but when I read the jooq manual I just have this sense of reading Java 6 code instead of Java 8 code.  Felt... dusty?

You should follow our blog, then :-)

If you spot some concrete pages where we could add useful examples, let us know. But beware that most of the manual talks about the DSL and the code generator. There's simply not much use for lambdas there...

 

And perhaps includes / excludes patterns. jOOQ is a database-first API, so yes, it's true that generated objects are inherently tied to the source database. We see this as a feature, but perhaps we've missed something - e.g. a specific use-case that we cannot cover yet?

Perhaps I'm missing the 'feature' aspect of it.

A schema is not inherently tied to any physical database but a record mapping with added validation.  Conceptually, I think of them as data structures in languages such as C, RPG IV or COBOL that you could use as a template moving it around in memory.  There is no reason I can see where a person should be forcibly restricted from pointing a valid schema abstraction at multiple connections. I'm sure I'm not the only person in the world doing a data migration.  QueryDSL was able to handle this use case out of the box, without any further XML configuration.  You simply reference the schema object and the connection when creating the query.

Hmm, this doesn't sound like anything that couldn't be done with jOOQ...

Would runtime schema and table mapping have been of any help, then?

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/runtime-schema-mapping/

In principle, jOOQ indeed doesn't care what physical database it runs queries against, and there are lots of means at different levels to implement things like multi-tenancy, mapping, renaming, or even complex SQL transformation...