Table Qualifiers in Select Statements

The library attempts to automatically calculate table qualifiers. If a table qualifier is specified, the library will automatically render the table qualifier on all columns associated with the table. For example with the following query:

SelectStatementProvider selectStatement =
    select(id, animalName)
    .from(animalData, "ad")
    .build()
    .render(RenderingStrategies.MYBATIS3);

The library will render SQL as:

select ad.id, ad.animal_name
from AnimalData ad

Notice that the table qualifier ad is automatically applied to columns in the select list.

In the case of join queries the table qualifier specified, or if not specified the table name itself, will be used as the table qualifier. However, this function is disabled for joins on subqueries.

With subqueries, it is important to understand the limits of automatic table qualifiers. The rules are as follows:

1. The scope of automatic table qualifiers is limited to a single select statement. For subqueries, the outer query has a different scope than the subquery.
2. A qualifier can be applied to a subquery, but that qualifier is not automatically applied to any column

As an example, consider the following query:

DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");

SelectStatementProvider selectStatement =
    select(animalName, rowNum)
    .from(
        select(id, animalName)
        .from(animalData, "a")
        .where(id, isLessThan(22))
        .orderBy(animalName.descending()),
        "b"
    )
    .where(rowNum, isLessThan(5))
    .and(animalName, isLike("%a%"))
    .build()
    .render(RenderingStrategies.MYBATIS3);

The rendered SQL will be as follows:

select animal_name, rownum()
from (select a.id, a.animal_name
      from AnimalDate a
      where id < #{parameters.p1}
      order by animal_name desc) b
where rownum() < #{parameters.p2}
  and animal_name like  #{parameters.p3}

Notice that the qualifier a is automatically applied to columns in the subquery and that the qualifier b is not applied anywhere.

If your query requires the subquery qualifier to be applied to columns in the outer select list, you can manually apply the qualifier to columns as follows:

DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");

SelectStatementProvider selectStatement =
    select(animalName.qualifiedWith("b"), rowNum)
    .from(
        select(id, animalName)
        .from(animalData, "a")
        .where(id, isLessThan(22))
        .orderBy(animalName.descending()),
        "b"
    )
    .where(rowNum, isLessThan(5))
    .and(animalName.qualifiedWith("b"), isLike("%a%"))
    .build()
    .render(RenderingStrategies.MYBATIS3);

In this case, we have manually applied the qualifier b to columns in the outer query. The rendered SQL looks like this:

select b.animal_name, rownum()
from (select a.id, a.animal_name
      from AnimalDate a
      where id < #{parameters.p1}
      order by animal_name desc) b
where rownum() < #{parameters.p2}
  and b.animal_name like  #{parameters.p3}

Subqueries in Where Conditions

The library support subqueries in the following where conditions:

• exists
• notExists
• isEqualTo
• isNotEqualTo
• isIn
• isNotIn
• isGreaterThan
• isGreaterThanOrEqualTo
• isLessThan
• isLessThanOrEqualTo

An example of an exists subquery is as follows:

SelectStatementProvider selectStatement = select(itemMaster.allColumns())
        .from(itemMaster, "im")
        .where(exists(
                select(orderLine.allColumns())
                .from(orderLine, "ol")
                .where(orderLine.itemId, isEqualTo(itemMaster.itemId))
        ))
        .orderBy(itemMaster.itemId)
        .build()
        .render(RenderingStrategies.MYBATIS3);

Note that the qualifier for the outer query (“im”) is automatically applied to the inner query, as well as the qualifier for the inner query (“ol”). Carrying alias from an outer query to an inner query is only supported with exists or not exists sub queries.

An example of a column based subquery is as follows:

SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .where(brainWeight, isEqualTo(
                select(min(brainWeight))
                .from(animalData)
            )
        )
        .orderBy(animalName)
        .build()
        .render(RenderingStrategies.MYBATIS3);

val selectStatement = select(ItemMaster.allColumns()) {
    from(ItemMaster, "im")
    where {
       exists {
          select(OrderLine.allColumns()) {
             from(OrderLine, "ol")
             where { OrderLine.itemId isEqualTo ItemMaster.itemId }
          }
       }
       orderBy(ItemMaster.itemId)
    }
}

val selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId) {
    from(Person)
    where {
       id isEqualTo {
          select(max(id)) {
             from(Person)
          }
       }
    }
}

Subqueries in Insert Statements

The library supports an INSERT statement that retrieves values from a SELECT statement. For example:

InsertSelectStatementProvider insertSelectStatement = insertInto(animalDataCopy)
        .withColumnList(id, animalName, bodyWeight, brainWeight)
        .withSelectStatement(
            select(id, animalName, bodyWeight, brainWeight)
            .from(animalData)
            .where(id, isLessThan(22))
        )
        .build()
        .render(RenderingStrategies.MYBATIS3);

val insertStatement = insertSelect(Person) {
    columns(id, firstName, lastName, birthDate, employed, occupation, addressId)
    select(add(id, constant<Int>("100")), firstName, lastName, birthDate, employed, occupation, addressId) {
        from(Person)
        orderBy(id)
    }
}

Subqueries in Update Statements

The library supports setting update values based on the results of a subquery. For example:

UpdateStatementProvider updateStatement = update(animalData)
        .set(brainWeight).equalTo(
            select(avg(brainWeight))
            .from(animalData)
            .where(brainWeight, isGreaterThan(22.0))
        )
        .where(brainWeight, isLessThan(1.0))
        .build()
        .render(RenderingStrategies.MYBATIS3);

val updateStatement = update(Person) {
    set(addressId) equalToQueryResult {
        select(add(max(addressId), constant<Int>("1"))) {
            from(Person)
        }
    }
    where { id isEqualTo 3 }
}

Subqueries in a From Clause

The library supports subqueries in from clauses and the syntax is a natural extension of the select DSL. An example is as follows:

DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");

SelectStatementProvider selectStatement =
    select(animalName, rowNum)
    .from(
        select(id, animalName)
        .from(animalData)
        .where(id, isLessThan(22))
        .orderBy(animalName.descending())
    )
    .where(rowNum, isLessThan(5))
    .and(animalName, isLike("%a%"))
    .build()
    .render(RenderingStrategies.MYBATIS3);

Notice the use of a DerivedColumn to easily specify a function like rownum() that can be used both in the select list and in a where condition.

val selectStatement =
    select(firstName, rowNum) {
        from {
            select(id, firstName) {
                from(Person)
                where { id isLessThan 22 }
                orderBy(firstName.descending())
            }
        }
        where { rowNum isLessThan 5 }
        and { firstName isLike "%a%" }
    }

val selectStatement =
    select(firstName, rowNum) {
        from {
            select(id, firstName) {
                from(Person, "a")
                where { id isLessThan 22 }
                orderBy(firstName.descending())
            }
            + "b"
        }
        where { rowNum isLessThan 5 }
        and { firstName isLike "%a%" }
    }

Subqueries in Join Clauses

The library supports subqueries in “join” clauses similarly to subqueries in “from” clauses. For example:

SelectStatementProvider selectStatement = select(orderMaster.orderId, orderMaster.orderDate,
        orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
    .from(orderMaster, "om")
    .join(
        select(orderDetail.orderId, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
        .from(orderDetail),
        "od")
    .on(orderMaster.orderId, equalTo(orderDetail.orderId.qualifiedWith("od")))
    .build()
    .render(RenderingStrategies.MYBATIS3);

This is rendered as:

select om.order_id, om.order_date, line_number, description, quantity
from OrderMaster om
join (select order_id, line_number, description, quantity from OrderDetail) od
on om.order_id = od.order_id

Notice that the subquery is aliased with “od”, but that alias is not automatically applied so it must be specified when required. If in doubt, specify the alias with the qualifiedBy method.

val selectStatement = select(OrderLine.orderId, OrderLine.quantity,
        "im"(ItemMaster.itemId), ItemMaster.description) {
    from(OrderMaster, "om")
    join(OrderLine, "ol") {
        on(OrderMaster.orderId) equalTo OrderLine.orderId
    }
    leftJoin(
       {
          select(ItemMaster.allColumns()) {
             from(ItemMaster)
          }
          + "im"
      }
    ) {
        on(OrderLine.itemId) equalTo "im"(ItemMaster.itemId)
    }
    orderBy(OrderLine.orderId, ItemMaster.itemId)
}

select ol.order_id, ol.quantity, im.item_id, description
from OrderMaster om join OrderLine ol on om.order_id = ol.order_id
left join (select * from ItemMaster) im on ol.item_id = im.item_id
order by order_id, item_id