I am trying to fetch entire data from a table and the outcome should be a comma separated string. Here is my code which is working fine.
import groovy.sql.Sql;
import java.sql.ResultSet;
def temp="";
def temp1="";
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username", "password", "oracle.jdbc.driver.OracleDriver")
sql.eachRow("select * FROM employee") {
temp1=it.toRowResult().values().join(", ")
if(temp=="")
{
temp=temp1;
}
else
{
temp=temp+"\n"+temp1
}
}
Kindly suggest if there is any better approach to handle this requirement in Groovy.
The keep it simple solution is the following:
import groovy.sql.Sql;
import java.sql.ResultSet;
Sql sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username", "password", "oracle.jdbc.driver.OracleDriver")
StringBuilder builder = new StringBuilder()
sql.eachRow("select * FROM employee") { row ->
builder.append( "${row.employeeId}," ) // no idea what your column names are
}
return builder.toString() // should trim trailing comma but I'll save that for you
You may also be able to do this with inject(), but I've not tried that on a SQL result set before.