Package org.fluentjdbc

Class DbContextJoinedSelectBuilder

java.lang.Object

org.fluentjdbc.DbContextJoinedSelectBuilder

All Implemented Interfaces:

DatabaseQueryable<DbContextJoinedSelectBuilder>, DbContextListableSelect<DbContextJoinedSelectBuilder>

@CheckReturnValue
public class DbContextJoinedSelectBuilder
extends Object
implements DbContextListableSelect<DbContextJoinedSelectBuilder>

DbContextSelectBuilder used to generate joined queries using SQL-92 standard SELECT * FROM table1 a JOIN table2 b ON a.column = b.column. To specify columns for selection and tables for retrieval of columns, use DbContextTableAlias and DatabaseColumnReference.

Only works well on JDBC drivers that implement ResultSetMetaData.getTableName(int), as this is used to calculate column indexes for aliased tables. This includes PostgreSQL, H2, HSQLDB, and SQLite, but not Oracle or SQL Server.

Pull requests are welcome for a substitute for SQL Server and Oracle.

Usage example:

 DbContextTableAlias p = productsTable.alias("p");
 DbContextTableAlias o = ordersTable.alias("o");
 return context
         .join(linesAlias.column("product_id"), p.column("product_id"))
         .join(linesAlias.column("order_id"), o.column("order_id"))
         .list(row -> new OrderLineEntity(
                 OrderRepository.toOrder(row.table(o)),
                 ProductRepository.toProduct(row.table(p)),
                 toOrderLine(row.table(linesAlias))
         ));
 

Constructor Summary

Constructors

Constructor	Description
DbContextJoinedSelectBuilder(DbContextTableAlias table)

Method Summary

Modifier and Type	Method	Description
void	forEach(DatabaseResult.RowConsumer consumer)	Executes the SELECT * FROM ... statement and calls back to DatabaseResult.RowConsumer for each returned row
int	getCount()	Executes SELECT count(*) FROM ... on the query and returns the result
DbContextJoinedSelectBuilder	join(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields)	Adds an additional table to the join as an inner join.
DbContextJoinedSelectBuilder	join(DatabaseColumnReference a, DatabaseColumnReference b)	Adds an additional table to the join as an inner join.
DbContextJoinedSelectBuilder	leftJoin(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields)	Adds an additional table to the join as a left join.
DbContextJoinedSelectBuilder	leftJoin(DatabaseColumnReference a, DatabaseColumnReference b)	Adds an additional table to the join as a left join.
<OBJECT> List<OBJECT>	list(DatabaseResult.RowMapper<OBJECT> mapper)	Execute the query and map each return value over the DatabaseResult.RowMapper function to return a list.
DbContextJoinedSelectBuilder	orderBy(String orderByClause)	Adds an order by clause to the query.
DbContextJoinedSelectBuilder	orderBy(DatabaseColumnReference column)	Adds an order by clause to the query.
DbContextJoinedSelectBuilder	query()	Returns or creates a query object to be used to add DatabaseQueryable.where(String, Object) statements and operations
<OBJECT> Optional<OBJECT>	singleObject(DatabaseResult.RowMapper<OBJECT> mapper)	If the query returns no rows, returns Optional.empty(), if exactly one row is returned, maps it and return it, if more than one is returned, throws `IllegalStateException`
DbContextJoinedSelectBuilder	skipAndLimit(int offset, int rowCount)	Adds OFFSET ...
<OBJECT> Stream<OBJECT>	stream(DatabaseResult.RowMapper<OBJECT> mapper)	Execute the query and map each return value over the DatabaseResult.RowMapper function to return a stream.
DbContextJoinedSelectBuilder	unordered()	If you haven't called orderBy(java.lang.String), the results of DatabaseListableQueryBuilder.list(java.sql.Connection, org.fluentjdbc.DatabaseResult.RowMapper<OBJECT>) will be unpredictable.
DbContextJoinedSelectBuilder	whereAll(List<String> fields, List<Object> values)	For each field adds "WHERE fieldName = value" to the query
DbContextJoinedSelectBuilder	whereExpressionWithParameterList(String expression, Collection<?> parameters)	Adds the expression to the WHERE-clause and all the values to the parameter list.
DbContextJoinedSelectBuilder	whereOptional(String fieldName, Object value)	Adds "WHERE fieldName = value" to the query unless value is null

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface org.fluentjdbc.DatabaseQueryable

where, whereExpression, whereExpression, whereIn

Methods inherited from interface org.fluentjdbc.DbContextListableSelect

limit, listLongs, listStrings, singleInputStream, singleInstant, singleLong, singleReader, singleString

Constructor Detail

DbContextJoinedSelectBuilder

public DbContextJoinedSelectBuilder(@Nonnull
                                    DbContextTableAlias table)

Method Detail

join

public DbContextJoinedSelectBuilder join(DatabaseColumnReference a,
                                         DatabaseColumnReference b)

Adds an additional table to the join as an inner join. Inner joins require a matching row in both tables and will leave out rows from one of the table where there is no corresponding table in the other

join

