Use the Polypheny JDBC driver to run SQL through the standard java.sql interfaces. Use the multimodel extension for MongoQL and Cypher.
Requirements and installation
Install the JDBC driver on Java 8 or newer and enable the Prism interface in Polypheny. The examples use the default TCP port 20590 and the default local development credentials pa with an empty password. The Cypher example requires Prism 2.3 support from the driver and server.
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. Iterate DocumentResult to read the documents.
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.multimodel.PolyStatement;
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);
try (PolyStatement statement = polyConnection.createPolyStatement()) {
DocumentResult documents = statement
.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;
Query a graph with Cypher
Create a PolyStatement from the unwrapped PolyConnection and pass the graph namespace and language identifier cypher to execute. Iterate GraphResult for a node-only projection such as RETURN n. For a mixed projection such as RETURN n AS person, n.name AS name, iterate RelationalResult and read the node and name by their column aliases.
The JDBC graph results guide provides a graph fixture and a Java program you can run with either result format.