#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

85.19

/src/main/java/org/fluentjdbc/DbContextJoinedSelectBuilder.java

package org.fluentjdbc;

import javax.annotation.CheckReturnValue;
import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.stream.Stream;

/**
 * {@link DbContextSelectBuilder} 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 DbContextTableAlias}
 * 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>
 * {@link DbContextTable}Alias p = productsTable.alias("p");
 * DbContextTableAlias o = ordersTable.alias("o");
 * return context
 *         .join(linesAlias.column("product_id"), p.column("product_id"))
 *         .join(linesAlias.column("order_id"), o.column("order_id"))
 *         .list(row -&gt; new OrderLineEntity(
 *                 OrderRepository.toOrder(row.table(o)),
 *                 ProductRepository.toProduct(row.table(p)),
 *                 toOrderLine(row.table(linesAlias))
 *         ));
 * </pre>
 */
@CheckReturnValue
public class DbContextJoinedSelectBuilder implements DbContextListableSelect<DbContextJoinedSelectBuilder> {
    private final DatabaseJoinedQueryBuilder builder;
    private final DbContextTable table;

    public DbContextJoinedSelectBuilder(@Nonnull DbContextTable table, @Nonnull DbContextTableAlias tableAlias) {
        this.table = table;
        builder = new DatabaseJoinedQueryBuilder(table.getTable(), tableAlias.getTableAlias());
    }

    /**
     * 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
     */
    public DbContextJoinedSelectBuilder join(DatabaseColumnReference a, DatabaseColumnReference b) {
        return query(builder.join(a, b));
    }

