[java] Hibernate Query By Example and Projections

To make it short: hibernate doesn't support projections and query by example? I found this post:

The code is this:

User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr))

Like the other poster said, The generated sql keeps having a where class refering to just y0_= ? instead of this_.city.

I already tried several approaches, and searched the issue tracker but found nothing about this.

I even tried to use Projection alias and Transformers, but it does not work:

User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr)).setResultTransformer(Transformers.aliasToBean(User.class));

Has anyone used projections and query by example ?

This question is related to java hibernate criteria projection

The answer is


The problem seems to happen when you have an alias the same name as the objects property. Hibernate seems to pick up the alias and use it in the sql. I found this documented here and here, and I believe it to be a bug in Hibernate, although I am not sure that the Hibernate team agrees.

Either way, I have found a simple work around that works in my case. Your mileage may vary. The details are below, I tried to simplify the code for this sample so I apologize for any errors or typo's:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("sectionHeader", sectionHeaderVar)) // <- Problem!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

Would produce this sql:

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(y1_) like ? ) 

Which was causing an error: java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

But, when I changed my restriction to use "this", like so:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("this.sectionHeader", sectionHeaderVar)) // <- Problem Solved!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

It produced the following sql and my problem was solved.

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(this_.SECTION_HEADER) like ? ) 

Thats, it! A pretty simple fix to a painful problem. I don't know how this fix would translate to the query by example problem, but it may get you closer.


The problem seems to happen when you have an alias the same name as the objects property. Hibernate seems to pick up the alias and use it in the sql. I found this documented here and here, and I believe it to be a bug in Hibernate, although I am not sure that the Hibernate team agrees.

Either way, I have found a simple work around that works in my case. Your mileage may vary. The details are below, I tried to simplify the code for this sample so I apologize for any errors or typo's:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("sectionHeader", sectionHeaderVar)) // <- Problem!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

Would produce this sql:

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(y1_) like ? ) 

Which was causing an error: java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

But, when I changed my restriction to use "this", like so:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("this.sectionHeader", sectionHeaderVar)) // <- Problem Solved!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

It produced the following sql and my problem was solved.

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(this_.SECTION_HEADER) like ? ) 

Thats, it! A pretty simple fix to a painful problem. I don't know how this fix would translate to the query by example problem, but it may get you closer.


The real problem here is that there is a bug in hibernate where it uses select-list aliases in the where-clause:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-817

Just in case someone lands here looking for answers, go look at the ticket. It took 5 years to fix but in theory it'll be in one of the next releases and then I suspect your issue will go away.


The real problem here is that there is a bug in hibernate where it uses select-list aliases in the where-clause:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-817

Just in case someone lands here looking for answers, go look at the ticket. It took 5 years to fix but in theory it'll be in one of the next releases and then I suspect your issue will go away.


ProjectionList pl = Projections.projectionList();
pl.add(Projections.property("id"));
pl.add(Projections.sqlProjection("abs(`pageNo`-" + pageNo + ") as diff", new String[] {"diff"}, types ), diff); ---- solution
crit.addOrder(Order.asc("diff"));
crit.setProjection(pl);

ProjectionList pl = Projections.projectionList();
pl.add(Projections.property("id"));
pl.add(Projections.sqlProjection("abs(`pageNo`-" + pageNo + ") as diff", new String[] {"diff"}, types ), diff); ---- solution
crit.addOrder(Order.asc("diff"));
crit.setProjection(pl);

I'm facing a similar problem. I'm using Query by Example and I want to sort the results by a custom field. In SQL I would do something like:

select pageNo, abs(pageNo - 434) as diff
from relA
where year = 2009
order by diff

It works fine without the order-by-clause. What I got is

Criteria crit = getSession().createCriteria(Entity.class);
crit.add(exampleObject);
ProjectionList pl = Projections.projectionList();
pl.add( Projections.property("id") );
pl.add(Projections.sqlProjection("abs(`pageNo`-"+pageNo+") as diff", new String[] {"diff"}, types ));
crit.setProjection(pl);

But when I add

crit.addOrder(Order.asc("diff"));

I get a org.hibernate.QueryException: could not resolve property: diff exception. Workaround with this does not work either.

PS: as I could not find any elaborate documentation on the use of QBE for Hibernate, all the stuff above is mainly trial-and-error approach


I'm facing a similar problem. I'm using Query by Example and I want to sort the results by a custom field. In SQL I would do something like:

select pageNo, abs(pageNo - 434) as diff
from relA
where year = 2009
order by diff

It works fine without the order-by-clause. What I got is

Criteria crit = getSession().createCriteria(Entity.class);
crit.add(exampleObject);
ProjectionList pl = Projections.projectionList();
pl.add( Projections.property("id") );
pl.add(Projections.sqlProjection("abs(`pageNo`-"+pageNo+") as diff", new String[] {"diff"}, types ));
crit.setProjection(pl);

But when I add

crit.addOrder(Order.asc("diff"));

I get a org.hibernate.QueryException: could not resolve property: diff exception. Workaround with this does not work either.

PS: as I could not find any elaborate documentation on the use of QBE for Hibernate, all the stuff above is mainly trial-and-error approach


I do not really think so, what I can find is the word "this." causes the hibernate not to include any restrictions in its query, which means it got all the records lists. About the hibernate bug that was reported, I can see it's reported as fixed but I totally failed to download the Patch.


I do not really think so, what I can find is the word "this." causes the hibernate not to include any restrictions in its query, which means it got all the records lists. About the hibernate bug that was reported, I can see it's reported as fixed but I totally failed to download the Patch.


