| :index-group: DataSources |
| :jbake-type: page |
| :jbake-status: status=published |
| = Dynamic Datasource Routing |
| |
| The TomEE dynamic datasource api aims to allow to use multiple data |
| sources as one from an application point of view. |
| |
| It can be useful for technical reasons (load balancing for example) or |
| more generally functionnal reasons (filtering, aggregation, enriching…). |
| However please note you can choose only one datasource by transaction. |
| It means the goal of this feature is not to switch more than once of |
| datasource in a transaction. The following code will not work: |
| |
| [source,java] |
| ---- |
| @Stateless |
| public class MyEJB { |
| @Resource private MyRouter router; |
| @PersistenceContext private EntityManager em; |
| |
| public void workWithDataSources() { |
| router.setDataSource("ds1"); |
| em.persist(new MyEntity()); |
| |
| router.setDataSource("ds2"); // same transaction -> this invocation doesn't work |
| em.persist(new MyEntity()); |
| } |
| } |
| ---- |
| |
| In this example the implementation simply use a datasource from its name |
| and needs to be set before using any JPA operation in the transaction |
| (to keep the logic simple in the example). |
| |
| == The implementation of the Router |
| |
| Our router has two configuration parameters: * a list of jndi names |
| representing datasources to use * a default datasource to use |
| |
| === Router implementation |
| |
| The interface Router (`org.apache.openejb.resource.jdbc.Router`) has |
| only one method to implement, `public DataSource getDataSource()` |
| |
| Our `DeterminedRouter` implementation uses a ThreadLocal to manage the |
| currently used datasource. Keep in mind JPA used more than once the |
| getDatasource() method for one operation. To change the datasource in |
| one transaction is dangerous and should be avoid. |
| |
| [source,java] |
| ---- |
| package org.superbiz.dynamicdatasourcerouting; |
| |
| import org.apache.openejb.resource.jdbc.AbstractRouter; |
| |
| import javax.naming.NamingException; |
| import javax.sql.DataSource; |
| import java.util.Map; |
| import java.util.concurrent.ConcurrentHashMap; |
| |
| public class DeterminedRouter extends AbstractRouter { |
| private String dataSourceNames; |
| private String defaultDataSourceName; |
| private Map<String, DataSource> dataSources = null; |
| private ThreadLocal<DataSource> currentDataSource = new ThreadLocal<DataSource>(); |
| |
| /** |
| * @param datasourceList datasource resource name, separator is a space |
| */ |
| public void setDataSourceNames(String datasourceList) { |
| dataSourceNames = datasourceList; |
| } |
| |
| /** |
| * lookup datasource in openejb resources |
| */ |
| private void init() { |
| dataSources = new ConcurrentHashMap<String, DataSource>(); |
| for (String ds : dataSourceNames.split(" ")) { |
| try { |
| Object o = getOpenEJBResource(ds); |
| if (o instanceof DataSource) { |
| dataSources.put(ds, DataSource.class.cast(o)); |
| } |
| } catch (NamingException e) { |
| // ignored |
| } |
| } |
| } |
| |
| /** |
| * @return the user selected data source if it is set |
| * or the default one |
| * @throws IllegalArgumentException if the data source is not found |
| */ |
| @Override |
| public DataSource getDataSource() { |
| // lazy init of routed datasources |
| if (dataSources == null) { |
| init(); |
| } |
| |
| // if no datasource is selected use the default one |
| if (currentDataSource.get() == null) { |
| if (dataSources.containsKey(defaultDataSourceName)) { |
| return dataSources.get(defaultDataSourceName); |
| |
| } else { |
| throw new IllegalArgumentException("you have to specify at least one datasource"); |
| } |
| } |
| |
| // the developper set the datasource to use |
| return currentDataSource.get(); |
| } |
| |
| /** |
| * |
| * @param datasourceName data source name |
| */ |
| public void setDataSource(String datasourceName) { |
| if (dataSources == null) { |
| init(); |
| } |
| if (!dataSources.containsKey(datasourceName)) { |
| throw new IllegalArgumentException("data source called " + datasourceName + " can't be found."); |
| } |
| DataSource ds = dataSources.get(datasourceName); |
| currentDataSource.set(ds); |
| } |
| |
| /** |
| * reset the data source |
| */ |
| public void clear() { |
| currentDataSource.remove(); |
| } |
| |
| public void setDefaultDataSourceName(String name) { |
| this.defaultDataSourceName = name; |
| } |
| } |
| ---- |
| |
| === Declaring the implementation |
| |
| To be able to use your router as a resource you need to provide a |
| service configuration. It is done in a file you can find in |
| META-INF/org.router/ and called service-jar.xml (for your implementation |
| you can of course change the package name). |
| |
| It contains the following code: |
| |
| [source,xml] |
| ---- |
| <ServiceJar> |
| <ServiceProvider id="DeterminedRouter" <!-- the name you want to use --> |
| service="Resource" |
| type="org.apache.openejb.resource.jdbc.Router" |
| class-name="org.superbiz.dynamicdatasourcerouting.DeterminedRouter"> <!-- implementation class --> |
| |
| # the parameters |
| |
| DataSourceNames |
| DefaultDataSourceName |
| </ServiceProvider> |
| </ServiceJar> |
| ---- |
| |
| == Using the Router |
| |
| Here we have a `RoutedPersister` stateless bean which uses our |
| `DeterminedRouter` |
| |
| [source,java] |
| ---- |
| package org.superbiz.dynamicdatasourcerouting; |
| |
| import javax.annotation.Resource; |
| import javax.ejb.Stateless; |
| import javax.persistence.EntityManager; |
| import javax.persistence.PersistenceContext; |
| |
| @Stateless |
| public class RoutedPersister { |
| @PersistenceContext(unitName = "router") |
| private EntityManager em; |
| |
| @Resource(name = "My Router", type = DeterminedRouter.class) |
| private DeterminedRouter router; |
| |
| public void persist(int id, String name, String ds) { |
| router.setDataSource(ds); |
| em.persist(new Person(id, name)); |
| } |
| } |
| ---- |
| |
| == The test |
| |
| In test mode and using property style configuration the foolowing |
| configuration is used: |
| |
| [source,java] |
| ---- |
| public class DynamicDataSourceTest { |
| @Test |
| public void route() throws Exception { |
| String[] databases = new String[]{"database1", "database2", "database3"}; |
| |
| Properties properties = new Properties(); |
| properties.setProperty(Context.INITIAL_CONTEXT_FACTORY, LocalInitialContextFactory.class.getName()); |
| |
| // resources |
| // datasources |
| for (int i = 1; i <= databases.length; i++) { |
| String dbName = databases[i - 1]; |
| properties.setProperty(dbName, "new://Resource?type=DataSource"); |
| dbName += "."; |
| properties.setProperty(dbName + "JdbcDriver", "org.hsqldb.jdbcDriver"); |
| properties.setProperty(dbName + "JdbcUrl", "jdbc:hsqldb:mem:db" + i); |
| properties.setProperty(dbName + "UserName", "sa"); |
| properties.setProperty(dbName + "Password", ""); |
| properties.setProperty(dbName + "JtaManaged", "true"); |
| } |
| |
| // router |
| properties.setProperty("My Router", "new://Resource?provider=org.router:DeterminedRouter&type=" + DeterminedRouter.class.getName()); |
| properties.setProperty("My Router.DatasourceNames", "database1 database2 database3"); |
| properties.setProperty("My Router.DefaultDataSourceName", "database1"); |
| |
| // routed datasource |
| properties.setProperty("Routed Datasource", "new://Resource?provider=RoutedDataSource&type=" + Router.class.getName()); |
| properties.setProperty("Routed Datasource.Router", "My Router"); |
| |
| Context ctx = EJBContainer.createEJBContainer(properties).getContext(); |
| RoutedPersister ejb = (RoutedPersister) ctx.lookup("java:global/dynamic-datasource-routing/RoutedPersister"); |
| for (int i = 0; i < 18; i++) { |
| // persisting a person on database db -> kind of manual round robin |
| String name = "record " + i; |
| String db = databases[i % 3]; |
| ejb.persist(i, name, db); |
| } |
| |
| // assert database records number using jdbc |
| for (int i = 1; i <= databases.length; i++) { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:db" + i, "sa", ""); |
| Statement st = connection.createStatement(); |
| ResultSet rs = st.executeQuery("select count(*) from PERSON"); |
| rs.next(); |
| assertEquals(6, rs.getInt(1)); |
| st.close(); |
| connection.close(); |
| } |
| |
| ctx.close(); |
| } |
| } |
| ---- |
| |
| == Configuration via openejb.xml |
| |
| The testcase above uses properties for configuration. The identical way |
| to do it via the `conf/openejb.xml` is as follows: |
| |
| [source,xml] |
| ---- |
| <!-- Router and datasource --> |
| <Resource id="My Router" type="org.apache.openejb.router.test.DynamicDataSourceTest$DeterminedRouter" provider="org.routertest:DeterminedRouter"> |
| DatasourceNames = database1 database2 database3 |
| DefaultDataSourceName = database1 |
| </Resource> |
| <Resource id="Routed Datasource" type="org.apache.openejb.resource.jdbc.Router" provider="RoutedDataSource"> |
| Router = My Router |
| </Resource> |
| |
| <!-- real datasources --> |
| <Resource id="database1" type="DataSource"> |
| JdbcDriver = org.hsqldb.jdbcDriver |
| JdbcUrl = jdbc:hsqldb:mem:db1 |
| UserName = sa |
| Password |
| JtaManaged = true |
| </Resource> |
| <Resource id="database2" type="DataSource"> |
| JdbcDriver = org.hsqldb.jdbcDriver |
| JdbcUrl = jdbc:hsqldb:mem:db2 |
| UserName = sa |
| Password |
| JtaManaged = true |
| </Resource> |
| <Resource id="database3" type="DataSource"> |
| JdbcDriver = org.hsqldb.jdbcDriver |
| JdbcUrl = jdbc:hsqldb:mem:db3 |
| UserName = sa |
| Password |
| JtaManaged = true |
| </Resource> |
| ---- |
| |
| === Some hack for OpenJPA |
| |
| Using more than one datasource behind one EntityManager means the |
| databases are already created. If it is not the case, the JPA provider |
| has to create the datasource at boot time. |
| |
| Hibernate do it so if you declare your databases it will work. However |
| with OpenJPA (the default JPA provider for OpenEJB), the creation is |
| lazy and it happens only once so when you’ll switch of database it will |
| no more work. |
| |
| Of course OpenEJB provides @Singleton and @Startup features of Java EE 6 |
| and we can do a bean just making a simple find, even on none existing |
| entities, just to force the database creation: |
| |
| [source,java] |
| ---- |
| @Startup |
| @Singleton |
| public class BoostrapUtility { |
| // inject all real databases |
| |
| @PersistenceContext(unitName = "db1") |
| private EntityManager em1; |
| |
| @PersistenceContext(unitName = "db2") |
| private EntityManager em2; |
| |
| @PersistenceContext(unitName = "db3") |
| private EntityManager em3; |
| |
| // force database creation |
| |
| @PostConstruct |
| @TransactionAttribute(TransactionAttributeType.SUPPORTS) |
| public void initDatabase() { |
| em1.find(Person.class, 0); |
| em2.find(Person.class, 0); |
| em3.find(Person.class, 0); |
| } |
| } |
| ---- |
| |
| === Using the routed datasource |
| |
| Now you configured the way you want to route your JPA operation, you |
| registered the resources and you initialized your databases you can use |
| it and see how it is simple: |
| |
| [source,java] |
| ---- |
| @Stateless |
| public class RoutedPersister { |
| // injection of the "proxied" datasource |
| @PersistenceContext(unitName = "router") |
| private EntityManager em; |
| |
| // injection of the router you need it to configured the database |
| @Resource(name = "My Router", type = DeterminedRouter.class) |
| private DeterminedRouter router; |
| |
| public void persist(int id, String name, String ds) { |
| router.setDataSource(ds); // configuring the database for the current transaction |
| em.persist(new Person(id, name)); // will use ds database automatically |
| } |
| } |
| ---- |
| |
| == Running |
| |
| [source,console] |
| ---- |
| ------------------------------------------------------- |
| T E S T S |
| ------------------------------------------------------- |
| Running org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest |
| Apache OpenEJB 4.0.0-beta-1 build: 20111002-04:06 |
| http://tomee.apache.org/ |
| INFO - openejb.home = /Users/dblevins/examples/dynamic-datasource-routing |
| INFO - openejb.base = /Users/dblevins/examples/dynamic-datasource-routing |
| INFO - Using 'javax.ejb.embeddable.EJBContainer=true' |
| INFO - Configuring Service(id=Default Security Service, type=SecurityService, provider-id=Default Security Service) |
| INFO - Configuring Service(id=Default Transaction Manager, type=TransactionManager, provider-id=Default Transaction Manager) |
| INFO - Configuring Service(id=My Router, type=Resource, provider-id=DeterminedRouter) |
| INFO - Configuring Service(id=database3, type=Resource, provider-id=Default JDBC Database) |
| INFO - Configuring Service(id=database2, type=Resource, provider-id=Default JDBC Database) |
| INFO - Configuring Service(id=Routed Datasource, type=Resource, provider-id=RoutedDataSource) |
| INFO - Configuring Service(id=database1, type=Resource, provider-id=Default JDBC Database) |
| INFO - Found EjbModule in classpath: /Users/dblevins/examples/dynamic-datasource-routing/target/classes |
| INFO - Beginning load: /Users/dblevins/examples/dynamic-datasource-routing/target/classes |
| INFO - Configuring enterprise application: /Users/dblevins/examples/dynamic-datasource-routing |
| WARN - Method 'lookup' is not available for 'javax.annotation.Resource'. Probably using an older Runtime. |
| INFO - Configuring Service(id=Default Singleton Container, type=Container, provider-id=Default Singleton Container) |
| INFO - Auto-creating a container for bean BoostrapUtility: Container(type=SINGLETON, id=Default Singleton Container) |
| INFO - Configuring Service(id=Default Stateless Container, type=Container, provider-id=Default Stateless Container) |
| INFO - Auto-creating a container for bean RoutedPersister: Container(type=STATELESS, id=Default Stateless Container) |
| INFO - Auto-linking resource-ref 'java:comp/env/My Router' in bean RoutedPersister to Resource(id=My Router) |
| INFO - Configuring Service(id=Default Managed Container, type=Container, provider-id=Default Managed Container) |
| INFO - Auto-creating a container for bean org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest: Container(type=MANAGED, id=Default Managed Container) |
| INFO - Configuring PersistenceUnit(name=router) |
| INFO - Configuring PersistenceUnit(name=db1) |
| INFO - Auto-creating a Resource with id 'database1NonJta' of type 'DataSource for 'db1'. |
| INFO - Configuring Service(id=database1NonJta, type=Resource, provider-id=database1) |
| INFO - Adjusting PersistenceUnit db1 <non-jta-data-source> to Resource ID 'database1NonJta' from 'null' |
| INFO - Configuring PersistenceUnit(name=db2) |
| INFO - Auto-creating a Resource with id 'database2NonJta' of type 'DataSource for 'db2'. |
| INFO - Configuring Service(id=database2NonJta, type=Resource, provider-id=database2) |
| INFO - Adjusting PersistenceUnit db2 <non-jta-data-source> to Resource ID 'database2NonJta' from 'null' |
| INFO - Configuring PersistenceUnit(name=db3) |
| INFO - Auto-creating a Resource with id 'database3NonJta' of type 'DataSource for 'db3'. |
| INFO - Configuring Service(id=database3NonJta, type=Resource, provider-id=database3) |
| INFO - Adjusting PersistenceUnit db3 <non-jta-data-source> to Resource ID 'database3NonJta' from 'null' |
| INFO - Enterprise application "/Users/dblevins/examples/dynamic-datasource-routing" loaded. |
| INFO - Assembling app: /Users/dblevins/examples/dynamic-datasource-routing |
| INFO - PersistenceUnit(name=router, provider=org.apache.openjpa.persistence.PersistenceProviderImpl) - provider time 504ms |
| INFO - PersistenceUnit(name=db1, provider=org.apache.openjpa.persistence.PersistenceProviderImpl) - provider time 11ms |
| INFO - PersistenceUnit(name=db2, provider=org.apache.openjpa.persistence.PersistenceProviderImpl) - provider time 7ms |
| INFO - PersistenceUnit(name=db3, provider=org.apache.openjpa.persistence.PersistenceProviderImpl) - provider time 6ms |
| INFO - Jndi(name="java:global/dynamic-datasource-routing/BoostrapUtility!org.superbiz.dynamicdatasourcerouting.BoostrapUtility") |
| INFO - Jndi(name="java:global/dynamic-datasource-routing/BoostrapUtility") |
| INFO - Jndi(name="java:global/dynamic-datasource-routing/RoutedPersister!org.superbiz.dynamicdatasourcerouting.RoutedPersister") |
| INFO - Jndi(name="java:global/dynamic-datasource-routing/RoutedPersister") |
| INFO - Jndi(name="java:global/EjbModule1519652738/org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest!org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest") |
| INFO - Jndi(name="java:global/EjbModule1519652738/org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest") |
| INFO - Created Ejb(deployment-id=RoutedPersister, ejb-name=RoutedPersister, container=Default Stateless Container) |
| INFO - Created Ejb(deployment-id=org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest, ejb-name=org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest, container=Default Managed Container) |
| INFO - Created Ejb(deployment-id=BoostrapUtility, ejb-name=BoostrapUtility, container=Default Singleton Container) |
| INFO - Started Ejb(deployment-id=RoutedPersister, ejb-name=RoutedPersister, container=Default Stateless Container) |
| INFO - Started Ejb(deployment-id=org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest, ejb-name=org.superbiz.dynamicdatasourcerouting.DynamicDataSourceTest, container=Default Managed Container) |
| INFO - Started Ejb(deployment-id=BoostrapUtility, ejb-name=BoostrapUtility, container=Default Singleton Container) |
| INFO - Deployed Application(path=/Users/dblevins/examples/dynamic-datasource-routing) |
| Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 2.504 sec |
| |
| Results : |
| |
| Tests run: 1, Failures: 0, Errors: 0, Skipped: 0 |
| ---- |