JPA namedQuery中的COALESCE

Question

我有以下的 namedQuery

select new test.entity.Emp(COALESCE(k.projectId,'N')
as projectId, k.projectName) from Emp o inner join o.projects k 

然而我遇到了错误

期望 RIGHT_ROUND_BRACKET，但发现了 '（'

如何在 namedQuery 中处理 COALESCE？

在 JPA 中还有其他处理空值的方法吗？

- Jacob

Answer 1

Coalesce是由JPA 2.0 API支持的。

new构造函数是Hibernate专有的，不一定在所有JPA实现中都受支持。首先尝试查询而不尝试构造对象：

select COALESCE(k.projectId,'N') as projectId, k.projectName from Emp o inner join o.projects k

- gknicker

---

它正在抱怨“第41列：在[(]处语法错误。内部异常：第1行：41行：期望“from”，找到'（'” - Jacob

Answer 2

你的括号有问题或者有多余的别名语句，当你正确缩进语句时就很容易看出来了。

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 

- Jens Schauder

Answer 3

我尝试了以下简单的单元测试，测试成功通过：

@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类：

@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;
    }
}

员工部门类：

@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;
    }
}

已使用 EclipseLink 2.5.0 进行测试：

    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>eclipselink</artifactId>
        <version>2.5.0</version>
    </dependency>

- Petros Splinakis

---

是的，使用Eclipselink它可以毫无问题地工作，然而使用Toplink就不行。 - Jacob

你的应用程序中使用的是哪个版本的TopLink？EclipseLink库包含在TopLink中，因此您可能需要更新EclipseLink库到最新版本。如果您正在使用Weblogic服务器，则可以参考此指南：http://docs.oracle.com/cd/E23943_01/doc.1111/e25034/tlandwls.htm#TLADG118（只需使用适用于您情况的服务器版本）以将EclipseLink更新到最新版本。 - Petros Splinakis