Hibernate: Data Object with a dynamic table name by Annotations

Question

I have a Data Class for Hibernate associated to a table; imagine the Entity Person like this:

 @Entity
 @org.hibernate.annotations.Proxy(lazy=false)
 @Table(name="Person", schema="MySchema")
 @Inheritance(strategy=InheritanceType.SINGLE_TABLE)
 public class ProfileData implements Serializable {

    private static final long serialVersionUID = -844564646821609090L;

    public PersonData() {
    }

    @Column(name="idPerson", nullable=false, unique=true)   
    @Id 
    ...

I need to create historic tables by years of this table: Person2010, Person2011, Person2012... Is it possible without creating new Data Objects? Maybe by a parameter...? I don´t know.

The Entity class is the same, changing the table name and the constructor.

ganzux · Accepted Answer · 2012-01-02T16:03:12.767

Another one Architecture, more complez but elegant:

YES, You can change the table names using NamingStrategies:

public class MyNamingStrategy extends DefaultNamingStrategy {
   ...
   @Override
   public  String tableName(String tableName) {
      return tableName+yearSuffixTable;
   }
   ...
}

And, when you wanna to use the _year tables, you must to create a session with Hibernate that override rhe table names:

  SessionFactory sessionFactory;
  Configuration config = new AnnotationConfiguration()
                         .configure("hibernate.cfg.xml")
                         .setNamingStrategy( new MyNamingStrategy () );
  sessionFactory = config.buildSessionFactory();
  session = sessionFactory.openSession();

For my architecture I create a session by year and store it into Application map for access when I need it.

Thanks.

Is this strategy create a new table(with new name) or just change the name of the old table? — Khalid Bin Huda, Jun 22 '22 at 07:58
Hi @KhalidBinHuda! As far as I remember, this was to change the name of the table you were connecting to :)! — ganzux, Jun 23 '22 at 01:02

score 3 · Answer 2 · answered Dec 20 '11 at 09:49

3

You should try Hibernate Envers for historic data.

answered Dec 20 '11 at 09:49

tobiasbayer

10,269
4
46
64

Thanks you. I am going to study it for my app. If it solves my problem, I will set your answer as correct :) – ganzux Dec 20 '11 at 12:45
Envers is easy and maybe the solution but... How can I set the Audited table suffix dynamicly? I need to separate the data by years because I have million of data per table and I need to search on. Thanks – ganzux Dec 21 '11 at 16:00
Why can't you just search on one table? A good DBMS should be able to handle this. – tobiasbayer Dec 21 '11 at 19:47
I can´t. I need the data stored in a table by year. It´s more efficient. I´ll try to create a session by year with different configurations of hibernate... – ganzux Dec 22 '11 at 08:23
Why are you so sure that it's more efficient to have separate tables? – tobiasbayer Dec 22 '11 at 09:39
The table I need to crop has more than 40 million of records by year. If I need to search data form 2010 I think it´s more efficient find it in a historioc_table_2010 of 40 million of records than in one big table of 400 million (10 years) – ganzux Dec 22 '11 at 10:14

score 1 · Answer 3 · answered Apr 17 '17 at 07:59

1

Modified at runtime(I think it's the best way):

Session session = super.getSession();
    SQLQuery query = session.createSQLQuery("raw sql");
    query.setParameter(":abc", "value");
    query.addEntity(Some.class);
    return query.list();

answered Apr 17 '17 at 07:59

helloworld1970

69
3

This is a SQL query. This does not work well on multiple DBs (Oracle DB, Microsoft SQL Server, Sybase). It's very easy to write SQL code which does not work on multiple DBs. – OGrandeDiEnne Sep 14 '20 at 09:27

michal.jakubeczy · Answer 4 · 2022-06-16T09:07:45.903

You can use Hibernate interceptors to change the table in the generated SQL statements.

For your case you can define your table class like this:

@Entity
@org.hibernate.annotations.Proxy(lazy=false)
@Table(name=PersonTableNameReplacer.PLACEHOLDER, schema="MySchema")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
public class ProfileData implements Serializable {

and define your Hibernate interceptor in a following way:

public class TableNameReplacer extends EmptyInterceptor {

    public static final String TABLE_PLACEHOLDER = "{person_table_placeholder}";

    @Override
    public String onPrepareStatement(String sql) {
        if (sql.contains(TABLE_PLACEHOLDER )) {
            String replacement = "{your logic to fill proper table name}";


            sql = sql.replace(TABLE_SUFFIX_PLACEHOLDER, replacement);
        }

        return super.onPrepareStatement(sql);
    }

Using this approach you're free to modify generated SQL and replace the table name there as you wish.

I recommend to use good placeholder value which you're sure will not be a part of actual values being saved to the table (or you can only limit this to select statements if you only read the data).

score 1 · Answer 5 · answered Dec 20 '11 at 09:56

1

In Hibernate you map 1 class to 1 table. You can not reuse the same Entity to map several tables dynamically.

Hibernate Envers is a quite good solution for Historic data, but you still will not be able to do what you try (dynamically grow the number of tables without touching mapper Entities).

answered Dec 20 '11 at 09:56

Mr.Eddart

10,050
13
49
77

Thanks you. I am going to study it for my app. If it solves my problem, I will set your answer as correct :) – ganzux Dec 20 '11 at 12:45
Envers is easy and maybe the solution but... How can I set the Audited table suffix dynamicly? I need to separate the data by years because I have million of data per table and I need to search on. Thanks – ganzux Dec 21 '11 at 16:01
You cannot. ORMs doens't offer you that level of flexibility, if you really want to go with that **bad** design that you propose, then you will have to renounce to Hibernate and Envers and use direct SQL. – Mr.Eddart Dec 22 '11 at 21:06
Well, bad design without knowing the entire design is a little arrogant. And I CAN do what I need loading an hibernate config file depending by the year I choose. – ganzux Dec 26 '11 at 12:43

ganzux · Answer 6 · 2011-12-26T12:56:39.113

Thanks to @CodeBrickie and @edutesoy I found Envers.

I configure the hibernate config file with AUD suffix and I create new hibernate config files per year (hibernate.cfg.2009.xml, hibernate.cfg.2010.xml, hibernate.cfg.2011.xml...) with the year-suffix.

When I save data, always is audited in AUD table. On January 1, automatically:

_AUD TABLE is renamed as _PAST_YEAR table.
A new _AUD table is created.
A new hibernate.cfg.past_year.xml is created with the new suffix.

When I need to get data, I load the corresponding hibernate configuration file.

Hope this helps to others :)

score 1 · Answer 7 · edited Feb 09 '12 at 08:25

1

@Table(name="emd_employee_1001")

In the above annotation file name can pass as parameter, for example

x=1001 
@Table(name="emd_employee_+x+")

edited Feb 09 '12 at 08:25

Aliaksei Kliuchnikau

13,589
4
59
72

answered Feb 09 '12 at 08:19

praveen

11
1

3

Yeah, ok, but once the Data Object is initialized, I can´t change the name. – ganzux Feb 16 '12 at 14:32

Hibernate: Data Object with a dynamic table name by Annotations

7 Answers7

Linked