英文:
how to write this query using jpa criteria api?
问题
选择 c.id，
(选择 c2.value 从 customer_table c2 其中 c2.id = c.id 和 c2.key = 'test') 作为 "test"，
(选择 c2.value 从 customer_table c2 其中 c2.id = c.id 和 c2.key = 'service-category') 作为 "service-category"，
(选择 c2.value 从 customer_table c2 其中 c2.id = c.id 和 c2.key = 'exam') 作为 "exam"
从 customer_table c
分组 按 c.id;
英文:
Select c.id,
(Select c2.value from customer_table c2 Where c2.id = c.id And c2.key = 'test') as "test",
(Select c2.value from customer_table c2 Where c2.id = c.id And c2.key = 'service-category') as "service-category",
(Select c2.value from customer_table c2 Where c2.id = c.id And c2.key = 'exam') as "exam"
From customer_table c
Group By c.id;
答案1
得分: 3
假设客户表实体 customerTable 的存在和正确建模，其关系以及 value 是 String 类型，实现如下所示：
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<YourPojo> cq = cb.createQuery(YourPojo.class);
Root<CustomerTable> root = cq.from(CustomerTable.class);
// 子查询 1
Subquery<String> sqVal1 = cq.subquery(String.class);
Root<CustomerTable> sq1Root = sqVal1.from(CustomerTable.class);
sqVal1.where(
cb.and(
cb.equal(root.get("id"), sq1Root.get("id")),
cb.equal(sq1Root.get("key"), "test")
)
);
sqVal1.select(sq1Root.get("value"));
// 子查询 2
Subquery<String> sqVal2 = cq.subquery(String.class);
Root<CustomerTable> sq2Root = sqVal2.from(CustomerTable.class);
sqVal2.where(
cb.and(
cb.equal(root.get("id"), sq2Root.get("id")),
cb.equal(sq2Root.get("key"), "service-category")
)
);
sqVal2.select(sq2Root.get("value"));
// 子查询 3
Subquery<String> sqVal3 = cq.subquery(String.class);
Root<CustomerTable> sq3Root = sqVal3.from(CustomerTable.class);
sqVal3.where(
cb.and(
cb.equal(root.get("id"), sq3Root.get("id")),
cb.equal(sq3Root.get("key"), "exam")
)
);
sqVal3.select(sq3Root.get("value"));
cq.groupBy(root.get("id"));
cq.multiselect(
root.get("id"),
sqVal1.getSelection(),
sqVal2.getSelection(),
sqVal3.getSelection()
);
你需要一个带有与 multiselect 子句中参数相同（顺序和类型相同）的构造函数的 POJO（普通 Java 对象）。
public class YourPojo {
public YourPojo(String id, String val1, String val2, String val3){
// 构造函数实现部分
}
}
推荐使用元模型来访问实体的属性，这将会替换下面的代码：
root.get("id");
使用以下代码：
root.get(CustomerTable_.id);
使用元模型的众多优势之一是能够自动补全属性名称，并降低此处出错的机会。
英文:
Assuming the existence and correct modeling of the customerTable entity, its relationships and that value is of type String, the implementation would be like this:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<YourPojo> cq = cb.createQuery(YourPojo.class);
Root<CustomerTable> root = cq.from(CustomerTable.class);
//Subquery 1
Subquery<String> sqVal1 = cq.subquery(String.class);
Root<CustomerTable> sq1Root = sqVal1.from(CustomerTable.class);
sqVal1.where(
cb.and(
cb.equal(root.get("id"),sq1Root.get("id")),
cb.equal(sq1Root.get("key"),"test")
)
);
sqVal1.select(sq1Root.get("value"));
//Subquery 2
Subquery<String> sqVal2 = cq.subquery(String.class);
Root<CustomerTable> sq2Root = sqVal2.from(CustomerTable.class);
sqVal2.where(
cb.and(
cb.equal(root.get("id"),sq2Root.get("id")),
cb.equal(sq2Root.get("key"),"service-category")
)
);
sqVal2.select(sq2Root.get("value"));
//Subquery 3
Subquery<String> sqVal3 = cq.subquery(String.class);
Root<CustomerTable> sq3Root = sqVal3.from(CustomerTable.class);
sqVal3.where(
cb.and(
cb.equal(root.get("id"),sq3Root.get("id")),
cb.equal(sq3Root.get("key"),"exam")
)
);
sqVal3.select(sq3Root.get("value"));
cq.groupBy(root.get("id"));
cq.multiselect(
root.get("id"),
sqVal1.getSelection(),
sqVal2.getSelection(),
sqVal3.getSelection()
);
You need a pojo with a constructor with the same parameters (in order and type) as the multiselect clause
public class YourPojo {
public YourPojo(String id, String val1, String val2, String val3){
[...]
}
}
It is recommended to use metamodels to access the properties of the entities, which would lead to replace the following code
root.get("id");
with this other
root.get(CustomerTable_.id);
One of the many advantages of using metamodels without getting too into the subject is the ability to auto-complete the property name and reduce the chance of error at this point.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论