Querying Polypheny From Kotlin

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

While other connection methods like the REST interface and HTTP interface are available, the JDBC driver remains the recommended approach for Kotlin applications due to its performance and comprehensive feature set. Information on using the REST interface can be found in the REST query interface documentation.

Prerequisites

The Polypheny JDBC Driver interfaces with the Prism query interface, which is available by default with every Polypheny deployment. If issues arise, ensure the interface is correctly deployed by checking the deployed interfaces in the Polypheny UI under “Interfaces”.

Installation

The Polypheny JDBC driver requires Kotlin to be set up with a Java 8 or higher environment. The driver can be manually downloaded and included, or managed through Maven or Gradle.

Maven

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

<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 projects, include the dependency in your build.gradle:

dependencies {
    implementation("org.polypheny:polypheny-jdbc-driver:2.2") // Check for 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

The JDBC standard and implements the java.sql interfaces:

  1. Open a connection:
    val connection = DriverManager.getConnection(jdbcUrl, username, password)
    
  2. Create a statement to execute queries:
    val statement = connection.createStatement()
    
  3. Execute the query:
    val resultSet = statement.executeQuery(sql)
    
  4. Process the results:
    while (resultSet.next()) { ... }
    
  5. Close resources efficiently:
    resultSet.close(); statement.close(); connection.close();
    

Using Kotlin’s use function can manage resources more effectively, as shown below.

Examples

Example 1 - Basic SQL Query

import java.sql.*
import java.util.logging.Level
import java.util.logging.Logger

fun main() {
    val logger = Logger.getLogger("DatabaseQueryLogger")
    val jdbcUrl = System.getenv("POLYPHENY_JDBC_URL") ?: "jdbc:polypheny://localhost/"
    val username = System.getenv("DB_USERNAME") ?: "pa"
    val password = System.getenv("DB_PASSWORD") ?: ""

    val sql = "SELECT * FROM emps"

    try {
        DriverManager.getConnection(jdbcUrl, username, password).use { connection ->
            connection.createStatement().use { statement ->
                statement.executeQuery(sql).use { resultSet ->
                    while (resultSet.next()) {
                        val employeeId = resultSet.getInt("empid")
                        val employeeName = resultSet.getString("name")
                        println("ID: $employeeId, Name: $employeeName")
                    }
                }
            }
        }
    } catch (e: SQLTimeoutException) {
        logger.log(Level.SEVERE, "Database connection timeout.", e)
    } catch (e: SQLException) {
        logger.log(Level.SEVERE, "SQL exception occurred.", e)
    } catch (e: Exception) {
        logger.log(Level.SEVERE, "Unexpected error.", e)
    }
}

Example 2 - Prepared Statements

Use Kotlin to secure dynamic SQL statements against injection:

import java.sql.*
import java.util.logging.Level
import java.util.logging.Logger

fun main() {
    val logger = Logger.getLogger("DatabaseQueryLogger")
    val jdbcUrl = "jdbc:polypheny://localhost/"
    val username = "pa"
    val password = ""

    val sql = "SELECT * FROM emps WHERE empid = ? and name = ?"

    try {
        DriverManager.getConnection(jdbcUrl, username, password).use { connection ->
            connection.prepareStatement(sql).use { preparedStatement ->
                preparedStatement.setInt(1, 1)
                preparedStatement.setString(2, "Jane")

                preparedStatement.executeQuery().use { resultSet ->
                    while (resultSet.next()) {
                        val id = resultSet.getInt("empid")
                        val name = resultSet.getString("name")
                        println("ID: $id, Name: $name")
                    }
                }
            }
        }
    } catch (e: SQLException) {
        logger.log(Level.SEVERE, "SQL exception occurred", e)
    } catch (e: Exception) {
        logger.log(Level.SEVERE, "Unexpected error", e)
    }
}

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:

import java.sql.*
import java.util.logging.Level
import java.util.logging.Logger

fun main() {
    val jdbcUrl = "jdbc:polypheny://localhost:20590"

    try {
        DriverManager.getConnection(jdbcUrl).use { connection ->
            // Check and cast to PolyConnection for multi-model support
            val polyConnection = connection.unwrap(PolyConnection::class.java)
            polyConnection?.let {
                // Create a statement
                val polyStatement = it.createPolyStatement()

                // Execute the query
                val result = it.execute("public", "mql", "db.products.find({ inStock: true })")

                // Process the results
                if (result.resultType == ResultType.DOCUMENT) {
                    val documentResult = result.unwrap(DocumentResult::class.java)
                    documentResult?.iterator()?.forEach { polyDocument ->
                        // Print the field "product_name" of all retrieved documents
                        println(polyDocument.get(PolyString("product_name")))
                    }
                }
            }
        }
    } catch (e: SQLException) {
        e.printStackTrace()
    } catch (e: Exception) {
        e.printStackTrace()
    }
}

Example 4 - Metadata

Another powerful features of the Polypheny JDBC driver is the capability to access a variety of metadata from a connected Polypheny instance. The following Kotlin example demonstrates how to retrieve a list of all tables in the database. For comprehensive details on the types of metadata available, refer to the following documentation pages:

import java.sql.DriverManager
import java.sql.SQLException

fun main() {
    val jdbcUrl = "jdbc:polypheny://localhost/"
    val username = "pa"
    val password = ""

    try {
        // Establish a connection
        DriverManager.getConnection(jdbcUrl, username, password).use { connection ->
            // Get metadata object
            val databaseMetaData = connection.metaData

            // Specify the type of object; in this case, we're interested in tables
            val types = arrayOf("TABLE")
            // Retrieve metadata
            val resultSet = databaseMetaData.getTables(null, null, "%", types)

            // Process results
            println("Table Names:")
            while (resultSet.next()) {
                val tableName = resultSet.getString(3)
                println(tableName)
            }
        }
    } catch (e: SQLException) {
        e.printStackTrace()
    }
}

Best Practices

  1. Connection Management: Utilize Kotlin’s use function to manage database connections, statements, and result sets efficiently. This ensures that all resources are closed properly and helps to avoid resource leaks.

  2. Error Handling: Implement comprehensive error handling to manage SQL exceptions effectively. This allows your application to recover from or report database access issues, minimizing downtime and maintaining operational efficiency.

  3. Credential Security: Securely manage database credentials by avoiding hard-coded values in source code. Opt for environment variables or configuration files to store sensitive information, enhancing security in production environments.

  4. Efficient Use of Kotlin Features: Make use of Kotlin’s language features such as safe calls (?.), which help prevent null pointer exceptions, and string templates for easier and more readable string manipulation.

© Polypheny GmbH. All Rights Reserved.