CODEWITHSUNDEEP
hibernatehql
Timur Yusupov
Timur Yusupov

Reputation: 685

HQL: combine "insert into ... select" with fixed parameters values

I have HQL statement:

insert into Item (ost, value, comments, startTime, endTime, proposedBy) 
select si.ost, si.value, si.comments, si.endTime, si.endTime, u 
from Item si, User u 
where si.ost = ? and u.id = ?

How could it be modified to use parameters' values for ost and startTime columns while taking other columns from select?

Upvotes: 8

Views: 8155

Answers (3)

samabcde
samabcde

Reputation: 8114

We can do so by using setParameter method and refer to example 6 of HQL and JPQL User Guide. In addition, "?" should be replaced by Named Parameter due to Legacy-style query parameters (?) are no longer supported after Hibernate 5.3. (For better readability and maintainability, Named Parameter should be used even if you are using earlier version)

Following method demonstrates above changes:

public void insertIntoSelectWithParameter(String ost, LocalDateTime startTime, String fromOst, Integer fromUserId) {
    String hql = "insert into Item (ost, value, comments, startTime, endTime, proposedBy) "
            + "select :ost, si.value, si.comments, :startTime, si.endTime, u " 
            + "from Item si, User u "
            + "where si.ost = :fromOst and u.id = :fromUserId";
    Session session = entityManager.unwrap(Session.class);
    Query<?> query = session.createQuery(hql);
    query.setParameter("ost", ost);
    query.setParameter("startTime", startTime);
    query.setParameter("fromOst", fromOst);
    query.setParameter("fromUserId", fromUserId);
    query.executeUpdate();
}

Upvotes: 0

NYCChris
NYCChris

Reputation: 649

I don't know about that last answer. I am using NH 3.2 and I was able to get this to work

var hql = @"INSERT INTO EventFacility (Facility, Event, Owner, Position) 
SELECT f, :evt, :own, :position from Facility f where f.Id IN (105, 109, 110)";

var @event = Session.Get<Event>(351931);
var query = Session.CreateQuery(hql)
                .SetInt32("position", 0)
                .SetEntity("evt", @event)
                .SetEntity("own", @event.Owner);

var x = query.ExecuteUpdate();
Assert.AreEqual(3, x);

In this example I needed to create a new EventFacility object. With pretty much all the fields you see here. The Event entity has another entity, Owner hanging off of it.

Upvotes: 0

bogdanb
bogdanb

Reputation: 458

Can’t be done in HQL; it doesn’t allow parameter references in the select clause.

Upvotes: 0

Related Questions