### Query Examples

Source: https://queritylib.github.io/spring-mvc/query-language.html

Common query patterns for filtering, sorting, and pagination.

```text
lastName="Skywalker"
lastName!="Skywalker"
lastName starts with "Sky"
lastName ends with "walker"
lastName contains "wal"
and(firstName="Luke", lastName="Skywalker")
age>30
age<30
height>=1.80
height<=1.80
and(lastName="Skywalker", age>30)
and(or(firstName="Luke", firstName="Anakin"), lastName="Skywalker") sort by age desc
and(not(firstName="Luke"), lastName="Skywalker")
lastName="Skywalker" page 2,10
lastName is null
lastName is not null
lastName in ("Skywalker", "Solo")
lastName not in ("Skywalker", "Solo")
deleted=false
address.city="Rome"
distinct and(orders.totalPrice>1000,currency="EUR")
sort by lastName asc, age desc page 1,10
```

--------------------------------

### Install Querity dependency

Source: https://queritylib.github.io/installing.html

Add the required dependency to your project build file.

```xml
<dependency>
  <groupId>io.github.queritylib</groupId>
  <artifactId>querity-spring-data-jpa</artifactId>
  <version>4.0.0</version>
</dependency>
```

```gradle
implementation "io.github.queritylib:querity-spring-data-jpa:4.0.0"
```

--------------------------------

### Textual Query Language Example

Source: https://queritylib.github.io/available-modules.html

This module enables parsing of Querity objects from a simple textual query language. It provides an alternative to the JSON approach offered by `querity-spring-web`.

```shell
curl 'http://localhost:8080/people?q=and(lastName="Skywalker",firstName="Luke")'
```

--------------------------------

### Advanced Queries with SELECT, GROUP BY, and HAVING

Source: https://queritylib.github.io/spring-mvc/query-language.html

Examples for advanced queries involving projections and grouping, typically parsed via QuerityParser.parseAdvancedQuery().

```text
select firstName, lastName, address.city
select id, UPPER(lastName) as upperName
select category, COUNT(id) as itemCount group by category
select category, SUM(amount) as total group by category having SUM(amount)>1000
select region, category, AVG(price) as avgPrice group by region, category sort by avgPrice desc
```

--------------------------------

### Nested Functions

Source: https://queritylib.github.io/features/functions.html

Illustrates nesting Querity functions to apply multiple operations sequentially. For example, applying `trim()` before `upper()` to a property.

```java
// Nested functions
upper(trim(prop("name")))  // UPPER(TRIM(name))
length(lower(prop("email")))  // LENGTH(LOWER(email))
```

--------------------------------

### Spring Web MVC JSON Query Example

Source: https://queritylib.github.io/available-modules.html

Use this module to pass a JSON Query or Condition object as a request parameter in your Spring @RestController for automatic deserialization into a Querity object.

```shell
curl 'http://localhost:8080/people?q={"filter":{"and":[{"propertyName":"lastName","operator":"EQUALS","value":"Skywalker"},{"propertyName":"lastName","operator":"EQUALS","value":"Luke"}]}}'
```

--------------------------------

### Building and Executing Queries with Querity

Source: https://queritylib.github.io/features.html

This section details how to initialize a query, run it to retrieve all matching entities, or count entities based on a condition.

```APIDOC
## Building and Executing Queries

### Description
This section covers the basic usage of the Querity library for building and executing queries against a database.

### Static Methods

#### Build Empty Query
Use the static method `Querity.query().build()` to create an empty query object. This can then be further customized using various query building methods.

```java
// Example of building an empty query
Querity.query().build();
```

### Instance Methods (using Querity Service)

To use the instance methods, you first need to `@Autowire` the `Querity` service. This service allows you to interact with the configured database.

#### Find All Entities
Retrieves all entities that match the given query.

- **Method**: `Querity.findAll(entityClass, query)`
- **Parameters**:
  - `entityClass` (*Class<?>*) - The class of the entity to query.
  - `query` (*Query*) - The built query object.

```java
// Example of finding all entities
List<MyEntity> results = querityService.findAll(MyEntity.class, query);
```

#### Count Entities
Counts the number of entities that satisfy the specified condition.

- **Method**: `Querity.count(entityClass, condition)`
- **Parameters**:
  - `entityClass` (*Class<?>*) - The class of the entity to count.
  - `condition` (*Condition*) - The condition to filter the entities by.

```java
// Example of counting entities
long count = querityService.count(MyEntity.class, condition);
```

### Query Customization

The Querity library supports various features for customizing queries, including:

- Filters
- Sorting
- Pagination
- Distinct results
- Projections (Select)
- Field-to-Field Comparison
- Function Expressions
- GROUP BY and HAVING
- Native Sort Expressions
- Query Customizers (JPA)
- Modify an existing Query
```

