Connecting to Polypheny from Scala applications is efficient and straightforward, particularly when using the Polypheny JDBC Driver. This driver provides robust performance and access to all Polypheny features via the Prism query interface. While the JDBC driver is the recommended method due to its performance and feature completeness, Scala developers can also utilize the REST interface and HTTP interface.
Prerequisites
The Polypheny JDBC Driver interfaces with the Prism query interface, which is automatically deployed with every Polypheny instance. Ensure this interface is correctly deployed by checking the deployed interfaces in the Polypheny UI under “Interfaces”.
Installation
The JDBC driver requires Java 8 or higher, and Scala applications can integrate it in the same manner.
SBT (Scala Build Tool)
Add the Polypheny JDBC Driver as a libraryDependency in your project’s build.sbt file:
libraryDependencies += "org.polypheny" % "polypheny-jdbc-driver" % "2.2" // Check for the latest version on Maven Central
Usage
Integration of the Polypheny JDBC Driver in Scala is analogous to Java. Here are the key steps:
- Open a connection:
import java.sql.DriverManager val jdbcUrl = "jdbc:polypheny://localhost/" val username = "pa" val password = "" val connection = DriverManager.getConnection(jdbcUrl, username, password) - Create a statement object to execute SQL queries:
val statement = connection.createStatement() - Execute the query and process the results:
val resultSet = statement.executeQuery("SELECT * FROM emps") while (resultSet.next()) { val id = resultSet.getInt("empid") val name = resultSet.getString("name") println(s"ID: $id, Name: $name") } - Properly close connections and other resources:
resultSet.close() statement.close() connection.close()
The above resource management can be handled more effectively in Scala using try and finally blocks, or by using the Loan Pattern to manage resources.
Examples
Example 1 - Basic Query
import java.sql.{Connection, DriverManager, ResultSet}
object PolyphenyJDBCExample extends App {
val jdbcUrl = "jdbc:polypheny://localhost/"
val username = "pa"
val password = ""
val sql = "SELECT * FROM emps"
var connection: Connection = null
try {
connection = DriverManager.getConnection(jdbcUrl, username, password)
val statement = connection.createStatement()
val resultSet = statement.executeQuery(sql)
while (resultSet.next()) {
val id = resultSet.getInt("empid")
val name = resultSet.getString("name")
println(s"ID: $id, Name: $name")
}
} catch {
case e: Exception => e.printStackTrace()
} finally {
if (connection != null) connection.close()
}
}
Example 2 - Prepared Statements
Using prepared statements in Scala to handle runtime values safely:
import java.sql._
object PreparedStatementsExample extends App {
val jdbcUrl = "jdbc:polypheny://localhost/"
val username = "pa"
val password = ""
val sql = "SELECT * FROM emps WHERE empid = ? AND name = ?"
var connection: Connection = null
try {
connection = DriverManager.getConnection(jdbcUrl, username, password)
val preparedStatement = connection.prepareStatement(sql)
preparedStatement.setInt(1, 1)
preparedStatement.setString(2, "Jane")
val resultSet = preparedStatement.executeQuery()
while (resultSet.next()) {
val id = resultSet.getInt("empid")
val name = resultSet.getString("name")
println(s"ID: $id, Name: $name")
}
} catch {
case e: Exception => e.printStackTrace()
} finally {
if (connection != null) connection.close()
}
}
Example 3 - MQL (MongoQL)
This example demonstrates how to execute a query expressed using the MongoQL (MQL) language and process the result:
import java.sql.{Connection, DriverManager}
import org.polypheny.client.jdbc.PolyConnection
import org.polypheny.client.grpc.PolyClientGRPC.PolyResult
import scala.collection.JavaConverters._
object MongoQLExample extends App {
val jdbcUrl = "jdbc:polypheny://localhost:20590/"
var connection: Connection = null
try {
connection = DriverManager.getConnection(jdbcUrl)
if (connection.isWrapperFor(classOf[PolyConnection])) {
val polyConnection = connection.unwrap(classOf[PolyConnection])
val statement = polyConnection.createPolyStatement()
val result = statement.execute("public", "mql", "db.products.find({ inStock: true })")
if (result.getResultType == PolyResult.ResultType.DOCUMENT) {
val documentResult = result.unwrap(classOf[PolyResult.DocumentResult])
val documentIterator = documentResult.getDocuments.iterator()
while (documentIterator.hasNext) {
val polyDocument = documentIterator.next()
println(polyDocument.get("product_name"))
}
}
}
} catch {
case e: Exception => e.printStackTrace()
} finally {
if (connection != null) connection.close()
}
}
Example 4 - Metadata
This example demonstrates how to retrieve database metadata, such as the list of tables, using the JDBC API in Scala:
import java.sql.{Connection, DriverManager, DatabaseMetaData}
object MetadataExample extends App {
val jdbcUrl = "jdbc:polypheny://localhost/"
val username = "pa"
val password = ""
var connection: Connection = null
try {
connection = DriverManager.getConnection(jdbcUrl, username, password)
val metaData = connection.getMetaData
val resultSet = metaData.getTables(null, null, "%", Array("TABLE"))
println("Table Names:")
while (resultSet.next()) {
val tableName = resultSet.getString("TABLE_NAME")
println(tableName)
}
} catch {
case e: Exception => e.printStackTrace()
} finally {
if (connection != null) connection.close()
}
}
For comprehensive details on the types of metadata available, refer to the following documentation pages:
