Hibernate: queries by example and many-to-one relationships

Question

Let's say those are my entities:

Table1.java

@Entity
public class Table1 {

    // Many to one
    private Table2 table2;

    // Raw attributes
    // ...

}

Table2.java

@Entity
public class Table2 {

    // Many to one
    private Table3 table3;

    // Raw attributes
    // ...

}

Table3.java

@Entity
public class Table3 {

    // Raw attributes
    private String lang; // "fr", "en", "de"...

}

I wanna list all the Table1 rows that have a table2.table3.lang that equals en. I tried to use a query by example:

Table3 table3Example = new Table3();
table3Example.setLang("en");

Table2 table2Example = new Table2();
table2Example.setTable3(table3Example);

Table1 table1Example = new Table1();
table1Example.setTable2(table2Example);

table1Repository.findByExample(table1Example);

The problem is that .findByExample(table1Example) returns all the rows of the database, no matter of the lang, which means that the filter isn't considered at all :(

Any help would be appreciated :)

PS: no exception is thrown, .findByExample(table1Example) just returns all of the Table1 rows.

score 2 · Accepted Answer · answered Jun 06 '12 at 08:57

2

Try something like this:

    Query q = entityManager.createQuery("Select o from Table1 o where o.table2.table3.lang = :lang");
    q.setParameter("lang", "en");
    List<Table1> r = (List<Table1>)q.getResultList();

To see why you get all the rows in Table1, make sure that you have

<property name="hibernate.show_sql" value="true"/>

in your persistence.xml and then watch the log to see the actual select that hibernate executes.

answered Jun 06 '12 at 08:57

tibtof

7,857
1
32
49

Works great! But it means that I have to implement all the filters I want by myself. It would have been great if a query by example could have done the many-to-one links on its own. Isn't it possible? Thanks anyway :) – sp00m Jun 06 '12 at 09:14
For what I know there are some problems with query by example and many-to-one relations. The best way to identify what happens when you run findByExample is to watch the log and see the actual select hibernate executes. – tibtof Jun 06 '12 at 09:18
In fact, in my `.findByExample` method (which is [SpringFuse](http://www.springfuse.com/)'s one), there is a `if (attr.getPersistentAttributeType() == MANY_TO_ONE) continue;`. When I comment that line, I catch an exception: `object references an unsaved transient instance`, which is quite understandable, 'cause the objects are examples, not real persisted entities. But yeah, seems to be a lack there, querying by example has a room for improvement `:)` Thanks anyway, I give you the 800+! – sp00m Jun 06 '12 at 09:30

Hibernate: queries by example and many-to-one relationships

1 Answers1