HQL：将“插入...选择”组合起来具有固定参数值

发布于 2024-09-25 12:31:20 字数 329 浏览 0 评论 0原文

我有 HQL 语句：

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 = ?

如何修改它以使用 ost 和 startTime 列的参数值，同时从 select 获取其他列？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沙沙粒小 2024-10-02 12:31:20

HQL中无法完成；它不允许在 select 子句中引用参数。

回复收藏 0 原文

缘字诀 2024-10-02 12:31:20

我不知道最后一个答案。我正在使用 NH 3.2，并且能够让它工作。

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

在此示例中，我需要创建一个新的 EventFacility 对象。几乎包含您在这里看到的所有字段。事件实体有另一个实体，所有者挂在它上面。

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.

回复收藏 0 原文

相思故 2024-10-02 12:31:20

我们可以通过使用 setParameter 方法并参考HQL 和 JPQL 用户指南示例 6。
此外， ”？”由于旧式查询参数 (?。（为了更好的可读性和可维护性，即使您使用的是早期版本，也应该使用命名参数）

以下方法演示了上述更改：

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

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

回复收藏 0 原文

~没有更多了~