protea protea - 2 days ago 5
Groovy Question

Returning only first row when I use rs.next

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.setPassword(sshPass);
session.setConfig("StrictHostKeyChecking", "no");
System.out.println("Establishing Connection...");
try {
session.connect();
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 (rs.next()) {
String value = rs.getString(table);
return (value)
}
}finally{
session.disconnect()
null
}
}
}

Answer

This code seems to different from your Java version

    while (rs.next()) {
        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 (rs.next()) {
        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)

Comments