Master Dynamic SQL in Spring Boot 3 with jpa-search-helper: Real‑World Examples

Introduction

Dynamic SQL allows applications to build and execute SQL statements at runtime based on user input, business rules, or data state, improving flexibility and reducing redundant code for complex query scenarios.

JPA Implementation

Using JpaSpecificationExecutor together with Specification and Java Predicate logic, you can construct dynamic queries as shown below:

public Page<Person> dynamicQuery(PersonDTO dto) {
    Specification<Person> spec = (root, query, builder) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (StringUtils.isNotEmpty(dto.getPhone())) {
            predicates.add(builder.equal(root.get("phone"), dto.getPhone()));
        }
        if (StringUtils.isNotEmpty(dto.getName())) {
            predicates.add(builder.like(root.get("name"), "%" + dto.getName() + "%"));
        }
        return predicates.isEmpty() ? null :
               predicates.size() == 1 ? predicates.get(0) :
               builder.and(predicates.toArray(new Predicate[0]));
    };
    return personRepository.findAll(spec, dto.getPageable());
}

MyBatis Implementation

With MyBatis you manually compose XML fragments. A simple example:

<select id="queryPerson" resultType="Person">
  SELECT * FROM x_person
  <where>
    <if test="name != null">AND name = #{name}</if>
    <if test="phone != null">AND email = #{phone}</if>
    <!-- more conditions -->
  </where>
</select>

MyBatis offers flexibility but can lead to bulky XML and maintenance challenges for complex queries.

Introducing jpa-search-helper

The jpa-search-helper library provides a concise way to compose advanced dynamic queries.

Query Modes :

Mode 1 – Map<String,String> for GET‑style query parameters.

Mode 2 – JSON JPASearchInput for POST‑style bodies, supporting nested logic and a full range of operators.

Projection : Select specific fields from results (available since 3.2.0).

Dependency

<dependency>
  <groupId>app.tozzi</groupId>
  <artifactId>jpa-search-helper</artifactId>
  <version>3.5.3</version>
</dependency>

Repository: https://repo.maven.apache.org/maven2

Entity Example

@Entity
public class Person {
    @Id
    private Long id;
    @Searchable
    private String name;
    @Searchable
    private Integer age;
    @Searchable
    private String phone;
    @Searchable
    private String email;
    @Searchable
    private Date birth;
    @NestedSearchable
    @OneToOne
    @JoinColumn(name = "company_id")
    private Company company;
}

@Entity
public class Company {
    @Id
    private Long id;
    @Searchable(entityFieldKey = "company.name")
    private String name;
    @Searchable(entityFieldKey = "company.address")
    private String address;
}

Annotate searchable fields with @Searchable and nested fields with @NestedSearchable.

Repository Interface

public interface PersonRepository extends JpaRepository<Person, Long>, JPASearchRepository<Person> {}

Mode 1 Query Examples

Simple Query

public List<Person> simpleQuery() {
    Map<String, String> filters = new HashMap<>();
    filters.put("name_contains", "十");
    filters.put("age_gte", String.valueOf(10));
    return personRepository.findAll(filters, Person.class);
}

Related Entity Query

public List<Person> simpleRelatedQuery() {
    Map<String, String> filters = new HashMap<>();
    filters.put("name_contains", "十");
    filters.put("age_gte", String.valueOf(10));
    filters.put("company.name_eq", "网易公司");
    return personRepository.findAll(filters, Person.class);
}

Paginated & Sorted Query

public Page<Person> simpleQuerySortAndPage() {
    Map<String, String> filters = new HashMap<>();
    filters.put("name_contains", "十");
    filters.put("age_gte", String.valueOf(10));
    filters.put("age_sort", "ASC");
    filters.put("_limit", "2");
    filters.put("_offset", "0");
    return personRepository.findAllWithPaginationAndSorting(filters, Person.class);
}

Mode 2 Query Examples

Using JSON JPASearchInput you can express complex logical trees. Example of a simple query:

public List<Person> complexQuery(JPASearchInput input) {
    return personRepository.findAll(input, Person.class);
}

Example of a combined query with nested or and and operators (excerpt):

{
  "filter": {
    "operator": "and",
    "filters": [
      {"operator": "gte", "key": "age", "value": "10"},
      {"operator": "and", "filters": [
        {"operator": "or", "filters": [
          {"operator": "contains", "key": "company.name", "value": "网易公司"},
          {"operator": "eq", "key": "company.address", "value": "广州市天河区珠江新城东塔"}
        ]}
      ]}
    ]
  },
  "options": {"pageSize": 1, "pageOffset": 0, "sortOptions": [{"key": "age", "desc": false}]}
}

Pagination and sorting are handled via the options section.

Result Visualization

Generated SQL statements and query results are shown in the original article as screenshots; representative images are retained below.