public DbContextJoinedSelectBuilder join(List<String> leftFields,
                                         DbContextTableAlias joinedTable,
                                         List<String> rightFields)

Adds an additional table to the join as an inner join. Inner joins require that all columns in both tables match and will leave out rows from one of the table where there is no corresponding table in the other

leftJoin

public DbContextJoinedSelectBuilder leftJoin(DatabaseColumnReference a,
                                             DatabaseColumnReference b)

Adds an additional table to the join as a left join. Left join only require a matching row in the first/left table. If there is no matching row in the second/right table, all columns are returned as null in this table. When calling DatabaseRow.table(DatabaseTableAlias) on the resulting row, null is returned

leftJoin

public DbContextJoinedSelectBuilder leftJoin(List<String> leftFields,
                                             DbContextTableAlias joinedTable,
                                             List<String> rightFields)

Adds an additional table to the join as a left join. Left join only require a matching row in the first/left table. If there is no matching row in the second/right table, all columns are returned as null in this table. When calling DatabaseRow.table(DatabaseTableAlias) on the resulting row, null is returned

getCount

public int getCount()

Executes SELECT count(*) FROM ... on the query and returns the result

Specified by:

getCount in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

stream

public <OBJECT> Stream<OBJECT> stream(DatabaseResult.RowMapper<OBJECT> mapper)

Execute the query and map each return value over the DatabaseResult.RowMapper function to return a stream. Example:

     t.join(t.column("id"), o.column("parent_id"))
          .where("status", status)
          .stream(row -> row.table(joinedTable).getInstant("created_at"))
 

Specified by:

stream in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

list

public <OBJECT> List<OBJECT> list(DatabaseResult.RowMapper<OBJECT> mapper)

Execute the query and map each return value over the DatabaseResult.RowMapper function to return a list. Example:

     DbContextTableAlias t = table.alias("t");
     DbContextTableAlias o = otherTable.alias("o");
     List<Instant> creationTimes = t.join(t.column("id"), o.column("parent_id"))
          .where(t.column("name"), name)
          .list(row -> row.table(o).getInstant("created_at"));
 

Specified by:

list in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

forEach

public void forEach(DatabaseResult.RowConsumer consumer)

Executes the SELECT * FROM ... statement and calls back to DatabaseResult.RowConsumer for each returned row

Specified by:

forEach in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

whereExpressionWithParameterList

public DbContextJoinedSelectBuilder whereExpressionWithParameterList(String expression,
                                                                     Collection<?> parameters)

Adds the expression to the WHERE-clause and all the values to the parameter list. E.g. whereExpression("created_at between ? and ?", List.of(earliestDate, latestDate))

Specified by:

whereExpressionWithParameterList in interface DatabaseQueryable<DbContextJoinedSelectBuilder>

whereOptional

public DbContextJoinedSelectBuilder whereOptional(String fieldName,
                                                  @Nullable
                                                  Object value)

Adds "WHERE fieldName = value" to the query unless value is null

Specified by:

whereOptional in interface DatabaseQueryable<DbContextJoinedSelectBuilder>

whereAll

public DbContextJoinedSelectBuilder whereAll(List<String> fields,
                                             List<Object> values)

For each field adds "WHERE fieldName = value" to the query

Specified by:

whereAll in interface DatabaseQueryable<DbContextJoinedSelectBuilder>

query

public DbContextJoinedSelectBuilder query()

Returns or creates a query object to be used to add DatabaseQueryable.where(String, Object) statements and operations

Specified by:

query in interface DatabaseQueryable<DbContextJoinedSelectBuilder>

singleObject

@Nonnull
public <OBJECT> Optional<OBJECT> singleObject(DatabaseResult.RowMapper<OBJECT> mapper)

If the query returns no rows, returns Optional.empty(), if exactly one row is returned, maps it and return it, if more than one is returned, throws `IllegalStateException`

Specified by:

singleObject in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

Parameters:

mapper - Function object to map a single returned row to a object

Returns:

the mapped row if one row is returned, Optional.empty otherwise

Throws:

IllegalStateException - if more than one row was matched the the query

unordered

public DbContextJoinedSelectBuilder unordered()

If you haven't called orderBy(java.lang.String), the results of DatabaseListableQueryBuilder.list(java.sql.Connection, org.fluentjdbc.DatabaseResult.RowMapper<OBJECT>) will be unpredictable. Call unordered() if you are okay with this.

orderBy

public DbContextJoinedSelectBuilder orderBy(String orderByClause)

Adds an order by clause to the query. Needed in order to list results in a predictable order.

Specified by:

orderBy in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

skipAndLimit

public DbContextJoinedSelectBuilder skipAndLimit(int offset,
                                                 int rowCount)

Adds OFFSET ... ROWS FETCH ... ROWS ONLY clause to the SELECT statement. FETCH FIRST was introduced in SQL:2008 and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.

Specified by:

skipAndLimit in interface DbContextListableSelect<DbContextJoinedSelectBuilder>

orderBy

public DbContextJoinedSelectBuilder orderBy(DatabaseColumnReference column)

Adds an order by clause to the query. Needed in order to list results in a predictable order.