#198

Committed 06 Oct 2025 10:27PM UTC coverage: 91.711% (-0.5%) from 92.242%

Build # #198

Build Type

push

Committed by

Commit Message

improved ergonomics of select builders

Run Details

88 of 100 new or added lines in 13 files covered. (88.0%)

2 existing lines in 1 file now uncovered.

1195 of 1303 relevant lines covered (91.71%)

0.92 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

89.11

/src/main/java/org/fluentjdbc/DatabaseJoinedQueryBuilder.java

package org.fluentjdbc;

import javax.annotation.CheckReturnValue;
import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * {@link DatabaseQueryBuilder} used to generate joined queries using SQL-92 standard
 * <code>SELECT * FROM table1 a JOIN table2 b ON a.column = b.column</code>. To specify
 * columns for selection and tables for retrieval of columns, use {@link DatabaseTableAlias}
 * and {@link DatabaseColumnReference}.
 *
 * <p><strong>Only works well on JDBC drivers that implement {@link ResultSetMetaData#getTableName(int)}</strong>,
 * as this is used to calculate column indexes for aliased tables. This includes PostgreSQL, H2, HSQLDB, and SQLite,
 * but not Oracle or SQL Server.</p>
 *
 * <p>Pull requests are welcome for a substitute for SQL Server and Oracle.</p>
 *
 * <h2>Usage example:</h2>
 *
 * <pre>
 * DatabaseTableAlias p = productsTable.alias("p");
 * DatabaseTableAlias 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(connection, row -&gt; new OrderLineEntity(
 *                 OrderRepository.toOrder(row.table(o)),
 *                 ProductRepository.toProduct(row.table(p)),
 *                 toOrderLine(row.table(linesAlias))
 *         ));
 * </pre>
 */
public class DatabaseJoinedQueryBuilder implements
        DatabaseQueryBuilder<DatabaseJoinedQueryBuilder>,
        DatabaseListableQueryBuilder<DatabaseJoinedQueryBuilder> {

    private final DatabaseTable table;
    private final DatabaseTableAlias tableAlias;
    private final List<JoinedTable> joinedTables = new ArrayList<>();
    private final DatabaseWhereBuilder whereBuilder = new DatabaseWhereBuilder();
    private final List<String> orderByClauses = new ArrayList<>();
    private Integer offset;
    private Integer rowCount;

    public DatabaseJoinedQueryBuilder(DatabaseTable table, DatabaseTableAlias tableAlias) {
        this.table = table;
        this.tableAlias = tableAlias;
    }

    /**
     * If you haven't called {@link #orderBy}, the results of {@link DatabaseListableQueryBuilder#list}
     * will be unpredictable. Call <code>unordered()</code> if you are okay with this.
     */
    @Override
    public DatabaseJoinedQueryBuilder unordered() {
        return this;
    }

    /**
     * Adds an <code>order by</code> clause to the query. Needed in order to list results
     * in a predictable order.
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder orderBy(@Nonnull DatabaseColumnReference column) {
        return orderBy(column.getQualifiedColumnName());
    }

    /**
     * Adds an <code>order by</code> clause to the query. Needed in order to list results
     * in a predictable order.
     */
    @Override
    public DatabaseJoinedQueryBuilder orderBy(String orderByClause) {
        orderByClauses.add(orderByClause);
        return this;
    }

    /**
     * Sets the <code>ORDER BY ...</code> clause of the <code>SELECT</code> statement
     */
    @Override
    public DatabaseJoinedQueryBuilder orderBy(List<String> orderByClauses) {
        this.orderByClauses.clear();
        this.orderByClauses.addAll(orderByClauses);
        return this;
    }

    /**
     * Adds <code>OFFSET ... ROWS FETCH ... ROWS ONLY</code> clause to the <code>SELECT</code>
     * statement. FETCH FIRST was introduced in
     * <a href="https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows">SQL:2008</a>
     * and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
     */
    public DatabaseJoinedQueryBuilder skipAndLimit(int offset, int rowCount) {
        this.offset = offset;
        this.rowCount = rowCount;
        return this;
    }

    /**
     * Adds the parameter to the WHERE-clause and all the parameter list.
     * E.g. <code>where(new DatabaseQueryParameter("created_at between ? and ?", List.of(earliestDate, latestDate)))</code>
     */
    @Override
    public DatabaseJoinedQueryBuilder where(DatabaseQueryParameter parameter) {
        whereBuilder.where(parameter);
        return this;
    }

    /**
     * Adds "<code>WHERE fieldName = value</code>" to the query
     */
    @Override
    public DatabaseJoinedQueryBuilder where(String fieldName, @Nullable Object value) {
        return whereExpression(tableAlias.getAlias() + "." + fieldName + " = ?", value);
    }

    /**
     * Implemented as <code>return this</code> for compatibility purposes
     */
    @Override
    public DatabaseJoinedQueryBuilder query() {
        return this;
    }

    /**
     * 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
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder join(DatabaseColumnReference a, DatabaseColumnReference b) {
        joinedTables.add(new JoinedTable(a, b, "inner join"));
        return this;
    }

    /**
     * Adds an additional table to the join as an inner join with the initial table. 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
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder join(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
        return join(tableAlias, leftFields, joinedTable, rightFields);
    }

    /**
     * Adds an additional table to the join as an inner join with the specified table. 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
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder join(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
        joinedTables.add(new JoinedTable(leftTable, leftFields, joinedTable, rightFields, "inner join"));
        return this;
    }

    /**
     * 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 {@link DatabaseRow#table(DatabaseTableAlias)} on
     * the resulting row, <code>null</code> is returned
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder leftJoin(DatabaseColumnReference a, DatabaseColumnReference b) {
        joinedTables.add(new JoinedTable(a, b, "left join"));
        return this;
    }

    /**
     * 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 {@link DatabaseRow#table(DatabaseTableAlias)} on
     * the resulting row, <code>null</code> is returned
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder leftJoin(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
        joinedTables.add(new JoinedTable(tableAlias, leftFields, joinedTable, rightFields, "left join"));
        return this;
    }

    /**
     * If the query returns no rows, returns {@link SingleRow#absent}, if exactly one row is returned, maps it and return it,
     * if more than one is returned, throws `IllegalStateException`
     *
     * @param connection Database connection
     * @param mapper     Function object to map a single returned row to a object
     * @return the mapped row if one row is returned, {@link SingleRow#absent} otherwise
     * @throws MultipleRowsReturnedException if more than one row was matched the query
     */
    @Nonnull
    @Override
    public <T> SingleRow<T> singleObject(Connection connection, DatabaseResult.RowMapper<T> mapper) {
        return query(connection, result -> result.single(
                mapper,
                () -> new NoRowsReturnedException(createSelectStatement(), whereBuilder.getParameters()),
                () -> new MultipleRowsReturnedException(createSelectStatement(), whereBuilder.getParameters())
        ));
    }

    /**
     * Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
     * <pre>
     *     table.where("status", status).stream(connection, row -&gt; row.table(joinedTable).getInstant("created_at"))
     * </pre>
     */
    @Override
    public <T> Stream<T> stream(@Nonnull Connection connection, DatabaseResult.RowMapper<T> mapper) {
        return list(connection, mapper).stream();
    }

    /**
     * Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a list. Example:
     * <pre>
     *     List&lt;Instant&gt; creationTimes = table.where("status", status)
     *          .list(connection, row -&gt; row.table(joinedTable).getInstant("created_at"))
     * </pre>
     */
    @Override
    public <T> List<T> list(Connection connection, DatabaseResult.RowMapper<T> mapper) {
        return query(connection, result -> result.list(mapper));
    }

    /**
     * Executes the <code>SELECT * FROM ...</code> statement and calls back to
     * {@link DatabaseResult.RowConsumer} for each returned row
     */
    @Override
    public void forEach(Connection connection, DatabaseResult.RowConsumer consumer) {
        query(connection, result -> {
            result.forEach(consumer);
            return null;
        });
    }

    /**
     * Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
     */
    @Override
    public int getCount(Connection connection) {
        String statement = "select count(*) as count " + fromClause() + whereBuilder.whereClause();
        return table.newStatement("COUNT", statement, whereBuilder.getParameters())
                .singleObject(connection, row -> row.getInt("count"))
                .orElseThrow();
    }

    /**
     * Executes the resulting <code>SELECT * FROM table ... INNER JOIN table ...</code> statement and
     * calculates column indexes based on {@link ResultSetMetaData}
     */
    @CheckReturnValue
    protected DatabaseResult createResult(@Nonnull PreparedStatement statement) throws SQLException {
        List<DatabaseTableAlias> aliases = new ArrayList<>();
        aliases.add(tableAlias);
        joinedTables.stream().map(JoinedTable::getAlias).forEach(aliases::add);

        Map<String, Integer> columnIndexes = new HashMap<>();
        Map<String, Map<String, Integer>> aliasColumnIndexes = new HashMap<>();
        aliases.forEach(t -> aliasColumnIndexes.put(t.getAlias().toUpperCase(), new HashMap<>()));
        int index = 0;

        ResultSet resultSet = statement.executeQuery();
        // Unfortunately, even though the database should know the alias for each table, JDBC doesn't reveal it
        ResultSetMetaData metaData = resultSet.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            while (!metaData.getTableName(i).equalsIgnoreCase(aliases.get(index).getTableName())) {
                index++;
                if (index == aliases.size()) {
                    throw new IllegalStateException("Failed to find table for column " + i + " (found " + aliasColumnIndexes + ") in " + aliases);
                }
            }
            String alias = aliases.get(index).getAlias().toUpperCase();
            String columnName = metaData.getColumnName(i).toUpperCase();
            if (aliasColumnIndexes.get(alias).containsKey(columnName)) {
                if (aliases.get(++index).getTableName().equalsIgnoreCase(metaData.getTableName(i))) {
                    alias = aliases.get(index).getAlias().toUpperCase();
                } else {
                    throw new IllegalStateException("Column twice in result " + alias + "." + columnName + ": " + aliasColumnIndexes);
                }
            }
            aliasColumnIndexes.get(alias).put(columnName, i);
            columnIndexes.putIfAbsent(columnName, i);
        }

        Map<DatabaseTableAlias, Integer> keys = new HashMap<>();
        for (JoinedTable table : joinedTables) {
            String tableAlias = table.joinedTable.getAlias().toUpperCase();
            String columnAlias = table.rightFields.get(0).toUpperCase();
            keys.put(table.joinedTable, aliasColumnIndexes.get(tableAlias).get(columnAlias));
        }
        return new DatabaseResult(statement, resultSet, columnIndexes, aliasColumnIndexes, keys);
    }

    @CheckReturnValue
    protected String createSelectStatement() {
        return "select *" + fromClause() + whereBuilder.whereClause() + orderByClause() + fetchClause();
    }

    @CheckReturnValue
    protected String fromClause() {
        return " from " + tableAlias.getTableNameAndAlias() + " " +
                joinedTables.stream().map(JoinedTable::toSql).collect(Collectors.joining(" "));
    }

    @CheckReturnValue
    protected String orderByClause() {
        return orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses);
    }

    @CheckReturnValue
    private String fetchClause() {
        return rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only";
    }

    private <T> T query(Connection connection, DatabaseResult.DatabaseResultMapper<T> resultMapper) {
        return createSelect().execute(connection, stmt -> {
            try (DatabaseResult result = createResult(stmt)) {
                return resultMapper.apply(result);
            }
        });
    }

    public DatabaseStatement createSelect() {
        return table.newStatement("SELECT", createSelectStatement(), whereBuilder.getParameters());
    }

    private static class JoinedTable {
        private final DatabaseTableAlias leftTable;
        private final List<String> leftFields;
        private final DatabaseTableAlias joinedTable;
        private final List<String> rightFields;
        private final String joinType;

        private JoinedTable(DatabaseColumnReference a, DatabaseColumnReference b, String joinType) {
            this(a.getTableAlias(), Collections.singletonList(a.getColumnName()), b.getTableAlias(), Collections.singletonList(b.getColumnName()), joinType);
        }

        public JoinedTable(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields, String joinType) {
            if (leftFields.size() != rightFields.size()) {
                throw new IllegalArgumentException("Column lists must have same length: " + leftFields + " != " + rightFields);
            }
            if (leftFields.isEmpty()) {
                throw new IllegalArgumentException("Must have at least one joined column");
            }
            this.leftTable = leftTable;
            this.leftFields = leftFields;
            this.joinedTable = joinedTable;
            this.rightFields = rightFields;
            this.joinType = joinType;
        }

        public String toSql() {
            List<String> joinParts = new ArrayList<>();
            for (int i = 0, leftFieldsSize = leftFields.size(); i < leftFieldsSize; i++) {
                joinParts.add(leftTable.getAlias() + "." + leftFields.get(i) + " = " + joinedTable.getAlias() + "." + rightFields.get(i));
            }

            return joinType + " " + joinedTable.getTableNameAndAlias()
                    + " on " + String.join(" and ", joinParts);
        }

        @Override
        public String toString() {
            return getClass().getSimpleName() + "[" + toSql() + "]";
        }

        DatabaseTableAlias getAlias() {
            return joinedTable;
        }
    }
}

