Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    42

    Unanswered: Oracle dialect does not support identity key generation

    Hello,

    I'm trying to run an integration tests suite against an Oracle 10g XE database server on a Linux box. But I have one issue that makes all the tests in error. Here is the message I get for each and every test:

    ]Results :

    Tests in error:
    testSaveAndRetrieve(com.thalasoft.learnintouch.cor e.dao.AdminDaoTest): com.thalasoft.learnintouch.core.dao.dialect.Custom Oracle10gDialect does not support identity key generation; nested exception is org.hibernate.MappingException: com.thalasoft.learnintouch.core.dao.dialect.Custom Oracle10gDialect does not support identity key generation
    I wonder what kind of domain classes mapping setup is required for Hibernate to correctly talk to Oracle.

    Also, I hope I can keep the domain classes mapping setup common to MySql and Oracle and not be forced to have one specific for each database server.

    I'm using the Oracle driver ojdbc14 10.2.0.4.0 with Spring 3.1.3.RELEASE and Hibernate 3.6.9.Final versions.

    Here is my Hibernate Spring setup and a domain class mapping setup.
    Code:
    	<bean id="sessionFactory"
    		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    		<property name="dataSource">
    			<ref bean="dataSource" />
    		</property>
    		<property name="mappingDirectoryLocations">
    			<list>
    				<value>classpath:com/thalasoft/learnintouch/core/domain</value>
    			</list>
    		</property>
    		<property name="mappingResources">
    			<list>
    				<value>com/thalasoft/learnintouch/core/domain/typedef.hbm.xml</value>
    			</list>
    		</property>
    		<property name="hibernateProperties">
    			<props>
    				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
    				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
    				<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
    				<prop key="hibernate.show_sql">true</prop>
    				<prop key="hibernate.format_sql">true</prop>
    				<prop key="hibernate.c3p0.min_size">5</prop>
    				<prop key="hibernate.c3p0.max_size">20</prop>
    				<prop key="hibernate.c3p0.timeout">1800</prop>
    				<prop key="hibernate.c3p0.max_statements">50</prop>
    			</props>
    		</property>
    	</bean>
    
    	<!-- Setup the Spring transaction manager -->
    	<bean id="transactionManager"
    		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    		<property name="sessionFactory">
    			<ref local="sessionFactory" />
    		</property>
    	</bean>
    
    	<tx:annotation-driven />
    
    	<bean		class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor" />
    
    	<!--  Translate dao exceptions into Spring exceptions -->
    	<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
    Code:
    <?xml version="1.0"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    <hibernate-mapping>
    	<class name="com.thalasoft.learnintouch.core.domain.Admin" table="admin"
    		dynamic-insert="true" dynamic-update="true">
    		<id name="id" type="java.lang.Integer">
    			<column name="id" />
    			<generator class="identity" />
    		</id>
    		<version name="version" type="int">
    			<column name="version" not-null="true" />
    		</version>
    		<property name="login" type="string">
    			<column name="login" length="50" not-null="true" unique="true" />
    		</property>
    		<property name="password" type="string">
    			<column name="password" length="100" not-null="true" />
    		</property>
    		<property name="passwordSalt" type="string">
    			<column name="password_salt" length="50" />
    		</property>
    		<property name="firstname" type="string">
    			<column name="firstname" not-null="true" />
    		</property>
    		<property name="lastname" type="string">
    			<column name="lastname" not-null="true" />
    		</property>
    		<property name="email" type="string">
    			<column name="email" not-null="true" />
    		</property>
    		<property name="superAdmin" type="boolean">
    			<column name="super_admin" not-null="true" />
    		</property>
    		<property name="preferenceAdmin" type="boolean">
    			<column name="preference_admin" not-null="true" />
    		</property>
    		<property name="address" type="string">
    			<column name="address" />
    		</property>
    		<property name="zipCode" type="string">
    			<column name="zip_code" length="10" />
    		</property>
    		<property name="city" type="string">
    			<column name="city" />
    		</property>
    		<property name="country" type="string">
    			<column name="country" />
    		</property>
    		<property name="profile" type="text">
    			<column name="profile" length="65535" />
    		</property>
    	</class>
    </hibernate-mapping>
    I'm also using a custom dialect:
    Code:
    public class CustomOracle10gDialect extends Oracle10gDialect {
    
    	public CustomOracle10gDialect() {
    		super();
    		registerColumnType(Types.LONGVARCHAR, "clob");
    	    registerColumnType(Types.LONGNVARCHAR, "clob");
    	    registerColumnType(Types.INTEGER, "number");
    	    registerColumnType(Types.BIT, "number");
    	    registerColumnType(Types.TIMESTAMP, "date");
    	}
    
    }
    The schema definition:
    Code:
    create table admin (
      id number(10) not null,
      version number(10) not null,
      firstname varchar2(255) not null,
      lastname varchar2(255) not null,
      login varchar2(50) not null,
      constraint admin_login_u1 unique (login),
      password varchar2(100) not null,
      password_salt varchar2(50),
      super_admin number(1) not null check (super_admin in (0, 1)),
      preference_admin number(1) not null check (preference_admin in (0, 1)),
      address varchar2(255),
      zip_code varchar2(10),
      city varchar2(255),
      country varchar2(255),
      email varchar2(255),
      profile clob,
      constraint admin_pk primary key (id)
    );
    create sequence sq_id_admin increment by 1 start with 1 nomaxvalue nocycle cache 10;
    create or replace trigger tr_id_inc_admin
    before insert
    on admin
    for each row
    declare
    begin
      select sq_id_admin.nextval into :new.id from dual;
    end;
    /
    Last edited by stephaneeybert; 11-16-12 at 05:39.

  2. #2
    Join Date
    Oct 2012
    Posts
    42
    I'm now trying to use a native value in the domain class mapping as in:

    <generator class="native" />

    But this raises an exception:

    Caused by: org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'org.springframework.dao.annotation.PersistenceExc eptionTranslationPostProcessor#0' defined in class path resource [spring-hibernate.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'sessionFactory' defined in class path resource [spring-hibernate.xml]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing sequence or table: hibernate_sequence
    As I understand it now, after reading some forum posts, I would need to specify in the Hibernate domain class mapping file, the name of the sequence used to increase the id primary key of the table. But I am reluctant to do that as it would make the Hibernate domain class mapping file Oracle specific. And I would like these domain class mapping files remain database server agnostic.

    I understand that Hibernate is expecting to find in the database a sequence named hibernate_sequence if no sequence name is explicitly specified in the domain class mapping file. Maybe I could use only one such sequence named hibernate_sequence for all of the tables of my schema, there are 117 tables. But for design considerations, I prefer to have a sequence for each table primary key as it allows for a continuity of the values in each primary key field.

    What puzzles me is why would Hibernate need to know about a sequence name, when there is a trigger that ties the sequence to the table at insert time. This seems like redundant and not needed from the part of Hibernate. In fact, I can manually insert a row from the sqlplus command line utility and it works fine, the id is produced by the trigger and sequence. Why does Hibernate need to know about the sequence name at all in this case ?
    Last edited by stephaneeybert; 11-16-12 at 05:51.

  3. #3
    Join Date
    Oct 2012
    Posts
    42
    Here is how I solved the issue:

    Code:
    		<id name="id" type="java.lang.Integer">
    			<column name="id" />
    			<generator class="native"><param name="sequence">sq_id_admin</param></generator>
    		</id>
    I can thus have only one domain class mapping file working on both MySql and Oracle database servers, this with one sequence per table in the case of Oracle.

  4. #4
    Join Date
    Oct 2012
    Posts
    42
    Not even running one test, having the following content in the beforeAnyTest method is enough to trigger the issue:
    Code:
      public void beforeAnyTest() throws Exception {
        navbar0 = navbarDao.makePersistent(navbar0);
        navbarDao.flush();
        navbarLanguage0 = navbarLanguageDao.makePersistent(navbarLanguage0);
    //    navbarLanguage1 = navbarLanguageDao.makePersistent(navbarLanguage1);
    //    navbarLanguage2 = navbarLanguageDao.makePersistent(navbarLanguage2);
    //    navbarLanguage3 = navbarLanguageDao.makePersistent(navbarLanguage3);
        navbarLanguageDao.flush();
      }
    I also noticed one surprising thing with the fisrt insert statement.

    When Hibernate tries inserting the navbar with the statement:
    Hibernate:
    insert
    into
    navbar
    (version, hide, id)
    values
    (?, ?, ?)
    2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [1] as [INTEGER] - 0
    2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [2] as [BIT] - false
    2012-11-17 15:14:43,600 TRACE [BasicBinder] binding parameter [3] as [INTEGER] - 517
    2012-11-17 15:14:43,600 DEBUG [AbstractBatcher] Executing batch size: 1
    2012-11-17 15:14:43,602 DEBUG [Expectations] success of batch update unknown: 0
    which is:
    insert into navbar (version, hide, id) values (0, 0, 517);
    Running the above sql statement on the command line sqlplus client shows:
    SQL> insert into navbar (version, hide, id) values (0, 0, 517);

    1 row created.

    SQL> select * from navbar;

    ID VERSION HIDE
    ---------- ---------- ----------
    503 0 0
    504 0 0
    505 0 0
    508 0 0
    509 0 0
    510 0 0
    511 0 0
    519 0 0

    8 rows selected.

    SQL>
    Every time, the value inserted (519) is greater by 2 than the value (517) seen by Hibernate.

    Oracle does the insert successfully and does not say anything, even if the id provided value (517) seen by Hibernate is not the one that ends up in the table (519).

    It is then no wonder that the child insert later fails:
    insert into navbar_language (version, language_code, navbar_id, id) values (0, 'en', 501, 583);

    Why is there this difference of 2 between the id value seen by Hibernate and the one inserted in the table ?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    There could be a trigger on the table to auto-populate the ID column.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2012
    Posts
    42
    Hello Anacedent,

    Thanks for the suggestion. You mean a trigger different than the one I already have ?

    As you can see in my first post on this thread the table already has the sequence and trigger:

    Code:
    create sequence sq_id_admin increment by 1 start with 1 nomaxvalue nocycle cache 10;
    create or replace trigger tr_id_inc_admin
    before insert
    on admin
    for each row
    declare
    begin
      select sq_id_admin.nextval into :new.id from dual;
    end;
    Or I you talking about something else ?

    Thanks.

  7. #7
    Join Date
    Oct 2012
    Posts
    42
    I solved the problem with the trigger id increment with an if statement as in:

    create or replace trigger tr_id_inc_admin
    before insert
    on admin
    for each row
    declare
    begin
    if (:new.id is null)
    then
    select sq_id_admin.nextval into :new.id from dual;
    end if;
    end;

    Indeed, Hibernate was first doing a select to get the next available id, and explicitly providing an id on the insert statement. In that case, the trigger was not to be used.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •