Querying Polypheny From Groovy

On this page

Prerequisites
Installation
Usage
Examples

Using the Polypheny JDBC Driver simplifies connecting to Polypheny from Groovy applications. This driver provides full access to Polypheny’s features through the Prism query interface. While other interfaces like REST and HTTP are available, the JDBC driver is preferred for its comprehensive feature set and performance.

Prerequisites

Ensure the Prism query interface is active in your Polypheny instance, which is accessible under “Interfaces” in the Polypheny UI.

Installation

Polypheny JDBC driver requires Groovy and Java 8 or later. Install the driver manually from GitHub or use Gradle:

Gradle

Add this dependency to your build.gradle:

dependencies {
    implementation 'org.polypheny:polypheny-jdbc-driver:2.0' // Replace with the latest version from Maven Central
}

Note: Check Maven Central to ensure you have the latest version.

Usage

Here’s how to integrate the Polypheny JDBC driver with your Groovy script:

Establish a connection:

def connection = DriverManager.getConnection("jdbc:polypheny://localhost/", "username", "password")

Execute SQL queries:

def statement = connection.createStatement()
def resultSet = statement.executeQuery("SELECT * FROM emps")

Process results and clean up:

while (resultSet.next()) {
    println("ID: ${resultSet.getInt('empid')}, Name: ${resultSet.getString('name')}")
}
resultSet.close(); statement.close(); connection.close();

Examples

Basic SQL Query

import java.sql.*

String jdbcUrl = "jdbc:polypheny://localhost/"
String username = "pa"
String password = ""

String sql = "SELECT * FROM emps"

try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
     Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {
    while (resultSet.next()) {
        println("ID: ${resultSet.getInt('empid')}, Name: ${resultSet.getString('name')}")
    }
} catch (SQLException e) {
    e.printStackTrace()
}

Using Prepared Statements

try (Connection connection = DriverManager.getConnection("jdbc:polypheny://localhost/", "pa", "");
     PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM emps WHERE empid = ? and name = ?")) {
    preparedStatement.setInt(1, 1)
    preparedStatement.setString(2, 'Jane')
    ResultSet resultSet = preparedStatement.executeQuery()
    while (resultSet.next()) {
        println("ID: ${resultSet.getInt('empid')}, Name: ${resultSet.getString('name')}")
    }
    resultSet.close()
} catch (SQLException e) {
    e.printStackTrace()
}

Certainly! Here are examples 3 and 4 adapted for Scala, focusing on using the Polypheny JDBC driver for more advanced scenarios, including handling different types of queries and fetching metadata.

Executing a MQL (MongoQL) Query

try (Connection connection = DriverManager.getConnection("jdbc:polypheny://localhost:20590")) {
    if (connection.isWrapperFor(PolyConnection.class)) {
        PolyConnection polyConnection = connection.unwrap(PolyConnection.class)
        PolyStatement polyStatement = polyConnection.createPolyStatement()
        Result result = polyConnection.execute("public", "mql", "db.products.find({ inStock: true })")
        if (result.getResultType() == ResultType.DOCUMENT) {
            DocumentResult documentResult = result.unwrap(DocumentResult.class)
            documentResult.iterator().each {
                println("Product Name: ${it.get(new PolyString('product_name'))}")
            }
        }
    }
} catch (SQLException e) {
    e.printStackTrace()
}

Retrieving Metadata

This example demonstrates how to retrieve database metadata, such as the list of tables, using the JDBC API in Scala:

try (Connection connection = DriverManager.getConnection("jdbc:polypheny://localhost/", "pa", "")) {
    DatabaseMetaData databaseMetaData = connection.getMetaData()
    ResultSet resultSet = databaseMetaData.getTables(null, null, "%", ['TABLE'] as String[])
    while (resultSet.next()) {
        println("Table Name: ${resultSet.getString(3)}")
    }
    resultSet.close()
} catch (SQLException e) {
    e.printStackTrace()
}

For comprehensive details on the types of metadata available, refer to the following documentation pages:

DataGrip

Java

Quick Search Results