Explain behaviors in mapping auto incremented composite id sequence with Hibernate

Question

I have a table

CREATE TABLE `SomeEntity` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `subid` int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (`id`,`subid`),

I have a entity class with an auto increment field in it.I want to read auto increment id assigned to it when it gets persisted

Annotations on getter are as below

  private long id;
   private int subid;
  @Id
  @GeneratedValue **//How do i correct this to have multiple rows with same id and different subid**
  @Column(name = "id")
  public long getId() {
    return id;
  }

  @Id
  @Column(name = "subid")
  public int getSubid() {
    return subid;
  }

I want to have entities as

id 1 subid 0 
id 1 subid 1
id 1 subid 2
id 2 subid 0

subid is default 0 in database and i am incrementing it programmatically on updates to that row. I tried the solution as in this SO post JPA - Returning an auto generated id after persist()

 @Transactional
  @Override
  public void daoSaveEntity(SomeEntity entity) {
    entityManager.persist(entity);
  }

Now outside this transaction I am trying to get the auto increment id assigned

      @Override
      public long serviceSaveEntity(SomeEntity entity) {
        dao.daoSaveEntity(entity);
        return entity.getId();
      }

I am calling this from a web service

  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Response createEntity(SomeEntity entity) {

The update method is as below

 @Transactional
  public void updateReportJob(SomeEntity someEntity) {

    Query query =
        entityManager
.createQuery("UPDATE SomeEntity SET state=:newState WHERE id = :id");
    query.setParameter("newState","PASSIVE");
    query.setParameter("id", id);
    query.executeUpdate();
    double rand = Math.random();
    int i = (int) (rand * 300);
    try {
      Thread.sleep(i);  //only to simulate concurrency issues
    } catch (InterruptedException e) {
      e.printStackTrace();
    }
    List<Integer> resList =
        entityManager.createQuery("select max(subid) from SomeEntity WHERE id = :id")
            .setParameter("id", jobId).getResultList();
    // Increment old subid by 1
    int subid = resList.get(0);
    SomeEntity.setsubid(subid + 1);
    SomeEntity.setState("ACTIVE");
    // entityManager.merge(SomeEntity);
    entityManager.persist(SomeEntity);
  }

i send N concurrent updates from N threads for entity with Id 1 and few other properties as below

SomeEnity entity = new SomeEntity();

 entity.setId(1);
  long num = Thread.currentThread().getId();
  entity.setFieldOne("FieldOne" + num);
  entity.setFieldTwo("" + i);
  entity.setFieldThree("" + j);
  i++;
  j++;

Case 1 with With @Id on id and @Id annotation on subid and `entityManager.persist' in update When i ran with 300 threads some failed with connection exception "too many connections" The databse state is

   id 1 subid 0 
   id 1 subid 1
   id 1 subid 2
   ..  ....
   id 1 subid 150

the subid is always incremental ,the race condition is only that which one will be ACTIVE is undefined because of race condition

Case 2 with With @Id on id and @Id annotation on subid and `entityManager.merge' in update

id 1 subid 0 id 1 subid 0 id 2 subid 0 .. .... id 151 subid 0 (Perhaps just a co-incidence that one more thread than case 1 was successful? )

Case 3 With @GeneratedValue and @Id on id and **NO @Id annotation on subid and entityManager.persist in update** exception -- Detached entity passed to persist

Case 3 With @GeneratedValue and @Id on id and **NO @Id annotation on subid and entityManager.merge in update** if update is run sequentially the database state is

id 1 subid 0

after next update

id 1 subid 1

after each update same row is updated (leading to only one row at a time)

id 1 subid 2

Case 4 same as Case 3 with Concurrent updates if run concurrently(with 300 threads) i get the below exception

 org.hibernate.HibernateException: More than one row with the given identifier was found: 1

database state is id 1 subid 2 (Only one thread would have been successful but because of race condition updated subid from 0 to 2 )

Case 5 With @GeneratedValue and @Id on id and @Id annotation on subid
Create also fails with subid org.hibernate.PropertyAccessException: IllegalArgumentException occurred while calling setter of SomeEntity.id

Please explain the causes.From the javadoc of methods i know that

persist - Make an instance managed and persistent.

merge - Merge the state of the given entity into the current persistence context.

My question is more towards how hibernate manages the annotations. Why is there be a detached entity exception in case 3 when the session is not closed yet? Why is there a IllegalArgumentException in case 5 ?

I am using hibernate 3.6 mysql 5 and Spring 4 Also please suggest a way achieve such incremental id and subid.(Using custom SelectGenerator ,with a demo implementation or any other way without doing a column concat)

Answer 1

Since the id field is already unique and auto incremented, you don't need a composite id in this case so your entity can look like this:

@Id
@Column(name = "id")
public long getId() {
    return id;
}

@Column(name = "subid")
public int getSubid() {
    return subid;
}

The entity can be fetched by id using the entity manager:

entityManager.find(MyEntity.class, entityId); 

or you could fetch the entity using a query that takes both the id and the subid:

MyEntity myEntity = entityManager.createTypeQuery("select me from MyEntity where id = :id and subid = :subid", MyEntity.class)
    .setParameter("id", entityId) 
    .setParameter("subid", entitySubId) 
    .getSingleResult();

Hibernate also has a SelectGenerator that can fetch the id from a database column, which is useful when the database generates the id using a trigger.

Unfortunately, it doesn't work with composite ids, so you wither wrote your own extended SelectGenerator or use a single string id_sub_id column that combines the id and sub-id into a single VARCHAR column:

'1-0'
'1-1'
'2-0'
'2-1' 

You have to write a database trigger to update the two columns using a database specific stored procedure and aggregate the two columns into the VARCHAR one. You then map the aggregated column using the standard SelectGenerator to a String field:

@Id
@Column(name = "id_sub_id")
@GeneratedValue( strategy = "trigger" )
@GenericGenerator( 
    name="trigger", strategy="org.hibernate.id.SelectGenerator",
    parameters = {
        @Parameter( name="keys", value="id_sub_id" )
    }
)
public String getId() {
    return id;
}

Answer 2

Let's say i have some books with ID and Version.An ID belong to a book which can have many updated versions with latest one being the current version which is what will be mostly queried. What would be a better approach? Should i use a one-to-many mapping

See the design outline below to understand how it should be correctly mapped:

Saving book:

@Transactional
public void saveBook(Book book) {
    em.persist(book);
}

Saving book version:

@Transactional
public void saveBookVersion(BookVersion bookVersion) {
    Book book = em.find(Book.class, bookVersion.getBook().getId());
    bookVersion.setBook(book);
    book.setLatestBookVersion(bookVersion);
    em.persist(bookVersion);
}

Retrieve latest book version:

@Transactional(readOnly=true)
public Book getLatestBookVersion(Long bookId) {
   // it is enough if lastestBookVersion is loaded eagerly
   return em.find(Book.class, bookId);
}

Table schema mapping of the logical model above:

Answer 3

_{Just because no one mentioned this.}

I have a table

CREATE TABLE `SomeEntity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subid` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`subid`),

I have a entity class with an auto increment field in it.

[..]

subid is default 0 in database and i am incrementing it programmatically on updates to that row.

To me this sounds a lot like versioning.

There is actually an annotation that will do exactly what you want, without you having to write any code:

@Version

A versioned entity is marked with the @Version annotation, as illustrated in the following code snippet:

public class User {
    @ID Integer id;
    @Version Integer version;
}

and its corresponding database schema has a version column, such as that created by the following SQL statement:

CREATE TABLE `User` (
    `id` NUMBER NOT NULL, 
    `version` NUMBER,
    PRIMARY KEY (`id`)
);

The version attribute can be an int, short, long, or timestamp. It is incremented when a transaction successfully commits. This results in an SQL operation such as the following:

UPDATE User SET ..., version = version + 1
WHERE id = ? AND version = readVersion

source: blogs.oracle.com ^{_{(emphasis mine)}}

I modified the example a bit. I recommend you to use the box types Integer, Long etc for database fields. Even id, which is NOT NULL, will be null before you save the entity for the first time. I find that using box types makes it explicit that these fields can and will be null.