[REQUEST FOR FEEDBACK]: What small but incredibly useful utility are you missing the most in jOOQ?

[Lukas Eder]

Dear group,

Part of jOOQ's success is its incredible amount of convenience methods that help reduce the boiler plate code at your side. We do this with massive overloading of API, for instance, when you work with fetch(), you may have noticed how many different types of fetch() there are in jOOQ.

Just now, I have added yet another convenience method. A Converter constructor:

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

It looks like this:

    static <T, U> Converter<T, U> of(

        Class<T> fromType,

        Class<U> toType,

        Function<? super T, ? extends U> from,

        Function<? super U, ? extends T> to

    ) { ... }

And also:

    static <T, U> Converter<T, U> ofNullable(

        Class<T> fromType,

        Class<U> toType,

        Function<? super T, ? extends U> from,

        Function<? super U, ? extends T> to

    ) {

        return of(

            fromType,

            toType,

            t -> t == null ? null : from.apply(t),

            u -> u == null ? null : to.apply(u)

        );

    }

The above allows for creating simple ad-hoc, one-liner converters, such as:

    Converter<String, Integer> converter =

    Converter.ofNullable(String.class, Integer.class, Integer::parseInt, Object::toString);

What's your biggest "itch" in the jOOQ API, which jOOQ could "scratch", or rather, make go away by adding new convenience API?

All ideas welcome!

Lukas

[Samir Faci]

1.  One conversion that would be useful to have is the ability to convert from POJOs to Records and vice versa. 

On occasion I need to roll out my own POJO because our data is represented different at the service layer then how the data 

is actually stored, but I find myself using the autogenerated records and POJOs a good bit.  It would be nice if there was an easy way to going from say:

TableNameRecord  -->  TableName (pojo) and back.  

It varies on my use case but I tend to usually fetchInto(  . class) and most of the the class passed in is either the record or pojo that was generated by jooq.

--

Samir Faci

--
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.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Lukas Eder]

Hi Samir,

2016-07-07 4:50 GMT+02:00 Samir Faci <sa...@esamir.com>:

1.  One conversion that would be useful to have is the ability to convert from POJOs to Records and vice versa. 

On occasion I need to roll out my own POJO because our data is represented different at the service layer then how the data 

is actually stored, but I find myself using the autogenerated records and POJOs a good bit.  It would be nice if there was an easy way to going from say:

TableNameRecord  -->  TableName (pojo) and back.  

Do I get you right, you'd like a hard-coded method in the generated TableNameRecord that does the mapping for the generated POJO? E.g.

class TableNameRecord extends UpdatableRecordImpl {

    ...

    public TableNamePojo intoTableNamePojo() { ... }

    public void fromTableNamePojo(TableNamePojo pojo) { ... }

}

Would this really help? I can definitely see the into() method. The from() method doesn't seem to add that much value over the existing Record.from(Object) method, except, perhaps, some additional type safety?

 

It varies on my use case but I tend to usually fetchInto(  . class) and most of the the class passed in is either the record or pojo that was generated by jooq.

Oh, I see, that's a different story, though. The problem is that fetchInto(Class) is called on a ResultQuery<R>, where R is a generic type. It will be a bit harder to find a solution along the lines of what you're looking for. Perhaps, we could generate a RecordMapper<TableNameRecord, TableNamePojo>, which would add some type safety over passing the class literal (and it could be implemented more optimally). That RecordMapper could then be made available from TableNameRecord:

class TableNameRecord extends UpdatableRecordImpl {

    ...

    public static RecordMapper<TableNameRecord, TableNamePojo> mapper() { ... }

}

This could then be used as such:

dsl.select(...).from(...).fetchInto(TableNameRecord.mapper())

What do you think?

[Leigh Whiting]

My question is similar (and perhaps is covered already) but goes in a slightly different direction.

I really like the JOOQ record mapper implementation for fetching "auto-magically" straight into custom POJOs - we tend to use this a bit when our domain classes don't nicely reflect what can be stored easily in a database.

However, it would be great if the inverse operation was also pluggable: mapping a POJO into a record (via a record mapper or similar interface).  For every instance a record mapper is used in our source we have code (not plugged into JOOQ) that performs the conversion the other way (POJO -> Record via a mapper).  It would be great if JOOQ could support record mappers that knew how to convert both ways.

Cheers,

Leigh

[Lukas Eder]

Hi Leigh,

Thanks for your feedback. I guess you can upvote this feature request, then:

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

:)

Indeed, that should be possible in a (hopefully near) future version. Specifically, the DefaultRecordMapper logic should be made available in reverse form through a new "DefaultRecordUnmapper"

Do note that some logic is already available through Record.from(Object).

Best Regards,

Lukas

--

[mybo...@gmail.com]

JOOQ has been really helpful. Like others have shared,  I think mapping is where I spend some considerable amount of time when using Jooq. I have not really been leveraging JPA column annotation (appears you have or will be deprecating support).  I have had to develop utilities to support mapping with the help of Modelmapper library. For queries with multiple joins, how do you effectively deal with duplicate records  in results (from resulting joins) and have them mapped to collection of different pojos without duplicates with current mapping strategies? With multiple selects, I can make do with the simplistic mappings but that is like going N + 1 queries.  I will like to know how folks are dealing with this without too many code duplication or if there are libraries that can help or examples.

On a side note I find the amount of options (methods available, overloaded etc) when using jooq api to be a distraction. Sometimes get the sense I don't know enough about the tool. All the same Jooq is a great tool and thanks for the effort invested to provide an alternative when the industry is "almost" drown in Hiba/jpa as a requirement for success.

Dayo 

[Lukas Eder]

Thank you very much for your feedback, Dayo

I will comment inline

2016-07-08 18:17 GMT+02:00 <mybo...@gmail.com>:

JOOQ has been really helpful. Like others have shared,  I think mapping is where I spend some considerable amount of time when using Jooq. I have not really been leveraging JPA column annotation (appears you have or will be deprecating support).

The deprecation will only affect the DefaultRecordMapper. The implementation should be moved out of this default mapper and into a separate JPARecordMapper. So, you should be safe to use this feature also in the future.

 

I have had to develop utilities to support mapping with the help of Modelmapper library. For queries with multiple joins, how do you effectively deal with duplicate records  in results (from resulting joins) and have them mapped to collection of different pojos without duplicates with current mapping strategies? With multiple selects, I can make do with the simplistic mappings but that is like going N + 1 queries.  I will like to know how folks are dealing with this without too many code duplication or if there are libraries that can help or examples.

For simple use-cases, the various intoGroups() methods will certainly help. For more complex use-cases, this will be much harder. JPA doesn't solve this sufficiently well either. It just hides the complexity from the user but adds performance overhead just the same.

 

On a side note I find the amount of options (methods available, overloaded etc) when using jooq api to be a distraction. Sometimes get the sense I don't know enough about the tool.

That's interesting. Do you have a particular example in mind? E.g. the various fetch methods?

[Mark Derricutt]

On Thu, Jul 7, 2016 at 12:16 AM, Lukas Eder <lukas...@gmail.com> wrote:

What's your biggest "itch" in the jOOQ API, which jOOQ could "scratch", or rather, make go away by adding new convenience API?

One that I hit yesterday, using fetchOne() which returns a null if absent, error if more than 1.  What I would ideally have had was "fetchUnique" which would error on anything BUT 1 result, or a "fetchOptional".

--
"Great artists are extremely selfish and arrogant things" — Steven Wilson, Porcupine Tree

[Lukas Eder]

Thanks a lot for your feedback, Mark!

2016-07-12 22:41 GMT+02:00 Mark Derricutt <ma...@talios.com>:

On Thu, Jul 7, 2016 at 12:16 AM, Lukas Eder <lukas...@gmail.com> wrote:

What's your biggest "itch" in the jOOQ API, which jOOQ could "scratch", or rather, make go away by adding new convenience API?

One that I hit yesterday, using fetchOne() which returns a null if absent, error if more than 1.  What I would ideally have had was "fetchUnique" which would error on anything BUT 1 result, or a "fetchOptional".

That is interesting indeed. After all these years, I suspect I've gotten used to the inconsistency created by the fetchOne() API contract... Note that jOOQ 3.7 introduced ResultQuery.fetchOptional, which returns an Optional<R>:

http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#fetchOptional--

(and still throws an exception if there are too many results)

In a way, fetchOne() *is* fetchUnique(), except that it will throw a NPE later on rather than a NoRecordFoundException :) On a more serious note, indeed, that would certainly add value as it clearly communicates the intention of a query to return exactly one record (e.g. when implementing optimistic locking, etc.) I've registered a feature request for this:

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

