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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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();
}