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.