I can think of several options to solve this problem:
1. Use a migration library
This is my favorite option, because I can’t imagine Hibernate applying automatic changes in production.
You can adopt a library as Liquibase or Flyway to manage scripts for changes to different versions of the database. These libraries take care of or at least facilitate updates for various schemas or different banks, just configure them properly.
I implemented a project a few weeks ago with Flyway and it was very easy to update several schemas in Postgresql. In this case, the schemas were created automatically and I kept a list of them on public
to then make the update.
I’ll put a summary of the implementation I did using the Spring Framework and Flyway. The following class uses two instances of Flyway to manage the schema public
and the specific schemas.
@Service
public class DatabaseMigration {
@Autowired @Qualifier("public") Flyway publicFlyway;
@Autowired @Qualifier("specific") Flyway specificFlyway;
@Autowired SchemaService schemaService;
@PostConstruct
void migrate() {
migratePublic();
migrateAllSpecificSchemas();
}
/**
* Updates public main schema when application is starting
*/
void migratePublic() {
publicFlyway.migrate();
}
/**
* Updates all schemas when application is starting
*/
void migrateAllSpecificSchemas() {
List<String> schemas = schemaService.listAll();
for (String schema : schemas) {
specificFlyway.setSchemas(schema);
specificFlyway.migrate();
}
}
/**
* Used when creating a new schema for a new client
*/
public void migrateSpecificSchema(String schema) {
specificFlyway.setSchemas(schema);
specificFlyway.migrate();
}
}
In my project, which follows the Maven structure, I store the public schema migration scripts and specific schemas in the following directory structure:
And configure the Flyway Beans as follows:
@Bean @Qualifier("public")
Flyway getFlywayPublicInstance(DataSource dataSource) {
Flyway f = new Flyway();
f.setDataSource(dataSource);
f.setLocations(publicMigrationsDirectory);
f.setOutOfOrder(true);
return f;
}
@Bean @Qualifier("specific")
Flyway getFlywaySpecificInstance(DataSource dataSource) {
Flyway f = new Flyway();
f.setDataSource(dataSource);
f.setLocations(specificMigrationsDirectory);
f.setOutOfOrder(true);
return f;
}
2. Initialize the hibernate in each Schema
At the beginning of the application, you can manually initialize Hibernate once for each schema. I’ve never done it, but it should work well.
I researched an example of how to do this and found this:
Configuration config = new Configuration().configure();
config.setProperty("hbm2ddl.auto", "update");
SessionFactory sessionFactory = config.buildSessionFactory();
sessionFactory = hibernateConfiguration.buildSessionFactory();
After this manual startup you can discard the session and use the multi-tenant configuration normally.
3. Generate scripts by Hibernate using SchemaExport
With class SchemaExport
you can generate the necessary DDL and then apply it to the different schemas.
There’s a example of exporting DDL in this Soen response. Just do it for each schema.
4. Apply changes by Hibernate using SchemaExport
With class SchemaExport
you can also force the bank update via code.
An example, which may be a little outdated, is at the end of this page:
AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
config.configure();
new SchemaExport(config).create(true, true);
Just do it for each schema. In case you need to pay attention to the method and the parameters used in SchemaExport
. Some commands can recreate or delete database elements, others can generate a file with the script, others only update the database.
Thanks for the reply @utluiz, would be able to pass me some article that you have used regarding the use of Flyway or Liquibase to perform this type of operation?
– Giancarlo Abel Giulian
Won’t solution 2, 3 and 4 go out deleting the data? I made a test here to generate the scripts with Schemaupdate, everything went right, updated in the database when the user informed the login and released a . sql, so I thought it best to have 1°option, because it has more data control. @utluiz
– Giancarlo Abel Giulian
@Giancarlogiulian Options 2, 3 and 4 will only delete the data if you use the option
drop-create
hibernate. Using the optionupdate
it won’t be a problem.– utluiz
@Giancarlogiulian I updated the first part of the answer with some code examples. I hope it helps.
– utluiz
Thank you very much, I had set the property to update, but I was deleting the tables and creating again using the Schemaexport class. And thank you for the addition to Liquibase and Flyway that you didn’t know. Great job for you (;
– Giancarlo Abel Giulian