My Adventures in Coding

February 29, 2012

Scala – Connecting a Scala app to MS SQL Server with Squeryl

Filed under: Scala,SQL Server 2005 — Brian @ 9:21 pm
Tags: ,

For all of our Scala applications up until now, we have being using MongoDB. However, recently we needed to connect one of our Scala applications to Microsoft SQL Server database. One of my excellent co-workers pointed us at a really nice project called Squeryl which provides a very simple library for doing just that. The documentation is excellent and great examples are provided to help get you up and running quickly.

Let’s look at a simple example.

Create a table in your SQL Server database:

CREATE TABLE [dbo].[Customer](
	[id] [int] NOT NULL,
	[name] [varchar] (100) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)) ON [PRIMARY]

Insert a row:

INSERT INTO dbo.Customer(id, name)
VALUES(1,'John Smith')

Get all of the libraries you will need to make the program run:

  • Download Squeryl jar (2.9.0)
  • Download jtds driver jar (1.2.5)
  • Download cglib jar (2.2.2)
  • Download asm jar (3.1)

Now, let’s run our test code to select a row from our Customer table:

package spikes

import org.squeryl.Session
import org.squeryl.adapters.MSSQLServer
import org.squeryl.SessionFactory
import org.squeryl.PrimitiveTypeMode._
import org.squeryl.Schema

// Create a Customer class which has the same fields as the Customer table in SQL Server
case class Customer(id: Long, name: String) {}

object SqlSpike extends App with Schema {
  val databaseConnectionUrl = "jdbc:jtds:sqlserver://myservername;DatabaseName=mydatabasename"
  val databaseUsername = "myusername"
  val databasePassword = "password"

  // Set the jtds driver
  Class.forName("net.sourceforge.jtds.jdbc.Driver")

  // Connect to the database
  SessionFactory.concreteFactory = Some(()=>
    Session.create(
      java.sql.DriverManager.getConnection(databaseConnectionUrl, databaseUsername, databasePassword),
      new MSSQLServer))

  // Setup the Customer class to be mapped to the "Customer" table in SQL Server
  val customers = table[Customer]("Customer")

  // Select Customer with id=1 from the Customer table
  transaction {
    val customer = customers.where(c=> c.id === 1).single
    println("Customer name: " + customer.name)
  }
}

Finally, the output of the program:

[info] Running spikes.SqlSpike 
Customer name: John Smith
[success] Total time: 4 s, completed 29-Feb-2012 8:42:24 PM

Overall we have found the Squeryl library to be very useful. If you need to connect a Scala application to a SQL database I recommend you give it a try, it should save you some aggravation.

Advertisements

2 Comments »

  1. Hi. Where is the ‘table’ is coming from? line 27

    Comment by Andrew — December 17, 2012 @ 11:17 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: