[
https://issues.apache.org/jira/browse/GROOVY-5436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18074603#comment-18074603
]
ASF GitHub Bot commented on GROOVY-5436:
----------------------------------------
Copilot commented on code in PR #2474:
URL: https://github.com/apache/groovy/pull/2474#discussion_r3106028199
##########
subprojects/groovy-sql/src/main/java/groovy/sql/Sql.java:
##########
@@ -938,6 +945,48 @@ public Object getObject() {
};
}
+ /**
+ * Wraps a collection so that it expands to a comma-separated list of
+ * positional placeholders when bound to a SQL {@code IN} clause.
+ * <p>
+ * Example usage:
+ * <pre>
+ * def ids = [1, 2, 3]
+ * sql.rows("SELECT name FROM t WHERE id IN (?)", [Sql.inList(ids)])
+ * // executed as: SELECT name FROM t WHERE id IN (?, ?, ?)
+ * // params: [1, 2, 3]
+ *
+ * // GString form:
+ * sql.rows "SELECT name FROM t WHERE id IN (${Sql.inList(ids)})"
+ *
+ * // Named-parameter form:
+ * sql.rows("SELECT name FROM t WHERE id IN (:ids)", [ids:
Sql.inList(ids)])
+ * </pre>
+ * <p>
+ * The collection is snapshotted at construction. Null or empty collections
+ * are rejected with an {@link IllegalArgumentException} — callers must
+ * guard empty input explicitly (e.g. return early or omit the clause).
+ *
+ * @param values the collection to expand; must be non-null and non-empty
+ * @return a marker usable as a parameter value in any Sql execution method
Review Comment:
The Javadoc states the returned marker is usable "in any Sql execution
method", but this isn't true for APIs that prepare the statement once and then
bind repeatedly (e.g., withBatch(...) using BatchingPreparedStatementWrapper).
Those code paths don't run through InListExpander, and also can't safely
support varying IN-list sizes without re-preparing. Please narrow the claim
(e.g., rows/eachRow/executeUpdate/call variants) or explicitly document the
unsupported cases.
```suggestion
* <p>
* This marker is supported by Sql execution methods that expand SQL text
* before preparing the statement, such as {@code rows}, {@code eachRow},
* {@code executeUpdate} and {@code call} variants that accept
parameters.
* It is not supported by APIs that prepare a statement once and then
bind
* repeatedly, such as {@code withBatch(...)}.
*
* @param values the collection to expand; must be non-null and non-empty
* @return a marker usable as a parameter value in supported Sql
execution methods
```
##########
subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java:
##########
@@ -0,0 +1,171 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package groovy.sql;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Iterator;
+import java.util.List;
+
+/**
+ * Rewrites a SQL string and parameter list so that {@link InListParameter}
+ * markers expand to {@code ?, ?, ..., ?} placeholders in the SQL and their
+ * contained values are spliced into the parameter list at the corresponding
+ * position.
+ * <p>
+ * The SQL is walked character-by-character so that {@code ?} characters
+ * appearing inside single-quoted strings, double-quoted identifiers, line
+ * comments, and block comments are ignored.
+ * <p>
+ * Package-private; consumed by {@link Sql} prior to {@code prepareStatement}.
+ */
+final class InListExpander {
+
+ private InListExpander() {}
+
+ static SqlWithParams expand(String sql, List<?> params) {
+ if (!containsInList(params)) {
+ return new SqlWithParams(sql, new ArrayList<>(params));
+ }
+ StringBuilder out = new StringBuilder(sql.length());
+ List<Object> expanded = new ArrayList<>(params.size());
+ int paramIdx = 0;
+ int i = 0;
+ int len = sql.length();
+ while (i < len) {
+ char c = sql.charAt(i);
+ switch (c) {
+ case '\'':
+ i = copyQuoted(sql, i, '\'', out);
+ break;
+ case '"':
+ i = copyQuoted(sql, i, '"', out);
+ break;
+ case '-':
+ if (i + 1 < len && sql.charAt(i + 1) == '-') {
+ i = copyLineComment(sql, i, out);
+ } else {
+ out.append(c);
+ i++;
+ }
+ break;
+ case '/':
+ if (i + 1 < len && sql.charAt(i + 1) == '*') {
+ i = copyBlockComment(sql, i, out);
+ } else {
+ out.append(c);
+ i++;
+ }
+ break;
+ case '?':
+ if (paramIdx < params.size() && params.get(paramIdx)
instanceof InListParameter) {
+ Collection<?> values = ((InListParameter)
params.get(paramIdx)).getValues();
+ Iterator<?> it = values.iterator();
+ out.append('?');
+ expanded.add(it.next());
+ while (it.hasNext()) {
Review Comment:
InListExpander assumes every InListParameter has at least one value and
calls iterator.next() unconditionally. Since InListParameter is public, a
custom implementation (or an unexpected getValues() result) could return an
empty collection and trigger a NoSuchElementException here. Consider validating
that values is non-null/non-empty and throwing an IllegalArgumentException with
a clear message (consistent with Sql.inList) instead of failing with
NoSuchElementException.
##########
subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java:
##########
@@ -0,0 +1,171 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package groovy.sql;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Iterator;
+import java.util.List;
+
+/**
+ * Rewrites a SQL string and parameter list so that {@link InListParameter}
+ * markers expand to {@code ?, ?, ..., ?} placeholders in the SQL and their
+ * contained values are spliced into the parameter list at the corresponding
+ * position.
+ * <p>
+ * The SQL is walked character-by-character so that {@code ?} characters
+ * appearing inside single-quoted strings, double-quoted identifiers, line
+ * comments, and block comments are ignored.
+ * <p>
+ * Package-private; consumed by {@link Sql} prior to {@code prepareStatement}.
+ */
+final class InListExpander {
+
+ private InListExpander() {}
+
+ static SqlWithParams expand(String sql, List<?> params) {
+ if (!containsInList(params)) {
+ return new SqlWithParams(sql, new ArrayList<>(params));
Review Comment:
expand(...) allocates a new ArrayList and wraps it in a new SqlWithParams
even when there are no InListParameter markers. Given that downstream code
(logging + setParameters) doesn't mutate the params list, this creates
avoidable per-call allocations on the hot path. Consider returning the original
params list unchanged in the fast path (or only copying when you actually
expand) to reduce overhead.
```suggestion
return new SqlWithParams(sql, params);
```
> add Sql.inList for collection expansion in SQL IN clauses
> ---------------------------------------------------------
>
> Key: GROOVY-5436
> URL: https://issues.apache.org/jira/browse/GROOVY-5436
> Project: Groovy
> Issue Type: Improvement
> Components: SQL processing
> Affects Versions: 1.8.6
> Reporter: Gerhard Stuhlpfarrer
> Priority: Major
>
> Groovy SQL (groovy.sql.Sql) provides methods to bind an Array of parameters
> to sql (e.g. rows, eachRow, execute, executeUpdate, ...).
> But you have a problem if you need to pass parameter lists to your sql
> statement which has a IN clause.
> There exists a workaround for this problem, but this is not very groovy.
> see discussions here:
> http://groovy.329449.n5.nabble.com/In-operator-in-Groovy-SQL-td4768856.html
> http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
