CODEWITHSUNDEEP
javahibernatejpa
boden
boden

Reputation: 1681

Change table name in runtime with JPA

I implement a service which is using database. There are few tables in the database. Inside these tables store results of some calculations. Another services creates a new table for each calculation and now list of table looks like this:

The table structure is same for all tables.

Is it possible to get data from these tables using JPA without creation entity for each table?

Upvotes: 1

Views: 783

Answers (2)

krokodilko
krokodilko

Reputation: 36127

@krokodilko I absolutely agree with you, but I am not responsible for changing design structure because I'm not a developer of it, I just use it one for my goals.

If you must live with it, then I would dynamically drop/create a view or synonym to cheat JPA. The idea is to:

  • before loading EntityManager, create a view/synonym with a fixed name, which maps the selected table to that common name
  • in JPA map the entity to this common name of view/synonym using XML or annotations
  • load entity manager

This approach has disadventages:

  • only one instance/thread of the application can use this view/synonym at the same time, this won't work in multiuser environment (unless all instances/threads are synchronized and will turn off / on entity managers simultaneously at the time when the view is updated/recreated - but I can not even imagine such a solution, it would be a huge project with many traps and errors)
  • only one table can be used at a time, in order to use another one, the entity manager must be closed, the view/synonym must be dropped, then created again, then the entity manager must be loaded again

You can also create a view which union many tables, something like this:

CREATE VIEM my_common_name AS
SELECT '1_0' as calc_name, t.* FROM calculation_1_0 t UNION ALL
SELECT '2_0' as calc_name, t.* FROM calculation_2_0 t UNION ALL
.....
.....
SELECT 'X_Y' as calc_name, t.* FROM calculation_X_Y t

but again - if the process creates a new table, you must close entity manager, recreate this view with a new table name, and load entity manager again.

And this will be very poor from the performance perspective - you cannot create any index on this view, it cannot be partitioned nor tuned nor materialized, every query against this monster view will almost always do full table scans of all these tables.

A bad database structure design was done in the first place, it's like designing phones with a different charging socket, you'll need to use a different plug each time you charge your phone. The best it can be done is to fix this original error because such a fix is very easy to do, just create one common table with an additional colum "calculation_name" and fix a code which saves data to these tables (insert data into common table instead of creating new tables each time), everything else will be a bothersome attempt to get around this problem.

If I were you, then I would go to my manager, and I would tell him that there is such a technical problem wit bad design. I would tell him we can fix it now, and it will cost a little ($). I would tell him that if we do not fix it now, then every future change in this functionality will be more and more troublesome and expensive ($$, $$$, $$$$$ - because of cost of man days, maintanance, performance/hardware etc), and eventually such a fix will be simply impossible and we will have to rewrite this system from scratch (and this will cost us $$$$$$$).
The manager has a wider view, he should know business plans, he should know if this functionality will be developed in the future, or maybe it will be abandoned in half a year. It's the manager decision - invest $ now and save $$$$$$ in the future, or do nothing with it because it will not give us any profits in the future.

Upvotes: 0

Robert Bain
Robert Bain

Reputation: 9586

Creating tables at runtime is a bad idea. Given that you know what the columns are, a better design would be to have a calculation_type lookup table and create a foreign key in your calculation table and index it. Then you can create your entities up front and have better relational integrity.

To answer the question directly, new tables cannot be created dynamically then mapped using JPA. You could use plain JDBC but there's a reason JPA doesn't support it - it's bad design.

Upvotes: 2

Related Questions