Hibernate Community • View topic - left join fetch query returns 0 rows

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

left join fetch query returns 0 rows

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

mhnagaoka

Post subject: left join fetch query returns 0 rows

Posted: Thu Jul 15, 2004 3:35 pm

Newbie

Joined: Thu Jul 15, 2004 2:47 pm
Posts: 2

Hello gurus,

I have this simple model for testing:

State 1-N City

When I issue the query:

Quote:

from zzz.State

it issues these statements:

Code:

16:17:29,133 DEBUG SQL:237 - select state0_.id as id, state0_.name as name from
zzz_state state0_
16:17:29,148 DEBUG SQL:237 - select cities0_.id as id__, cities0_.state_id as st
ate_id__, cities0_.id as id0_, cities0_.name as name0_, cities0_.state_id as sta
te_id0_ from zzz_city cities0_ where cities0_.state_id=?
16:17:29,148 DEBUG SQL:237 - select cities0_.id as id__, cities0_.state_id as st
ate_id__, cities0_.id as id0_, cities0_.name as name0_, cities0_.state_id as sta
te_id0_ from zzz_city cities0_ where cities0_.state_id=?

and correctly returns the 2 states stored in the database.

However, in order to reduce the SQL queries issued to the database, I'm trying the following query:

Quote:

from zzz.State as state left join fetch state.cities

The issued SQL statement appears to be correct:

Code:

16:17:50,694 DEBUG SQL:237 - select state0_.id as id0_, cities1_.id as id1_, sta
te0_.name as name0_, cities1_.name as name1_, cities1_.state_id as state_id1_, c
ities1_.id as id__, cities1_.state_id as state_id__ from zzz_state state0_ left
outer join zzz_city cities1_ on state0_.id=cities1_.state_id

but nothing is displayed in Hibern8IDE.

This is the source for zzz.State:
[code]
package zzz;

import java.util.Set;

/**
* zzz_state
*
* @hibernate.class table="zzz_state"
*/
public class State {

private Long id;

private String name;

private Set cities;

/**
* Obt

Top

mhnagaoka

Post subject: Solved!

Posted: Fri Jul 16, 2004 9:21 am

Newbie

Joined: Thu Jul 15, 2004 2:47 pm
Posts: 2

I ran those queries above manually (not in Hibern8IDE) and it worked perfectly.

This is the code I used to test the queries:

Code:

package zzz.test;

import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import zzz.State;

import net.sf.hibernate.Query;
import net.sf.hibernate.Session;

import com.cpqd.commons.command.hibernate.HibernateUtil;

import junit.framework.TestCase;

/**
 * Test case para State 1-N City
 */
public class CityStateTest extends TestCase {

  public static final Logger log = Logger.getLogger(CityStateTest.class);
  
  private static final String configPath = "/hibernate-dev.cfg.xml";

  private Session sess;

  protected void setUp() throws Exception {
  }

  protected void tearDown() throws Exception {
  }
  
  public void testQueryStates() throws Exception {
    sess = HibernateUtil.currentSession(configPath);
    Query q = sess.createQuery("from State state");
    List states = q.list();
    HibernateUtil.closeSession();
    
    assertEquals(2, states.size());
    for (Iterator i = states.iterator(); i.hasNext();) {
      State state = (State) i.next();
      log.info(state.getName() + ": " + state.getCities().size());
    }
  }

  public void testQueryStatesLeftJoinFetch() throws Exception {
    sess = HibernateUtil.currentSession(configPath);
    Query q = sess.createQuery("select state from State as state left join fetch state.cities");
    List states = q.list();
    HibernateUtil.closeSession();
    
    assertEquals(5, states.size());
    for (Iterator i = states.iterator(); i.hasNext();) {
      State state = (State) i.next();
      log.info(state.getName() + ": " + state.getCities().size());
    }
  }
}

And this is an excerpt of the log file:

Code:

14:17,337 DEBUG SQL:237 - select state0_.id as id, state0_.name as name from zzz_state state0_
14:17,399 DEBUG SQL:237 - select cities0_.id as id__, cities0_.state_id as state_id__, cities0_.id as id0_, cities0_.name as name0_, cities0_.state_id as state_id0_ from zzz_city cities0_ where cities0_.state_id=?
14:17,415 DEBUG SQL:237 - select cities0_.id as id__, cities0_.state_id as state_id__, cities0_.id as id0_, cities0_.name as name0_, cities0_.state_id as state_id0_ from zzz_city cities0_ where cities0_.state_id=?
14:17,477  INFO CityStateTest:43 - sp: 3
14:17,477  INFO CityStateTest:43 - rj: 2
14:17,508 DEBUG SQL:237 - select state0_.id as id0_, cities1_.id as id1_, state0_.name as name0_, cities1_.name as name1_, cities1_.state_id as state_id1_, cities1_.id as id__, cities1_.state_id as state_id__ from zzz_state state0_ left outer join zzz_city cities1_ on state0_.id=cities1_.state_id
14:17,508  INFO CityStateTest:56 - sp: 3
14:17,508  INFO CityStateTest:56 - sp: 3
14:17,508  INFO CityStateTest:56 - sp: 3
14:17,508  INFO CityStateTest:56 - rj: 2
14:17,508  INFO CityStateTest:56 - rj: 2

Thanks.

Top

gus

Post subject:

Posted: Fri Jul 16, 2004 9:46 am

Beginner

Joined: Thu Jun 24, 2004 1:04 pm
Posts: 35
Location: Minnesota - USA

A coworker of mine was using Hibern8ide and noticed similar behaviour when trying to do fetch joins.

The queries work fine in the application, just don't seem to be able to test/debug them in the tool.

My guess is this is related to JIRA issue http://opensource.atlassian.com/project ... wse/HB-876

--gus

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum