Re: Porting from Hibernate to jOOQ - question

[Lukas Eder]

Hello,

The jOOQ DSL API is composed of various types that can be created
independently from a SELECT statement. Your Criteria API client code
could translate to this:

Condition c = Factory.trueCondition();
if ( firstName != null )
condition = condition.and(FIRST_NAME.eq(firstName));
if ( lastName != null )
condition = condition.and(LAST_NAME.eq(lastName));
if ( license != null )
condition = condition.and(LICENSE.eq(license));
if ( birthYear > 0 )
condition = condition.and(BIRTH_YEAR.eq(birthYear));

// And then
Result<Record> res =
factory.select().from(PERSON).where(condition).fetch();
// Or even
Result<PersonRecord> res =
factory.selectFrom(PERSON).where(condition).fetch();

Hope this helps

2012/9/19 JK <jk3...@gmail.com>:
>
>
> Hello,
>
> I am semi seriously porting a project from Hibernate to jOOQ. I would like
> to hear your ideas / comments how to port following code to jOOQ. In
> Hibernate it is easy to build criteria in steps. Hibernate internally adds
> "and" between each criteria components.
>
>
> public static List<Person> selectPerson( SessionHolder sessionHolder,
> String firstName, String lastName, String license, int birthYear, int mode )
> {
> Criteria criteria =
> sessionHolder.getSession().createCriteria(Person.class);
> if ( firstName != null )
> criteria = criteria.add(eq("firstName",firstName));
> if ( lastName != null )
> criteria = criteria.add(eq("lastName",lastName));
> if ( license != null )
> criteria = criteria.add(eq("license",license));
> if ( birthYear > 0 )
> criteria = criteria.add(eq("birthYear",birthYear));
> List<Person> personList = criteria.list();
> ... code deleted ...
> return personList;
> }
>
>
> selectPerson is used so that depending on the case one, two, ... all of the
> parameters: firstName, lastName, license, birthYear are given. Number of
> different parameter combinations (given / missing) is so high that it is not
> a solution to write a code for each solution:
>
> public static List<Person> selectPerson( Factory factory, String
> firstName, String lastName, String license, int birthYear, int mode ) {
>
> if (( firstName != null ) && ( lastName != null ) && ( license !=
> null ) && ( birthYear > 0 )) {
> Result<Record> res = factory.select().from(PERSON).where(....
> } else (( firstName != null ) && ( lastName != null ) && ( license
> != null )) {
> Result<Record> res = factory.select().from(PERSON).where(....
> } else (( firstName != null ) && ( lastName != null )) {
> Result<Record> res = factory.select().from(PERSON).where(....
> } else (( firstName != null )) {
>
> } else ...
> ... code deleted ...
> return personList;
> }
>
>
> Any ideas are welcome!
> Thank you for your help
>
> JK
>

[Ben Hood]

On Wednesday, 19 September 2012 22:39:58 UTC+1, JK wrote:

I am semi seriously porting a project from Hibernate to jOOQ. I would like to hear your ideas / comments how to port following code to jOOQ. In Hibernate it is easy to build criteria in steps. Hibernate internally adds "and" between each criteria components.

I ported a large project from Hibernate to JOOQ and couldn't be happier - the control that you win back is great.

In order to factor out conditions in our statements, we've used this construct in a few instances (excuse the Scala code, but the code should be isomorphic for Java):

def insertOrUpdate[R <: Record](t:Factory, table:Table[R], finders:Map[_ <: Field[_], _], values:Map[_ <: Field[_], _]) {

  t.insertInto(table).

      set(finders).

      set(values).

    onDuplicateKeyUpdate().

      set(values).

    execute()

} 

Basically we're passing in a map of PK fields to identify the row and a map of the rest of the non-PK values we want to insert into the row.

This may not be ideal solution to use everywhere, since it de-SQLizes your code to an extent, but it does avoid some repetition and allows to pass in a dynamic bag of attributes and still retain type safety(*).

FWIW another nice way I've seen this done is the way myBatis uses thread locals to avoid the dot notation when constructing SQL programmtically. However this is just syntactical sugar as it doesn't actually type check (*) your tables or their columns in the way JOOQ does - not to mention all of the other benefits JOOQ gives you.

HTH,

Ben

(*) for some value of type safety

[Lukas Eder]

> I ported a large project from Hibernate to JOOQ and couldn't be happier -
> the control that you win back is great.

Thanks for sharing, Ben

> FWIW another nice way I've seen this done is the way myBatis uses thread
> locals to avoid the dot notation when constructing SQL programmtically.

Yes, let's re-publish that interesting idea by myBatis here on the user group:
http://www.mybatis.org/core/statement-builders.html

Cheers
Lukas

[JK]

Thank you for your responses. Factory.trueCondition() was exactly what I was looking for.

How would you write following parametrized SQL with DSL API:

select name from person where name :EQUALITY ':VALUE' order by name :ORDER

:VALUE part is easy, but how to implement :EQUALITY and :ORDER without writing any SQL?

I was looking for something like this:

equality = jOOQ.Equality.GT
order = jOOQ.Order.ASC

select().from(PERSON).where(PERSON.NAME.comparison(equality,value)).orderBy(order)

but it seems not to go this way.

[JK]

Modified the code.

On Monday, September 24, 2012 9:17:43 AM UTC+3, JK wrote:

Thank you for your responses. Factory.trueCondition() was exactly what I was looking for.

How would you write following parametrized SQL with DSL API:

select name from person where name :EQUALITY ':VALUE' order by name :ORDER

:VALUE part is easy, but how to implement :EQUALITY and :ORDER without writing any SQL?

I was looking for something like this:

equality = jOOQ.Equality.GT

ordr = jOOQ.Order.ASC

select().from(PERSON).where(PERSON.NAME.comparison(equality,value)).orderBy(PERSON.NAME.order(ordr))

[Lukas Eder]

Hello

> :VALUE part is easy, but how to implement :EQUALITY and :ORDER without
> writing any SQL?
>
> I was looking for something like this:
>
> equality = jOOQ.Equality.GT
> order = jOOQ.Order.ASC

Those are good ideas, but they're currently not available in the
public API. I have added feature requests #1841, #1482 for this:

- Adding SortField Field.sort(SortOrder)
https://github.com/jOOQ/jOOQ/issues/1841

- Adding Condition Field.compare(Comparator, Field<T>)
https://github.com/jOOQ/jOOQ/issues/1842

These will make it into jOOQ 2.6.0

Cheers
Lukas

[JK]

Hello again and thank you for these new (coming) features.

Could it be possible to have same type of functionality for creating joins?

Now there are functions:

                    leftOuterJoin(JoinType jointType, String sql)
                    leftOuterJoin(JoinType jointType, TableLike<?> table)
                    leftOuterJoin(JoinType jointType, String sql, Object... bindings)
                    leftOuterJoin(JoinType jointType, String sql, QueryPart... parts)

for all join types.

My proposal is to have also these:

                    enum JoinType { JOIN, LEFT_OUTER_JOIN, RIGHT_OUTER_JOIN, ... }
                   
                    join(JoinType jointType, String sql)
                    join(JoinType jointType, TableLike<?> table)
                    join(JoinType jointType, String sql, Object... bindings)
                    join(JoinType jointType, String sql, QueryPart... parts)

What is your schedule for v2.6?

Cheers,
Jouni

[JK]

There was error in code

On Monday, September 24, 2012 1:01:09 PM UTC+3, JK wrote:

Hello again and thank you for these new (coming) features.

Could it be possible to have same type of functionality for creating joins?

Now there are functions:

                    leftOuterJoin(String sql)
                    leftOuterJoin(TableLike<?> table)
                    leftOuterJoin(String sql, Object... bindings)
                    leftOuterJoin(String sql, QueryPart... parts)

[Lukas Eder]

> Could it be possible to have same type of functionality for creating joins?

For joins, this might be a bit more complex. Not all join types
require an ON or USING clause (e.g. NATURAL JOIN, CROSS JOIN). I'll
have to think about this. Maybe the "Condition..." argument is
optional. I'll track this as #1844, due for jOOQ 2.6.0
https://github.com/jOOQ/jOOQ/issues/1844

Please tell me if you find any other parts from the DSL API that
should be rendered more dynamic.

Note, there is already a non-DSL "classic" API originating from the
early days of jOOQ. This is mentioned here:
http://www.jooq.org/doc/2.5/manual/sql-building/sql-statements/dsl-and-non-dsl/#N10674

Under the hood, every DSL query uses this "classic" API. In the case
of joins, this is already available here:
http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html#addJoin(org.jooq.TableLike,%20org.jooq.JoinType,%20org.jooq.Condition...)

The SelectQuery object is available from every DSL Select step:
http://www.jooq.org/javadoc/latest/org/jooq/SelectFinalStep.html#getQuery()

> What is your schedule for v2.6?

I doubt I'll manage to release an official version before the end of
October. However, I release working SNAPSHOT versions from time to
time. They're available from here:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

Cheers
Lukas

[JK]

On Monday, September 24, 2012 2:10:17 PM UTC+3, Lukas Eder wrote:

Please tell me if you find any other parts from the DSL API that
should be rendered more dynamic.

At the moment I don't have any other ideas in this area.
 

Note, there is already a non-DSL "classic" API originating from the
early days of jOOQ. This is mentioned here:
http://www.jooq.org/doc/2.5/manual/sql-building/sql-statements/dsl-and-non-dsl/#N10674

I have to check that.
 

Under the hood, every DSL query uses this "classic" API. In the case
of joins, this is already available here:
http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html#addJoin(org.jooq.TableLike,%20org.jooq.JoinType,%20org.jooq.Condition...)

The SelectQuery object is available from every DSL Select step:
http://www.jooq.org/javadoc/latest/org/jooq/SelectFinalStep.html#getQuery()

Can I add query parts in any order (addFrom, addSelect, addJoin, AddCondition, add... )? At least following seems to work:

SelectQuery query = db.select().from(AUTHOR).where(BOOK.TITLE.le("Java Book")).orderBy(AUTHOR.LAST_NAME,BOOK.TITLE).limit(5).getQuery();
query.addJoin(BOOK, JoinType.LEFT_OUTER_JOIN, BOOK.AUTHOR_ID.eq(AUTHOR.ID));

i.e. query is otherwise ready but then as a last step I add the join. Is this coincidence or general feature of the non-DSL API?

My porting project is still on the way, but I have to say that jOOQ seems to be very promising. It is fun to work with it.

Cheers

Jouni

[Lukas Eder]

> Can I add query parts in any order (addFrom, addSelect, addJoin,
> AddCondition, add... )? At least following seems to work:
>
> SelectQuery query = db.select().from(AUTHOR).where(BOOK.TITLE.le("Java
> Book")).orderBy(AUTHOR.LAST_NAME,BOOK.TITLE).limit(5).getQuery();
> query.addJoin(BOOK, JoinType.LEFT_OUTER_JOIN, BOOK.AUTHOR_ID.eq(AUTHOR.ID));

Yes, this works

> i.e. query is otherwise ready but then as a last step I add the join. Is
> this coincidence or general feature of the non-DSL API?

It's a "feature". The non-DSL API evolved somewhat organically, being
a half-internal API. Expect things to be reviewed (and maybe changed)
for jOOQ 3.0 due for late 2012. In the case of addJoin(), the joined
table is joined to the last table referenced from the FROM clause. In
other words, once you've written

select().from(A, B)

you can no longer join tables to A, only to B. If you add C using
addFrom(C), you can no longer join tables to B, only to C. In early
days of jOOQ, I thought it was a good idea to have a non-DSL API with
only "setters" and "adders", no "getters". API evolution has shown
that users will want to have full access to jOOQ's internal QueryPart
representation for many reasons, e.g. to patch SQL statements
centrally using an ExecuteListener:
http://www.jooq.org/doc/2.5/manual/sql-execution/execute-listeners/

This will all be improved in jOOQ 3.0

> My porting project is still on the way, but I have to say that jOOQ seems to
> be very promising. It is fun to work with it.

Great, good to know!

Cheers
Lukas

[Lukas Eder]

> - Adding SortField Field.sort(SortOrder)
> https://github.com/jOOQ/jOOQ/issues/1841
>
> - Adding Condition Field.compare(Comparator, Field<T>)
> https://github.com/jOOQ/jOOQ/issues/1842

These features are implemented on GitHub master and will be included
in the next SNAPSHOT.
The "dynamic JOIN" feature will follow shortly

Cheers
Lukas

[Lukas Eder]

> These features are implemented on GitHub master and will be included
> in the next SNAPSHOT.
> The "dynamic JOIN" feature will follow shortly

"dynamic JOIN" is added to the DSL API:
https://github.com/jOOQ/jOOQ/issues/1844

[Adam Gent]

On Thursday, September 20, 2012 4:57:42 PM UTC-4, Ben Hood wrote:

FWIW another nice way I've seen this done is the way myBatis uses thread locals to avoid the dot notation when constructing SQL programmtically. However this is just syntactical sugar as it doesn't actually type check (*) your tables or their columns in the way JOOQ does - not to mention all of the other benefits JOOQ gives you.

HTH,

Ben

(*) for some value of type safety

 I Actually ported the myBatis SqlBuilder to NOT use thread locals: http://adamgent.com/post/30974973820/mybatis-sqlbuilder-rewritten

Clinton from myBatis I think will incorporate that style in future releases.

As for type checking its nice... but its sort of lie. Its not guaranteed syntactically correct unless it runs against the database particularly if the query is dynamically constructed.

This was the impetus for Play 2.0 to choose Anorm (which although I agree with the idea I think the Play 2.0 team just lost some steam and kind of pooped out Anorm).

However the table constant/field generation... thats pretty awesome and does add some nice safety. 

 

[JK]

Hi Lukas,

And thank you again for these features! You are really fast developing them.

Couple of new ideas came into my mind during the port:

Feature proposal 1:

Currently generated record.store() returns 1 if the record was stored to the database. 0 if storing was not necessary.

Proposed functionality:

Generated XXXRecord.store() returns    
                    1 if the record was inserted to the database.
                    2 if the record was updated in the database.
                    0 if storing was not necessary.


Feature proposal 2:

Take SQL table field default values into use in XXXRecord constructors. Generate XXXRecord field initialization from SQL table field default values.
Designer could enable or disable this default value generation by editing the code generation configuration XML file, e.g:
<configuration>
  <generator>
    <generate>
        <defaults>true</defaults>

With this functionality designer could have correct default values in XXXRecord instances even before storing them into db.


Example SQL

    create table Person (
        id integer generated by default as identity (start with 1) primary key,
        birth_year integer default 1950 not null,
        first_name varchar_ignorecase(255),
        last_name varchar_ignorecase(255),
        license varchar_ignorecase(255) unique,
        sex_int integer default 0,
        version integer not null,
    ...
    );

This would yield

    class PersonRecord {
    public PersonRecord() {
        setBirthYear(1950);
        setSexInt(0);
    }

    }





Feature proposal 3:

When XXXRecord.setValue(Field,value) function is called record raises a field specific flag "isChanged" to indicate that field is changed and it must be stored in db during next XXXRecord.store()

Proposed functionality:

To add a function XXXRecord.setChanged(Field field, boolean isChanged) which sets the value true or false. This is needed e.g. when records are copied field by field to new  (e.g. extended class) records. Ofcourse reflection can be used to reset isChanged, but it is not very efficient way of doing it.



Cheers,

Jouni

[Lukas Eder]

Hello Jouni,

> Feature proposal 1:
> [...]

> Generated XXXRecord.store() returns
> 1 if the record was inserted to the database.
> 2 if the record was updated in the database.
> 0 if storing was not necessary.

I'm not sure this is a good idea. 1 isn't a code indicating that a
record was stored, it means that "one" record was affected by the
underlying SQL statement, just as you may get 1 from int count =
stmt.executeUpdate("INSERT INTO ..");

There are some parts of the API that involve updating records, which
already return values greater than 1 to indicate that 2 records were
updated, for instance.

What would you need this information for? Maybe there is another way
to know this.

> Feature proposal 2:
>
> Take SQL table field default values into use in XXXRecord constructors.
>

> With this functionality designer could have correct default values in
> XXXRecord instances even before storing them into db.

Yes, I had recently thought about this feature. It won't be very
simple to implement backwards-compatibly, as today the default values
are applied implicitly if you don't call the relevant setters. Also,
POJO generation would have to be considered carefully.

Also, maybe there is a way to implement this without a new generator
setting. Intuitively, I feel that this change shouldn't be
configurable - if backwards-compatibility can be guaranteed.

But I'll still register it as a feature request #1859
https://github.com/jOOQ/jOOQ/issues/1859

However, this only works with constant default values. SQL allows for
various expressions to be supplied to DDL DEFAULT clauses. I'll have
to evaluate how those would play into this feature.

> Feature proposal 3:

>
> Proposed functionality:
>
> To add a function XXXRecord.setChanged(Field field, boolean isChanged) which
> sets the value true or false. This is needed e.g. when records are copied
> field by field to new (e.g. extended class) records. Ofcourse reflection
> can be used to reset isChanged, but it is not very efficient way of doing
> it.

That API suggestion feels like internals are allowed to leak to the
public API. Why would you need this?

Cheers
Lukas

[jk3m41l]

On 3.10.2012 10:10, Lukas Eder wrote:

Hello Jouni,

Feature proposal 1:
[...]
Generated XXXRecord.store() returns
                    1 if the record was inserted to the database.
                    2 if the record was updated in the database.
                    0 if storing was not necessary.

I'm not sure this is a good idea. 1 isn't a code indicating that a
record was stored, it means that "one" record was affected by the
underlying SQL statement, just as you may get 1 from int count =
stmt.executeUpdate("INSERT INTO ..");

There are some parts of the API that involve updating records, which
already return values greater than 1 to indicate that 2 records were
updated, for instance.

What would you need this information for? Maybe there is another way
to know this.

I have record manipulation on one layer and storing is done on other layer. Storing part can do different type of logging depending on the operation (=insert, update, nothing).

I understand that the change might be questionable if the meaning of the return value is currently "number of rows changed".

Is it possible to have a function that returns the type of last executed operation in db.

E.g.
    Author a = ...
    a.store()
    Operation op = a.getLastOpetion();
        if ( op == Operation.INSERT )
        ...

    or

    Author a = ...
    a.store()
    Operation op = factory.getLastOpetion();
        if ( op == Operation.INSERT )
        ...

or even better

factory.getNumberOfInsertedRows()
factory.getNumberOfUpdatedRows()

Feature proposal 3:

Proposed functionality:

To add a function XXXRecord.setChanged(Field field, boolean isChanged) which
sets the value true or false. This is needed e.g. when records are copied
field by field to new  (e.g. extended class) records. Ofcourse reflection
can be used to reset isChanged, but it is not very efficient way of doing
it.

That API suggestion feels like internals are allowed to leak to the
public API. Why would you need this?

I made a piece of code that parses records from query resulting multiple different types of records. Suppose you have SQL:

select * from author left join book on author.id=book.author_id left join language on book.language_id=language.id

Resulting rows might look something like this:

Author1, Book1, Language1
Author1, Book2, Language1
Author1, Book3, null
Author2, Book4, Language2
Author3, null,  null

Where AuthorX means all column values belonging to AuthorX, that is: select * from Author where id = id of AuthorX.

My simple result parser creates Author, Book and Language records from Result<Record>. This involves copying record fields and without setting isChanged=false afterwards these newly created Author, Book and Language records have all fields dirty although they are just fetched from db. I made a temporary resetting solution using reflection, but I would like to replace it with better / faster solution.


Cheers,

Jouni

[Lukas Eder]

Hi Jouni,

>> What would you need this information for? Maybe there is another way
>> to know this.
>
> I have record manipulation on one layer and storing is done on other layer.
> Storing part can do different type of logging depending on the operation
> (=insert, update, nothing).

If this is purely about logging, you might find the ExecuteListener
API useful for your purpose:

http://www.jooq.org/doc/2.5/manual/sql-execution/execute-listeners/
http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html

For instance, you could perform this check:

public class MyListener extends DefaultExecuteListener {
public void end(ExecuteContext ctx) {
if (ctx.query() instanceof Insert) {
// Is insert
}
else if (ctx.query() instanceof Update) {
// Is update

}
}
}

> Is it possible to have a function that returns the type of last executed
> operation in db.
>
> E.g.
> Author a = ...
> a.store()
> Operation op = a.getLastOpetion();
> if ( op == Operation.INSERT )
> ...

This is indeed an option. I have registered feature request #1865 for this.
https://github.com/jOOQ/jOOQ/issues/1865

I'll think about other options too as I feel that a
"getLastOperation()" method is a bit of a quirk, similar to JDBC's
ResultSet.wasNull(). But it is an option.

> Author a = ...
> a.store()
> Operation op = factory.getLastOpetion();
> if ( op == Operation.INSERT )

That's the wrong spot to implement this. This wouldn't suit batch
operations, or store() operations that use an ExecuteListener-injected
Connection, in case of which no factory reference might be available:
http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html#start(org.jooq.ExecuteContext)

Besides, the Factory can also execute plain SQL queries, where such an
information would have to map to Operation.UNKNOWN

> or even better
>
> factory.getNumberOfInsertedRows()
> factory.getNumberOfUpdatedRows()

This would suit batch operations, but the "Factory problems" mentioned
before would remain the same. I really think that this information has
to stay close to the store() methods, as they're the only ones causing
this "problem" of not knowing whether an INSERT or an UPDATE was
performed.

>> That API suggestion feels like internals are allowed to leak to the
>> public API. Why would you need this?
>

> [...]

>
> My simple result parser creates Author, Book and Language records from
> Result<Record>. This involves copying record fields and without setting
> isChanged=false afterwards these newly created Author, Book and Language
> records have all fields dirty although they are just fetched from db. I made
> a temporary resetting solution using reflection, but I would like to replace
> it with better / faster solution.

I see. Yes that topic is already adressed in some ticket. In the mean
time, you might want to consider explicitly executing INSERT / UPDATE
operations:

INSERT: http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#executeInsert(R)
UPDATE: http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#executeUpdate(R)

This might also resolve your first issue...

Cheers
Lukas