Fetching values from a query which join two tables

jayakumar · **Joined:** Thu Apr 27, 2006 5:49 am **Posts:** 4

Hi,

I have written q query in hibernate for joining two tables and it return a list. By retrieving the values from the list, i am unable to know the instance object type.
I have written the code like this..

list = session.find("select folder.foldername,folderfiles.filename from uploader.registration.Folder folder,uploader.registration.Folderfiles folderfiles where folder.folderid=folderfiles.folderid and folder.userid='"+username+"'");
System.out.println("list size="+list.size());//it is showing the size correctly.
for(Iterator itr =list.iterator();itr.hasNext();)
{
Folder f=(Folder)itr.next();
f.getFolderName();
}

Here i am getting the ClassCastException. then I have used like this.
if(itr.next() instanceof Folderfiles)
//some code
if(itr.next() instanceof Folder)
//some code.
After adding this nothing happens and if(itr.next() instanceof Folderfiles) stmts are not excecuting. Can anybody please help to fetch records from two tables from the above query.

Thanks,
Jaya.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

That query will return an Object[2], where Object[0] is the foldername (a String, I guess), and Object[1] is filename (also a String?).

You are always able to find out what type is in the list by printing itr.next().getClass().getSimpleName() or equivalent.

That query looks a bit odd. Shouldn't Folder be mapped with a colleciton of Files? So your query would simply be "from Folder where user = :user"? Then to get the files, you'd use something like

Code:

Query qry = session.createQuery("from Folder where user = :user");
qry.setEntity("user", user);
List<Folder> folders = qry.list();
for (Folder folder : folders)
{
  Collection<File> files = folder.getFiles();
  ...
}

Also, don't use string buiding to pass parameters, use named parameters.

jayakumar · **Joined:** Thu Apr 27, 2006 5:49 am **Posts:** 4

hi,

I am new to hibenate and I could not understand your answer. I could not find out where we have join the tables in the code u have given. Could you please be more eloberate sothat we can understand it clearly. Sorry for troubling you.

Thanks a lot.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I am assuming that you have mapped Folder with a colleciton of Files, like this:

Code:

<class name="Folder" ...>
  ...
  <set name="Files" ...>
    <key column="folderId"/>
    <one-to-many class="File"/>
  </set>
  ...
</class>

That's where the join is specified. That way, hibernate does all the joining for you when you create a query like this

Code:

from Filter f where f.File.name = :filename

So you don't specify a join in the query.

In the query that you wrote, the select specified "folder.foldername, folderfiles.filename". This will cause the object returned from query.list() to be an array of Objects, where the first element is a String with the name of the folder in it, and the second element is a String with the name of the file in it. While that may be sufficient, you would prefer Folder objects, where each Folder has the correct File objects in the getFiles() collection. That's what my query does.

If you need to know more, check out the hibernate ref docs.