ADBCJ in Scala: Getting Started

By gamlerhart February 28, 2013 English, java, software-development

What, again asynchronous database access? Yes, because I’m currently knee deep in this stuff. (^^) This post series will show how to use ADBCJ in Scala 2.10, share a few tips, performance insights etc.

ADBCJ and Scala, new buddies

Grabbing the Bits

Of course, first we need to grab the bits. We can get it via SBT. Currently there are only snapshots available on my snapshot repository. We pull the Scala ADBCJ API, the ADBCJ connection pool and the MySQL driver.

name := "Scala ADBCJ Demo" version := "1.0" scalaVersion := "2.10.0" libraryDependencies += "info.gamlor.adbcj" %% "scala-adbcj" % "0.6-SNAPSHOT" libraryDependencies += "org.adbcj" % "adbcj-connection-pool" % "0.6-SNAPSHOT" libraryDependencies += "org.adbcj" % "mysql-async-driver" % "0.6-SNAPSHOT" resolvers += "Gamlor-Repo" at "https://raw.github.com/gamlerhart/gamlor-mvn/master/snapshots"

Getting Access to the Database

The next step is to get access the the database. We do this with the Database object, by passing in the connection URL, username and password. This create a instance which is used to access this database. Usually you should have one instance only per database in your application.

// Note the "pooled" prefix, for using the connection pool val database: DatabaseAccess = Database("adbcj:pooled:mysql://localhost/adbcj-demo", "adbcj", "adbcj-pwd")

Then we can create new connections. The most basic way is to create a connection directly. However, you are then responsible for closing the connection.

// connect() will return a future, which contains the connection // You need to close the connection yourself. val simpleConnectionFuture = database.connect() // We can use the Scala for construct to deal nicely with futures val futureForWholeOperation = for { connection <-simpleConnectionFuture closeDone <- connection.close() } yield "Done"

Closing Connections Automatically

Now closing the connection manually can be error prone and tedious. Therefore helper methods exists. One for using a connection and one for using a connection with a new transaction.
The closure has to return a future. Since everything is asynchronously, we cannot close the connection when we leave the future. Instead we need to close it when everything is done. So, the connection will be close, when the future returned by the closure is completed.

val doneWithConnection:Future[DBResultList] = database.withConnection{ connection => // do something with the connection // you need to return a future, because everything is asynchrous // so the connection can only be closed when everything is done connection.executeQuery("SELECT 1") } val doneWithTransactionAndConnection:Future[DBResultList] = database.withTransaction{ connection => // Same goes for transactions connection.executeQuery("SELECT 1") }

Some Real Work

Let’s do some real work. We create the schema if it doesn’t exist and then insert a few items. In general, I highly recommend to read the Akka documentation about futures, which gives an idea how to deal with tem.

val txDoneFuture = database.withTransaction{ connection => // Same goes for transactions val schemaCreation = connection.executeQuery("""CREATE TABLE IF NOT EXISTS posts(\n id int NOT NULL AUTO_INCREMENT,\n title varchar(255) NOT NULL,\n ontent TEXT NOT NULL,\n PRIMARY KEY (id)\n ) ENGINE = INNODB;""") val firstPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('The Title','TheContent')") val secondPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('Second Title','More Content')") val thirdPost =connection.executeUpdate("INSERT INTO posts(title,content) VALUES('Third Title','Even More Content')") // we want to await for all operations at the end for { schemaDone <-schemaCreation postOneDone <-firstPost postTwoDone <-secondPost postThreeDone <-thirdPost } yield "All Done" }

Query

Let’s also query for some data. When running queries, your future will contain a immutable result set, which supports the standard collection operations.

val postsDataFuture = database.withTransaction{ connection => val postsFuture =connection.executeQuery("SELECT * FROM posts") postsFuture onSuccess { case rs:DBResultList => { for (row <- rs){ System.out.println("ID: "+row("ID").getLong()+" with title "+row("title").getString()); } } } postsFuture }

Prepared Statements

Of course, there prepared statement support:

val postsDataFuture= database.withTransaction{ connection => val preparingStmt =connection.prepareQuery("SELECT * FROM posts WHERE title LIKE ?") val postsFuture = preparingStmt.flatMap(stmt=>stmt.execute("Third Title")) postsFuture onSuccess { case rs:DBResultList => { for (row <- rs){ System.out.println("ID: "+row("ID").getLong()+" with title "+row("title").getString()); } } } postsFuture }

One Last Hint, Debugging

So, this was a small tour through the API. However I want to share one last important thing, which helps you dealing with failures.

Let’s assume you ran a back query:

Then you get a future which contain the error. However, the stack trace of the error does not contain any hint where in the code the query was issues. It just contains a stack trace with the internal event loop:

You can use the debug flat to get a stack trace which points at the location which sent the request. Either add -Dorg.adbcj.debug=true to the JVM flags. Or set the appropriate property when crating the database access. Note that this feature is quite expensive, so I recommend to use it while developing or during an investigation.

org.adbcj.DbException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exis at org.adbcj.support.stacktracing.StackTraceCapturing$CaputureByDefault.capture(StackTraceCapturing.java:20) at org.adbcj.support.stacktracing.StackTraceCapturing.defaultCapture(StackTraceCapturing.java:11) at org.adbcj.support.stacktracing.StackTracingOptions$1.captureStacktraceAtEntryPoint(StackTracingOptions.java:12) at org.adbcj.support.DefaultDbFuture.<init>(DefaultDbFuture.java:45) at org.adbcj.support.DefaultDbSessionFuture.<init>(DefaultDbSessionFuture.java:50) at org.adbcj.mysql.codec.MySqlRequests.executeQuery(MySqlRequests.java:28) at org.adbcj.mysql.codec.MySqlConnection.executeQuery(MySqlConnection.java:108) at org.adbcj.mysql.codec.MySqlConnection.executeQuery(MySqlConnection.java:102) at org.adbcj.connectionpool.PooledConnection.executeQuery(PooledConnection.java:78) at info.gamlor.db.DBConnection$$anonfun$executeQuery$1.apply(DBConnection.scala:109) at info.gamlor.db.DBConnection$$anonfun$executeQuery$1.apply(DBConnection.scala:109) at info.gamlor.db.FutureConversions$class.completeWithAkkaFuture(FutureConversions.scala:20) at info.gamlor.db.DBConnection.completeWithAkkaFuture(DBConnection.scala:31) at info.gamlor.db.DBConnection.executeQuery(DBConnection.scala:109) --->at info.gamlor.adbcj.scalademo.MainDemo$$anonfun$8.apply(MainDemo.scala:99) ... // RIGHT Location Caused by: org.adbcj.mysql.codec.MysqlException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exist at org.adbcj.mysql.codec.packets.ErrorResponse.toException(ErrorResponse.java:49) at org.adbcj.mysql.codec.decoding.ExpectQueryResult.handleError(ExpectQueryResult.java:31) at org.adbcj.mysql.codec.decoding.ResponseStart.parse(ResponseStart.java:33) at org.adbcj.mysql.codec.decoding.AcceptNextResponse.parse(AcceptNextResponse.java:26) at org.adbcj.mysql.codec.MySqlClientDecoder.doDecode(MySqlClientDecoder.java:96) at org.adbcj.mysql.codec.MySqlClientDecoder.decode(MySqlClientDecoder.java:67) at org.adbcj.mysql.netty.Decoder.decode(MysqlConnectionManager.java:177) at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:113)

So, that’s it for now. Future posts will go into more aspects of ADBCJ the interaction with Scala.

Tagged on: adbcj, concurrency, database, java, Scala