aspose file tools*
The moose likes Object Relational Mapping and the fly likes Joining tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Joining tables" Watch "Joining tables" New topic
Author

Joining tables

subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi all,
Could anybody please guide me in joining two tables using Hibernate?
Also, kindly provide me some useful examples on the same.

Thanks in advance,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The documentation covers this pretty thoroughly (with examples). Have you looked there? Is there something more specific you don't understand? Are you getting errors trying this?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Could anybody give me some complete example code on joining two tables using hibernate?
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi,
I don't want to use HQL. without using HQL is it possible to join two tables? if yes, then could you kindly provide me with some example code?

Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

We don't usually give out code examples, primarily because its not the intention of JavaRanch to be a code mill.
But, like I said, you can get examples by reading the Hibernate documentation. There are examples of joins using criteria, HQL and mapping files and annotations in there.
[ September 16, 2008: Message edited by: Paul Sturrock ]
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi,
i would like to know how i would join two tables using Criteria queries. I have searched the net ample number of times but could not find anything on it.

Suppose, i have two tables a1 and b2.

a1 has columns identity(type int) and name(type varchar).
b2 has columns id(type int) and address(type varchar).

i would like to join these two tables using Criteria API and then print the result. Kindly do let me know how i should proceed.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I have searched the net ample number of times but could not find anything on it.

Really? The fficial&client=firefox-a" target="_blank" rel="nofollow">first result in a quick google search gave me an example.

The best place to start looking for an answer to a question like this is in the Hibernate documentation which (as I said before) contains the examples you are looking for.
[ September 16, 2008: Message edited by: Paul Sturrock ]
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968

I guess it depends on what you mean by 'join' two tables?

If you just want to create a foreign key relationship, you do that when you create the tables at the SQL level.

You can map such an association with Hibernate and JPA annotations quite easily, of course, and use a SchemaExport to create the tables that will have foreign key enforced join. Here's one I do on my website (a free tutorial!)







Mapping Two Tables Joined by a Foreign Key

But now you're talking all about a criteria query, so that's a whole other ball of wax. I'd ask if you've tried a criteria query on just one, unjoined, table? It's pretty easy if you've got a good tutorial. Once you understand how to do an Example or Restrictions Criteria on one table or class, don't it with associated classes is a lead pipe cinch.

Tutorial on the Criteria API

Let us know the problems you run into when you try this, and we'll help you out!

-Cameron McKenzie
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
hi,
corresponding to the two tables, i am having two classes:
a1:



and b2:


My hibernate.cfg.xml looks like the following:



and join.hbm.xml looks the follwoing:


In a third class i have to write the logic for creating the join of the two tables. Kindly guide me as to how i should proceed for creating the third class.

Thanks,
Subhash

PS: I don't want to use annotations
[ September 16, 2008: Message edited by: subhashchandra medhiassam ]
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi,
Could anybody kindly give me the code to join these two tables using hibernate?

I would like to use either of Criteria API or HQL queries.
Kindly do let me know.

Thanks,
Subhash
[ September 18, 2008: Message edited by: subhashchandra medhiassam ]
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
@Paul Sturrock

The article( http://www.javalobby.org/articles/hibernatequery102/ ) prescribed by you discusses performing an inner join. But what i am looking for is joining two different tables and not necessarily an inner join.

Could anybody kindly help me with this? I am very,very new to Hibernate.I need help.

Thanks,
Subhash

[ September 19, 2008: Message edited by: subhashchandra medhiassam ]
[ September 19, 2008: Message edited by: subhashchandra medhiassam ]
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
The tables mentioned by me are unrelated to each other. They don't have pk/fk relationship.

And i want to use Criteria queries.

Could anybody kindly give me some code examples?

Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


The tables mentioned by me are unrelated to each other. They don't have pk/fk relationship

So why perform a join? Why are you trying to relate unrelated tables? Could you not just use two queries?

The only valid join in these circumstances is a UNION which isn't yet supported in HQL, so I would be surprised if you could do it with Criteria.
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi all,
I am modifying my question.

I have two tables in MYSQL: department and employee.

department has columns departmentId(type int) and name(type varchar).
employee has columns employeeId(type int), name(type varchar), job(type varchar) and departmentId(type int). The attribute departmentId of table employee acts as the foreign key for table department.

Now, i want to join the two tables department and employee using Criteria API. how should i proceed?

Thanks,
Subhash
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Corresponding to the two tables i have two classes: employee.java and department.java.

employee.java looks like the following:



department.java looks like the following:



Now in a third class i am trying to join the two tables. The code fragment looks like this:



Is this List object going to contain the values obtained through the joining of tables employee and department? If not then how should i retrieve the values obtained through joining tables employee and department.

Kindly help.

Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The class Department has no association with Employee, so the fetch is meaningless. There may be a relationship in the data model, if there is and you want this relationship to be reflected in your object model you will need to map the association.

See the Hibernate documentation on associations to learn how to do this.
[ September 23, 2008: Message edited by: Paul Sturrock ]
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
My mapping file join.hbm.xml looks like the following:



When i run the program, however, i am getting the follwoing message:


org.hibernate.MappingException: An association from the table employee refers to an unmapped class: int
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:932)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:888)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1036)
at Criteria2.main(Criteria2.java:19)

Why is it so? Where did i go wrong? Could anybody kindly bail me out?

Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Reading the Hibernate documentation on associations really would help.


OK, lets look at what you have here:


This is a mapping for a class called employee, which has the following properties and types:
  • employeeId (int)
  • name (String)
  • job (String)
  • departmentId (int)
  • departmentId (department)


  • The last two look suspect - Hibernate is complaining because you have two properties mapped to the same field one should be read only.

    If you think about it, based on what you have, I'd expect a class that looked like this:

    You will see that this class in not valid Java.

    What you've defined here:

    is known as a unidirectional many-to-one association. If you look in the documentation for an example you'll find a good right at the top of the section on mapping associations.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    Now, i have changed my hbm.xml file so that it looks like the following:



    When i run my main class which looks like the following:



    i am getting the following message:



    Where did i go wrong? What should i do? Kindly help.

    Thanks,
    Subhash
    [ September 25, 2008: Message edited by: subhashchandra medhiassam ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    <many-to-one name="departmentId" column="departmentId" not-null="true" />

    You have elected not to use the class attribute in your many-to-one element which means the type at the other end of the association is determined by reflection. So my guess is your mapped employee class has a property of departmentId whose type is an int?
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    My employee class looks like the following:




    I have a property departmentId of type int.

    Kindly help.

    Thanks,
    Subhash
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    If you read the documentation for many-to-one you will see that you can use the class attribute to define the type the association maps to. As I said above if you don't use this attribute Hibernate determines the type by reflection - based on the type of the property this association maps to. So, given the type of the property departmentId is int, Hibernate is trying to create an association to a mapped class called "int". There isn't one, so it fails.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    Okay,I have changed my hbm.xml file so that it now looks like the following:



    But when i run my program now, i am getting the following message:



    Kindly help.

    Thanks,
    Subhash
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Did you change the type of your departmentId property?
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    No, i didn't. It is still of type "int" in the classes and also in the mapping file.
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    It is not of type int in the mapping file. You've (correctly) changed the class the association maps to to be of the type department.

    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    But you asked about the property departmentId.what should i do now?

    Kindly help.

    Thanks,
    Subhash
    [ September 25, 2008: Message edited by: subhashchandra medhiassam ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Yes, I did. And I posted the bit of your mapping file that maps the associated class department to this property. You need to fix your employee class so it correctly reflects the object model you have mapped in your mapping file.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    Well, i changed my employee class and made the type of the property departmentId as department. When i run my program, it is executing properly.

    Now, my question is that how do i retrieve the values from the list? The list would be having joined values from both the tables, employee and department.

    Also, suppose i want to join the two tables in such a way that i keep the type of field departmentId in employee class as int only then what modifications should i bring in the mapping file?


    Kindly help.
    Thanks,
    Subhash
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    Okay,
    I am able to retrieve the values of the table department from the join of the two tables. Thanks a lot for the help, Paul.

    But i have a question.Is it necessary that in a many-to-one association, to have a join of the two classes the class corresponding to the "many" portion of the relation should have a property of type of the class corresponding to the "one" type of the relation?

    Kindly do let me know.

    Thanks,
    Subhash
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    Now, my question is that how do i retrieve the values from the list? The list would be having joined values from both the tables, employee and department.

    Its just a List - treat it as you would any List in Java.


    Also, suppose i want to join the two tables in such a way that i keep the type of field departmentId in employee class as int only then what modifications should i bring in the mapping file?

    Not sure I see a reason to do this, what are you trying to acheive?
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    I have changed the employee class's departmentId field's type to department.

    Then should i also not change the table employee? what should be the type of the column "departmentId" of table employee??

    I mean to say that departmentId column in employee table is having type int though in the class employee i have changed the type of property departmentId to department. Isn't it an error? Then how should the table employee be changed?

    Kindly advise.

    Thanks,
    Subhash
    [ September 25, 2008: Message edited by: subhashchandra medhiassam ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    Then should i also not change the table employee? what should be the type of the column "departmentId" of table employee??

    No. Your object model should not be influencing your ER model. This is one of the purposes of an ORM tool.


    I mean to say that departmentId column in employee table is having type int though in the class employee i have changed the type of property departmentId to department. Isn't it an error?

    No. This is one of the basic reasons why you would use Hibernate. As I have said a good few times before, I think you would really benefit from reading the Hibernate documentation. You need to understand what the tool you are using is for; a lot of the answers for your questions will follow from that.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    My table employee looks like the following:

    Column Type
    -------------- -----------------
    employeeId int
    name varchar
    job varchar
    departmentId int


    How should i modify the table?

    Kindly help.

    Thanks,
    Subhash
    [ September 25, 2008: Message edited by: subhashchandra medhiassam ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Like I said in my previous post, you shouldn't.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    okay.

    Now in my main program, i am retrieving the values like this:



    The above code prints all the values of column job available in table employee.
    To print the values of fields of the department table obtained through join, what i have to do is use a line of code like this:



    The above line prints out the values in the column name of table department.

    Similarly, i can get the values of the departmentId of table department by using the following line of code:



    I can also get the values of table employee, obtained through join, by using the following statements:



    It all works fine.

    But when i try to get the departmentId of table employee by using the following statement , i get some garbage values. Why is it so?

    Kindly help.

    Thanks,
    subhash
    [ September 25, 2008: Message edited by: subhashchandra medhiassam ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    But when i try to get the departmentId of table employee by using the following statement:

    i get some garbage values. Why is it so?

    Your employee class has a property called departmentId which is of the type department. As with any type in Java, if you call:

    the toString() method is called on the object "myType". Unless you have implemented a toString() method for your department class you will be using the one supplied by Object.
    subhashchandra medhiassam
    Ranch Hand

    Joined: Sep 10, 2008
    Posts: 72
    Thanks a lot, Paul. You have been extremely helpful. Your suggestions have helped me a lot. In the future if i need any help, i shall bother you again.

    Thank you once again,
    Subhash
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Joining tables
     
    Similar Threads
    Choice of Implementation
    Regarding Hibernate Query
    sql query returning duplicates
    distinct in select query
    ejbhome