overriding datasource in the persistent unit

[rtselvan]

My application is a multi-tenant app, I would need to override the datasource at runtime when creating the entitymanager factory based on the client code/name. There would be only one persistent unit but at runtime different database connection to be used.

Does OpenJPA support that? Hibernate supports it and I have tested it successfully but I would like to use OpenJPA due to the light weight nature of it.

Any ideas?

Thanks
/selvan

[Daryl Stultz]

On Fri, Nov 6, 2009 at 8:10 PM, rtselvan <[hidden email]> wrote:

>
> My application is a multi-tenant app, I would need to override the
> datasource
> at runtime when creating the entitymanager factory based on the client
> code/name.
>
> Does OpenJPA support that?


In a month or two I will be modifying my application to do just this sort of
thing. I assume by "multi-tenant" you mean someone will login as a user
under one of many organizations and the user will then have access to one of
many persistence units that correspond to the organizations? I have much
more to do than selecting the right persistence unit / em factory, but I'm
interested in whatever you come up with. I hadn't considered that OpenJPA
might support it, I figured I'd have to instantiate all the factories when
the app starts up, then use the URL the user accesses the app with to map to
the right factory. It actually seems fairly easy, but if OpenJPA supports
some mechanism already, all the better.

I currently use a static factory with a ThreadLocal entity manager. So I
want my business logic to continue to be able to pick the entity manager
from the current thread. This means I need to set up the ThreadLocal state
at the beginning of the event thread to be able to create an entity manager
from the right factory, presumably by mapping the URL to the factory.

Those are my thoughts thus far.

--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:[hidden email]

[Miłosz Tylenda]

Hello!

> >
> > My application is a multi-tenant app, I would need to override the
> > datasource
> > at runtime when creating the entitymanager factory based on the client
> > code/name.
> >
> > Does OpenJPA support that?
>
>
> In a month or two I will be modifying my application to do just this sort of
> thing. I assume by "multi-tenant" you mean someone will login as a user
> under one of many organizations and the user will then have access to one of
> many persistence units that correspond to the organizations? I have much
> more to do than selecting the right persistence unit / em factory, but I'm
> interested in whatever you come up with. I hadn't considered that OpenJPA
> might support it, I figured I'd have to instantiate all the factories when
> the app starts up, then use the URL the user accesses the app with to map to
> the right factory. It actually seems fairly easy, but if OpenJPA supports
> some mechanism already, all the better.

Have you tried calling Persistence.createEntityManagerFactory(String, Map)? The second parameter should allow you to override any value you have set in persistence.xml, so in particular, the data source name.

Regards,
Milosz

[Michael Dick]

Adding to Milosz response.

The property keys that you want are javax.persistence.jtaDataSource and
javax.persistence.nonJtaDataSource (for jta-data-source and
non-jta-data-source respectively)

For example :
        Map<String, Object> props = new HashMap<String, Object>();
        props.put("javax.persistence.jtaDataSource", "jdbc/override");
        props.put("javax.persistence.nonJtaDataSource",
"jdbc/overrideNonJTA");
        EntityManagerFactory emf =
Persistence.createEntityManagerFactory(getPersistenceUnitName(), props);

In earlier versions of OpenJPA you can use openjpa.ConnectionFactoryName for
jta-data-source and openjpa.ConnectionFactory2Name for non-jta-data-source.

Hope this helps,
-mike

On Sat, Nov 7, 2009 at 2:41 PM, Miłosz Tylenda <[hidden email]> wrote:

> Hello!
>
> > >
> > > My application is a multi-tenant app, I would need to override the
> > > datasource
> > > at runtime when creating the entitymanager factory based on the client
> > > code/name.
> > >
> > > Does OpenJPA support that?
> >
> >
> > In a month or two I will be modifying my application to do just this sort
> of
> > thing. I assume by "multi-tenant" you mean someone will login as a user
> > under one of many organizations and the user will then have access to one
> of
> > many persistence units that correspond to the organizations? I have much
> > more to do than selecting the right persistence unit / em factory, but
> I'm
> > interested in whatever you come up with. I hadn't considered that OpenJPA
> > might support it, I figured I'd have to instantiate all the factories
> when
> > the app starts up, then use the URL the user accesses the app with to map
> to
> > the right factory. It actually seems fairly easy, but if OpenJPA supports
> > some mechanism already, all the better.
>
> Have you tried calling Persistence.createEntityManagerFactory(String, Map)?
> The second parameter should allow you to override any value you have set in
> persistence.xml, so in particular, the data source name.
>
> Regards,
> Milosz
>
>

