DB2 + JPA generated value

[Bram Pramono]

Hi all,

Does anyone here have experience with JPA/DB2 mapping?

I have tried many things but I cannot get it work properly. I am working with JPA and DB2 database for AS400.

Below are the 2 my mapping files possibilities I have tried:
- Mapping 1: using sequence

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                 version="1.0">
    <schema>BALGPRD</schema>
    <entity name="Application"
            class="com.centerparcs.contentservice.domain.Application">
        <table name="Apps"/>
        <attributes>
            <id name="id">
                <column name="id" insertable="false" updatable="false"/>
                <generated-value strategy="SEQUENCE" generator="SQAPP"/>
                <sequence-generator name="SQAPP" sequence-name="BALGPRD.SQAPP" initial-value="1"/>
            </id>
            <basic name="code">
                <column name="code"/>
            </basic>
            <basic name="name">
                <column name="name"/>
            </basic>
            <one-to-many name="applicationVersions" fetch="LAZY" target-entity="com.centerparcs.contentservice.domain.ApplicationVersion" mapped-by="application">
            </one-to-many>
            <one-to-one name="activeApplicationVersion" optional="true">
                <join-column name="actVerId"/>
            </one-to-one>
        </attributes>
    </entity>

    <entity name="ApplicationVersion"
            class="com.centerparcs.contentservice.domain.ApplicationVersion">
        <table name="AppVers"/>
        <attributes>
            <id name="id">
                <column name="id" insertable="false" updatable="false"/>
                <generated-value strategy="SEQUENCE" generator="SQAPPVER"/>
                <sequence-generator name="SQAPPVER" sequence-name="BALGPRD.SQAPPVER" initial-value="1"/>
            </id>
            <basic name="createdOn">
                <column name="created" insertable="false" updatable="false"/>
            </basic>
            <many-to-one name="application" fetch="LAZY">
                <join-column name="appId"/>
            </many-to-one>
            <one-to-many name="labelGroups" fetch="LAZY" target-entity="com.centerparcs.contentservice.domain.LabelGroup" mapped-by="applicationVersion">
            </one-to-many>
        </attributes>
    </entity>

</entity-mappings>
    

- Error from Mapping 1 :
* org.hibernate.exception.SQLGrammarException: could not get next sequence value
* java.sql.SQLException: [SQL0029] INTO clause missing from embedded statement.

- Mapping 2 : using Identity

  <?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                 version="1.0">
    <schema>BALGPRD</schema>
    <entity name="Application"
            class="com.centerparcs.contentservice.domain.Application">
        <table name="Apps"/>
        <attributes>
            <id name="id">
                <column name="id" insertable="false" updatable="false"/>
                <generated-value strategy="IDENTITY"/>
            </id>
            <basic name="code">
                <column name="code"/>
            </basic>
            <basic name="name">
                <column name="name"/>
            </basic>
            <one-to-many name="applicationVersions" fetch="LAZY" target-entity="com.centerparcs.contentservice.domain.ApplicationVersion" mapped-by="application">
            </one-to-many>
            <one-to-one name="activeApplicationVersion" optional="true">
                <join-column name="actVerId"/>
            </one-to-one>
        </attributes>
    </entity>

    <entity name="ApplicationVersion"
            class="com.centerparcs.contentservice.domain.ApplicationVersion">
        <table name="AppVers"/>
        <attributes>
            <id name="id">
                <column name="id" insertable="false" updatable="false"/>
                <generated-value strategy="IDENTITY"/>
            </id>
            <basic name="createdOn">
                <column name="created" insertable="false" updatable="false"/>
            </basic>
            <many-to-one name="application" fetch="LAZY">
                <join-column name="appId"/>
            </many-to-one>
            <one-to-many name="labelGroups" fetch="LAZY" target-entity="com.centerparcs.contentservice.domain.LabelGroup" mapped-by="applicationVersion">
            </one-to-many>
        </attributes>
    </entity>
</entity-mappings>
   

- Error from Mapping 2:
* util.JDBCExceptionReporter [SQL0798] Value cannot be specified for GENERATED ALWAYS column ID.
* org.hibernate.util.JDBCExceptionReporter - [SQL0798] Value cannot be specified for GENERATED ALWAYS column ID.

My question is has anyone worked with JPA / DB2? If you do, how did you configure your database table and jpa? Did you use sequence or identity?

Please help.

Thank you in advance.

Cheers,

Bram

[Nigel Kibble]

Hi Bram,

I've got some experience of using JPA with the AS/400, which I hope will be of some help.

The choice between SEQUENCE and IDENTIFIER comes down to whether you can modify the existing table or not.
If you are in a position where you can alter the table, then you can add an identifier column to it, like so:

alter TABLE nkibble/src        
  add  RCFKID BIGINT NOT NULL 
   GENERATED ALWAYS AS IDENTITY
   (START WITH 1               
   INCREMENT BY 1              
   CYCLE nocache)              

then, in your java entity have the following:

@Id @Column(name="RCFKID") @GeneratedValue(strategy=GenerationType.IDENTITY)
	private Long id;

If, on the other hand, you're not in a position to alter the table, you can create an SQL sequence to apply an identifier for you.
On the AS/400 you'll need to enter in:

CREATE SEQUENCE NKIBBLE/SRC
START WITH 500             
INCREMENT BY 1             
MAXVALUE 999999999         
CYCLE                      
NOCACHE       

And then in your java entity have the following:

@Id @Column(name="RCFKID") @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="NKIBBLE.SRC")

You'll probably want a separate sequence for each table.

Hope this helps.

Nigel.