Java JPA 中的 COALESCE 命名查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27528976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COALESCE in JPA namedQuery
提问by J?cob
I have the following namedQuery
我有以下namedQuery
select new test.entity.Emp(COALESCE(k.projectId,'N')
as projectId, k.projectName) from Emp o inner join o.projects k
However I am getting error
但是我收到错误
expecting RIGHT_ROUND_BRACKET, found '('
期待 RIGHT_ROUND_BRACKET，找到 '('
How to handle COALESCEin namedQuery?
如何处理COALESCEnamedQuery？
Are there any other ways to handle null values in JPA?
有没有其他方法可以处理 JPA 中的空值？
回答by gknicker
Coalesce is supported by JPA 2.0 API.
JPA 2.0 API 支持Coalesce 。
The newconstruct is proprietary to Hibernate, not necessarily supported in all JPA implementations. First try the query without also trying to construct an object:
该new构造是 Hibernate 专有的，不一定在所有 JPA 实现中都支持。首先尝试查询而不尝试构造一个对象：
select COALESCE(k.projectId,'N') as projectId, k.projectName from Emp o inner join o.projects k
回答by Petros Splinakis
I tried the following simple unit test, which passes successfully:
我尝试了以下简单的单元测试，成功通过：
@Test
public void coalesceTest() {
EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("PersistenceUnit");
EntityManager entityManager = entityManagerFactory.createEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
DepartmentEmployee employee = new DepartmentEmployee();
EmployeeDepartment department = new EmployeeDepartment();
department.getEmployees().add(employee);
employee.setDepartment(department);
transaction.begin();
try {
entityManager.persist(employee);
entityManager.persist(department);
transaction.commit();
Assert.assertTrue("Employee not persisted", employee.getId() > 0);
Assert.assertTrue("Department not persisted", department.getId() > 0);
} catch (Exception x) {
if(transaction.isActive()) {
transaction.rollback();
}
Assert.fail("Failed to persist: " + x.getMessage());
}
TypedQuery<String> query = entityManager.createQuery("select coalesce(e.name, 'No Name') from EmployeeDepartment d join d.employees e", String.class);
String employeeName = query.getSingleResult();
Assert.assertEquals("Unexpected query result", "No Name", employeeName);
}
DepartmentEmployee class:
部门员工类：
@Entity
public class DepartmentEmployee implements Serializable {
@Id
@GeneratedValue
private int id;
private String name;
@ManyToOne
private EmployeeDepartment department;
public int getId() {
return id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public EmployeeDepartment getDepartment() {
return department;
}
public void setDepartment(EmployeeDepartment department) {
this.department = department;
}
}
EmployeeDepartment class:
EmployeeDepartment 类：
@Entity
public class EmployeeDepartment implements Serializable {
@Id
@GeneratedValue
private int id;
@OneToMany
private List<DepartmentEmployee> employees;
public EmployeeDepartment() {
employees = new ArrayList<DepartmentEmployee>();
}
public int getId() {
return id;
}
public List<DepartmentEmployee> getEmployees() {
return employees;
}
public void setEmployees(List<DepartmentEmployee> employees) {
this.employees = employees;
}
}
Tested using EclipseLink 2.5.0:
使用 EclipseLink 2.5.0 测试：
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.5.0</version>
</dependency>
回答by Jens Schauder
Your brackets are messed up or you have a superfluous alias clause, which becomes easy to see when you indent your statement properly.
你的括号乱了，或者你有一个多余的别名子句，当你正确缩进你的语句时，这很容易看到。
select
new test.entity.Emp(
COALESCE(k.projectId,'N') as projectId,
k.projectName
)
from Emp o inner join o.projects k
try this instead:
试试这个：
select
new test.entity.Emp(
COALESCE(k.projectId,'N'),
k.projectName
)
from Emp o inner join o.projects k