Questions with java tag:

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log getting " (1) no such column: _id10 " error Instantiating a generic type When to create variables (memory management) java doesn't run if structure inside of onclick listener String method cannot be found in a main class method Are all Spring Framework Java Configuration injection examples buggy? Calling another method java GUI I need to know how to get my program to output the word i typed in and also the new rearranged word using a 2D array Java and unlimited decimal places? Read input from a JOptionPane.showInputDialog box Cannot retrieve string(s) from preferences (settings) strange error in my Animation Drawable Two Page Login with Spring Security 3.2.x Hadoop MapReduce: Strange Result when Storing Previous Value in Memory in a Reduce Class (Java) Got a NumberFormatException while trying to parse a text file for objects Best way for storing Java application name and version properties Call japplet from jframe FragmentActivity to Fragment Comparing two joda DateTime instances Maven dependencies are failing with a 501 error IntelliJ: Error:java: error: release version 5 not supported Has been compiled by a more recent version of the Java Runtime (class file version 57.0) Why am I getting Unknown error in line 1 of pom.xml? Gradle: Could not determine java version from '11.0.2' Error: Java: invalid target release: 11 - IntelliJ IDEA Android Gradle 5.0 Update:Cause: org.jetbrains.plugins.gradle.tooling.util Why is 2 * (i * i) faster than 2 * i * i in Java? must declare a named package eclipse because this compilation unit is associated to the named module How do I install Java on Mac OSX allowing version switching? How to install JDK 11 under Ubuntu? Java 11 package javax.xml.bind does not exist IntelliJ can't recognize JavaFX 11 with OpenJDK 11 Difference between OpenJDK and Adoptium/AdoptOpenJDK OpenJDK8 for windows How to allow all Network connection types HTTP and HTTPS in Android (9) Pie? Find the smallest positive integer that does not occur in a given sequence Error: JavaFX runtime components are missing, and are required to run this application with JDK 11 How to uninstall Eclipse? Failed to resolve: com.google.firebase:firebase-core:16.0.1 How to resolve Unable to load authentication plugin 'caching_sha2_password' issue

Questions with hibernate tag:

Hibernate Error executing DDL via JDBC Statement How does spring.jpa.hibernate.ddl-auto property exactly work in Spring? Error creating bean with name 'entityManagerFactory' defined in class path resource : Invocation of init method failed JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory Disable all Database related auto configuration in Spring Boot Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] HikariCP - connection is not available Hibernate-sequence doesn't exist How to find distinct rows with field in list using JPA and Spring? Spring Data JPA and Exists query How to log SQL statements in Spring Boot? UnsatisfiedDependencyException: Error creating bean with name 'entityManagerFactory' How does the FetchMode work in Spring Data JPA Spring Data JPA map the native query result to Non-Entity POJO Native query with named parameter fails with "Not all named parameters have been set" Unable to get spring boot to automatically create database schema Default fetch type for one-to-one, many-to-one and one-to-many in Hibernate org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set Spring Hibernate - Could not obtain transaction-synchronized Session for current thread TransactionRequiredException Executing an update/delete query hibernate - get id after save object What is the use of the @Temporal annotation in Hibernate? Could not commit JPA transaction: Transaction marked as rollbackOnly Spring Boot + JPA : Column name annotation ignored Spring Boot - Handle to Hibernate SessionFactory No serializer found for class org.hibernate.proxy.pojo.javassist.Javassist? javax.validation.ValidationException: HV000183: Unable to load 'javax.el.ExpressionFactory' org.hibernate.NonUniqueResultException: query did not return a unique result: 2? What is the difference between Hibernate and Spring Data JPA org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'MyController': org.hibernate.MappingException: Unknown entity: annotations.Users Ignore fields from Java object dynamically while sending as JSON from Spring MVC org.hibernate.hql.internal.ast.QuerySyntaxException: table is not mapped How to fix Hibernate LazyInitializationException: failed to lazily initialize a collection of roles, could not initialize proxy - no Session spring data jpa @query and pageable JPA COUNT with composite primary key query not working How to resolve "could not execute statement; SQL [n/a]; constraint [numbering];"? How to fix org.hibernate.LazyInitializationException - could not initialize proxy - no Session org.hibernate.QueryException: could not resolve property: filename Difference between save and saveAndFlush in Spring data jpa Why do I need to configure the SQL dialect of a data source? Error creating bean with name 'entityManagerFactory how to fetch data from database in Hibernate what is the use of annotations @Id and @GeneratedValue(strategy = GenerationType.IDENTITY)? Why the generationtype is identity? [Ljava.lang.Object; cannot be cast to BeanFactory not initialized or already closed - call 'refresh' before could not extract ResultSet in hibernate Joining two table entities in Spring Data JPA Hibernate: best practice to pull all lazy collections UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only

Questions with criteria tag:

Hibernate Criteria Join with 3 Tables Hibernate Criteria Restrictions AND / OR combination How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery? Hibernate Criteria for Dates Getting a count of rows in a datatable that meet certain criteria How to get SQL from Hibernate Criteria API (*not* for logging) How to get distinct results in hibernate with joins and row-based limiting (paging)? JPA and Hibernate - Criteria vs. JPQL or HQL Hibernate Query By Example and Projections

Questions with projection tag:

How to select a single field for all documents in a MongoDB collection? Retrieve only the queried element in an object array in MongoDB collection Hibernate Query By Example and Projections