Thanks a lot for providing feedback!

"Great artists are extremely selfish and arrogant things" — Steven Wilson, Porcupine Tree

Excellent, I'll have to remember this :)

[Lukas Eder]

Hi Samir,

I have registered a feature request to generate a mapper() onto generated TableRecords:

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

That's a low hanging fruit. Additional features are certainly possible, but need more discussion, I think.

Best Regards,

Lukas

[Samir Faci]

Hi Lukas, 

Sorry for the long delay in response.  We've used the JPA with default mapper to load data into both Records and POJOs and having the ability to go back and forth or even in one direction (Records -> Pojos) would be great.

Thanks for opening up the ticket.

Our usual cases tends to be one of 3 scenarios.

LoadInto:

  - Generated Records.class

  - Generated POJO .class

  - HandRolled POJO due to data being returned and modeled differently then the DB.

I would assume that 1 and 2 is somewhat doable. 3rd Item is probably not feasible, but if there was some mechanism that would allows us to go from any of these entities back and forth that'd be great.

Even the HandRolled POJOs.  end of the day we end up with 

Object.fieldName  maps to GeneratedPojo.fieldName which corresponds to GeneratedRecord.fieldName.

having the ability to say take a List of hand rolled POJO and extrapolate a List of records we can INSERT/UPDATE depending on use case would be very useful to us.

that being said, this is "our" needs, it doesn't necessarily make sense for Jooq in general.  Just passing on our use case in case it resonates with others and feedback was requested. :)

--

Samir 

--
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.

[mi...@providencehr.com]

Hi Lukas,

A generated method to provide a type safe insert statement including all the table columns would be extremely useful for us. We typically use records to do inserts but for a few high volume processes we are batching raw inserts. We found this significantly faster than instantiating records and using dsl.batchInsert(). The issue with this is that as our schema evolves it's easy to leave columns out of the insert. So for instance if I have a table customer, it would be great to be able to do something like 

Tables.CUSTOMER.insert(name, street1, ...)

[luis....@buntplanet.com]

Hi lukas!

For really complex queries is really useful query.addJoin(BOOK, JoinType.LEFT_OUTER_JOIN, BOOK.AUTHOR_ID.equal(AUTHOR.ID));

I would love a way to add a list of joins... something like query.addJoins(/* List<Tuple3<Table,Field,Condition>> */)

is that possible? thanks!

[Lukas Eder]

OK, thanks a lot for the clarifications, Samir. I'll see what I can do with this. Certainly, that RecordUnmapper API which is on the roadmap, will help implementing reusable logic for hand rolled POJO to Record transformations...

Cheers,

Lukas

2016-07-14 19:47 GMT+02:00 Samir Faci <sa...@esamir.com>:

that being said, this is "our" needs, it doesn't necessarily make sense for Jooq in general.  Just passing on our use case in case it resonates with others and feedback was requested. :)

I think your needs aren't very unusual, from what I've seen on this group, stack overflow, and the user manual comments

[Lukas Eder]

Hi Mike,

Thanks a lot for your feedback. From what I understand, your insert() method would produce an org.jooq.Insert object, that can be executed in the context of a Configuration, such as:

DSL.using(configuration).execute(CUSTOMER.insert(name, street1, ...));

In a way, this already exists via the following API:

DSL.using(configuration).executeInsert(new CustomerRecord(name, street1, ...));

Does that respond to your API needs? Now, the question is, how to not execute the statement, but collect it for later batching... An alternative API that could be used for this would be:

DSL.using(configuration).insertInto(CUSTOMER).set(new CustomerRecord(name, street1, ...));

The set() call isn't type safe, but that might be OK. One more option is to use the existing Loader API

DSL.using(configuration)
   .loadInto(CUSTOMER)
   .loadRecords(new CustomerRecord(name, street1, ...))
   .fields(CUSTOMER.fields())
   .execute();

And finally :)

Result<CustomerRecord> result = DSL.using(configuration).newResult(CUSTOMER);
result.add(new CustomerRecord(name, street1, ...));
String inserts = result.formatInsert();

These are just some ideas of what's possible today. I'm very happy to discuss further steps.

Lukas

[Lukas Eder]

Hi Luis,

Hmm, interesting, thanks a lot for your suggestion. Hah, this is a very low level query builder API method. I somehow wish it didn't exist (in the public API) :)

But I certainly see your use-case. Let's discuss!

1. What are the pain points when constructing complex joins through the "official" DSL API, i.e. by doing something like:

Table<?> join = BASE_TABLE;

if (something)

    join = join.join(OTHER_TABLE).on(condition);

if (somethingElse)

    join = join...;

2. What's the use-case? Perhaps I'll better understand where this could go, if I know what exactly you understand by "complex query"

Cheers,
Lukas

[Mike Foody]

Hi Lukas, 

DSL.using(configuration).execute(CUSTOMER.insert(name, street1, ...));

That's exactly what we're looking for. In particular we want to batch the org.jooq.Insert instances. I'll take a look at your suggestions. What we found was the dirty checking that occurs in the records when using dsl.batchInsert(records) significantly slowed down the process.

-

Mike Foody

Providence HR
http://www.providencehr.com

(832) 714 1652

mi...@providencehr.com

--
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/7hfDlmEwfQI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Oh, I see, that's a good hint. Indeed, all existing record.store() or record.insert() based approaches don't take advantage of your knowledge about wanting to insert *all* columns.

I think the Loader API solution will be the best one right now, as it also allows you to fine tune bulk, batch, and commit sizes (each of which highly depend on the database vendor, and your actual data).

But there's certainly food for thought here for two improvements:

1. Look into what's so slow about batchInsert() and how that can be improved

2. Perhaps find a new API (or an adaptation of existing API) that will do exactly what you have in mind.

[Luis Artola]

hi again!

We have a javascript query builder, where user can construct queries to our fact/dimensional model. More than 50 possible data filters are sent to our java backend. So we have this kind of code (of course, it´s a simplification :-) ):

FilterEngine engine = new FilterEngine(request);

DSL.select(engine.getSelectFields())/* it's possible! :-) */
        .from(engine.getFactTable())/* it's possible! :-) */
        .joins(engine.getDimensionJoins())/* NOT possible :-( */
        .where(engine.getWhereConditions())/* it's possible! :-) */
        .groupBy(engine.getGroupByFields())/* it's possible! :-) */
        .orderBy(engine.getSortFields());/* it's possible! :-) */

Usually not all the query parts are mobile parts, but normally dimension tables are joined if a filter is present in the incoming json... 

Thanks!!!

--
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/7hfDlmEwfQI/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.

--

Luis Artola

Zuatzu Kalea 9. Edificio Europa. Planta 3. 20018 Donostia T + 34 943 223 031

[Lukas Eder]

Hi Luis,

I see, thanks for the additional example. Hmm, this is an excellent example of what I've often noticed at client's shops. There's a misunderstanding about what the JOIN clause really means in a SELECT statement. It is, in fact, not part of the SELECT statement, so ideally, your pseudo query should look like this:

DSL.select(engine.getSelectFields())/* it's possible! :-) */

   .from(engine.getFactTableWithJoinedDimensions())/* it's possible! :-) */
// .joins(engine.getDimensionJoins())

   .where(engine.getWhereConditions())/* it's possible! :-) */
   .groupBy(engine.getGroupByFields())/* it's possible! :-) */
   .orderBy(engine.getSortFields());/* it's possible! :-) */

The fact that you can write .join() on the SelectJoinStep is mere convenience for the trivial query case. But in fact, what you're really doing is, you're creating a join graph and pass that entire graph to the FROM clause. So, why not let your engine.getFactTable() method return this instead:

Table<?> join = FACT_TABLE;

if (something)

    join = join.join(OTHER_TABLE).on(condition);

if (somethingElse)

    join = join...;

After all, the ON clause really belongs to each individual JOIN, and if you need to nest joins with parentheses, there wouldn't be any other way than to explicitly build this graph.

I'm not rejecting your idea by the way, just helping you find a workaround for now. I like the idea of being able to construct a join graph from a collection of tables (this was already discussed in this user group earlier on). But I still think this needs more thinking. It won't be a .joins() method on the select statement. It will be a table() constructor that automatically assembles the join graph given some arguments.

Thanks again for your input!

Lukas

[Luis Artola]

understood... that could be great! :-)

[dcomi...@gmail.com]

Hello Lukas!

First of all, congratulations (and thanks!) for your great work on JOOQ.

One thing that would be a killer feature for many of the projects I have been working on would be the ability to automatically retrieve/fetch and nest/embed foreign entities (which have 1-N, N-1 or N-N relationships) in Records and POJOs, similarly to what PostgREST does with Postgres (http://postgrest.com/api/reading/#embedding-foreign-entities) when it returns JSON data for queries.

E.g.: table "sessions" references table "speakers", and a SessionsRecord for an existing Sessions row would be able to fetch SpeakersRecords for every referenced Speakers row.  Better yet, it could return a whole POJO graph after a single method/fetch invocation.

In order to see a working and somewhat equivalent example, just access the following URL in your web browser


https://postgrest.herokuapp.com/sessions


and then compare the returned JSON to the one from this other URL


https://postgrest.herokuapp.com/sessions?select=*,speaker{*}


In fact, we are evaluating the use of PostgREST as a substitute for ORMs and even JOOQ in some use cases mainly because of this feature.

Thanks for your support!

Danilo

[Lukas Eder]

Hi Danilo,

Sorry for the delay - thank you very much for your feedback and for your nice words!

Indeed, automation of fetching nested collection is something that's been on my mind for a long time. The SQL standard approach would be to make use of the MULTISET() operator - which is only really supported in Oracle. I haven't proven it yet, but I do hope that a variety of basic usage of MULTISET() can be emulated by running several queries. The relevant feature request is here:

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

In PostgreSQL, emulation could be done via arrays of rows, i.e. by using something like array_agg(row(a, b, c))

Many other APIs (including JPA) have implemented tricks to enable foreign key / primary key navigation in a somewhat convenient way. I think they're not getting the big picture right. If jOOQ ever implements this, it'll be the SQL way, i.e. allowing users to create ad-hoc nested collections, not just nested collections for pre-defined relationships. (The latter of course will also be possible as it is a special case of the former).

Thanks again for your feedback. PostgREST seems very nice. Quite possiby, it will be sufficient for your immediate use-cases where jOOQ is more overhead to fetch entities and nested entities. I will definitely think about this again to make jOOQ more competitive with tools like PostgREST in the future.

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+unsubscribe@googlegroups.com.

[Max Kremer]

I just have to say that JOOQ is AWESOME! We've been using it for about 2.5 years now and it's really a joy to work with. Its a big library and has its learning curve but it crushes every ORM (or any other lib that wraps JDBC) out there. Once you get the "JOOQ Way" of doing things it makes for robust, easy to maintain and readable code. Thanks Lukas for all your hard work and support.

-Max

On Wednesday, July 6, 2016 at 8:16:44 AM UTC-4, Lukas Eder wrote:

[Lukas Eder]

Hey Max,

Thanks a lot for your very nice words. This is greatly appreciated!

I always see room for improvement, even in a compliment, though :) May I ask, what's the biggest part of jOOQ having its learning curve? Is there anything that you can still recall now, after 2.5 years, that you think could be improved for new users? Can the "JOOQ Way" of doing things be conveyed more easily?

Cheers,
Lukas

--

[Max Kremer]

Hey Lukas,

  Sorry for the super late reply. If I had to pick one thing to improve it would definitely be the documentation. More comprehensive example code along with detailed explanations. I know it's tuff to cover everything, but a broader set of example use-cases would've helped a lot in the beginning. 

Cheers,
Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Max,

Thanks a lot for this input. Have you seen the examples on GitHub?

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples

In any case, I do agree that they could use more explanations, perhaps in the form of comments... And I also think that they're not linked well enough from the manual.

What do you mean by "example use-cases"? You mean concrete examples involving business use-cases like ETL, reporting, complex predicate logic?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Max Kremer]

Hi Lukas,

 I haven't seen those examples. Thanks for pointing them out. I went through a couple of them and although the readme files are quite light :) it seems those examples focus on specific technology examples.

When I mentioned example use-cases I was referring more to complex predicate logic and query "building" rather than business use-cases like ETL and reporting. Stuff like using multiple CTEs or inner queries, dynamically generating join conditions and predicates, etc...

Because JOOQ provides a rich object model it's possible to delegate building various parts of the SQL to the appropriate business components. So you can generate JOINS and WHERE clauses in completely different parts of the code but it all comes together when you execute the SQL. I don't know if I'm doing a good job explaining it - maybe I can provide some examples if that helps.

[Lukas Eder]

Hi Max,

You're right. The readme files deserve some additional work, just like the code itself, where a few comments would certainly help.

Hmm, there's a chapter about dynamic SQL here:

http://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql

But I get what you're really saying here. All of that info is there somewhere, but as a new user, it's hard to get to the info. It might be possible to google it if the new user was trying around 20 different keywords (dynamic SQL, query building, predicate logic, etc. etc.) but it would be much easier if there was a "getting started" page (or section) that briefly illustrates the use-case with 1 paragraph, and then provides links to more details.

I've created this issue to fix this:

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

Hope that responds to your suggestion? :)

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Anthony Calce]

Hey Lukas,

Is it possible to use "NOW()" in an insert function.  For example, the insert is to look like this

INSERT INTO db.customer(

  account_number, 

  cancel_date

)

VALUES (

  1, 

  NOW()

)

The Jooq Query will look like 

wrapper.getCreate()

.insertInto(

    CUSTOMER,

    CUSTOMER.ACCOUNT_NUMBER, // int

    CUSTOMER.CANCEL_DATE)    // joda DateTime

.values(

    1,

    SOMETHING); // What should this be

)

.execute();

I use a type converter from Timestamp to DateTime. The problem is that the insert is expecting a DateTime object. I could use new DateTime(), but I want to use the MYSQL server time for this.  The following don't work

• DSL.val("NOW()")  -> returns a string, type mismatch
  
• DSL.field("NOW()", DateTime.class) -> returns a Field<DateTime> which isn't compatible
  

Any ideas?

[Anthony Calce]

I've found a solution, but it's not very pretty

        Collection<Object> x = new ArrayList<Object>();

        x.add(1);

        x.add(DSL.field("NOW()", Timestamp.class));

        

        try (KrJooqWrapper wrapper = new KrJooqWrapper(DbType.KRDB, true)){

            wrapper.getCreate()

            .insertInto(

                CUSTOMER,

                CUSTOMER.ACCOUNT_NUMBER,

                CUSTOMER.CANCEL_DATE)

            .values(

                x

            )

            .execute();

        }

Notice how I had to use Timestamp.  If I use DateTime in that field type, I get the following error "Type class org.joda.time.DateTime is not supported in dialect DEFAULT".  This solution isn't too appealing since I lose type information for the values.  Any alternatives?

[Samir Faci]

You could also use 

DSL.currentDate()

DSL.currentTime()

DSL.currentTimestamp();

Based on your code snippet I assume you probably want DSL.currentTimestamp().

Probably a bit prettier then relying on field("...") 

--

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

[Mike Foody]

You might try creating your own function.

public Field<DateTime> now() {

  return DSL.function("now", DateTime.class);

}

-

Mike Foody

Providence HR
http://www.providencehr.com

(832) 714 1652

mi...@providencehr.com

--
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/7hfDlmEwfQI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

Hey Anthony,

2016-12-07 23:46 GMT+02:00 Anthony Calce <anthon...@gmail.com>:

• DSL.val("NOW()")  -> returns a string, type mismatch
  

That wouldn't work, because DSL.val() creates a bind variable, not an expression. In this case, the value would be the string 'NOW()', instead of the function call NOW().

• DSL.field("NOW()", DateTime.class) -> returns a Field<DateTime> which isn't compatible
  

That's close, but you cannot do it this way, because jOOQ doesn't know what Joda's DateTime means. (It would work with JSR 310 LocalDateTime). You have to provide jOOQ with the actual DataType that contains your converter. There are several ways to do it:

1) Create the DataType afresh using SQLDataType.TIMESTAMP.asConvertedDataType(YOUR_DATE_TIME_CONVERTER)

2) Recycle the existing DataType on the inserted column: CUSTOMER.CANCEL_DATE.getDataType()

And then:

DSL.field("NOW()", theDateTimeDataType);

Your workaround works, because of some internal type coercion in the INSERT statement, when you bypass type safety.

Samir's suggestion (DSL.currentTimestamp()) will also work (if you bypass type safety with a Collection) because of that same type coercion, but it will return the CURRENT_TIMESTAMP() function, which is very subtly different from NOW() in MySQL.

Hope this helps,

Lukas

[Max Kremer]

Hi Lukas,

  I've taken some time to think of a cool feature that would useful. It would be really handy to if column annotation was extensible enough to take valid select clause expression as input and make it available to a pojo getter. Best illustrated by example:

@Column(expression =  "replace( '_', '-', col_bar)  ||  'replaced!'  "  name = "FOO"  )

public fooSetter( String foo) { ...}

[Lukas Eder]

Hi Max,

Thank you very much for your suggestion. Note that the @Column annotation jOOQ currently "supports" is JPA's standard annotation for interoperability reasons, so jOOQ cannot change its behaviour.

Your idea sounds similar to Hibernate's @Formula. I was personally never convinced that putting business / query logic into annotations is a good idea, but I'm very happy to discuss your feature request. So, you're selecting a FOO column in your jOOQ/SQL statement, and then when you retrieve the value of the FOO column, you want to replace it with an expression that can reference other columns from the same POJO.

My question to you: Why not just select that expression in the first place, and name it FOO in SQL?

SELECT replace('_', '-', col_bar) || 'replaced!' AS "FOO"

FROM my_table

Or in jOOQ:

ctx.select(replace("_", "-", MY_TABLE.COL_BAR).concat("replaced!").as("FOO"))
   .from(MY_TABLE)
   .fetchInto(MyPojo.class);

Alternatively, don't do it in SQL/jOOQ, but in Java:

ctx.select()
   .from(MY_TABLE)
   .fetch()
   .stream()
   .map(r -> r.into(MyPojo.class).withFoo(p.getColBar().replace("_", "-") + "replaced!"))
   .collect(toList());

Or even, write a MyPojo class that just implements an entirely different getter for FOO, that does the replacement explicitly. That would be a classic vanilla Java solution and would guarantee this behaviour even outside of jOOQ... See, what would happen in your case, if you manually set COL_BAR on your POJO. Who would update the value of your getFoo getter, if there was some annotation-based rule?

Happy to hear your thoughts...

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Thomas GILLET]

Hi Danilo,

I'm currently trying to do the same thing than you, that is, if I understood it right, define a whole entity graph, and fetch it in one call to make a JSON payload.

What I've done for now is:
- extend the JavaGenerator to generate properties (i.e field/getter/setter) in generated records (or POJOs) based on foreign keys
- write code to fetch records from a table and its "children" tables, and then associate children records to their parent (that is create a graph) using generated setters
- write code to insert/update/merge/delete a graph of record objects, copying foreign key values from parent to children when needed

For fetching it's a lot less than what a MULTISET can offer I guess, but the idea was to have write capabilities too (so you can insert/update a graph right from deserialized JSON).
And for now I only handles one-to-one and one-to-many relationships.

Posting it in case it can help you to keep using jOOQ, until Lukas provide us with a great beautiful MULTISET API and emulation ;)
Open to discussion if interested in more details.

Thomas

[Lukas Eder]

Hi Thomas,

Thanks a lot for your comments. I'm very curious about some examples of what you have done:

2017-02-27 13:17 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

What I've done for now is:
- extend the JavaGenerator to generate properties (i.e field/getter/setter) in generated records (or POJOs) based on foreign keys
- write code to fetch records from a table and its "children" tables, and then associate children records to their parent (that is create a graph) using generated setters
- write code to insert/update/merge/delete a graph of record objects, copying foreign key values from parent to children when needed

Perhaps there's already a low-hanging feature request that could be extracted from the above and moved into core jOOQ. 

 

For fetching it's a lot less than what a MULTISET can offer I guess

You bet. Check this out:

https://twitter.com/lukaseder/status/832939068933681152

Getting all actors, and the categories they played in, as a nested collection, and the films per category they played in as a doubly nested collection. Beautiful! :)

 

but the idea was to have write capabilities too (so you can insert/update a graph right from deserialized JSON).
And for now I only handles one-to-one and one-to-many relationships.

Just to get the complete picture: Materialising a 1:1 entity graph that is supposed to be written back to the database later on is exactly the main value proposition of JPA. What currently keeps you from using JPA?

Cheers,
Lukas

[Thomas GILLET]

Hi Lukas,

Just to get the complete picture [...] What currently keeps you from using JPA?

A long time ago, I used to use JPA to do very basic CRUD stuffs, and it was working fine.
But I began to issue less and less insert/updates, and more and more complex selects, and to be very frustrated with the "code-in-a-string" JPQL approach, the verbosity of CriteriaBuilder, the lack of type-safety, and the heaviness of JPA in general.
Then came jOOQ, and it was good, and then came Java 8 streams and collectors, and there was nothing else left to say.
Until I needed a dumb CRUD resource on objects spanning several tables, and the circle was complete.

Hence the idea to be able to occasionally CRUD simple object graphs with jOOQ - the keywords being "occasionally" and "simple".
But I must confess I had the nasty feeling to re-create a JPA engine when writing this code, so I may well be wrong in refusing to use JPA again.

Perhaps there's already a low-hanging feature request that could be extracted from the above and moved into core jOOQ.

Well, I'll say the easiest part is the code generation, but not sure it's very useful by itself...
Also I'm heavily relying on Key objects, and it's bothering me that they don't have any generic to represent their type. But I posted another topic about that.

You bet. Check this out:

Ok, you won. I suddenly feel so pointless with my tiny helper...

I'm very curious about some examples of what you have done

For the purpose of demonstration, here is a simple graph:

                ------------
               |    SIMU    |
               |------------|
               |PK: SIMU_ID |
                ------------
                     1
          ___________|___________
         /                       \
        /                         \
       1                           *
 -------------              ---------------------------
|  SIMU_COST  |            |      SIMU_OPERATION       |
|-------------|            |---------------------------|
| PK: SIMU_ID |            | PK: SIMU_ID, OPERATION_ID |
| FK: SIMU_ID |            | FK: SIMU_ID               |
 -------------              ---------------------------

Where it is assumed that the SimuRecord class has two additional properties:

private SimuCostRecord cost;
private List<SimuOperationRecord> operation;

// and related accessors (getter/setter)

These properties are generated with a special JavaGenerator in this example, but they could as well be written manually by simply extending the SimuRecord class.

Tree creation

The base of my object tree is the TreeNode class:

// KR: type of the PK of the parent (or root) table
// PR: type of the parent table
public abstract class TreeNode<KR extends Record, PR extends TableRecord<PR>> {...}

I started with a simple tree builder to construct trees manually at runtime:

// SimpleNode assumes a Record1<K> primary key
TreeNode<Record1<Integer>, SimuRecord> NODE = SimpleNode
    // parent table and its primary key
    .one(SIMU, SIMU.SIMU_ID)
    // one-to-one child with foreign key and accessors for a SimuCostRecord property
    .toOne(SIMU_COST, SIMU_COST.SIMU_ID, SimuRecord::getCost, SimuRecord::setCost)
    // one-to-many child with foreign key and accessors for a List<SimuOperationRecord> property
    .toMany(SIMU_OPERATION, SIMU_OPERATION.SIMU_ID, SimuRecord::getOperation, SimuRecord::setOperation);

No need for actual foreign or unique keys here so it can be used with any schema.

Then comes the interesting part, allowing user to write as little code as possible:

// Associations are retrieved from foreign and unique keys reported by jOOQ,
// and accessors are found by reflection.
TreeNode<?, SimuRecord> NODE = new ReflectNode<SimuRecord>(SIMU);

ReflectNode is very simple: it deduces relationships from the foreign keys referencing the parent table primary key, and looks for unique constraints to determine their cardinality:

// Relationships targeting the Table "parentTable"
Collection<ForeignKey<?,?>> keys = parentTable
    .getPrimaryKey()
    .getReferences();

// Cardinality of relationship defined by ForeignKey "fk"
boolean unique = fk
    .getTable()
    .getKeys()
    .stream()
    .anyMatch(uk -> fk.getFields().containsAll(uk.getFields()));

This code is used in both code generation (to generate properties) and at runtime to create the tree.
At runtime, properties are retrieved by reflecting on generated record classes.

Usage

And then, after writing the appropriate code to fetch/merge/delete a tree (which, with all the assumptions made on PKs and FKs, is quite simple), here is how a simple CRUD resource can look like:

private final static TreeNode<?, SimuRecord> NODE;    // use one of the above method to create the tree

// assumes a DSLContext field "dsl" is available

@GET
public Collection<SimuRecord> query() {
    return NODE.fetch(dsl);
}

@POST
public SimuRecord merge(SimuRecord parent) {
    NODE.merge(dsl, parent);
    return NODE.fetchOne(dsl, condition(intoPrimaryKey(parent)));
}

@DELETE
@Path("{id}")
public void delete(@PathParam("id") Integer id) {
    NODE.delete(dsl, DSL.row(id));
}

Conclusion

