CODEWITHSUNDEEP
mysqljpajpa-2.0eclipselink
Marcin Kapusta
Marcin Kapusta

Reputation: 5366

Table name based on entity value in EclipseLink 2.5, JPA 2.0

I would like to create design based on entities and JPA 2.0. In our current architecture we store the data in 2000 partitions in MySQL. Each partition is MyIsam table with names from data_0000 to data_1999 and this is only software solution. We didn't use any database specific partitions options because of performance problems. This works great but we didn't have nice architecture from the software point of view. Now we are migrating everything to JPA and we have such problem.

Suppose when we want to insert data to some partition we have to calculate partition number (table name) which is data_(entity.object_id mod 2000). The object_id column is not null. How we can design this using JPA and EclipseLink that if I have entity with object_id=453 so when I persist this object the data will be stored in table data_0453. I've seen many partition examples based on addnotation here http://wiki.eclipse.org/EclipseLink/Examples/JPA/Partitioning but no one of them is related for partitioning based on table names.

How is the best approach for this situation?

--

Thanks

Upvotes: 1

Views: 586

Answers (1)

James
James

Reputation: 18379

EclipseLink's partitioning support is for database partitioning, not table partitioning. Most databases have support for table partitioning, so it is odd you had to custom build a solution.

Can you try to encapsulate the access to the table with a view, or with stored procedures? EclipseLink allows you to override the CRUD operations with stored procedure calls, see http://wiki.eclipse.org/EclipseLink/Examples/JPA/CRUDStoredProcedures

Otherwise you could map a subclass to each table partition, but with 2000, that would probably not be a good solution. You may be able to use EclipseLink's dynamic support to avoid having the create 2000 subclasses.

You may also look into EclipseLink's multi-tenancy support, which does have some ability to choose a table based on an id.

You could also just not map the table and access it through native SQL queries.

But, I think using stored procedures is probably your best solution.

Upvotes: 1

Related Questions