Wednesday, 18 September 2013

Using Scala to connect to Greenplum

In this demo we use a basic Scala program to connect to Greenplum using the Postgres JDBC driver.

1. Create a scala program as shown below.

ScalaGreenplumDemo.scala

  
import java.sql.DriverManager
import java.sql.Connection

object ScalaGreenplumDemo {

  def main(args: Array[String]) {
    val driver = "org.postgresql.Driver"
    val url = "jdbc:postgresql://127.0.0.1:5432/gpadmin"
    val username = "pas"
    val password = "pas"

    var connection:Connection = null

    try {
      // make the connection
      Class.forName(driver)
      connection = DriverManager.getConnection(url, username, password)

      // create the statement, and run the select query
      val statement = connection.createStatement()
      val resultSet = statement.executeQuery("select * from scott.emp;")
      while ( resultSet.next() ) {
        val empno = resultSet.getString("empno")
        val ename = resultSet.getString("ename")
        println("empno=" + empno + ", ename = " + ename)
      }
    } catch {
      case e: Throwable => e.printStackTrace
    }
    connection.close()
  }

}

2. Compile it ensuring you have the Postgres JDBC jar file in the classpath which in this example is the directory where we are running from.

export CUR_DIR=`pwd`
scalac -classpath $CUR_DIR/postgresql-9.2-1002.jdbc4.jar:. ScalaGreenplumDemo.scala

3. Run it as follows

export CUR_DIR=`pwd`
scala -classpath $CUR_DIR/postgresql-9.2-1002.jdbc4.jar:. ScalaGreenplumDemo

Output

empno=7499, ename = ALLEN
empno=7521, ename = WARD
empno=7698, ename = BLAKE
empno=7782, ename = CLARK
empno=7788, ename = SCOTT
empno=7844, ename = TURNER
empno=7876, ename = ADAMS
empno=7900, ename = JAMES
empno=7902, ename = FORD
empno=7934, ename = MILLER
empno=7111, ename = LUCIA
empno=7113, ename = SIENA
empno=7369, ename = SMITH
empno=7566, ename = JONES
empno=7654, ename = MARTIN
empno=7839, ename = KING
empno=7933, ename = PAS
empno=7112, ename = LUCAS

2 comments:

Kabir Idris said...

I use to thing assigning an object a null value to not a good approach?
var connection: Connection = null

well, nice article

Kabir Idris said...

I use to thing assigning an object a null value to not a good approach?
var connection: Connection = null

well, nice article