Thursday, March 6, 2014

Querying Hibernate with Querydsl

Querydsl provides a typesafe querying layer on top of JPA, JDO, JDBC and other backends. This blog post presents a simple tutorial on how to get started with querying on Hibernate using Querydsl.

Querydsl for JPA/Hibernate is an alternative to both JPQL and JPA 2 Criteria queries. It combines the dynamic nature of Criteria queries with the expressiveness of JPQL and all that in a fully typesafe manner.

If you are unsure if switching to Querydsl from JPA 2 Criteria is worth a try, see this comparison.

Maven integration


To get started with Querydsl for Hibernate using a Maven 2 based build environment, follow the following steps.

Add the following dependencies to your Maven project and make sure that the Maven 2 repo of Mysema Source is accessible from your POM :

<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-apt</artifactId>
  <version>1.8.2</version>
  <scope>provided</scope>
</dependency
     
<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-hql</artifactId>
  <version>1.8.2</version>
</dependency>
 
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-log4j12</artifactId>
  <version>1.5.2</version>
</dependency>  

Querydsl uses the Annotation Processing Tool of Java 6 for code generation which needs to be configured as well :

<project>
  <build>
  <plugins>
    ...
    <plugin>
      <groupId>com.mysema.maven</groupId>
      <artifactId>maven-apt-plugin</artifactId>
      <version>0.3.2</version>
      <executions>
        <execution>
          <goals>
            <goal>process</goal>
          </goals>
          <configuration>
            <outputDirectory>target/generated-sources/java</outputDirectory>
            <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
          </configuration>
        </execution>
      </executions>
    </plugin>
    ...
  </plugins>
  </build>
</project>

The JPAAnnotationProcessor finds domain types annotated with the javax.persistence.Entity annotation and generates query types for them.

If you use Hibernate annotations in your domain types you should use the APT processorcom.mysema.query.apt.hibernate.HibernateAnnotationProcessor instead.

Run clean install and you will get your Query types generated into target/generated-sources/java.

If you use Eclipse, run mvn eclipse:eclipse to update your Eclipse project to include target/generated-sources/java as a source folder.

Now you are able to construct Querydsl query instances and instances of the query domain model.


Ant integration


If you have an Ant based build environment you can use one of the packaged releases from theDownloads page.

Place the jar files from the full-deps bundle on your classpath and use the following tasks for Querydsl code generation :

   <javac srcdir="${src}" classpathref="cp">
     <compilerarg value="-proc:only"/>     
     <compilerarg value="-processor"/>
     <compilerarg value="com.mysema.query.apt.jpa.JPAAnnotationProcessor"/>
     <compilerarg value="-s"/>
     <compilerarg value="${generated}"/>
   </javac>
     
   
   <javac classpathref="cp" destdir="${build}">     
     <src path="${src}"/>
     <src path="${generated}"/>
   </javac>

Replace src with your main source folder, generated with your folder for generated sources and buildwith your target folder.

Using query types


To create queries with Querydsl you need to instantiate variables and Query implementations. We will start with the variables.

Let's assume that your project has the following domain type :

@Entity
public class Customer {
    private String firstName;
    private String lastName;
 
    public String getFirstName(){
        return firstName;
    }
 
    public String getLastName(){
        return lastName;
    }
 
    public void setFirstName(String fn){
        firstName = fn;
    }
 
    public void setLastName(String ln)[
        lastName = ln;
    }
}

Querydsl will generate a query type with the simple name QCustomer into the same package as Customer. QCustomer can be used as a statically typed variable in Querydsl queries as a representative for the Customer type.

QCustomer has a default instance variable which can be accessed as a static field :

QCustomer customer = QCustomer.customer;

Alternatively you can define your own Customer variables like this :

QCustomer customer = new QCustomer("myCustomer");

Querying


For the HQL-module HibernateQuery is the main Query implementation. It is instantiated like this :

// where session is a Hibernate session
HQLQuery query = new HibernateQuery (session);

To use the JPA API instead of the Hibernate API, you can instantiate a JPAQuery like this :

// where entityManager is a JPA EntityManager  
HQLQuery query = new JPAQuery (entityManager);

To retrieve the customer with the first name Bob you would construct a query like this :

QCustomer customer = QCustomer.customer;
HQLQuery query = new HibernateQuery (session);
Customer bob = query.from(customer)
  .where(customer.firstName.eq("Bob"))
  .uniqueResult(customer);

The from call defines the query source, the where part defines the filter and uniqueResult defines the projection and tells Querydsl to return a single element. Easy, right?

