Difference in SQL for sequence in db2 for Hibernate 4 and 5

Question

Is there a way I can manage or change the Hibernate 5 generated sql for how the sequence is retrived from AS400 DB2? Or revert it to use the Hibernate 4 SQL?

I have a Entity mapped as follows:

@Id
@Column(name = "D")
@SequenceGenerator(
        name = "art_id_gen",
        sequenceName = "G_ID_SEQ",
        allocationSize = 1)
@GeneratedValue(generator = "art_id_gen", strategy = GenerationType.SEQUENCE)
private long id;

When I use Hibernate 4 the SQL is (which works):

values nextval for G_ID_SEQ;

but when ussing Hibernate 5 the SQL is (which doesn't work):

select next_val as id_val from G_ID_SEQ for update with rs;

I'm using the org.hibernate.dialect.DB2400Dialect

Anyone have some input or suggestion how to fix this?

What dialect are you using?

piet.t
– piet.t

2016-09-01 12:31:08 +00:00
Commented Sep 1, 2016 at 12:31 — piet.t
– piet.t, Commented Sep 1, 2016 at 12:31

Perty · Accepted Answer · 2017-12-20 10:36:07Z

2

The solution was to use the:

org.hibernate.dialect.DB2Dialect

which is generating the same sql as hibernate 4:

values
    nextval for G_ID_SEQ

One year later I stumbled on this in my code and eventually we patched the DB2400Dialect:

/**
 * This is a patched version of the PatchedDB2400Dialect which is working in hibernate 5 and DB2 AS400
 *
 * There seems to be a problem with generated SQL for sequences and tables generated SQL for increment of unique id's
 * and identities. Either the sequence work when using DB2Dialect and not table generators or vice versa for DB2400Dialect.
**/
public class PatchedDB2400Dialect extends DB2400Dialect {
        private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
            @Override
            public String processSql(String sql, RowSelection selection) {
                if ( LimitHelper.hasFirstRow( selection ) ) {
                    //nest the main query in an outer select
                    return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
                            + sql + " fetch first " + getMaxOrLimit( selection ) + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
                            + selection.getFirstRow() + " order by rownumber_";
                }
                return sql + " fetch first " + getMaxOrLimit( selection ) + " rows only";
            }
...

edited Dec 20, 2017 at 10:36

answered Sep 1, 2016 at 12:56

Perty

9212 gold badges9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

piet.t Over a year ago

org.hibernate.dialect.DB2400Dialect might be even more appropriate and save you some headaches in the more exotic cases.

Daniel Over a year ago

Interesting that a more general dialect works better than a more specific. Smells like a bug.

Neil Stockton Over a year ago

Interesting that Hibernate still requires users to specify the dialect and not auto-detect it from the JDBC driver and database, when other JPA providers do it automatically by default

Collectives™ on Stack Overflow

Difference in SQL for sequence in db2 for Hibernate 4 and 5

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related