protea protea - 1 year ago 129
Groovy Question

Returning only first row when I use

When I use the same query using MySQL Workbench it seems to work properly and returns all rows(which is 2) . But When I do it using groovy it gives me only the 1st row. Not sure What I am doing wrong here.

Here is my code:

context.setProperty("Database", new Database());

import groovy.sql.Sql
import java.sql.*
import com.jcraft.jsch.JSch
import com.jcraft.jsch.Session

class Database{
def queryDatabase(sqlQuery, table)
// ssh login
String sshHost = 'test'
String sshUser = 'test'
String sshPass = 'test'
int sshPort = 22

// database login
String targetHost = 'localhost'
String targetUser = 'test'
String targetPass = 'test'
int targetPort = 3306
int lport = 4322

JSch jsch = new JSch();
Session session = jsch.getSession(sshUser, sshHost, sshPort);
session.setConfig("StrictHostKeyChecking", "no");
System.out.println("Establishing Connection...");
try {
int assinged_port=session.setPortForwardingL(lport, targetHost, targetPort);

Connection con = null;
String driver = "org.mariadb.jdbc.Driver";
String connectionString = "jdbc:mariadb://" + targetHost +":" + lport + "/";
con = DriverManager.getConnection(connectionString, targetUser, targetPass);
Statement st = con.createStatement();
java.sql.ResultSet rs = st.executeQuery(sqlQuery);
while ( {
String value = rs.getString(table);
return (value)

Answer Source

This code seems to different from your Java version

    while ( {
        String value = rs.getString(table)
        return (value)

This code returns the value immediately, instead of storing it in a temp variable and returning the whole list, so you could write something like this instead:

    def result = []

    while ( {
        result << rs.getString(table)

    return result

However, if you are going to rewrite this code to Groovy, why not use groovy.sql.Sql instead?

That way you can do things like:

List authors = sql.rows('SELECT firstname, lastname FROM Author')

(from the documentation here)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download