-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
arensa
 Post subject: Column order in DB play a role when using auto-generated IDs
PostPosted: Fri Apr 22, 2016 3:38 am 
Newbie

Joined: Fri Apr 22, 2016 3:17 am
Posts: 3
Hello,

TL:DR: Using a PosgreSQL DB 9.1, I had to reorder the columns of a table to make sure the primary key column that is automatically filled by a sequence, is the first one in the table. Otherwise, Hibernate will give me an exception. For me it seems that Hibernate assumes that the ID column is the first one in the returning set for creating/completing the entity objects.


Code:
DEBUG [org.hibernate.SQL] insert into phrases (frequency, language, phrase) values (?, ?, ?)
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [1] as [INTEGER] - 4
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [2] as [VARCHAR] - ger
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [3] as [VARCHAR] - lklkh
WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0, SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for type int : lklkh


More details. I noticed the strange behaviour using Hibernate 4.01 in JBoss AS 7.1.1:

An entity bean is specified to have its primary key (Integer value) generated by the database via a sequence:

In the bean:
Code:
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public java.lang.Integer getId()
    {
        return id;
    }
   ...


In the PostgreSQL 9.1 database:

Code:
\d phrases
                                     Table "phrases"
  Column   |         Type         |                              Modifiers
-----------+----------------------+----------------------------------------------------------------------
phrase    | text                 |
frequency | integer              | default 4
language  | character varying(3) |
id        | bigint               | not null default nextval('phrases_id_seq'::regclass)
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)


Only when reordering the ID column in the database as first one, it works without a flaw.

Code:
\d phrases
                                     Table "phrases"
  Column   |         Type         |                              Modifiers
-----------+----------------------+----------------------------------------------------------------------
id        | bigint               | not null default nextval('phrases_id_seq'::regclass)
phrase    | text                 |
frequency | integer              | default 4
language  | character varying(3) |
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)




The following is given as root cause stack trace. Let me know if you need more details:

Code:
javax.persistence.PersistenceException: org.hibernate.exception.DataException: Bad value for type int : lklkh
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1295) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:876) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   at org.jboss.as.jpa.container.AbstractEntityManager.merge(AbstractEntityManager.java:548) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
   at lu.tudor.santec.menssana.wikifood.ejb.session.beans.OCRDictionaryManagerBean.addOCRDictionaryPhrase(OCRDictionaryManagerBean.java:210) [wikifood.jar:]
   at lu.tudor.santec.menssana.wikifood.ejb.session.beans.OCRDictionaryManagerBean.checkTextForDictionaryContents(OCRDictionaryManagerBean.java:124) [wikifood.jar:]
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
   at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
   at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:202) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
   ... 130 more
Caused by: org.hibernate.exception.DataException: Bad value for type int : lkjlkjlkj ljlékjlékj lékm éksdfg aslkgfj
   at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:134) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:108) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at com.sun.proxy.$Proxy105.getInt(Unknown Source)
   at org.hibernate.id.IdentifierGeneratorHelper.get(IdentifierGeneratorHelper.java:120) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.id.IdentifierGeneratorHelper.getGeneratedIdentity(IdentifierGeneratorHelper.java:89) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:100) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:55) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2757) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3268) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:78) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:272) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:304) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:128) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.ejb.event.EJB3MergeEventListener.saveWithGeneratedId(EJB3MergeEventListener.java:71) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:422) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.DefaultMergeEventListener.mergeTransientEntity(DefaultMergeEventListener.java:342) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:304) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:259) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:86) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:781) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:766) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:770) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:867) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
   ... 152 more
Caused by: org.postgresql.util.PSQLException: Bad value for type int : lkjlkjlkj ljlékjlékj lékm éksdfg aslkgfj
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2955)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2138)
   at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1052)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
   at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
   at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:104) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
   ... 175 more



Thanks,
Andreas
Top
 Profile  
 
vlad
 Post subject: Re: Column order in DB play a role when using auto-generated IDs
PostPosted: Fri Apr 22, 2016 3:59 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
That looks like an issue. Can you open a Jira issue and provide a test case for it? We have these templates that make it very easy to add a replicating use case. Thanks.
Top
 Profile  
 
arensa
 Post subject: Re: Column order in DB play a role when using auto-generated IDs
PostPosted: Mon Apr 25, 2016 7:05 am 
Newbie

Joined: Fri Apr 22, 2016 3:17 am
Posts: 3
Thanks for your feedback. Unfortunately, I am not able to reproduce this error in a test case. I suppose either the problem does not affect H2 but only PostgreSQL databases or my way of recreating the table through native queries is incorrect. Here's what I've tried:

Code:
      entityManager.getTransaction().begin();
      TestBean tb = new TestBean();
      tb.setPhrase("abc");
      TestBean tb2 = entityManager.merge(tb);
      entityManager.getTransaction().commit();      
      logger.info(tb2.getId() + ": " + tb2.getPhrase());
      entityManager.detach(tb2);
      
      entityManager.getTransaction().begin();
      
      entityManager.createNativeQuery("drop table TestBean if exists").executeUpdate();
      entityManager.createNativeQuery("create table TestBean (" +
            "phrase varchar(255)," +
            "id integer generated by default as identity," +
            "primary key (id))").executeUpdate();
      tb = new TestBean();
      tb.setPhrase("def");
      tb2 = entityManager.merge(tb);
      entityManager.getTransaction().commit();      
      logger.info(tb2.getId() + ": " + tb2.getPhrase());


Should I create a JIRA issue anyway?
Top
 Profile  
 
vlad
 Post subject: Re: Column order in DB play a role when using auto-generated IDs
PostPosted: Mon Apr 25, 2016 8:53 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You can create a test case for PostgreSQL if that's where you can replicate the issue. If you cannot replicate it, then how can we know it's a bug?
Top
 Profile  
 
arensa
 Post subject: Re: Column order in DB play a role when using auto-generated IDs
PostPosted: Tue Apr 26, 2016 4:21 am 
Newbie

Joined: Fri Apr 22, 2016 3:17 am
Posts: 3
Okay, I was able to create a test case with PosgreSQL version 9.1 and 9.5 using Hibernate version 4.0.1.Final. With Hibernate 4.3.11.Final, the error does not show up. So I suppose the error has been closed in the meantime? Do you still want me to create a JIRA issue and attach the test code?.
Top
 Profile  
 
vlad
 Post subject: Re: Column order in DB play a role when using auto-generated IDs
PostPosted: Tue Apr 26, 2016 5:10 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If the issue does not replicate in a later version, it means it got fixed, in which case you might want to upgrade.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.