2010-11-10

MongoDB 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 MongoDB using Querydsl.

Maven integration


To get started with Querydsl for MongoDB 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-mongodb</artifactId>
  <version>${querydsl.version}</version>
</dependency>

<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-log4j12</artifactId>
  <version>1.6.1</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>1.0</version>
      <executions>
        <execution>
          <goals>
            <goal>process</goal>
          </goals>
          <configuration>
            <outputDirectory>${generatedSources}<outputDirectory>
            <processor>com.mysema.query.mongodb.morphia.MorphiaAnnotationProcessor</processor>
          </configuration>
        </execution>
      </executions>
    </plugin>
    ...
  </plugins>
  </build>
</project>

The MorphiaAnnotationProcessor finds domain types annotated with the com.google.code.morphia.annotations.Entity and com.google.code.morphia.annotations.Embedded annotations and generates query types for them.

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 the Downloads page.

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

<!-- APT based code generation -->
   <javac srcdir="${src}" classpathref="cp">
     <compilerarg value="-proc:only"/>      
     <compilerarg value="-processor"/>
     <compilerarg value="com.mysema.query.mongodb.morphia.MorphiaAnnotationProcessor"/>
     <compilerarg value="-s"/>
     <compilerarg value="${generated}"/>
   </javac>
    
   <!-- compilation -->
   <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 build with 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 @Id ObjectId id;

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

    public ObjectId getId() {
        return id;
    }

    public void setId(ObjectId id) {
        this.id = id;
    }
}

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 MongoDB-module MorphiaQuery is the main Query implementation. It is instantiated like this :

// test database
String dbname = "testdb"; 
// Morphia configuration
Morphia morphia = new Morphia().map(Customer.class); 
// datastore
Datastore ds = morphia.createDatastore(dbname); 
QCustomer customer = QCustomer.customer;
MorphiaQuery<Customer> query = new MorphiaQuery<Customer>(morphia, ds, customer);

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

QCustomer customer = QCustomer.customer;
MorphiaQuery<Customer> query = new MorphiaQuery<Customer>(morphia, ds, customer);
Customer bob = query.where(customer.firstName.eq("Bob"))
  .uniqueResult();

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

And to use multiple filters use it like this

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

Or like this

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


General usage


Use the the cascading methods of the MorphiaQuery class like this

  • where : Define the query filters, either in varargs form separated via commas or cascaded via the and-operator.
  • 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
    .orderBy(customer.lastName.asc(), customer.firstName.desc())
    .list();


Where to go next


For an up-to-date guide on how to use Querydsl with MongoDB, 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!

2010-10-04

SQL queries in Scala

In a previous blog post I showed you how Querydsl and Scala works with JPA, JDO and Mongodb. This blog post shows you how to use Scala queries on top of Querydsl SQL.

If you are using Maven you need the following two dependencies :

<dependency>
      <groupId>com.mysema.querydsl</groupId>
      <artifactId>querydsl-scala</artifactId>
      <version>${querydsl.version}</version>
</dependency>

<dependency>
      <groupId>com.mysema.querydsl</groupId>
      <artifactId>querydsl-sql</artifactId>
      <version>${querydsl.version}</version>
</dependency>

Like with Querydsl SQL for Java you need to generate Query types to be able to construct your queries. The following two unit test methods show how this is done :

val templates = new HSQLDBTemplates()
  var connection: Connection = _

  @Before
  def setUp() {
    // set up the JDBC connection for JDBC metadata access
    Class.forName("org.hsqldb.jdbcDriver")
    val url = "jdbc:hsqldb:mem:testdb"
    connection = DriverManager.getConnection(url, "sa", "")
  }

  @Test
  def Generation_without_Beantypes() {
    val namingStrategy = new DefaultNamingStrategy()
    val serializer = new ScalaMetaDataSerializer("Q", namingStrategy)
    val exporter = new MetaDataExporter("Q", "test", new File("target/gen1"), namingStrategy, serializer)
    exporter.setCreateScalaSources(true)
    exporter.export(connection.getMetaData())
  }

  @Test
  def Generation_with_Beantypes() {
    val namingStrategy = new DefaultNamingStrategy()
    val serializer = new ScalaMetaDataSerializer("Q", namingStrategy)
    val beanSerializer = new ScalaBeanSerializer()
    val exporter = new MetaDataExporter("Q", "com.mysema.example", new File("target/gen2"), namingStrategy, serializer, beanSerializer)
    exporter.setCreateScalaSources(true)
    exporter.export(connection.getMetaData())
  }

