Quantcast

Difference between sql.rows().each and sql.eachRow()

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Difference between sql.rows().each and sql.eachRow()

AxiomShell
Hi everyone,

Can someone explain me why Groovy allows this:

sql.rows(someQuery).each { Map row -> def item = new
ItemThatTakesAMap(row) }

But not this:

sql.eachRow(someQuery) { Map row -> def item = new
ItemThatTakesAMap(row) }

All the best.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

Jim White-2
AxiomShell wrote:

> Hi everyone,
>
> Can someone explain me why Groovy allows this:
>
> sql.rows(someQuery).each { Map row -> def item = new
> ItemThatTakesAMap(row) }
>
> But not this:
>
> sql.eachRow(someQuery) { Map row -> def item = new
> ItemThatTakesAMap(row) }

Design gap?

sql.rows returns a List of g.sql.GroovyRowResult which implements Map as
you observe.

http://groovy.codehaus.org/api/groovy/sql/GroovyRowResult.html

sql.eachRow iterates providing a g.sql.GroovyResultSet which implements
java.sql.ResultSet.  ResultSet is not a Map (or a Set for that matter).

http://groovy.codehaus.org/api/groovy/sql/GroovyResultSet.html

I think it is a legacy of JDBC being developed long before Java
Collections and the gap has never been closed in the API because folks
tend to use various ORM tools.  Groovy of course could, and perhaps
should, correct that oversight.

While GinA explains the usage of these methods well enough, the GDK docs
need to provide these key details on what types are being used.

http://groovy.codehaus.org/api/groovy/sql/Sql.html

I looked around a bit to see if there is an easy way to get a Map from a
GroovyResultSet, but don't see anything although there may be something.

Jim


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

AxiomShell
Hi Jim,

Thanks for the explanation.
It's not an issue for me (the first method works fine), but I was
curious about the logic behind it.
I'm not sure if this is the kind of thing I should open a JIRA request
for...

All the best.

On Apr 26, 5:25 pm, Jim White <[hidden email]> wrote:

> AxiomShell wrote:
> > Hi everyone,
>
> > Can someone explain me why Groovy allows this:
>
> > sql.rows(someQuery).each { Map row -> def item = new
> > ItemThatTakesAMap(row) }
>
> > But not this:
>
> > sql.eachRow(someQuery) { Map row -> def item = new
> > ItemThatTakesAMap(row) }
>
> Design gap?
>
> sql.rows returns a List of g.sql.GroovyRowResult which implements Map as
> you observe.
>
> http://groovy.codehaus.org/api/groovy/sql/GroovyRowResult.html
>
> sql.eachRow iterates providing a g.sql.GroovyResultSet which implements
> java.sql.ResultSet.  ResultSet is not a Map (or a Set for that matter).
>
> http://groovy.codehaus.org/api/groovy/sql/GroovyResultSet.html
>
> I think it is a legacy of JDBC being developed long before Java
> Collections and the gap has never been closed in the API because folks
> tend to use various ORM tools.  Groovy of course could, and perhaps
> should, correct that oversight.
>
> While GinA explains the usage of these methods well enough, the GDK docs
> need to provide these key details on what types are being used.
>
> http://groovy.codehaus.org/api/groovy/sql/Sql.html
>
> I looked around a bit to see if there is an easy way to get a Map from a
> GroovyResultSet, but don't see anything although there may be something.
>
> Jim
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

Mike Dillon-4
In reply to this post by Jim White-2
begin Jim White quotation:

> Design gap?
>
> sql.rows returns a List of g.sql.GroovyRowResult which implements Map as
> you observe.
>
> http://groovy.codehaus.org/api/groovy/sql/GroovyRowResult.html
>
> sql.eachRow iterates providing a g.sql.GroovyResultSet which implements
> java.sql.ResultSet.  ResultSet is not a Map (or a Set for that matter).
>
> http://groovy.codehaus.org/api/groovy/sql/GroovyResultSet.html

I just ran into this yesterday... I wanted to do something like this:

    sql.eachRow("select ...") {
        println it.values().join(sep)
    }

And I got bit. The reason I thought it would work was that the
toString() of "println it" looked like it was a Map. I'm not actually
sure where that toString() was coming from.

I ended up having to do something gross like this:

    sql.eachRow("select ...") { rs ->
        def fields = (0..<rs.getMetaData().columnCount).collect { rs[it] }

        println fields.join(sep)
    }
   

-md

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

AbuNassar
GroovyRowResult offers a toRowResult() method which I've found useful. E.g.

import groovy.sql.*

sql = Sql.newInstance('jdbc:...", ...)

query = """
SELECT
    *
FROM blah
"""

sql.eachRow query, {
    // The "row" is actually a GroovyResultSet, so it.each() does *not* iterate over the name/value pairs (i.e. column name, column value). So...
    it.toRowResult().each { k, v ->
        println "$k, $v"
    }
}

Therefore:

def builder = new groovy.xml.StreamingMarkupBuilder()
builder.encoding = 'UTF-8'
def xml = builder.bind {
    mkp.xmlDeclaration()
    comment << new Date()
    results { // arbitrary name for outermost XML element
        sql.eachRow query, {
            row { // arbitrary XML element name for each row
                it.toRowResult().each { k, v -> // key/value pair for each column in row
                    "$k"(v) // element name for each column value determined dynamically!
                }
            }
        }
    }
}
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

Thom Nichols
In reply to this post by AxiomShell
I asked this question a while back:
http://www.nabble.com/Mapping-GroovyResultSet-to-POGOs--tp24293820p24432657.html

Basically, do this:

sql.eachRow( query ) { rs ->
  def item = new ItemThatTakesAMap( rs.toRowResult() )
}

I think sql.rows collects all rows into a list, while sql.eachRow
iterates over the resultSet, so all of the resultset data is not
buffered into a list-of-maps first.  I'd imagine eachRow is much more
efficient as it does not buffer all that resultset data into those
data structures.

-Tom


On Sat, Apr 26, 2008 at 12:04 PM, AxiomShell<[hidden email]> wrote:

> Hi everyone,
>
> Can someone explain me why Groovy allows this:
>
> sql.rows(someQuery).each { Map row -> def item = new
> ItemThatTakesAMap(row) }
>
> But not this:
>
> sql.eachRow(someQuery) { Map row -> def item = new
> ItemThatTakesAMap(row) }
>
> All the best.
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between sql.rows().each and sql.eachRow()

Rick R
On Wed, Aug 26, 2009 at 9:45 AM, Tom Nichols <[hidden email]> wrote:
 
sql.eachRow( query ) { rs ->
 def item = new ItemThatTakesAMap( rs.toRowResult() )
}
 

I keep touting this to my buddies about how powerful this feature is. People really underestimate it, in my opinion. For example, I'm a huge iBATIS fan which is not an ORM solution but is a "sql mapper to objects" solution.  Yet, with using groovy sql, I've basically made the need for iBATIS to drastically diminish.. especially because you can even tweak your base pojo to allow you to even map nested items (eg Employee has a Department object.)  The only thing that doesn't work out of the box with my groovy sql solution is nesting Lists in objects - which ibatis handles well because of the meta data. However, I'm thinking of creating an object to handle this in groovy as well, I just haven't had the time to work on it (it would actually be quite easy to implement so I should get on it:)

(iBATIS has a lot of other nice features though, like built in caching, etc, but for many apps groovy sql could just be all you need. Yes, yes, I know Hibernate has its place also, but some of us still like sql:)
Loading...