CODEWITHSUNDEEP
mysqlsql-serverspring-boothibernatejpa
Alchnemesis
Alchnemesis

Reputation: 479

Override primary key generator strategy in Spring Boot when deployed to MySQL

In my application I want to support more databases to which it can be loaded, and for MS SQL Server I have set the identity generator to SEQUENCE

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;

Because it can be deployed to MySQL as well, I need somehow to change the generator to IDENTITY since SEQUENCE is not available for MySQL, is there a way to do it programatically ?

Upvotes: 0

Views: 599

Answers (1)

crizzis
crizzis

Reputation: 10716

The simplest solution would probably be to create your schema without the help of Hibernate (e.g. manage your schema using a tool like Liquibase) and use the DB capabilities for assigning the ids. In your specific scenario, you could probably use strategy = IDENTITY for both DBs (just so that Hibernate delegates the id column management to the DB) and then create an INSTEAD OF INSERT trigger for SQL Server. I'm not sure about performance, though.

If you still want Hibernate to do the job - it's not going to be super easy, but I can think of one option you could try:

  1. Use the @GeneratedValue.name property with your id field:
@Id
@GeneratedValue(name = "my-entity-generator")
private Long id;
  1. Declare two versions of my-entity-generator for the two databases. You need to put them on something that is optional for the entity scan. @GenericGenerator can be put on a package, for instance, so you can use two empty packages and declare the generators corresponding to the two strategies in their respective package-infos (never tried it with Spring, though, so I'm not sure if that will get picked up). You could probably also use two dummy @MappedSuperclasses in two different packages.

So, let's say you end up with one mapped superclass called com.example.mssql.Generators:

@MappedSuperclass
@GenericGenerator(name = "my-entity-generator", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator", ...) 
public abstract class Generators {}

And another one called com.example.mysql.Generators:

@MappedSuperclass
@GenericGenerator(name = "my-entity-generator", strategy = "org.hibernate.id.IdentityGenerator") 
public abstract class Generators {}
  1. Conditionally include the two packages in the entity scan:
@Profile("mysql") // or @ConditionalOnProperty, or your own custom condition
@Configuration
@EntityScan(basePackages = "com.example.mysql")
public class MySqlConfig {}

@Profile("mssql") // or @ConditionalOnProperty, or your own custom condition
@Configuration
@EntityScan(basePackages = "com.example.mssql")
public class MsSqlConfig {}

(of course, for this to work, you need an unconditional @EntityScan on top of e.g. your application class that does not cover the two packages)

Another possible option could be to only have one generator declaration but instead use your own custom generator implementation that detects the DB and then delegates to either an IdentityGenerator or a SequenceStyleGenerator. Thus, you have one option that involves configuration magic and another that involves heavy coding.

(finally, I think you could also use Hibernate mapping XML files and - again - conditionally include them in your mapping, but it's an ancient technique and the documentation is not great)

Upvotes: 1

Related Questions