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

I try to use multi-tenancy, but can’t unwrap EntityManagerFactory to SessionFactory. Here is my code:

public abstract class Dao<T extends Entity> {

    @PersistenceUnit
    protected EntityManagerFactory emf;

    protected EntityManager getEntityManager(String multitenancyIdentifier) {
        SessionFactory sf = emf.unwrap(SessionFactory.class);
        final MultitenancyResolver tenantResolver = (MultitenancyResolver) ((SessionFactoryImplementor) sf).getCurrentTenantIdentifierResolver();
        tenantResolver.setTenantIdentifier(multitenancyIdentifier);
        return emf.createEntityManager();
    }

    public Optional<T> findById(T entity, String multitenancyIdentifier) {
        return (Optional<T>) Optional
                .ofNullable(getEntityManager(multitenancyIdentifier).find(entity.getClass(), entity.getId()));
    }

    public T save(T entity, String multitenancyIdentifier) {
        getEntityManager(multitenancyIdentifier).persist(entity);
        return entity;
    }

    public T update(T entity, String multitenancyIdentifier) {
        getEntityManager(multitenancyIdentifier).merge(entity);
        return entity;
    }

    public void remove(T entity, String multitenancyIdentifier) {
        getEntityManager(multitenancyIdentifier).remove(entity);
    }
}

And the exception: Provider does not support unwrapping javax.persistence.EntityManagerFactory to org.hibernate.SessionFactory

Are you using Hibernate as your persistence provider?

