Beginner hibernate: Why does my hibernate query return only one result, instead of a list?

Question

I'm very new to hibernate, and I'm trying to set up a new method in our PersonDAO.

My hibernate mapping file looks like this:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.foo.bar.domain">
<class name="Person" table="person">
    <meta attribute="class-description">A Person</meta>
    <id name="id" type="java.lang.Long" column="rid" unsaved-value="null">
        <generator class="native" />
    </id>


    <version name="version" type="integer" column="rversion" unsaved-value="null" />
    <property name="UID"           type="string"  column="UID" length="16" not-null="true" unique="true"/>
    <property name="lastName"         type="string"  column="last_name"  not-null="true" />
    <property name="firstName"        type="string"  column="first_name" not-null="true" />
    <property name="ownDepartment"    type="string"  column="own_department"/>

    <!-- a person has many responsibilities and a responsibility can can assigned to many person -->
    <set name="responsibilities" table="person_responsibility">
        <key column="person_id"/>
        <many-to-many column="responsibility_id" class="Responsibility"/>
    </set>

    <set name="additionalDepartments" table="PERSON_TO_ADDL_DEPARTMENT">    
        <key column="person_id"/>               
        <element column="ADDITIONAL_DEPARTMENT" type="string"/>         
    </set>                                              
</class>

and I've written a method like this, in java, to fetch all the managers from a given department:

public List<Person> getManagerByDepartment(final String givenDepartment){
    List<Person> l = (List<Person>) this.getHibernateTemplate().executeFind(new HibernateCallback<List<Person>>() {

    public List<Person> doInHibernate(Session session) throws HibernateException, SQLException {
        String query = "select p from Person p join p.responsibilities responsibilities join p.additionalDepartments additionalDepartments where responsibilities.name = 'manager' and (p.ownDepartment = :givenDepartment or additionalDepartments = :givenDepartment)";
        List<Person> result = (List<Person>) session.createQuery(query).setString("givenDepartment", givenDepartment).list();
        return result;  
        }
    });

    return l;
}

now I do a manual query in SQL, and I can see that for a given department, there are definitely more than one people who have the additional responsibility 'manager'...why does my method only ever return one person, instead of all of them?

I have a strong suspicion that my method, specifically my query, and not the mapping, is the issue, but I can't see what's wrong with it...

I've jumped in at the deep end here, so any help would be very much appreciated.

edit: note, I'm working on hundreds of records, not millions, and this isn't exactly a bottle-neck operation, so I'm not too worried about performance...that said if I'm doing something that's pointlessly wasteful, do point it out

Amit Jain · Accepted Answer · 2014-11-18 10:31:19Z

1

You can print hibernate query by enabling showsql option and check the query getting created and then test it against the database.

answered Nov 18, 2014 at 10:31

Amit Jain

1418 bronze badges

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

1 Comment

Paul Over a year ago

Turns out I'm stupid and neither the code nor the hibernate mapping was the issue. I was querying one database from the application, and manually connecting to an entirely different database to check what results I should be getting

Predrag Maric · Accepted Answer · 2014-11-18 10:10:39Z

1

Hard to be sure without the sample data, but when you do join in HQL, it is translated to inner join in SQL. So, if you know that there should be more than one result with given responsibility then the problem is probably join p.additionalDepartments.

Try this query with left join for additionalDepartments and see if it works

String query = "select p from Person p join p.responsibilities responsibilities left join p.additionalDepartments additionalDepartments where responsibilities.name = 'manager' and (p.ownDepartment = :givenDepartment or additionalDepartments = :givenDepartment)";

answered Nov 18, 2014 at 10:10

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Collectives™ on Stack Overflow

Beginner hibernate: Why does my hibernate query return only one result, instead of a list?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related