@GeneratedValue polymorphic abstract superclass over MySQL

Question

In a Spring MVC application using Hibernate and MySQL, I have an abstract superclass BaseEntity that manages the values of the IDs for all the other entities in the model. The id field uses @GeneratedValue. I am encountering a problem whenever my code tries to save any of the subclasses that extend BaseEntity. The problem comes with the choice of GenerationType for the @GeneratedValue.

At every place in my code where a subclass of BaseEntity tries to save to the underlying MySQL database, I get the following error:

ERROR SqlExceptionHelper - Table 'docbd.hibernate_sequences' doesn't exist  

I have read many postings about this on SO and on google, but they either deal with other databases (not MySQL) or they do not deal with abstract superclasses. I cannot solve the problem by using GenerationType.IDENTITY because I am using an abstract superclass to manage id fields for all entities in the model. Similarly, I cannot use GenerationType.SEQUENCE because MySQL does not support sequences.

So how do I solve this problem?

Here is the code for BaseEntity.java:

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    protected Integer id;

    public void setId(Integer id) {this.id = id;}
    public Integer getId() {return id;}

    public boolean isNew() {return (this.id == null);}

}

Here is an example of the code for one of the entities that extends BaseEntity:

@Entity
@Table(name = "ccd")
public class CCD extends BaseEntity{
    //other stuff
}

Here is the DDL:

CREATE TABLE IF NOT EXISTS ccd(
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  #other stuff
)engine=InnoDB;SHOW WARNINGS;

Here is the JPQL code in the DAO:

    @Override
    @Transactional
    public void saveCCD(CCD ccd) {
    if (ccd.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist CCD ]]]]]]]]]]]]]]]]]]]]");
        this.em.persist(ccd);
        this.em.flush();
    }
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge CCD [[[[[[[[[[[[[[[[[[[[[");
        this.em.merge(ccd);
        this.em.flush();
    }
} 

---

EDIT:

The reason I cannot use @MappedSuperClass in this situation is that I need to have ManyToOne relationships that allow for multiple subtypes to be used interchangeably. Look at the AccessLog class below as an example. It has an actor_entity and a target_entity. There can be many types of actor entities and many types of target entities, but they all inherit from BaseEntity. This inheritance enables the underlying accesslogs data table in MySQL to just have one actor_entity_id field and just one target_entity_id field instead of having to have several fields for each. When I change @Entity above BaseEntity to @MappedSuperClass, a different error gets thrown indicating that AccessLog cannot find BaseEntity. BaseEntity needs @Entity annotation in order for AccessLog to have polymorphic properties.

@Entity
@Table(name = "accesslogs")
public class AccessLog extends BaseEntity{

    @ManyToOne
    @JoinColumn(name = "actorentity_id")
    private BaseEntity actor_entity;

    @ManyToOne
    @JoinColumn(name = "targetentity_id")
    private BaseEntity target_entity;

    @Column(name="action_code")
    private String action;
    //getters, setters, & other stuff
}

---

SECOND EDIT:

As per JBNizet's suggestion, I created a hibernate_sequences table as follows:

CREATE TABLE IF NOT EXISTS hibernate_sequences(
  sequence_next_hi_value int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)engine=InnoDB;SHOW WARNINGS;

But now I am getting the following error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'sequence_name' in 'where clause'  

Here is the hibernate sql causing the error, followed by the next 2 lines of the stack trace:

Hibernate: select sequence_next_hi_value from hibernate_sequences where sequence_name = 'BaseEntity' for update
ERROR MultipleHiLoPerTableGenerator - HHH000351: Could not read or init a hi value
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'sequence_name' in 'where clause'

How do I resolve this?

Answer 1

What a mess... AUTO_INCREMENT is MySQL's hidden sequence. The radical problem is that MySQL can not insert and return the PK at the same time, but Hibernate need this while INSERTing a new Entity.

The Problems you run into:

1. If Hibernate save a new Entity, he try to immerdentelly set the id to the new EntityBean. Therefore hibernate must read what ID will the Database use before hibernate save the new Tuple to the Table.
2. If you have multiple Servers who access the database, you shall let hibernate's session-factory decide to use the built-in sequence(AUTO-INCREMENT) or let hibernate decide (GenerationType.AUTO/GenerationType.IDENTITY) how large the open range of reserved PK's is (Job of a DB-Architect). (We have about 20 servers to one Database, so on a good-used table we use a PK-distance of +100). If only one server have access to the database GenerationType.TABLE shall be correct.

Hibernate must calculate the next id by yourself using max(*)+1 but:

• What if two requests ask for max(*)+1 at the same time/with the same result? Right: The last try to insert will fail.

So you need to have a Table LAST_IDS in the database who stores the last Table-PK's. If you like to add one, you must do this steps:

1. Start read-optimistic transaction.
2. SELECT MAX(address_id) FROM LAST_IDS
3. store the maximum in a java-variable i.e.: $OldID.
4. $NewID = $OldID + 1. (+100 in pessimistic-lock)
5. UPDATE LAST_IDS SET address_id= $newID WHERE address_id= $oldID?
6. commit the read-optimistic transaction.
7. if commit was successfull, store $newID to setID() in the HibernateBean you like to save.
8. Finally let Hibernate call the insert.

This is the only way i know.

BTW: Hibernate-Entitys shall only use inheritance if the Database support inheritance between tables like PostgreSQL or Oracle.

Answer 2

Because you use the TABLE identifier generator you need to have that table created. If you are not using the enhanced identifier generators, chances are you are going to use the MultipleHiLoPerTableGenerator.

The MultipleHiLoPerTableGenerator can use one table for all table identifier generators.

My suggestion is to grab the table ddl from your integration tests, in case you use hbmddl to build the test schema. If you use flyway or liquibase for testing, you can add a maven plugin to generate the ddl schema.

Once you have the schema, you need to take the exact create table command and make add it to your MySQL database.