Query with using CriteriaQuery and EntityManager(Hibernate)

Question

How create query with using CriteriaQuery and EntityManager for this SQL query:

SELECT * FROM user WHERE user.login = '?' and user.password = '?'

I try so:

        final CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
        final CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
        Root<User> root = criteriaQuery.from(User.class);
        criteriaQuery.select(root);
        criteriaQuery.where(criteriaBuilder.gt(root.get("login"), userLogin));
        return getEntityManager().createQuery(criteriaQuery).getResultList().get(0);

Tim Biegeleisen · Accepted Answer · 2017-05-07 11:23:42Z

2

Your code looks like it's on the right track, except that it only has one WHERE condition, which does not agree with your raw SQL query, which has two conditions.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> q = cb.createQuery(User.class);
Root<User> c = q.from(User.class);
q.select(c);
ParameterExpression<String> p1 = cb.parameter(String.class);
ParameterExpression<String> p2 = cb.parameter(String.class);
q.where(
    cb.equal(c.get("login"), p1),
    cb.equal(c.get("password"), p2)
);
return em.createQuery(q).getResultList().get(0);

As a side note, in real life you would typically not be storing raw user passwords in your database. Rather, you be storing a salted and encrypted password. So hopefully your actual program is not storing raw passwords.

edited May 7, 2017 at 11:23

answered May 7, 2017 at 11:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

ASH · Accepted Answer · 2018-04-26 16:56:37Z

This worked for me in the end was this:

    entityManager.getTransaction().begin();

    CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
    Root<Profile> fromRoot = criteriaQuery.from(Profile.class);
    criteriaQuery.select(fromRoot);

    criteriaQuery.where(criteriaBuilder.equal(fromRoot.get("userName"), username),
                        criteriaBuilder.equal(fromRoot.get("password"), password));

    List<Object> resultList = entityManager.createQuery(criteriaQuery).getResultList();
    Profile dbProfile = null;
    if (resultList.isEmpty()) {
        // Handle Error
    } else {
        dbProfile = (Profile) resultList.get(0);
    }

    entityManager.getTransaction().commit();

Ujwala Nuti · Accepted Answer · 2025-03-28 13:05:25Z

0

In Where clause two conditions are present, we can use Predicate .


public List<User> findUserByLoginAndPassword(String login, String password){
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);

Predicate loginPredicate = cb.equal(user.get("login"), login);
Predicate passwordPredicate = cb.equal(user.get("password"), password);

//select * from User Where user.login=login and user.password=password;
cq.select(root).where(cb.and(loginPredicate, passwordPredicate));

TypedQuery<User> query = entityManager.createQuery(cq);
return query.getResultList().get(0);

}

Hope this will help.

answered Mar 28 at 13:05

Ujwala Nuti

711 silver badge3 bronze badges

Collectives™ on Stack Overflow

Query with using CriteriaQuery and EntityManager(Hibernate)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related