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

Is it neccessary to define -@onetomany and -@joincolumn in the entity class to use joins in hql? — Aadam
– Aadam, Commented Apr 26, 2013 at 19:50

Max Dron · Accepted Answer · 2013-04-26 23:35:05Z

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

edited Apr 26, 2013 at 23:35

answered Apr 26, 2013 at 18:03

Max Dron

813 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aadam Over a year ago

how to map entities in this case? please let me kno

Aadam Over a year ago

Is it neccessary to define -@onetomany and -@joincolumn in the entity class to use joins in hql? @Max Dron

Ashish Ratan · Accepted Answer · 2013-04-26 16:13:09Z

0

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

answered Apr 26, 2013 at 16:13

Ashish Ratan

2,8661 gold badge26 silver badges51 bronze badges

4 Comments

Aadam Over a year ago

hi. i edited the question once have a look, i tried with similar query. and output also there

Ashish Ratan Over a year ago

why are you using id column? plz provide little description, to make clear the question.

Aadam Over a year ago

is that possible using single query like join ?

Ashish Ratan Over a year ago

inner jopin will be helpful

Aadam · Accepted Answer · 2013-04-26 17:54:12Z

0

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)

answered Apr 26, 2013 at 17:54

Aadam

1,5419 gold badges32 silver badges63 bronze badges

1 Comment

Aadam Over a year ago

how to do the same with hibernate annotations

Collectives™ on Stack Overflow

hibernate join table mysql

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related