CODEWITHSUNDEEP
javahibernate
leon
leon

Reputation: 10395

Using Hibernate Criteria to retrieve data from multiple tables without entity mapping setting

I have a problem in my project. I am trying to create a search function to search users from the user table, but at the meantime, I also want to retrieve the corresponding "url" of user's avatar from another Avatar table. I do want to create a hard mapping between these two tables. How can I do it flexibly using Hibernate Criteria? Both tables are using primary key of "loginID".

I have two classes: 

public class User{
    private String loginID;
    private String screenname;
    ......
}
public class Avatar{
    private Integer id;
    private String loginID;
    private String url;
    .......
}

What I have written: 

  public List<Users> searchLogin(String keywords, int startFrom) {
        List<Users> userList = new ArrayList<Users>();
        try {
            Session session = HibernateUtil.beginTransaction();
            Criteria criteria = session.createCriteria(Users.class,"users");
            criteria.add(Restrictions.ilike("loginID", keywords, MatchMode.ANYWHERE));
            userList = criteria.list();
            if (session.isOpen()) {
                session.close();
            }
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

Thanks guys!!

Upvotes: 5

Views: 25375

Answers (3)

Rouche
Rouche

Reputation: 254

Late, but it could be useful for others who actually google it and ends up here. No need to map or use HQL.

Here's how:

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
    Root<EntityA> entityARoot= criteria.from(EntityA.class);
    Root<EntityB> entityBRoot = criteria.from(EntityB.class);

    //Predicates
    List<Predicate> predicates = new ArrayList<>();
    //Add the predicates you need

    //And predicates
    List<Predicate> andPredicates = new ArrayList<>();
    andPredicates.add(builder.equal(entityARoot.get("id"), entityBRoot.get("id")));
    andPredicates.add(builder.and(predicates.toArray(new Predicate[0])));

    criteria.multiselect(entityARoot, entityBRoot);
    criteria.where(andPredicates.toArray(new Predicate[0]));

    TypedQuery<Tuple> query = em.createQuery(criteria);

    List<Tuple> result = query.getResultList();

Upvotes: 8

siebz0r
siebz0r

Reputation: 20369

Both tables are using primary key of "loginID".

Is this correct? I see the Avatar class having a field named 'id', isn't this the id/primary key?

I also noticed that you refer from Avatar to User using the loginId field. A correct way of linking entities is by their class. Using annotations it should look like this:

@Entity
public class User
{
    @Id
    private String loginId;
    private String screenName;
    @OneToOne(mappedBy = "user")
    private Avatar avatar;
}

@Entity
public class Avatar
{
    @Id
    private Integer id;
    @OneToOne
    private User user;
    private String url;
}

If you then want to fetch a User and the URL of the Avatar belonging to the User the best thing you can do is fetch a User and it's Avatar using a join and then access the Avatar's URL and thus not having to cast from Object to User and Avatar preserving type safety.

User user = fetchUserJoinAvatar("123");
String url = user.getAvatar().getUrl();

public User fetchUserJoinAvatar(String loginId)
{
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> query = cb.createQuery(User.class);

    Root<User> user = query.from(User.class);
    user.fetch("avatar");
    query.select(user).where(cb.equal(user.get("loginId"), loginId));
    return em.createQuery(query).getSingleResult();
}

Upvotes: 2

gkamal
gkamal

Reputation: 21010

Use HQL

from User u,Avatar a where where u.loginID = a.loginID and u.loginID = :loginID

This will return a list of [User,Avatar] arrays.

Upvotes: 3

Related Questions