java - JPA - Increment a numeric field through a sequence programmatically

Question

I have a JPA 2 web application (Struts 2, Hibernate 4 as JPA implementation only).

The current requirement is to add a (non-id) numeric sequential field, filled for certain rows only, to an existing entity. When inserting a new row, based on a certain condition, I need to set the new field to its highest value + 1 or to NULL.

For example:

ID     NEW_FIELD     DESCRIPTION
--------------------------------
1          1           bla bla
2                      bla bla       <--- unmatched: not needed here
3                      bla bla       <--- unmatched: not needed here
4          2           bla bla
5          3           bla bla
6          4           bla bla
7                      bla bla       <--- unmatched: not needed here
8          5           bla bla
9                      bla bla       <--- unmatched: not needed here
10         6           bla bla

In the good old SQL, it would be something like:

INSERT INTO myTable (
    id, 
    new_field, 
    description
) VALUES (
    myIdSequence.nextVal, 
    (CASE myCondition
        WHEN true 
        THEN myNewFieldSequence.nextVal
        ELSE NULL
    END),
    'Lorem Ipsum and so on....'
)

But I've no clue on how to achieve it with JPA 2.

I know I can define callbacks methods, but JSR-000317 Persistence Specification for Eval 2.0 Eval discourages some specific operations from inside it:

3.5 Entity Listeners and Callback Methods
<sub>- Lifecycle callbacks can invoke JNDI, JDBC, JMS, and enterprise beans.
- In general, the lifecycle method of a portable application should not invoke EntityManager or Query operations, access other entity instances, or modify relationships within the same persistence context.^[43] A lifecycle callback method may modify the non-relationship state of the entity on which it is invoked.</sub>

^[43] _{The semantics of such operations may be standardized in a future release of this specification.}

Summarizing, yes to JDBC (!) and EJB, no to EntityManager and other Entities.

---

EDIT

I'm trying to achieve the solution described in the answer from @anttix, but I'm encoutering some problem, so please correct me where I'm wrong.

Table

MyTable
-------------------------
ID            number (PK)
NEW_FIELD     number
DESCRIPTION   text

Main Entity

@Entity
@Table(name="MyTable")
public class MyEntity implements Serializable {

    @Id
    @SequenceGenerator(name="seq_id", sequenceName="seq_id", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq_id")
    private Long id;

    @OneToOne(cascade= CascadeType.PERSIST) 
    private FooSequence newField;

    private String description

    /* Getters and Setters */
}

Sub entity

@Entity
public class FooSequence {

    @Id
    @SequenceGenerator(name="seq_foo", sequenceName="seq_foo", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq_foo")
    private Long value;

    /* Getter and Setter */
}

DAO

myEntity.setNewField(new FooSequence());
entityManager.persist(myEntity);

Exception

Caused by: javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.

[...]

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: ERROR: relation "new_field" does not exist

[...]

Caused by: org.hibernate.exception.SQLGrammarException: ERROR: relation "new_field" does not exist

[...]

Caused by: org.postgresql.util.PSQLException: ERROR: relation "new_field" does not exist

What am I doing wrong ? I'm pretty new to JPA 2 and I've never used an entity not associated to a physical table... this approach is totally new to me.

I guess I need to put the @Column definition somewhere: how could JPA possibly know that the newField column (mapped through ImprovedNamingStrategy to new_field on the database) is retrieved through the value property of the FooSequence entity ?

Some pieces of the puzzle are missing.

---

EDIT

As asked in comments, this is the persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" 
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
                     http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="MyService"  transaction-type="JTA">

        <jta-data-source>java:jboss/datasources/myDS</jta-data-source>      

        <properties>             

            <property name="hibernate.dialect" 
                     value="org.hibernate.dialect.PostgreSQLDialect" />

            <property name="hibernate.ejb.naming_strategy" 
                     value="org.hibernate.cfg.ImprovedNamingStrategy"/>

            <property name="hibernate.query.substitutions" 
                     value="true 'Y', false 'N'"/>           

         <property name="hibernate.show_sql" value="true" />
         <property name="format_sql"         value="true" />
         <property name="use_sql_comments"   value="true" />

        </properties>

    </persistence-unit>
</persistence>

Answer 1

One possible solution is to use a separate entity with its own table that will encapsulate only the new field and have an OneToOne mapping with that entity. You will then instantiate the new entity only when you encounter an object that needs the additional sequence number. You can then use any generator strategy to populate it.

@Entity
public class FooSequence {
    @Id
    @GeneratedValue(...)
    private Long value;
}

@Entity 
public class Whatever {
    @OneToOne(...)
    private FooSequnce newColumn;
}

See:

• Hibernate JPA Sequence (non-Id)
• https://forum.hibernate.org/viewtopic.php?p=2405140

A gradle 1.11 runnable SSCCE (using Spring Boot):

src/main/java/JpaMultikeyDemo.java

import java.util.List;
import javax.persistence.*;
import lombok.Data;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Transactional;

@Configuration
@EnableTransactionManagement
@EnableAutoConfiguration
public class JpaMultikeyDemo {
    @Entity @Data
    public static class FooSequence {
        @Id @GeneratedValue private Long value;
    }

    @Entity @Data
    public static class FooEntity {
        @Id @GeneratedValue private Long id;
        @OneToOne           private FooSequence sequence;
    }

    @PersistenceContext
    EntityManager em;

    @Transactional
    public void runInserts() {
        // Create ten objects, half with a sequence value
        for(int i = 0; i < 10; i++) {
            FooEntity e1 = new FooEntity();
            if(i % 2 == 0) {
                FooSequence s1 = new FooSequence();
                em.persist(s1);
                e1.setSequence(s1);
            }
            em.persist(e1);
        }
    }

    public void showAll() {
        String q = "SELECT e FROM JpaMultikeyDemo$FooEntity e";
        for(FooEntity e: em.createQuery(q, FooEntity.class).getResultList())
            System.out.println(e);
    }

    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(JpaMultikeyDemo.class);
        context.getBean(JpaMultikeyDemo.class).runInserts();
        context.getBean(JpaMultikeyDemo.class).showAll();
        context.close();
    }
}

build.gradle

apply plugin: 'java'
defaultTasks 'execute'

repositories {
    mavenCentral()
    maven { url "http://repo.spring.io/libs-milestone" }
}

dependencies {
    compile "org.springframework.boot:spring-boot-starter-data-jpa:1.0.0.RC5"
    compile "org.projectlombok:lombok:1.12.6"
    compile "com.h2database:h2:1.3.175"
}

task execute(type:JavaExec) {
    main = "JpaMultikeyDemo"
    classpath = sourceSets.main.runtimeClasspath
}

See also: http://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/htmlsingle/#boot-features-configure-datasource