hibernate join table mysql

Question

I have two simple tables.

    mysql> select * from filesshare;
+----+--------+--------+-------+
| id | userId | fileId | owner |
+----+--------+--------+-------+
|  1 |      2 |      1 |     1 |
|  2 |      3 |      1 |     1 |
|  3 |      4 |      2 |     1 |
|  4 |      5 |      2 |     1 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)

mysql> select * from filesinfo;
+----+-----------------+-----------------------------------+
| id | name            | url                               |
+----+-----------------+-----------------------------------+
|  1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
|  2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
+----+-----------------+-----------------------------------+
2 rows in set (0.01 sec)

i will have now two values as input. 1) userId 2) owner

now assume userId=3 and owner=1. Now i want out put to match all the rows with given values in filesshare table in this case its second row that is " 2 | 3 | 1 | 1 " now i want that column three value which is 1 now. Then with that value i want to retrieve the data from filesinfo table. in this case the first row of filesinfo table will b the output. like this:

+----+-----------------+-----------------------------------+
| id | name            | url                               |
+----+-----------------+-----------------------------------+
|  1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
+----+-----------------+-----------------------------------+

is that possible using joins using hibernate, a simple example can be a kickstart for me.

i tried this

mysql> Select fileid,name,url from filesshare fs,filesinfo fi where fs.fileid=fi
.id;
+--------+-----------------+-----------------------------------+
| fileid | name            | url                               |
+--------+-----------------+-----------------------------------+
|      1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
|      1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
|      1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
|      1 | dwnld_btn.png   | C:\shareapp\admin\dwnld_btn.png   |
|      2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
|      2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
|      2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
|      2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
+--------+-----------------+-----------------------------------+

Thanks and regards

Answer 1

in hibernate you can deal with it two ways:

• use plain sql

  example: SQLQuery query = session.createSQLQuery("select fi.* from fileshare fs, filesinfo fi where fs.fileId =fi.id and fs. userId = :uid and fs.owner = :oid"); query.setInteger("uid", userid); query.setInteger("oid", ownerid);

     /*maybe deal with transformation */
     query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);)
     return  (List<Map<String,Object>>)query.list();   
  

  here is the link with info on transformation details: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-associations

• use hql assuming you have entities mapped to

  example:

    Criteria crit =  session.createCriteria(Filesinfo.class)
    crit.setFetchMode("fs.fileshare", FetchMode.JOIN);
    crit.add( Restrictions.eq("fs.userId", userid) Restrictions.eq("fs.owner", ownerid) );
    return crit.list();
  
  all info on this method is here: http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/queryhql.html
  
   and here is the details of teh ORM mapping via xml: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/xml.html
  

Answer 2

k guys this one one after scratching head, of course help from google also.

     mysql> select a.id, a.name, a.url from filesinfo a inner join filesshare b on 
a.id=b.fileid where b.userid=5 and b.owner=1;
    +----+-----------------+-----------------------------------+
    | id | name            | url                               |
    +----+-----------------+-----------------------------------+
    |  2 | dwnld_btn_1.png | C:\shareapp\admin\dwnld_btn_1.png |
    |  4 | loader3.gif     | C:\shareapp\admin\loader3.gif     |
    +----+-----------------+-----------------------------------+
    2 rows in set (0.02 sec)

Answer 3

Select id,name,url from fileshare fs,filesinfo fi where fs.id=fs.id;