Boolean literal in CASE WHEN THEN ELSE END

Question

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

You have obviously missed the `return` statement in your example. I belive that this is only misprint in question, not in your actual code. I just fixed it in your question. Approve if its so. — Andremoniy, Jan 14 '13 at 12:24
Copy and pasted your code and it works fine, are you sure you didn't made a typo or something? I was having problems with comparing fields, maybe you're comparing Integer with Boolean on some where clause? — Montolide, Jan 14 '13 at 12:32
Odd. Please include the exception and stack. In general many databases don't have a boolean type, and use integer instead, normally 0 = false, 1 = true. — James, Jan 14 '13 at 13:56

score 1 · Answer 1 · answered Jan 14 '13 at 18:51

1

Which version of derby do you use ? Older versions of derby doesnt support Boolean data type. But you can use with integer values.

 @NamedQuery(name = Currency.NQ_EXIST_BY_HREF,
            query = "SELECT" + (" CASE WHEN (COUNT(c) > 0L)"
                                + " THEN 1 ELSE 0 END")
                    + " FROM Currency AS c"
                    + " WHERE c.service = :service"
                    + " AND c.href = :href")

answered Jan 14 '13 at 18:51

vels4j

11,208
5
38
63

I tried and got the same result. She wants `Integer.class` not `Boolean.class`. – Jin Kwon Jan 15 '13 at 01:02
@JinKwon jdk doesn't matter. it deals with derby. – vels4j Jan 15 '13 at 07:15

score 1 · Accepted Answer · answered Jan 16 '13 at 14:30

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.

Boolean literal in CASE WHEN THEN ELSE END

2 Answers2