Tutorial ScalaFX & Slick

After writing a couple of small Java Swing applications that stores data in some database I thought about other ways to implement such applications. I am very interested in the functional object oriented  language Scala so I focused my experiments on this language. Unfortunately, in past there was no easy way to use the flavor of Scala with a relational database like in Java with JPA and Hibernate.

A nice approach is the lifted embedding in Slick. Instead of trying to adopt ORM to Scala this API provides a DSL define database tables in Scala. The usage of Slick lifted embedding is described here.

In my example, I have chosen an one-table database to store last name, first name, and an email address for a person. A person is a simple case class and the database table Persons based on this case class:

case class Person(id: Option[Int], last: String, first: String, email: String)

object Persons extends Table[Person]("PERSONS"){
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def last = column[String]("LAST")
  def first = column[String]("FIRST")
  def email = column[String]("EMAIL")
  def * = id.? ~ last ~ first ~ email <> (Person, Person.unapply _)
  def forInsert = last ~ first ~ email <> ({t => Person(None, t._1, t._2, t._3)}, {(p: Person) => Some(p.last, p.first, p.email)})
}

As primary key I use an artificial integer column with auto increment. As described in the documentation of Slick, it is in the current version 1.0 necessary to define this column as optional. Furthermore, a special method defined as forInsert is needed to allow the insertion of new data with auto increment by using None as value for the optional value.

Slick allows now to create the defined database and fill it with example data (using a H2 database):

Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
    Persons.ddl.create
    Persons.forInsert insertAll (
      Person(None, "Mustermann", "Max", "max.mustermann@example.com"),
      Person(None, "Mustermann", "Mandy", "mandy.mustermann@example.com"),
      Person(None, "Doe", "John", "jdoe@nowhere.com")
    )
}

The next step is to add this database implementation to a ScalaFX application. The ScalaFX project you can find here. The example application is a simple FX table with editable cells and the possibility to add new data sets as described for Java in the JavaFX documentation from Oracle.

At first you need to extend the case class Person that it provides string property beans. Because the data should be mutable, you have to mark the members of the case class as var.

case class Person(id: Option[Int], var last: String, var first: String, var email: String) {
  val lastName = new StringProperty(this, "lastName", last)
  val firstName = new StringProperty(this, "firstName", first)
  val emailAddress = new StringProperty(this, "emailAddress", email)
}

Now you are prepared to write the ScalaFX application. For the start the code for implementing a non-editable table looks like:

object AddressBook extends JFXApp {

    // Read the data from databse in an observable buffer
    val personsTableModel = new ObservableBuffer[Person]
    personsTableModel ++= Query(Persons).list

    stage = new JFXApp.PrimaryStage {

      // Sets the title as lable
      val labelName = "Address book"
      val label = new Label(labelName) {
        font = Font("Arial", 20)
      }
      title = labelName

      scene = new Scene {

        // Define the table columns
        val firstNameColumn = new TableColumn[Person, String] {
          text = "First name"
          cellValueFactory = {_.value.firstName}
          prefWidth = 180
        }
        val lastNameColumn = new TableColumn[Person, String] {
          text = "Last name"
          cellValueFactory = {_.value.lastName}
          prefWidth = 180
        }
        val emailColumn = new TableColumn[Person, String] {
          text = "email address"
          cellValueFactory = {_.value.emailAddress}
          prefWidth = 250
        }

        // Compose the table
        val table = new TableView[Person](personsTableModel) {
          columns +=(firstNameColumn, lastNameColumn, emailColumn)
        }

        // Stuck all together in a VBox
        content = new VBox {
          content = List(label, table)
          spacing = 10
          padding = Insets(10, 10, 10, 10)
        }
    }
}

To make this table editable, you have to switch on this feature in the table definition:

         val table = new TableView[Person](personsTableModel) {
          columns +=(firstNameColumn, lastNameColumn, emailColumn)
          editable = true
        }

Now you have to modify the table columns by setting the cell factory to an editable field. Note that you must tell the cell factory how to convert the input string to the data type of the cell. Because the cell data type is String too, you can use DefaultStringConverter.  After that you must define what shall happen after finish editing by setting to onEditCommit an suitable handler. For the last name column this handler replace  the last name in the Person regarding to the edited row an updates this value in the database table:

        val lastNameColumn = new TableColumn[Person, String] {
          text = "last name"
          cellValueFactory = {_.value.lastName}
          cellFactory = _ => new TextFieldTableCell[Person, String] (new DefaultStringConverter())
          onEditCommit = (evt: CellEditEvent[Person, String]) => {
            val person = evt.rowValue
            val newLastNameVal = evt.newValue
            // Update current person data set
            person.last = newLastNameVal
            // Update database entry
            Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
              val updateQuery = for (persons <- Persons if persons.id === person.id) yield persons.last
              updateQuery.update(newLastNameVal)
            }
          }
          prefWidth = 180  
        }     

For the other two columns you must extends the implementation in this manner.

Last but not least there is missing a possibility to insert new persons. For this reason you have to add three text fields, a button for adding the new data, and a HBox to stuck this elements together into a row. Further you have to append this HBox to the VBox defined above. All together can be done by this code fragment:

        val firstTextField = new TextField {
          promptText = "first name"
          maxWidth = firstNameColumn.getPrefWidth
        }

        val lastTextField = new TextField {
          promptText = "last name"
          maxWidth = lastNameColumn.getPrefWidth
        }

        val emailTextField = new TextField {
          promptText = "email"
          maxWidth = emailColumn.getPrefWidth
        }

        val addButton = new Button("Add") {
          onAction = (_:ActionEvent) => {
            val addPerson = Person(None, lastTextField.getText, firstTextField.getText, emailTextField.getText)
            println(addPerson)
            Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
              Persons.forInsert insert addPerson
              // Reload table data from database because of auto generated index
              // (Simple but inefficient for larger tables)
              personsTableModel.clear()
              personsTableModel ++= Query(Persons).list
            }
            lastTextField.clear()
            firstTextField.clear()
            emailTextField.clear()
          }
        }

        val hbox = new HBox {
          content = List(firstTextField, lastTextField, emailTextField, addButton)
        }

        content = new VBox {
          content = List(label, table, hbox)
          spacing = 10
          padding = Insets(10, 10, 10, 10)
        }
      }