The MetaDataExporter can be used with and without Bean type generation. If you just want to write SQL queries and you don't need a JavaBean based model for projection, insertion and update, you can use it without bean projection. If you want to mirror your SQL schema into a minimal domain model and use it ORM-like then generate the bean types.

Make sure that the target folder (target/gen1 and target/gen2 in the two examples) is in your source path. And replace com.mysema.example with a target package of your choice.

Now you can use the generated query types for querying :

@Test
  def Querying() {
    val survey = new QSurvey("survey")
    val employee = new QEmployee("employee")

    assertEquals("abc", query from survey where (survey.id eq 1) 
      .uniqueResult survey.name)

    assertEquals("def", query from survey where (survey.id eq 2) 
      .uniqueResult survey.name)

    assertEquals("Bob", query from employee 
      .where (employee.lastname eq "Smith") 
      .uniqueResult employee.firstname)

    assertEquals("John", query from employee 
      .where (employee.lastname eq "Doe") 
      .uniqueResult employee.firstname)
  }

  def query = new SQLQueryImpl(connection, templates)

Querydsl SQL for Scala doesn't use the $-sign based escape syntax for expression construction nor alias variables. Instead code generation is used to output the Query model as a set of Scala classes that reflect the tables, columns and keys of your relational schema.

You can instantiate the generated Q-types instead. Alternatively you can also use a more SQL-like syntax:

val survey = QSurvey as "survey"

For more details on how to use Querydsl SQL see the related section in the latest revision of the Reference Documentation.

Recent released have changed the Querydsl Scala API. Visit the Reference Documentation for updated documentation.

2010-10-01

Using Querydsl In Eclipse

Querydsl usage in Eclipse is now easier than ever. Since Querydsl 2.0 we have improved APT support in Querydsl to support incremental code generation in IDEs. We have also improved the release bundles to streamline the APT configuration in Java IDEs like Eclipse.

The APT usage improvements affect the JPA, JDO and MongoDB modules. Instead of a set of screenshots we have prepared a screencast to showcase the new APT features in Querydsl in an Eclipse context.

To enable APT usage in Eclipse you need to
  • set the Enable annotation processing checkbox via Properties > Java Compiler > Annotation Processing,
  • add the APT option defaultOverwrite = true if you are using Eclipse Helios and
  • set the Factory Path to one of the apt-one-jar files included in your release bundle.

The screencast uses the Querydsl JPA module in combination with Hibernate and shows the creation of a Java project, the creation of two simple domain types and the creation of a minimal demo service which presents the simplicity of Querydsl query usage.

2010-09-28

Querydsl 2.0 has been released!

Querydsl 2.0 has been released! After two alpha and two beta releases we are proud to announce the final 2.0 release. The biggest single change in Querydsl 2.0 compared to previous 1.* releases is the improved Expression API. We got rid of the coupling between the query construction contract and the DSL features of the Expression API.

The Expression API is now divided into two parts, an interface based part which describes the archetypes of Expressions which are used in Query construction, and a class based part, which provides the standard Expression DSL.

Here is an UML overview of the new Expression model : Expression model changes

In addition to the improvements of the Expression API there is improved Eclipse IDE support, initial Scala and Mongodb support.

Querying with Scala

Scala support for Querydsl has been on the roadmap for some time, and with the Querydsl 2.0 release we got initial support for using Querydsl in Scala applications released.