    /**
     * 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
     */
    public DbContextJoinedSelectBuilder join(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
        return query(builder.join(leftFields, joinedTable.getTableAlias(), 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
     */
    public DbContextJoinedSelectBuilder join(DbContextTableAlias leftTable, List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
        return query(builder.join(leftTable.getTableAlias(), leftFields, joinedTable.getTableAlias(), rightFields));
    }

    /**
     * Adds an additional table to the join as a left join. Left join only require a matching row in
     * the first/left table. If there is no matching row in the second/right table, all columns are
     * returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
     * the resulting row, <code>null</code> is returned
     */
    public DbContextJoinedSelectBuilder leftJoin(DatabaseColumnReference a, DatabaseColumnReference b) {
        return query(builder.leftJoin(a, b));
    }

    /**
     * Adds an additional table to the join as a left join. Left join only require a matching row in
     * the first/left table. If there is no matching row in the second/right table, all columns are
     * returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
     * the resulting row, <code>null</code> is returned
     */
    public DbContextJoinedSelectBuilder leftJoin(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
        return query(builder.leftJoin(leftFields, joinedTable.getTableAlias(), rightFields));
    }

    /**
     * Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
     */
    @Override
    public int getCount() {
        return builder.getCount(getConnection());
    }

    /**
     * Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
     *
     * <pre>
     *     t.join(t.column("id"), o.column("parent_id"))
     *          .where("status", status)
     *          .stream(row -&gt; row.table(joinedTable).getInstant("created_at"))
     * </pre>
     */
    @Override
    public <OBJECT> Stream<OBJECT> stream(DatabaseResult.RowMapper<OBJECT> mapper) {
        return builder.stream(getConnection(), mapper);
    }

    /**
     * Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a list. Example:
     *
     * <pre>
     *     {@link DbContextTableAlias} t = table.alias("t");
     *     DbContextTableAlias o = otherTable.alias("o");
     *     List&lt;Instant&gt; creationTimes = t.join(t.column("id"), o.column("parent_id"))
     *          .where(t.column("name"), name)
     *          .list(row -&gt; row.table(o).getInstant("created_at"));
     * </pre>
     */
    @Override
    public <OBJECT> List<OBJECT> list(DatabaseResult.RowMapper<OBJECT> mapper) {
        return builder.list(getConnection(), mapper);
    }

    /**
     * Executes the <code>SELECT * FROM ...</code> statement and calls back to
     * {@link DatabaseResult.RowConsumer} for each returned row
     */
    @Override
    public void forEach(DatabaseResult.RowConsumer consumer) {
        builder.forEach(getConnection(), consumer);
    }

    /**
     * Adds the expression to the WHERE-clause and all the values to the parameter list.
     * E.g. <code>whereExpression("created_at between ? and ?", List.of(earliestDate, latestDate))</code>
     */
    public DbContextJoinedSelectBuilder whereExpressionWithParameterList(String expression, Collection<?> parameters){
        return query(builder.whereExpressionWithParameterList(expression, parameters));
    }

    /**
     * Adds "<code>WHERE fieldName = value</code>" to the query unless value is null
     */
    @Override
    public DbContextJoinedSelectBuilder whereOptional(String fieldName, @Nullable Object value) {
        return query(builder.whereOptional(fieldName, value));
    }

    /**
     * Adds "<code>WHERE fieldName = value</code>" to the query unless value is null
     */
    @Override
    public DbContextJoinedSelectBuilder whereOptional(DatabaseColumnReference column, @Nullable Object value) {
        return query(builder.whereOptional(column, value));
    }

    /**
     * For each field adds "<code>WHERE fieldName = value</code>" to the query
     */
    @Override
    public DbContextJoinedSelectBuilder whereAll(List<String> fields, List<Object> values) {
        return query(builder.whereAll(fields, values));
    }

    /**
     * For each key and value adds "<code>WHERE fieldName = value</code>" to the query
     */
    @Override
    public DbContextJoinedSelectBuilder whereAll(Map<String, ?> fields) {
        return query(builder.whereAll(fields));
    }

    /**
     * Returns or creates a query object to be used to add {@link #where(String, Object)} statements and operations
     */
    @Override
    public DbContextJoinedSelectBuilder query() {
        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 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
     */
    @Override
    @Nonnull
    public <OBJECT> SingleRow<OBJECT> singleObject(DatabaseResult.RowMapper<OBJECT> mapper) {
        return builder.singleObject(getConnection(), mapper);
    }

    /**
     * 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.
     */
    public DbContextJoinedSelectBuilder unordered() {
        return query(builder.unordered());
    }

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

    /**
     * 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 DbContextJoinedSelectBuilder skipAndLimit(int offset, int rowCount) {
        return query(builder.skipAndLimit(offset, rowCount));
    }

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

    private DbContextJoinedSelectBuilder query(@SuppressWarnings("unused") DatabaseJoinedQueryBuilder builder) {
        return this;
    }

    private Connection getConnection() {
        return table.getDbContext().getThreadConnection();
    }
}


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.ResultSetMetaData;
8	import java.util.Collection;
9	import java.util.List;
10	import java.util.Map;
11	import java.util.stream.Stream;
12
13	/**
14	* {@link DbContextSelectBuilder} used to generate joined queries using SQL-92 standard
15	* <code>SELECT * FROM table1 a JOIN table2 b ON a.column = b.column</code>. To specify
16	* columns for selection and tables for retrieval of columns, use {@link DbContextTableAlias}
17	* and {@link DatabaseColumnReference}.
18	*
19	* <p><strong>Only works well on JDBC drivers that implement {@link ResultSetMetaData#getTableName(int)}</strong>,
20	* as this is used to calculate column indexes for aliased tables. This includes PostgreSQL, H2, HSQLDB, and SQLite,
21	* but not Oracle or SQL Server.</p>
22	*
23	* <p>Pull requests are welcome for a substitute for SQL Server and Oracle.</p>
24	*
25	* <h2>Usage example:</h2>
26
27	* <pre>
28	* {@link DbContextTable}Alias p = productsTable.alias("p");
29	* DbContextTableAlias o = ordersTable.alias("o");
30	* return context
31	* .join(linesAlias.column("product_id"), p.column("product_id"))
32	* .join(linesAlias.column("order_id"), o.column("order_id"))
33	* .list(row -> new OrderLineEntity(
34	* OrderRepository.toOrder(row.table(o)),
35	* ProductRepository.toProduct(row.table(p)),
36	* toOrderLine(row.table(linesAlias))
37	* ));
38	* </pre>
39	*/
40	@CheckReturnValue
41	public class DbContextJoinedSelectBuilder implements DbContextListableSelect<DbContextJoinedSelectBuilder> {
42	private final DatabaseJoinedQueryBuilder builder;
43	private final DbContextTable table;
44
45	public DbContextJoinedSelectBuilder(@Nonnull DbContextTable table, @Nonnull DbContextTableAlias tableAlias) {	1✔
46	this.table = table;	1✔
47	builder = new DatabaseJoinedQueryBuilder(table.getTable(), tableAlias.getTableAlias());	1✔
48	}	1✔
49
50	/**
51	* Adds an additional table to the join as an inner join. Inner joins require a matching row
52	* in both tables and will leave out rows from one of the table where there is no corresponding
53	* table in the other
54	*/
55	public DbContextJoinedSelectBuilder join(DatabaseColumnReference a, DatabaseColumnReference b) {
56	return query(builder.join(a, b));	1✔
57	}
58
59	/**
60	* Adds an additional table to the join as an inner join with the initial table. Inner joins require that
61	* all columns in both tables match and will leave out rows from one of the table where there is no corresponding
62	* table in the other
63	*/
64	public DbContextJoinedSelectBuilder join(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
65	return query(builder.join(leftFields, joinedTable.getTableAlias(), rightFields));	×
66	}
67
68	/**
69	* Adds an additional table to the join as an inner join with the specified table. Inner joins require that
70	* all columns in both tables match and will leave out rows from one of the table where there is no
71	* corresponding table in the other
72	*/
73	public DbContextJoinedSelectBuilder join(DbContextTableAlias leftTable, List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
74	return query(builder.join(leftTable.getTableAlias(), leftFields, joinedTable.getTableAlias(), rightFields));	1✔
75	}
76
77	/**
78	* Adds an additional table to the join as a left join. Left join only require a matching row in
79	* the first/left table. If there is no matching row in the second/right table, all columns are
80	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
81	* the resulting row, <code>null</code> is returned
82	*/
83	public DbContextJoinedSelectBuilder leftJoin(DatabaseColumnReference a, DatabaseColumnReference b) {
84	return query(builder.leftJoin(a, b));	1✔
85	}
86
87	/**
88	* Adds an additional table to the join as a left join. Left join only require a matching row in
89	* the first/left table. If there is no matching row in the second/right table, all columns are
90	* returned as null in this table. When calling {@link DatabaseRow#table(DatabaseTableAlias)} on
91	* the resulting row, <code>null</code> is returned
92	*/
93	public DbContextJoinedSelectBuilder leftJoin(List<String> leftFields, DbContextTableAlias joinedTable, List<String> rightFields) {
94	return query(builder.leftJoin(leftFields, joinedTable.getTableAlias(), rightFields));	×
95	}
96
97	/**
98	* Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
99	*/
100	@Override
101	public int getCount() {
102	return builder.getCount(getConnection());	1✔
103	}
104
105	/**
106	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
107	*
108	* <pre>
109	* t.join(t.column("id"), o.column("parent_id"))
110	* .where("status", status)
111	* .stream(row -> row.table(joinedTable).getInstant("created_at"))
112	* </pre>
113	*/
114	@Override
115	public <OBJECT> Stream<OBJECT> stream(DatabaseResult.RowMapper<OBJECT> mapper) {
116	return builder.stream(getConnection(), mapper);	1✔
117	}
118
119	/**
120	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a list. Example:
121	*
122	* <pre>
123	* {@link DbContextTableAlias} t = table.alias("t");
124	* DbContextTableAlias o = otherTable.alias("o");
125	* List<Instant> creationTimes = t.join(t.column("id"), o.column("parent_id"))
126	* .where(t.column("name"), name)
127	* .list(row -> row.table(o).getInstant("created_at"));
128	* </pre>
129	*/
130	@Override
131	public <OBJECT> List<OBJECT> list(DatabaseResult.RowMapper<OBJECT> mapper) {
132	return builder.list(getConnection(), mapper);	1✔
133	}
134
135	/**
136	* Executes the <code>SELECT * FROM ...</code> statement and calls back to
137	* {@link DatabaseResult.RowConsumer} for each returned row
138	*/
139	@Override
140	public void forEach(DatabaseResult.RowConsumer consumer) {
141	builder.forEach(getConnection(), consumer);	1✔
142	}	1✔
143
144	/**
145	* Adds the expression to the WHERE-clause and all the values to the parameter list.
146	* E.g. <code>whereExpression("created_at between ? and ?", List.of(earliestDate, latestDate))</code>
147	*/
148	public DbContextJoinedSelectBuilder whereExpressionWithParameterList(String expression, Collection<?> parameters){
149	return query(builder.whereExpressionWithParameterList(expression, parameters));	1✔
150	}
151
152	/**
153	* Adds "<code>WHERE fieldName = value</code>" to the query unless value is null
154	*/
155	@Override
156	public DbContextJoinedSelectBuilder whereOptional(String fieldName, @Nullable Object value) {
157	return query(builder.whereOptional(fieldName, value));	×
158	}
159
160	/**
161	* Adds "<code>WHERE fieldName = value</code>" to the query unless value is null
162	*/
163	@Override
164	public DbContextJoinedSelectBuilder whereOptional(DatabaseColumnReference column, @Nullable Object value) {
165	return query(builder.whereOptional(column, value));	1✔
166	}
167
168	/**
169	* For each field adds "<code>WHERE fieldName = value</code>" to the query
170	*/
171	@Override
172	public DbContextJoinedSelectBuilder whereAll(List<String> fields, List<Object> values) {
173	return query(builder.whereAll(fields, values));	1✔
174	}
175
176	/**
177	* For each key and value adds "<code>WHERE fieldName = value</code>" to the query
178	*/
179	@Override
180	public DbContextJoinedSelectBuilder whereAll(Map<String, ?> fields) {
UNCOV 181	return query(builder.whereAll(fields));	×
182	}
183
184	/**
185	* Returns or creates a query object to be used to add {@link #where(String, Object)} statements and operations
186	*/
187	@Override
188	public DbContextJoinedSelectBuilder query() {
189	return this;	1✔
190	}
191
192	/**
193	* If the query returns no rows, returns {@link SingleRow#absent}, if exactly one row is returned, maps it and return it,
194	* if more than one is returned, throws `IllegalStateException`
195	*
196	* @param mapper Function object to map a single returned row to a object
197	* @return the mapped row if one row is returned, Optional.empty otherwise
198	* @throws IllegalStateException if more than one row was matched the the query
199	*/
200	@Override
201	@Nonnull
202	public <OBJECT> SingleRow<OBJECT> singleObject(DatabaseResult.RowMapper<OBJECT> mapper) {
203	return builder.singleObject(getConnection(), mapper);	1✔
204	}
205
206	/**
207	* If you haven't called {@link #orderBy}, the results of {@link DatabaseListableQueryBuilder#list}
208	* will be unpredictable. Call <code>unordered()</code> if you are okay with this.
209	*/
210	public DbContextJoinedSelectBuilder unordered() {
211	return query(builder.unordered());	1✔
212	}
213
214	/**
215	* Adds an <code>order by</code> clause to the query. Needed in order to list results
216	* in a predictable order.
217	*/
218	@Override
219	public DbContextJoinedSelectBuilder orderBy(String orderByClause) {
220	return query(builder.orderBy(orderByClause));	1✔
221	}
222
223	/**
224	* Adds <code>OFFSET ... ROWS FETCH ... ROWS ONLY</code> clause to the <code>SELECT</code>
225	* statement. FETCH FIRST was introduced in
226	* <a href="https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows">SQL:2008</a>
227	* and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
228	*/
229	@Override
230	public DbContextJoinedSelectBuilder skipAndLimit(int offset, int rowCount) {
231	return query(builder.skipAndLimit(offset, rowCount));	1✔
232	}
233
234	/**
235	* Adds an <code>order by</code> clause to the query. Needed in order to list results
236	* in a predictable order.
237	*/
238	public DbContextJoinedSelectBuilder orderBy(DatabaseColumnReference column) {
239	return orderBy(column.getQualifiedColumnName());	1✔
240	}
241
242	private DbContextJoinedSelectBuilder query(@SuppressWarnings("unused") DatabaseJoinedQueryBuilder builder) {
243	return this;	1✔
244	}
245
246	private Connection getConnection() {
247	return table.getDbContext().getThreadConnection();	1✔
248	}
249	}
250

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