#172

Committed 01 Aug 2024 07:53PM UTC coverage: 94.146% (-1.3%) from 95.478%

Build # #172

Build Type

push

Committed by

Commit Message

DatabaseUpdatable.setField with expression allows to insert and update rows with calculated column values

Run Details

41 of 45 new or added lines in 7 files covered. (91.11%)

32 existing lines in 12 files now uncovered.

1174 of 1247 relevant lines covered (94.15%)

0.94 hits per line

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

94.06

/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.Collection;
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 List<String> conditions = new ArrayList<>();
    private final List<Object> parameters = new ArrayList<>();
    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 "<code>WHERE fieldName = value</code>" to the query
     */
    @Override
    public DatabaseJoinedQueryBuilder where(String fieldName, @Nullable Object value) {
        return whereExpression(tableAlias.getAlias() + "." + fieldName + " = ?", value);
    }

    /**
     * Adds the expression to the WHERE-clause and all the values to the parameter list.
     * E.g. <code>whereExpressionWithParameterList("created_at between ? and ?", List.of(earliestDate, latestDate))</code>
     */
    @Override
    public DatabaseJoinedQueryBuilder whereExpressionWithParameterList(String expression, Collection<?> parameters) {
        conditions.add(expression);
        this.parameters.addAll(parameters);
        return this;
    }

    /**
     * Adds the expression to the WHERE-clause and all the values to the parameter list.
     * E.g. <code>whereColumnValues("json_column", "?::json", jsonString)</code>
     */
    @CheckReturnValue
    public DatabaseJoinedQueryBuilder whereColumnValuesEqual(String column, String expression, Collection<?> parameters) {
        conditions.add(column + " = " + expression);
        this.parameters.addAll(parameters);
        return this;
    }

    /**
     * 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, Optional.empty otherwise
     * @throws IllegalStateException if more than one row was matched the 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(), parameters)));
    }

    /**
     * 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 query = "select count(*) as count " + fromClause() + whereClause() + orderByClause();
        return table.newStatement("COUNT", query, parameters)
                .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 the 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() + whereClause() + orderByClause() + fetchClause();
    }

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

    @CheckReturnValue
    protected String whereClause() {
        return conditions.isEmpty() ? "" : " where " + String.join(" and ", conditions);
    }

    @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 table.newStatement("SELECT", createSelectStatement(), parameters).execute(connection, stmt -> {
            try (DatabaseResult result = createResult(stmt)) {
                return resultMapper.apply(result);
            }
        });
    }

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

jhannes / fluent-jdbc / #172

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