Tuesday, June 28, 2011

Squeryl with Java Experiment

This is an attempt to use the popular Scala ORM/DSL Squeryl with plain Java code. I had been watching Squeryl grow for a while and loved the syntax and style since the beginning, but never had a chance to actually use it. The first attempt I had with it was trying to use it with Squeryl-Record module in Lift. However at the time, it was just too much to handle for someone who is quite new to Scala, Record and Squeryl. So, I took a step back and start with just Squeryl and try to connect with Java, which am familiar with, and see if it's really that "straight forward" as it says on the site.

I use Squeryl 0.9.4 built against Scala 2.9.0.

First thing, it is not directly possible to make Squeryl looks 100% Java, due to the nature that Squeryl requires its model class to be defined in Scala. Of course, you can always map the Scala model to a Java counter part.

Alright let's get on it.
(I am using a real world DAO Java interface from one of my project, by only changing the model to be implemented by Scala)

According to point 2 of "Using in a Java project" (oh wait, all 3 points are labeled "1"), define Schema class in Scala.
Here is my Schema:
abstract class BaseEntity extends KeyedEntity[Long] {
  var id: Long = 0
  var createDate: Timestamp = new Timestamp(System.currentTimeMillis)

class Members(@BeanProperty var name: String, @BeanProperty var email: String) extends BaseEntity

class Categories(@BeanProperty var code: String, 
    @BeanProperty var name: String, 
    @BeanProperty var sequence: Int, 
    @BeanProperty var parentId: Long) extends BaseEntity

class Articles(@BeanProperty var categoryId: Long, 
    @BeanProperty var name: String, 
    @BeanProperty var sound1Url: String, 
    @BeanProperty var sound2Url: String, 
    @BeanProperty var imageUrl: String, 
    @BeanProperty var estimatedDuration: Int, 
    @BeanProperty var clickCount: Int) extends BaseEntity {
    def this() = this(null.asInstanceOf[Long], null, null, null, 
          null, null.asInstanceOf[Int], null.asInstanceOf[Int])

object Models extends Schema {
  val members = table[Members]
  val categories = table[Categories]
  val articles = table[Articles]

Here I am making all model properties var instead of val so it behaves like what Java objects usually behaves, mutable.
One thing to notice here is that, in my original Java version of the models, I have all the Int as java.lang.Integer, and Long as java.lang.Long. Therefore, I could have NULL for my number properties. However, in Scala Int and Long actually maps to Java's int and long, so there will always be the default value of 0. In the Articles model's zero-argument constructor, I am simply demonstrating that setting the values to null won't set the field to NULL.
In the site it says
"@BeanInfo annotations, this will cause the compiler to generate Java style getters and setters for every property, the class will then look exactly like POJOs in the Java code"
However this will only make the model class usable to by Java but does not actually properly generate the Java getter/setter, so @BeanProperty is still necessary for each property. Here is what you get for not using @BeanProperty

Also implement a zero-argument constructor for each model class, so other Java code can instantiate the model class like the way usual Java code does.

Next, define the DAO. Here I use a DAO interface taken from my project and just changed the model class to the new Scala implemented model

public interface ArticlesDao {

  public boolean existsByCategory(Long categoryId);
  public Articles findById(Long id);
  public int deleteById(Long id);
  public List<Articles> findByCategory(Long categoryId);
  public int insert(Articles articles);
  public int update(Articles articles);
  public List<Articles> findTopCountArticles(int top);
  public int addClickCount(Long id, int count);

And let's start the fun by implementing it with Squeryl
package com.netgents.dw.dao.scala

import java.lang.{Long => JLong}
import java.util.List
import scala.collection.JavaConversions._
import org.squeryl.PrimitiveTypeMode._
import org.squeryl.SessionFactory
import org.squeryl.Session
import com.netgents.dw.dao.ArticlesDao
import com.netgents.dw.model.Models._
import com.netgents.dw.model.Articles
import org.springframework.stereotype.Repository

class ArticlesDaoImpl extends ArticlesDao {
  def existsByCategory(categoryId: JLong): Boolean = 
    byCategory(categoryId).headOption.map(_ => true).getOrElse(false)
  def findById(id: JLong): Articles =
  def deleteById(id: JLong): Int = 
    if(articles.delete(id.longValue)) 1 else 0
  def findByCategory(categoryId: JLong): List[Articles] = 
  private def byCategory(categoryId: JLong) =
    from(articles) (a => 
      where(a.categoryId === categoryId.longValue) 
  def insert(a: Articles): Int = {
  def update(a: Articles): Int = {
  def findTopCountArticles(top: Int): List[Articles] =
    from(articles)(a => 
      orderBy(a.clickCount desc)).take(top).toList

  def addClickCount(id: JLong, count: Int): Int = {
    org.squeryl.PrimitiveTypeMode.update(articles)(a => //dont know why i cant just use update without specifying full class package
      where(a.id === id.longValue)
      set(a.clickCount := a.clickCount.~ + count))

Here are some basic and small things to notice. Because we are basically working with Java , we need to give java.util.Long an alias, so we can work with it easier. And add import scala.collection.JavaConversions._ to make Scala List convert to Java List.

Here are some problems I encountered
  1. Due to the requirement of original API both insert and update are required to return the number of records affected. However, both insert and update in the Table don't return the number of records affected. Insert returns the inserted instance with id populated. Update simply returns Unit. I am not sure if what will happen if something goes wrong in database and the database doesn't give any error and just returned 0 as number of affected records (I have encountered that with a version of MS SQL2005), or when update did not find any record to update, so for now I can only assume 1 record is affected as long as there is no exception (actual effect explained later).
  2. not sure how to do top/limit SQL directly, except by using "take". (I am sure it's just because I didn't look hard enough)

Alright, you might have noticed I did not use any transaction {} or inTransaction {}. This is because I am planning to put my transaction control in my Service level.

As you may have noticed my usage of the @Repository annotation, yes I will be using Spring to take care of my transaction. In order to make Squeryl work with Spring or any outside transaction support, you have to use the SessionFactory.externalTransactionManagementAdapter instead of the good old SessionFactory.concreteFactory. And this is the way to use Squeryl without wrapping inside transaction/inTransaction {}. However, there are more things to be done than just implementing this method. According to the document, Session.cleanupResources needs to be called manually when your done with the connection. Since I use Spring's annotated transaction, I decide to write my own TransactionManager.
Here is my implementation:
class MyDataSourceTransactionManager extends DataSourceTransactionManager {

  def init() {
    SessionFactory.externalTransactionManagementAdapter = Some(() => {
      if(Session.hasCurrentSession) {
      else {
        val s = new Session(DataSourceUtils.getConnection(getDataSource), 
                    new MySQLAdapter, None){
          override def cleanup = {
        //Session.create(DataSourceUtils.getConnection(getDataSource), new MySQLAdapter)
  override def doCleanupAfterCompletion(transaction: AnyRef) {

    Session.cleanupResources //clean up resources when done, following the doc


The problems I faced here are
  1. Session needs to be controlled by myself so that all query made in the same transaction will get the same Session. Here I leverage the built-in Session storing mechanism, bindToCurrentThread. 
  2. Since I am using s.bindToCurrentThread, I need to unbind when I am done. However Session.cleanupResources doesn't do that and I don't have direct access to unbindFromCurrentThread, so I override the Session's implementation to call unbindFromCurrentThread when cleanup is called.
One small tip here is that since I am using Spring's transaction control, when getting Connection, I have to use DataSourceUtils.getConnection(getDataSource) instead of simply  getDataSource.getConnection.

There, that's about it, the rest can be plain Java and not to worry that the DAO is in Scala

Let's do a little test.

I will leave out the detail of Service implement because it's mostly mapping of 1 to 1 method call to DAO.

Here is my test class

@Test(expected = RuntimeException.class)
public void test1() {

  Articles a = new Articles();
  a.setName("Article 1");
  a.setSound1Url("sound url 1");
  a.setSound2Url("sound url 2");
  a.setImageUrl("image url");
  assertEquals(1L, a.getId());//id should be set to 1 after insert
  a.setImageUrl("image url updated");
  a = articlesService.findById(new Long(1));
  assertNotNull(a);//a should not be null
  assertNull(a.getSound1Url());//should be null now
  assertEquals("image url updated", a.getImageUrl());
  articlesService.deleteById(new Long(1));
  a = articlesService.findById(new Long(1));
  assertNull(a);//should be deleted

  //lets see if not record is found to update
  a = new Articles();
  a.setCategoryId(new Long(34));
  a.setImageUrl("another image url");
  articlesService.update(a);//will throw a RuntimeException: failed to update
public void transaction() {
  List<articles> as = articlesService.findByCategory(new Long(1));
  assertEquals(60, as.size());//inserted 60 records

  //test to see if transaction works
  try {
  catch(Exception e) {
    System.out.println("error message: "+e.getMessage());
  as = articlesService.findByCategory(new Long(1));
  assertEquals(60, as.size());//worked, number of records stayed at 60    
Here is what I do in goodInsertsAction and badInsertsAction
public void goodInsertsActions() {

public void badInsertsActions() {
  throw new DataIntegrityViolationException("hell with it");

private void doInserts() {
  Articles a;
  for(int i = 1; i <= 60; i++) {
    a = new Articles();
    a.setName("Article " + i);
    a.setSound1Url("sound url 1");
    a.setClickCount(6 + i);
The test demonstrated:
  1. id will be updated once data is inserted and new id retrieved
  2. values can be simply NULL without the use of Scala's convention of Option
  3. when update cannot find a record to update a RuntimeException is raised
  4. transaction is working good

No comments:

Post a Comment