Skip to content

Fundamentals

Jump to bottom
Konstantin Triger edited this page Feb 14, 2022 · 3 revisions

Since we need to write a tutorial, we decided to not invent the wheel and use an existing one. After all they make a good job reviewing SQL from the very basic to advanced functionality. We have chosen PostgreSQL tutorial and wrote most interesting SQL samples from there in FluentJPA (skipping repeating stuff). So we suggest to open the original tutorial side-by-side and compare. Each topic below is a link to the relevant tutorial page; search the label above the sample. Have fun comparing!

JPA mappings (Model) is declared here.

PostgreSQL SELECT

Other basic clauses: INSERT, UPDATE, DELETE

Using SELECT statement to query data from multiple columns

FluentJPA.SQL((Customer customer) -> {

    SELECT(customer.getFirstName(), customer.getLastName(), customer.getEmail());
    FROM(customer);
});

Using SELECT statement to query data in all columns of a table

FluentJPA.SQL((Customer customer) -> {

    SELECT(customer);
    FROM(customer);
});

Using SELECT statement with expressions

FluentJPA.SQL((Customer customer) -> {

    SELECT(customer.getFirstName() + " " + customer.getLastName(), customer.getEmail());
    FROM(customer);
});

Using SELECT statement with only expressions

FluentJPA.SQL(() -> {

    SELECT(alias(5 * 3, "result"));
});

PostgreSQL ORDER BY

FluentJPA.SQL((Customer customer) -> {

    SELECT(customer.getFirstName(), customer.getLastName());
    FROM(customer);
    ORDER(BY(customer.getFirstName()).ASC(), BY(customer.getLastName()).DESC());
});

PostgreSQL WHERE

Using WHERE clause with the BETWEEN operator

FluentJPA.SQL((Customer customer) -> {

    long nameLength = alias(LENGTH(customer.getFirstName()), "name_length");

    SELECT(customer.getFirstName(), nameLength);
    FROM(customer);
    // String.matches() is mapped to SQL LIKE operator
    WHERE(customer.getFirstName().matches("A%") && BETWEEN(nameLength, 3, 5));
    ORDER(BY(nameLength));
});

PostgreSQL INNER JOIN

INNER JOIN to join 3 tables

FluentJPA.SQL((Staff staff,
               Payment payment,
               Customer customer) -> {

    // add more columns as needed
    SELECT(customer.getFirstName(), staff.getLastName(), payment.getAmount());
    FROM(customer).JOIN(payment)
            .ON(payment.getCustomer() == customer)
            .JOIN(staff)
            .ON(payment.getStaff() == staff);
});

PostgreSQL HAVING

HAVINGclause to select store that has more than 300 customers

FluentJPA.SQL((Customer customer) -> {

    int customerCount = COUNT(customer.getId());
    Store store = customer.getStore();

    SELECT(store, customerCount);
    FROM(customer);
    GROUP(BY(store));
    HAVING(customerCount > 300);
});

Getting Started

Examples

Basic SQL DML Statements

Advanced SQL DML Statements

Advanced Topics

Clone this wiki locally