This is an automated email from the ASF dual-hosted git repository. paulk pushed a commit to branch GROOVY_4_0_X in repository https://gitbox.apache.org/repos/asf/groovy.git
The following commit(s) were added to refs/heads/GROOVY_4_0_X by this push: new b7e7f564f4 GROOVY-11342: SQL enhancement to allow metadata access for execute b7e7f564f4 is described below commit b7e7f564f4159e51aca9d9453d7c9deb479a7666 Author: Paul King <pa...@asert.com.au> AuthorDate: Thu Mar 28 22:25:59 2024 +1000 GROOVY-11342: SQL enhancement to allow metadata access for execute --- .../groovy-sql/src/main/java/groovy/sql/Sql.java | 224 ++++++++++++++++----- .../test/groovy/groovy/sql/SqlCompleteTest.groovy | 13 ++ 2 files changed, 186 insertions(+), 51 deletions(-) diff --git a/subprojects/groovy-sql/src/main/java/groovy/sql/Sql.java b/subprojects/groovy-sql/src/main/java/groovy/sql/Sql.java index 3ff10ec672..f17db5fdc0 100644 --- a/subprojects/groovy-sql/src/main/java/groovy/sql/Sql.java +++ b/subprojects/groovy-sql/src/main/java/groovy/sql/Sql.java @@ -1094,7 +1094,7 @@ public class Sql implements AutoCloseable { } private static ArrayList<Object> singletonList(Object item) { - ArrayList<Object> params = new ArrayList<Object>(); + ArrayList<Object> params = new ArrayList<>(); params.add(item); return params; } @@ -2379,7 +2379,7 @@ public class Sql implements AutoCloseable { /** * Executes the given piece of SQL. - * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. * <p> * Example usages: * <pre> @@ -2397,29 +2397,50 @@ public class Sql implements AutoCloseable { * Resource handling is performed automatically where appropriate. * * @param sql the SQL to execute - * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values - * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. - * The closure will be called for each result produced from executing the SQL. + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @since 2.3.2 */ - public void execute(String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure processResults) throws SQLException { + public void execute(String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { + execute(sql, (Closure) null, resultClosure); + } + + /** + * Executes the given piece of SQL. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. + * In addition, the <code>metaClosure</code> will be called for each result producing a ResultSet, if any, passing in the <code>ResultSetMetaData</code> as argument. + * + * @param sql the SQL to execute + * @param metaClosure called for metadata (only called for results which produce a ResultSet, if any) + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. + * @throws SQLException if a database access error occurs + * @since 4.0.21 + */ + public void execute( + String sql, + @ClosureParams(value = SimpleType.class, options = "java.sql.ResultSetMetaData") Closure metaClosure, + @ClosureParams(value = FromString.class, options = {"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure + ) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); boolean isResultSet = statement.execute(sql); int updateCount = statement.getUpdateCount(); - while(isResultSet || updateCount != -1) { - if (processResults.getMaximumNumberOfParameters() != 2) { - throw new SQLException("Incorrect number of parameters for processResults Closure"); + while (isResultSet || updateCount != -1) { + if (resultClosure.getMaximumNumberOfParameters() != 2) { + throw new SQLException("Incorrect number of parameters for resultClosure"); } if (isResultSet) { ResultSet resultSet = statement.getResultSet(); - List<GroovyRowResult> rowResult = resultSet == null ? null : asList(sql, resultSet); - processResults.call(isResultSet, rowResult); + List<GroovyRowResult> rowResult = resultSet == null ? null : asList(sql, resultSet, metaClosure); + resultClosure.call(isResultSet, rowResult); } else { - processResults.call(isResultSet, updateCount); + resultClosure.call(isResultSet, updateCount); } isResultSet = statement.getMoreResults(); updateCount = statement.getUpdateCount(); @@ -2475,7 +2496,7 @@ public class Sql implements AutoCloseable { /** * Executes the given piece of SQL with parameters. - * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. * <p> * This method supports named and named ordinal parameters. * See the class Javadoc for more details. @@ -2484,14 +2505,43 @@ public class Sql implements AutoCloseable { * * @param sql the SQL statement * @param params a list of parameters - * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values - * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. - * The closure will be called for each result produced from executing the SQL. + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #execute(String, Closure) * @since 2.3.2 */ - public void execute(String sql, List<Object> params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure processResults) throws SQLException { + public void execute(String sql, List<Object> params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { + + } + + /** + * Executes the given piece of SQL with parameters. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. + * In addition, the <code>metaClosure</code> will be called for each result producing a ResultSet, if any, passing in the <code>ResultSetMetaData</code> as argument. + * <p> + * This method supports named and named ordinal parameters. + * See the class Javadoc for more details. + * <p> + * Resource handling is performed automatically where appropriate. + * + * @param sql the SQL statement + * @param params a list of parameters + * @param metaClosure called for metadata (only called for results which produce a ResultSet, if any) + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. + * @throws SQLException if a database access error occurs + * @see #execute(String, Closure) + * @since 4.0.21 + */ + public void execute( + String sql, + List<Object> params, + @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, + @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure + ) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { @@ -2499,15 +2549,15 @@ public class Sql implements AutoCloseable { boolean isResultSet = statement.execute(); int updateCount = statement.getUpdateCount(); while(isResultSet || updateCount != -1) { - if (processResults.getMaximumNumberOfParameters() != 2) { - throw new SQLException("Incorrect number of parameters for processResults Closure"); + if (resultClosure.getMaximumNumberOfParameters() != 2) { + throw new SQLException("Incorrect number of parameters for resultClosure"); } if (isResultSet) { ResultSet resultSet = statement.getResultSet(); - List<GroovyRowResult> rowResult = resultSet == null ? null : asList(sql, resultSet); - processResults.call(isResultSet, rowResult); + List<GroovyRowResult> rowResult = resultSet == null ? null : asList(sql, resultSet, metaClosure); + resultClosure.call(isResultSet, rowResult); } else { - processResults.call(isResultSet, updateCount); + resultClosure.call(isResultSet, updateCount); } isResultSet = statement.getMoreResults(); updateCount = statement.getUpdateCount(); @@ -2542,14 +2592,36 @@ public class Sql implements AutoCloseable { * * @param params a map containing the named parameters * @param sql the SQL statement - * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values - * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. - * The closure will be called for each result produced from executing the SQL. + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @since 2.3.2 */ - public void execute(Map params, String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure processResults) throws SQLException { - execute(sql, singletonList(params), processResults); + public void execute(Map params, String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { + execute(sql, singletonList(params), resultClosure); + } + + /** + * A variant of {@link #execute(String, java.util.List, Closure, Closure)} + * useful when providing the named parameters as named arguments. + * + * @param params a map containing the named parameters + * @param sql the SQL statement + * @param metaClosure called for metadata (only called for results which produce a ResultSet, if any) + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. + * @throws SQLException if a database access error occurs + * @since 4.0.21 + */ + public void execute( + Map params, + String sql, + @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, + @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure + ) throws SQLException { + execute(sql, singletonList(params), metaClosure, resultClosure); } /** @@ -2581,15 +2653,42 @@ public class Sql implements AutoCloseable { * * @param sql the SQL statement * @param params an array of parameters - * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values - * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. - * The closure will be called for each result produced from executing the SQL. + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #execute(String, List, Closure) * @since 2.3.2 */ - public void execute(String sql, Object[] params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure processResults) throws SQLException { - execute(sql, Arrays.asList(params), processResults); + public void execute(String sql, Object[] params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { + execute(sql, Arrays.asList(params), resultClosure); + } + + /** + * Executes the given piece of SQL with parameters. + * <p> + * An Object array variant of {@link #execute(String, List, Closure, Closure)}. + * <p> + * This method supports named and named ordinal parameters by supplying such + * parameters in the <code>params</code> array. See the class Javadoc for more details. + * + * @param sql the SQL statement + * @param params an array of parameters + * @param metaClosure called for metadata (only called for results which produce a ResultSet, if any) + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. + * @throws SQLException if a database access error occurs + * @see #execute(String, List, Closure, Closure) + * @since 4.0.21 + */ + public void execute( + String sql, + Object[] params, + @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, + @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure + ) throws SQLException { + execute(sql, Arrays.asList(params), metaClosure, resultClosure); } /** @@ -2622,22 +2721,45 @@ public class Sql implements AutoCloseable { /** * Executes the given SQL with embedded expressions inside. - * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. * Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params - * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values - * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. - * The closure will be called for each result produced from executing the SQL. + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #expand(Object) * @see #execute(String, List, Closure) * @since 2.3.2 */ - public void execute(GString gstring, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure processResults) throws SQLException { + public void execute(GString gstring, @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { + List<Object> params = getParameters(gstring); + String sql = asSql(gstring, params); + execute(sql, params, resultClosure); + } + + /** + * Executes the given SQL with embedded expressions inside. + * Also calls the provided <code>resultClosure</code> to process any ResultSet or UpdateCount results that executing the SQL might produce. + * In addition, the <code>metaClosure</code> will be called for each result producing a ResultSet, if any, passing in the <code>ResultSetMetaData</code> as argument. + * Resource handling is performed automatically where appropriate. + * + * @param gstring a GString containing the SQL query with embedded params + * @param resultClosure a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values + * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. + * The closure will be called for each result produced from executing the SQL. + * @throws SQLException if a database access error occurs + * @see #expand(Object) + * @see #execute(String, List, Closure, Closure) + * @since 4.0.21 + */ + public void execute(GString gstring, + @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, + @ClosureParams(value=FromString.class, options={"boolean,java.util.List<groovy.sql.GroovyRowResult>", "boolean,int"}) Closure resultClosure) throws SQLException { List<Object> params = getParameters(gstring); String sql = asSql(gstring, params); - execute(sql, params, processResults); + execute(sql, params, metaClosure, resultClosure); } /** @@ -3826,7 +3948,7 @@ public class Sql implements AutoCloseable { boolean savedWithinBatch = withinBatch; BatchingPreparedStatementWrapper psWrapper = null; if (preCheck != null) { - indexPropList = new ArrayList<Tuple>(); + indexPropList = new ArrayList<>(); for (Object next : preCheck.getParams()) { indexPropList.add((Tuple) next); } @@ -3903,11 +4025,11 @@ public class Sql implements AutoCloseable { protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { Connection connection = createConnection(); CallableStatement statement = null; - List<GroovyResultSet> resultSetResources = new ArrayList<GroovyResultSet>(); + List<GroovyResultSet> resultSetResources = new ArrayList<>(); try { statement = getCallableStatement(connection, sql, params); boolean hasResultSet = statement.execute(); - List<Object> results = new ArrayList<Object>(); + List<Object> results = new ArrayList<>(); int indx = 0; int inouts = 0; for (Object value : params) { @@ -3931,9 +4053,9 @@ public class Sql implements AutoCloseable { indx++; } closure.call(results.toArray(new Object[inouts])); - List<List<GroovyRowResult>> resultSets = new ArrayList<List<GroovyRowResult>>(); + List<List<GroovyRowResult>> resultSets = new ArrayList<>(); if (processResultsSets == NO_RESULT_SETS) { - resultSets.add(new ArrayList<GroovyRowResult>()); + resultSets.add(new ArrayList<>()); return resultSets; } //Check both hasResultSet and getMoreResults() because of differences in vendor behavior @@ -4039,7 +4161,7 @@ public class Sql implements AutoCloseable { protected List<GroovyRowResult> asList(String sql, ResultSet rs, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { - List<GroovyRowResult> results = new ArrayList<GroovyRowResult>(); + List<GroovyRowResult> results = new ArrayList<>(); try { if (metaClosure != null) { @@ -4199,7 +4321,7 @@ public class Sql implements AutoCloseable { * @see #expand(Object) */ protected List<Object> getParameters(GString gstring) { - return new ArrayList<Object>(Arrays.asList(gstring.getValues())); + return new ArrayList<>(Arrays.asList(gstring.getValues())); } /** @@ -4311,7 +4433,7 @@ public class Sql implements AutoCloseable { private Connection createConnection(DataSource dataSource) throws SQLException { Connection con; try { - con = java.security.AccessController.doPrivileged(new PrivilegedExceptionAction<Connection>() { + con = java.security.AccessController.doPrivileged(new PrivilegedExceptionAction<>() { @Override public Connection run() throws SQLException { return dataSource.getConnection(); @@ -4435,12 +4557,12 @@ public class Sql implements AutoCloseable { private static List<List<Object>> calculateKeys(ResultSet keys) throws SQLException { // Prepare a list to contain the auto-generated column // values, and then fetch them from the statement. - List<List<Object>> autoKeys = new ArrayList<List<Object>>(); + List<List<Object>> autoKeys = new ArrayList<>(); int count = keys.getMetaData().getColumnCount(); // Copy the column values into a list of a list. while (keys.next()) { - List<Object> rowKeys = new ArrayList<Object>(count); + List<Object> rowKeys = new ArrayList<>(count); for (int i = 1; i <= count; i++) { rowKeys.add(keys.getObject(i)); } @@ -4535,7 +4657,7 @@ public class Sql implements AutoCloseable { return new SqlWithParams(sql, params); } - List<Tuple> indexPropList = new ArrayList<Tuple>(); + List<Tuple> indexPropList = new ArrayList<>(); for (Object next : preCheck.getParams()) { indexPropList.add((Tuple) next); } @@ -4583,12 +4705,12 @@ public class Sql implements AutoCloseable { return null; } - List<Object> indexPropList = new ArrayList<Object>(propList); + List<Object> indexPropList = new ArrayList<>(propList); return new SqlWithParams(newSql, indexPropList); } public List<Object> getUpdatedParams(List<Object> params, List<Tuple> indexPropList) { - List<Object> updatedParams = new ArrayList<Object>(); + List<Object> updatedParams = new ArrayList<>(); for (Tuple tuple : indexPropList) { int index = (Integer) tuple.get(0); String prop = (String) tuple.get(1); diff --git a/subprojects/groovy-sql/src/test/groovy/groovy/sql/SqlCompleteTest.groovy b/subprojects/groovy-sql/src/test/groovy/groovy/sql/SqlCompleteTest.groovy index 370f201e2f..55d2ae8ad2 100644 --- a/subprojects/groovy-sql/src/test/groovy/groovy/sql/SqlCompleteTest.groovy +++ b/subprojects/groovy-sql/src/test/groovy/groovy/sql/SqlCompleteTest.groovy @@ -168,6 +168,19 @@ class SqlCompleteTest extends SqlHelperTestCase { assert personMetaClosureCalled } + void testExecuteWithStringAndClosures() { + def results = [:] + sql.execute("select * from PERSON", personMetaClosure) { isResultSet, rs -> + if (isResultSet) { + rs.each { + results.put(it.firstname, it['lastname']) + } + } + } + assert results == ["James": "Strachan", "Bob": "Mcwhirter", "Sam": "Pullara"] + assert personMetaClosureCalled + } + void testEachRowWithStringAndList() { def results = [] sql.eachRow("select * from FOOD where type=? and name != ?", ["cheese", "edam"]) { results.add(it.name) }