SQL Data Access in Play using Scala

Today’s modern application frameworks come with a promise of easy sql data access. There is no wonder why we have so many frameworks that make it easier to issue queries and handle transactions. SQL is the lingua franca of most applications when it comes to databases.

Play comes with the JDBC plugin. We encountered the JDBC plugin previously in order to modify our database schema.

The first step is to include the jdbc and the evolutions module.

libraryDependencies += evolutions
libraryDependencies += jdbc

Then we shall define the connection string needed. We will use a simple h2 database. The configuration is added at the application.conf.

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:/tmp/defaultdatabase"

Then we add a script that creates the users table.

# Users schema

# --- !Ups

CREATE TABLE users (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (email)
);

# --- !Downs

DROP TABLE users;

Before creating our repository class let’s check what the jdbc plugin provides us with.

We have the plain getConnection method, responsible for returning a jdbc connection. This is similar to the DataSource.getConnection from Java. Thus pay extra attention since you must close the connection.

val connection = db.getConnection()
connection.close()

Next method is withConnection. By using withConnection you get Play to manage the connection for you. All you have to do is pass a block of code with jdbc actions.

  def fetchUsers(): List[User] = {

    db.withConnection { conn =>

      val stmt = conn.createStatement
      var rs = stmt.executeQuery("SELECT*FROM users");
      val listBuffer = ListBuffer[User]()

      while(rs.next()) {

        listBuffer.append(User(Option(rs.getLong("id")),rs.getString("email"),rs.getString("first_name"),rs.getString("last_name")))
      }

      listBuffer.toList
    }
  }

As you can see above, we’ve just returned back a list of our user entries.

And last but not least withTransaction. You’ve guessed right, what you receive back is a connection with autocommit set to false.

  def addUser(user:User): User = {

    db.withTransaction { conn =>
      val stmt = conn.createStatement

      val insertQuery = "INSERT INTO users ( email, first_name, last_name) VALUES( '"+user.email+"', '"+user.firstName+"','"+user.lastName+"') "
      stmt.executeUpdate(insertQuery,Statement.RETURN_GENERATED_KEYS)
      val resultSet = stmt.getGeneratedKeys;
      if(resultSet.next()) {
        val id = resultSet.getLong(1);
        new User(Option(id),user.email,user.firstName,user.lastName)
      } else {
        throw new Exception("User not persisted properly")
      }
    }
  }

In the above example a user is persisted. In case of failure we throw an exception and the transaction is rolled back.

To sum up we have just checked how to access a sql database using play. Also we have checked the extra functions that play api provides apart from the familiar jdbc api.
That’s all for now! Feel free to check the code on github.

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