Yes. Here is the pom file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="testjpa" transaction-type="JTA">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <class>com.mycompany.testjpa.entities.Account</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetailpurchase</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetailtax</class>
    <class>com.mycompany.testjpa.entities.Faadjustment</class>
    <class>com.mycompany.testjpa.entities.Contract</class>
    <class>com.mycompany.testjpa.entities.Autobusiness</class>
    <class>com.mycompany.testjpa.entities.Butransferdetail</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetail</class>
    <class>com.mycompany.testjpa.entities.Fixedasset</class>
    <class>com.mycompany.testjpa.entities.Frfunctionlist</class>
    <class>com.mycompany.testjpa.entities.Exportxml</class>
    <class>com.mycompany.testjpa.entities.Sutransfer</class>
    <class>com.mycompany.testjpa.entities.Reftype</class>
    <class>com.mycompany.testjpa.entities.Buvoucherlist</class>
    <class>com.mycompany.testjpa.entities.Openinginventoryentry</class>
    <class>com.mycompany.testjpa.entities.Feature</class>
    <class>com.mycompany.testjpa.entities.Buplanwithdraw</class>
    <class>com.mycompany.testjpa.entities.Autoid</class>
    <class>com.mycompany.testjpa.entities.Butransferdetailpurchase</class>
    <class>com.mycompany.testjpa.entities.Taxtype</class>
    <class>com.mycompany.testjpa.entities.Budgetsource</class>
    <class>com.mycompany.testjpa.entities.Bawithdraw</class>
    <class>com.mycompany.testjpa.entities.Project</class>
    <class>com.mycompany.testjpa.entities.Subsystem</class>
    <class>com.mycompany.testjpa.entities.Userpermision</class>
    <class>com.mycompany.testjpa.entities.Buplanadjustmentdetail</class>
    <class>com.mycompany.testjpa.entities.Department</class>
    <class>com.mycompany.testjpa.entities.Employeetype</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetailparallel</class>
    <class>com.mycompany.testjpa.entities.FrtemplateCustom</class>
    <class>com.mycompany.testjpa.entities.Reportlist</class>
    <class>com.mycompany.testjpa.entities.Supplyledger</class>
    <class>com.mycompany.testjpa.entities.Cashwithdrawtype</class>
    <class>com.mycompany.testjpa.entities.Glvoucherdetail</class>
    <class>com.mycompany.testjpa.entities.Exportxmlbctc</class>
    <class>com.mycompany.testjpa.entities.Budgetsourcemappingtoexport</class>
    <class>com.mycompany.testjpa.entities.Voucherlist</class>
    <class>com.mycompany.testjpa.entities.Fadepreciation</class>
    <class>com.mycompany.testjpa.entities.Invoiceformnumbercategory</class>
    <class>com.mycompany.testjpa.entities.Projectactivityallocationconfig</class>
    <class>com.mycompany.testjpa.entities.Bubudgetreservedetail</class>
    <class>com.mycompany.testjpa.entities.Userfeaturepermision</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Ininwardoutwarddetail</class>
    <class>com.mycompany.testjpa.entities.Userprofile</class>
    <class>com.mycompany.testjpa.entities.Dboption</class>
    <class>com.mycompany.testjpa.entities.Accountcategory</class>
    <class>com.mycompany.testjpa.entities.Careceipt</class>
    <class>com.mycompany.testjpa.entities.Bank</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetail</class>
    <class>com.mycompany.testjpa.entities.FrformulaDefault</class>
    <class>com.mycompany.testjpa.entities.Budgetchapter</class>
    <class>com.mycompany.testjpa.entities.Projectexpense</class>
    <class>com.mycompany.testjpa.entities.Faincrementdecrement</class>
    <class>com.mycompany.testjpa.entities.Faadjustmentdetailfa</class>
    <class>com.mycompany.testjpa.entities.Dbinfo</class>
    <class>com.mycompany.testjpa.entities.Accountingobjectcategory</class>
    <class>com.mycompany.testjpa.entities.Capayment</class>
    <class>com.mycompany.testjpa.entities.Inventoryitem</class>
    <class>com.mycompany.testjpa.entities.Faminutesinventoryfixedasset</class>
    <class>com.mycompany.testjpa.entities.Fixedassetdetailaccessory</class>
    <class>com.mycompany.testjpa.entities.Bubudgetreserve</class>
    <class>com.mycompany.testjpa.entities.FrtemplateDefault</class>
    <class>com.mycompany.testjpa.entities.Purchasepurpose</class>
    <class>com.mycompany.testjpa.entities.Contractcategory</class>
    <class>com.mycompany.testjpa.entities.Frreportconfiglist</class>
    <class>com.mycompany.testjpa.entities.Careceiptdetail</class>
    <class>com.mycompany.testjpa.entities.Babanktransferdetail</class>
    <class>com.mycompany.testjpa.entities.Budgetprovidence</class>
    <class>com.mycompany.testjpa.entities.Invoiceformnumber</class>
    <class>com.mycompany.testjpa.entities.Currency</class>
    <class>com.mycompany.testjpa.entities.Bucommitmentadjustment</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Faincrementdecrementdetail</class>
    <class>com.mycompany.testjpa.entities.Fixedassetactivity</class>
    <class>com.mycompany.testjpa.entities.Suincrementdecrementdetail</class>
    <class>com.mycompany.testjpa.entities.Openingcommitmentdetail</class>
    <class>com.mycompany.testjpa.entities.Babanktransfer</class>
    <class>com.mycompany.testjpa.entities.Fixedassetcategory</class>
    <class>com.mycompany.testjpa.entities.Taxitem</class>
    <class>com.mycompany.testjpa.entities.Buvoucherlistdetailtransfer</class>
    <class>com.mycompany.testjpa.entities.Openingfixedassetentry</class>
    <class>com.mycompany.testjpa.entities.Butransferdetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Badepositdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Budgetkinditem</class>
    <class>com.mycompany.testjpa.entities.Errorlog</class>
    <class>com.mycompany.testjpa.entities.Glvoucher</class>
    <class>com.mycompany.testjpa.entities.Frcolumnlist</class>
    <class>com.mycompany.testjpa.entities.Fixedassetledger</class>
    <class>com.mycompany.testjpa.entities.Budgetitem</class>
    <class>com.mycompany.testjpa.entities.Reportparameters</class>
    <class>com.mycompany.testjpa.entities.Budgetsourcecategory</class>
    <class>com.mycompany.testjpa.entities.Fadepreciationdetail</class>
    <class>com.mycompany.testjpa.entities.Glvoucherdetailtax</class>
    <class>com.mycompany.testjpa.entities.Buplanreceiptdetail</class>
    <class>com.mycompany.testjpa.entities.Buvoucherlistdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Badeposit</class>
    <class>com.mycompany.testjpa.entities.Reftypecategory</class>
    <class>com.mycompany.testjpa.entities.Careceiptdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Bucommitmentrequest</class>
    <class>com.mycompany.testjpa.entities.Openingsupplyentry</class>
    <class>com.mycompany.testjpa.entities.Inventoryitemcategory</class>
    <class>com.mycompany.testjpa.entities.Salaryscale</class>
    <class>com.mycompany.testjpa.entities.Inventoryledger</class>
    <class>com.mycompany.testjpa.entities.Buplanadjustment</class>
    <class>com.mycompany.testjpa.entities.Fundstructure</class>
    <class>com.mycompany.testjpa.entities.Careceiptdetailsale</class>
    <class>com.mycompany.testjpa.entities.Openingcommitment</class>
    <class>com.mycompany.testjpa.entities.Badepositdetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Featurepermision</class>
    <class>com.mycompany.testjpa.entities.FrformulaCustom</class>
    <class>com.mycompany.testjpa.entities.Listitem</class>
    <class>com.mycompany.testjpa.entities.Glvoucherlistdetail</class>
    <class>com.mycompany.testjpa.entities.Stock</class>
    <class>com.mycompany.testjpa.entities.Frtemplateforktnn</class>
    <class>com.mycompany.testjpa.entities.Glvoucherdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Ininwardoutwarddetailparallel</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetailpurchase</class>
    <class>com.mycompany.testjpa.entities.Accountbalance</class>
    <class>com.mycompany.testjpa.entities.Capitalplan</class>
    <class>com.mycompany.testjpa.entities.Puinvoicedetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetailsalary</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetailsalary</class>
    <class>com.mycompany.testjpa.entities.Pasalarysortorder</class>
    <class>com.mycompany.testjpa.entities.Expenditureestimatedetail</class>
    <class>com.mycompany.testjpa.entities.Badepositdetailtax</class>
    <class>com.mycompany.testjpa.entities.Badepositdetailsale</class>
    <class>com.mycompany.testjpa.entities.Audittinglog</class>
    <class>com.mycompany.testjpa.entities.Contractdetail</class>
    <class>com.mycompany.testjpa.entities.Invoicetype</class>
    <class>com.mycompany.testjpa.entities.Autobusinessparallel</class>
    <class>com.mycompany.testjpa.entities.Ininwardoutward</class>
    <class>com.mycompany.testjpa.entities.Faadjustmentdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Suincrementdecrement</class>
    <class>com.mycompany.testjpa.entities.Butransferdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Accountingobject</class>
    <class>com.mycompany.testjpa.entities.Glpaymentlist</class>
    <class>com.mycompany.testjpa.entities.Budgetexpense</class>
    <class>com.mycompany.testjpa.entities.Gltemp</class>
    <class>com.mycompany.testjpa.entities.Bucommitmentadjustmentdetail</class>
    <class>com.mycompany.testjpa.entities.Buvoucherlistdetail</class>
    <class>com.mycompany.testjpa.entities.Buplanwithdrawdetail</class>
    <class>com.mycompany.testjpa.entities.Babanktransferdetailparallel</class>
    <class>com.mycompany.testjpa.entities.Generalledger</class>
    <class>com.mycompany.testjpa.entities.Openingaccountentry</class>
    <class>com.mycompany.testjpa.entities.Careceiptdetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Originalgeneralledger</class>
    <class>com.mycompany.testjpa.entities.Puinvoice</class>
    <class>com.mycompany.testjpa.entities.Butransfer</class>
    <class>com.mycompany.testjpa.entities.Activity</class>
    <class>com.mycompany.testjpa.entities.Budgetgroupitem</class>
    <class>com.mycompany.testjpa.entities.Capaymentdetailtax</class>
    <class>com.mycompany.testjpa.entities.Badepositdetail</class>
    <class>com.mycompany.testjpa.entities.Glpaymentlistdetail</class>
    <class>com.mycompany.testjpa.entities.Buplanreceipt</class>
    <class>com.mycompany.testjpa.entities.Faadjustmentdetail</class>
    <class>com.mycompany.testjpa.entities.Glvoucherlist</class>
    <class>com.mycompany.testjpa.entities.Sutransferdetail</class>
    <class>com.mycompany.testjpa.entities.Careceiptdetailtax</class>
    <class>com.mycompany.testjpa.entities.Bucommitmentrequestdetail</class>
    <class>com.mycompany.testjpa.entities.Bawithdrawdetailfixedasset</class>
    <class>com.mycompany.testjpa.entities.Accounttransfer</class>
    <class>com.mycompany.testjpa.entities.Expenditureestimate</class>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>

    <properties>
      <property name="javax.persistence.schema-generation.database.action" value="none"/>
      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
      <property name="hibernate.multiTenancy" value="DATABASE"/>
      <property name="hibernate.tenant_identifier_resolver" value="com.mycompany.testjpa.dao.multitenancy.DatabaseTenantResolver"/>
      <property name="hibernate.multi_tenant_connection_provider" value="com.mycompany.testjpa.dao.multitenancy.DatabaseMultiTenantProvider"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/ibigtime?zeroDateTimeBehavior=CONVERT_TO_NULL"/>
      <property name="javax.persistence.jdbc.user" value="root"/>
      <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.password" value=""/>
    </properties>
  </persistence-unit>
</persistence>

Now a new exception occurred:

java.lang.ClassCastException: com.sun.enterprise.container.common.impl.EntityManagerFactoryWrapper cannot be cast to org.hibernate.engine.spi.SessionFactoryImplementor

I don’t know what application server you are using, I assume Glassfish or Payara, but this might be a bug in their implementation. You can debug into the code and try to figure out what is happening here: Payara/EntityManagerFactoryWrapper.java at master · payara/Payara · GitHub

Yes, I’m using Payara as application server. Is there a way to create MultitenancyResolver directly from EntityManagerFactory, a not to try to unwrap SessionFactory?

I don’t know what you mean by MultitenancyResolver. Do you mean CurrentTenantIdentifierResolver? To access that you have to unwrap to a session factory. You should consult the Payara team about this. Looks like a bug on their side.

1 Like

@beikov Thanks! Now I change the server to WildFly and it works fine!

When I had the similar problem, I found mysql odbc driver and it worked.