CODEWITHSUNDEEP
javamysqlhibernatehql
caot
caot

Reputation: 3318

Hibernate or HQL Select on the result of another SELECT

Is there a way using HQL on the following query?

SELECT userId, pwd, pwdDate FROM (SELECT userId, AES_DECRYPT(pwd, 'key_str') as pwd, pwdDate FROM UserHistory order by pwdDate desc limit 5 ) AS A WHERE pwd = :pwd

The following worked.

SELECT * FROM UserHistory order by pwdDate desc limit 5

The above sql can be the following in hibernate.

Criteria criteria = session.createCriteria(UserHistory.class); criteria.addOrder(Order.desc("pwdDate")); List<UserHistory> list = criteria.setMaxResults(5).list();

Upvotes: 1

Views: 866

Answers (1)

caot
caot

Reputation: 3318

The following worked. The key is to createSQLQuery for Native SQL.

String SQL = 
"SELECT A.* FROM \n" +
"(select * \n" +
" from user_history$ order by pwdDate desc limit 5 ) AS A \n" + 
"where pwd = AES_ENCRYPT(:pwd, 'key_str') \n";

Query query = session.createSQLQuery(SQL);
query.setParameter("pwd", psw);
List<UserHistory> list = query.list();

Upvotes: 1

Related Questions