Groovy Sql rows

Question

Hello I am trying to get rows using Groovy Sql connection but it returns me records as a List inside a List. The following:

Sql sql = new Sql(dataSource)
List<GroovyRowResult> row = sql.rows('select * from user where username=:userName and password=:password, [userName:'groovy',password:'123'])

returns the result as [[return record as map]]

Any one help me to figure out why the result is a List inside a List. How will I get it as a single level List using the rows method?

Answer 1

It doesn't return a List inside a List, it returns a List of Map with each map containing the columns selected from your select.

So if you want all of the usernames selected (as a List), you can just do:

def usernames = row.username

If you just want a single row, you can do:

GroovyRowResult row = sql.firstRow('select * from user where username=:userName and password=:password, [userName:'groovy',password:'123'])

And then this will effectively just be a map with each key being the field name selected, and each value being the value of the first row of each field

Answer 2

Your results are coming back as a list of maps, not a list of lists. Look at the ':' and ',' chars in the inner part. You can use standard groovy extraction of values from these.

In your case, it looks like you're using a primary key search, so will only return one result, so use firstRow in this case, so that you don't have to extract the single map result from the list.

See the documentation for the groovy Sql class for examples.

In the more general case where you are returning multiple rows, then your data probably looks like this:

[[username:"foo", password:"foopass"], [username:"bar", password:"barpass"]]

Assuming the line:

def results = sql.rows('select * from user')

You can then do things like spread operators:

assert results.username == ["foo", "bar"]
assert results.password == ["foopass", "barpass"]

or iterate over the results

results.each { println it.username }
==> foo
==> bar

or use any of the many Collection functions

println results.collect { "${it.username} -> ${it.password}" }
==> [ "foo -> foopass", "bar -> barpass" ]

I think your main issue was not recognising a single map entry in a list.