0

I have an embedded field and I want to compare it to the passed parameter but only if this parameter is not null. With regular (not embedded) fields I could use the following query and it would work as expected:

FROM TestEntity entity where :embedded is null or entity.embedded = :embedded

But with embedded fields Hibernate doesn't bind parameters correctly, it binds more parameters than it should. As you can see in the output below there are three "?" but four bindings. Is that a hibernate bug?

   2019-02-27 13:19:31,497 [DEBUG] org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement(SqlStatementLogger.java:92) 
/* FROM TestEntity entity where  :embedded is null or entity.embedded = :embedded */ select testentity0_.id as id1_0_, testentity0_.emb_first as emb_firs2_0_, testentity0_.emb_second as emb_seco3_0_ from test testentity0_ where ? is null or testentity0_.emb_first=? and testentity0_.emb_second=?
2019-02-27 13:19:31,498 [TRACE] org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:53) 
binding parameter [1] as [VARCHAR] - [null]
2019-02-27 13:19:31,499 [TRACE] org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:53) 
binding parameter [2] as [VARCHAR] - [null]
2019-02-27 13:19:31,499 [TRACE] org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:53) 
binding parameter [3] as [VARCHAR] - [null]
2019-02-27 13:19:31,499 [TRACE] org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:53) 
binding parameter [4] as [VARCHAR] - [null]
2019-02-27 13:19:31,499 [WARN ] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:129) 
SQL Error: 90008, SQLState: 90008
2019-02-27 13:19:31,499 [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:131) 
Недопустимое значение "4" для параметра "parameterIndex"
Invalid value "4" for parameter "parameterIndex" [90008-194]
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1419)
    at org.hibernate.Query.getResultList(Query.java:427)
    at Main.select(Main.java:48)
    at Main.main(Main.java:20)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.loader.Loader.doList(Loader.java:2618)
    at org.hibernate.loader.Loader.doList(Loader.java:2598)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
    at org.hibernate.loader.Loader.list(Loader.java:2425)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:370)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1481)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
    ... 3 more
Caused by: org.h2.jdbc.JdbcSQLException: Недопустимое значение "4" для параметра "parameterIndex"
Invalid value "4" for parameter "parameterIndex" [90008-194]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.getInvalidValueException(DbException.java:228)
    at org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1436)
    at org.h2.jdbc.JdbcPreparedStatement.setNull(JdbcPreparedStatement.java:316)
    at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:61)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:279)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:274)
    at org.hibernate.type.ComponentType.nullSafeSet(ComponentType.java:336)
    at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:53)
    at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:628)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1956)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
    at org.hibernate.loader.Loader.doQuery(Loader.java:932)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2615)
    ... 12 more

TestEntity.java

import javax.persistence.*;

@Entity
@Table(name = "test")
public class TestEntity {
    @Id
    @GeneratedValue(generator = "test_seq")
    @SequenceGenerator(name = "test_seq", sequenceName="TEST_SEQ")
    @Column(name = "id")
    private int id;

    @Embedded
    private TestEmbedded embedded;

    public TestEntity() {
    }

    public TestEntity(TestEmbedded embedded) {
        this.embedded = embedded;
    }

    public int getId() {
        return id;
    }

    public TestEmbedded getEmbedded() {
        return embedded;
    }
}

TestEmbedded.java

import javax.persistence.*;

@Embeddable
public class TestEmbedded {
    @Column(name = "emb_first", nullable = false)
    private String embFirst;

    @Column(name = "emb_second", nullable = false)
    private String embSecond;

    public TestEmbedded() {
    }

    public TestEmbedded(String embFirst, String embSecond) {
        this.embFirst = embFirst;
        this.embSecond = embSecond;
    }

    public String getEmbFirst() {
        return embFirst;
    }

    public String getEmbSecond() {
        return embSecond;
    }
}

Main.java

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import java.util.List;
import java.util.Properties;

public class Main {
    public static void main(String[] args) {
        Properties entityManagerFactoryProperties = new Properties();

        entityManagerFactoryProperties.setProperty("javax.persistence.jdbc.driver", "org.h2.Driver");
        entityManagerFactoryProperties.setProperty("javax.persistence.jdbc.url", "jdbc:h2:mem:");
        entityManagerFactoryProperties.setProperty("javax.persistence.jdbc.user", "sa");
        entityManagerFactoryProperties.setProperty("javax.persistence.jdbc.password", "");

        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("main", entityManagerFactoryProperties);

        insert(entityManagerFactory);
        select(entityManagerFactory);
    }