The goal was to use the Query interfaces as such, and to provide an alternative expression DSL better suited for Scala usage. With most Querydsl modules APT is used for code generation. As there is no equivalent for Scala we didn't try to provide code generation features for Scala domain models.

Instead we combined the Alias-feature of the Querydsl Core module with the Scala expression DSL. The result is in our opinion quite compact and readable.

For the moment Querydsl with Scala can be used with JPA, JDO and Mongodb. To get started, you will need the additional querydsl-scala dependency in your project in addition to the dependencies for your Querydsl backend support. Querydsl for Scala has been tested against Scala 2.8.0.

Using Maven you would need to declare the following additional dependency :

<dependency>
      <groupId>com.mysema.querydsl</groupId>
      <artifactId>querydsl-scala</artifactId>
      <version>${querydsl.version}</version>
</dependency>

Here is a minimal example with JPA/Hibernate :

Domain model :

@Entity
class User {
  @BeanProperty
  @Id
  var id: Integer = _;
  @BeanProperty
  var userName: String = _;
  @BeanProperty
  @ManyToOne
  var department: Department = _;
}

@Entity
class Department {
  @BeanProperty
  @Id
  var id: Integer = _;
  @BeanProperty
  var name: String = _;
}

And here are some query examples

import com.mysema.query.scala.Conversions._
import com.mysema.query.jpa.impl.JPAQuery

import com.mysema.query.types.path._
import org.junit.Test

class JPAQueryTest {

  val person = alias(classOf[Person])

  @Test
  def Various() {
    // list
    query from person where (person.firstName $like "Rob%") 
      .list person

    // unique result
    query from person where (person.firstName $like "Rob%") 
      .unique person

    // long where
    query from person 
      .where (person.firstName $like "Rob%", person.lastName $like "An%") 
      .list person

    // order
    query from person orderBy (person.firstName asc) list person

    // not null 
    query from person 
      .where (person.firstName $isEmpty, person.lastName $isNotNull) 
      .list person
  }

  def query() = new JPAQuery(entityManager)

}

The main import for Querydsl Scala integration is the following

import com.mysema.query.scala.Conversions._

The factory method for query creation is

def query() = new JPAQuery(entityManager)

In addition to queries you need variables which can be created like this

var person = alias(classOf[Person])

The person variable is a proxy instance of the the Person class which can be used in queries. Now you can construct your queries, populate them via from-where-...-orderBy calls and get the projection out via list/uniqueResult/listResults calls.

Querydsl expressions are constructed via method calls starting with the "$" sign.

With the Querydsl Java API a simple like expression would be constructed like this :

person.firstName.like("Rob%")

Using the Scala API it is

person.firstName $like "Rob%"

For more examples see src/test/scala in SVN.

2010-09-10

Secure Registration Method

Many services require users to register using real email addresses. Usually this is done so that the system sends a confirmation message to the email address after the user has registered. This requires a two phase flow in which the account details are first persisted into an inactive state and later activated. An alternative is to make the account temporarily active to let the user in right away, but close it after a while if there is no email confirmation.

There are several drawbacks to this two phase registration method:
  1. Increased complexity in handling account activation

  2. Unconfirmed users are persisted and need to be cleaned up from the database from time to time

  3. If the user is let in temporarily right away, there have to be additional measures to guard against bots and to authorize actions that are reserved for actually confirmed accounts.

  4. It is very inconvenient for the user if she first gives all her details and then somehow fails to confirm the account, e.g. due to a typo in the email address.

A much simpler, yet secure way of registration starts by confirming the email address. First the user gives her email address and the system sends her an email containing a unique registration link. With a properly constructed link, nothing needs to be persisted on the server side at this point. Only when the user follows the link, is she requested for other account details. The account is then created directly into an active state and the user is let in with full access rights.

