Create multiple databases with JPA

Hi guys!
I’m developing a web app using JPA and MySQL. In this app, users can create new databases. I have about 200 tables, and thus about 200 entities. Could any one tell me how to automatically create multiple PersistenceUnits each time users create new database?

I don’t know what you mean by “automatically”. Switching databases is “not that easy”. Maybe the multi-tenancy feature is what you are looking for? With a MultiTenantConnectionProvider you can setup connections to target the appropriate database by executing e.g. use mydb1 to switch the database. Also see the documentation: Hibernate ORM 5.5.6.Final User Guide

My app is an accounting app, so users want to create multiple databases: year2019, year2020, year2021… with the same tables structures. I use MYBATIS to run sql files each time users create new databases:

try (Connection conn = ConnectionUtils.getConnection(databaseName, "?createDatabaseIfNotExist=true&serverTimezone=UTC")) {
            //creating database
            String createSQLPath = getClass().getClassLoader().getResource("sql/create.sql").getFile();
            ScriptRunner runner = new ScriptRunner(conn);
            runner.runScript(new FileReader(new File(createSQLPath)));
            //altering database
            String alterSQLPath = getClass().getClassLoader().getResource("sql/alter.sql").getFile();
            runner.runScript(new FileReader(new File(alterSQLPath)));
            //insert default data to database
            String insertSQLPath = getClass().getClassLoader().getResource("sql/insert.sql").getFile();
            BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(insertSQLPath), StandardCharsets.UTF_8));
            runner.runScript(in);

        } catch (SQLException | IOException e) {
            LOGGER.error("Could not create database", e);
        }

Now, how to map tables to entities and to create PersistanceUnit for each new database. As I understand, inside persistence.xml we need to specify database name by property javax.persistence.jdbc.url, for example:

<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/ibigtime?zeroDateTimeBehavior=CONVERT_TO_NULL"/>

I want to make this assignment programmatically. I can’t manually edit persistance.xml.

You can either use the multi-tenancy feature that I mentioned before or you build a dedicated EntityManagerFactory with overridden properties with Persistence.createEntityManagerFactory