use "join" or "many-to-one"?

alskor · **Posted:** Wed Jul 12, 2006 6:17 am

I have a general question about mapping data in Hibernate. I think that's an easy question to answer. I just need a general idea.

I have 3 linked tables:

---------------------
table test: (stores some tests)
id - primary key
name
--------------------

table testresult: (stores the testing results for each test)
(test_id, testingdate) - composite primary key
message,
server_id
----------------------

table server: (stores the servers where the testing was performed)
(id) - primary key
servername
---------------------

I was able to build an application, which uses all this, but I see it is very inefficient.
I have classes TestBean, TestResultBean, TestResultBeanPK, ServerBean.

What I'd like to have is when I query test results, I get a full object using a single sql "select" operation.
But what I see in logs is - separate sql "select" operations are executed - to get the test result, then to get server, etc (I have other linked tables similar to this "server" table).

Code:

that's a piece of my current  TestResultBean.hbm.xml file:
        <many-to-one name="server" class="ServerBean" 
             column="server_id" lazy="false"/>

I had to use "lazy=false" to avoid "session is already closed" error because I need to display the results on a web-page,

my questions are:
1. is it possible to have a "join" construction for testresult mapping?
I see this paragraph in the tutorial, but it does not describe my situation well:

Quote:

5.1.18. join

Using the <join> element, it is possible to map properties of one class to several tables.

Unfortunately, there's no clear explanation what this "join" element should be used for.
The problem with this "join" is that when I try to apply this, I'm getting error:
"Foreign key must have same number of columns as the referenced primary key".
That's the join construction I'm trying to experiment with:

Code:

piece of TestResultBean.hbm.xml
           
        <join table="server">
      <key column="server_id"/>
           <many-to-one name="server"
            column="id" 
            not-null="true"/>
        </join>-->

so, should I use this "join" if I want to get the test results to be fetched using a single sql query instead of current multiple "selects"?
if yes, then how to use it properly?

2. should I add "id" column to "testresult" table? Section "5.1.4" of the tutorial says:

Quote:

Mapped classes must declare the primary key column of the database table.

of course, I'd prefer to use my current composite ID as see no real need in new "id".

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

What you are looking for is pretty easy! (isn't that the best)

I've never used the <join ...> syntax, but have accomplished joins on my objects by adding fetch="join" or outer-join="true" to the syntax, depending I think on your Hibernate version

Code:

        <many-to-one name="server" class="ServerBean"
             column="server_id" fetch="join" lazy="false"/> 

or

Code:

        <many-to-one name="server" class="ServerBean"
             column="server_id" outer-join="true" lazy="false"/> 

Please rate posts...

dan

alskor · **Posted:** Thu Jul 13, 2006 12:50 am

dslevine wrote:

fetch="join"

Code:

        <many-to-one name="server" class="ServerBean"
             column="server_id" fetch="join" lazy="false"/> 

I'm using the latest Hibernate (3).
thanks! I have read the documentation - it looks like "fetch" is exactly what I need.
but after adding fetch="join" to TestResultBean.hbm.xml I still see the same (!) "select" operators in output, which is strange.

After some thinking I believe this is because of the way how I retrieve the test results:

Code:

   public static List getTestResults(Session session, Long testId,
         Date dateFrom, Date dateTo) {
      String queryString = "from TestResultBean where test_id = :testId ";
      if (dateFrom != null) {
         queryString += " and testingdate>= :dateFrom ";
      }
      if (dateTo != null) {
         queryString += " and testingdate<= :dateTo ";
      }
      Query query = session.createQuery(queryString);

      query.setLong("testId", testId);
      if (dateFrom != null) {
         query.setDate("dateFrom", dateFrom);
      }
      if (dateTo != null) {
         query.setDate("dateTo", dateTo);
      }
      List list = query.list();
      return list;
   }

So, maybe for this fetch="join" to work, I need to retrieve the test results in other way, not by using a hand-made query?
I could use test.getTestResults() and then filter the results in a Java cycle, but that's obviously not a good idea.

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

First, it absolutely work for a handmade query. (For many other reasons, you might want to replace that with non-handmade queries, but this should work.)

Thinking about it further, try to add

outer-join="true"

to the sets also. Fetch might just tell it when to fetch it and not how to fetch it. I think outer-join on all of the object fields in the mapping file will fix you up.

alskor · **Posted:** Fri Jul 14, 2006 3:23 am

dslevine, thanks again!

UPDATE:

it still does not work, even with outer-join="true". I mean, it works, but with multiple "selects", not with joins.
when I modify my java code to use test.getTestResults(), then (AT LAST!) I see JOIN statements in the output, but if I manually select the test results using the query, it still works with "SELECT", not with "JOIN".

I even added this parameter to hibernate.cfg.xml:
<property name="hibernate.max_fetch_depth">2</property>
and checked hibernate.log that it accepts this parameter:
INFO SettingsFactory:184 - Maximum outer join fetch depth: 2

but JOIN still does not work when using my custom query.

alskor · **Posted:** Fri Jul 14, 2006 3:31 am

oh, I forgot - I also had another question:

2. should I add "id" column to "testresult" table? Section "5.1.4" of the tutorial says:

Quote:

Mapped classes must declare the primary key column of the database table.

so, should I always add ID column to tables used by Hibernate?
of course, I'd prefer to use my current composite ID as see no real need in new "id".
but this is a new database and I can modify it, if this is needed for Hiberenate to work faster, or whatever.

alskor · **Posted:** Fri Jul 14, 2006 5:40 am

uhhh, I finally found a way how to search for test results WITHOUT a custom query. I did it this way:

Code:

      Criteria criteria = session.createCriteria(TestResultBean.class).add(
            Restrictions.eq("id.testId", test.getId()));
      if (dateFrom != null) {
         criteria.add(Restrictions.ge("id.testingDate", dateFrom));
      }
      if (dateTo != null) {
         criteria.add(Restrictions.le("id.testingDate", dateTo));
      }
      testResultsList = criteria.list();

and I just checked that this variant finally uses JOINs!

adamtarshis · **Joined:** Mon May 08, 2006 1:01 pm **Posts:** 5

fetch="join" in the mapping file is ignored by hibernate when you use an hql query. However, as you have noticed, it does impact criteria queries (as well as most other forms of querying in hibernate - see the advanced faq).

To get outer joins in hql queries, you need to explicity include fetch join statements for the tables and properties you are interested in.