To create a query with multiple sources you just use the HQLQuery interface like this :

query.from(customer, company);   

And to use multiple filters use it like this

query.from(customer)
    .where(customer.firstName.eq("Bob"), customer.lastName.eq("Wilson"));  

Or like this

query.from(customer)
    .where(customer.firstName.eq("Bob").and(customer.lastName.eq("Wilson")));

In native JPQL form the query would be written like this :

from Customer as customer
    where customer.firstName = "Bob" and customer.lastName = "Wilson"

Using joins


Querydsl supports the following join variants in JPQL : inner join, join, left join and full join. Join usage is typesafe, and follows the following pattern :

query.from(cat)
    .innerJoin(cat.mate, mate)
    .leftJoin(cat.kittens, kitten)
    .list(cat);

The native JPQL version of the query would be

from Cat as cat
    inner join cat.mate as mate
    left outer join cat.kittens as kitten

Another example

query.from(cat)
    .leftJoin(cat.kittens, kitten)
    .on(kitten.bodyWeight.gt(10.0))
    .list(cat);

With the following JPQL version

from Cat as cat
    left join cat.kittens as kitten
    with kitten.bodyWeight > 10.0 

General usage


Use the the cascading methods of the HQLQuery interface like this

  • from : Define the query sources here.
  • innerJoin, join, leftJoin, fullJoin, on : Define join elements using these constructs. For the join methods the first argument is the join source and the second the target (alias).
  • where : Define the query filters, either in varargs form separated via commas or cascaded via the and-operator.
  • groupBy : Define the group by arguments in varargs form.
  • having : Define the having filter of the "group by" grouping as an varags array of EBoolean expressions.
  • orderBy : Define the ordering of the result as an varargs array of order expressions. Use asc() and desc() on numeric, string and other comparable expression to access the OrderSpecifier instances.
  • limit, offset, restrict : Define the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

Ordering


The syntax for declaring ordering is

query.from(customer)
    .orderBy(customer.lastName.asc(), customer.firstName.desc())
    .list(customer);

which is equivalent to the following native JPQL

from Customer as customer
    order by customer.lastName asc, customer.firstName desc

Grouping


Grouping can be done in the following form

query.from(customer)
    .groupBy(customer.lastName)
    .list(customer.lastName);

which is equivalent to the following native JPQL

select customer.lastName
    from Customer as customer
    group by customer.lastName

Delete clauses


Delete clauses in Querydsl follow a simple delete-where-execute form. Here are some examples :

QCat cat = QCat.cat;
// delete all cats
new HibernateDeleteClause(session, cat).execute();
// delete all cats with kittens
new HibernateDeleteClause(session, cat).where(cat.kittens.isNotEmpty()).execute(); 

The second parameter of the HibernateDeleteClause constructor is the entity to be deleted. The where call is optional and the execute call performs the deletion and returns the amount of deleted entities.

For JPA based Delete usage, use the JPADeleteClause instead.

Update clauses


Update clauses in Querydsl follow a simple update-set/where-execute form. Here are some examples :

QCat cat = QCat.cat;
// rename cats named Bob to Bobby
new HibernateUpdateClause(session, cat).where(cat.name.eq("Bob"))
    .set(cat.name, "Bobby")
    .execute(); 

The second parameter of the HibernateUpdateClause constructor is the entity to be updated. The set invocations define the property updates in SQL-Update-style and the execute call performs the Update and returns the amount of updated entities.

For JPA based Update usage, use the JPAUpdateClause instead.

Subqueries


To create a subquery you create a HQLSubQuery instance, define the query parameters via from, where etc and use unique or list to create a subquery, which is just a type-safe Querydsl expression for the query. unique is used for a unique (single) result and list for a list result.

query.from(department)
    .where(department.employees.size().eq(
        new HQLSubQuery().from(d).unique(d.employees.size().max())
     )).list(department);

Another example

query.from(employee)
    .where(employee.weeklyhours.gt(
        new HQLSubQuery().from(employee.department.employees, e)
        .where(e.manager.eq(employee.manager))
        .unique(e.weeklyhours.avg())
    )).list(employee);

Exposing the original query


If you need to do tune the original Query before the execution of the query you can expose it like this :

HibernateQuery query = new HibernateQuery(session);
org.hibernate.Query hibQuery = query.from(employee).createQuery(employee);
hibQuery.setResultTransformer(someTransformer);
List results = hibQuery.list();

Using Native SQL in Hibernate queries


Querydsl supports Native SQL in Hibernate via the HibernateSQLQuery class.

To use it, you must generate Querydsl query types for your SQL schema. This can be done for example with the following Maven configuration :

<plugin>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-maven-plugin</artifactId>
  <version>${project.version}</version>
  <executions>
    <execution>
      <goals>
        <goal>export</goal>
      </goals>
    </execution>           
  </executions>
  <configuration>
    <jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver>
    <jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
    <packageName>com.mycompany.mydomain</packageName>
    <targetFolder>target/generated-sources/java</targetFolder>
  </configuration>
  <dependencies>
    <dependency>
      <groupId>org.apache.derby</groupId>
      <artifactId>derby</artifactId>
      <version>${derby.version}</version>
    </dependency>
  </dependencies>
</plugin>

When the query types have successfully been generated into the location of your choice, you can use them in your queries.

Single column query :

// serialization templates
SQLTemplates templates = new DerbyTemplates();
// query types (S* for SQL, Q* for domain types)
SAnimal cat = new SAnimal("cat");  
SAnimal mate = new SAnimal("mate");
QCat catEntity = QCat.cat;         
  
HibernateSQLQuery query = new HibernateSQLQuery(session, templates);
List names = query.from(cat).list(cat.name);

Query multiple columns :

query = new HibernateSQLQuery(session, templates);
List rows = query.from(cat).list(cat.id, cat.name);

Query all columns :

List rows = query.from(cat).list(cat.all());

Query in SQL, but project as entity :

query = new HibernateSQLQuery(session, templates);
List cats = query.from(cat).orderBy(cat.name.asc()).list(catEntity);

Query with joins :

query = new HibernateSQLQuery(session, templates);
cats = query.from(cat)
    .innerJoin(mate).on(cat.mateId.eq(mate.id))
    .where(cat.dtype.eq("Cat"), mate.dtype.eq("Cat"))
    .list(catEntity);

Query and project into DTO :

query = new HibernateSQLQuery(session, templates);
List catDTOs = query.from(cat)
    .orderBy(cat.name.asc())
    .list(EConstructor.create(CatDTO.class, cat.id, cat.name));

If you are using the JPA API instead of the Hibernate API, then use JPASQLQuery instead of HibernateSQLQuery.

Where to go next


For an up-to-date guide on how to use Querydsl with Hibernate, see the latest Reference Documentation.

And if you have any issues, questions or ideas for enhancement post a thread in the Mysema Source Forum.

Happy querying!

8 comments:

oakleyses said...

louis vuitton handbags, oakley sunglasses, louboutin, longchamp outlet, nike shoes, louis vuitton outlet stores, chanel handbags, burberry outlet, prada outlet, jordan shoes, tiffany and co, michael kors outlet, tory burch outlet, louis vuitton outlet, longchamp handbags, nike free, true religion jeans, michael kors outlet, kate spade outlet, polo ralph lauren outlet, tiffany and co, prada handbags, polo ralph lauren outlet, michael kors outlet, michael kors outlet, longchamp handbags, oakley sunglasses, ray ban sunglasses, kate spade handbags, burberry outlet, louis vuitton outlet, louboutin outlet, louboutin, coach factory outlet, air max, air max, coach outlet, gucci outlet, christian louboutin shoes, michael kors outlet, coach purses, ray ban sunglasses, michael kors outlet, louis vuitton, coach outlet store online, true religion jeans, oakley sunglasses cheap

oakleyses said...

ralph lauren, lululemon, air max, hollister, north face, nike air max, polo lacoste, vanessa bruno, timberland, vans pas cher, louboutin, louis vuitton, oakley pas cher, air max pas cher, nike roshe run, air max, true religion outlet, barbour, sac longchamp, air force, hollister, sac louis vuitton, nike free, polo ralph lauren, nike trainers, louis vuitton uk, nike roshe, sac hermes, longchamp, michael kors, sac burberry, sac guess, mulberry, new balance pas cher, converse pas cher, sac louis vuitton, hogan outlet, nike tn, north face, true religion outlet, ray ban pas cher, michael kors, air jordan, nike blazer, nike free pas cher, michael kors pas cher, abercrombie and fitch, ray ban sunglasses

oakleyses said...

mac cosmetics, mont blanc, marc jacobs, canada goose outlet, nike huarache, vans shoes, soccer jerseys, hollister, giuseppe zanotti, beats by dre, abercrombie and fitch, longchamp, insanity workout, celine handbags, bottega veneta, ghd, nfl jerseys, north face outlet, chi flat iron, ugg boots, birkin bag, ugg australia, canada goose, herve leger, ugg pas cher, rolex watches, valentino shoes, canada goose uk, canada goose, ferragamo shoes, canada goose, ugg boots, uggs outlet, north face jackets, soccer shoes, asics running shoes, new balance shoes, p90x, lululemon outlet, canada goose jackets, mcm handbags, instyler, babyliss pro, ugg, wedding dresses, jimmy choo outlet, reebok outlet, nike roshe run

