#190

Committed 01 Sep 2025 11:48AM UTC coverage: 92.296% (-0.2%) from 92.457%

Build # #190

Build Type

push

Committed by

Commit Message

Introduce DataQueryParameter for more extensible queries

Run Details

72 of 85 new or added lines in 18 files covered. (84.71%)

1 existing line in 1 file now uncovered.

1186 of 1285 relevant lines covered (92.3%)

0.92 hits per line

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

91.84

/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;
    }

    /**
     * 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.
     */
    @Override
    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	* Adds <code>OFFSET ... ROWS FETCH ... ROWS ONLY</code> clause to the <code>SELECT</code>
93	* statement. FETCH FIRST was introduced in
94	* <a href="https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows">SQL:2008</a>
95	* and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
96	*/
97	@Override
98	public DatabaseJoinedQueryBuilder skipAndLimit(int offset, int rowCount) {
99	this.offset = offset;	1✔
100	this.rowCount = rowCount;	1✔
101	return this;	1✔
102	}
103
104	/**
105	* Adds the parameter to the WHERE-clause and all the parameter list.
106	* E.g. <code>where(new DatabaseQueryParameter("created_at between ? and ?", List.of(earliestDate, latestDate)))</code>
107	*/
108	@Override
109	public DatabaseJoinedQueryBuilder where(DatabaseQueryParameter parameter) {
110	whereBuilder.where(parameter);	1✔
111	return this;	1✔
112	}
113
114	/**
115	* Adds "<code>WHERE fieldName = value</code>" to the query
116	*/
117	@Override
118	public DatabaseJoinedQueryBuilder where(String fieldName, @Nullable Object value) {
119	return whereExpression(tableAlias.getAlias() + "." + fieldName + " = ?", value);	1✔
120	}
121
122	/**
123	* Implemented as <code>return this</code> for compatibility purposes
124	*/
125	@Override
126	public DatabaseJoinedQueryBuilder query() {
127	return this;	1✔
128	}
129
130	/**
131	* Adds an additional table to the join as an inner join. Inner joins require a matching row
132	* in both tables and will leave out rows from one of the table where there is no corresponding
133	* table in the other
134	*/
135	@CheckReturnValue
136	public DatabaseJoinedQueryBuilder join(DatabaseColumnReference a, DatabaseColumnReference b) {
137	joinedTables.add(new JoinedTable(a, b, "inner join"));	1✔
138	return this;	1✔
139	}
140
141	/**
142	* Adds an additional table to the join as an inner join with the initial table. Inner joins require that
143	* all columns in both tables match and will leave out rows from one of the table where there is no corresponding
144	* table in the other
145	*/
146	@CheckReturnValue
147	public DatabaseJoinedQueryBuilder join(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
148	return join(tableAlias, leftFields, joinedTable, rightFields);	×
149	}
150
151	/**
152	* Adds an additional table to the join as an inner join with the specified table. Inner joins require that
153	* all columns in both tables match and will leave out rows from one of the table where there is no
154	* corresponding table in the other
155	*/
156	@CheckReturnValue
157	public DatabaseJoinedQueryBuilder join(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
158	joinedTables.add(new JoinedTable(leftTable, leftFields, joinedTable, rightFields, "inner join"));	1✔
159	return this;	1✔
160	}
161
162	/**
163	* Adds an additional table to the join as a left join. Left join only require a matching row in
164	* the first/left table. If there is no matching row in the second/right table, all columns are
165	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
166	* the resulting row, <code>null</code> is returned
167	*/
168	@CheckReturnValue
169	public DatabaseJoinedQueryBuilder leftJoin(DatabaseColumnReference a, DatabaseColumnReference b) {
170	joinedTables.add(new JoinedTable(a, b, "left join"));	1✔
171	return this;	1✔
172	}
173
174	/**
175	* Adds an additional table to the join as a left join. Left join only require a matching row in
176	* the first/left table. If there is no matching row in the second/right table, all columns are
177	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
178	* the resulting row, <code>null</code> is returned
179	*/
180	@CheckReturnValue
181	public DatabaseJoinedQueryBuilder leftJoin(List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields) {
182	joinedTables.add(new JoinedTable(tableAlias, leftFields, joinedTable, rightFields, "left join"));	×
183	return this;	×
184	}
185
186	/**
187	* If the query returns no rows, returns {@link SingleRow#absent}, if exactly one row is returned, maps it and return it,
188	* if more than one is returned, throws `IllegalStateException`
189	*
190	* @param connection Database connection
191	* @param mapper Function object to map a single returned row to a object
192	* @return the mapped row if one row is returned, {@link SingleRow#absent} otherwise
193	* @throws MultipleRowsReturnedException if more than one row was matched the query
194	*/
195	@Nonnull
196	@Override
197	public <T> SingleRow<T> singleObject(Connection connection, DatabaseResult.RowMapper<T> mapper) {
198	return query(connection, result -> result.single(	1✔
199	mapper,
NEW 200	() -> new NoRowsReturnedException(createSelectStatement(), whereBuilder.getParameters()),	×
NEW 201	() -> new MultipleRowsReturnedException(createSelectStatement(), whereBuilder.getParameters())	×
202	));
203	}
204
205	/**
206	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
207	* <pre>
208	* table.where("status", status).stream(connection, row -> row.table(joinedTable).getInstant("created_at"))
209	* </pre>
210	*/
211	@Override
212	public <T> Stream<T> stream(@Nonnull Connection connection, DatabaseResult.RowMapper<T> mapper) {
213	return list(connection, mapper).stream();	1✔
214	}
215
216	/**
217	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a list. Example:
218	* <pre>
219	* List<Instant> creationTimes = table.where("status", status)
220	* .list(connection, row -> row.table(joinedTable).getInstant("created_at"))
221	* </pre>
222	*/
223	@Override
224	public <T> List<T> list(Connection connection, DatabaseResult.RowMapper<T> mapper) {
225	return query(connection, result -> result.list(mapper));	1✔
226	}
227
228	/**
229	* Executes the <code>SELECT * FROM ...</code> statement and calls back to
230	* {@link DatabaseResult.RowConsumer} for each returned row
231	*/
232	@Override
233	public void forEach(Connection connection, DatabaseResult.RowConsumer consumer) {
234	query(connection, result -> {	1✔
235	result.forEach(consumer);	1✔
236	return null;	1✔
237	});
238	}	1✔
239
240	/**
241	* Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
242	*/
243	@Override
244	public int getCount(Connection connection) {
245	String statement = "select count(*) as count " + fromClause() + whereBuilder.whereClause();	1✔
246	return table.newStatement("COUNT", statement, whereBuilder.getParameters())	1✔
247	.singleObject(connection, row -> row.getInt("count"))	1✔
248	.orElseThrow();	1✔
249	}
250
251	/**
252	* Executes the resulting <code>SELECT * FROM table ... INNER JOIN table ...</code> statement and
253	* calculates column indexes based on {@link ResultSetMetaData}
254	*/
255	@CheckReturnValue
256	protected DatabaseResult createResult(@Nonnull PreparedStatement statement) throws SQLException {
257	List<DatabaseTableAlias> aliases = new ArrayList<>();	1✔
258	aliases.add(tableAlias);	1✔
259	joinedTables.stream().map(JoinedTable::getAlias).forEach(aliases::add);	1✔
260
261	Map<String, Integer> columnIndexes = new HashMap<>();	1✔
262	Map<String, Map<String, Integer>> aliasColumnIndexes = new HashMap<>();	1✔
263	aliases.forEach(t -> aliasColumnIndexes.put(t.getAlias().toUpperCase(), new HashMap<>()));	1✔
264	int index = 0;	1✔
265
266	ResultSet resultSet = statement.executeQuery();	1✔
267	// Unfortunately, even though the database should know the alias for each table, JDBC doesn't reveal it
268	ResultSetMetaData metaData = resultSet.getMetaData();	1✔
269	for (int i = 1; i <= metaData.getColumnCount(); i++) {	1✔
270	while (!metaData.getTableName(i).equalsIgnoreCase(aliases.get(index).getTableName())) {	1✔
271	index++;	1✔
272	if (index == aliases.size()) {	1✔
273	throw new IllegalStateException("Failed to find table for column " + i + " (found " + aliasColumnIndexes + ") in " + aliases);	×
274	}
275	}
276	String alias = aliases.get(index).getAlias().toUpperCase();	1✔
277	String columnName = metaData.getColumnName(i).toUpperCase();	1✔
278	if (aliasColumnIndexes.get(alias).containsKey(columnName)) {	1✔
279	if (aliases.get(++index).getTableName().equalsIgnoreCase(metaData.getTableName(i))) {	1✔
280	alias = aliases.get(index).getAlias().toUpperCase();	1✔
281	} else {
282	throw new IllegalStateException("Column twice in result " + alias + "." + columnName + ": " + aliasColumnIndexes);	×
283	}
284	}
285	aliasColumnIndexes.get(alias).put(columnName, i);	1✔
286	columnIndexes.putIfAbsent(columnName, i);	1✔
287	}
288
289	Map<DatabaseTableAlias, Integer> keys = new HashMap<>();	1✔
290	for (JoinedTable table : joinedTables) {	1✔
291	String tableAlias = table.joinedTable.getAlias().toUpperCase();	1✔
292	String columnAlias = table.rightFields.get(0).toUpperCase();	1✔
293	keys.put(table.joinedTable, aliasColumnIndexes.get(tableAlias).get(columnAlias));	1✔
294	}	1✔
295	return new DatabaseResult(statement, resultSet, columnIndexes, aliasColumnIndexes, keys);	1✔
296	}
297
298	@CheckReturnValue
299	protected String createSelectStatement() {
300	return "select *" + fromClause() + whereBuilder.whereClause() + orderByClause() + fetchClause();	1✔
301	}
302
303	@CheckReturnValue
304	protected String fromClause() {
305	return " from " + tableAlias.getTableNameAndAlias() + " " +	1✔
306	joinedTables.stream().map(JoinedTable::toSql).collect(Collectors.joining(" "));	1✔
307	}
308
309	@CheckReturnValue
310	protected String orderByClause() {
311	return orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses);	1✔
312	}
313
314	@CheckReturnValue
315	private String fetchClause() {
316	return rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only";	1✔
317	}
318
319	private <T> T query(Connection connection, DatabaseResult.DatabaseResultMapper<T> resultMapper) {
320	return createSelect().execute(connection, stmt -> {	1✔
321	try (DatabaseResult result = createResult(stmt)) {	1✔
322	return resultMapper.apply(result);	1✔
323	}
324	});
325	}
326
327	public DatabaseStatement createSelect() {
328	return table.newStatement("SELECT", createSelectStatement(), whereBuilder.getParameters());	1✔
329	}
330
331	private static class JoinedTable {
332	private final DatabaseTableAlias leftTable;
333	private final List<String> leftFields;
334	private final DatabaseTableAlias joinedTable;
335	private final List<String> rightFields;
336	private final String joinType;
337
338	private JoinedTable(DatabaseColumnReference a, DatabaseColumnReference b, String joinType) {
339	this(a.getTableAlias(), Collections.singletonList(a.getColumnName()), b.getTableAlias(), Collections.singletonList(b.getColumnName()), joinType);	1✔
340	}	1✔
341
342	public JoinedTable(DatabaseTableAlias leftTable, List<String> leftFields, DatabaseTableAlias joinedTable, List<String> rightFields, String joinType) {	1✔
343	if (leftFields.size() != rightFields.size()) {	1✔
344	throw new IllegalArgumentException("Column lists must have same length: " + leftFields + " != " + rightFields);	1✔
345	}
346	if (leftFields.isEmpty()) {	1✔
347	throw new IllegalArgumentException("Must have at least one joined column");	1✔
348	}
349	this.leftTable = leftTable;	1✔
350	this.leftFields = leftFields;	1✔
351	this.joinedTable = joinedTable;	1✔
352	this.rightFields = rightFields;	1✔
353	this.joinType = joinType;	1✔
354	}	1✔
355
356	public String toSql() {
357	List<String> joinParts = new ArrayList<>();	1✔
358	for (int i = 0, leftFieldsSize = leftFields.size(); i < leftFieldsSize; i++) {	1✔
359	joinParts.add(leftTable.getAlias() + "." + leftFields.get(i) + " = " + joinedTable.getAlias() + "." + rightFields.get(i));	1✔
360	}
361
362	return joinType + " " + joinedTable.getTableNameAndAlias()	1✔
363	+ " on " + String.join(" and ", joinParts);	1✔
364	}
365
366	@Override
367	public String toString() {
368	return getClass().getSimpleName() + "[" + toSql() + "]";	×
369	}
370
371	DatabaseTableAlias getAlias() {
372	return joinedTable;	1✔
373	}
374	}
375	}

jhannes / fluent-jdbc / #190

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