Querying Polypheny From Scala

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
The version number is for illustration purposes only. Always check Maven Central for the latest version.

Usage

Integration of the Polypheny JDBC Driver in Scala is analogous to Java. Here are the key steps:

  1. Open a connection:
    import java.sql.DriverManager
    val jdbcUrl = "jdbc:polypheny://localhost/"
    val username = "pa"
    val password = ""
    val connection = DriverManager.getConnection(jdbcUrl, username, password)
    
  2. Create a statement object to execute SQL queries:
    val statement = connection.createStatement()
    
  3. 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")
    }
    
  4. 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:

© Polypheny GmbH. All Rights Reserved.