--------------------------------

### Simple Entity Query vs. Projection Query

Source: https://queritylib.github.io/features/projections.html

Demonstrates the difference between a simple entity query using `Query` and `findAll()`, and a projection query using `AdvancedQuery` and `findAllProjected()`.

```java
// Simple entity query
Query query = Querity.query()
    .filter(filterBy("lastName", EQUALS, "Skywalker"))
    .build();
List<Person> entities = querity.findAll(Person.class, query);
```

```java
// Projection query
AdvancedQuery advQuery = Querity.advancedQuery()
    .select(selectBy("firstName", "lastName"))
    .filter(filterBy("lastName", EQUALS, "Skywalker"))
    .build();
List<Map<String, Object>> projections = querity.findAllProjected(Person.class, advQuery);
```

--------------------------------

### Implement a Spring REST Controller with Querity

Source: https://queritylib.github.io/spring-mvc.html

Use Query as a controller parameter to handle incoming requests. Ensure the querity-spring-web module is imported for automatic configuration.

```java
import io.github.queritylib.querity.api.Query;

@RestController
public class MyRestController {

  @Autowired
  MyService service;

  @GetMapping(value = "/people", produces = MediaType.APPLICATION_JSON_VALUE)
  Result<Person> getPeople(@RequestParam(required = false) Query q) {
    return service.getPeople(q);
  }
}
```

--------------------------------

### Build an empty query

Source: https://queritylib.github.io/features.html

Use the static build method to initialize an empty query object.

```java
Querity.query().build()
```

--------------------------------

### Building a Query with Simple Filters

Source: https://queritylib.github.io/features/filters.html

Demonstrates how to build a basic query with a single filter condition using `filterBy`.

```APIDOC
## Building a Query with Simple Filters

### Description
Use `Querity.query().filter(condition).build()` to construct a query with filters. This example shows a simple condition.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```java
Query query = Querity.query()
    .filter(filterBy("lastName", EQUALS, "Skywalker"))
    .build();
```

### Response
#### Success Response (200)
N/A (Java API)

#### Response Example
N/A
```

--------------------------------

### Function Arguments

Source: https://queritylib.github.io/features/functions.html

Explains the types of arguments accepted by Querity functions, including property references and literal values.

```APIDOC
## Function arguments 

Function arguments must be either property references or literals:
  * `prop("fieldName")` - reference to an entity property (alias for `property()`)
  * `lit(value)` - literal value (String, Number, or Boolean)

```java
// Combine functions
coalesce(prop("nickname"), lit("Anonymous"))
mod(prop("quantity"), lit(10))
concat(prop("firstName"), lit(" - "), prop("lastName"))

// Nested functions
upper(trim(prop("name")))  // UPPER(TRIM(name))
length(lower(prop("email")))  // LENGTH(LOWER(email))

// Nested properties (e.g., address.city)
upper(prop("address.city"))
coalesce(prop("contact.email"), prop("contact.phone"), lit("N/A"))
```
```

--------------------------------

### Spring Service for Data Querying

Source: https://queritylib.github.io/quick-start.html

Use this service to fetch and count data using Querity. It supports filtering, sorting, and pagination. The `record` keyword requires Java 14+.

```java
import static io.github.queritylib.querity.api.Querity.*;
import static io.github.queritylib.querity.api.Operator.*;
import static io.github.queritylib.querity.api.Sort.Direction.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class MyService {

  @Autowired
  Querity querity;

  public Result<Person> getPeople() {
    Query query = Querity.query()
        .filter(
            not(and(
                filterBy("lastName", EQUALS, "Skywalker"),
                filterBy("firstName", EQUALS, "Luke")
            ))
        )
        .sort(sortBy("lastName"), sortBy("birthDate", DESC))
        .pagination(1, 10)
        .build();
    List<Person> items = querity.findAll(Person.class, query);
    Long totalCount = querity.count(Person.class, query.getFilter());
    return new Result<>(items, totalCount);
  }

  record Result<T>(List<T> items, Long totalCount) {
  }
}
```

--------------------------------

### Building Queries with OR Conditions

Source: https://queritylib.github.io/features/filters.html

Demonstrates how to combine multiple filter conditions using a logical OR.

```APIDOC
## Building Queries with OR Conditions

### Description
Use `Querity.or` to group multiple conditions where at least one must be true.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```java
Query query = Querity.query()
    .filter(
        or(
            filterBy("lastName", EQUALS, "Skywalker"),
            filterBy("lastName", EQUALS, "Kenobi")
        )
    ).build();
```

### Response
#### Success Response (200)
N/A (Java API)

#### Response Example
N/A
```

--------------------------------

### Building Queries with AND Conditions

Source: https://queritylib.github.io/features/filters.html

Shows how to combine multiple filter conditions using a logical AND.

```APIDOC
## Building Queries with AND Conditions

### Description
Use `Querity.and` to group multiple conditions that must all be true.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```java
Query query = Querity.query()
    .filter(
        and(
            filterBy("firstName", EQUALS, "Luke"),
            filterBy("lastName", EQUALS, "Skywalker")
        )
    ).build();
```

### Response
#### Success Response (200)
N/A (Java API)

#### Response Example
N/A
```

--------------------------------

### Build Query with AND Conditions

Source: https://queritylib.github.io/features/filters.html

Combine multiple conditions using Querity.and() to ensure all specified criteria are met. Supports nesting for complex logic.

```java
Query query = Querity.query()
    .filter(
        and(
            filterBy("firstName", EQUALS, "Luke"),
            filterBy("lastName", EQUALS, "Skywalker")
        )
    ).build();
```

--------------------------------

### Build a sorted query with Querity

Source: https://queritylib.github.io/features/sorting.html

Constructs a query with multiple sorting criteria using the fluent API.

```java
Query query = Querity.query()
    .sort(sortBy("lastName"), sortBy("birthDate", DESC))
    .build();
```

--------------------------------

### Using Functions in Sorting

Source: https://queritylib.github.io/features/functions.html

Illustrates how to use functions like `length()` and `upper()` for custom sorting criteria.

```APIDOC
## Using functions in sorting 

```java
// Sort by string length
Query query = Querity.query()
    .sort(sortBy(length(prop("lastName")), DESC))
    .build();

// Sort by uppercase value
Query query = Querity.query()
    .sort(sortBy(upper(prop("firstName"))))
    .build();
```
```

--------------------------------

### Using Functions in Filters

Source: https://queritylib.github.io/features/functions.html

Demonstrates how to apply functions like `upper()` and `length()` within filter conditions.

```APIDOC
## Using functions in filters 

```java
import static io.github.queritylib.querity.api.Querity.*;

// Filter by uppercase lastName
Query query = Querity.query()
    .filter(filterBy(upper(prop("lastName")), EQUALS, "SKYWALKER"))
    .build();

// Filter by string length
Query query = Querity.query()
    .filter(filterBy(length(prop("firstName")), GREATER_THAN, 5))
    .build();
```
```

--------------------------------

### Build Query with OR Conditions

Source: https://queritylib.github.io/features/filters.html

Combine multiple conditions using Querity.or() to allow any of the specified criteria to be met. Supports nesting for complex logic.

```java
Query query = Querity.query()
    .filter(
        or(
            filterBy("lastName", EQUALS, "Skywalker"),
            filterBy("lastName", EQUALS, "Kenobi")
        )
    ).build();
```

--------------------------------

### Combine Functions

Source: https://queritylib.github.io/features/functions.html

Demonstrates combining Querity functions with property references and literal values. Functions like `coalesce()`, `mod()`, and `concat()` can be used.

```java
// Combine functions
coalesce(prop("nickname"), lit("Anonymous"))
mod(prop("quantity"), lit(10))
concat(prop("firstName"), lit(" - "), prop("lastName"))
```

--------------------------------

### Using Functions in Projections

Source: https://queritylib.github.io/features/functions.html

Shows how to use functions for transforming and aliasing selected properties in projections.

```APIDOC
## Using functions in projections 

```java
// Select with function expressions
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        prop("id"),
        upper(prop("lastName")).as("upperLastName"),
        concat(prop("firstName"), lit(" "), prop("lastName")).as("fullName"),
        length(prop("email")).as("emailLength")
    ))
    .build();
```
```

--------------------------------

### Backend Support for Functions

Source: https://queritylib.github.io/features/functions.html

Details the support level for Querity functions across different backend implementations (JPA, MongoDB, Elasticsearch).

```APIDOC
## Backend support for functions 

>   * **JPA** : Full support for all functions in filters, sorting, and projections
>   * **MongoDB** : Functions supported in filters only (via `$expr`). Using functions in sort or select throws `UnsupportedOperationException`
>   * **Elasticsearch** : Functions are **not supported**. Using functions throws `UnsupportedOperationException`
> 

**Function support by implementation:**
Function | JPA | MongoDB | Elasticsearch  
---|---|---|---  
`abs()`, `sqrt()`, `mod()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
`concat()`, `substring()`, `trim()`, `ltrim()`, `rtrim()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
`lower()`, `upper()`, `length()`, `locate()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
`currentDate()`, `currentTime()`, `currentTimestamp()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
`coalesce()`, `nullif()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
`count()`, `sum()`, `avg()`, `min()`, `max()` | ✓ Filters, Sort, Select | ✓ Filters only | ✗  
* * *
```

--------------------------------

### Build Paginated Query

Source: https://queritylib.github.io/features/pagination.html

Use `pagination(page, pageSize)` to specify the page number and the number of items per page. This method is part of the query builder. Requires importing `Querity`.

```java
Query query = Querity.query()
    .pagination(1, 5)
    .build();

```

--------------------------------

### Basic GROUP BY Query

Source: https://queritylib.github.io/features/group-by.html

Group query results by a single property and select aggregated columns like count and sum. Ensure necessary imports are included.

```java
import static io.github.queritylib.querity.api.Querity.*;

// Group by single property
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        prop("category"),
        count(prop("id")).as("itemCount"),
        sum(prop("amount")).as("totalAmount")
    ))
    .groupBy("category")
    .build();
List<Map<String, Object>> results = querity.findAllProjected(Order.class, query);
// Returns: [{category: "Electronics", itemCount: 42, totalAmount: 15000}, ...]
```

--------------------------------

### Build Query with Simple Filter

Source: https://queritylib.github.io/features/filters.html

Use Querity.query().filter(filterBy(...)).build() to create a query with a single simple condition. Supports nested properties using dot notation.

```java
Query query = Querity.query()
    .filter(filterBy("lastName", EQUALS, "Skywalker"))
    .build();
```

--------------------------------

### Nested Properties

Source: https://queritylib.github.io/features/functions.html

Shows how to access nested properties using dot notation within function arguments, such as `prop("address.city")`. Also demonstrates chaining `coalesce()` with multiple property references and a literal.

```java
// Nested properties (e.g., address.city)
upper(prop("address.city"))
coalesce(prop("contact.email"), prop("contact.phone"), lit("N/A"))
```

--------------------------------

### Build Query with NOT Condition (Simple)

Source: https://queritylib.github.io/features/filters.html

Negate a single condition using Querity.not() to exclude results matching the specified criteria.

```java
Query query = Querity.query()
    .filter(not(filterBy("lastName", EQUALS, "Skywalker")))
    .build();
```

--------------------------------

### Group by Multiple Properties

Source: https://queritylib.github.io/features/group-by.html

Group query results by multiple properties simultaneously and calculate aggregate functions like average.

```java
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        prop("category"),
        prop("region"),
        avg(prop("price")).as("avgPrice")
    ))
    .groupBy("category", "region")
    .build();
```

--------------------------------

### Supported Filter Operators

Source: https://queritylib.github.io/features/filters.html

Lists the available operators for building filter conditions in Querity.

```APIDOC
## Supported Filter Operators

### Description
These operators can be used with `filterBy` to define conditions.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
N/A

### Response
#### Success Response (200)
List of supported operators:
- EQUALS
- NOT_EQUALS
- STARTS_WITH (case-insensitive where supported*)
- ENDS_WITH (case-insensitive where supported*)
- CONTAINS (case-insensitive where supported*)
- GREATER_THAN
- GREATER_THAN_EQUALS
- LESSER_THAN
- LESSER_THAN_EQUALS
- IS_NULL
- IS_NOT_NULL
- IN
- NOT_IN

* Operators STARTS_WITH, ENDS_WITH, CONTAINS are case-insensitive only if the underlying database supports case-insensitive matching and proper configurations are applied.

#### Response Example
N/A
```

--------------------------------

### Using Native Conditions

Source: https://queritylib.github.io/features/filters.html

Details how to incorporate database-specific conditions using `filterByNative`.

```APIDOC
## Using Native Conditions

### Description
Use `Querity.filterByNative` to embed database-specific query conditions when Querity's API is insufficient. This is only supported via the Java API.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
**Example with Spring Data JPA / Jakarta Persistence:**
```java
Specification<Person> specification = (root, cq, cb) -> cb.equal(root.get("lastName"), "Skywalker");
Query query = Querity.query()
    .filter(filterByNative(specification))
    .build();
```

**Example with Spring Data MongoDB / Elasticsearch:**
```java
Criteria criteria = Criteria.where("lastName").is("Skywalker");
Query query = Querity.query()
    .filter(filterByNative(criteria))
    .build();
```

### Response
#### Success Response (200)
N/A (Java API)

