rAJ rAJ - 29 days ago 8
SQL Question

Store DB columns values to string in Groovy

I am trying to store Sql query result to string.

def DBlist = con.rows("select a.pkResourceItemsID,b.fieldValue,a.fkResourceID,a.fkCountryID,b.fkLocationID,b.fkBusinessUnitID,b.floor from tblResourceItems a inner join tblresourceitemData b on a.pkResourceItemsID=b.fkResourceItemID where a.pkResourceItemsID='$id'" )

log.info "DB_List = ${DBlist}"


The result I am getting:

DB_List =[[pkResourceItemsID:2000, fieldValue:Hello, fkResourceID:1, fkCountryID:1, fkLocationID:88, fkBusinessUnitID:518, floor:1]]


The result I want :

DB_List =[2000, Hello, 1, 1, 88, 518, 1]


What I have tried :

def a = DBlist.pkResourceItemsID


It Gives me result
[2000]
, but I can only do this for one column at a time.

Don't know how to do for all the columns. How can achieve this?

Answer

Sql.rows(GString query) returns a List<GroovyRowResult> this is why when you make a println of your returned object you get:

[[pkResourceItemsID:2000, fieldValue:Hello, fkResourceID:1, fkCountryID:1, fkLocationID:88, fkBusinessUnitID:518, floor:1]]

If instead of this you want simply a list with only the result values you can perform the follow operation on the object returned by Sql.rows() method call as:

// for each row in the result list
def rowResults = DBlist.collect{ row ->
    // get only the column values
    row.keySet().collect { row[it] }
}

This is a generic approach since rows() returns multiple results, now you've a list of lists where each list only contains the values.

If you're interested only in the first result; get the first object in that list:

println rowResults[0] 
// [2000, Hello, 1, 1, 88, 518, 1]

Another approach: use firstRow instead

If your query it's designed to get only one result, use firstRow() instead of rows(), this way you'll have directly a GroovyRowResult instead of List<GroovyRowResult>, and then you can get all values without the column names easily:

def DBlist = con.firstRow("select a.pkResourceItemsID,b.fieldValue,a.fkResourceID,a.fkCountryID,b.fkLocationID,b.fkBusinessUnitID,b.floor from tblResourceItems a inner join tblresourceitemData b on a.pkResourceItemsID=b.fkResourceItemID where a.pkResourceItemsID='$id'" )

def rowValues = DBlist.keySet().collect { DBlist[it] }
println rowValues
// [2000, Hello, 1, 1, 88, 518, 1]