How to convert nested SQL to HQL

Question

I am new to the Hibernate and HQL. I want to write an update query in HQL, whose SQL equivalent is as follows:

update patient set 
      `last_name` = "new_last", 
      `first_name` = "new_first" 
where id = (select doctor_id from doctor 
            where clinic_id = 22 and city = 'abc_city');

doctor_id is PK for doctor and is FK and PK in patient. There is one-to-one mapping.

The corresponding Java classes are Patient (with fields lastName, firstName, doctorId) and Doctor (with fields doctorId).

Can anyone please tell what will be the HQL equivalent of the above SQL query?

Thanks a lot.

mcyalcin · Accepted Answer · 2011-03-04 20:16:24Z

14

String update = "update Patient p set p.last_name = :new_last, p.first_name = :new_first where p.id = some (select doctor.id from Doctor doctor where doctor.clinic_id = 22 and city = 'abc_city')";

You can work out how to phrase hql queries if you check the specification. You can find a section about subqueries there.

edited Mar 4, 2011 at 20:16

answered Mar 4, 2011 at 20:10

mcyalcin

2,0811 gold badge15 silver badges15 bronze badges

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

3 Comments

mcyalcin Over a year ago

:new_last and :new_first are variable bindings, you can replace literals if you wish..

Sachin Over a year ago

thanks a lot for the answer as well for the link. i had a doubt . the query is working fine even without the word 'some'. what is the use of that? i mean since i don't know it, i tried it both ways (w/ and w/o) and it works same.

mcyalcin Over a year ago

@Bhushan: That is for when the id is not unique. When the subquery returns multiple results, you might want to match p.id to any one of the results.

Abel · Accepted Answer · 2011-03-04 20:22:51Z

4

I don't think you need HQL (I know, you ask that explicitly, but since you say you're new to Hibernate, let me offer a Hibernate-style alternative). I am not a favor of HQL, because you are still dealing with strings, which can become hard to maintain, just like SQL, and you loose type safety.

Instead, use Hibernate criteria queries and methods to query your data. Depending on your class mapping, you could do something like this:

List patients = session.CreateCriteria(typeof(Patient.class))         
    .createAlias("doctor", "dr")
          .add(Restrictions.Eq("dr.clinic_id", 22))
          .add(Restrictions.Eq("dr.city", "abc_city"))
    .list();

// go through the patients and set the properties something like this:
for(Patient p : patients)
{
     p.lastName = "new lastname";
     p.firstName = "new firstname";
}

Some people argue that using CreateCriteria is difficult. It takes a little getting used to, true, but it has the advantage of type safety and complexities can easily be hidden behind generic classes. Google for "Hibernate java GetByProperty" and you see what I mean.

answered Mar 4, 2011 at 20:22

Abel

57.5k25 gold badges159 silver badges260 bronze badges

1 Comment

Abel Over a year ago

@Bhushan, you're welcome. If you like it, you may up the vote ;)

yogsma · Accepted Answer · 2011-03-04 20:16:54Z

2

update Patient set last_name = :new_last , first_name = :new_first where patient.id = some(select doctor_id from Doctor as doctor where clinic_id = 22 and city = abc_city)

answered Mar 4, 2011 at 20:16

yogsma

10.7k32 gold badges109 silver badges158 bronze badges

2 Comments

mcyalcin Over a year ago

That's right, I missed the 'some'. Although presumably the id is unique.

Sachin Over a year ago

thanks a lot for your input. the query is working fine even without the word 'some'. what is the use of that? i mean since i don't know it, i tried it both ways (w/ and w/o) and it works same.

Krass · Accepted Answer · 2011-03-23 19:57:48Z

0

There is a significant difference between executing update with select and actually fetching the records to the client, updating them and posting them back:

UPDATE x SET a=:a WHERE b in (SELECT ...)

works in the database, no data is transferred to the client.

list=CreateCriteria().add(Restriction).list();

brings all the records to be updated to the client, updates them, then posts them back to the database, probably with one UPDATE per record.

Using UPDATE is much, much faster than using criteria (think thousands of times).

answered Mar 23, 2011 at 19:57

Krass

1

Comments

Heri · Accepted Answer · 2022-07-07 14:18:17Z

Since the question title can be interpreted generally as "How to use nested selects in hibernate", and the HQL syntax restricts nested selects only to be in the select- and the where-clause, I would like to add here the possibility to use native SQL as well. In Oracle - for instance - you may also use a nested select in the from-clause.

Following query with two nested inner selects cannot be expressed by HQL:

select ext, count(ext)
from (
    select substr(s,   nullif( instr(s,'.', -1) +1, 1) ) as ext
    from ( 
         select b.FILE_NAME as s from ATTACHMENT_B b 
         union select att.FILE_NAME as s from ATTACHEMENT_FOR_MAIL att
         )
 )
GROUP BY ext
order by ext;

(which counts, BTW, the occurences of each distinct file name extension in two different tables).

You can use such an sql string as native sql like this:

@Autowired
private SessionFactory sessionFactory;

    String sql = ...
    SQLQuery qry = sessionFactory.getCurrentSession().createSQLQuery(sql);
    // provide an appropriate ResultTransformer
    return qry.list();

Collectives™ on Stack Overflow

How to convert nested SQL to HQL

5 Answers 5

3 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related