how to perform query on multiple tables dynamically?

legolas_wood · **Joined:** Fri Nov 17, 2006 7:24 pm **Posts:** 16

Hi
Thank you for reading my post
My question is mostly technical and i want to know whether hibernate or EJB 3.0 can do this or not.
we have an application which we are going to port it to Hibernate or JPA from plain JDBC.

in our application, we have tables which are created after we complete the code, those tables contains some data which we do not know about its fields and.... in our application design time.

what we have in runtime is a set of metadata that describe those tables, for example thier fields and .....

now we want to be able to query those table with HQL or EJBQL , meanwhile we have no entity mapped for those table because they are created after we designed the application,

What i want to know is:
-how we can query them,
-what will be the query result? it is a collection? if it is a collection, then what are collection elements?

Thanks

batmat · **Posted:** Sat Dec 16, 2006 9:25 am

Maybe it's possible to try and use/hack the sessionFactory to be able to reparse some informations from db to map it dynamically.

In which type of class do you put those dynamic data? I think it's possible to do sql-queries and precise the result-type. If not possible to know the result-type, just do sql-queries dynamically generated, it will return an Object array that you will have to retrieve in a quite jdbc style... But that's maybe what you want to avoid. :-)

legolas_wood · **Joined:** Fri Nov 17, 2006 7:24 pm **Posts:** 16

What we know is, data type and column name of each column that are used in the query.

can you explain more about your reply?
if we use an sql string then it will be like using plain JDBC, am i right?
what we want to achieve with moving to hibernate/ JPA(EJB 3.0) is using container provided facilities like security, transaction and....

thanks

batmat · **Posted:** Sat Dec 16, 2006 10:18 am

legolas_wood wrote:

if we use an sql string then it will be like using plain JDBC, am i right?

No, you'll still be able to use some feature of Hibernate, like being able to ask it to automatically convert the result into an object list, instead of a list of arrays.

You'd be doing plain jdbc if retrieving the connection from the session and the using it as if Hibernate was not used.

Instead, if, for example, you can map a named query in the hbm.xml, you'd be able to do something like

Code:

Query q = session.getNamedQuery("myQuery");
q.setParameter(.....);
q.setParameter(.....);
List<MyClass> myClasses = q.list();

for(MyClass currentClass : myClasses)
{
...
}

As you see, it'd be a bit simpler less repetitive than jdbc, not having to iterate over the resultset to retrieve each column to build each object :-).

legolas_wood wrote:

what we want to achieve with moving to hibernate/ JPA(EJB 3.0) is using container provided facilities like security, transaction and....

Well, I guess you're speaking about things I know only by name like Seam or Hibernate Entity Manager, because I didn't know you could have security or tx management by using Hibernate.

We also use automatic tx demarcation, but we use Spring for this.