4. More Complex Examples

Where Clause

val selectUsersMatchingEmptyUUIDAndNotMatchingTBDName = SelectStatement.Builder(
    USER_TABLE,
    listOf(USER_ID_COL, USER_NAME_COL, USER_EMAIL_COL, USER_BALANCE_COL),
    where =  WhereClause.builder().equal(ClauseInput.column(USER_ID_COL), UUID.fromString("00000000-0000-0000-0000-000000000000"))
        .and().notEqual(ClauseInput.column(USER_NAME_COL), ClauseInput.UNDETERMINED)
).build()
val statement: String = PostgresDialect.createSelectStatement(selectUsersMatchingEmptyUUIDAndNotMatchingTBDName)

This creates the following SQL statement:

SELECT user.id, user.name, user.email, user.balance FROM user WHERE user.id = '00000000-0000-0000-0000-000000000000' AND user.name != '?';

OrderBy Clause

val selectUsersOrderedByBalanceAscending = SelectStatement.Builder(
    USER_TABLE,
    listOf(USER_ID_COL, USER_NAME_COL, USER_EMAIL_COL, USER_BALANCE_COL),
    orderBy = OrderByClause.builder().asc().column(USER_BALANCE_COL),
).build()
val statement: String = PostgresDialect.createSelectStatement(selectUsersOrderedByBalanceAscending)

This creates the following SQL statement:

SELECT user.id, user.name, user.email, user.balance FROM user ORDER BY user.balance ASC;

Joining

For the next examples, the following tables and columns exist:

val TEAM_TABLE = ...
val TEAM_ID_COL = ... // INT
val TEAM_NAME_COL = ... // VARCHAR(50)
val TEAM_RANKING_COL = ... // INT

val userTable = ...
val USER_TEAM_ID_COL = ... // INT
val USER_ID_COL = ... // UUID
val USER_NAME_COL = ... // TEXT
val USER_AGE_COL = ... // INT

Examples

// selecting user names with team ranking less than 4 and age >= 18
SelectStatement.Builder(
    TEAM_TABLE,
    listOf(USER_NAME_COL),
    joins = listOf(
        TableJoin(
            USER_TABLE,
            TableJoin.Type.INNER_JOIN,
            JoinColumns.Builder().add(
                JoinColumn(TEAM_TABLE, TEAM_ID_COL),
                JoinColumn(USER_TABLE, USER_TEAM_ID_COL)
            ).build()
        )
    ),
    where =  WhereClause.builder().lessThan(ClauseInput.column(TEAM_RANKING_COL), 4)
        .and().greaterThanOrEqual(ClauseInput.column(USER_AGE_COL), 18)
).build()

This creates the following SQL statement:

"SELECT user.name FROM teams INNER JOIN users ON team.id = user.team_id WHERE team.ranking < 4 AND user.age >= 18;"