The trick is in the link itself. It should be constructed to be a) hard to guess and tamper with and b) expiring by itself after a fixed amount of time. This can be achieved easily by adding a timestamp and the email address itself as parameters to the link and using a Message Authentication Code (MAC) to ensure that these parameters are not tampered with:
https://yourdomain.com/register
?
timestamp=123456789&email=john.doe@example.com&mac=MAC
in which the MAC is constructed using a hash function like SHA:
sha(123456789;john.doe@example.com;SECRET)
Here the SECRET is a long random phrase that is only known by the server. Even if one knows the building blocks of the link, one cannot fake it unless one knows the secret phrase.

Before even showing the user a registration form, the MAC is to be checked by comparing the supplied MAC with one calculated from the other message parameters.

Once we know that we can trust the link parameters we know that the given email address exists and is accessible by the user. Expiring the link is simply a matter of checking whether the timestamp is not too far in the past (e.g. three hours).

2010-09-06

Software business would be so easy without customers and users

This has sometimes come to my mind when being in one of those moments of pressure to deliver the promised features on time. Or when hearing that the delivery is not what was ordered or doesn’t have all the features imagined or is perfect apart from being late.

Well, no-one says the software business is easy, and it definitely isn’t so. But why is creating working software in the real world so difficult? I think the main reason is because the software business is about creating and selling something which is much more intangible than what we as human beings are used to deal with. And we are pretty much just learning how this new field of industry really works.

The intangible nature of the software can be seen from how
  • things can change fast
  • big changes are possible
  • it’s not easy to describe what is actually needed
  • it’s not easy to say if something is good enough
  • there is always multiple workable ways to solve problems

The rules and the laws on how to build a good house and good software are different, we have a long history on successfully constructing the buildings, we know how to do it well, but we are just learning on what kind of new skills and ways of thinking are needed to create successful software projects.

Embracing the change


Using agile methods to create software comes from the learned lesson that it’s not useful to fight against the intangible nature of software and the process of developing it. We have to have tools that are able embrace the change rather than fight against it. And the whole iterative development process with practices like continuous integration, delivering often, test driven development and so on are aimed to make that possible.

So now we have a toolbox full of good ways to react fast when things go into unexpected directions as they always do and everything is fine, right?

Well, no. What hasn’t changed that much at all is the relationship between the customers and developers. While the methods of creating the software have changed to be more agile, the contracts have not changed that much. And in the end the contract is the bottom line of what is possible in the relationship between these two parties.

The tighter the contract, the less it allows room to react, and the more there is risk that the whole project is doomed in the end due to trying to pretend that what everyone is doing is something solid, pre-designable and predictable far in advance.

The trust allows more space to react


Constructing software for someone is about fusing their ideas and wishes into working software. And if it would only be that, it would be quite manageable, but unfortunately there is no escape from the hard facts of time and money, and usually in the form of not having enough of both.

Agility means the ability to change the direction fast, the steer of the boat of software creation. And changing direction means there has to be some openness that the current direction might not be the best direction. So being agile means inherently being able to withstand a little bit of unsureness, the lack of knowing. The more we can feel confident in moving to uncharted territory with the knowledge that if things go bad, we can react correctly, the faster we can travel safely. But that’s confidence on the tools and the people you are traveling with, it’s not the (contractually) enforced confidence in the plan that was created in the past and which might not be working very well anymore in the changed situation.

So, in order for the software process to be truly agile, there has to be trust between the parties. Trust that both are actually traveling together, not a totally known path, but mutually agreed direction. When this trust is not present, what remains are the rigid steel frames of contracts. And usually this state also contracts the space of creativity too.

The big questions


Now that we have some ideas on what is a good way to create software and what kind of relationship between a paying customer and supplier would be good base for the successful project, there are two big questions:
  • How do you sell an agile software project?
  • How do you create a good contract for an agile project?

I’m positive that there is plenty of room for improvements in that arena and it’s going to be interesting to see how the agile movement is going to be seen in the arena of selling and making contracts. I will try to cover some ideas on them in my next blog post.

