Intro

JpqlBuilder is a tool to dynamically build JPQL strings. JpqlBuilder provides:

• Type safety
• Fluent API
• No annotation processors

Warning:

• Exploits ByteBuddy for proxying the entity objects
• Bundles ByteBuddy with the library binaries making them 3.5 megabytes in size

Quick example:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class); // Company is an entity class

Select select = builder.select(c);
select.where(c.getName()).like("%TikTok%").orderBy(c.getName());

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where a.name like :a order by a.name
Params: {a=%TikTok%}

The object returned by the from() method is a proxy object created specifically for the framework purposes and must not be merged or persisted.

A simple query like the one above can be a one-liner:

JpqlQuery query = JpqlBuilder.selectBuilder().select(Company.class).where(c -> $(c.getName()).like("%TikTok%")).orderBy(Company::getName);

The $ method starts an expression. One-liner pattern can be used to build queries that have one entity in the select clause and have no joins.

Currently unsupported (coming soon)

• union
• Subqueries

Learn by example

Below is the model that is going to be used in all examples (getters, setters and annotations are omitted):

@Entity(name = "test_Company")
public class Company {
  private Long id;
  private Status status;
  private String name;
  private List<Department> departments;
  private Map<Long, Employee> heads;
}

@Entity(name = "test_Department")
public class Department {
  private Long id;
  private Status status;
  private String name;
  private Company company;
}

@Entity(name = "test_Employee")
public class Employee {
  private Long id;
  private Status status;
  private String name;
  private Department department;
  private Boolean headOfDepartment;
  private Date employmentDate;
}

@Entity(name = "test_HeadOfDepartment")
public class HeadOfDepartment extends Employee {
}

public enum Status {
  ACTIVE, DELETED;
}

Selecting multiple items

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c.getId(), upper(c.getName()), c.getStatus());

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a.id, upper(a.name), a.status from test_Company a
Params: {}

upper is one of the functions that can be used in queries. See below for a complete list of functions.