#### Response Example
N/A
```

--------------------------------

### Configure PropertyNameMappingPreprocessor as a Spring Bean

Source: https://queritylib.github.io/spring-mvc/preprocessors.html

Instantiate a PropertyNameMappingPreprocessor bean in your Spring configuration to define property name mappings using SimplePropertyNameMapper.

```java
@SpringBootApplication
public class MyApplication {
  public static void main(String[] args) {
    SpringApplication.run(MyApplication.class, args);
  }

  @Bean
  public QueryPreprocessor preprocessor1() {
    return new PropertyNameMappingPreprocessor(
        SimplePropertyNameMapper.builder()
            .recursive(true) // default is true
            .mapping("prop1", "prop2") // customize your mappings here
            .build());
  }
}
```

--------------------------------

### Sort by String Length

Source: https://queritylib.github.io/features/functions.html

Use the `length()` function in sorting to order results based on the length of a string property. Ensure `Querity` and necessary static imports are available.

```java
// Sort by string length
Query query = Querity.query()
    .sort(sortBy(length(prop("lastName")), DESC))
    .build();
```

--------------------------------

### Fetch Join (Eager Loading) with JPA

Source: https://queritylib.github.io/features/query-customizers.html

Use fetchJoin to eagerly load associated entities and resolve N+1 query issues. Supports nested paths for complex associations.

```java
import io.github.queritylib.querity.jpa.JPAHints;

// Fetch associated entities eagerly to avoid N+1 queries
Query query = Querity.query()
    .filter(filterBy("status", EQUALS, "ACTIVE"))
    .customize(JPAHints.fetchJoin("orders", "orders.items")) // Supports nested paths
    .build();
List<Person> results = querity.findAll(Person.class, query);
```

--------------------------------

### Named Entity Graphs with JPA

Source: https://queritylib.github.io/features/query-customizers.html

Apply a predefined JPA entity graph to a query for fetch optimization.

```java
Query query = Querity.query()
    .customize(JPAHints.namedEntityGraph("Person.withOrders"))
    .build();
```

--------------------------------

### Select with Function Expressions

Source: https://queritylib.github.io/features/functions.html

Use various functions like `upper()`, `concat()`, and `length()` within the `selectBy()` clause for projections. Aliases can be assigned using `.as()`. Ensure `Querity` and necessary static imports are available.

```java
// Select with function expressions
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        prop("id"),
        upper(prop("lastName")).as("upperLastName"),
        concat(prop("firstName"), lit(" "), prop("lastName")).as("fullName"),
        length(prop("email")).as("emailLength")
    ))
    .build();
```

--------------------------------

### Build Query with NOT Condition (Complex)

Source: https://queritylib.github.io/features/filters.html

Negate a complex logical condition (e.g., AND) using Querity.not() to exclude results matching a combination of criteria.

```java
Query query = Querity.query()
    .filter(
        not(and(
            filterBy("firstName", EQUALS, "Luke"),
            filterBy("lastName", EQUALS, "Skywalker")
        ))
    ).build();
```

--------------------------------

### Apply Preprocessor to RestController Parameter

Source: https://queritylib.github.io/spring-mvc/preprocessors.html

Use the @WithPreprocessor annotation on Query or Condition parameters in Spring RestControllers to trigger the registered preprocessor.

```java
@RestController
public class MyRestController {

  @Autowired
  MyService service;

  @GetMapping(value = "/people", produces = MediaType.APPLICATION_JSON_VALUE)
  Result<Person> getPeople(@RequestParam(required = false) @WithPreprocessor("preprocessor1") Query q) {
    return service.getPeople(q);
  }
}
```

--------------------------------

### Optional WHERE Keyword

Source: https://queritylib.github.io/spring-mvc/query-language.html

Using the optional WHERE keyword for improved readability.

```text
where lastName="Skywalker"
where and(firstName="Luke", lastName="Skywalker") sort by age
distinct where status="ACTIVE" page 1,10
```

--------------------------------

### JPA Query Customization

Source: https://queritylib.github.io/features/query-customizers.html

Methods for optimizing JPA queries using fetch joins, entity graphs, and various performance hints.

```APIDOC
## JPA Query Customization

### Description
Use the `.customize()` method on a Querity query to apply JPA-specific hints for performance tuning and N+1 query resolution.

### Usage
- **Fetch Join**: `JPAHints.fetchJoin(String... paths)` - Eagerly load associations.
- **Named Entity Graph**: `JPAHints.namedEntityGraph(String name)` - Apply a predefined JPA entity graph.
- **Batch Size**: `JPAHints.batchSize(int size)` - Set JDBC fetch size.
- **Timeout**: `JPAHints.timeout(int ms)` - Set query execution timeout in milliseconds.
- **Cacheable**: `JPAHints.cacheable(boolean enabled)` - Enable or disable L2 query caching.

