Rolling Table Names

Leesy · **Posted:** Wed Aug 15, 2007 10:33 am

Hibernate version: 3.2
Name and version of the database you are using: MySQL 5.0.44

Hi all,

I'm using Hibernate to write a logging system. We're storing the data in a database and the idea is to store each months data in it's own table. So for example - logs200701, logs200702, logs200703 and so on.

Does anyone have any ideas on how I can achieve this kind of naming strategy? Is this something I can do via Hibernate or am I looking at SQL commands either from Hibernate or via a trigger on the DB.

Thanks for any input,
Lee

Leesy · **Posted:** Thu Aug 23, 2007 10:33 am

Bit of a bump for this topic. I've got so far but hopefully someone can give me a hand getting over that final hurdle.

I've gotten over my table naming problem using a NamingStrategy. Here's the code I'm using:

Code:

public String tableName(String tableName) {
  String result = tableName;
  if (tableName != null && tableName.endsWith(WILDCARD)) {
    Calendar cal = Calendar.getInstance();
    int imonth = cal.get(Calendar.MONTH)+1;
    String month = (imonth < 10 ? "0"+ imonth : ""+ imonth);
    String year = "" + cal.get(Calendar.YEAR);
    result = result.substring(0, result.length() - 1) + year + month;
  }

  return result;
}

So if I have a entity like...

Code:

@Entity
@Table(name = "search_log_*")
public class LogEntry implements ILogEntry {
  ...
}

When it hits the DB it will actually be looking for a table called search_log_200708. That's great and what I want.

The trouble is that once the month ticks over, the table name won't match any table that exists in the table. It won't have been created yet.

Does anyone have any tips on how I could get Hibernate to automatically recreate the table in an efficient way?

MikePloed · **Posted:** Thu Aug 23, 2007 10:41 am

Why don't you just use an alias for the table in the database. This would work transparently for Hibernate.

Which DB are you using?

Leesy · **Posted:** Thu Aug 23, 2007 11:15 am

MySQL. I'm not familiar with aliases on a DB (unless you mean when I do something like "select e.* from entry as e" with e being the alias), have you got an example?

If I'm thinking of the right kind of aliases then I don't really see show I can use them to map to multiple tables. But then again, I have a stinking headache right now and can't think straight anyway :)