how do i query from dynamic table (hibernate)

amateur83 used Ask the Experts™
Hi All,

I have a hibernate-mapping :

<class table="TransactionInfo_0902" name="com.object.TransactionInfo">

And my query as below:

 public List getTransactionList()
    throws Exception
   List transactionList = new ArrayList();
    StringBuffer queryStr = new StringBuffer();
    queryStr.append("select transactionInfo ");
    queryStr.append(" from TransactionInfo transactionInfo ");
    queryStr.append(" order by transactionInfo.recDate desc ");
    transactionList = getSession().createQuery(queryStr.toString()).list();
    return transactionList;

My problem is the tables of transactionInfo are in these format :

In March, the february transactions will be stored in transactionInfo_0902
In April, the March transactions will be stored in transactionInfo_0903

Is there anyway i can do for this (without changing the tbl name in hibernate mapping)
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Without changing the mapping you can also use plain SQL and use a ResultTransformer
Top Expert 2009
I think it is poor design to do this in your database (changing table names), this sounds like a use for partitions or views. Changing table names every month makes for maintenance problems. Not sure what database you are using, but look into partitions, if they are available, so that you could use a single table name.

To take the view approach, so your Hibernate mapping would become static, write a view, and reference the view from your hibernate file. The view name will never change, but every month, you can tweak the view SQL.

Assuming Oracle

-- March
create or replace  view v_transaction_info as
  select * from transactionInfo_0902;

-- in April, re-create with
create or replace view v_transaction_info as
  select * from transactionInfo_0903;

You could also write this as a scheduled script and it would run automatically.


Hi mrjoltcola, the database was already in used ever before i take over the project. Anyway, i really like ths suggestion that you gave. Thanks for the help experts :)
Top Expert 2009

If you have problems with the view (inserting or updating does not work like tables) then instead of a view, use a synonym if your database supports them.

Oracle sample:  create synonym v_transaction_info for transactionInfo_0902;

Then drop / recreate the synonym every month. I would probably do that instead of a view, unless the view is readonly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial