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
}
Usage
The JDBC standard and implements the java.sql interfaces:
- Open a connection:
val connection = DriverManager.getConnection(jdbcUrl, username, password) - Create a statement to execute queries:
val statement = connection.createStatement() - Execute the query:
val resultSet = statement.executeQuery(sql) - Process the results:
while (resultSet.next()) { ... } - 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
-
Connection Management: Utilize Kotlin’s
usefunction to manage database connections, statements, and result sets efficiently. This ensures that all resources are closed properly and helps to avoid resource leaks. -
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.
-
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.
-
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.
