Querying Polypheny From Java

Connecting to Polypheny from Java applications is straightforward and efficient, especially when using the Polypheny JDBC Driver. This driver offers robust performance and access to the full range of Polypheny features via the Prism query interface.

While other connection options exist, such as the REST interface and HTTP interface, the JDBC driver is the recommended method for Java applications due to its performance and feature completeness. Examples and guidance for using the REST interface can be found in the REST query interface documentation.

Prerequisites

The Polypheny JDBC Driver connects to the Prism Query Interface which is deployed with every Polypheny instance per default. However, if you run into problems, make sure that the interface is correctly deployed. You can check the currently deployed interfaces in the Polypheny user interface under “Interfaces”.

Installation

The Polypheny JDBC driver is compatible with Java version 8 or higher. You can either manually download and import the driver or install it using Maven or Gradle.

Maven

Add the Polypheny JDBC Driver as a dependency in your project’s pom.xml file:

<dependencies>
    <dependency>
        <groupId>org.polypheny</groupId>
        <artifactId>polypheny-jdbc-driver</artifactId>
        <version>2.2</version> <!-- Check Maven Central for the latest version -->
    </dependency>
</dependencies>

Gradle

For Gradle-based projects, include the dependency in your build.gradle file:

dependencies {
    implementation 'org.polypheny:polypheny-jdbc-driver:2.2' // Verify the latest version on Maven Central
}
The version number is for illustration purposes only. Please always check on Maven Central for the latest version.

Usage

Since the Polypheny JDBC driver is fully compliant with the JDBC standard and implements the java.sql interfaces, the process is straightforward. Integrating the Polypheny JDBC Driver into your Java application involves several key steps:

  1. Open a connection to your Polypheny database:
    Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
    
  2. Create a statement object to execute SQL queries:
    Statement statement = connection.createStatement();
    
  3. Execute the query:
    ResultSet resultSet = statement.executeQuery(sql);
    
  4. Process the result of the query:
    while (resultSet.next()) { ... }
    
  5. Properly close connections and other resources to prevent leaks:
    resultSet.close(); statement.close(); connection.close();
    

The last step (closing connections clean-up resources) can be automated by using try-with-resources. This can be seen in the examples below.

Detailed information on how to use the Polypheny JDBC Driver can be found in the Driver documentation.

Example 1 - SQL

Here’s a basic example for executing establishing a connection end executing a simple SQL query:

import java.sql.*;

public class PolyphenyJDBCExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:polypheny://localhost/";
        String username = "pa";
        String password = "";

        String sql = "SELECT * FROM emps";

        try {
            try (
                // Establish a connection
                Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
                // Create a statement
                Statement statement = connection.createStatement();
                // Process results
                ResultSet resultSet = statement.executeQuery(sql)
            ) {
                while (resultSet.next()) {
                    int id = resultSet.getInt("empid");
                    String name = resultSet.getString("name");
                    System.out.println("ID: " + id + ", Name: " + name);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example 2 - Prepared Statement in SQL

When queries including values defined at runtime are required, prepared statement should be used. The use of string operations to generate statements is strongly discouraged as this makes your code vulnerable to SQL injection attacks.

public static void main(String[] args) {
        String jdbcUrl = "jdbc:polypheny://localhost/";
        String username = "pa";
        String password = "";

        // Using a placeholder (?) for the parameters
        String sql = "SELECT * FROM emps WHERE empid = ? and name = ?";

        try {
            try (
                // Establish a connection
                Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
                // Create a prepared statement
                PreparedStatement preparedStatement = connection.prepareStatement(sql)
            ) {
                // Assign values to all placeholders
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, 'Jane');

                // Execute the query
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        int id = resultSet.getInt("empid");
                        String name = resultSet.getString("name");
                        System.out.println("ID: " + id + ", Name: " + name);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Example 3 - MQL

Besides the standards compliant interface for executing SQL queries and processing relational result sets, the Polypheny JDBC driver also supports other query languages and data models. Below a statement in the MongoDB Query Language is executed.

try (
    // Establish connection
    Connection connection = DriverManager.getConnection("jdbc:polypheny://localhost:20590");
) {
    // Upgrade connection for multi model support
    if (connection.isWrapperFor(PolyConnection.class)) {
        PolyConnection polyConnection = connection.unwrap(PolyConnection.class);
        // Create a statment
        PolyStatement polyStatement = polyConnection.createPolyStatement();
        // Execute the query
        Result result = polyConnection.execute("public", "mql", "db.products.find({ inStock: true })");
        // Process the results
        if (result.getResultType = ResultType.DOCUMENT) {
            DocumentResult documentResult = result.unwrap(DocumentResult.class);
            Iterator<PolyDocument> documentIterator = documentResult.iterator();
            while (documentIterator.hasNext()) {
                PolyDocument polyDocument = documentIterator.next();
                // print the field "product_name" of all retrieved documents
                System.out.println(polyDocument.get("product_name"))
            }
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Example 4 - Cypher

Another supported data model is the Labeled Property Graph (LPG) model. One language used to query data in that model is the Cypher Query Language. Below, a statement in the LPG model using cypher is executed.

try (
    // Establish connection
    Connection connection = DriverManager.getConnection("jdbc:polypheny://localhost:20590");
) {
    // Upgrade connection for multi model support
    if (connection.isWrapperFor(PolyConnection.class)) {
        PolyConnection polyConnection = connection.unwrap(PolyConnection.class);
        // Create a statment
        PolyStatement polyStatement = polyConnection.createPolyStatement();
        // Execute the query
        GraphResult result = polyStatement.execute("cyphertest", "cypher", "MATCH (n:Person) RETURN n ORDER BY n.id").unwrap(GraphResult.class);
        // Process the results
        Iterator<PolyGraphElement> elements = result.iterator();
        while (elements.hasNext()) {
            PolyGraphElement element = elements.next();
            System.out.println("Name: " + element.get("name").asString());
            System.out.println("Labels: " + element.getLabels());
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Example 5 - Metadata

Another feature of the Polypheny JDBC driver is the option to request a variety of metadata from the connected Polypheny instance. An example on how to retrieve a list of all tables is shown below. For a complete list of all metadata available, please take a look at the corresponding documentation pages:

String jdbcUrl = "jdbc:polypheny://localhost/";
String username = "pa";
String password = ""
try {
    // Establish a connection
    try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
        // Get a metadata object
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        
        // Retrieve metadata
        String[] types = {"TABLE"};
        ResultSet resultSet = databaseMetaData.getTables(null, null, "%", types);
        
        // Process the tesults
        System.out.println("Table Names:");
        while (resultSet.next()) {
            String tableName = resultSet.getString(3);
            System.out.println(tableName);
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Best Practices

  • Connection Management: Use try-with-resources or similar mechanisms to ensure that database connections, statements, and result sets are closed properly to avoid resource leaks.
  • Error Handling: Implement comprehensive error handling to manage SQL exceptions and ensure your application can recover from or report database access issues effectively.
  • Credential Security: Securely manage database credentials, avoiding hard-coded values in source code for production environments.
© Polypheny GmbH. All Rights Reserved.