Using new in the select clause

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(_new(ReportRow.class, c.getId(), c.getName()));
select.where(c.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select new org.thepavel.jpqlbuilder.demo.ReportRow(a.id, a.name) from test_Company a where a.status <> :a
Params: {a=DELETED}

Where conditions

Nested expressions:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
select
   .where(c.getStatus()).isNot(Status.DELETED)
   .and(
       $(c.getName()).is("Google")
       .or(c.getName()).is("Apple")
   );

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where a.status <> :a and (a.name = :b or a.name = :c)
Params: {a=DELETED, b=Google, c=Apple}

Or like this:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
select
   .where(
       $(c.getName()).is("Google")
       .or(c.getName()).is("Apple")
   )
   .and(c.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where (a.name = :a or a.name = :b) and a.status <> :c
Params: {a=Google, b=Apple, c=DELETED}

The not operator:

import static org.thepavel.jpqlbuilder.operators.builders.OperatorBuilder.not;
import static org.thepavel.jpqlbuilder.operators.builders.OperatorBuilder.$;
...

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
select
   .where(c.getStatus()).isNot(Status.DELETED)
   .and(not(
       $(c.getName()).is("Google")
       .or(c.getName()).is("Apple")
   ));

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where a.status <> :a and (not (a.name = :b or a.name = :c))
Params: {a=DELETED, b=Google, c=Apple}

Operators

Operator	Example	JPQL string
is	$(c.getName()).is("TikTok")	a.name = :a
isNot	$(c.getName()).isNot("TikTok")	a.name <> :a
isNull	$(c.getName()).isNull()	a.name is null
isNotNull	$(c.getName()).isNotNull()	a.name is not null
isEmpty	$(c.getDepartments()).isEmpty()	a.departments is empty
isNotEmpty	$(c.getDepartments()).isNotEmpty()	a.departments is not empty
in	$(c.getName()).in("Apple", "Google")	a.name in :a
	$(c.getName()).in(namesCollection)	a.name in :a
notIn	$(c.getName()).notIn("Apple", "Google")	a.name not in :a
	$(c.getName()).notIn(namesCollection)	a.name not in :a
between	$(c.getId()).between(1, 10)	a.id between :a and :b
notBetween	$(c.getId()).notBetween(1, 10)	a.id not between :a and :b
greaterThan	$(c.getId()).greaterThan(1)	a.id > :a
greaterThanOrEqual	$(c.getId()).greaterThanOrEqual(1)	a.id >= :a
lessThan	$(c.getId()).lessThan(1)	a.id < :a
lessThanOrEqual	$(c.getId()).lessThanOrEqual(1)	a.id <= :a
like	$(c.getName()).like("%TikTok%")	a.name like :a
	$(c.getName()).like("High _%", "_")	a.name like :a escape :b
notLike	$(c.getName()).notLike("%TikTok%")	a.name not like :a
	$(c.getName()).notLike("High _%", "_")	a.name not like :a escape :b
not	not($(c.getName()).is("TikTok"))	(not (a.name = :a))
memberOf	$(d).memberOf(c.getDepartments())	:a member of a.departments
notMemberOf	$(d).notMemberOf(c.getDepartments())	:a not member of a.departments

Functions

String functions

Function	Example	JPQL string
concat	$(c.getName()).like(concat("%", name, "%"))	a.name like concat(:a, :b, :c)
length	$(length(c.getName())).is(10)	length(a.name) = :a
locate	$(locate("Mr", c.getName())).is(1)	locate(:a, a.name) = :b
	$(locate("Mr", c.getName(), 2)).is(1)	locate(:a, a.name, :b) = :c
lower	$(lower(c.getName())).like(lower("TikTok"))	lower(a.name) like lower(:a)
upper	$(upper(c.getName())).like(upper("TikTok"))	upper(a.name) like upper(:a)
regexp	$(regexp(c.getName(), "^Go*")).is(Boolean.TRUE)	a.name regexp :a = :b
substring	$(substring(c.getName(), 1, 2)).is("Mr")	substring(a.name, :a, :b) = :c
	$(substring(c.getName(), 4)).is("Smith")	substring(a.name, :a) = :b
trim	$(trim(c.getName())).like(trim("TikTok"))	trim(a.name) like trim(:a)
	$(trim(c.getName(), '-')).like(trim("TikTok"))	trim(:a from a.name) like trim(:b)
ltrim	$(ltrim(c.getName())).like(ltrim("TikTok"))	trim(leading from a.name) like trim(leading from :a)
	$(ltrim(c.getName(), '-')).like(ltrim("TikTok"))	trim(leading :a from a.name) like trim(leading from :b)
rtrim	$(rtrim(c.getName())).like(rtrim("TikTok"))	trim(trailing from a.name) like trim(trailing from :a)
	$(rtrim(c.getName(), '-')).like(rtrim("TikTok"))	trim(trailing :a from a.name) like trim(trailing from :b)

Arithmetic functions

Function	Example	JPQL string
add	$(add(length(c.getName()), 5)).is(10)	length(a.name) + :a = :b
sub	$(sub(length(c.getName()), 5)).is(10)	length(a.name) - :a = :b
multi	$(multi(length(c.getName()), 5)).is(10)	length(a.name) * :a = :b
div	$(div(length(c.getName()), 5)).is(10)	length(a.name) / :a = :b
abs	$(abs(sub(length(c.getName()), 5))).is(5)	abs(length(a.name) - :a) = :b
mod	$(mod(div(length(c.getName()), 10))).is(5)	mod(length(a.name) / :a) = :b
sqrt	$(length(c.getName())).is(sqrt(100))	length(a.name) = sqrt(:a)

Datetime functions

Function	Example	JPQL string
current_date	$(e.getStartDate()).is(currentDate())	a.startDate = current_date
current_time	$(e.getStartDate()).is(currentTime())	a.startDate = current_time
current_timestamp	$(e.getStartDate()).is(currentTimestamp())	a.startDate = current_timestamp
extract	$(extract(e.getStartDate(), Extract.Part.YEAR)).is(extract(currentDate(), Extract.Part.YEAR))	extract(YEAR from a.startDate) = extract(YEAR from current_date)

Aggregation functions

Function	Example	JPQL string
count	builder.select(count(e))	select count(a)
min	builder.select(min(e.getRank()))	select min(a.rank)
max	builder.select(max(e.getRank()))	select max(a.rank)
avg	builder.select(avg(e.getRank()))	select avg(a.rank)
sum	builder.select(sum(e.getRank()))	select sum(a.rank)

Assorted functions

Function	Example	JPQL string
cast	$(cast(e.getStatus(), Cast.Type.STRING)).is("active")	cast(a.status string) = :a
coalesce	$(length(coalesce(e.getName(), "dummy"))).greaterThan(4)	length(coalesce(a.name, :a)) > :b
nullif	$(nullif(lower(e.getName()), "google")).isNull()	nullif(lower(a.name), :a) is null
index	select.join(c.getDepartments()).on(d -> $(index(d)).between(1, 10))	join a.departments b on index(b) between :a and :b
key	select.join(c.getHeads()).on(h -> $(key(h)).between(1L, 10L))	join a.heads b on key(b) between :a and :b
value	select.join(c.getHeads()).on(h -> $(value(h).getStatus()).isNot(Status.DELETED))	join a.heads b on value(b).status <> :a
size	$(size(c.getDepartments())).greaterThan(5)	size(a.departments) > :a
type	$(type(c)).is(Company.class)	type(a) = test_Company
function	$(function("coalesce", c.getName(), "dummy")).is("x")	function('coalesce', a.name, :a) = :b
func	$(func("coalesce", c.getName(), "dummy")).is("x")	func('coalesce', a.name, :a) = :b
sql	$(sql("cast(? as char)", c.getStatus())).is("active")	sql('cast(? as char)', a.status) = :a
column	$(column("rowid", c)).lessThan(100)	column('rowid', a) < :a

Case expression

There are two types of the expression. The first one is:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

JpqlFunction<Integer> code = _case(lower(c.getName()))
   .when("google").then(1)
   .when("apple").then(2)
   .orElse(0);

Select select = builder.select(c);
select.where(code).is(0);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where case lower(a.name) when :a then :b when :c then :d else :e end = :f
Params: {a=google, b=1, c=apple, d=2, e=0, f=0}

Another way to build the case expression is:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

JpqlFunction<Integer> code = _case()
   .when(c.getName()).is("Google").then(1)
   .when(c.getName()).is("Apple").then(2)
   .orElse(0);

Select select = builder.select(c);
select.where(code).is(0);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a where case when a.name = :a then :b when a.name = :c then :d else :e end = :f
Params: {a=Google, b=1, c=Apple, d=2, e=0, f=0}

Order by

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
select
   .orderBy(c.getStatus())
   .orderBy(c.getName()).desc()
   .orderBy(c.getId()).asc();

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a order by a.status, a.name desc, a.id asc
Params: {}

Nulls first / nulls last:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
select
   .orderBy(c.getStatus()).nullsLast()
   .orderBy(c.getName()).desc().nullsFirst();

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a order by a.status nulls last, a.name desc nulls first
Params: {}

Joins

Joining a collection relationship:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);
Department d = builder.join(c.getDepartments()).getPathSpecifier();

Select select = builder.select(c);
select
   .where(c.getStatus()).isNot(Status.DELETED)
   .and(d.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a join a.departments b where a.status <> :a and b.status <> :b
Params: {a=DELETED, b=DELETED}

Joining on a condition:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);
builder.join(c.getDepartments()).on(d -> $(d.getStatus()).isNot(Status.DELETED));

Select select = builder.select(c);
select.where(c.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a join a.departments b on b.status <> :a where a.status <> :b
Params: {a=DELETED, b=DELETED}

Joining a many-to-one/one-to-one relationship:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Department d = builder.from(Department.class);
Company c = builder.join(d.getCompany()).getPathSpecifier();

Select select = builder.select(d);
select
   .where(c.getName()).is("TikTok")
   .orderBy(d.getName()).desc();

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Department a join a.company b where b.name = :a order by a.name desc
Params: {a=TikTok}

Joining an entity class:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Department d = builder.from(Department.class);
Company c = builder.join(Company.class).on(e -> $(d.getCompany()).is(e)).getPathSpecifier();

Select select = builder.select(d);
select
   .where(c.getName()).is("TikTok")
   .orderBy(d.getName()).desc();

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Department a join test_Company b on a.company = b where b.name = :a order by a.name desc
Params: {a=TikTok}

Left join:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);
Department d = builder
   .leftJoin(c.getDepartments())
   .on(e -> $(e.getStatus()).isNot(Status.DELETED))
   .getPathSpecifier();

Select select = builder.select(c);
select
   .where(c.getStatus()).isNot(Status.DELETED)
   .and(d.getName()).like("%IT%");

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a left join a.departments b on b.status <> :a where a.status <> :b and b.name like :c
Params: {a=DELETED, b=DELETED, c=%IT%}

Join fetch:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);
builder.joinFetch(c.getDepartments());

