While EzQu excels at mapping raw database tables to Java objects, real-world business logic often requires executing database-side functions, such as aggregations, string manipulations, or conditional checks. EzQu bridges the gap between pure Java and native SQL using the com.centimia.orm.ezqu.Function utility class.

The Function class allows you to invoke common SQL functions directly within your type-safe Fluent API queries. These functions can be used either as a direct parameter in a select() method to return a single calculated value, or within an anonymous inner class to map derived data into a transient @Extension field, or, by using a complete non persistent object for mapping the results.

Here is an in-depth look at the available functions and how to use them.

Aggregate Functions

EzQu supports standard SQL aggregations: max(), min(), avg(), sum() and count()

When using aggregate functions, you must pass the target field and the active Db session context. The session context allows EzQu to register the function token within the query tree safely

Example: Direct Value Selection If you only need a single value (e.g., the maximum salary or the total number of records), you can pass the function directly into the select() statement.

Double maxSalary = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT MAX(salary) FROM Employee WHERE id > 8;
    return db.from(emp).where(emp.getId()).biggerThan(8L).selectFirst(Function.max(emp.getSalary(), db));
});

Counting

There are several ways to perform counting with EzQu. The obvious way, like in min(), max()... you can use Function.count(), or Function.count(column). Here are examples:

long numOfJohns = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT COUNT(last_name) FROM Employee where name = 'john'
    return db.from(emp).where(emp.getName()).is("john").selectFirst(Function.count(emp.getLastName(), db));
});

or

long numRecords = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT COUNT(*) FROM Employee
    return db.from(emp).selectFirst(Function.count());
});

While Function.count() or Function.count(Z) can be used, EzQu also provides built-in query-level counters. You can use selectCount() on the query builder for a simple row count. For complex grouped counts, use the selectCount(Z) object. With selectCount(Z) you can alos arrange the results in ASC or DESC order. Here are examples:

long totalEmployees = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT COUNT(*) FROM Employee
    return db.from(emp).selectCount()
});

or

long numOfJohns = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT COUNT(*) FROM Employee where name = 'john'
    return db.from(emp).where(emp.getName()).is("john").selectCount();
});

or

List<ColumnCount<String>> nameCount = sessionFactory.getFromSession(db ->
    Employee emp = new Employee();

    // Equivalent to: SELECT name, COUNT(*) FROM Employee group by name
    return db.from(emp).selectCount(emp.getLastName());
});

String Manipulation

You can manipulate strings on the database side before they are returned to your Java application using length(), concat(), and like()

Example: Dynamic Concatenation inside an Anonymous Class Often, you want to merge fields (like a first and last name) into a single non-persisted @Extension field. The concat() function accepts the Db session, an SQL alias name, and a varargs array of fields or raw strings to combine

List<Person> activePeople = sessionFactory.getSession(select -> {
    Person p = new Person();

    // We populate an extended "fullName" field dynamically
    // Equivalent to: select first_name as firstName, last_name as lastName, concat(first_name, ' ', last_name) as fullName from Person where status = 'ACTIVE'
    return select.from(p).where(p.getStatus()).is("ACTIVE").select(new Person() {
        {
            // Map standard fields
            firstName = p.getFirstName(); 
            lastName = p.getLastName();

            // Database-side concatenation: CONCAT(firstName, ' ', lastName) as full_name
            fullName = Function.concat(db, "full_name", p.getFirstName(), " ", p.getLastName()); 
        }
    });
});

In the latter example, Person is an entity and fullName was marked with the @Extensionannotation. As explained here, you can also use a non entity with fields which are not persistent as your anonymous class and map functions to fields. Here is another example:

List<TestObject> testObjects = sessionFactory.getFromSession(db -> {

    final TableForFunctions desc = new TableForFunctions();

    // Unlike the filter like, this like returns true or false on condition match. Test all that their name is like 'name1' and return true
    // Equivalent to: select id, name, (name like '%me1') as testResult, value from table_for_functions
    return db.from(desc).select(new TestObject() {
        {
            id = desc.getId();
            name = desc.getName();
            testResult = Function.like(desc.getName(), "%me1", db);
            value = desc.value;
        }
    });
});

Conditional and Null Handling

Handling NULL values safely in the database prevents unexpected NullPointerExceptions in your Java code. EzQu provides isNull(), isNotNull(), and ifNull().

The ifNull function is particularly powerful. It translates to IFNULL, NVL or COALESCE (depending on your database dialect) and allows you to provide a fallback value if the target field is null.

Example: Like all other functions, ifNull can be used directly in the select or within an anonymous class data.

List<Product> products = sessionFactory.getFromSession(select -> {
    Product prod = new Product();

    // Equivalent to: select name, nvl(price, 0.0) as price from product
    return select.from(prod).select(new Product() {
        {
            name = prod.getName();
            // If the database price is NULL, return 0.0 instead
            price = Function.ifNull(prod.getPrice(), 0.0, select);
        }
    });
});

or

List<Double> prices = sessionFactory.getFromSession(select -> {
    Product prod = new Product();

    // Equivalent to: select nvl(price, 0.0) as price from product
    return select.from(prod).select(Function.ifNull(prod.getPrice(), 0.0, select));
});

Like Function.like(), _the isNull() _and isNotNull() functions differ from the builder's filter isNull() and isNotNull() methods. These functions are boolean, returning true or false according to the condition.

List<Product> products = sessionFactory.getFromSession(select -> {
    Product prod = new Product();

    // Equivalent to: select name, nvl(price, 0.0) as price, (end_date is null) as isActive from product
    return select.from(prod).select(new Product() {
        {
            name = prod.getName();
            // If the database price is NULL, return 0.0 instead
            price = Function.ifNull(prod.getPrice(), 0.0, select);
            isActive = Function.isNull(prod.getEndDate(), db);
        }
    });
});

Logical Operators

For highly complex conditional logic, the Function class exposes and(), or(), and not(). These are generally used when you need to construct advanced boolean groupings that go beyond the standard chained .where().and().or() builder methods.

List<Product> products = sessionFactory.getFromSession(select -> {
    Product prod = new Product();

    // Equivalent to: select name, nvl(price, 0.0) as price, (endDate is null or status = 'ACTIVE') as isActive from product
    return select.from(prod).select(new Product() {
        {
            name = prod.getName();
            // If the database price is NULL, return 0.0 instead
            price = Function.ifNull(prod.getPrice(), 0.0, select);
            isActive = Function.or(select, Function.isNull(prod.getEndDate(), db),  Function.like(prod.getStatus(), "ACTIVE", select));
        }
    });
});