Calling Groovy via JDBC

Calling Groovy via JDBC

NOTE: In addition to the driver JAR itself, the full Groovy language package has to be available on the classpath.

The following sample demonstrates fetching JSON data from Google Finance Internet service and transforming it into a tabular format. (NOTE: This is just a technical sample to demonstrate the capabilities of the tool: please consult Google Use of Service Conditions before you actually use any data from their API!)

Use the following URL to establish the connection: jdbc:dyna4jdbc:scriptengine:groovy:classpath=<groovy JAR path>

Important: in the URL above, replace <groovy JAR path> with the actual path to your groovy-all-X.X.X.jar file. Otherwise the driver cannot locate Groovy runtime and will not be able to establish a Groovy script session.

For this sample, we execute the Groovy script below. You can see how straightforward it is: it simply fetches data and outputs the desired results to the standard output via println method:

import groovy.json.JsonSlurper

def printRow(String... values) { println values.join("\t") }

def jsonData = new URL("http://www.google.com/finance/info?infotype=infoquoteall&q=NASDAQ:AAPL,IBM,MSFT,GOOG").text.replaceFirst("//", "")
def data = new JsonSlurper().parseText(jsonData)

printRow "Ticker::", "Name::", "Open::", "Close::", "Change::" // first line with the formatting header
data.each { printRow it["t"], it["name"], it["op"], it["l_cur"], it["c"] }

Full Java source code (Remember: both driver JAR and Groovy JARs have to be on the classpath):

package samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class GroovyDemo {

    private static final String GROOVY_SCRIPT = ""
            + "	import groovy.json.JsonSlurper														\n"
            + "																						\n"
            + " def printRow(String... values) { println values.join(\"\t\") }						\n"
            + " def jsonData = new URL('http://www.google.com/finance/info?"
                + "infotype=infoquoteall&q=NASDAQ:AAPL,IBM,MSFT,GOOG').text.replaceFirst('//', '')	\n"
            + " def data = new JsonSlurper().parseText(jsonData)									\n"
            + " printRow 'Ticker::', 'Name::', 'Open::', 'Close::', 'Change::'						\n"
            + " data.each { printRow it['t'], it['name'], it['op'], it['l_cur'], it['c'] } 			\n";

    public static void main(String[] args) throws SQLException {

        try (Connection connection = DriverManager.getConnection("jdbc:dyna4jdbc:scriptengine:groovy")) {

            try (Statement statement = connection.createStatement()) {
                boolean results = statement.execute(GROOVY_SCRIPT);
                while (results) {
                    try (ResultSet rs = statement.getResultSet()) {

                        printResultSetWithHeaders(rs);
                    }

                    results = statement.getMoreResults();
                }
            }
        }
    }

    private static void printResultSetWithHeaders(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        final int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnLabel = metaData.getColumnLabel(i);
            int columnDisplaySize = metaData.getColumnDisplaySize(i);
            String formatStr = "%" + columnDisplaySize + "s | ";
            System.out.format(formatStr, columnLabel);
        }
        System.out.println();

        for (int i = 1; i <= columnCount; i++) {
            int columnDisplaySize = metaData.getColumnDisplaySize(i);
            String formatStr = "%" + columnDisplaySize + "s | ";
            System.out.format(String.format(formatStr, "").replace(' ', '-'));
        }
        System.out.println();

        while (rs.next()) {

            for (int i = 1; i <= columnCount; i++) {
                int columnDisplaySize = metaData.getColumnDisplaySize(i);
                String formatStr = "%" + columnDisplaySize + "s | ";
                System.out.format(formatStr, rs.getString(i));
            }
            System.out.println();
        }
    }

}