DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Implement Hibernate Second-Level Cache With NCache
  • Architectural Miscalculation and Hibernate Problem "Type UUID but Expression Is of Type Bytea"
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps
  • Simplify Java Persistence Using Quarkus and Hibernate Reactive

Trending

  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Why High-Performance AI/ML Is Essential in Modern Cybersecurity
  • A Deep Dive Into Firmware Over the Air for IoT Devices
  • Top Book Picks for Site Reliability Engineers
  1. DZone
  2. Data Engineering
  3. Databases
  4. Hibernate Query by Example (QBE)

Hibernate Query by Example (QBE)

By 
Donat Szilagyi user avatar
Donat Szilagyi
·
Feb. 27, 14 · Interview
Likes (3)
Comment
Save
Tweet
Share
62.2K Views

Join the DZone community and get the full member experience.

Join For Free

What is It

Query by example is an alternative querying technique supported by the main JPA vendors but not by the JPA specification itself.

QBE returns a result set depending on the properties that were set on an instance of the queried class.

So if I create an Address entity and fill in the city field then the query will select all the Address entities having the same city field as the given Address entity.

The typical use case of QBE is evaluating a search form where the user can fill in any search fields and gets the results based on the given search fields. In this case QBE can reduce code size significantly.

When to Use

·  Using many fields of an entity in a query

·  User selects which fields of an Entity to use in a query

·  We are refactoring the entities frequently and don’t want to worry about breaking the queries that rely on them

Limitations

·  QBE is not available in JPA 1.0 or 2.0

·  Version properties, identifiers and associations are ignored

·  The query object should be annotated with @Entity

Test Data

I used the following entities to test the QBE feature of Hibernate:

·  Address (long id, String city, String street, String countryISO2Code, AddressType addressType)

·  AddressType (Integer type, String description)

Imports

The examples will refer to the following classes:

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;
import java.util.List;

Utility Methods

I also made two utility methods to present a list of the two entity types:

private void listAddresses(List<Address> addresses) {
  for (Address address : addresses) {
    System.out.println(address.getId() + ", " +
      address.getCountryISO2Code() + ", " +
      address.getCity() + ", " + address.getStreet() + ", " +
      address.getAddressType().getType() + ", " +
      address.getAddressType().getDescription());
  }
}

private void listAddressTypes(List<AddressType> addressTypes) {
  for (AddressType addressType : addressTypes) {
    System.out.println(addressType.getType() + ", " +
      addressType.getDescription());
  }
}

Example 1: Equals

This example code returns the Address entities matching the given CountryISO2Code and City.

Method:

@Test
public void testEquals() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 2: Id Limitation

This example presents that id fields in the query object are ignored.

Method:

@Test
public void testIdLimitation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  address.setId(100);  // setting id is ignored
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
} 

Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 3: Association Limitation

Associations of the query object are ignored, too.

Method:

@Test
public void testAssociationLimitation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  AddressType addressType = new AddressType();
  addressType.setType(5);
  address.setAddressType(addressType);  // setting an association is ignored
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}


Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 4: Like

QBE supports like in the query object if we enable it with Example.enableLike().

Method:

@Test
public void testLike() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("AT%");
  Example addressExample = Example.create(address).enableLike();
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

 Result:

  83, US, ATLANTA, null, 6, Customer

  184, US, ATLANTA, null, 1, Shipper

  25, US, ATLANTA, null, 1, Shipper

Example 5: ExcludeProperty

We can exclude a property with Example.excludeProperty(String propertyName).

Method:

@Test
public void testExcludeProperty() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("AT%");
  Example addressExample = Example.create(address).enableLike()
  .excludeProperty("countryISO2Code");
  // countryISO2Code is a property of Address
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

Result:

  154, GR, ATHENS, BETA ALPHA Street 5, 2, Consignee

  83, US, ATLANTA, null, 6, Customer

  25, US, ATLANTA, null, 1, Shipper

  184, US, ATLANTA, null, 1, Shipper

Example 6: IgnoreCase