oakleyses said...

parajumpers, karen millen, air max, converse, pandora charms, moncler, louboutin, moncler, links of london, lancel, juicy couture outlet, oakley, hollister, pandora charms, supra shoes, thomas sabo, canada goose, gucci, wedding dresses, timberland boots, swarovski crystal, air max, coach outlet store online, moncler, ray ban, canada goose, moncler, ugg, louis vuitton, swarovski, hollister, montre homme, moncler, hollister clothing store, ralph lauren, rolex watches, moncler outlet, moncler, iphone 6 cases, baseball bats, juicy couture outlet, toms shoes, vans, pandora jewelry, ugg, converse shoes

oakleyses said...

jordan pas cher, chanel handbags, nike outlet, michael kors pas cher, kate spade outlet, replica watches, longchamp pas cher, nike free, jordan shoes, christian louboutin shoes, nike free run, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, polo ralph lauren, ugg boots, christian louboutin uk, air max, louis vuitton outlet, ugg boots, tiffany jewelry, polo ralph lauren outlet online, burberry pas cher, prada outlet, nike air max, gucci handbags, sac longchamp pas cher, ray ban sunglasses, louboutin pas cher, louis vuitton, uggs on sale, tiffany and co, oakley sunglasses, louis vuitton outlet, ray ban sunglasses, longchamp outlet, louis vuitton, longchamp outlet, replica watches, nike roshe, polo outlet, oakley sunglasses, cheap oakley sunglasses, oakley sunglasses wholesale, christian louboutin, christian louboutin outlet, tory burch outlet

oakleyses said...

lululemon canada, nike air max, burberry outlet, oakley pas cher, burberry handbags, coach outlet store online, kate spade, michael kors outlet, michael kors, nike air force, true religion jeans, true religion outlet, michael kors, polo lacoste, nike tn, new balance, abercrombie and fitch uk, michael kors outlet, uggs outlet, michael kors outlet, ralph lauren uk, michael kors outlet online, replica handbags, coach outlet, true religion outlet, coach purses, nike free uk, sac vanessa bruno, mulberry uk, michael kors outlet online, michael kors outlet online, michael kors outlet online, north face, uggs outlet, converse pas cher, hogan outlet, nike air max uk, hollister pas cher, sac hermes, nike roshe run uk, hollister uk, nike air max uk, true religion outlet, timberland pas cher, vans pas cher, ray ban pas cher, guess pas cher, ray ban uk

oakleyses said...

ipad cases, beats by dre, iphone 6 cases, ferragamo shoes, wedding dresses, hollister clothing, p90x workout, instyler, asics running shoes, nike huaraches, hermes belt, north face outlet, s6 case, babyliss, nfl jerseys, hollister, iphone 6 plus cases, insanity workout, ralph lauren, iphone cases, iphone 6s plus cases, bottega veneta, new balance shoes, longchamp uk, iphone 6s cases, giuseppe zanotti outlet, nike roshe run, mont blanc pens, herve leger, oakley, lululemon, timberland boots, nike air max, iphone 5s cases, ghd hair, valentino shoes, louboutin, nike trainers uk, celine handbags, north face outlet, baseball bats, vans outlet, abercrombie and fitch, chi flat iron, soccer jerseys, mac cosmetics, mcm handbags, soccer shoes, jimmy choo outlet, reebok outlet

oakleyses said...

supra shoes, marc jacobs, canada goose, converse outlet, toms shoes, ugg uk, ugg pas cher, canada goose outlet, canada goose, nike air max, louis vuitton, karen millen uk, doudoune moncler, converse, louis vuitton, vans, gucci, montre pas cher, moncler outlet, canada goose uk, canada goose outlet, moncler, pandora jewelry, barbour uk, moncler outlet, juicy couture outlet, louis vuitton, canada goose, pandora uk, coach outlet, swarovski crystal, ugg,uggs,uggs canada, juicy couture outlet, links of london, louis vuitton, louis vuitton, hollister, swarovski, canada goose outlet, ray ban, moncler, ugg, barbour, ugg,ugg australia,ugg italia, replica watches, pandora charms, moncler uk, pandora jewelry, wedding dresses, canada goose jackets, hollister, lancel