Mapping GroovyResultSet to POGOs?

[Tom Nichols]

I thought it was possible to "map" a GroovyResultset to a POGO like this:
class Person {
  String name
  String address
}

def sql = Sql.newInstance(...)
def people = []
sql.eachRow( "select name, address from person" ).eachRow { people <<
new Person( it ) }

This would be possible if the GroovyResultSet were interpreted as a
Map, correct?  i.e. the map would contain named properties that can
normally be passed to a constructor in Groovy.  Would this be possible
by adding an asType(Map) to GroovyResultset?

Thanks.
-Tom

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

    http://xircles.codehaus.org/manage_email

[rickcr-2]

I made a blog post how to do what you want here:

http://reumann.blogspot.com/2009/05/set-pojo-properties-with-groovy-sql-row.html

You just need the call toRowResult()

def getPersons() {
    def persons = []
    sql.eachRow("Select * from Person") {
        persons << new Person( it.toRowResult() )
    }
    return persons
}
 

On Wed, Jul 1, 2009 at 12:59 PM, Tom Nichols <tmnichols@...> wrote:

I thought it was possible to "map" a GroovyResultset to a POGO like this:
class Person {
 String name
 String address
}

def sql = Sql.newInstance(...)
def people = []
sql.eachRow( "select name, address from person" ).eachRow { people <<
new Person( it ) }

This would be possible if the GroovyResultSet were interpreted as a
Map, correct?  i.e. the map would contain named properties that can
normally be passed to a constructor in Groovy.  Would this be possible
by adding an asType(Map) to GroovyResultset?

Thanks.
-Tom

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

   http://xircles.codehaus.org/manage_email

--
Rick R

[Tom Nichols]

Cool, thanks for pointing that out Rick.

On Wed, Jul 1, 2009 at 2:39 PM, Rick<rickcr@...> wrote:

> I made a blog post how to do what you want here:
>
> http://reumann.blogspot.com/2009/05/set-pojo-properties-with-groovy-sql-row.html
>
> You just need the call toRowResult()
>
> def getPersons() {
>     def persons = []
>     sql.eachRow("Select * from Person") {
>         persons << new Person( it.toRowResult() )
>     }
>     return persons
> }
>
>
>
> On Wed, Jul 1, 2009 at 12:59 PM, Tom Nichols <tmnichols@...> wrote:
>>
>> I thought it was possible to "map" a GroovyResultset to a POGO like this:
>> class Person {
>>  String name
>>  String address
>> }
>>
>> def sql = Sql.newInstance(...)
>> def people = []
>> sql.eachRow( "select name, address from person" ).eachRow { people <<
>> new Person( it ) }
>>
>> This would be possible if the GroovyResultSet were interpreted as a
>> Map, correct?  i.e. the map would contain named properties that can
>> normally be passed to a constructor in Groovy.  Would this be possible
>> by adding an asType(Map) to GroovyResultset?
>>
>> Thanks.
>> -Tom
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>
>
> --
> Rick R
>

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

    http://xircles.codehaus.org/manage_email

[Tom Nichols]

Actually, I just tried this and it didn't quite work as expected.  I got
groovy.lang.MissingPropertyException: No such property: NAME for class: Person