2010-07-31

Query Extensions in Action

Querydsl is all about providing the best type-safe querying tool for Java. To be the best fit for most of the situations, Querydsl provides lots of customization possibilities.

Query types can be extended to fit custom querying needs, custom method templates can be added to generated types and static delegate methods can be used as well.

Until recently static delegate methods could only be used for entity types. Since version 1.8.3 they can be used for built-in types as well.

This feature was requested by Luis Fernando Planella Gonzalez from the Cyclos project and they immediately took it into use.

Here is the declaration of their delegate methods :

/**
 * Contains extensions for common query classes
 * @author luis
 */
public class QueryExtensions {

    /**
     * Adds a period filter
     */
    @QueryDelegate(Date.class)
    public static EBoolean period(PDate<Date> expr, IDatePeriod period) {
        BooleanBuilder bb = new BooleanBuilder();
        Date begin = period == null ? null : period.getBegin();
        if (begin != null) {
            bb.and(expr.goe(begin));
        }
        Date end = period == null ? null : period.getEnd();
        if (end != null) {
            bb.and(expr.loe(end));
        }
        return bb;
    }

    /**
     * Adds a date period filter on a timestamp expression
     */
    @QueryDelegate(Timestamp.class)
    public static EBoolean period(PDateTime<Timestamp> expr, IDatePeriod period) {
        BooleanBuilder bb = new BooleanBuilder();
        Date begin = period == null ? null : period.getBegin();
        if (begin != null) {
            bb.and(expr.goe(DateHelper.toTime(begin)));
        }
        Date end = period.getEnd();
        if (end != null) {
            bb.and(expr.lt(DateHelper.toTime(DateHelper.truncateNextDay(end))));
        }
        return bb;
    }

    /**
     * Adds a timestamp period filter on a timestamp expression
     */
    @QueryDelegate(Timestamp.class)
    public static EBoolean period(PDateTime<Timestamp> expr, ITimestampPeriod period) {
        BooleanBuilder bb = new BooleanBuilder();
        Timestamp begin = period == null ? null : period.getBegin();
        if (begin != null) {
            bb.and(expr.goe(begin));
        }
        Timestamp end = period.getEnd();
        if (end != null) {
            bb.and(expr.loe(end));
        }
        return bb;
    }
}

All of the shown delegate methods are factory methods for "date in period" style expressions. The types for date and period vary in the methods.

The semantics of QueryDelegate annotations are simple. The @QueryDelegate.value determines the real type for the expression type to be extended and the first argument of the delegate method is used for the expression type instance itself. The rest of the arguments are used for the method in the extended query type.

In this example extensions for the types java.sql.Date and java.sql.Timestamp are declared. The respective builtin types in Querydsl are PDate and PDateTime. Two classes are created to extend the signature for PDate and PDateTime for Date and Timestamp usage :

public class QDate extends PDate {

    private static final long serialVersionUID = 494982445;

    public QDate(PEntity entity) {
        super(entity.getType(),entity.getMetadata());
    }

    public QDate(PathMetadata metadata) {
        super(java.sql.Date.class, metadata);
    }

    public EBoolean period(IDatePeriod period) {
        return QueryExtensions.period(this, period);
    }

}

public class QTimestamp extends PDateTime {

    private static final long serialVersionUID = 789961463;

    public QTimestamp(PEntity entity) {
        super(entity.getType(),entity.getMetadata());
    }

    public QTimestamp(PathMetadata metadata) {
        super(java.sql.Timestamp.class, metadata);
    }

    public EBoolean period(IDatePeriod period) {
        return QueryExtensions.period(this, period);
    }

    public EBoolean period(ITimestampPeriod period) {
        return QueryExtensions.period(this, period);
    }

}

With these generated types all of the property paths in the entity query types of type java.sql.Date and java.sql.Timestamp will use QDate and QTimestamp.

Now the extensions are ready to use in your queries. In Cyclos 4 the presented extensions are used in many places, for example the CardServiceBean class :

