Back to Examples

Retrieving data

SELECT query examples using DTO mode, SQL mode, and native SQL.

Row Results

When querying in SQL mode or using Native SQL, Litebridge returns records encapsulated in the Rowclass, providing easy column access without requiring entity or DTO mapping.

Retrieve all

Select all entities of a specific type.

SelectAllExample.java
import org.example.dto.Person;
List<Person> persons = litebridge.select(Person.class).list();

Query with conditions

Fetch all Person entities where the "name" field value is "Alice" and the "id" field value is less than 5. This does not fetch any related entities.

SelectWhereExample.java
import org.example.dto.Person;
import org.example.entity.meta.PersonMeta;
List<Person> persons = litebridge.select(Person.class)
    .where(PersonMeta.name).eq("Alice")
    .and(PersonMeta.id).lt(5)
    .list();

Query with joins

Fetch all Person and related Account entities. Person has an "accounts" field of type "List".

SelectJoinExample.java
import org.example.dto.Person;
import org.example.dto.Account;
import org.example.entity.meta.PersonMeta;
List<Person> persons = litebridge.select(Person.class)
    .join(Account.class).on(PersonMeta.accounts)
    .list();

Select specific columns

Retrieve and populate specific fields of the first matching entity found, and apply a scalar function to change the "surname" field to uppercase.

SelectColumnsExample.java
import static org.example.meta.PersonMeta.id;
import static org.example.meta.PersonMeta.name;
import static org.example.meta.PersonMeta.surname;
// Only the "id", "name", and "surname" fields are populated
Person person = litebridge.select(id, name, surname.upper())
    .from(Person.class)
    .where(PersonMeta.name).eq("Alice")
    .orderBy(PersonMeta.id).asc()
    .firstOrNull();

Programmatic criteria queries

When assembling dynamic search filters from optional inputs,DtoWhereCriteriaBuilderflattens condition assembly by eliminating manual tracking of where() vs and():

Construct conditional WHERE clauses dynamically using DtoWhereCriteriaBuilder without branching over where() vs and().

CriteriaBuilderExample.java
import org.jspecify.annotations.Nullable;

import org.litebridge.orm.api.condition.DtoWhereCriteriaBuilder;

import org.example.dto.Person;
import org.example.entity.meta.PersonMeta;
public List<Person> searchPersons(
        @Nullable String name,
        @Nullable Integer minAge,
        @Nullable String status) {

    // Initialise criteria builder from a select statement
    DtoWhereCriteriaBuilder<Person> criteria = new DtoWhereCriteriaBuilder<>(
            litebridge.select(Person.class)
    );

    // Conditions can be added sequentially; the first condition acts as WHERE,
    // and subsequent conditions default to AND
    if (name != null && !name.isBlank()) {
        criteria.add(q -> q.where(PersonMeta.name).like(name + "%"));
    }

    if (minAge != null) {
        criteria.add(q -> q.where(PersonMeta.age).gte(minAge));
    }

    if (status != null && !status.isBlank()) {
        criteria.add(q -> q.where(PersonMeta.status).eq(status));
    }

    return criteria.build()
            .orderBy(PersonMeta.id).asc()
            .list();
}