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 which 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.0</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.0' // Verify the latest version on Maven Central
}
Usage
Since the Polypheny JDBC driver is fully compliant with the JDBC standard and implements the java.sql interfaces, the process is straight forward. Integrating the Polypheny JDBC Driver into your Java application involves several key steps:
- Open a connection to your Polypheny database:
Connection connection = DriverManager.getConnection(jdbcUrl, username, password); - Create a statement object to execute SQL queries:
Statement statement = connection.createStatement(); - Execute the query:
ResultSet resultSet = statement.executeQuery(sql); - Process the result of the query:
while (resultSet.next()) { ... } - 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 bellow.
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 querries 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. The example below shows how to exectute a query expressed using the MongoQL (MQL) query language and processing the result:
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(new PolyString("product_name")))
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
Example 4 - 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.
