Index: src/main/groovy/sql/Sql.java =================================================================== RCS file: /home/projects/groovy/scm/groovy/groovy-core/src/main/groovy/sql/Sql.java,v retrieving revision 1.18 diff -u -r1.18 Sql.java --- src/main/groovy/sql/Sql.java 13 Jan 2005 12:51:01 -0000 1.18 +++ src/main/groovy/sql/Sql.java 19 Feb 2005 11:22:00 -0000 @@ -44,12 +44,14 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collections; import java.util.Iterator; import java.util.List; +import java.util.LinkedHashMap; import java.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; @@ -368,7 +370,89 @@ warnDeprecated(); eachRow(gstring, closure); } - + + /** + * Performs the given SQL query and return the rows of the result set + */ + public List rows(String sql) throws SQLException { + List results = new ArrayList(); + Connection connection = createConnection(); + Statement statement = connection.createStatement(); + configure(statement); + ResultSet rs = null; + try { + log.fine(sql); + rs = statement.executeQuery(sql); + while (rs.next()) { + ResultSetMetaData metadata = rs.getMetaData(); + LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true); + for(int i=1 ; i<=metadata.getColumnCount() ; i++) { + lhm.put(metadata.getColumnName(i),rs.getObject(i)); + } + GroovyRowResult row = new GroovyRowResult(lhm); + results.add(row); + } + return(results); + } + catch (SQLException e) { + log.log(Level.FINE, "Failed to execute: " + sql, e); + throw e; + } + finally { + closeResources(connection, statement, rs); + } + } + + /** + * Performs the given SQL query and return the first row of the result set + */ + public Object firstRow(String sql) throws SQLException { + return( rows(sql).get(0)); + } + + /** + * Performs the given SQL query with the list of params and return + * the rows of the result set + */ + public List rows(String sql, List params) throws SQLException { + List results = new ArrayList(); + Connection connection = createConnection(); + PreparedStatement statement = null; + ResultSet rs = null; + try { + log.fine(sql); + statement = connection.prepareStatement(sql); + setParameters(params, statement); + configure(statement); + rs = statement.executeQuery(); + while (rs.next()) { + ResultSetMetaData metadata = rs.getMetaData(); + LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true); + for(int i=1 ; i<=metadata.getColumnCount() ; i++) { + lhm.put(metadata.getColumnName(i),rs.getObject(i)); + } + GroovyRowResult row = new GroovyRowResult(lhm); + results.add(row); + } + return(results); + } + catch (SQLException e) { + log.log(Level.FINE, "Failed to execute: " + sql, e); + throw e; + } + finally { + closeResources(connection, statement, rs); + } + } + + /** + * Performs the given SQL query with the list of params and return + * the first row of the result set + */ + public Object firstRow(String sql, List params) throws SQLException { + return( rows(sql, params).get(0)); + } + /** * Executes the given piece of SQL */ Index: src/test/UberTestCase2.java =================================================================== RCS file: /home/projects/groovy/scm/groovy/groovy-core/src/test/UberTestCase2.java,v retrieving revision 1.8 diff -u -r1.8 UberTestCase2.java --- src/test/UberTestCase2.java 5 Feb 2005 07:34:09 -0000 1.8 +++ src/test/UberTestCase2.java 19 Feb 2005 11:22:00 -0000 @@ -54,6 +54,7 @@ suite.addTestSuite(groovy.sql.SqlTest.class); suite.addTestSuite(groovy.sql.SqlWithBuilderTest.class); suite.addTestSuite(groovy.sql.SqlWithTypedResultsTest.class); + suite.addTestSuite(groovy.sql.SqlRowsTest.class); suite.addTestSuite(groovy.text.TemplateTest.class); suite.addTestSuite(groovy.tree.NodePrinterTest.class); suite.addTestSuite(groovy.txn.TransactionTest.class); Index: src/main/groovy/sql/GroovyRowResult.java =================================================================== RCS file: src/main/groovy/sql/GroovyRowResult.java diff -N src/main/groovy/sql/GroovyRowResult.java --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/main/groovy/sql/GroovyRowResult.java 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,123 @@ +/* +$Id$ + +Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved. + +Redistribution and use of this software and associated documentation +("Software"), with or without modification, are permitted provided +that the following conditions are met: + +1. Redistributions of source code must retain copyright + statements and notices. Redistributions must also contain a + copy of this document. + +2. Redistributions in binary form must reproduce the + above copyright notice, this list of conditions and the + following disclaimer in the documentation and/or other + materials provided with the distribution. + +3. The name "groovy" must not be used to endorse or promote + products derived from this Software without prior written + permission of The Codehaus. For written permission, + please contact info@codehaus.org. + +4. Products derived from this Software may not be called "groovy" + nor may "groovy" appear in their names without prior written + permission of The Codehaus. "groovy" is a registered + trademark of The Codehaus. + +5. Due credit should be given to The Codehaus - + http://groovy.codehaus.org/ + +THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS +``AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT +NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND +FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL +THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, +INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR +SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) +HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, +STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) +ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED +OF THE POSSIBILITY OF SUCH DAMAGE. + +*/ +package groovy.sql; + +import groovy.lang.GroovyObjectSupport; +import groovy.lang.MissingPropertyException; + +import java.util.Iterator; +import java.util.LinkedHashMap; + +/** +* Represents an extent of objects. +* It's used in the oneRow method to be able to access the result +* of a SQL query by the name of the column, or by the column number. +* +* @Author Jean-Louis Berliet +* @version $Revision$ +*/ +public class GroovyRowResult extends GroovyObjectSupport { + + private LinkedHashMap result; + + public GroovyRowResult(LinkedHashMap result) { + this.result = result; + } + + /** + * Retrieve the value of the property by its name * + * @param property is the name of the property to look at + * @return the value of the property + */ + public Object getProperty(String property) { + try { + Object value = result.get(property); + if (value==null) { + // with some databases/drivers, the columns names are stored uppercase. + value = result.get(property.toUpperCase()); + if (value==null) + throw new MissingPropertyException(property, GroovyRowResult.class); + } + return(value); + } + catch (Exception e) { + throw new MissingPropertyException(property, GroovyRowResult.class, e); + } + } + + /** + * Retrieve the value of the property by its index. + * A negative index will count backwards from the last column. + * @param index is the number of the column to look at + * @return the value of the property + */ + public Object getAt(int index) { + try { + // a negative index will count backwards from the last column. + if (index<0) + index += result.size(); + Iterator it = result.values().iterator(); + int i = 0; + Object obj = null; + while ( (obj==null) && (it.hasNext()) ) { + if (i==index) + obj = it.next(); + else + it.next(); + i++; + } + return(obj); + } + catch (Exception e) { + throw new MissingPropertyException(Integer.toString(index), GroovyRowResult.class, e); + } + } + + public String toString() { + return(result.toString() ); + } + +} Index: src/test/groovy/sql/SqlRowsTest.groovy =================================================================== RCS file: src/test/groovy/sql/SqlRowsTest.groovy diff -N src/test/groovy/sql/SqlRowsTest.groovy --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/groovy/sql/SqlRowsTest.groovy 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,79 @@ +package groovy.sql + +import org.axiondb.jdbc.AxionDriver + +class SqlRowsTest extends TestHelper { + + void testFirstRowWithPropertyName() { + sql = createSql() + + results = sql.firstRow("select firstname, lastname from PERSON where id=1").firstname + expected = "James" + assert results == expected + } + + void testFirstRowWithPropertyNameAndParams() { + sql = createSql() + + results = sql.firstRow("select firstname, lastname from PERSON where id=?", [1]).lastname + expected = "Strachan" + assert results == expected + } + + void testFirstRowWithPropertyNumber() { + sql = createSql() + + results = sql.firstRow("select firstname, lastname from PERSON where id=1")[0] + expected = "James" + assert results == expected + } + + void testFirstRowWithPropertyNumberAndParams() { + sql = createSql() + + results = sql.firstRow("select firstname, lastname from PERSON where id=?", [1])[0] + expected = "James" + assert results == expected + } + + void testAllRowsWithPropertyNumber() { + sql = createSql() + + results = sql.rows("select firstname, lastname from PERSON where id=1 or id=2 order by id") + assert results[0][0] == "James" + assert results[0][1] == "Strachan" + assert results[1][0] == "Bob" + assert results[1][1] == "Mcwhirter" + } + + void testAllRowsWithPropertyNumberAndParams() { + sql = createSql() + + results = sql.rows("select firstname, lastname from PERSON where id=? or id=? order by id", [1,2]) + assert results[0][0] == "James" + assert results[0][1] == "Strachan" + assert results[1][0] == "Bob" + assert results[1][1] == "Mcwhirter" + } + + void testAllRowsWithPropertyName() { + sql = createSql() + + results = sql.rows("select firstname, lastname from PERSON where id=1 or id=2 order by id") + assert results[0].firstname == "James" + assert results[0].lastname == "Strachan" + assert results[1].firstname == "Bob" + assert results[1].lastname == "Mcwhirter" + } + + void testAllRowsWithPropertyNameAndParams() { + sql = createSql() + + results = sql.rows("select firstname, lastname from PERSON where id=? or id=? order by id", [1,2]) + assert results[0].firstname == "James" + assert results[0].lastname == "Strachan" + assert results[1].firstname == "Bob" + assert results[1].lastname == "Mcwhirter" + } + +}