Select select = builder.select(c);
select.where(c.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a join fetch a.departments where a.status <> :a
Params: {a=DELETED}

Join fetch with alias:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Department d = builder.from(Department.class);
Company c = builder.joinFetchWithAlias(d.getCompany()).getPathSpecifier();

Select select = builder.select(d);
select.orderBy(c.getName()).orderBy(d.getName());

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Department a join fetch a.company b order by b.name, a.name
Params: {}

Casting a joined entity (using the treat function):

SelectBuilder builder = JpqlBuilder.selectBuilder();
Department d = builder.from(Department.class);

HeadOfDepartment h = builder
   .join(d.getEmployees())
   .as(HeadOfDepartment.class)
   .on(x -> $(x.isHeadOfDepartment()).is(Boolean.TRUE))
   .getPathSpecifier();

Select select = builder.select(d);
select.where(h.getStatus()).isNot(Status.DELETED);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Department a join treat(a.employees as test_HeadOfDepartment) b on b.headOfDepartment = :a where b.status <> :b
Params: {a=true, b=DELETED}

Grouping

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);
Department d = builder.join(c.getDepartments()).getPathSpecifier();

Select select = builder.select(c.getName(), count(d));
select.groupBy(c.getName()).having(count(d)).greaterThan(1);

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a.name, count(b) from test_Company a join a.departments b group by a.name having count(b) > :a
Params: {a=1}

