COALESCE in JPA namedQuery

Question

I have the following 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 '('

How to handle COALESCE in namedQuery?

Are there any other ways to handle null values in JPA?

Regressive

Answer 1

Coalesce is supported by JPA 2.0 API.

The new construct is proprietary to Hibernate, not necessarily supported in all JPA implementations. First try the query without also trying to construct an object:

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

Goolsby

It's complaining about column 41: syntax error at [(]. Internal Exception: line 1:41: expecting "from", found '(' – Regressive

Answer 2

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 

Laughing

Answer 3

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:

@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:

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

Coast

Yes, it does work without any problem using Eclipselink, however with Toplink, it doesn't work. – Regressive

Which version of TopLink are you using in your application? EclipseLink libraries are included in TopLink, so you may need to update EclipseLink libraries to latest version. In case you are using Weblogic server, you may refer to this guide: docs.oracle.com/cd/E23943_01/doc.1111/e25034/… (just use the server version applicable to your case) for updating EclipseLink to latest version. – Coast