First remark: the ReflectNode doesn't have a known type for the parent primary key (it extends TreeNode<?, Record>). It makes the NODE.delete() method typeless and I'm not happy with that. This must be improved. But I'm still not sure about the right way to convey typed keys (posted another topic about that).

Then if I'm quite happy with the CUD part of the CRUD, the R part may be too simplistic, I'll probably need filters and joins in my select one day.
So I'd like to find a way to mix this tree approach with a custom select query. Maybe Record with additional fields (Field<Record> or Field<List<Record>>) can replace concrete TableRecord with additional properties. Saw some feature requests about this kind of field I'll need to check.
But then again, let's try not to write yet another JPA engine.

I've not included the actual fetch/merge/delete code nor the generator code, because I didn't manage to extract meaningful snippets. And this post is already long enough anyway, seems I've been carried away by enthusiasm ;)
But I may share once properly packaged in a more standalone lib, if you're interested.

Cheers,
Thomas

[Lukas Eder]

Hi Thomas,

Thank you very much for your detailed elaborations, greatly appreciated!

2017-02-28 16:51 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

Hi Lukas,

Just to get the complete picture [...] What currently keeps you from using JPA?

A long time ago, I used to use JPA to do very basic CRUD stuffs, and it was working fine.
But I began to issue less and less insert/updates, and more and more complex selects, and to be very frustrated with the "code-in-a-string" JPQL approach, the verbosity of CriteriaBuilder, the lack of type-safety, and the heaviness of JPA in general.
Then came jOOQ, and it was good, and then came Java 8 streams and collectors, and there was nothing else left to say. 

Until I needed a dumb CRUD resource on objects spanning several tables, and the circle was complete.

Indeed - the current SQL centric approach works best as long as there is hardly any writing back of entity graphs to the database.

 

Hence the idea to be able to occasionally CRUD simple object graphs with jOOQ - the keywords being "occasionally" and "simple".
But I must confess I had the nasty feeling to re-create a JPA engine when writing this code, so I may well be wrong in refusing to use JPA again.

That describes it, and it's also the reason why I always tried to refuse implementing such features in jOOQ, because users tend to say: "Just support to-one relationships". "Just support single nested collections". And before we notice it, jOOQ will pass the entire JPA TCK and more :)

My usual advice here is to do what also some of the JPA advocates advise to do: Mix both worlds. Write the query with jOOQ, but execute and map it with JPA:

https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities

It's not perfect either, sometimes a JPA JOIN FETCH might be a bit more optimal (their lame workaround for MULTISET support), but it might also be good enough.

Also I'm heavily relying on Key objects, and it's bothering me that they don't have any generic to represent their type. But I posted another topic about that.

Yes, I've seen that. Will respond there.

 

You bet. Check this out:

Ok, you won. I suddenly feel so pointless with my tiny helper...

Don't feel pointless :) The more people try solving this problem, the more probable it is that someone will find the breakthrough eventually. The problem is to finally find a solution that breaks the impedance mismatch. Because there is none. In theory, and in my opinion, the perceived impedance mismatch is only a SQL language implementation detail, or missing feature:

https://blog.jooq.org/2015/08/26/there-is-no-such-thing-as-object-relational-impedance-mismatch/

The "only" problem is that few databases really support nested collections in a way that:

1. It is easy to write them (e.g. MULTISET)

2. It is easy to serialise them (e.g. XML)

3. It is fast (only XML in SQL Server. XML in Oracle or PostgreSQL, and MULTISET in Oracle is much slower)

So, as long as the above isn't solved by the databases, we client application writers will continue searching for suitable workarounds.

I'm very curious about some examples of what you have done

For the purpose of demonstration, here is a simple graph:

Oh oh... That's a recursive tree structure. In SQL, it could only be reasonably materialised with a "higher order MULTISET nested collection" (how awesome would that be!), or you could work around things by tricking and using recursive SQL, and then materialising the tree only in the client.

I actually don't think that anyone has really found a thorough and satisfactory solution to this problem in any ORM, neither SQL based like jOOQ, nor entity graph based like JPA.

But your approach does look very interesting!

Be careful. That might kill your performance. These constraints are very useful when querying, especially when you query the entire tree.

Notice that some databases allow you to use DEFERRED mode with foreign key constraints, meaning that the constraint is only enforced upon commit, not upon INSERT / UPDATE. E.g. PostgreSQL:

https://www.postgresql.org/docs/current/static/sql-set-constraints.html

You should definitely support materialising your tree from a flattened result set, which you produce using recursive CTE.

 

Maybe Record with additional fields (Field<Record> or Field<List<Record>>) can replace concrete TableRecord with additional properties. Saw some feature requests about this kind of field I'll need to check.
But then again, let's try not to write yet another JPA engine.

I've not included the actual fetch/merge/delete code nor the generator code, because I didn't manage to extract meaningful snippets. And this post is already long enough anyway, seems I've been carried away by enthusiasm ;)

It happens. :) And it's very interesting, thank you for sharing! I'd love to solve this problem, if it can be solved in a SQL way. I mean, the problem with doing these things with entity graph approaches will always be the same: Terribly slow execution because such a solution will always traverse the graph and operate on individual nodes rather than bulk/batch stream it in one go.

My thinking is (for insert):

- Insert the entire tree flattened in a bulk INSERT .. RETURNING

- Retreive all the generated IDs and associate them with the individual tree nodes in the client model

- Update the parent references in a bulk UPDATE or MERGE by joining an in-memory table containing the relationships to the actual table

That should be rather "easy."

Deletion is also rather easy with recursive SQL.

Merging might be harder.

 

But I may share once properly packaged in a more standalone lib, if you're interested.

Absolutely interested!
Cheers,
Lukas

[Lukas Eder]

Btw: This was published just now:

https://arnaudroger.github.io/blog/2017/03/02/jooq-one-to-many-without-dto.html

Here's the author:

https://twitter.com/arnaudroger

[mi...@providencehr.com]

We've solved writing back entity graphs a bit differently. The application has a requirement for business process purposes as well as compliance reasons to maintain a complete history of all changes. We've chosen to support this using a bi-temporal data model. Almost every table in the application either has a valid range and transaction time range or is immutable and belongs to a table that has a valid and transaction time. Because of this when an entity graph hits a repository, it's trivial to identify what needs to be done.

• Insert entities with a current transaction time or which belong to an entity with a curent transaction time.
• Update the transaction end time (effectively the transaction end time is the only piece of mutable data) of any entities with transaction end time equal to the current transaction time.
• Deletes are not allowed.

We use the Scala collection operations to identify these scenarios and map them into insert or update statements that are executed in batch. Also, since the transaction time has additional meaning we have database constraints to ensure we're doing this correctly. At least for us this has proven to be a very simple, explicit way to handle entity graph updates. Very interested in any feedback or similar solutions people have come up with.

Hi Thomas,

private final static TreeNode<?, SimuRecord> NODE<span style="color:rgb(102,102,0)" class="gmail-m_8753945

[Lukas Eder]

That sounds very interesting. Indeed, if you remove UD from CRUD, things do get much simpler. Would be very curious to see that in action!

--

[Mike Foody]

Totally. I didn't intend to be snarky. The beauty of the JOOQ api is how all of these subtle niceties come together to create something wonderfully simple.

-

Mike Foody

Providence HR
http://www.providencehr.com

(832) 714 1652

mi...@providencehr.com

--
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/7hfDlmEwfQI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

Snarky? Why? Did you mean to reply to the other thread? :)

[Thomas GILLET]

Hi Lukas,

Thank you very much for your detailed elaborations, greatly appreciated!

Well, when I finally find someone interested in what I have to say, I don’t let him go that easily ;) 

My usual advice here is to do what also some of the JPA advocates advise to do: Mix both worlds. Write the query with jOOQ, but execute and map it with JPA:
https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities

I had forgotten this “native query” stuff. I can indeed use JPA with SQL, so with jOOQ!

Glad you told me. I was starting to wonder how to use jOOQ to generate JPQL queries...

This was published just now:
https://arnaudroger.github.io/blog/2017/03/02/jooq-one-to-many-without-dto.html

Hey that’s interesting! My fk-based property generation on records would fit nicely with this 1-N mapping feature!

In theory, and in my opinion, the perceived impedance mismatch is only a SQL language implementation detail, or missing feature:
https://blog.jooq.org/2015/08/26/there-is-no-such-thing-as-object-relational-impedance-mismatch/

Interesting thoughts… This will make me think for a while…

Oh oh... That's a recursive tree structure. […] you could work around things by tricking and using recursive SQL, and then materialising the tree only in the client.

Errr… what? That’s just a very simple tree… Three tables, two relations (one 1-1 and one 1-N). In what way is it recursive?
I also don’t get the recursive SQL… I know how it works for a hierarchy of elements of the same type (i.e inside one table), but with 3 different tables, without any self-reference… Am I missing something?

Or maybe you misunderstood my schematic? That would explain your enthusiasm for this ;) because modelling an actual recursive tree, that would be more of a challenge…

No need for actual foreign or unique keys here so it can be used with any schema.

Be careful. That might kill your performance. These constraints are very useful when querying, especially when you query the entire tree.

Agreed, and I don’t intend to do without keys. I just prefer to start with the simplest and most generic possible implementation, to see what’s what. Also it provides a way out in case constraints are not generated by jooq. For example I’m using, among others, a strange IBM database which seems not to have any concept of keys nor indices.

For actual use, it would be the ReflectNode, or maybe some intermediate implementation where you manually give a parent table and a list of FKs.

Also, unfortunately only the first implementation can provide type-safety on the keys…

You should definitely support materialising your tree from a flattened result set, which you produce using recursive CTE.

Again, don’t see the recursion…
But yes, for retrieval there could be different sources, it doesn’t have to be DB -> Tree -> Graph. Could be used with anything.

Actually I’m only focusing on the tree: classes to model it, methods to create it, helpers to walk it, objects to hold graph data (TableRecords with extra properties, Records with collection-valued fields, Tuples…).
Then there is plenty of ways to use the tree. Writing the queries seems very database- and schema-dependent to me (i.e merge action specific to H2, handling of generated keys not needed if you only use natural PKs, ability to use deferred constraints…).

So when (if) I package it in a library, I think SQL generation from a tree will be left to the user. Or at the very least be packaged separately.

My thinking is (for insert):
- Insert the entire tree flattened in a bulk INSERT .. RETURNING
- Retreive all the generated IDs and associate them with the individual tree nodes in the client model
- Update the parent references in a bulk UPDATE or MERGE by joining an in-memory table containing the relationships to the actual table

Left to the user… or left to you! Seems you have lots of very good ideas ;)

When you say “bulk”, do you mean batch operations? I was under the impression that batches cannot return results…
Also this definitely needs deferred constraints to work, right?

That should be rather "easy."

Yeah I know, I said that writing fetch/merge/delete “is quite simple”… I think the complete sentence would be “is quite simple when you don’t give a damn about performance” ;)
I’m mainly developing prototype applications, so I confess performance is not much of a concern to me…

But I may share once properly packaged in a more standalone lib, if you're interested.

Absolutely interested!

Glad you are. But maybe I jumped the gun here. Not sure when I’ll find time to do it properly. Not sure how to share it properly either… creating a GitHub repo may be too much…

Anyway, thanks again for the useful feedback!
Thomas

[Lukas Eder]

Hi Thomas,

I'm sorry for the delay. This message slipped under my radar

2017-03-09 15:49 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

My usual advice here is to do what also some of the JPA advocates advise to do: Mix both worlds. Write the query with jOOQ, but execute and map it with JPA:
https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities

I had forgotten this “native query” stuff. I can indeed use JPA with SQL, so with jOOQ!

Yes, there's a thin line when to stick with the SQL paradigm, and when to switch to the ORM paradigm. I like to link to this article here by Mike Hadlow:

http://mikehadlow.blogspot.ch/2012/06/when-should-i-use-orm.html

He included a helpful diagram:

 

Glad you told me. I was starting to wonder how to use jOOQ to generate JPQL queries...

Don't! :)

In my opinion, once you start querying (entities or tuples (what those folks call "projections")), JPQL is so primitive that you'll fight the language limitations all the time, wishing you had switched to SQL from the beginning.

A long time ago, I thought JPQL at least doesn't bypass first and second level caches and somehow magically joins cached entities to actual entities. But it doesn't. So, I really don't see any benefit at all in using a JPQL (or Criteria) Query. But again, that's my opinion.

 

In theory, and in my opinion, the perceived impedance mismatch is only a SQL language implementation detail, or missing feature:

https://blog.jooq.org/2015/08/26/there-is-no-such-thing-as-object-relational-impedance-mismatch/

Interesting thoughts… This will make me think for a while…

Looking forward to your thoughts :)

 

Oh oh... That's a recursive tree structure. […] you could work around things by tricking and using recursive SQL, and then materialising the tree only in the client.

Errr… what? That’s just a very simple tree… Three tables, two relations (one 1-1 and one 1-N). In what way is it recursive?
I also don’t get the recursive SQL… I know how it works for a hierarchy of elements of the same type (i.e inside one table), but with 3 different tables, without any self-reference… Am I missing something?

Or maybe you misunderstood my schematic? That would explain your enthusiasm for this ;) because modelling an actual recursive tree, that would be more of a challenge…

OK, I misunderstood.

When you say “bulk”, do you mean batch operations? I was under the impression that batches cannot return results…
Also this definitely needs deferred constraints to work, right?

No, I try to clearly distinguish between the terms "bulk" and "batch" (they're heavily overloaded in the blogosphere, unfortunately).

- Bulk: Several rows per statement. This mostly optimises algorithms in the database.

- Batch: Several statements per request. This mostly optimises network traffic to the database.

Cheers,

Lukas

[Thomas GILLET]

Hello all,

One thing I felt missing this week is an easy way to create a custom derived table.

When writing a complex (sub-)query, I sometimes like to package it in its own class, together with its (usually aliases) fields.

Here a very simple example:

// Packaged query:

public class MyQuery {

    public final Table<...> QUERY;
    public final Field<Integer> FIELD;
   
    public MyQuery(String alias) {
       
        // The query
        this.QUERY =
             select(SOME_TABLE.SOME_FIELD)
            .from(SOME_TABLE)
            .asTable(alias);
       
        // The fields of the query, aliased accordingly
        this.FIELD = QUERY.field(SOME_FIELD);
    }
}

// Usage:

MyQuery q1 = new MyQuery("hello");
MyQuery q2 = new MyQuery("world");

select(q1.FIELD, q2.FIELD).from(q1.QUERY.join(q2.QUERY).on(...));

The idea being that I can alias my packaged query very easily, almost as if it was a generated table.

Not so useful here, but worth the trouble when the query has a lot of fields.

That was to explain the idea. Now the real thing:

Since it is almost like a generated table, why not make it exactly like a generated table? That is make MyQuery into an actual Table rather than having a Table field.

Here is what I would find very cool:

public class MyQuery extends SomeKindOfTableImpl<...> {

    public final static MyQuery MY_QUERY = new MyQuery();

    public final Field<Integer> FIELD = createField("field", ...);
   
    public MyQuery() {
        setTheQuery(
            select(anyExpression.as(FIELD))
            ...;
        );
    }
}

// And then use it as any other table:
select(MY_QUERY.FIELD).from(MY_QUERY);

// Or aliased:
MyQuery q = MY_QUERY.as("yop");
select(q.FIELD).from(q);

And here is a very very ugly try at a SomeKindOfTableImpl implementation:

public abstract class DerivedTableImpl<R extends Record> extends TableImpl<R> {

    /**
     * Default constructor.<br>
     * The underlying query must then be set by subclasses through {@code query()}.
     */
    protected DerivedTableImpl(String name) {
        super(name);
    }
   
    /**
     * Copy constructor, to be used when implementing {@code as()} in subclasses.<br>
     * The underlying query is copied from the given table.
     */
    protected DerivedTableImpl(String name, DerivedTableImpl<R> other) {
        super(name);
        setWrapped(this, getWrapped(other));
    }
   
    /**
     * Sets the underlying query.<br>
     * Should be called by subclasses in their (not copy) constructor.
     */
    protected DerivedTableImpl<R> query(Select<? extends R> query) {
        setWrapped(this, createDerivedTable(query));
        return this;
    }

    // Force subclasses to covariantly override this method
    /** {@inheritDoc} **/
    @Override
    public abstract DerivedTableImpl<R> as(String as);
   
//////// BLACK MAGIC
   
    /**
     * Set the internal alias of a {@code TableImpl}, even though field and class are final and package-private.<br>
     * Allow to control the {@code wrapInParentheses} flag, and to set the alias after the constructor call.
     */
    private static <R extends Record> void setWrapped(TableImpl<R> table, Table<R> wrapped) {
        Object alias = wrapped == null ? null : on("org.jooq.impl.Alias").create(wrapped, table.getName(), true).get();
        on(table).set("alias", alias);
    }
   
    /**
     * Get the table wrapped in the internal alias of a {@code TableImpl}.
     */
    private static <R extends Record> Table<R> getWrapped(TableImpl<R> table) {
        Object alias = on(table).get("alias");
        return alias == null ? null : on(alias).call("wrapped").get();
    }
   
    /**
     * Create a {@code DerivedTable} instance, even though the class is package-private.<br>
     * Allow to get a clean {@code DerivedTable} without aliasing (unlike {@code Select.asTable()}).
     */
    private static <R extends Record> Table<R> createDerivedTable(Select<? extends R> query) {
        return Reflect.on("org.jooq.impl.DerivedTable").create(query).get();
    }
}

Not intended to be used in real-life, but it seems to work. Just to show that all necessary code is already there, it's just not accessible.

For those interested, there is a way to do it without black-magic (create a Table implementation delegating to query.asTable(), tweak accept() to only visit the original query and add extra parenthesis, and use an instance of this as the aliased param of TableImpl constructor). But it is quite cumbersome.

Well! I may be the only one needing such a feature, but I found it very handy when packaging custom queries together with jOOQ generated classes in one single lib.

Cheers,

Thomas

[Thomas GILLET]

Hello Lukas,

Don't apologize for the delay, we all have a day job (and a night life, hopefully) to keep us busy.

Plus, I think your response time already beats any other support ;)

