“选择新对象...加入...其中”的问题
我在 HQL 查询方面遇到问题
三个类
ClassOne 是我的 BusinessObject
public class ClassOne {
private int id;
private int status;
private Set<ClassTwo> classTwos;
+ other fields/getters/setters/constructor etc
}
ClassTwo 在一组 ClassOne 中被引用,并且是 ClassOne 对象的历史记录
public class ClassTwo {
private int id;
private int oldStatus;
private int newStatus;
private String message;
//+ getters/setters/constructor etc
}
ClassThree 是我的 DTO/VO,只有一个 classTwo (不是整个历史记录) )
public class ClassThree {
private int id;
private int status;
private ClassTwo classTwo;
public ClassThree(int pId, int pStatus, ClassTwo pClassTwo) {
id=pId;
status=pStatus;
classTwo=pClassTwo;
}
//+ getters/setters etc
}
现在我想创建一个像这样的 HQL 查询:
我想获取具有特定状态的 ClassThree 的所有对象,如果存在,则获取具有特定 newStatus 的最新 ClassTwo。
例如:
我想要获取 ClassOne 的所有 DTO(ClassThree),其状态现在为 1,但在其历史记录的早期,它一直是 2,并且我想要最新的 ClassTwo 对象,其 newStatus 为 2。
SELECT new ClassThree(c1.id, c1.status, c2)
FROM ClassOne c1
LEFT JOIN c1.classtwos c2 (...)
and (...) 是我不知道该怎么做的地方,我什至不确定它是否是 join / join fetch
环顾四周并尝试了很多,但没有任何线索。特别是在连接获取时,我遇到了一些 Hibernate 错误,例如 org.hibernate.QueryException: 查询指定的连接获取,但所获取的关联的所有者不存在于选择列表中。
像这样获取 BusinessObject 是没有问题的
SELECT distinct(c1)
FROM ClassOne c1
LEFT OUTER JOIN FETCH c1.classtwos c2
,我将 ClassTwos 作为我的字段。
预先感谢,
Jacob
P.S.:有一件事可能很重要,ClassTwo 没有引用 ClassOne!
PPS:解决我的问题的简单 SQL 查询看起来或多或少像这样:
select * from classone as c1 left join (select * from classtwo where newstatus = 2) c2 on c1.id=c2.id_classone whete c1.status = 1
这个查询可以工作并获取我的 PostGreSQL DB 上所需的所有信息,但我真的希望有一个 HQL 可以继续使用,特别是用于维护原因等等...
更新解决方案:
获取所有状态为 1 的 ClassOne 的 id
Collection<Integer> ids = null;
ids = (Collection<Integer>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session pSession) throws HibernateException, SQLException {
return getDocumentIds(pSession, pStatus);
}
}
);
现在,我获取了所有处于状态 2 的 DTO(感谢 Ivan)
:查询Document.dto.with.transfer
SELECT new DocumentDTO(d.id, d.status, histo)
FROM Document d
LEFT JOIN d.histories histo
WHERE
d.id in (:ids)
AND
(histo.id =
SELECT MAX(innerhisto.id)
FROM Document innerd
JOIN innerd.histories innerhisto
WHERE d.id = innerd.id AND innerhisto.newStatus = 21)
(在我的代码中我使用一些命名查询)
List<DocumentDTO> lRes = new ArrayList<DocumentDTO>();
Query lQuery = getSession(false).getNamedQuery("Document.dto.with.transfer");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());
之后我删除了从我的列表ID中已经找到的所有ID
for (DocumentDTO dto : lResultList) {
ids.remove(dto .getId());
}
我使用DTO的第二个构造函数执行第三个查询,用虚拟对象初始化我的历史记录。
命名查询Document.dto.simple
SELECT new DocumentDTO(d.id, d.status)
FROM Document d
WHERE
d.id in (:ids)
(另一个命名查询)
lQuery = getSession(false).getNamedQuery("Document.dto.simple");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());
就完成了。
I'm having a problem with an HQL query
Three classes
ClassOne is my BusinessObject
public class ClassOne {
private int id;
private int status;
private Set<ClassTwo> classTwos;
+ other fields/getters/setters/constructor etc
}
ClassTwo is referenced in a set of ClassOne and is kind of the history of an object of ClassOne
public class ClassTwo {
private int id;
private int oldStatus;
private int newStatus;
private String message;
//+ getters/setters/constructor etc
}
ClassThree is my DTO/VO with just one classTwo (not the whole history)
public class ClassThree {
private int id;
private int status;
private ClassTwo classTwo;
public ClassThree(int pId, int pStatus, ClassTwo pClassTwo) {
id=pId;
status=pStatus;
classTwo=pClassTwo;
}
//+ getters/setters etc
}
Now I'd like to create an HQL query like this:
I'd like to get all objects of ClassThree with a certain status and if it exists the newest ClassTwo with a certain newStatus.
For example:
I'd like to get all the DTOs (ClassThree) of ClassOne whose status is now 1, but earlier in their history it has been 2 and I'd like to have the latest ClassTwo object which has 2 as newStatus.
SELECT new ClassThree(c1.id, c1.status, c2)
FROM ClassOne c1
LEFT JOIN c1.classtwos c2 (...)
and (...) is where I don't know what to do, I'm not even sure if it's a join / join fetch
Looked around and tried quite a lot already, but no clue. Especially with the join fetch I get some Hibernate errors like org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list
.
Fetching the BusinessObject like that is no problem
SELECT distinct(c1)
FROM ClassOne c1
LEFT OUTER JOIN FETCH c1.classtwos c2
and I get the ClassTwos as my field.
Thanks in advance,
Jacob
P.S.: One thing might be important, ClassTwo has no reference to ClassOne!!
P.P.S : The simple SQL query which resolves my problem looks more or less like that:
select * from classone as c1 left join (select * from classtwo where newstatus = 2) c2 on c1.id=c2.id_classone whete c1.status = 1
This query works and gets all the information needed on my PostGreSQL DB, but I'd really like to have an HQL to continue to work with, especially for maintenance reasons and so on...
Update with workaround solution:
Getting the ids of all the ClassOnes with a status 1
Collection<Integer> ids = null;
ids = (Collection<Integer>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session pSession) throws HibernateException, SQLException {
return getDocumentIds(pSession, pStatus);
}
}
);
Now I get all the DTOs which have been in status 2 (thanks to Ivan) with:
Named query Document.dto.with.transfer
SELECT new DocumentDTO(d.id, d.status, histo)
FROM Document d
LEFT JOIN d.histories histo
WHERE
d.id in (:ids)
AND
(histo.id =
SELECT MAX(innerhisto.id)
FROM Document innerd
JOIN innerd.histories innerhisto
WHERE d.id = innerd.id AND innerhisto.newStatus = 21)
(in my code I use some named queries)
List<DocumentDTO> lRes = new ArrayList<DocumentDTO>();
Query lQuery = getSession(false).getNamedQuery("Document.dto.with.transfer");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());
afterwards I remove all the IDs already found from my list ids
for (DocumentDTO dto : lResultList) {
ids.remove(dto .getId());
}
I do a third query using a second constructor for the DTO, initializing my history with a dummy-object.
Named query Document.dto.simple
SELECT new DocumentDTO(d.id, d.status)
FROM Document d
WHERE
d.id in (:ids)
(another named query)
lQuery = getSession(false).getNamedQuery("Document.dto.simple");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());
and it's done.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
要包含没有历史记录的文档,我们应该使用
LEFT JOIN
并测试空集合,然后使用子查询 (SELECT COUNT(...)
) 来检测所有具有历史记录的文档从未处于状态 2。最后一个OR 子句
用于获取具有指定状态的最后历史记录。以下是 HQL 查询:
然后在查询中调用
setParameter("historyStatus", 2)
和setParameter("docStatus", 1)
以获得正确的结果。就是这样!
请注意,我做了一个假设,我们可以使用
History
的id
属性值作为对象放入数据库的顺序的指示符。To include Documents with no history we should use
LEFT JOIN
and test for empty collection, then we use subquery (SELECT COUNT(...)
) to detect all documents that have never been in status 2. The lastOR-clause
is for fetching the last history with the specified status.Here is the HQL query:
Then call
setParameter("historyStatus", 2)
andsetParameter("docStatus", 1)
on your query to get the correct result.That's it!
Please note, I've made an assumption, that we can use a value of
id
attribute ofHistory
as an indicator of the order in which objects were put in your database.