### Request Example
```java
Query query = Querity.query()
    .filter(filterBy("status", EQUALS, "ACTIVE"))
    .customize(
        JPAHints.fetchJoin("orders", "orders.items"),
        JPAHints.batchSize(50),
        JPAHints.timeout(5000),
        JPAHints.cacheable(true)
    )
    .build();
```
```

--------------------------------

### Build a distinct query with Querity

Source: https://queritylib.github.io/features/distinct.html

Use this pattern when filtering by nested properties that might cause duplicate SQL rows. Note that this flag has no effect in NoSQL databases.

```java
Query query = Querity.query()
    .distinct(true)
    .filter(filterBy("orders.items.quantity", GREATER_THAN, 8))
    .pagination(1, 5)
    .build();
```

--------------------------------

### Modify a Query using QueryBuilder

Source: https://queritylib.github.io/features/modify-query.html

Use toBuilder() to create a mutable copy of an existing query, apply modifications, and build the new instance.

```java
Query query = originalQuery.toBuilder()
    .sort(sortBy("lastName"))
    .build();
```

--------------------------------

### Function Expressions

Source: https://queritylib.github.io/spring-mvc/query-language.html

Using built-in functions within query expressions.

```text
UPPER(lastName)="SKYWALKER"
LENGTH(firstName)>5
LOWER(email) starts with "luke"
and(UPPER(lastName)="SKYWALKER", LENGTH(firstName)>3)
sort by LENGTH(lastName) desc
COALESCE(nickname, firstName)="Luke"
```

--------------------------------

### Configure Querity bean manually

Source: https://queritylib.github.io/installing.html

Define the Querity bean in your Spring configuration when not using Spring Boot autoconfiguration.

```java
import io.github.queritylib.querity.api.Querity;

@Configuration
public class QuerityConfiguration {
  @Bean
  Querity querity(EntityManager entityManager) {
    return new QuerityJpaImpl(entityManager);
  }
}
```

--------------------------------

### Query Language Grammar

Source: https://queritylib.github.io/spring-mvc/query-language.html

The formal grammar definition for the query language in ANTLR v4 format.

```ANTLR
DISTINCT    : 'distinct';
WHERE       : 'where';
AND         : 'and';
OR          : 'or';
NOT         : 'not';
SORT        : 'sort by';
ASC         : 'asc';
DESC        : 'desc';
PAGINATION  : 'page';
SELECT      : 'select';
GROUP_BY    : 'group by';
HAVING      : 'having';
AS          : 'as';
NEQ         : '!=';
LTE         : '<=';
GTE         : '>=';
EQ          : '=';
LT          : '<';
GT          : '>';
STARTS_WITH : 'starts with';
ENDS_WITH   : 'ends with';
CONTAINS    : 'contains';
IS_NULL     : 'is null';
IS_NOT_NULL : 'is not null';
IN          : 'in';
NOT_IN      : 'not in';
LPAREN      : '(';
RPAREN      : ')';
COMMA       : ',';

