Scala PostgreSQL Access

July 08, 2010

Recently, I've been digging into Scala to understand how PostgreSQL would integrate inside of Scala code. Scala is an interesting language which runs on the JVM like Java but eliminates much of the Java boiler plate code increasing the productivity of developers. With many of the knowledgeable Java programmers advocating Scala and large success stories like Twitter becoming more common, there is a possibility that one day Scala will unseat Java and the dominate enterprise programming language in the future. Given this possibility, it is encouraging that PostgreSQL is respected in the Scala community, but unfortunately, what I'm finding, database persistence in general is still appears to be immature inside of Scala. There is an early module called DBC that attempts to provide database connectivity, but this initial attempt was lacking. Below is an example of running a simple query which I find very awkward:

object MainDBC { import scala.dbc._ import scala.dbc.Syntax._ import syntax.Statement._ def main(args: Array[String]): Unit = { val db = database("jdbc:postgresql://localhost/scala","postgres","password") val res = db.executeStatement { (select fields (("pgbench_tellers.tid" of integer) and ("pgbench_tellers.bid" of integer) and ("pgbench_branches.bbalance" of integer)) from "pgbench_tellers, pgbench_branches" where "pgbench_tellers.bid = pgbench_branches.bid") } println(" tid bid bbalance") println("----- ----- ----------") for(val i <- res) { for(val f <- i.fields) { val r = f.content.sqlString print(r + " ".dropRight(r.length)) } println() } } }

There are a number of object layers available that may be much easier to use, but the nice thing about Scala running on the JVM is that you can leverage the low level JDBC interface and eliminate much of the awkwardness. Below is an example of the same query but using JDBC.

object MainJDBC { import java.sql.{Connection, DriverManager, ResultSet} def main(args: Array[String]): Unit = { classOf[org.postgresql.Driver] val db = DriverManager.getConnection("jdbc:postgresql://localhost/scala","postgres","password") val st = db.createStatement val res = st.executeQuery( "SELECT pgbench_tellers.tid, pgbench_tellers.bid, pgbench_branches.bbalance" + " FROM pgbench_tellers, pgbench_branches " + " WHERE pgbench_tellers.bid = pgbench_branches.bid") println(" tid bid bbalance") println("----- ----- ----------") while (res.next) { for(val i <- 1 to res.getMetaData.getColumnCount) { val r = res.getInt(i).toString print(r + " ".dropRight(r.length)) } println } db.close } }

 

 

Share this

Relevant Blogs

Highlights from the PostgreSQL 16 Beta Release

The PostgreSQL Global Development Group released PostgreSQL 16 Beta 1 on May 25, 2023. PostgreSQL 16 improves logical replication by enabling replication from standbys as well as the ability to...
June 02, 2023

More Blogs

Processing PostgreSQL JSON & JSONB data in Java

Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a...
April 01, 2023