Querying Polypheny from Java

Use the Polypheny JDBC driver to run SQL through the standard java.sql interfaces and MongoQL through the driver’s multimodel extension.

Requirements and installation

JDBC driver 3.1 runs on Java 8 or newer and connects to Polypheny through the Prism 2.2 query interface. Check the running interfaces under Interfaces in Polypheny. The examples use the default TCP port 20590 and the default local development credentials pa with an empty password.

Add the driver from Maven Central to your Maven project’s pom.xml.

<dependencies>
    <dependency>
        <groupId>org.polypheny</groupId>
        <artifactId>polypheny-jdbc-driver</artifactId>
        <version>3.1</version>
    </dependency>
</dependencies>

For a Gradle project, add this to the Java project’s build.gradle.

repositories {
    mavenCentral()
}

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

You can also add the JAR from GitHub Releases to your classpath.

Query a table and list relational metadata

Run this SQL in the public namespace of a development database to create the example table.

CREATE TABLE jdbc_demo_employees (id INTEGER NOT NULL, name VARCHAR(40), PRIMARY KEY (id));
INSERT INTO jdbc_demo_employees VALUES (1, 'Ada'), (2, 'Grace');

Save the example as JdbcSqlExample.java. The JDBC URL selects the public namespace. Set POLYPHENY_JDBC_URL if your endpoint differs, and set POLYPHENY_USER and POLYPHENY_PASSWORD for your database account. Bind runtime values to ? placeholders using the setter that matches the value’s JDBC type. Parameter positions start at 1. Try-with-resources closes the result sets, statement, and connection.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcSqlExample {
    public static void main(String[] args) throws SQLException {
        String url = System.getenv().getOrDefault(
                "POLYPHENY_JDBC_URL", "jdbc:polypheny://localhost:20590/public");
        String user = System.getenv().getOrDefault("POLYPHENY_USER", "pa");
        String password = System.getenv().getOrDefault("POLYPHENY_PASSWORD", "");

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            String sql = "SELECT id, name FROM jdbc_demo_employees WHERE id = ?";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setInt(1, 1);
                try (ResultSet rows = statement.executeQuery()) {
                    while (rows.next()) {
                        System.out.println(rows.getInt("id") + " " + rows.getString("name"));
                    }
                }
            }

            try (ResultSet tables = connection.getMetaData().getTables(
                    null, "public", "jdbc_demo_employees", null)) {
                while (tables.next()) {
                    System.out.println(tables.getString("TABLE_NAME"));
                }
            }
        }
    }
}

Use Connection.createStatement() and Statement.executeQuery(String sql) for a fixed SQL query. For writes, use executeUpdate() and check the returned affected-row count. See the JDBC statement reference and the database metadata reference.

Query a collection and inspect its metadata

Create a document namespace using SQL.

CREATE DOCUMENT NAMESPACE jdbc_demo_documents;

Switch to the jdbc_demo_documents namespace in the MongoQL interface and run these statements.

db.createCollection("products")
db.products.insertOne({product_name: "Pencil", in_stock: true, specs: {color: "blue"}})

Save this example as JdbcDocumentExample.java and run it with the same connection settings as the SQL example. unwrap(PolyConnection.class) exposes the driver’s multimodel API. PolyStatement.execute(namespace, language, statement) runs a query in the specified namespace and language. Use DocumentResult for document rows. JDBC 3.1 also supports relational and scalar results through this API. Queries returning graph frames require a client that supports that result type.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.polypheny.jdbc.PolyConnection;
import org.polypheny.jdbc.multimodel.DocumentResult;
import org.polypheny.jdbc.types.PolyDocument;
import org.polypheny.jdbc.dependency.prism.Document;
import org.polypheny.jdbc.dependency.prism.DocumentField;
import org.polypheny.jdbc.dependency.prism.DocumentIndex;
import org.polypheny.jdbc.dependency.prism.Entity;

public class JdbcDocumentExample {
    public static void main(String[] args) throws SQLException {
        String url = System.getenv().getOrDefault(
                "POLYPHENY_JDBC_URL", "jdbc:polypheny://localhost:20590/public");
        String user = System.getenv().getOrDefault("POLYPHENY_USER", "pa");
        String password = System.getenv().getOrDefault("POLYPHENY_PASSWORD", "");
        String namespace = "jdbc_demo_documents";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            PolyConnection polyConnection = connection.unwrap(PolyConnection.class);
            DocumentResult documents = polyConnection.createPolyStatement()
                    .execute(namespace, "mongo", "db.products.find({in_stock: true})")
                    .unwrap(DocumentResult.class);
            for (PolyDocument document : documents) {
                System.out.println(document.get("product_name").asString());
            }

            for (Entity entity : polyConnection.getPrismInterfaceClient()
                    .searchEntities(namespace, "products", polyConnection.getTimeout())) {
                if (!entity.hasDocument()) {
                    continue;
                }
                Document metadata = entity.getDocument();
                System.out.println(metadata.getNamespaceName() + "." + metadata.getCollectionName());
                for (DocumentField field : metadata.getFieldsList()) {
                    System.out.printf("%s %s (optional=%s)%n",
                            field.getFieldPath(), field.getTypeName(), field.getIsOptional());
                }
                if (metadata.hasSampleDocumentJson()) {
                    System.out.println(metadata.getSampleDocumentJson());
                }
                for (DocumentIndex index : metadata.getDocumentIndexesList()) {
                    System.out.printf("%s %s (unique=%s)%n",
                            index.getFieldPath(), index.getIndexType(), index.getUnique());
                }
            }
        }
    }
}

searchEntities(namespace, pattern, timeout) looks up entities in one namespace. Pass null as the pattern to list all entities there. The JDBC JAR includes the generated Prism classes under org.polypheny.jdbc.dependency.prism. These classes give you the collection name, optional sample JSON, field paths with inferred types and optionality, and indexes reported by the store. Treat inferred fields and the sample as a description of sampled data. See the JDBC collection metadata reference and Prism collection metadata for the method and message definitions.

Run the following SQL in the development database to remove the fixtures when finished.

DROP TABLE jdbc_demo_employees;
DROP NAMESPACE jdbc_demo_documents;
© Polypheny GmbH. All Rights Reserved.