Have you ever encountered that?  Apparently the row aliases are being
returned as uppercase, even though my query has them in lowercase.  It
could be vendor-specific (I'm using Oracle) - any ideas there?

Thanks.
-Tom

On Wed, Jul 1, 2009 at 2:39 PM, Rick<rickcr@...> wrote:

> I made a blog post how to do what you want here:
>
> http://reumann.blogspot.com/2009/05/set-pojo-properties-with-groovy-sql-row.html
>
> You just need the call toRowResult()
>
> def getPersons() {
>     def persons = []
>     sql.eachRow("Select * from Person") {
>         persons << new Person( it.toRowResult() )
>     }
>     return persons
> }
>
>
>
> On Wed, Jul 1, 2009 at 12:59 PM, Tom Nichols <tmnichols@...> wrote:
>>
>> I thought it was possible to "map" a GroovyResultset to a POGO like this:
>> class Person {
>>  String name
>>  String address
>> }
>>
>> def sql = Sql.newInstance(...)
>> def people = []
>> sql.eachRow( "select name, address from person" ).eachRow { people <<
>> new Person( it ) }
>>
>> This would be possible if the GroovyResultSet were interpreted as a
>> Map, correct?  i.e. the map would contain named properties that can
>> normally be passed to a constructor in Groovy.  Would this be possible
>> by adding an asType(Map) to GroovyResultset?
>>
>> Thanks.
>> -Tom
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>
>
> --
> Rick R
>

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

    http://xircles.codehaus.org/manage_email

[rickcr-2]

On Wed, Jul 1, 2009 at 4:24 PM, Tom Nichols <tmnichols@...> wrote:

Actually, I just tried this and it didn't quite work as expected.  I got
groovy.lang.MissingPropertyException: No such property: NAME for class: Person

Have you ever encountered that?  Apparently the row aliases are being
returned as uppercase, even though my query has them in lowercase.  It
could be vendor-specific (I'm using Oracle) - any ideas there?

 

 hmm just to test if the uppercase is the issue, create a Person class with the property NAME (all caps) and see if it works.

(Also, just to double check, make sure all fields returned in your query do have matching property definitions in your class, maybe for some weird reason it's barfing on 'name' when it's really some other property at the root of the problem? - I doubt it, but can't hurt to double check.)

[Tom Nichols]

The problem was how Oracle treats column aliases.  Normally, if you
execute a query like :
select person.first_name name from person ....
the column label will come back as  "NAME" -- However, if you
double-quote the label like so:
select person.first_name "name" from person ...
then the label comes back as "name"

Apparently Oracle's ResultSet.getObject( columnName ) accounts for a
difference in case (I think it converts everything to uppercase before
doing any comparison).  But since calling toRowResult basically
inspects the ResultSetMetadata and sticks all of the column label :
values into a HashMap (see
http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/groovy/sql/GroovyRowResult.java?r=trunk)
case suddenly matters in that map whose keys are expected to be
case-sensitive property names.  Quoting the labels is a little
annoying, but I can't think of a good way to improve toRowResult or
rowResult in a way that could accomodate this.  I suppose it's still
easier than having to manually pass each retrieved column value when
constructing my data object.

See also: http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/org/codehaus/groovy/runtime/DefaultGroovyMethods.java?r=16764#l11269

Thanks for the help.

-Tom



On Wed, Jul 1, 2009 at 7:14 PM, Rick<rickcr@...> wrote:

> On Wed, Jul 1, 2009 at 4:24 PM, Tom Nichols <tmnichols@...> wrote:
>>
>> Actually, I just tried this and it didn't quite work as expected.  I got
>> groovy.lang.MissingPropertyException: No such property: NAME for class:
>> Person
>>
>> Have you ever encountered that?  Apparently the row aliases are being
>> returned as uppercase, even though my query has them in lowercase.  It
>> could be vendor-specific (I'm using Oracle) - any ideas there?
>>
>>
>
>  hmm just to test if the uppercase is the issue, create a Person class with
> the property NAME (all caps) and see if it works.
>
> (Also, just to double check, make sure all fields returned in your query do
> have matching property definitions in your class, maybe for some weird
> reason it's barfing on 'name' when it's really some other property at the
> root of the problem? - I doubt it, but can't hurt to double check.)
>
>

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

    http://xircles.codehaus.org/manage_email

[Brian Maso]

Tangentially, there is a similar issue with querying HTTP Headers from HttpServletRequest (or any Groovy object backed by the HttpServletRequest's headers feature). HTTP spec says HTTP header name comparisons should be case insensitive, but exposing headers using a Map interface (or something Map-like) yields the same kind of problem.

Not sure what the solution is, other than reating a custom Map implementation that utilizes case-insensitive hashing (hashCode()) and comparison (equals()) implementations.

Brian Maso

On Fri, Jul 10, 2009 at 11:38 AM, Tom Nichols <tmnichols@...> wrote:

The problem was how Oracle treats column aliases.  Normally, if you
execute a query like :
select person.first_name name from person ....
the column label will come back as  "NAME" -- However, if you
double-quote the label like so:
select person.first_name "name" from person ...
then the label comes back as "name"

Apparently Oracle's ResultSet.getObject( columnName ) accounts for a
difference in case (I think it converts everything to uppercase before
doing any comparison).  But since calling toRowResult basically
inspects the ResultSetMetadata and sticks all of the column label :
values into a HashMap (see
http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/groovy/sql/GroovyRowResult.java?r=trunk)
case suddenly matters in that map whose keys are expected to be
case-sensitive property names.  Quoting the labels is a little
annoying, but I can't think of a good way to improve toRowResult or
rowResult in a way that could accomodate this.  I suppose it's still
easier than having to manually pass each retrieved column value when
constructing my data object.

See also: http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/org/codehaus/groovy/runtime/DefaultGroovyMethods.java?r=16764#l11269

Thanks for the help.

-Tom



On Wed, Jul 1, 2009 at 7:14 PM, Rick<rickcr@...> wrote:
> On Wed, Jul 1, 2009 at 4:24 PM, Tom Nichols <tmnichols@...> wrote:
>>
>> Actually, I just tried this and it didn't quite work as expected.  I got
>> groovy.lang.MissingPropertyException: No such property: NAME for class:
>> Person
>>
>> Have you ever encountered that?  Apparently the row aliases are being
>> returned as uppercase, even though my query has them in lowercase.  It
>> could be vendor-specific (I'm using Oracle) - any ideas there?
>>
>>
>
>  hmm just to test if the uppercase is the issue, create a Person class with
> the property NAME (all caps) and see if it works.
>
> (Also, just to double check, make sure all fields returned in your query do
> have matching property definitions in your class, maybe for some weird
> reason it's barfing on 'name' when it's really some other property at the
> root of the problem? - I doubt it, but can't hurt to double check.)
>
>

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

   http://xircles.codehaus.org/manage_email

[Tom Nichols]

Well, if it's not _actually_ backed by a map, it should be smart
enough to to a toUpperCase before performing a comparison.  The
problem comes when iterating over a keySet and expecting those values
to have a particular case, however. i.e. if you insert someString your
keySet might yield "somestring" or "SOMESTRING".

I believe Commons-collections might contain case-insensitive
collections...  Maybe they are smart enough to perform hashing w/o
actually normalizing the key.

-Tom


On Fri, Jul 10, 2009 at 3:08 PM, Brian Maso<brian@...> wrote:

> Tangentially, there is a similar issue with querying HTTP Headers from
> HttpServletRequest (or any Groovy object backed by the HttpServletRequest's
> headers feature). HTTP spec says HTTP header name comparisons should be case
> insensitive, but exposing headers using a Map interface (or something
> Map-like) yields the same kind of problem.
>
> Not sure what the solution is, other than reating a custom Map
> implementation that utilizes case-insensitive hashing (hashCode()) and
> comparison (equals()) implementations.
>
> Brian Maso
>
> On Fri, Jul 10, 2009 at 11:38 AM, Tom Nichols <tmnichols@...> wrote:
>>
>> The problem was how Oracle treats column aliases.  Normally, if you
>> execute a query like :
>> select person.first_name name from person ....
>> the column label will come back as  "NAME" -- However, if you
>> double-quote the label like so:
>> select person.first_name "name" from person ...
>> then the label comes back as "name"
>>
>> Apparently Oracle's ResultSet.getObject( columnName ) accounts for a
>> difference in case (I think it converts everything to uppercase before
>> doing any comparison).  But since calling toRowResult basically
>> inspects the ResultSetMetadata and sticks all of the column label :
>> values into a HashMap (see
>>
>> http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/groovy/sql/GroovyRowResult.java?r=trunk)
>> case suddenly matters in that map whose keys are expected to be
>> case-sensitive property names.  Quoting the labels is a little
>> annoying, but I can't think of a good way to improve toRowResult or
>> rowResult in a way that could accomodate this.  I suppose it's still
>> easier than having to manually pass each retrieved column value when
>> constructing my data object.
>>
>> See also:
>> http://fisheye.codehaus.org/browse/groovy/trunk/groovy/groovy-core/src/main/org/codehaus/groovy/runtime/DefaultGroovyMethods.java?r=16764#l11269
>>
>> Thanks for the help.
>>
>> -Tom
>>
>>
>>
>> On Wed, Jul 1, 2009 at 7:14 PM, Rick<rickcr@...> wrote:
>> > On Wed, Jul 1, 2009 at 4:24 PM, Tom Nichols <tmnichols@...> wrote:
>> >>
>> >> Actually, I just tried this and it didn't quite work as expected.  I
>> >> got
>> >> groovy.lang.MissingPropertyException: No such property: NAME for class:
>> >> Person
>> >>
>> >> Have you ever encountered that?  Apparently the row aliases are being
>> >> returned as uppercase, even though my query has them in lowercase.  It
>> >> could be vendor-specific (I'm using Oracle) - any ideas there?
>> >>
>> >>
>> >
>> >  hmm just to test if the uppercase is the issue, create a Person class
>> > with
>> > the property NAME (all caps) and see if it works.
>> >
>> > (Also, just to double check, make sure all fields returned in your query
>> > do
>> > have matching property definitions in your class, maybe for some weird
>> > reason it's barfing on 'name' when it's really some other property at
>> > the
>> > root of the problem? - I doubt it, but can't hurt to double check.)
>> >
>> >
>>
>> ---------------------------------------------------------------------
>> 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