Hibernate Criteria Api does not provide accurate result list

Question

I am having two tables in database USER_DETAILS and Address. The Entity classes for both the tables are below

@Entity
@Table(name="USER_DETAILS")
public class UserDetails {`

@Id @GeneratedValue(strategy=GenerationType.AUTO)
private int UserID;
private String UserName;

@OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
private List<Address> address=new ArrayList<>();

-------------------------------------------------------------
@Entity
public class Address {

@Id @Column(name="ADD_ID") @GeneratedValue(strategy=GenerationType.AUTO)
private int Id;
private String street;
private String city;

I want to execute a query such that it return me all the results where UserName="user1" and address.city='City1' When i execute the sql query as

SELECT u.UserName,a.street
from USER_DETAILS u, Address a
where u.UserName='user1'
and a.city='City1';

The result is

+----------+---------+
| UserName | street  |
+----------+---------+
| user1    | street1 |
+----------+---------+

But when I run a criteria query for the above as

 Criteria criteria=session.createCriteria(UserDetails.class,"user")
                        .createAlias("address", "ad")
                        .add(Restrictions.eq("user.UserName", "user1"))
                        .add(Restrictions.eq("ad.street", "street2"));

The result it shows is completely different than expected which is

user1
City2 :: street2
City6 :: street6
City4 :: street4
City8 :: street8
City0 :: street0

I tried other options like createQuery("Address") instead of createAlias(). but the results where same. Please give your suggestions.

Thank you

EDIT: I used HQl query similar to one i used as sql as

List<Object[]> list=session.createQuery("select u.UserName,a.street "
        +" from UserDetails u,Address a"
        + " where u.UserName=:username and a.city=:cityname")
        .setString("username", "user1")
        .setString("cityname", "City1")
        .list();

I got the same result as in sql which is the correct one. But criteria does not show the correct result.

Your first query doesn't have a join between user and address. Your second query has one, and returns all the users named "user1" and having at least one address with street=street2. That doesn't mean these users don't have other addresses. I guess (because you didn't post the cde), that that's what you're printing: the users returned by the query, and the addresses of the users returned by the query. — JB Nizet, Aug 24 '14 at 21:39
I did not get you. Would you clearify a little with the code. please — Irshad, Aug 24 '14 at 21:43
Let's say I ask "who knows Java?" You will answer "I do". Does that mean that the only thing you know is Java? No, it doesn't. If, after that, I ask you "tell me all you know", you'll answer Java, C, Football, English, ... The same happens here. You're executing a query asking "who is named user1 and has an address with street2". One user is returned. And then you're printing the user's name and the whole list of his addresses. — JB Nizet, Aug 24 '14 at 21:49
Also, neither the SQL query nor the HQL query at the end makes sure that the user1 returned has the street1 in any of his addresses. You're just making a cross product of the two tables (all the combinations of user and address), and only take the combinations having user 1 as the name and street1 as the street. You need a join between the two tables/entities. — JB Nizet, Aug 24 '14 at 21:53

score 0 · Answer 1 · answered Aug 25 '14 at 03:43

Something as below

HQL/SQL

SELECT u.UserName,a.street
from USER_DETAILS u 
inner join u.Address ua
where u.UserName='user1'
and ua.city='City1';

Criteria API

Criteria c = session.createCriteria(UserDetails.class, "u");
c.createAlias("user.Address", "ua");
c.add(Restrictions.eq("u.UserName", "user1")
c.add(Restrictions.eq("ua.city", "City1");

Have a look here for more info.

Hibernate Criteria Api does not provide accurate result list

1 Answers1