1	package org.fluentjdbc;
2
3	import javax.annotation.CheckReturnValue;
4	import javax.annotation.Nonnull;
5	import javax.annotation.Nullable;
6	import java.sql.Connection;
7	import java.sql.PreparedStatement;
8	import java.sql.ResultSet;
9	import java.sql.ResultSetMetaData;
10	import java.sql.SQLException;
11	import java.util.ArrayList;
12	import java.util.Collections;
13	import java.util.HashMap;
14	import java.util.List;
15	import java.util.Map;
16	import java.util.stream.Collectors;
17	import java.util.stream.Stream;
18
19	/**
20	* {@link DatabaseQueryBuilder} used to generate joined queries using SQL-92 standard
21	* <code>SELECT * FROM table1 a JOIN table2 b ON a.column = b.column</code>. To specify
22	* columns for selection and tables for retrieval of columns, use {@link DatabaseTableAlias}
23	* and {@link DatabaseColumnReference}.
24	*
25	* <p><strong>Only works well on JDBC drivers that implement {@link ResultSetMetaData#getTableName(int)}</strong>,
26	* as this is used to calculate column indexes for aliased tables. This includes PostgreSQL, H2, HSQLDB, and SQLite,
27	* but not Oracle or SQL Server.</p>
28	*
29	* <p>Pull requests are welcome for a substitute for SQL Server and Oracle.</p>
30	*
31	* <h2>Usage example:</h2>
32	*
33	* <pre>
34	* DatabaseTableAlias p = productsTable.alias("p");
35	* DatabaseTableAlias o = ordersTable.alias("o");
36	* return context
37	* .join(linesAlias.column("product_id"), p.column("product_id"))
38	* .join(linesAlias.column("order_id"), o.column("order_id"))
39	* .list(connection, row -> new OrderLineEntity(
40	* OrderRepository.toOrder(row.table(o)),
41	* ProductRepository.toProduct(row.table(p)),
42	* toOrderLine(row.table(linesAlias))
43	* ));
44	* </pre>
45	*/
46	public class DatabaseJoinedQueryBuilder implements
47	DatabaseQueryBuilder<DatabaseJoinedQueryBuilder>,
48	DatabaseListableQueryBuilder<DatabaseJoinedQueryBuilder> {
49
50	private final DatabaseTable table;
51	private final DatabaseTableAlias tableAlias;
52	private final List<JoinedTable> joinedTables = new ArrayList<>();	1✔
53	private final DatabaseWhereBuilder whereBuilder = new DatabaseWhereBuilder();	1✔
54	private final List<String> orderByClauses = new ArrayList<>();	1✔
55	private Integer offset;
56	private Integer rowCount;
57
58	public DatabaseJoinedQueryBuilder(DatabaseTable table, DatabaseTableAlias tableAlias) {	1✔
59	this.table = table;	1✔
60	this.tableAlias = tableAlias;	1✔
61	}	1✔
62
63	/**
64	* If you haven't called {@link #orderBy}, the results of {@link DatabaseListableQueryBuilder#list}
65	* will be unpredictable. Call <code>unordered()</code> if you are okay with this.
66	*/
67	@Override
68	public DatabaseJoinedQueryBuilder unordered() {
69	return this;	1✔
70	}
71
72	/**
73	* Adds an <code>order by</code> clause to the query. Needed in order to list results
74	* in a predictable order.
75	*/
76	@CheckReturnValue
77	public DatabaseJoinedQueryBuilder orderBy(@Nonnull DatabaseColumnReference column) {
78	return orderBy(column.getQualifiedColumnName());	1✔
79	}
80
81	/**
82	* Adds an <code>order by</code> clause to the query. Needed in order to list results
83	* in a predictable order.
84	*/
85	@Override
86	public DatabaseJoinedQueryBuilder orderBy(String orderByClause) {
87	orderByClauses.add(orderByClause);	1✔
88	return this;	1✔
89	}
90
91	/**
92	* Sets the <code>ORDER BY ...</code> clause of the <code>SELECT</code> statement
93	*/
94	@Override
95	public DatabaseJoinedQueryBuilder orderBy(List<String> orderByClauses) {
NEW 96	this.orderByClauses.clear();	×
NEW 97	this.orderByClauses.addAll(orderByClauses);	×
NEW 98	return this;	×
99	}
100
101	/**
102	* Adds <code>OFFSET ... ROWS FETCH ... ROWS ONLY</code> clause to the <code>SELECT</code>
103	* statement. FETCH FIRST was introduced in
104	* <a href="https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows">SQL:2008</a>
105	* and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
106	*/
107	public DatabaseJoinedQueryBuilder skipAndLimit(int offset, int rowCount) {
108	this.offset = offset;	1✔
109	this.rowCount = rowCount;	1✔
110	return this;	1✔
111	}
112
113	/**
114	* Adds the parameter to the WHERE-clause and all the parameter list.
115	* E.g. <code>where(new DatabaseQueryParameter("created_at between ? and ?", List.of(earliestDate, latestDate)))</code>
116	*/
117	@Override
118	public DatabaseJoinedQueryBuilder where(DatabaseQueryParameter parameter) {
119	whereBuilder.where(parameter);	1✔
120	return this;	1✔
121	}
122
123	/**
124	* Adds "<code>WHERE fieldName = value</code>" to the query
125	*/
126	@Override
127	public DatabaseJoinedQueryBuilder where(String fieldName, @Nullable Object value) {
128	return whereExpression(tableAlias.getAlias() + "." + fieldName + " = ?", value);	1✔
129	}
130
131	/**
132	* Implemented as <code>return this</code> for compatibility purposes
133	*/
134	@Override
135	public DatabaseJoinedQueryBuilder query() {
136	return this;	1✔
137	}
138
139	/**
140	* Adds an additional table to the join as an inner join. Inner joins require a matching row
141	* in both tables and will leave out rows from one of the table where there is no corresponding
142	* table in the other
143	*/
144	@CheckReturnValue
145	public DatabaseJoinedQueryBuilder join(DatabaseColumnReference a, DatabaseColumnReference b) {
146	joinedTables.add(new JoinedTable(a, b, "inner join"));	1✔
147	return this;	1✔
148	}
149
150	/**
151	* Adds an additional table to the join as an inner join with the initial table. Inner joins require that
152	* all columns in both tables match and will leave out rows from one of the table where there is no corresponding
153	* table in the other
154	*/
155	@CheckReturnValue
156	public DatabaseJoinedQueryBuilder join(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
157	return join(tableAlias, leftFields, joinedTable, rightFields);	×
158	}
159
160	/**
161	* Adds an additional table to the join as an inner join with the specified table. Inner joins require that
162	* all columns in both tables match and will leave out rows from one of the table where there is no
163	* corresponding table in the other
164	*/
165	@CheckReturnValue
166	public DatabaseJoinedQueryBuilder join(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
167	joinedTables.add(new JoinedTable(leftTable, leftFields, joinedTable, rightFields, "inner join"));	1✔
168	return this;	1✔
169	}
170
171	/**
172	* Adds an additional table to the join as a left join. Left join only require a matching row in
173	* the first/left table. If there is no matching row in the second/right table, all columns are
174	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
175	* the resulting row, <code>null</code> is returned
176	*/
177	@CheckReturnValue
178	public DatabaseJoinedQueryBuilder leftJoin(DatabaseColumnReference a, DatabaseColumnReference b) {
179	joinedTables.add(new JoinedTable(a, b, "left join"));	1✔
180	return this;	1✔
181	}
182
183	/**
184	* Adds an additional table to the join as a left join. Left join only require a matching row in
185	* the first/left table. If there is no matching row in the second/right table, all columns are
186	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
187	* the resulting row, <code>null</code> is returned
188	*/
189	@CheckReturnValue
190	public DatabaseJoinedQueryBuilder leftJoin(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
191	joinedTables.add(new JoinedTable(tableAlias, leftFields, joinedTable, rightFields, "left join"));	×
192	return this;	×
193	}
194
195	/**
196	* If the query returns no rows, returns {@link SingleRow#absent}, if exactly one row is returned, maps it and return it,
197	* if more than one is returned, throws `IllegalStateException`
198	*
199	* @param connection Database connection
200	* @param mapper Function object to map a single returned row to a object
201	* @return the mapped row if one row is returned, {@link SingleRow#absent} otherwise
202	* @throws MultipleRowsReturnedException if more than one row was matched the query
203	*/
204	@Nonnull
205	@Override
206	public <T> SingleRow<T> singleObject(Connection connection, DatabaseResult.RowMapper<T> mapper) {
207	return query(connection, result -> result.single(	1✔
208	mapper,
209	() -> new NoRowsReturnedException(createSelectStatement(), whereBuilder.getParameters()),	×
210	() -> new MultipleRowsReturnedException(createSelectStatement(), whereBuilder.getParameters())	×
211	));
212	}
213
214	/**
215	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
216	* <pre>
217	* table.where("status", status).stream(connection, row -> row.table(joinedTable).getInstant("created_at"))
218	* </pre>
219	*/
220	@Override
221	public <T> Stream<T> stream(@Nonnull Connection connection, DatabaseResult.RowMapper<T> mapper) {
222	return list(connection, mapper).stream();	1✔
223	}
224
225	/**
226	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a list. Example:
227	* <pre>
228	* List<Instant> creationTimes = table.where("status", status)
229	* .list(connection, row -> row.table(joinedTable).getInstant("created_at"))
230	* </pre>
231	*/
232	@Override
233	public <T> List<T> list(Connection connection, DatabaseResult.RowMapper<T> mapper) {
234	return query(connection, result -> result.list(mapper));	1✔
235	}
236
237	/**
238	* Executes the <code>SELECT * FROM ...</code> statement and calls back to
239	* {@link DatabaseResult.RowConsumer} for each returned row
240	*/
241	@Override
242	public void forEach(Connection connection, DatabaseResult.RowConsumer consumer) {
243	query(connection, result -> {	1✔
244	result.forEach(consumer);	1✔
245	return null;	1✔
246	});
247	}	1✔
248
249	/**
250	* Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
251	*/
252	@Override
253	public int getCount(Connection connection) {
254	String statement = "select count(*) as count " + fromClause() + whereBuilder.whereClause();	1✔
255	return table.newStatement("COUNT", statement, whereBuilder.getParameters())	1✔
256	.singleObject(connection, row -> row.getInt("count"))	1✔
257	.orElseThrow();	1✔
258	}
259
260	/**
261	* Executes the resulting <code>SELECT * FROM table ... INNER JOIN table ...</code> statement and
262	* calculates column indexes based on {@link ResultSetMetaData}
263	*/
264	@CheckReturnValue
265	protected DatabaseResult createResult(@Nonnull PreparedStatement statement) throws SQLException {
266	List<DatabaseTableAlias> aliases = new ArrayList<>();	1✔
267	aliases.add(tableAlias);	1✔
268	joinedTables.stream().map(JoinedTable::getAlias).forEach(aliases::add);	1✔
269
270	Map<String, Integer> columnIndexes = new HashMap<>();	1✔
271	Map<String, Map<String, Integer>> aliasColumnIndexes = new HashMap<>();	1✔
272	aliases.forEach(t -> aliasColumnIndexes.put(t.getAlias().toUpperCase(), new HashMap<>()));	1✔
273	int index = 0;	1✔
274
275	ResultSet resultSet = statement.executeQuery();	1✔
276	// Unfortunately, even though the database should know the alias for each table, JDBC doesn't reveal it
277	ResultSetMetaData metaData = resultSet.getMetaData();	1✔
278	for (int i = 1; i <= metaData.getColumnCount(); i++) {	1✔
279	while (!metaData.getTableName(i).equalsIgnoreCase(aliases.get(index).getTableName())) {	1✔
280	index++;	1✔
281	if (index == aliases.size()) {	1✔
282	throw new IllegalStateException("Failed to find table for column " + i + " (found " + aliasColumnIndexes + ") in " + aliases);	×
283	}
284	}
285	String alias = aliases.get(index).getAlias().toUpperCase();	1✔
286	String columnName = metaData.getColumnName(i).toUpperCase();	1✔
287	if (aliasColumnIndexes.get(alias).containsKey(columnName)) {	1✔
288	if (aliases.get(++index).getTableName().equalsIgnoreCase(metaData.getTableName(i))) {	1✔
289	alias = aliases.get(index).getAlias().toUpperCase();	1✔
290	} else {
291	throw new IllegalStateException("Column twice in result " + alias + "." + columnName + ": " + aliasColumnIndexes);	×
292	}
293	}
294	aliasColumnIndexes.get(alias).put(columnName, i);	1✔
295	columnIndexes.putIfAbsent(columnName, i);	1✔
296	}
297
298	Map<DatabaseTableAlias, Integer> keys = new HashMap<>();	1✔
299	for (JoinedTable table : joinedTables) {	1✔
300	String tableAlias = table.joinedTable.getAlias().toUpperCase();	1✔
301	String columnAlias = table.rightFields.get(0).toUpperCase();	1✔
302	keys.put(table.joinedTable, aliasColumnIndexes.get(tableAlias).get(columnAlias));	1✔
303	}	1✔
304	return new DatabaseResult(statement, resultSet, columnIndexes, aliasColumnIndexes, keys);	1✔
305	}
306
307	@CheckReturnValue
308	protected String createSelectStatement() {
309	return "select *" + fromClause() + whereBuilder.whereClause() + orderByClause() + fetchClause();	1✔
310	}
311
312	@CheckReturnValue
313	protected String fromClause() {
314	return " from " + tableAlias.getTableNameAndAlias() + " " +	1✔
315	joinedTables.stream().map(JoinedTable::toSql).collect(Collectors.joining(" "));	1✔
316	}
317
318	@CheckReturnValue
319	protected String orderByClause() {
320	return orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses);	1✔
321	}
322
323	@CheckReturnValue
324	private String fetchClause() {
325	return rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only";	1✔
326	}
327
328	private <T> T query(Connection connection, DatabaseResult.DatabaseResultMapper<T> resultMapper) {
329	return createSelect().execute(connection, stmt -> {	1✔
330	try (DatabaseResult result = createResult(stmt)) {	1✔
331	return resultMapper.apply(result);	1✔
332	}
333	});
334	}
335
336	public DatabaseStatement createSelect() {
337	return table.newStatement("SELECT", createSelectStatement(), whereBuilder.getParameters());	1✔
338	}
339
340	private static class JoinedTable {
341	private final DatabaseTableAlias leftTable;
342	private final List<String> leftFields;
343	private final DatabaseTableAlias joinedTable;
344	private final List<String> rightFields;
345	private final String joinType;
346
347	private JoinedTable(DatabaseColumnReference a, DatabaseColumnReference b, String joinType) {
348	this(a.getTableAlias(), Collections.singletonList(a.getColumnName()), b.getTableAlias(), Collections.singletonList(b.getColumnName()), joinType);	1✔
349	}	1✔
350
351	public JoinedTable(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields, String joinType) {	1✔
352	if (leftFields.size() != rightFields.size()) {	1✔
353	throw new IllegalArgumentException("Column lists must have same length: " + leftFields + " != " + rightFields);	1✔
354	}
355	if (leftFields.isEmpty()) {	1✔
356	throw new IllegalArgumentException("Must have at least one joined column");	1✔
357	}
358	this.leftTable = leftTable;	1✔
359	this.leftFields = leftFields;	1✔
360	this.joinedTable = joinedTable;	1✔
361	this.rightFields = rightFields;	1✔
362	this.joinType = joinType;	1✔
363	}	1✔
364
365	public String toSql() {
366	List<String> joinParts = new ArrayList<>();	1✔
367	for (int i = 0, leftFieldsSize = leftFields.size(); i < leftFieldsSize; i++) {	1✔
368	joinParts.add(leftTable.getAlias() + "." + leftFields.get(i) + " = " + joinedTable.getAlias() + "." + rightFields.get(i));	1✔
369	}
370
371	return joinType + " " + joinedTable.getTableNameAndAlias()	1✔
372	+ " on " + String.join(" and ", joinParts);	1✔
373	}
374
375	@Override
376	public String toString() {
377	return getClass().getSimpleName() + "[" + toSql() + "]";	×
378	}
379
380	DatabaseTableAlias getAlias() {
381	return joinedTable;	1✔
382	}
383	}
384	}

jhannes / fluent-jdbc / #198

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous