Hibernate select after insertion

Question

I'm having an issue with inserting new rows into my MySQL database. I'm using Spring Boot with Spring Boot Data JPA.

Since MySQL doesn't support sequences, I decided to try and make my own sequence generator table. This is basically what I've done.

I created a sequences table that uses an auto increment field (used as my id's for my tables).
Created a function, sequences_nextvalue() which inserts into the sequences table and returns the new auto incremented id.
I then created triggers on each table that get triggered before insertion and replaces the id field with the result of calling sequences_nextvalue().

So this is working fine when inserting new rows. I'm getting unique ids across all tables. The issue I'm having is with my JPA entities.

@Entity
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract class AbstractBaseClass {
  @Id
  private Integer id = -1;
  ...
}

@Entity
public class ConcreteClass1 extends AbstractBaseClass {
  ...
}


@Entity
public class ConcreteClass2 extends AbstractBaseClass {
  ...
}

I want to be able to query from the abstract base class so I've placed my @Id column in that class and used @Entity with InheritanceType.TABLE_PER_CLASS. I've also initialized the id to -1 since an id is required to call save() from my spring crud repository.

After calling the save() function of my Spring data CrudRepository, the -1 for id properly gets replaced by the MySQL trigger but the resulting entity returned by save() doesn't return with the new id but instead retains the -1. After looking at the SQL logs, a select statement is not being called after insertion to get the new id but instead the original entity is being returned.

Is it possible to force Hibnerate to re-select the entity after insertion to get the new id when you're not using @GeneratedValue?

Any help is greatly appreciated.

MySQL supports Sequence..https://stackoverflow.com/questions/26578313/how-do-i-create-a-sequence-in-mysql — Yogi, Dec 21 '17 at 10:02
the problem with `AUTO_INCREMENT` is that is scoped so that each table has it's own `AUTO_INCREMENT` field which allows for overlapping id's. I want to have unique id's across multiple tables meaning if I have id of 1 in `SOME_TABLE` there will not be an id of 1 in `ANOTHER_TABLE`. So basically I want multiple tables to share and `AUTO_INCREMENT` field. — Jason White, Dec 21 '17 at 12:29
I already have this working but since I'm not using `@GeneratedValue`, Hibernate is not performing the `SELECT` it normally does after `INSERT` so I'm not getting the newly generated id in the returned entity on `save()`. The id is stored properly in the database though. — Jason White, Dec 21 '17 at 12:32

score 0 · Accepted Answer · answered Dec 21 '17 at 16:22

Just wanted to provide an update on this question. Here is my solution.

Instead of creating MySQL TRIGGER's to replace the id on INSERT, I created a Hibernate IdentifierGenerator which executes a CallableStatement to get and return a new id.

My abstract base class now looks like this.

@Entity
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract class AbstractBaseClass {

  @Id
  @GenericGenerator(name="MyIdGenerator", strategy="com.sample.model.CustomIdGenerator" )
    @GeneratedValue(generator="MyIdGenerator" )
  private Integer id;
  ...

}

and my generator looks like this.

public class CustomIdGenerator implements IdentifierGenerator {

    private Logger log = LoggerFactory.getLogger(CustomIdGenerator.class);
    private static final String QUERY = "{? = call sequence_nextvalue()}";

    @Override
    public Serializable generate(SessionImplementor session, Object object) throws HibernateException {

        Integer id = null;
        try {
            Connection connection = session.connection();
            CallableStatement statement = connection.prepareCall(QUERY);
            statement.registerOutParameter(1, java.sql.Types.INTEGER);
            statement.execute();
            id = statement.getInt(1);
        } catch(SQLException e) {
            log.error("Error getting id", e);
            throw new HibernateException(e);
        }
        return id;
    }

}

And just for reference

The sequences table.

CREATE TABLE sequences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  thread_id INT NOT NULL,
  created DATETIME DEFAULT CURRENT_TIMESTAMP
) ^;

The sequence_nextvalue function

CREATE FUNCTION sequence_nextvalue()
RETURNS INTEGER
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE nextvalue INTEGER;
  INSERT INTO sequences (thread_id) VALUE (CONNECTION_ID());
  SELECT id FROM sequence_values ORDER BY created DESC LIMIT 1 INTO nextvalue;
  RETURN nextvalue;
END ^;

Hibernate select after insertion

1 Answers1