ADBCJ in Scala: Getting Started
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" |
by 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:
| org.adbcj.mysql.codec.MysqlException: 42S02Table 'adbcj-demo.thistabledoesnotexist' doesn't exis | |
| 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) | |
| ... |
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.