Wiki
Clone wikiquery-builder / Home
About
Compact tool for building dynamic SQL, JPA or hibernate queries without transitive dependencies.
Usage
Add it as maven dependency:
#!xml <dependency> <groupId>org.bitbucket.brunneng.qb</groupId> <artifactId>query-builder</artifactId> <version>1.0.3</version> </dependency>
The main package is org.bitbucket.brunneng.qb.
Usage is similar to StringBuilder, except you can append parts of queries in case if some conditions are satisfied:
#!java JpaQueryBuilder qb = new JpaQueryBuilder(entityManager); qb.append("select p from Person p"); qb.appendJoin("join p.address a"); // appends not immediately, but only if it's alias "a" will be used somewhere in resulting query qb.append("where"); // if there are will be no conditions applied, then this last "while" will be removed to not break query syntax. qb.appendLike("and p.name like :v", partOfName); // dynamic condition: appends only if partOfName is not null or empty. // Note, that in the first condition after the "while" (this or subsequent) "and" operator will be automatically removed to not break query syntax. qb.append("and a.zipCode = :v", zipCode); // dynamic condition: appends only if zipCode is not null. This also triggers appedning join. qb.append("and p.age > :v", age); // dynamic condition: appends only if age is not null. List<Person> persons = qb.build(Person.class).getResultList();
#!sql select p from Person p where p.name like :pName and p.age > :pAge
For example, parameters to this query are: pName = '%Joe%', pAge = 30. Notice, they are renamed from special shortcut name :v to have better readability of result query, in case if you need to debug something. Also, you may notice, that additional space is added between calls to append, so you will not need to think about it: to put a space after the condition, or before 'and' etc.
Let's assume zipCode = "49050", and other parameters are null. Then resulting query will be:
#!sql select p from Person p join p.address a where a.zipCode = :aZipCode
And finally, let's assume all parameters are "null". Then resulting query will be:
#!sql select p from Person p
Usually, for every new query, the new query builder should be created.
As you can see, the query builder allows building truly dynamic queries without losing readability. So you don't need to write complex "if/else" logic to build a query.
Dependencies
This utility has no transitive dependencies, which allows you to use only those builder, which you need, without cluttering your classpath with needless dependencies.
Builders
QueryBuilder - The most general query builder.
SqlQueryBuilder extends QueryBuilder - A query builder which has useful methods to build SQL queries.
AbstractJpqlQueryBuilder extends SqlQueryBuilder - Abstract query builder for JPQL queries.
JpaQueryBuilder extends AbstractJpqlQueryBuilder - JPQL Query builder which uses JPA EntityManager to build queries. Needs dependency on JPA.
HibernateQueryBuilder extends AbstractJpqlQueryBuilder - HQL Query builder which uses hibernate Session to build queries. Needs dependency on hibernate-core.
Method appendLike is already mentioned above, but there are a lot of other useful methods in corresponding builders.
Spring
Supports loading paged and sorted data using spring Pageable:
#!java Page<Person> personsPage = SpringQueryBuilderUtils.loadPage(qb, pageable, "id");
Updated