Developing Scala applications that access IBM Db2
Scala is a concise, expressive and powerful Java Virtual Machine (JVM) programming language. Scala code gets compiled into bytecode that runs on a JVM allowing Scala code to consume Java libraries.
To access IBM Data Servers using Scala, use the IBM Data Server Driver for JDBC and SQLJ. You can write applications using the JDBC API or third-party data access libraries which are built as abstractions on top of the JDBC API. All features and API interfaces provided by the driver can be used in Scala applications.
Example
The following is an example of a Scala application written using JDBC API:package
com.example.db2.jdbc object DB2Client {
def connectURL (): java.sql. Connection = {
/*
* connection properties may be provided on url,
* for example to include trace properties on the url:
*
* val url = "jdbc:db2://localhost:50000/SAMPLE:traceFile=cpds.txt;traceLevel=-1;"
*
* all other variants of java.sql.DriverManager.getConnection( ) APIs may
* also be used, for example:
*
*java.sql.DriverManager.getConnection(String url, Properties info)
*java.sql.DriverManager.getConnection(String url, String user, String password)
*/
val url = "jdbc:db2://localhost:50000/SAMPLE"
val username = "db2admin"
val password = "db2admin"
// load the driver and acquire connection.
// Note, the Class.forName( ) way of loading a driver is not needed
// when using the JDBC 4 compliant JCC driver db2jcc4.jar
Class. forName ( "com.ibm.db2.jcc.DB2Driver" ) // needed only for db2jcc.jar, not db2jcc4.jar
var connection = java.sql.DriverManager. getConnection ( url , username , password )
connection
}
def main ( args : Array[String]) {
println ( "obtaining connection..." )
val con : java.sql. Connection = connectURL ()
println ( "connection obtained." )
try {
println ( "retrieving data..." )
con . setAutoCommit (false)
var st = con . createStatement ()
var rs = st . executeQuery ( "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY > 50000" )
while( rs . next ()) {
printf ( "Employee (%s, %s, %s, %8.2f) \n " , rs . getString ( 1 ), rs . getString ( 2 ),
rs . getString ( 3 ), rs . getDouble ( 4 ))
}
rs . close ()
st . close ()
println ( "updating data..." )
var pst = con . prepareStatement ( """INSERT INTO EMPLOYEE (
EMPNO,
FIRSTNME,
LASTNAME,
EDLEVEL,
BIRTHDATE,
SALARY)
VALUES (?, ?, ?, ?, ?, ?)""" )
pst . setString ( 1 , "300000" )
pst . setString ( 2 , "JOHN" )
pst . setString ( 3 , "DOE" )
pst . setInt ( 4 , 18 )
pst . setDate ( 5 , java.sql.Date. valueOf ( "1980-01-01" ))
pst . setDouble ( 6 , 50500.00 )
pst . executeUpdate ()
pst . clearParameters ()
pst . setString ( 1 , "300100" )
pst . setString ( 2 , "JANE" )
pst . setString ( 3 , "DOE" )
pst . setInt ( 4 , 19 )
pst . setDate ( 5 , java.sql.Date. valueOf ( "1980-01-01" ))
pst . setDouble ( 6 , 60750.00 )
pst . executeUpdate ()
pst . close
println ( "retrieving data again..." )
st = con . createStatement ()
rs = st . executeQuery ( "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY > 50000" )
while( rs . next ()) {
var emp = printf ( "Employee (%s, %s, %s, %8.2f)" , rs . getString ( 1 ), rs . getString ( 2 ),
rs . getString ( 3 ), rs . getDouble ( 4 ))
println ( emp )
}
rs . close ()
st . close ()
} catch{
case ex : java.sql.SQLException => println ( "SQLException occurred: " + ex )
} finally{
println ( "closing connection." )
con . close ()
}
}
}
Other ways of accessing Db2 in Scala
The Spark SQL module of the Spark big data processing system allows access to databases through JDBC. Using the IBM Data Server Driver for JDBC and SQLJ, Db2 can be accessed using Spark SQL. The following is a code snippet from a Spark SQL application written in Scala that uses Spark's DataFrame API and IBM Data Server Driver for JDBC and SQLJ to access Db2:
import org.apache.spark.sql.SQLContext
import org.apache.spark.SparkConf
import org.apache.spark.SparkContext
.
.
.
// Obtain Sparkcontext
val sc = new SparkContext(conf)
// Obtain SQLContext
val sqlContext = new SQLContext(sc)
// Construct DataFrame from Employee table,
// passing the Database URL and Driver class
// to the sqlContext.load( ) API
val employeeDF = sqlContext.load("jdbc", Map(
"url" -> "jdbc:db2://localhost:50000/SAMPLE:user=db2admin;password=db2admin;",
"driver" -> "com.ibm.db2.jcc.DB2Driver",
"dbtable" -> "EMPLOYEE"))
// show the DataFrame contents
employeeDF.show();
Scala Development Resources
- Java application development for IBM data servers
Information on the JDBC driver, installation, and features of the driver
-
JDBC application programming
You can write Scala applications using the JDBC interfaces.
- Scala programming
language binaries
The Scala programming language binaries can be downloaded and installed from here.
- Simple Build Tool
(sbt)
sbt is a widely used tool that fits very well with Scala projects and provides build and dependency management similar to Maven. sbt's dependency management abilities include using Maven format repositories.