Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Many databases, same shared schema. One EntityManagerFactory?(How to share the same EntityManagerFactory for several (same-schema) databases)
Many databases, same shared schema. One EntityManagerFactory? [message #1747555] Tue, 15 November 2016 21:52 Go to next message
Miguel García López is currently offline Miguel García LópezFriend
Messages: 1
Registered: November 2016
Junior Member
Hi, I am facing a problem on a legacy project I just inherited and written by not very experienced developers.

We have a webapp deployed to a Tomcat instance and using EclipseLink 2.4.2 (could upgrade if required) to access a MySQL database (Amazon RDS).

Previous developers took the approach to *create a new database* for every new customer, all of them with the same schema definition. And then store these customer DBs names (schemas, or catalogs in MySQL parlance) somewhere.

The application creates *a new*, dedicated EntityManagerFactory for each customer session, store it into a Map[cust, emf] and remove it upon session expiration. We have a base persistence.xml and then set the specific DB name property at runtime when Persistence.createEntityManagerFactory(pu, props)-ing.

We now have > 1000 customers... the server eats loads of memory as you'll imagine and has the same number of DB connections to the server. This is of course not scalable. We're sort of screwed because we're growing Sad

(we're planning to rewrite most of our data access layer and fix this, but we need something for the meantime because that'll be a daunting task)

Is there anything we could do with EclipseLink? I am thinking, maybe some way we could set our persistence.xml use a (Tomcat) DataSource to MySQL with no db name in the URL, and then setting the name (or schema, or catalog) in a per-case basis at runtime for each EntityManager we get.

Whether the above is feasible, and/or how to do it is unknown to me. I would be more than grateful if anyone here could help or maybe suggest an alternative approach.

Thanks a lot!
icon4.gif  Re: Many databases, same shared schema. One EntityManagerFactory? [message #1752533 is a reply to message #1747555] Tue, 24 January 2017 21:11 Go to previous messageGo to next message
Kevin Sutter is currently offline Kevin SutterFriend
Messages: 106
Registered: July 2009
Senior Member
Any input on this? We're experiencing the same type of scenario. We had this capability with OpenJPA [1], but we can't get this to consistently work with EclipseLink. Although this sounds "unique", it's actually a fairly common pattern. Thanks for any insights!

[1] http://openjpa.apache.org/builds/2.4.1/apache-openjpa/docs/manual.html#ref_guide_dbsetup_setDSBenefits
Re: Many databases, same shared schema. One EntityManagerFactory? [message #1754256 is a reply to message #1752533] Wed, 15 February 2017 19:16 Go to previous messageGo to next message
Joe Grassel is currently offline Joe GrasselFriend
Messages: 3
Registered: April 2015
Junior Member
Something like that is already supported by Eclipselink, under its "Auditing" feature:

https://wiki.eclipse.org/EclipseLink/Examples/JPA/Auditing

According to the docs, all that needs to be set are the following:

properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
properties.put("javax.persistence.jtaDataSource", [DataSource object or DataSource's JNDI name]);
em = emf.createEntityManager(properties);

The JNDI name approach is currently bugged (https://bugs.eclipse.org/bugs/show_bug.cgi?id=512255), but dropping a DataSource object in works.

[Updated on: Wed, 15 February 2017 20:16]

Report message to a moderator

Re: Many databases, same shared schema. One EntityManagerFactory? [message #1754368 is a reply to message #1754256] Thu, 16 February 2017 21:31 Go to previous message
Joe Grassel is currently offline Joe GrasselFriend
Messages: 3
Registered: April 2015
Junior Member
Bug 512255 has been delivered to master, so JNDI names should now work as well as dropping in a DataSource object.
Previous Topic:Eclipselink 2.6.4CR1 can't query with MongoDB
Next Topic:XmlPath Issue with MOXy 2.6.0
Goto Forum:
  


Current Time: Mon Dec 11 23:00:06 GMT 2017

Powered by FUDForum. Page generated in 0.01972 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software