These Java examples use Polypheny JDBC Driver 3.1 and a local Polypheny server with the Prism 2.2 interface enabled. Each program creates a namespace for its own data and drops it at the end. Run them against a development instance with permission to create and drop namespaces. The names jdbc_docs_demo_rel and jdbc_docs_demo_documents must be available before you start.
Both programs read POLYPHENY_JDBC_URL, POLYPHENY_USER, and POLYPHENY_PASSWORD from the environment. Without these variables, the examples use jdbc:polypheny://localhost:20590 and the default local development account pa with an empty password.
SQL CRUD, batching, and transactions
Save this example as JdbcSqlExample.java. It uses the standard java.sql interfaces to insert two rows as a batch and update one name. It then deletes a row inside a transaction and rolls back the deletion. Both the prepared query and the SQL query through the multimodel extension return two rows.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Map;
import org.polypheny.jdbc.PolyConnection;
import org.polypheny.jdbc.multimodel.PolyRow;
import org.polypheny.jdbc.multimodel.PolyStatement;
import org.polypheny.jdbc.multimodel.RelationalResult;
import org.polypheny.jdbc.multimodel.Result;
public class JdbcSqlExample {
public static void main(String[] args) throws SQLException {
Map<String, String> env = System.getenv();
String url = env.getOrDefault("POLYPHENY_JDBC_URL", "jdbc:polypheny://localhost:20590");
String user = env.getOrDefault("POLYPHENY_USER", "pa");
String password = env.getOrDefault("POLYPHENY_PASSWORD", "");
try (Connection connection = DriverManager.getConnection(url, user, password);
Statement ddl = connection.createStatement()) {
connection.setAutoCommit(true);
ddl.executeUpdate("CREATE RELATIONAL NAMESPACE jdbc_docs_demo_rel");
try {
ddl.executeUpdate("CREATE TABLE jdbc_docs_demo_rel.employees "
+ "(id INTEGER PRIMARY KEY, name VARCHAR(40) NOT NULL)");
connection.setAutoCommit(false);
try {
try (PreparedStatement insert = connection.prepareStatement(
"INSERT INTO jdbc_docs_demo_rel.employees (id, name) VALUES (?, ?)")) {
insert.setInt(1, 1);
insert.setString(2, "Ada");
insert.addBatch();
insert.setInt(1, 2);
insert.setString(2, "Grace");
insert.addBatch();
System.out.println("Inserted: " + Arrays.toString(insert.executeBatch()));
}
try (PreparedStatement update = connection.prepareStatement(
"UPDATE jdbc_docs_demo_rel.employees SET name = ? WHERE id = ?")) {
update.setString(1, "Ada Lovelace");
update.setInt(2, 1);
System.out.println("Updated: " + update.executeUpdate());
}
connection.commit();
try (PreparedStatement delete = connection.prepareStatement(
"DELETE FROM jdbc_docs_demo_rel.employees WHERE id = ?")) {
delete.setInt(1, 2);
System.out.println("Deleted before rollback: " + delete.executeUpdate());
}
connection.rollback();
try (PreparedStatement query = connection.prepareStatement(
"SELECT id, name FROM jdbc_docs_demo_rel.employees WHERE id >= ? ORDER BY id")) {
query.setInt(1, 1);
try (ResultSet rows = query.executeQuery()) {
while (rows.next()) {
System.out.println(rows.getInt("id") + ": " + rows.getString("name"));
}
}
}
PolyStatement sql = connection.unwrap(PolyConnection.class).createPolyStatement();
Result result = sql.execute("jdbc_docs_demo_rel", "sql",
"SELECT id, name FROM employees ORDER BY id");
System.out.println("Multimodel result: " + result.getResultType());
for (PolyRow row : result.unwrap(RelationalResult.class)) {
System.out.println(row.getColumnCount() + " columns: "
+ row.getValue(0).asInt() + ", " + row.getValue(1).asString());
}
connection.commit();
} catch (SQLException failure) {
try {
connection.rollback();
} catch (SQLException rollbackFailure) {
failure.addSuppressed(rollbackFailure);
}
throw failure;
}
} finally {
// Use a separate connection so cleanup cannot commit the DML transaction.
try (Connection cleanup = DriverManager.getConnection(url, user, password);
Statement drop = cleanup.createStatement()) {
cleanup.setAutoCommit(true);
drop.executeUpdate("DROP NAMESPACE jdbc_docs_demo_rel");
}
}
}
}
}
Bind each ? placeholder by its one-based position. Each call to addBatch() queues the current parameter values. A server can report a combined update count for a prepared batch, such as [2] for the two inserts above. Use the no-argument executeQuery() for a prepared query and executeUpdate() for a prepared write. See the prepared statement reference and connection reference.
When you use PolyStatement, getResultType() identifies the actual result before you unwrap it. A PolyRow uses zero-based column indexes. See relational results.
Document queries with MQL
Save this example as JdbcDocumentExample.java. It creates a collection, inserts a document, reads a nested field, updates the document, and deletes it. The standard JDBC connection handles resource ownership. Unwrap it to PolyConnection to create the PolyStatement that sends MQL using the language identifier mongo.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import org.polypheny.jdbc.PolyConnection;
import org.polypheny.jdbc.multimodel.DocumentResult;
import org.polypheny.jdbc.multimodel.PolyStatement;
import org.polypheny.jdbc.multimodel.ScalarResult;
import org.polypheny.jdbc.types.PolyDocument;
public class JdbcDocumentExample {
private static final String NAMESPACE = "jdbc_docs_demo_documents";
public static void main(String[] args) throws SQLException {
Map<String, String> env = System.getenv();
String url = env.getOrDefault("POLYPHENY_JDBC_URL", "jdbc:polypheny://localhost:20590");
String user = env.getOrDefault("POLYPHENY_USER", "pa");
String password = env.getOrDefault("POLYPHENY_PASSWORD", "");
try (Connection connection = DriverManager.getConnection(url, user, password);
Statement ddl = connection.createStatement()) {
connection.setAutoCommit(true);
ddl.executeUpdate("CREATE DOCUMENT NAMESPACE " + NAMESPACE);
try {
PolyStatement statement = connection.unwrap(PolyConnection.class).createPolyStatement();
ScalarResult create = statement.execute(NAMESPACE, "mongo", "db.createCollection(\"products\")")
.unwrap(ScalarResult.class);
System.out.println("Create collection result: " + create.getScalar());
printWriteResult(statement, "db.products.insertOne({\"name\": \"Pencil\", "
+ "\"in_stock\": true, \"specs\": {\"color\": \"blue\"}})");
DocumentResult products = statement.execute(
NAMESPACE, "mongo", "db.products.find({\"in_stock\": true})")
.unwrap(DocumentResult.class);
for (PolyDocument product : products) {
String name = product.get("name").asString();
PolyDocument specs = product.get("specs").asDocument();
System.out.println(name + ": " + specs.get("color").asString());
}
printWriteResult(statement, "db.products.updateOne({\"name\": \"Pencil\"}, "
+ "{\"$set\": {\"in_stock\": false}})");
printWriteResult(statement, "db.products.deleteOne({\"name\": \"Pencil\"})");
} finally {
ddl.executeUpdate("DROP NAMESPACE " + NAMESPACE);
}
}
}
private static void printWriteResult(PolyStatement statement, String query) throws SQLException {
DocumentResult result = statement.execute(NAMESPACE, "mongo", query).unwrap(DocumentResult.class);
for (PolyDocument document : result) {
System.out.println("Affected documents: " + document.get("updateCount").asLong());
}
}
}
The collection creation returns a scalar. The insert, update, and delete return a document with an updateCount field, while find returns documents from the collection. Consume a DocumentResult before reusing its PolyStatement because the next execution replaces the previous statement on the server. The fields read here are present in the example data. For collections with varying fields, check the value returned by PolyDocument.get and use TypedValue.isNull() to handle explicit null values. See the multimodel extension and document metadata.
Compile and run
If you use Maven or Gradle, add the JDBC dependency and run the classes from your project. For a direct run with the release JAR, put the two .java files next to the JAR and run these commands. On Windows, use ; as the classpath separator in place of :.
javac -cp polypheny-jdbc-driver-3.1.jar JdbcSqlExample.java JdbcDocumentExample.java
java -cp .:polypheny-jdbc-driver-3.1.jar JdbcSqlExample
java -cp .:polypheny-jdbc-driver-3.1.jar JdbcDocumentExample