Case-insensitive search is supported by Example.ignoreCase().

Method:

@Test
public void testIgnoreCase() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setDescription("customer");
  Example addressTypeExample = Example.create(addressType).ignoreCase();
  Criteria criteria = session.createCriteria(AddressType.class)
    .add(addressTypeExample);
  listAddressTypes(criteria.list());
}


Result:

  6, Customer

Example 7: ExcludeZeroes

We can ignore 0 values of the query object by Example.excludeZeroes().

Method:

@Test
public void testExcludeZeroes() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setType(0);
  addressType.setDescription("Customer");
  Example addressTypeExample = Example.create(addressType)
    .excludeZeroes();
  Criteria criteria = session.createCriteria(AddressType.class)
    .add(addressTypeExample);
  listAddressTypes(criteria.list());
}

Result:

  6, Customer

Example 8: Combining with Criteria

QBE can be combined with criteria query. In this example we add further restriction to the query object using criteria query.

Method:

@Test
public void testCombiningWithCriteria() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setDescription("Customer");
  Example addressTypeExample = Example.create(addressType);
  Criteria criteria = session
    .createCriteria(AddressType.class).add(addressTypeExample)
    .add(Restrictions.eq("type", 6));
  listAddressTypes(criteria.list());
}

Result:

  6, Customer

Example 9: Association

With criteria query we can filter both sides of an association, using two query objects.

Method:

@Test
public void testAssociation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  AddressType addressType = new AddressType();
  addressType.setType(6);
  Example addressExample = Example.create(address);
  Example addressTypeExample = Example.create(addressType);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample)
    .createCriteria("addressType").add(addressTypeExample);
  // addressType is a property of Address
  listAddresses(criteria.list());
}

Result:

  84, US, BOSTON, null, 6, Customer

  83, US, ATLANTA, null, 6, Customer

  82, US, SAN FRANCISCO, null, 6, Customer

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

EclipseLink

EclipseLink QBE uses QueryByExamplePolicy, ReadObjectQuery and JpaHelper:

QueryByExamplePolicy qbePolicy =newQueryByExamplePolicy();
qbePolicy.excludeDefaultPrimitiveValues();
Address address =newAddress();
address.setCity("CHICAGO");
ReadObjectQuery roq =newReadObjectQuery(address, qbePolicy);
Query query =JpaHelper.createQuery(roq, entityManager);



OpenJPA

OpenJPA uses OpenJPAQueryBuilder:

CriteriaQuery<Address> cq = openJPAQueryBuilder.createQuery(Address.class);
Address address =newAddress();
address.setCity("CHICAGO");
cq.where(openJPAQueryBuilder.qbe(cq.from(Address.class), address);


References

Hibernate:

·  Srinivas Guruzu and Gary Mak: Hibernate Recipes: A Problem-Solution Approach (Apress)

·  http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-examples

·  http://www.java2s.com/Code/Java/Hibernate/CriteriaQBEQueryByExampleCriteria.htm

·  http://www.dzone.com/snippets/hibernate-query-example

·  http://gal-levinsky.blogspot.de/2012/01/qbe-pattern.html

Hibernate associations:

·  http://stackoverflow.com/questions/9309884/query-by-example-on-associations

·  http://stackoverflow.com/questions/8236596/hibernate-query-by-example-equivalent-of-association-criteria-query

JPA:

·  http://stackoverflow.com/questions/2880209/jpa-findbyexample

EclipseLink:

·  http://www.coderanch.com/t/486528/ORM/databases/findByExample-JPA-book

OpenJPA:

·  http://www.ibm.com/developerworks/java/library/j-typesafejpa/#N10C18

Database Hibernate Delivery (commerce) Jackson (API)

Opinions expressed by DZone contributors are their own.

Related

  • Implement Hibernate Second-Level Cache With NCache
  • Architectural Miscalculation and Hibernate Problem "Type UUID but Expression Is of Type Bytea"
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps
  • Simplify Java Persistence Using Quarkus and Hibernate Reactive

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!