Building a query dynamically

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

Select select = builder.select(c);
Where where = null;

for (String name : Arrays.asList("Google", "Apple")) {
  if (where == null) {
    where = select.where(c.getName()).is(name);
  } else {
    where.or(c.getName()).is(name);
  }
}

builder.join(c.getDepartments());

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a join a.departments b where a.name = :a or a.name = :b
Params: {a=Google, b=Apple}

One more example:

SelectBuilder builder = JpqlBuilder.selectBuilder();
Company c = builder.from(Company.class);

ExpressionChain condition = null;

for (String name : Arrays.asList("Google", "Apple")) {
  if (condition == null) {
    condition = $(c.getName()).is(name);
  } else {
    condition.or(c.getName()).is(name);
  }
}

Select select = builder.select(c);
select.where(condition).and(c.getStatus()).isNot(Status.DELETED);

builder.join(c.getDepartments()).on(d -> $(d.getStatus()).isNot(Status.DELETED));

System.out.println("Query: " + select.getQueryString());
System.out.println("Params: " + select.getParameters());

Output:

Query: select a from test_Company a join a.departments b on b.status <> :a where (a.name = :b or a.name = :c) and a.status <> :d
Params: {a=DELETED, b=Google, c=Apple, d=DELETED}

Delete Queries

DeleteBuilder builder = JpqlBuilder.deleteBuilder();
Company c = builder.from(Company.class);

JpqlQuery query = builder.delete(c).where(lower(c.getName())).like("%abc%");

System.out.println("Query: " + query.getQueryString());
System.out.println("Params: " + query.getParameters());

Output:

Query: delete from test_Company a where lower(a.name) like :a
Params: {a=%abc%}

One-liner example:

JpqlQuery query = JpqlBuilder.deleteBuilder().delete(Company.class).where(c -> $(c.getDepartments()).isEmpty());

System.out.println("Query: " + query.getQueryString());
System.out.println("Params: " + query.getParameters());

Output:

Query: delete from test_Company a where a.departments is empty
Params: {}

Update Queries

UpdateBuilder builder = JpqlBuilder.updateBuilder();
Company c = builder.entity(Company.class);

JpqlQuery query = builder
    .update(c)
    .set(c.getStatus()).to(Status.DELETED)
    .set(c.getName()).to(concat("DELETED-", c.getName()))
    .where(c.getStatus()).is(Status.DISABLED);

System.out.println("Query: " + query.getQueryString());
System.out.println("Params: " + query.getParameters());

Output:

Query: update test_Company a set a.status = :a, a.name = concat(:b, a.name) where a.status = :c
Params: {a=DELETED, b=DELETED-, c=DISABLED}

One-liner example:

JpqlQuery query = JpqlBuilder
    .updateBuilder()
    .update(Company.class)
    .set(c -> set(c.getStatus()).to(Status.DELETED).set(c.getName()).to("DELETED"))
    .where(c -> $(c.getStatus()).is(Status.DISABLED));

System.out.println("Query: " + query.getQueryString());
System.out.println("Params: " + query.getParameters());

Output:

Query: update test_Company a set a.status = :a, a.name = :b where a.status = :c
Params: {a=DELETED, b=DELETED, c=DISABLED}

Compatibility

Tested on Java 8 and 11. In order to run on Java 11 add the following argument to the JVM:

--add-opens java.base/jdk.internal.reflect=ALL-UNNAMED