Dynamically create EntityManager / Connect to custom host/DB every time in Hibernate

Question

I have an application running on Websphere Liberty which should compare tables from 2 databases/schemas.

The user should be able to input the connection data, like the host and the credentials.

I'm using Hibernate to access the application DB.

I've tried to use multiple Persistence Units, one for the application DB and one for all other DBs.

But i got 2 problems:

1. i get an "Illegal attempt to enlist multiple 1PC XAResources" error sometimes
2. can query the 2 DBs with user-submitted credentials, but i get no results except if i connect to the same db listed in server.xml file as DataSource

This are the DataSources on the server.xml on the server (dbs are oracle dbs)

<dataSource id="MyAppDS" jndiName="jdbc/MyDS" type="javax.sql.ConnectionPoolDataSource">
    <jdbcDriver javax.sql.ConnectionPoolDataSource="oracle.jdbc.pool.OracleConnectionPoolDataSource" libraryRef="OracleSQLLib"/>
    <connectionManager agedTimeout="30m" connectionTimeout="10s" maxPoolSize="20" minPoolSize="5"/>
    <properties password="..." url="jdbc:oracle:thin:@...:1521:..." user="..."/>
</dataSource>
<dataSource id="OtherOracle" jndiName="jdbc/OtherOracle" type="javax.sql.ConnectionPoolDataSource">
    <jdbcDriver javax.sql.ConnectionPoolDataSource="oracle.jdbc.pool.OracleConnectionPoolDataSource" libraryRef="OracleSQLLib"/>
    <connectionManager agedTimeout="30m" connectionTimeout="10s" maxPoolSize="20" minPoolSize="5"/>
    <properties password="..." url="jdbc:oracle:thin:@127.0.0.1:1521:XE" user="..."/>
</dataSource>

This is persistence.xml on the EJB module

<?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="main-persistence">
    <jta-data-source>jdbc/MyDS</jta-data-source>

    <class>classes...</class>

    <properties>
        <property name="hibernate.transaction.jta.platform"
            value="org.hibernate.service.jta.platform.internal.WebSphereExtendedJtaPlatform" />
        <property name="hibernate.dialect"
            value="org.hibernate.dialect.Oracle9iDialect" />
        <property name="hibernate.temp.use_jdbc_metadata_defaults"
            value="false" />
    </properties>
</persistence-unit>
<persistence-unit name="other-persistence" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/OtherOracle</non-jta-data-source>
    <class>classes...</class>

    <properties>
        <property name="hibernate.transaction.jta.platform"
            value="org.hibernate.service.jta.platform.internal.WebSphereExtendedJtaPlatform" />
        <property name="hibernate.dialect"
            value="org.hibernate.dialect.Oracle9iDialect" />
        <property name="hibernate.temp.use_jdbc_metadata_defaults"
            value="false" />
    </properties>
</persistence-unit>

On the Java Bean i use the EntityManagerFactory

    @PersistenceUnit(unitName = "other-persistence")
    private EntityManagerFactory emf;

And i create the entity manager with custom credentials like this

    Map<String, String> properties = new HashMap<String, String>();
    properties.put("hibernate.connection.driver_class", "oracle.jdbc.OracleDriver");
    properties.put("hibernate.connection.url", myCustomCreatedConnectionUrl);
    properties.put("hibernate.connection.username", customUser);
    properties.put("hibernate.connection.password", customPassword);
    properties.put("hibernate.dialect", "org.hibernate.dialect.Oracle9iDialect");
    properties.put("hibernate.show-sql", "true");
    EntityManager entityManager = emf.createEntityManager(properties);

If i check EntityManager properties with getProperties everything seems to be right. But the queries works only if the credentials/host are = to the DataSource. Otherwise i get no results (but no errors)

What could the problem be? Is there a way to use just one Persistence Unit but with custom host/credentials for different queries?

Answer 1

I have recently had the same request where a user can connect to application and enter their database details. From this point on every JPA Query is executed on this new connection.

The additional problem was that there were more than 100 bases to choose from and creating all datasources at start was not a good idea.

We have thus created a single RoutingDatasource that manages the JDBC connections. We were heavily inspired by Spring's implementation org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.

The main idea is:

• You extend AbstractDataSource and getConnection() method which is called on every JPA query. You are basically tinkering the JDBC layer under the JPA layer.
• You then create a new datasource according to what your user entered
• Set Your routing datasource as main datasource for your entity manager
• Optionally add some caching to avoid recreating datasources every time (or implement some pool)

Here is a demo class:

public class RoutingDataSource extends AbstractDataSource {

  ...
  ...

  @Override
  public Connection getConnection() throws SQLException {
    return determineTargetDataSource().getConnection();
  }

  DataSource determineTargetDataSource() {
    EmployeeDatabase lookupKey = determineCurrentLookupKey();

    DataSource dataSource = this.datasources.get(lookupKey);
    if (dataSource == null) {
      logger.debug("Datasource not found. Creating new one");

      SQLServerDataSource newDatasource = new SQLServerDataSource();
      newDatasource.setURL("jdbc:sqlserver://" + lookupKey.getDatabaseHost());
      newDatasource.setPassword(dbPass);
      datasources.put(lookupKey, newDatasource);

      dataSource = newDatasource;
    } else {
      logger.debug("Found existing database for key " + lookupKey);
    }

    logger.debug("Connecting to " + dataSource);
    return dataSource;
  }
}

Answer 2

Regarding the first error, ""Illegal attempt to enlist multiple 1PC XAResources", this occurs because you are using both resources within the same transaction. I can see <non-jta-data-source>jdbc/OtherOracle</non-jta-data-source> in your configuration, which indicates that you might have intended for jdbc/OtherOracle to be a non-enlisting resource. To make that work, the data source itself needs to be configured as non-enlisting. You can do this with the transactional="false" attribute as follows:

<dataSource id="OtherOracle" jndiName="jdbc/OtherOracle" type="javax.sql.ConnectionPoolDataSource" transactional="false">
...

On the other hand, if you actually want both resources to enlist in the transaction, then you need to use XADataSource rather than ConnectionPoolDataSource. Here is an example of how to do that (notice that both type type under dataSource and the attribute & class under jdbcDriver must be updated for this:

<dataSource id="MyAppDS" jndiName="jdbc/MyDS" type="javax.sql.XADataSource">
    <jdbcDriver javax.sql.XADataSource="oracle.jdbc.xa.client.OracleXADataSource" libraryRef="OracleSQLLib"/>
    <connectionManager agedTimeout="30m" connectionTimeout="10s" maxPoolSize="20" minPoolSize="5"/>
    <properties password="..." url="jdbc:oracle:thin:@...:1521:..." user="..."/>
</dataSource>
<dataSource id="OtherOracle" jndiName="jdbc/OtherOracle" type="javax.sql.XADataSource">
    <jdbcDriver javax.sql.XADataSource="oracle.jdbc.xa.client.OracleXADataSource" libraryRef="OracleSQLLib"/>
    <connectionManager agedTimeout="30m" connectionTimeout="10s" maxPoolSize="20" minPoolSize="5"/>
    <properties password="..." url="jdbc:oracle:thin:@127.0.0.1:1521:XE" user="..."/>
</dataSource>

In the second question, I think you are saying the different users cannot see the data. Could this be because the different database users are using different schema and do not have access each others' data? If you can get all of the users using a common schema, then adding @Table(schema="YOUR_SCHEMA_NAME") to the JPA @Entity might help. JavaDoc for the Table annotation can be found here.