private List<CardVO> doSearch(Class<CardVO> resultType, CardQuery params) {
        QCard c = QCard.card;
        DBQuery query = from(c);

        if (params.getCardNumber() != null) {
            query.where(c.cardNumber.eq(params.getCardNumber()));
        }

        if (params.getCardType() != null) {
            query.where(c.cardType().id.eq(params.getCardType().getId()));
        }

        if (params.getExpirationDate() != null) {
            query.where(c.expirationDate.period(params.getExpirationDate()));
        }

        if (params.getCreationDate() != null) {
            query.where(c.creationDate.period(params.getCreationDate()));
        }

        if (params.getActivationDate() != null) {
            query.where(c.activationDate.period(params.getActivationDate()));
        }

        if (params.getGroups() != null) {
            Set groupsIds = EntityVO.getIds(params.getGroups());
            query.where(c.owner().group().id.in(groupsIds));
        }

        if (params.getOwner() != null) {
            query.where(c.owner().id.eq(params.getOwner().getId()));
        }

        if (params.getStatus() != null && !params.getStatus().isEmpty()) {
            query.where(c.status.in(params.getStatus()));
        }

        return query.run(resultType, params, c);
    }

The properties creationDate and activationDate are of type java.sql.Timestamp and expirationDate is of type java.sql.Date.

Conclusion


When you find yourself using lots of static helper methods to create your Querydsl queries, then consider using annotated delegate methods instead. They will make your queries more compact and are also easier to write as code autocomplete can be used.

2010-07-22

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 processor com.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 the Downloads page.

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

<!-- APT based 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>
    
   <!-- compilation -->
   <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 build with 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<String> names = query.from(cat).list(cat.name);

Query multiple columns :

query = new HibernateSQLQuery(session, templates);
List<Object[]> rows = query.from(cat).list(cat.id, cat.name);

Query all columns :

List<Object[]> rows = query.from(cat).list(cat.all());

Query in SQL, but project as entity :

query = new HibernateSQLQuery(session, templates);
List<Cat> 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<CatDTO> 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!

2010-06-22

Typesafe queries for DataNucleus

The guys from DataNucleus are now working actively on getting typesafe queries working natively in the Access Platform and later as part of the JDO standard.

The following blog post shows the direction and gives a concrete query example :

QCustomer customer = QCustomer.customer;
Query<Customer> q = pm.newTypesafeQuery(customer);
Customer bob = q.filter(customer.firstName.eq("Bob"))
     .unique(true).execute();

What about queries which return customer.firstName results?

Using the type parameter in the query there are the options of encoding the source type or the projection in the query.

The example is a bit vague, but could be transformed into the following forms

Projection as argument

QCustomer customer = QCustomer.customer;
Query q = pm.newTypesafeQuery(customer);
Customer bob = q.filter(customer.firstName.eq("Bob"))
     .unique(true).execute(customer);

What about list typed results? Do we get this type-safe?

Unique flag implicit via projection method

QCustomer customer = QCustomer.customer;
Query q = pm.newTypesafeQuery(customer);
Customer bob = q.filter(customer.firstName.eq("Bob"))
     .unique(customer);

The last example is closest to the Querydsl projection usage, and is also the most compact.

These examples manage without a type parameter, since the projection type is given in the end.

2010-06-08

Querydsl usage in Cyclos

Until now Querydsl has been mostly used in smaller projects outside of Mysema. Cyclos is one of the larger adopters of Querydsl.

Cyclos is an open source system for complementary currencies and is used in several projects with methodologies like C3, LETS, time banks and Barter's. See their homepage for more information. From their SourceForge download statistics can be seen that there are a large number of downloads for a server application in this area.

Cyclos 4

The current stable version of Cyclos is 3.5. Beta versions of 3.6 with numerous improvements are already available. Cyclos 4 was started a year ago. Some new features needed major refactorings to the old code so a new branch with new technologies was started. Struts, Hibernate and Tomcat were replaced with GWT, EJB3 (using Hibernate), Glassfish and Querydsl.