INT_VALUE     : [0-9]+;
DECIMAL_VALUE : [0-9]+'.'[0-9]+;
BOOLEAN_VALUE : 'true' | 'false';
PROPERTY      : [a-zA-Z_][a-zA-Z0-9_.]*;
STRING_VALUE  : '"' (~["\\] | '\\' .)* '"';
FUNCTION_NAME : 'UPPER' | 'LOWER' | 'LENGTH' | 'TRIM' | 'LTRIM' | 'RTRIM' |
                'ABS' | 'SQRT' | 'MOD' | 'CONCAT' | 'SUBSTRING' | 'LOCATE' |
                'COALESCE' | 'NULLIF' | 'CURRENT_DATE' | 'CURRENT_TIME' |
                'CURRENT_TIMESTAMP' | 'COUNT' | 'SUM' | 'AVG' | 'MIN' | 'MAX';

query            : DISTINCT? (WHERE? condition)? (SORT sortFields)? (PAGINATION paginationParams)? ;
advancedQuery    : (SELECT selectFields)? (WHERE? condition)? (GROUP_BY groupByFields)? (HAVING havingCondition)? (SORT sortFields)? (PAGINATION paginationParams)? ;
condition        : simpleCondition | conditionWrapper | notCondition;
operator         : NEQ | LTE | GTE | EQ | LT | GT | STARTS_WITH | ENDS_WITH | CONTAINS | IS_NULL | IS_NOT_NULL | IN | NOT_IN ;
conditionWrapper : (AND | OR) LPAREN condition (COMMA condition)* RPAREN ;
notCondition     : NOT LPAREN condition RPAREN ;
simpleValue      : INT_VALUE | DECIMAL_VALUE | BOOLEAN_VALUE | STRING_VALUE;
arrayValue       : LPAREN simpleValue (COMMA simpleValue)* RPAREN ;
propertyOrFunction : PROPERTY | functionCall ;
functionCall     : FUNCTION_NAME LPAREN (functionArg (COMMA functionArg)*)? RPAREN ;
functionArg      : PROPERTY | simpleValue | functionCall ;
simpleCondition  : propertyOrFunction operator (simpleValue | arrayValue)? ;
direction        : ASC | DESC ;
sortField        : propertyOrFunction (direction)? ;
sortFields       : sortField (COMMA sortField)* ;
selectField      : propertyOrFunction (AS PROPERTY)? ;
selectFields     : selectField (COMMA selectField)* ;
groupByFields    : propertyOrFunction (COMMA propertyOrFunction)* ;
havingCondition  : condition ;
paginationParams : INT_VALUE COMMA INT_VALUE ;
```

--------------------------------

### Native Select Expressions with JPA

Source: https://queritylib.github.io/features/projections.html

For JPA, use `selectByNative` with `CriteriaBuilder` for advanced selections like concatenating strings. Results are returned as a list of maps.

```java
// Select concatenated full name using native expression
SelectionSpecification<Person> fullNameSpec = AliasedSelectionSpecification.of(
    (root, cb) -> cb.concat(cb.concat(root.get("firstName"), " "), root.get("lastName")),
    "fullName"
);
AdvancedQuery query = Querity.advancedQuery()
    .select(selectByNative(fullNameSpec))
    .build();
List<Map<String, Object>> results = querity.findAllProjected(Person.class, query);
// Each map contains: {fullName: "Luke Skywalker"}
```

--------------------------------

### Sort by Uppercase Value

Source: https://queritylib.github.io/features/functions.html

Use the `upper()` function in sorting to order results based on the uppercase version of a string property. Ensure `Querity` and necessary static imports are available.

```java
// Sort by uppercase value
Query query = Querity.query()
    .sort(sortBy(upper(prop("firstName"))))
    .build();
```

--------------------------------

### Filter by String Length

Source: https://queritylib.github.io/features/functions.html

Use the `length()` function to filter records based on the length of a string property. Ensure `Querity` and necessary static imports are available.

```java
// Filter by string length
Query query = Querity.query()
    .filter(filterBy(length(prop("firstName")), GREATER_THAN, 5))
    .build();
```

--------------------------------

### Building Queries with NOT Conditions

Source: https://queritylib.github.io/features/filters.html

Explains how to negate a filter condition using the NOT operator.

```APIDOC
## Building Queries with NOT Conditions

### Description
Use `Querity.not` to negate a single condition or a group of conditions.

### Method
N/A (Java API)

### Endpoint
N/A (Java API)

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
**Negating a simple condition:**
```java
Query query = Querity.query()
    .filter(not(filterBy("lastName", EQUALS, "Skywalker")))
    .build();
```

**Negating a complex condition:**
```java
Query query = Querity.query()
    .filter(
        not(and(
            filterBy("firstName", EQUALS, "Luke"),
            filterBy("lastName", EQUALS, "Skywalker")
        ))
    ).build();
```

### Response
#### Success Response (200)
N/A (Java API)

#### Response Example
N/A
```

--------------------------------

### Group by Function Expression

Source: https://queritylib.github.io/features/group-by.html

Group query results based on the output of a function applied to a property, such as converting a category name to uppercase.

```java
// Group by function result (e.g., group by uppercase category)
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        upper(prop("category")).as("upperCategory"),
        count(prop("id")).as("orderCount")
    ))
    .groupBy(upper(prop("category")))
    .build();
```

--------------------------------

### JPA Query Optimizations

Source: https://queritylib.github.io/features/query-customizers.html

Apply batch size, timeout, and cache settings to JPA queries using JPAHints.

```java
Query query = Querity.query()
    .customize(
        JPAHints.batchSize(50),      // Optimize JDBC fetch size
        JPAHints.timeout(5000),      // Query timeout in ms
        JPAHints.cacheable(true)     // Enable L2 Query Cache
    )
    .build();
```

--------------------------------

### Filter by Uppercase Last Name

Source: https://queritylib.github.io/features/functions.html

Use the `upper()` function to filter records based on the uppercase version of a property. Ensure `Querity` and necessary static imports are available.

```java
import static io.github.queritylib.querity.api.Querity.*;

