26

There is this table that is being generated on a monthly basis. Basically the table structure of all monthly tables is the same.

Since it would be a lot of work to map the same entity just with a different table name,

Is it possible to change the table name of an entity as follows on runtime since they have the same table structure after all?

   @Entity
   @Table(name="FOO_JAN2010") // any other ways to generate this dynamically?
   public class FooJan2010Table {  // if we can dynamically set the table name this can be simply named FooTable
       ...
   }

If not, what approach can you suggest?

Shawn
  • 513
  • 9
  • 18
Joopiter
  • 525
  • 3
  • 8
  • 18

4 Answers4

12

Is it possible to change the table name of an entity as follows on runtime since they have the same table structure after all?

This is not really possible, at least not with standard JPA (which doesn't mean I did it with non standard JPA) as mentioned in questions such as:

To summarize, JPA doesn't offer a way to "alter" a given entity of an already initialized persistence unit (and the related pre-compiled CRUD queries, the pre-compiled named queries, etc).

Still, since you're using Hibernate, maybe have a look at http://www.hibernate.org/171.html to get an idea of what would be possible using Hibernate Core API.

Another option I can think of would be to use a database synonym / alias: FOO would be an alias for FOO_JAN2010 until... you change the alias to point on FOO_FEB2010. I've never tested this, I don't know if it will suit your needs. But it's another idea.

Community
  • 1
  • 1
Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
5

You can probably do it using Naming Strategies if you use Hibernate as JPA provider. See my answer to this previous question for reference.

You should be able to design your naming strategy to return table names in a dynamic way.

The question whether you should do it that way is a completely different one, though.

Also, thanks Pascal for reminding me, this will only work if the EntityManagerFactory is recreated once per month (there are many ways to do that, restarting the webapp being the simplest one)

Community
  • 1
  • 1
Sean Patrick Floyd
  • 292,901
  • 67
  • 465
  • 588
  • I am not sure but I don't think a naming strategy is supposed to return things that change constantly. Maybe I'm thinking too small but CRUD queries, named queries are computed and pre-compiled once for all at EntityManagerFactory creation time, I don't see how a dynamic naming strategy could help. – Pascal Thivent Oct 07 '10 at 13:00
  • Yeah, I thought as much. Makes sense. But if it's a once-per-month thing, some automation to either recreate the entitymanagerfactory or restart the servlet context might be an option – Sean Patrick Floyd Oct 07 '10 at 13:17
  • Of course, I agree on the last part (but I wanted to make sure I didn't miss something obvious about the naming strategy). – Pascal Thivent Oct 07 '10 at 13:54
2

I cannot imagine a good way to map this. If this is a legacy database, you'll have a hard time using JPA to access it.

If, however, the database layout is under your control, then I'd change it. Having multiple tables with the exact same layout is usually bad design. You could achieve the same result by having only one table with extra columns for year and month. Don't forget to put an index on the two columns.

Henning
  • 16,063
  • 3
  • 51
  • 65
  • Initially this was the design(with a year and month columns), but we found that data access is more efficient by doing this. We might have to go back to the previous implementation if needed. – Joopiter Oct 07 '10 at 08:00
  • 1
    @Joopiter: Access shouldn't be any less efficient if you make sure to have effective indexes (on the year and month column and any others you use in where clauses, in the order of relevance to the query). This design could *possibly* become inefficient when the table is so lange the index doesn't fit into the memory of the database server. – Henning Oct 07 '10 at 08:09
  • Horizontal partitioning like this is totally standard for timeseries data. Some databases support it natively now; it allows entire tables to be trivially eliminated from queries if they specify date ranges. – Nick Johnson Jan 21 '13 at 16:59
1

I could not figure this one either, had a similar requirement.

Since my table name changed relatively infrequently (daily) , I ended up defining a DB alias to the "active" table at the RDBMS (I am using DB2) and referenced the table alias name in the @Table annotation.

I am fully aware this is not strictly what the OP asked, but thought I would share the experience.

Denilson
  • 104
  • 5