Querying Polypheny from Groovy

Use the Polypheny JDBC driver to run SQL from Groovy. Groovy’s resource handling works with JDBC connections, statements, and result sets. The driver also provides multi-model extensions for MongoQL.

Set up the project

Use JDK 17, Gradle 8.10, and a running Polypheny instance with the Prism 2.2 query interface enabled. For a local server the default TCP port is 20590 and the default credentials are pa with an empty password.

In a new project directory, create an empty settings.gradle. Add the project configuration below to build.gradle.

plugins {
    id 'groovy'
    id 'application'
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.apache.groovy:groovy:4.0.29'
    implementation 'org.polypheny:polypheny-jdbc-driver:3.1'
}

application {
    mainClass = 'PolyphenyExample'
}

Run SQL and read metadata

Save this script as src/main/groovy/PolyphenyExample.groovy.

import java.sql.DriverManager

String url = 'jdbc:polypheny://localhost:20590/'
DriverManager.getConnection(url, 'pa', '').withCloseable { connection ->
    connection.prepareStatement('SELECT CAST(? AS VARCHAR(32)) AS product').withCloseable { statement ->
        statement.setString(1, 'Keyboard')
        statement.executeQuery().withCloseable { rows ->
            while (rows.next()) {
                println rows.getString('product')
            }
        }
    }

    connection.metaData.schemas.withCloseable { schemas ->
        while (schemas.next()) {
            println schemas.getString('TABLE_SCHEM')
        }
    }
}

Run the example from the project directory.

gradle run

It prints Keyboard followed by the schema names visible on the connection. Use prepared-statement parameters for values supplied by users.

Query a document collection

Create a document namespace named docs_jdbc_catalog in Polypheny. In a MongoQL console for that namespace, create the collection and add a document.

db.createCollection("products");
db.products.insertOne({name: "Keyboard", inStock: true});

Save the following as src/main/groovy/PolyphenyExample.groovy and run gradle run again.

import java.sql.DriverManager
import org.polypheny.jdbc.PolyConnection
import org.polypheny.jdbc.multimodel.DocumentResult

String url = 'jdbc:polypheny://localhost:20590/'
DriverManager.getConnection(url, 'pa', '').withCloseable { connection ->
    def statement = connection.unwrap(PolyConnection).createPolyStatement()
    def result = (DocumentResult) statement.execute(
            'docs_jdbc_catalog', 'mongo', 'db.products.find({inStock: true})')
    result.each { document ->
        println document.get('name').asString()
    }
}

The query returns a document result. Read the typed value with asString() and consume the rows while the connection is open. See the Java integration guide for additional JDBC examples.

© Polypheny GmbH. All Rights Reserved.