[rtselvan]

There may be some difference in what you are doing though. I have one only persistence unit in the XML file and connecting to different database based on the user/organization. This avoids to creating a lot of persistence unit and as well, ability to add additional organization without having change the persistence unit xml file.

Daryl Stultz wrote:

On Fri, Nov 6, 2009 at 8:10 PM, rtselvan <[e-mail]> wrote:

>
> My application is a multi-tenant app, I would need to override the
> datasource
> at runtime when creating the entitymanager factory based on the client
> code/name.
>
> Does OpenJPA support that?


In a month or two I will be modifying my application to do just this sort of
thing. I assume by "multi-tenant" you mean someone will login as a user
under one of many organizations and the user will then have access to one of
many persistence units that correspond to the organizations? I have much
more to do than selecting the right persistence unit / em factory, but I'm
interested in whatever you come up with. I hadn't considered that OpenJPA
might support it, I figured I'd have to instantiate all the factories when
the app starts up, then use the URL the user accesses the app with to map to
the right factory. It actually seems fairly easy, but if OpenJPA supports
some mechanism already, all the better.

I currently use a static factory with a ThreadLocal entity manager. So I
want my business logic to continue to be able to pick the entity manager
from the current thread. This means I need to set up the ThreadLocal state
at the beginning of the event thread to be able to create an entity manager
from the right factory, presumably by mapping the URL to the factory.

Those are my thoughts thus far.

--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:[e-mail]

[rtselvan]

Thanks Guys!

Let me try that, it looks pretty simple

Michael Dick wrote:

Adding to Milosz response.

The property keys that you want are javax.persistence.jtaDataSource and
javax.persistence.nonJtaDataSource (for jta-data-source and
non-jta-data-source respectively)

For example :
        Map<String, Object> props = new HashMap<String, Object>();
        props.put("javax.persistence.jtaDataSource", "jdbc/override");
        props.put("javax.persistence.nonJtaDataSource",
"jdbc/overrideNonJTA");
        EntityManagerFactory emf =
Persistence.createEntityManagerFactory(getPersistenceUnitName(), props);

In earlier versions of OpenJPA you can use openjpa.ConnectionFactoryName for
jta-data-source and openjpa.ConnectionFactory2Name for non-jta-data-source.

Hope this helps,
-mike

On Sat, Nov 7, 2009 at 2:41 PM, Miłosz Tylenda <[e-mail]> wrote:

> Hello!
>
> > >
> > > My application is a multi-tenant app, I would need to override the
> > > datasource
> > > at runtime when creating the entitymanager factory based on the client
> > > code/name.
> > >
> > > Does OpenJPA support that?
> >
> >
> > In a month or two I will be modifying my application to do just this sort
> of
> > thing. I assume by "multi-tenant" you mean someone will login as a user
> > under one of many organizations and the user will then have access to one
> of
> > many persistence units that correspond to the organizations? I have much
> > more to do than selecting the right persistence unit / em factory, but
> I'm
> > interested in whatever you come up with. I hadn't considered that OpenJPA
> > might support it, I figured I'd have to instantiate all the factories
> when
> > the app starts up, then use the URL the user accesses the app with to map
> to
> > the right factory. It actually seems fairly easy, but if OpenJPA supports
> > some mechanism already, all the better.
>
> Have you tried calling Persistence.createEntityManagerFactory(String, Map)?
> The second parameter should allow you to override any value you have set in
> persistence.xml, so in particular, the data source name.
>
> Regards,
> Milosz
>
>

[Daryl Stultz]

On Sat, Nov 7, 2009 at 7:53 PM, rtselvan <[hidden email]> wrote:

>
> There may be some difference in what you are doing though. I have one only
> persistence unit in the XML file and connecting to different database based
> on the user/organization. This avoids to creating a lot of persistence unit
> and as well, ability to add additional organization without having change
> the persistence unit xml file.
>

Yeah, I definitely want to be able to add and drop organizations while the
application is live, thus no changes to persistence.xml. But isn't a one to
one correspondence between persistence unit and database implied? How does
JPA know the difference between 2 entities of the same class with the same
ID but from different databases?

I'm using a DataSource to get connections from my connection pool, I can see
clearly how I can choose a connection from different databases, but I would
think JPA would get very confused if I did that.

--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:[hidden email]

[rtselvan]

It works great! It was identical to hibernate JPA implementation.

rtselvan wrote:

Thanks Guys!

Let me try that, it looks pretty simple

Michael Dick wrote:

Adding to Milosz response.

