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!
1 Like