// Filter by uppercase lastName
Query query = Querity.query()
    .filter(filterBy(upper(prop("lastName")), EQUALS, "SKYWALKER"))
    .build();
```

--------------------------------

### Select Specific Fields with AdvancedQuery

Source: https://queritylib.github.io/features/projections.html

Use `selectBy` with `advancedQuery` to retrieve only specified fields. Supports nested properties using dot notation. Results are returned as a list of maps.

```java
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy("firstName", "lastName", "address.city"))
    .filter(filterBy("lastName", EQUALS, "Skywalker"))
    .build();
List<Map<String, Object>> results = querity.findAllProjected(Person.class, query);
// Each map contains only: {firstName: "...", lastName: "...", city: "..."}
```

--------------------------------

### GROUP BY with HAVING Clause

Source: https://queritylib.github.io/features/group-by.html

Filter grouped results using the HAVING clause based on an aggregate condition, such as a sum greater than a specific value.

```java
AdvancedQuery query = Querity.advancedQuery()
    .select(selectBy(
        prop("category"),
        sum(prop("amount")).as("total")
    ))
    .groupBy("category")
    .having(filterBy(sum(prop("amount")), GREATER_THAN, 1000))
    .build();
// Returns only categories with total amount > 1000
```

--------------------------------

### Parse Query in Spring REST Controller

Source: https://queritylib.github.io/spring-mvc/query-language.html

Use QuerityParser to convert a query string parameter into a Query object for service layer processing.

```java
import io.github.queritylib.querity.api.Query;
import io.github.queritylib.querity.parser.QuerityParser;

@RestController
public class MyRestController {

  @Autowired
  MyService service;

  @GetMapping(value = "/people", produces = MediaType.APPLICATION_JSON_VALUE)
  Result<Person> getPeople(@RequestParam(required = false) String q) {
    Query query = QuerityParser.parseQuery(q);
    return service.getPeople(query);
  }
}
```

--------------------------------

### Disable Spring Boot autoconfiguration

Source: https://queritylib.github.io/installing.html

Add this property to application.properties to disable automatic configuration.

```properties
querity.autoconfigure.enabled=false
```

--------------------------------

### Build Query with Native JPA Specification

Source: https://queritylib.github.io/features/filters.html

Incorporate database-specific conditions using Querity.filterByNative with a Spring Data JPA Specification. This allows for complex queries not directly supported by Querity APIs.

```java
Specification<Person> specification = (root, cq, cb) -> cb.equal(root.get("lastName"), "Skywalker");
Query query = Querity.query()
    .filter(filterByNative(specification))
    .build();
```

--------------------------------

### Build Query with Native MongoDB/Elasticsearch Criteria

Source: https://queritylib.github.io/features/filters.html

Incorporate database-specific conditions using Querity.filterByNative with Spring Data MongoDB or Elasticsearch Criteria. This is useful for complex queries not directly supported by Querity APIs.

```java
Criteria criteria = Criteria.where("lastName").is("Skywalker");
Query query = Querity.query()
    .filter(filterByNative(criteria))
    .build();
```

--------------------------------

### JPA Native Sort by Length

Source: https://queritylib.github.io/features/native-sort.html

Sorts results by the length of the 'lastName' field using JPA CriteriaBuilder. Requires OrderSpecification and Querity.

```java
// Sort by length of lastName using CriteriaBuilder
OrderSpecification<Person> orderSpec = (root, cb) -> cb.asc(cb.length(root.get("lastName")));
Query query = Querity.query()
    .sort(sortByNative(orderSpec))
    .build();
List<Person> results = querity.findAll(Person.class, query);

```

--------------------------------

### Compare Entity Fields with filterByField

Source: https://queritylib.github.io/features/field-comparison.html

Use `filterByField` to compare two entity fields directly. Reference fields on the right-hand side using `Querity.field("propertyName")`. Supports various comparison operators.

```java
Query query = Querity.query()
    .filter(filterByField("quantity", GREATER_THAN, field("availableStock")))
    .build();
```

```java
Query query = Querity.query()
    .filter(filterByField("salary", GREATER_THAN, field("manager.salary")))
    .build();
```

--------------------------------

### MongoDB Native Sort by Field

Source: https://queritylib.github.io/features/native-sort.html

Sorts MongoDB results by the 'lastName' field using Spring Data MongoDB's Sort.Order. Requires Spring Data MongoDB and Querity.

```java
// Sort using Spring Data MongoDB's Order
org.springframework.data.domain.Sort.Order order = Sort.Order.asc("lastName");
Query query = Querity.query()
    .sort(sortByNative(order))
    .build();


```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.