@GeneratedValue polymorphic abstract MySQL superclass

Question

In a Spring MVC application using Hibernate and MySQL, I have an abstract superclass BaseEntity that manages identifier values ​​for all other objects in the model. The id field uses @GeneratedValue . I run into a problem when my code tries to save any of the subclasses extending BaseEntity . The problem is with choosing GenerationType for @GeneratedValue .

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

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

I read a lot of posts about this on SO and google, but they either relate to other databases (not MySQL), or they don't deal with abstract superclasses. I cannot solve the problem using GenerationType.IDENTITY because I am using an abstract superclass to manage id fields for all objects 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 sample code for one of the objects 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 can't use @MappedSuperClass in this situation is because I need to have ManyToOne relationships that allow multiple subtypes to be used interchangeably. Take a look at the AccessLog class below as an example. It has actor_entity and a target_entity . There may be many types of actor actors and many types of targets, but they all inherit from BaseEntity . This inheritance allows the base accesslogs data table in MySQL to have only one actor_entity_id field and only one target_entity_id field instead of having multiple fields for each. When I change @Entity above BaseEntity to @MappedSuperClass , another error appears indicating that AccessLog cannot find BaseEntity . BaseEntity requires @Entity annotations for AccessLog 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 EDITING:

As suggested by JBNizet, 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 get 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 following two 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 to resolve this?

Answer 1

Since you are using the TABLE identifier generator, you need to create this table. If you are not using extended identifier generators , most likely you are going to use the MultipleHiLoPerTableGenerator .

MultipleHiLoPerTableGenerator can use one table for all table identifier generators.

My suggestion is to grab the ddl table from your integration tests if you use hbmddl to build a testing scheme. If you use flyways or test fluids, you can add the maven plugin to create the ddl schema.

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

Answer 2

What a mess ... AUTO_INCREMENT is a hidden MySQL sequence. The radical problem is that MySQL cannot insert and return PK at the same time, but Hibernate needs this, and INSERT needs to add a new Entity.

Problems you face:

• If Hibernate saves the new Entity, it will try immerdentelly to set the id to the new EntityBean. Therefore, hibernate must read which identifier the database will use before hibernation in order to save the new Tuple in the table.
• If you have several Servers that access the database, you should let hibernate session-factory decide to use the built-in sequence (AUTO-INCREMENT) or let hibernate decide ( GenerationType.AUTO / GenerationType.IDENTITY ) as a large open range of reserved PK ( Job DB-Architect). (We have about 20 servers for one database, so on a well-used table we use a PK distance of +100). If only one server has access to the GenerationType.TABLE database, it will be correct.

Sleep mode should calculate the next id on its own using max(*)+1 , but:

• What if two requests request max(*)+1 at the same time / with the same result? Right: The last INSERT attempt will fail.

So, you need to have the LAST_IDS table in the database that stores the latest PC tables. If you want to add it, you must take the following steps:

• Start reading optimization.
• SELECT MAX (address_id) FROM LAST_IDS
• keep maximum in java variable, that is: $ OldID.
• $ NewID = $ OldID + 1. (+100 in pessimistic lock)
• UPDATE LAST_IDS SET address_id = $newID WHERE address_id = $oldID ?
• Make a transaction with read optimization.
• If the commit is successful, save $newID until setID() in the HibernateBean that you want to save.
• Finally, let Hibernate invoke the insert.

This is the only way to find out.

BTW: Hibernate-Entity should only use inheritance if the database supports inheritance between tables such as PostgreSQL or Oracle .