The property keys that you want are javax.persistence.jtaDataSource and
javax.persistence.nonJtaDataSource (for jta-data-source and
non-jta-data-source respectively)

For example :
        Map<String, Object> props = new HashMap<String, Object>();
        props.put("javax.persistence.jtaDataSource", "jdbc/override");
        props.put("javax.persistence.nonJtaDataSource",
"jdbc/overrideNonJTA");
        EntityManagerFactory emf =
Persistence.createEntityManagerFactory(getPersistenceUnitName(), props);

In earlier versions of OpenJPA you can use openjpa.ConnectionFactoryName for
jta-data-source and openjpa.ConnectionFactory2Name for non-jta-data-source.

Hope this helps,
-mike

On Sat, Nov 7, 2009 at 2:41 PM, Miłosz Tylenda <[e-mail]> wrote:

> Hello!
>
> > >
> > > My application is a multi-tenant app, I would need to override the
> > > datasource
> > > at runtime when creating the entitymanager factory based on the client
> > > code/name.
> > >
> > > Does OpenJPA support that?
> >
> >
> > In a month or two I will be modifying my application to do just this sort
> of
> > thing. I assume by "multi-tenant" you mean someone will login as a user
> > under one of many organizations and the user will then have access to one
> of
> > many persistence units that correspond to the organizations? I have much
> > more to do than selecting the right persistence unit / em factory, but
> I'm
> > interested in whatever you come up with. I hadn't considered that OpenJPA
> > might support it, I figured I'd have to instantiate all the factories
> when
> > the app starts up, then use the URL the user accesses the app with to map
> to
> > the right factory. It actually seems fairly easy, but if OpenJPA supports
> > some mechanism already, all the better.
>
> Have you tried calling Persistence.createEntityManagerFactory(String, Map)?
> The second parameter should allow you to override any value you have set in
> persistence.xml, so in particular, the data source name.
>
> Regards,
> Milosz
>
>

[rtselvan]

Correct, you need to create a separate instance of the EntityManagerFactory for each organization by passing in the datasource name. it means that you create the factory.createEntityManager() for each thread based on the factory.

here is where you apply the solution that Michael and Milosz were taking about.

eg:

                EntityManagerFactory emf = factories.get(client);
                if (emf == null)
                {
                        Map<String, String> props = new HashMap<String, String>();
                        props.put("javax.persistence.jtaDataSource", "jdbc/" + client + "db");
                        emf = Persistence.createEntityManagerFactory("pu", props);
                        factories.put(client, emf);
                }
                EntityManager em = emf.createEntityManager();

also, you need to create the data sources programmatically if you adding new organizations while application is live.  If you are interested here is an example of it.

<pre>
                String dsName = String.format("%1$sDb", clientCode);
                Resource res = new Resource(dsName, "javax.sql.DataSource");
                res.setJndi(dsName);
               
                Properties props = res.getProperties();
                props.put("JdbcDriver", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
                props.put("JdbcUrl", String.format("jdbc:sqlserver://172.19.101.12;databaseName=%1$s_db", clientCode));
                props.put("UserName", "sa");
                props.put("Password", "whatever");
                props.put("JtaManaged", "true");
               
                try
                {
                        Assembler component = SystemInstance.get().getComponent(Assembler.class);
                        component.createResource(new ConfigurationFactory().configureService(res, ResourceInfo.class));
                        Thread.yield();
                }
                catch (OpenEJBException e)
                {
                        throw e;
                }
</pre>

Hope that it helps

Daryl Stultz wrote:

On Sat, Nov 7, 2009 at 7:53 PM, rtselvan <[e-mail]> wrote:

>
> There may be some difference in what you are doing though. I have one only
> persistence unit in the XML file and connecting to different database based
> on the user/organization. This avoids to creating a lot of persistence unit
> and as well, ability to add additional organization without having change
> the persistence unit xml file.
>

Yeah, I definitely want to be able to add and drop organizations while the
application is live, thus no changes to persistence.xml. But isn't a one to
one correspondence between persistence unit and database implied? How does
JPA know the difference between 2 entities of the same class with the same
ID but from different databases?

I'm using a DataSource to get connections from my connection pool, I can see
clearly how I can choose a connection from different databases, but I would
think JPA would get very confused if I did that.

--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:[e-mail]

[Daryl Stultz]

On Sat, Nov 7, 2009 at 11:41 PM, rtselvan <[hidden email]> wrote:

>
> Correct, you need to create a separate instance of the EntityManagerFactory
> for each organization by passing in the datasource name.


Thanks for sharing your solution. I'm sure it will come in handy when it
comes time to add this feature to my system.


--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:[hidden email]