asked on

how do i query from dynamic table (hibernate)

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)

mahome

Without changing the mapping you can also use plain SQL and use a ResultTransformer

ASKER CERTIFIED SOLUTION

mahome

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amateur83

ASKER

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 :)

mrjoltcola

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.