Search code examples
hibernatejpaeclipselinkderbyjpql

Boolean literal in CASE WHEN THEN ELSE END

I followed JPQL statement returning boolean value for checking an Entity's existance.

SELECT CASE WHEN (COUNT(e) > 0L) THEN TRUE ELSE FALSE END
FROM MyEntity AS e
WHERE ...

But when I tried

boolean exist(...) {
    return em.createNamedQuery(<above>, Boolean.class)
        .setParameter(...)
        .getSingleResult();
}

complains that she can't cast an Integer into a Boolean. Is it normal?

I have to use it like this.

boolean exist(..) {
    return em.createNamedQuery(<above>, Integer.class)
        .setParameter(...)
        .getSingleResult() > 0; // Shouldn't it be different from COUNT(e)?
}

UPDATE

Here comes the actual code.

@Entity
@NamedQueries({
    @NamedQuery(name = Currency.NQ_EXIST_BY_HREF,
                query = "SELECT" + (" CASE WHEN (COUNT(c) > 0L)"
                                    + " THEN TRUE ELSE FALSE END")
                        + " FROM Currency AS c"
                        + " WHERE c.service = :service"
                        + " AND c.href = :href")
})
@Table(name = "CURRENCY", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"SERVICE_ID", "HREF"})
})
@XmlTransient
public class Currency implements Serializable {

    public static final String NQ_EXIST_BY_HREF = "Currency.NQ_EXIST_BY_HREF";

Method in Test.

protected static boolean NQ_EXIST_BY_HREF(
    final EntityManager manager, final String href, final Service service) {

    return manager.createNamedQuery(Currency.NQ_EXIST_BY_HREF,
                                    Boolean.class)
        .setParameter("href", href)
        .setParameter("service", service)
        .getSingleResult();
}

Here comes the traces.

java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Boolean
    at ....persistence.finance.CurrencyTest.NQ_EXIST_BY_HREF(CurrencyTest.java:93)
    at ....persistence.finance.CurrencyTest.newHref(CurrencyTest.java:168)
    at ....persistence.finance.CurrencyTest.testNQ_EXIST_BY_HREF(CurrencyTest.java:231)

I'm testing with Derby(Memory)+EclipseLink with following persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="localPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>...</class>
    <class>...</class>
    <properties>
      <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:corrsDB;create=true"/>
      <!--      <property name="javax.persistence.jdbc.user" value=""/>
      <property name="javax.persistence.jdbc.password" value=""/>-->
      <property name="eclipselink.ddl-generation" value="create-tables"/>
      <property name="eclipselink.ddl-generation.output-mode" value="both"/>
      <property name="eclipselink.create-ddl-jdbc-file-name" value="target/createDDL.jdbc"/>
      <property name="eclipselink.drop-ddl-jdbc-file-name" value="target/dropDDL.jdbc"/>
<!--      <property name="eclipselink.logging.level.sql" value="FINEST"/>
      <property name="eclipselink.logging.parameters" value="true"/>-->
      <property name="eclipselink.target-database" value="Derby"/>
    </properties>
  </persistence-unit>
</persistence>

UPDATE

Here comes derby info.

databaseProductName: Apache Derby
databaseProductVersion: 10.8.1.2 - (1095077)
databaseMajorVersion: 10
databaseMinorVersion: 8

UPDATE

Here is my pom/dependencies. I'm not targeting any specific derby. I'm using the one included in JDK a.k.a. Java DB.

  <dependencies>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <!-- this dependency must be mentioned prior to javax:javaee-api -->
      <groupId>org.glassfish.main.extras</groupId>
      <artifactId>glassfish-embedded-all</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.jboss.arquillian.container</groupId>
      <artifactId>arquillian-glassfish-embedded-3.1</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.jboss.arquillian.testng</groupId>
      <artifactId>arquillian-testng-container</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.jboss.shrinkwrap.descriptors</groupId>
      <artifactId>shrinkwrap-descriptors-spi</artifactId>
      <version>2.0.0-alpha-4</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>javax</groupId>
      <artifactId>javaee-api</artifactId>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.mockito</groupId>
      <artifactId>mockito-core</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testng</groupId>
      <artifactId>testng</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

dependency:tree

$ mvn dependency:tree
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building corrs-entities 1.0-alpha-1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-dependency-plugin:2.1:tree (default-cli) @ corrs-entities ---
[INFO] kr.co.ticomms.corrs:corrs-entities:jar:1.0-alpha-1-SNAPSHOT
[INFO] +- org.apache.commons:commons-lang3:jar:3.1:test
[INFO] +- org.glassfish.main.extras:glassfish-embedded-all:jar:3.1.2.2:test
[INFO] +- org.jboss.arquillian.container:arquillian-glassfish-embedded-3.1:jar:1.0.0.CR3:test
[INFO] |  +- org.jboss.arquillian.container:arquillian-container-spi:jar:1.0.0.CR7:test
[INFO] |  |  +- org.jboss.arquillian.core:arquillian-core-spi:jar:1.0.0.CR7:test
[INFO] |  |  +- org.jboss.arquillian.config:arquillian-config-api:jar:1.0.0.CR7:test
[INFO] |  |  +- org.jboss.arquillian.config:arquillian-config-impl-base:jar:1.0.0.CR7:test
[INFO] |  |  +- org.jboss.shrinkwrap:shrinkwrap-api:jar:1.0.0-cr-1:test
[INFO] |  |  \- org.jboss.shrinkwrap.descriptors:shrinkwrap-descriptors-api:jar:1.1.0-beta-1:test
[INFO] |  +- org.jboss.arquillian.container:arquillian-container-test-spi:jar:1.0.0.CR7:test
[INFO] |  +- org.jboss.arquillian.protocol:arquillian-protocol-servlet:jar:1.0.0.CR7:test
[INFO] |  +- org.jboss.arquillian.testenricher:arquillian-testenricher-cdi:jar:1.0.0.CR7:test
[INFO] |  +- org.jboss.arquillian.testenricher:arquillian-testenricher-ejb:jar:1.0.0.CR7:test
[INFO] |  +- org.jboss.arquillian.testenricher:arquillian-testenricher-resource:jar:1.0.0.CR7:test
[INFO] |  \- org.jboss.arquillian.testenricher:arquillian-testenricher-initialcontext:jar:1.0.0.CR7:test
[INFO] +- org.jboss.arquillian.testng:arquillian-testng-container:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.testng:arquillian-testng-core:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.test:arquillian-test-api:jar:1.0.3.Final:test
[INFO] |  |  \- org.jboss.arquillian.core:arquillian-core-api:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.test:arquillian-test-spi:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.container:arquillian-container-test-api:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.core:arquillian-core-impl-base:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.test:arquillian-test-impl-base:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.container:arquillian-container-impl-base:jar:1.0.3.Final:test
[INFO] |  +- org.jboss.arquillian.container:arquillian-container-test-impl-base:jar:1.0.3.Final:test
[INFO] |  \- org.jboss.shrinkwrap:shrinkwrap-impl-base:jar:1.0.1:test
[INFO] |     \- org.jboss.shrinkwrap:shrinkwrap-spi:jar:1.0.1:test
[INFO] +- org.jboss.shrinkwrap.descriptors:shrinkwrap-descriptors-spi:jar:2.0.0-alpha-4:test
[INFO] |  \- org.jboss.shrinkwrap.descriptors:shrinkwrap-descriptors-api-base:jar:2.0.0-alpha-4:test
[INFO] +- javax:javaee-api:jar:6.0:provided
[INFO] +- org.mockito:mockito-core:jar:1.9.5:test
[INFO] |  +- org.hamcrest:hamcrest-core:jar:1.1:test
[INFO] |  \- org.objenesis:objenesis:jar:1.0:test
[INFO] \- org.testng:testng:jar:6.8:test
[INFO]    +- junit:junit:jar:4.10:test
[INFO]    +- org.beanshell:bsh:jar:2.0b4:test
[INFO]    +- com.beust:jcommander:jar:1.27:test
[INFO]    \- org.yaml:snakeyaml:jar:1.6:test
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.442s
[INFO] Finished at: Tue Jan 15 16:28:01 KST 2013
[INFO] Final Memory: 11M/221M
[INFO] ------------------------------------------------------------------------

UPDATE

I added explicit (latest) derby as a dependency and got no changes.

databaseProductName: Apache Derby
databaseProductVersion: 10.9.1.0 - (1344872)
databaseMajorVersion: 10
databaseMinorVersion: 9

UPDATE (WRONG)

The problem seems came from Derby. (WRONG) See EclipseLink JPA 2.0 case expressions. (WRONG)

I changed from derby to hsqldb and it works with Boolean.class. (WRONG)

CONCLUSION

I was wrong. The problem is came from EclipseLink. I tested with two different provider settings.

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <!-- doesn't work -->

and

<provider>org.hibernate.ejb.HibernatePersistence</provider> <!-- works -->
Solution

  • The issue seems to be that Derby does not support a boolean data-type so returns 1/0 from the query instead. This is why is works on HSQL, as boolean are supported.

    Since you have typed your named query return type, EclipseLink should be smart enough to auto-convert integer to boolean for you. It does not seem to be doing this, so please log a bug for that.