The goal was to make everything configurable: from accounts, to custom fields and localization. Beside the usual application localization even proper data localization is being implemented. Configuration entities have, for example, the name in each active language.

Cyclos is being produced by two teams of programmers, one in Brazil and one in Uruguay, being part of the respective national social trade organisation: STRO Uruguay and Instrodi. Luis Fernando Planella Gonzalez from the Brazilian team is the person behind the Querydsl adoption and has been communicating actively with the Querydsl maintainers to make the adoption as smooth as possible.

Querydsl usage

The Querydsl usage in Cyclos is very advanced. The Querydsl HQL module is used in conjunction with the JPA 2 API as an replacement for the JPA 2 Criteria API. JPA 2 Criteria were found to be too verbose. See the following post for some JPA 2 Criteria vs Querydsl comparisons.

Instead of using the the default query classes for JPA 2 provided by Querydsl, custom query and subquery classes were created to customize Querydsl as good as possible to the demands of Cyclos 4 development. The customizations are Entity to DTO transformations in the query projection, convenience methods for paging and some Hibernate specific optimizations.

The sources of Cyclos 4 are accessible via Subversion from this location : https://svn.berlios.de/svnroot/repos/cyclos.

2010-05-13

Querydsl user experiences

I just found this blog post describing the experiences of one Querydsl HQL user : JPA 2 Criteria

The blog post mentions the following benefits of Querydsl in comparison to JPA 2 Criteria :
* conciseness and readability (also discussed here)
* extendability of Query classes (also discussed here)
* support for other backends (JDO, SQL, Lucene etc)

Here are some other Querydsl user comments

"...We have applied Querydsl to the articial playground project and to a realworld application, LeanCast. Querydsl has been easy to get running, and easy to write compact and readable queries in. Integration in an existing project has been as painless as it could be, and we have had almost no problems."

"...Where we had problems, they were resolved in manner of hours by the Querydsl developers, which both seem dedicated and got a company backing the development. The API is complete, making all queries we have tried to express possible. When we had a request for a simplifying addition to the API, it was willingly accepted. ..."

Stein Magnus Jodal

"...Querydsl rocks! It's the best thing since sliced bread for data access in Java."

Alex Besogonov

"...Using querydsl you keep the reference to your HibernateQuery object and you can apply as many joins, wheres etc as you can, in any order, so that you can get a totally mutable hql query without ugly string concatenation. "

Lukasz Wisniewski

We at Mysema hope that you like Querydsl as much as we do ;)

2010-04-24

Quality assurance at Mysema

We at Mysema follow high quality standards in our projects. This manifests itself both in our development process and the tools we use to develop and build our projects. We follow agile processes in our projects and Scrum is our process model of choice for most of our projects.

One of the main focuses of the Scrum process in continuous feedback. In our daily development practice this applies to our usage of Continuous integration, which provides immediate feedback on failing builds.

Our build toolset consists of the following applications

  • Hudson for Continuous integration
  • Sonar for Code quality metrics

Hudson polls our code versioning system for changes to the source code of our projects, and when changes are detected, the projects are built on the Hudson server. For failing builds notifications are sent to the committers for immediate reaction.

Sonar is used for nightly builds to provide detailed analysis results on test coverage, source code style and bugs inspected from the compiled bytecode. Sonar internally uses CheckStyle, FindBugs, PMD and Cobertura and provides the analysis results in a very readable way.

We have configured the following alerts in Sonar

  • Blocker violations : Error threshold 0
  • Coverage : Warning threshold 50%, Error threshold 25%
  • Critical violations : Error threshold 0
  • Package tangle index : Warning threshold 5%, Error threshold 10%
  • Rules compliance : Warning threshold 95%, Error threshold 90%

