#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

87.5

/src/main/java/org/fluentjdbc/DatabaseSqlBuilder.java

package org.fluentjdbc;

import javax.annotation.CheckReturnValue;
import javax.annotation.Nonnull;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.stream.Stream;

/**
 * Used to construct SQL SELECT statements in a flexible way with {@link #where(String, Object)}
 * clauses, {@link #select(String...)} column names, {@link #from(String)} table statement,
 * {@link #groupBy(String...)}, {@link #orderBy(String)} and {@link #skipAndLimit(int, int)}.
 *
 * <h2>Example:</h2>
 *
 * <pre>
 * new DatabaseSqlBuilder()
 *      .select("city, avg(age) as average_age")
 *      .from("person")
 *      .groupBy("city")
 *      .order("avg(age) desc")
 *      .limit(1)
 *      .singleString(connection, "city");
 * </pre>
 *
 */
public class DatabaseSqlBuilder implements DatabaseQueryBuilder<DatabaseSqlBuilder>, DatabaseListableQueryBuilder<DatabaseSqlBuilder> {

    private final ArrayList<String> columns = new ArrayList<>();
    private final DatabaseStatementFactory factory;
    private String fromStatement;
    private final ArrayList<String> groupByClauses = new ArrayList<>();
    private final ArrayList<String> orderByClauses = new ArrayList<>();
    private Integer offset;
    private Integer rowCount;
    private DatabaseWhereBuilder whereBuilder = new DatabaseWhereBuilder();

    public DatabaseSqlBuilder(DatabaseStatementFactory factory) {
        this.factory = factory;
    }

    /**
     * Add the arguments to the column list for the <code>SELECT column, column...</code> statement
     */
    public DatabaseSqlBuilder select(String... columns) {
        this.columns.addAll(Arrays.asList(columns));
        return this;
    }

    /**
     * Replace the "from" part of the <code>SELECT ... FROM fromStatement</code> in the select statement
     */
    public DatabaseSqlBuilder from(String fromStatement) {
        this.fromStatement = fromStatement;
        return this;
    }

    /**
     * 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 DatabaseSqlBuilder whereExpressionWithParameterList(String expression, Collection<?> parameters) {
        whereBuilder = whereBuilder.whereExpressionWithParameterList(expression, 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 DatabaseSqlBuilder whereColumnValuesEqual(String column, String expression, Collection<?> parameters) {
        whereBuilder = whereBuilder.whereColumnValuesEqual(column, expression, parameters);
        return this;
    }

    /**
     * Add the arguments to the column list for the <code>SELECT column, column...</code> statement
     */
    public DatabaseSqlBuilder groupBy(String... groupByStatement) {
        groupByClauses.addAll(Arrays.asList(groupByStatement));
        return this;
    }

    /**
     * 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 DatabaseSqlBuilder unordered() {
        return this;
    }

    /**
     * Adds <code>ORDER BY ...</code> clause to the <code>SELECT</code> statement
     */
    @Override
    public DatabaseSqlBuilder 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 DatabaseSqlBuilder skipAndLimit(int offset, int rowCount) {
        this.offset = offset;
        this.rowCount = rowCount;
        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 an object
     * @return the mapped row if one row is returned, {@link SingleRow#absent} otherwise
     * @throws IllegalStateException if more than one row was matched the query
     */
    @Nonnull
    @Override
    public <OBJECT> SingleRow<OBJECT> singleObject(Connection connection, DatabaseResult.RowMapper<OBJECT> mapper) {
        return getDatabaseStatement().singleObject(connection, mapper);
    }

    /**
     * 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(row -&gt; row.getInstant("created_at"))
     * </pre>
     */
    @Override
    public <OBJECT> Stream<OBJECT> stream(@Nonnull Connection connection, DatabaseResult.RowMapper<OBJECT> mapper) {
        return getDatabaseStatement().stream(connection, 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) {
        getDatabaseStatement().forEach(connection, consumer);
    }

    /**
     * Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
     */
    @Override
    public int getCount(Connection connection) {
        String selectStatement = "select count(*) as count "
                + (" from " + fromStatement)
                + whereBuilder.whereClause()
                + (groupByClauses.isEmpty() ? "" : " group by " + String.join(", ", groupByClauses));
        return factory.newStatement("*", "COUNT", selectStatement, whereBuilder.getParameters())
                .singleObject(connection, row -> row.getInt("count"))
                .orElseThrow();
    }

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

    @Nonnull
    private DatabaseStatement getDatabaseStatement() {
        return factory.newStatement("*", "SELECT", createSelectStatement(String.join(", ", columns)), whereBuilder.getParameters());
    }

    private String createSelectStatement(String columns) {
        return "select " + columns
                + (" from " + fromStatement)
                + whereBuilder.whereClause()
                + (groupByClauses.isEmpty() ? "" : " group by " + String.join(", ", groupByClauses))
                + (orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses))
                + (rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only");
    }
}

1	package org.fluentjdbc;
2
3	import javax.annotation.CheckReturnValue;
4	import javax.annotation.Nonnull;
5	import java.sql.Connection;
6	import java.util.ArrayList;
7	import java.util.Arrays;
8	import java.util.Collection;
9	import java.util.stream.Stream;
10
11	/**
12	* Used to construct SQL SELECT statements in a flexible way with {@link #where(String, Object)}
13	* clauses, {@link #select(String...)} column names, {@link #from(String)} table statement,
14	* {@link #groupBy(String...)}, {@link #orderBy(String)} and {@link #skipAndLimit(int, int)}.
15	*
16	* <h2>Example:</h2>
17	*
18	* <pre>
19	* new DatabaseSqlBuilder()
20	* .select("city, avg(age) as average_age")
21	* .from("person")
22	* .groupBy("city")
23	* .order("avg(age) desc")
24	* .limit(1)
25	* .singleString(connection, "city");
26	* </pre>
27	*
28	*/
29	public class DatabaseSqlBuilder implements DatabaseQueryBuilder<DatabaseSqlBuilder>, DatabaseListableQueryBuilder<DatabaseSqlBuilder> {
30
31	private final ArrayList<String> columns = new ArrayList<>();	1✔
32	private final DatabaseStatementFactory factory;
33	private String fromStatement;
34	private final ArrayList<String> groupByClauses = new ArrayList<>();	1✔
35	private final ArrayList<String> orderByClauses = new ArrayList<>();	1✔
36	private Integer offset;
37	private Integer rowCount;
38	private DatabaseWhereBuilder whereBuilder = new DatabaseWhereBuilder();	1✔
39
40	public DatabaseSqlBuilder(DatabaseStatementFactory factory) {	1✔
41	this.factory = factory;	1✔
42	}	1✔
43
44	/**
45	* Add the arguments to the column list for the <code>SELECT column, column...</code> statement
46	*/
47	public DatabaseSqlBuilder select(String... columns) {
48	this.columns.addAll(Arrays.asList(columns));	1✔
49	return this;	1✔
50	}
51
52	/**
53	* Replace the "from" part of the <code>SELECT ... FROM fromStatement</code> in the select statement
54	*/
55	public DatabaseSqlBuilder from(String fromStatement) {
56	this.fromStatement = fromStatement;	1✔
57	return this;	1✔
58	}
59
60	/**
61	* Adds the expression to the WHERE-clause and all the values to the parameter list.
62	* E.g. <code>whereExpressionWithParameterList("created_at between ? and ?", List.of(earliestDate, latestDate))</code>
63	*/
64	@Override
65	public DatabaseSqlBuilder whereExpressionWithParameterList(String expression, Collection<?> parameters) {
UNCOV 66	whereBuilder = whereBuilder.whereExpressionWithParameterList(expression, parameters);	×
UNCOV 67	return this;	×
68	}
69
70	/**
71	* Adds the expression to the WHERE-clause and all the values to the parameter list.
72	* E.g. <code>whereColumnValues("json_column", "?::json", jsonString)</code>
73	*/
74	@CheckReturnValue
75	public DatabaseSqlBuilder whereColumnValuesEqual(String column, String expression, Collection<?> parameters) {
76	whereBuilder = whereBuilder.whereColumnValuesEqual(column, expression, parameters);	1✔
77	return this;	1✔
78	}
79
80	/**
81	* Add the arguments to the column list for the <code>SELECT column, column...</code> statement
82	*/
83	public DatabaseSqlBuilder groupBy(String... groupByStatement) {
84	groupByClauses.addAll(Arrays.asList(groupByStatement));	1✔
85	return this;	1✔
86	}
87
88	/**
89	* If you haven't called {@link #orderBy}, the results of {@link DatabaseListableQueryBuilder#list}
90	* will be unpredictable. Call <code>unordered()</code> if you are okay with this.
91	*/
92	@Override
93	public DatabaseSqlBuilder unordered() {
94	return this;	1✔
95	}
96
97	/**
98	* Adds <code>ORDER BY ...</code> clause to the <code>SELECT</code> statement
99	*/
100	@Override
101	public DatabaseSqlBuilder orderBy(String orderByClause) {
102	orderByClauses.add(orderByClause);	1✔
103	return this;	1✔
104	}
105
106	/**
107	* Adds <code>OFFSET ... ROWS FETCH ... ROWS ONLY</code> clause to the <code>SELECT</code>
108	* statement. FETCH FIRST was introduced in
109	* <a href="https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows">SQL:2008</a>
110	* and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
111	*/
112	@Override
113	public DatabaseSqlBuilder skipAndLimit(int offset, int rowCount) {
114	this.offset = offset;	1✔
115	this.rowCount = rowCount;	1✔
116	return this;	1✔
117	}
118
119	/**
120	* If the query returns no rows, returns {@link SingleRow#absent}, if exactly one row is returned, maps it and return it,
121	* if more than one is returned, throws `IllegalStateException`
122	*
123	* @param mapper Function object to map a single returned row to an object
124	* @return the mapped row if one row is returned, {@link SingleRow#absent} otherwise
125	* @throws IllegalStateException if more than one row was matched the query
126	*/
127	@Nonnull
128	@Override
129	public <OBJECT> SingleRow<OBJECT> singleObject(Connection connection, DatabaseResult.RowMapper<OBJECT> mapper) {
130	return getDatabaseStatement().singleObject(connection, mapper);	1✔
131	}
132
133	/**
134	* Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
135	* <pre>
136	* table.where("status", status).stream(row -> row.getInstant("created_at"))
137	* </pre>
138	*/
139	@Override
140	public <OBJECT> Stream<OBJECT> stream(@Nonnull Connection connection, DatabaseResult.RowMapper<OBJECT> mapper) {
141	return getDatabaseStatement().stream(connection, mapper);	1✔
142	}
143
144	/**
145	* Executes the <code>SELECT * FROM ...</code> statement and calls back to
146	* {@link DatabaseResult.RowConsumer} for each returned row
147	*/
148	@Override
149	public void forEach(Connection connection, DatabaseResult.RowConsumer consumer) {
UNCOV 150	getDatabaseStatement().forEach(connection, consumer);	×
UNCOV 151	}	×
152
153	/**
154	* Executes <code>SELECT count(*) FROM ...</code> on the query and returns the result
155	*/
156	@Override
157	public int getCount(Connection connection) {
158	String selectStatement = "select count(*) as count "	1✔
159	+ (" from " + fromStatement)
160	+ whereBuilder.whereClause()	1✔
161	+ (groupByClauses.isEmpty() ? "" : " group by " + String.join(", ", groupByClauses));	1✔
162	return factory.newStatement("*", "COUNT", selectStatement, whereBuilder.getParameters())	1✔
163	.singleObject(connection, row -> row.getInt("count"))	1✔
164	.orElseThrow();	1✔
165	}
166
167	/**
168	* Implemented as <code>return this</code> for compatibility purposes
169	*/
170	@Override
171	public DatabaseSqlBuilder query() {
UNCOV 172	return this;	×
173	}
174
175	@Nonnull
176	private DatabaseStatement getDatabaseStatement() {
177	return factory.newStatement("*", "SELECT", createSelectStatement(String.join(", ", columns)), whereBuilder.getParameters());	1✔
178	}
179
180	private String createSelectStatement(String columns) {
181	return "select " + columns	1✔
182	+ (" from " + fromStatement)
183	+ whereBuilder.whereClause()	1✔
184	+ (groupByClauses.isEmpty() ? "" : " group by " + String.join(", ", groupByClauses))	1✔
185	+ (orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses))	1✔
186	+ (rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only");	1✔
187	}
188	}

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