hibernate - CASE WHEN THEN ELSE END 中的 bool 文字

Question

我关注了JPQL statement returning boolean value用于检查实体的存在。

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

但是当我尝试

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

提示她不能将整数转换为 bool 值。正常吗？

我必须像这样使用它。

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

更新

这是实际的代码。

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

测试中的方法。

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();
}

痕迹来了。

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)

我正在使用 Derby(Memory)+EclipseLink 进行测试使用以下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>

更新

Derby 信息来了。

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

更新

这是我的 pom/依赖项。我没有针对任何特定的 Derby 。我正在使用包含在 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>

依赖:树

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

更新

我添加了显式(最新) Derby 作为依赖项，但没有任何更改。

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

更新 (错误的)

问题似乎来自 Derby 。 (错误的)
见 EclipseLink JPA 2.0 case expressions . (错误的)

我从 derby 更改为 hsqldb，它适用于 Boolean.class . (错误的)

结论

我错了。问题来自 EclipseLink。我使用两种不同的提供商设置进行了测试。

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

和

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

Answer 1

问题似乎是 Derby 不支持 bool 数据类型，因此从查询中返回 1/0。
这就是为什么它适用于 HSQL，因为支持 bool 值。

由于您已经输入了命名查询返回类型，EclipseLink 应该足够聪明，可以为您自动将整数转换为 bool 值。它似乎没有这样做，所以请为此记录一个错误。