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

I just discovered ScalikeJDBC which offers a Scala like API wrapper for JDBC.

(I found ScalikeJDBC when researching how to use ScalaAnorm without Play Framework. Now it looks like I won't be needing Anorm for my project.)

Here is a simple example, though it offers many interesting features not shown here:

import scalikejdbc._

Class.forName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource")

ConnectionPool.singleton("jdbc:mysql://localhost:3306/myschema", "user", "password")

DB.localTx { implicit conn =>

  val data = sql"select mystringcol, myintcolumn from mytable".map {
    rs => (rs.string("mystringcol"), rs.int("myintcolumn"))
  }.list().apply()

  println(data)

}

Some documentation links:

• Documentation on running queries
• Connection pool and loan pattern

Answer 2

Try O/R Broker:

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

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

import org.orbroker._
val builder = new BrokerBuilder(ds)
val broker = builder.build

val myObj: MyObj = // Parse stuff to create MyObj instance
broker.transaction() { session =>
  session.execute("INSERT INTO MYTABLE VALUES(:obj.name, :obj.year)", "obj"->myObj)s
}

val myObjs: Seq[MyObj] = // Parse stuff to create sequence of MyObj instances
broker.transaction() { session =>
  session.executeBatch("INSERT INTO MYTABLE VALUES(:obj.name, :obj.year)", "obj"->myObjs)
}

Answer 3

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 4

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 5

Off course you can use all Java version compatible with JDBC (Hibernate, Spring, etc), but for better use of Scala language, I recommend using a Scala specific framework, which have a better DSL.

• ScalaQuery is an API / DSL (domain specific language) built on top of JDBC for accessing relational databases in Scala. It was designed with the following goals in mind:
• Squeryl is a Scala ORM and DSL for talking with Databases with minimum verbosity and maximum type safety
• SORM is a Scala ORM-framework designed to eliminate boilerplate code and solve the problems of scalability with a high level abstraction and a functional programming style
• Slick - Typesafe backed project with Functional Relational Mapping

Check out more about these frameworks at https://stackoverflow.com/questions/1362748/looking-for-a-comparison-of-scala-persistence-frameworks

Answer 6

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.