    private static void insert(EntityManagerFactory entityManagerFactory) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        entityManager.getTransaction().begin();
        try {
            entityManager.persist(new TestEntity(new TestEmbedded("1", "2")));
            entityManager.persist(new TestEntity(new TestEmbedded("3", "2")));
            entityManager.getTransaction().commit();
        } catch (Exception e) {
            entityManager.getTransaction().rollback();
            throw e;
        } finally {
            entityManager.close();
        }
    }

    private static void select(EntityManagerFactory entityManagerFactory) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        String queryText = "FROM TestEntity entity where "
                + " :embedded is null or entity.embedded = :embedded";
        entityManager.getTransaction().begin();
        try {
            TypedQuery<TestEntity> query = entityManager.createQuery(queryText, TestEntity.class);
            query.setParameter("embedded", null);
            List<TestEntity> result = query.getResultList();
            entityManager.getTransaction().commit();
        } catch (Exception e) {
            entityManager.getTransaction().rollback();
            throw e;
        } finally {
            entityManager.close();
        }
    }
}

persistence.xml

<persistence 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"
             version="2.0">
    <persistence-unit name="main">
        <class>TestEntity</class>

        <exclude-unlisted-classes>true</exclude-unlisted-classes>

        <properties>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
            <property name="hibernate.use_sql_comments" value="true"/>
        </properties>
    </persistence-unit>
</persistence>
Kirill Smirnov
  • 1,452
  • 2
  • 21
  • 28
  • I personally wouldn't check inside the query if the parameter is null. I doubt you would write a query like `select * from Foo where null is null` as this invalidates the `where` clause completely. Also a where clause like this `where entity.embedded = null` wont return any results (at least not for MSSQL). The query that you most probably need is `FROM TestEntity entity where entity.embedded is not null or entity.embedded = :embedded` – XtremeBaumer Feb 27 '19 at 10:30
  • Thanks for your response, but I want my query to depend on :embedded, not on entity.embedded. – Kirill Smirnov Feb 27 '19 at 10:32
  • it does depend on `:embedded`. `FROM TestEntity entity where entity.embedded = :embedded` is what you might seek then. if `:embedded` is null, then you wont return any records. if it is not null, then you will have a match – XtremeBaumer Feb 27 '19 at 10:35
  • But I want to have all records when it is null :( I want to select all results with no restrictions when :embedded is null and sort results according to passed :embedded when it is not null. – Kirill Smirnov Feb 27 '19 at 10:36
  • It's like a user search - if a user doesn't specify the criteria then I don't want to use this criteria at all. – Kirill Smirnov Feb 27 '19 at 10:38
  • 1
    In that case, a `CriteriaQuery` might be better suited for your issue, as those can be created completely dynamic, depending on the parameters etc – XtremeBaumer Feb 27 '19 at 10:39
  • Hmm I guess I'll try it if I won't find any other solutions. I don't use Criteria API in my project and I think it's better to stick to just one approach in terms of unification. But maybe I should use it after all. – Kirill Smirnov Feb 27 '19 at 10:46
  • `Criteria API` is part of hibernate, so its still within unification. What happens if you include `TestEmbedded` in your persistence.xml? – XtremeBaumer Feb 27 '19 at 10:48
  • I've added it to persistence.xml but I still get the same error. Oh, and for the reference, I took this approach with null checks from here - https://stackoverflow.com/a/43780684/1600356 – Kirill Smirnov Feb 27 '19 at 10:49
  • Have you implemented `equals()` method for both classes? So far the only references to this error were either bugs or errors within the actual query – XtremeBaumer Feb 27 '19 at 10:56
  • I've done it now (equals and hashCode) for both classes, same error. – Kirill Smirnov Feb 27 '19 at 11:02
  • Since embedded objects don't have a distinct database key, Hibernate is unable to distinguish between "missing embedded object" and "embedded object with all fields null". – chrylis -cautiouslyoptimistic- Feb 27 '19 at 11:06
  • Yep, but everything works fine if I leave just the part of the query with null: "FROM TestEntity entity where :embedded is null". And still, Hibernate generates the correct query, just binds the parameters in a strange way. – Kirill Smirnov Feb 27 '19 at 11:16

0 Answers0