I was starting to wonder how to use jOOQ to generate JPQL queries...

Don't! :)

Well, I thought about that again, and I was wondering.

When using JPA to simplify a complex schema, writing (jOOQ) queries on the database level may hinder this simplification.
I mean, JPQL allows to stay on the JPA model side, not worrying about mapping details. Would not some kind of  jOOJPQL make sense in that case ?

For example, I have a very ugly schema with bad, incoherent column and table names, lots of room for @Embedded classes... It would be cool to have generated fields named after the JPA model, including embedded objects.

This example is only about generation but there is more to JPA... i.e you mentioned fetch joins earlier...

Hope I'm not going over the dark side of something with this jOOJPQL talk...

Modelling an actual recursive tree, that would be more of a challenge

OK, I misunderstood.

Actually, I think implementing a recursive tree would be both easier and funnier (optimization-wise) than a regular entity tree.

What a shame I've no use for that in my application!

 

No, I try to clearly distinguish between the terms "bulk" and "batch" (they're heavily overloaded in the blogosphere, unfortunately).

- Bulk: Several rows per statement. This mostly optimises algorithms in the database.

- Batch: Several statements per request. This mostly optimises network traffic to the database.

Ok I see. Thanks for the clarification. (And now I wonder about a batch of several bulk statements...)

 

About my entity tree thing I think I won't be improving it a lot for now, so I attach the source for those interested.

Incidentally, there is a TableKey class inside which could provide an example to my other post about typed keys.

Thomas

[steinar....@miles.no]

Hi

As mentioned earlier, I really like the flexibility that .Net MicroORMs offer. This could be offered by JOOQ as well with more unmapper support: https://github.com/jOOQ/jOOQ/issues/5896

Currently I get by with some custom extensions:

ColumnValueMap valMapper = ColumnValueMap.create("OWNERSYS.OWNER")
        .add(field("ID"), generatedSeqKey)
        .add(field("ENTITY_ID"), owner.getEntityId().Value)
        .add(field("NAME"), owner.getName())
        .add(field("OFFICIAL_SOURCE"), owner.getOfficialSourceType().getValue())
        .add(field("BUSINESS_TYPE_ID"), owner.getBusinessType().getValue())
        .add(field("OWNER_TYPE_ID"), owner.getOwnerType().getValue())
        .add(field("VALID_FROM"), new Timestamp(owner.getValidFrom().toInstant().getEpochSecond() * 1000L))
        .add(field("VALID_UNTIL"), new Timestamp(owner.getValidUntil().toInstant().getEpochSecond() * 1000L))
        .add(field("REGISTERED_TIME"), new Timestamp(owner.getRegisteredTime().toInstant().getEpochSecond() * 1000L));

ctx.insertInto(table(valMapper.Table), valMapper.getColumns()).values(valMapper.getValues()).execute();

..

This is almost identical to what is already supported by JOOQ, the difference is that you get more compact code by mapping column and value in a single .add() method, thus the expression will become more compact, which again increases readability.

 

An example of using the ColumnFieldMapper on simple types:

..

Contact c = new Contact(0,"t_at_t.no", "99221144", "Mr Anonymous");
ColumnFieldMap<Contact> mapper = new ColumnFieldMap<>(c, "OWNERSYS.CONTACT");
mapper.add(field("ID"), "Id").add(field("EMAIL"), "Email").add(field("PHONE"), "Phone").add(field("NAME"), "Name");
ctx.insertInto(table(mapper.Table), mapper.getColumns()).values(mapper.getValues()).execute();

Having support for more unmapper features in JOOQ would be great, and it is ultra flexible.

Thanks,

Steinar.

P.S I would not mind if the DAO support was removed.

[Lukas Eder]

Hi Thomas,

2017-03-17 16:47 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

Hello all,

One thing I felt missing this week is an easy way to create a custom derived table.

When writing a complex (sub-)query, I sometimes like to package it in its own class, together with its (usually aliases) fields.

Here a very simple example:

// Packaged query:

public class MyQuery {

    public final Table<...> QUERY;
    public final Field<Integer> FIELD;
   
    public MyQuery(String alias) {
       
        // The query
        this.QUERY =
             select(SOME_TABLE.SOME_FIELD)
            .from(SOME_TABLE)
            .asTable(alias);
       
        // The fields of the query, aliased accordingly
        this.FIELD = QUERY.field(SOME_FIELD);
    }
}

// Usage:

MyQuery q1 = new MyQuery("hello");
MyQuery q2 = new MyQuery("world");

select(q1.FIELD, q2.FIELD).from(q1.QUERY.join(q2.QUERY).on(...));

The idea being that I can alias my packaged query very easily, almost as if it was a generated table.

Not so useful here, but worth the trouble when the query has a lot of fields.

Yeah, that's this feature request here:

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

You're creating a view but you're not storing it in SQL, you're "storing it in jOOQ". Would definitely be nice!

Yes, there will be some sort of implementation. Probably not using reflection, though :) I cannot give any authoritative info yet about how this could best be implemented, but rest assured, I also want this! :)

Lukas

[Lukas Eder]

2017-03-20 15:45 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

Hello Lukas,

Don't apologize for the delay, we all have a day job (and a night life, hopefully) to keep us busy.

Don't know if you noticed, but this *is* my day job. ;)

Night life: sure. My kid wakes me up 3x per night, if that counts.

 

Plus, I think your response time already beats any other support ;)

Alright, good to know ;)

Well, I thought about that again, and I was wondering.

When using JPA to simplify a complex schema, writing (jOOQ) queries on the database level may hinder this simplification.
I mean, JPQL allows to stay on the JPA model side, not worrying about mapping details.

Hold your horses. JPQL bypasses first and second level query caches and suffers from 1-2 other problems that aren't mentioned on a high level discussion.

But anyway, I know what you mean :)

 

Would not some kind of  jOOJPQL make sense in that case ?

You mean criteria query?

 

For example, I have a very ugly schema with bad, incoherent column and table names, lots of room for @Embedded classes... It would be cool to have generated fields named after the JPA model, including embedded objects.

I'm not 100% sure if I follow, but there is a feature request for embedded records to be generated:

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

 

This example is only about generation but there is more to JPA... i.e you mentioned fetch joins earlier...

Hope I'm not going over the dark side of something with this jOOJPQL talk...

Don't know yet :) I think that JPQL is a half assed query language that wasn't well thought through (like SQL) and responded to quick needs (like jOOQ's DAOs). Fetch Joins are a quick win. MULTISET would have been the right solution (again). *Especially* in JPA, as that would be perfect for an ORM.

Nevertheless, there's always room for improvement (and there are always interesting features to steal).

Actually, I think implementing a recursive tree would be both easier and funnier (optimization-wise) than a regular entity tree.

What a shame I've no use for that in my application!

You may have no use, but you may have some (free?) time! :)

Cheers,

Lukas

[Lukas Eder]

Thank you very much for your feedback, Steinar.

I've cross linked your detailed suggestions from issue #5896 for better referencing.

Best Regards,

Lukas

--

[Danilo Cominotti Marques]

Lukas/Thomas,

Sometime ago I replied to this thread saying that it would be useful to be able to fetch the entire graph of related tables starting from a Record instance. I believe jOOQ is but a few steps away from some "ORMs" such as Bookshelf.js and Objection.js with regard to their "Data Mapper" capabilities, which is what I had in mind for jOOQ when I replied here (although I didn't know those ORMs at the time). Their beauty is in the way they only try to map tables instead of arbitrary entities and in the way SQL can be used at any time, even to specify which related tables should be fetched. jOOQ could provide all of that but with the beauty of its type safety.

Perhaps you would enjoy to see some examples of the usage of Objetion.js, such as in eager queries:

http://vincit.github.io/objection.js/#eager-queries

Kind regards,

Danilo

P.S.: Thanks for the great code snippets, Thomas!

[Lukas Eder]

Thanksf or those pointers. I'll be happy to study the two libraries.

Do note that "lazy" fetching (including the N+1 risk) is already possible through the UpdatableTable.fetchXXX() methods! But I'm not happy with those yet. We can do better :)

Cheers,

Lukas

[Thomas GILLET]

Hello Lukas,

Sorry for the delay (my turn), but my day job just came back at me... This may be the last message of my recent posting frenzy...

And indeed this is your day job! I forgot that for a moment. And my congratulations/condolences (choose one) for your night life ;)

About this jOOQPQL stuff, I finally read the article about JPA you pointed me to, as well as the excellent Joel Spolsky's article about leaky abstractions.

And now I see why adding a jOOQ abstraction on top of the JPQL abstraction of SQL may not be a good idea ;)

I'm not 100% sure if I follow, but there is a feature request for embedded records to be generated:
https://github.com/jOOQ/jOOQ/issues/2530

No, that was not my point. But that's a very interesting feature I'll keep an eye on.

I just meant, given an @Embeddable Address class, using SQL (so jOOQ) to write the query forces me to voluntarily "leak through" my JPA model abstraction, by using the actual column names (probably something like ADDRESS_STREET and co).

That would be particularly annoying for small code-first applications where the main point is to let JPA take charge of the DB. But that's probably completely off track for enterprise-grade applications.

And yes, by jOOQPQL I meant criteria builder, but a fluent one, with a generator to create type-safe classes for entities, fields and embeddables from the JPA model.

MULTISET would have been the right solution (again). *Especially* in JPA, as that would be perfect for an ORM.

You *do* love MULTISETs, don't you? But it seem indeed a very good news for the OO world. Would definitively replace the long collector chains I have to write to transform a result into a JSON-able tree.

Cheers,

Thomas

@Danilo: You're welcome. And thanks for the pointers.

[friso.v...@gmail.com]

Hi,

Sorry for the late reply, I find myself doing something like this regularly:

DSL.using(config).transaction(t -> {
    int inserted = DSL.using(t).insertInto(t).execute();
    Preconditions.checkState(inserted == 1);
});

So I guess I could do with a insertSingle (insertChecked?); a method that throws an exception if the insert did not insert 1 row (or an update did not update one row, or an update that did not at least update one row or a delete ...)

Groeten,

Friso

Op woensdag 6 juli 2016 14:16:44 UTC+2 schreef Lukas Eder:

Dear group,

Part of jOOQ's success is its incredible amount of convenience methods that help reduce the boiler plate code at your side. We do this with massive overloading of API, for instance, when you work with fetch(), you may have noticed how many different types of fetch() there are in jOOQ.

...

[Lukas Eder]

Hi Friso,

Never too late for interesting ideas! :)

And yours is interesting. There's the possibility of suffixing the execute method with the established terms "One" (cardinality 0:1) or with the new term "Single" (added in jOOQ 3.10, cardinality 1:1). These terms are there on the fetch API, because they indicate whether the result is a collection (0:N with fetch() : Result<Record>) or a single record (0:1 with fetchOne : Record), i.e. a separate method is required to provide a variation on the resulting type.

I personally think the cardinality of an update operation is not interesting enough to account for several methods like these. Perhaps we could allow for passing in a Predicate<Integer> or even a Consumer<Integer> style type, along the lines of the existing fetchInto(RecordHandler<R>) method. With that, you could write:

// Reusable consumer:

public static final Consumer<Integer> SINGLE_UPDATE = i -> Preconditions.checkState(i == 1);

// And then:

DSL.using(config).transaction(t -> {

    using(t).insertInto(t).values(...).executeInto(SINGLE_UPDATE);

});

I think that would be the solution that is most consistent with existing API.

I'll let this settle a bit. I like the idea, but there still needs to be a bit of refinement on how to exactly implement this API wise.

Lukas

--

[alam...@gmail.com]

Super small request

Can you please add isFetched() convenience method to a Record? Currently this field is private and cannot be read

Thanks for the great work!

On Wednesday, July 6, 2016 at 5:16:44 AM UTC-7, Lukas Eder wrote:

Dear group,

Part of jOOQ's success is its incredible amount of convenience methods that help reduce the boiler plate code at your side. We do this with massive overloading of API, for instance, when you work with fetch(), you may have noticed how many different types of fetch() there are in jOOQ.

[Lukas Eder]

Thanks a lot for your feedback. We could certainly add such a method, but would you mind explaining your use-case first?

--

[Charles Henry]

Dear Lukas,

I think I would request a passthrough or passive mode, which could be engaged in your org.jooq.conf.Settings class.

There are certain scenarios where we might want to disengage JOOQ.  For example, I have recently posted about JOOQ safely converting a Postgres BigInt / INT8  NaN into a zero.  For a special case, I would like to keep the original NaN value in my output, without removing the dependency on JOOQ, as the library is very valuable and useful.

Thank you, good sir!

:-)

-Charlie

[Lukas Eder]

Hi Charlie,

Thank you very much for taking the time to write your suggestion. May I counter-suggest we first get to the same level of understanding of the problem that you've started to state in this other discussion:

https://groups.google.com/d/msg/jooq-user/7hfDlmEwfQI/_muUo0XhBwAJ

Only then, we can assess whether such a "pass through" feature would really be desireable.

Thanks,

Lukas

--

[Lukas Eder]

Hi Samir,

This issue is now implemented in jOOQ 3.12:

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

Generated records will now contain mapper() and unmapper() methods that produce non-reflective RecordMapper and RecordUnmapper instances in case both records and pojos are being generated.

Thanks,

Lukas

On Thursday, July 7, 2016 at 4:50:21 AM UTC+2, csgeek wrote:

1.  One conversion that would be useful to have is the ability to convert from POJOs to Records and vice versa. 

On occasion I need to roll out my own POJO because our data is represented different at the service layer then how the data 

is actually stored, but I find myself using the autogenerated records and POJOs a good bit.  It would be nice if there was an easy way to going from say:

TableNameRecord  -->  TableName (pojo) and back.  

It varies on my use case but I tend to usually fetchInto(  . class) and most of the the class passed in is either the record or pojo that was generated by jooq.

--

Samir Faci

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Samir Faci]

I'm super late to the party was going to see about using the feature but it looks like this was more complicated than expected?  

Mostly chiming in for anyone else reading this to see that the feature was rolled back.

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.

--

Thank you
Samir Faci

https://keybase.io/csgeek

--

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7007e64b-daf1-496a-b7c8-fec78aee6d12%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Friso Vrolijken]

I have methods that take a Record as an argument. This is because the method can be entered from multiple other methods, each having their own query. So in this case I don't always know if a certain field is present in the Record. For this I created a helper method:

    public static <T> T optionalValueForField(Record record, Field<T> field) {
        int i = record.fieldsRow().indexOf(field);
        if (i < 0) {
            return null;
        }
        return record.get(i, field.getType());
    }

(and some variants of it). Some might prefer Optional<T> as a return type, but this works for me.

Op woensdag 6 juli 2016 14:16:44 UTC+2 schreef Lukas Eder:

What's your biggest "itch" in the jOOQ API, which jOOQ could "scratch", or rather, make go away by adding new convenience API?

All ideas welcome!

Lukas

[Lukas Eder]

Hi Samir,

There are a lot of edge cases related to arrays, user defined types, custom data type bindings, etc. So indeed, the feature turned out to cause much more trouble than it added value, at least from a maintenance perspective.

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAJ7OkQ_c%2BNvU4%2Bj9_44ohX6w-_-tL_7vqewa8tZ5SJT5Ey-j4A%40mail.gmail.com.

[mybo...@gmail.com]

I had something like this in mind for handling mapping from records to Pojos. Having something like this or better out of box will be nice!

See https://github.com/PicnicSupermarket/jolo

[Samir Faci]

Yeah, I figured.  No worries, just confirming that it was rolled back.  Plus for anyone who is reading this can easily see the feature was removed without digging into the code. 

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7wSR3QzxEbKvu8gfvG1sBHPsvHyRjGEgOdtPWj-tFKig%40mail.gmail.com.