As can be seen from the configuration, we focus on consistent design (Package tangle index), no critical errors (Blocker and Critical violations) and general Rules compliance.

Test coverage is also important, but as we have lots of highly modular builds, the tests for a particular artifact are not always located in the source tree of the artifact itself, so the results of the Cobertura test coverage do not fully reflect the total test coverage.

Here are some examples from the Sonar pages of Querydsl, our Open Source framework for type-safe queries:

Dashboard view


The high code duplication level comes from delegation pattern duplications in the Querydsl expression model.

Modules


The combination of Hudson and Sonar brings us the continuous feedback on our coding we need. All of our active projects are continuously built in Hudson and nightly builds are available for Sonar.

Make sure you visit Nemo, the public live instance of Sonar, which displays the analysis results of prominent Java Open Source frameworks.

Now we are aiming to further improve our code reuse level. We extensively use Open Source frameworks and host also our own frameworks, but sometimes helpful code snippets are buried in our SCM tool Subversion, where it is not easily found.

OpenGrok seems like a good solution for maintaining an index of program sources. Stay tuned for further posts.

2010-04-13

Querydsl as an alternative to the JPA 2 Criteria API

Querydsl is our flagship Open Source project for type-safe queries in JDO, JPA, SQL and other querying languages.

Last weekend I posted a thread in our Mysema Source Forum related to the verbosity of JPA 2 Criteria queries.

The thread presents JPA 2 and Querydsl queries side by side. Just that, not more. Here is one query comparison example.

The use case of this example is to query for single men and women as an cartesian view. At first the query expressed via the JPA 2 Criteria API :

CriteriaQuery query = builder.createQuery();
Root<Person> men = query.from( Person.class );
Root<Person> women = query.from( Person.class );
Predicate menRestriction = builder.and(
    builder.equal( men.get( Person_.gender ), Gender.MALE ),
    builder.equal( men.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE ));
Predicate womenRestriction = builder.and(
    builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
    builder.equal( women.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE ));
query.where( builder.and( menRestriction, womenRestriction ) );

and now via Querydsl

JPAQuery query = new JPAQuery(em);
QPerson men = new QPerson("men");
QPerson women = new QPerson("women");
query.from(men, women).where(
      men.gender.eq(Gender.MALE),
      men.relationshipStatus.eq(RelationshipStatus.SINGLE),
      women.gender.eq(Gender.FEMALE),
      women.relationshipStatus.eq(RelationshipStatus.SINGLE));    

The guys from the JPA group decided that fluent expression construction is not an option so a CriteriaBuilder for criteria construction was needed. CriteriaBuilder contains methods for all possible operation constructs, not grouped in any way.

Compare this with Querydsl, which has a dynamic query model with a fluent API, which supports IDE autocomplete and a much faster and compact way to write queries.

Also the distinction of paths, joins and the metamodel doesn't really make query construction easy.

A root path for the Person type is constructed like this in JPA 2 :

Root<Person> men = query.from( Person.class );

In Querydsl you just create an instance of the metatype for Person :

QPerson men = new QPerson("men");

In most cases though you can use the default instance of the metatype like this :

QPerson men = QPerson.person;

And to refer to the gender property of the Person path :

men.get(Person_.gender)

whereas in Querydsl you just use a field of the metatype instance :

men.gender

The dynamic metatypes of Querydsl combine a type-safe representation of the query domain, path references and operation construction in a fluent and concise way.

In addition to JPAQL and HQL Querydsl supports JDOQL, SQL, RDF, Lucene, Hibernate Search and Java collections.

We at Mysema use Querydsl in all our own projects with multiple backends and are 100% happy with it. ;)

Welcome to our Blog

We are pleased to announce the launch of our Mysema blog. This is the space where you can read posts on our customer and open source projects as well as comments on external events. The main language of this blog will be English with occasional posts in Finnish or other languages. If you haven't already done, be sure to visit also our Homepage for more information on our company and Mysema Source for more information on our Open Source projects. Hope to see you around!