How can I connect to a MySQL database using Scala?

Question

I'm working on a little project where I'd like to parse some data, and then put it into a database. I'm not working with Lift, and I haven't been able to find a standard way to do this.

I'm fine writing the queries myself, but I'm not sure what to use to actually connect to the DB.

Answer 1

You can use JDBC - the standard means of getting Java to talk to databases. You'll need the appropriate MySQL JDBC driver. Apache DbUtils provides some utility classes surrounding JDBC and would be useful.

If you want a higher level API which takes some of the boilerplate out, then check out Spring's JDBC integration.

If you want an ORM (object-relational mapping), then Hibernate is a good choice.

I've used all three in Scala with success.

Answer 2

I've actually written a SQL command shell, in Scala, that talks to any arbitrary database for which a JDBC driver exists. As Brian Agnew notes, it works perfectly. In addition, there are tools like Querulous, SQueryL and OR/Broker that provide Scala-friendly database layers. They sit on top of JDBC, but they provide some additional semantics (via DSLs, in some cases) to make things easier for you.

Answer 3

For completeness, also check out RichSQL. It's demo code showing how to wrap JDBC to make more Scala-like operations, but it's actually quite usable. It has the advantage of being simple and small, so you can easily study the source to see what's going on. Don't forget to close() your PreparedStatements.

Answer 4

Try O/R Broker:

case class MyObj(name: String, year: Int)

val insertStm = "INSERT INTO MYTABLE VALUES(:obj.name, :obj.year)"

val ds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource
// set properties on ds

import org.orbroker._
val builder = new BrokerBuilder(ds)
builder.register('insertStm, insertStm) //'
val broker = builder.build

val myObj: MyObj = // Parse stuff to create MyObj instance
broker.transactional() { session =>
  session.execute('insertStm, "obj"->myObj) //'
  session.commit()
}

val myObjs: Seq[MyObj] = // Parse stuff to create sequence of MyObj instances
broker.transactional() { session =>
  session.executeBatch('insertStm, "obj"->myObj) //'
  session.commit()
}