18 min read

(For more resources related to this topic, see here.)

We have learned how to create snippets, how to work with forms, and Ajax, how to test your code, and how to create a REST API, and all this is awesome! However, we did not learn how to persist data to make it durable.

In this article, we will see how to use Mapper, an object-relational mapping ( ORM ) system for relational databases, included with Lift.

The idea is to create a map of database tables into a well-organized structure of objects; for example, if you have a table that holds users, you will have a class that represents the user table.

Let’s say that a user can have several phone numbers. Probably, you will have a table containing a column for the phone numbers and a column containing the ID of the user owning that particular phone number.

This means that you will have a class to represent the table that holds phone numbers, and the user class will have an attribute to hold the list of its phone numbers; this is known as a one-to-many relationship .

Mapper is a system that helps us build such mappings by providing useful features and abstractions that make working with databases a simple task. For example, Mapper provides several types of fields such as MappedString, MappedInt, and MappedDate which we can use to map the attributes of the class versus the columns in the table being mapped. It also provides useful methods such as findAll that is used to get a list of records or save, to persist the data. There is a lot more that Mapper can do, and we’ll see what this is through the course of this article.

Configuring a connection to database

The first thing we need to learn while working with databases is how to connect the application that we will build with the database itself.

In this recipe, we will show you how to configure Lift to connect with the database of our choice. For this recipe, we will use PostgreSQL; however, other databases can also be used:

Getting ready

  1. Start a new blank project.
  2. Edit the build.sbt file to add the lift-mapper and PostgreSQL driver dependencies: 
    "net.liftweb" %% "lift-mapper" % liftVersion % "compile",
"org.postgresql" % "postgresql" % "9.2-1003-jdbc4"  % "compile"
  3. Create a new database.
  4. Create a new user.

How to do it…

Now carry out the following steps to configure a connection with the database:

  1. Add the following lines into the default.props file: 
    db.driver=org.postgresql.Driver
db.url=jdbc:postgresql:liftbook
db.user=<place here the user you've created>
db.password=<place here the user password>
  2. Add the following import statement in the Boot.scala file: 
    import net.liftweb.mapper._
  3. Create a new method named configureDB() in the Boot.scala file with the following code: 
      def configureDB() {
    for {
      driver <- Props.get("db.driver")
      url <- Props.get("db.url")
    } yield {
      val standardVendor =
        new StandardDBVendor(driver, url, Props.get("db.user"),
     Props.get("db.password"))

      LiftRules.unloadHooks.append(standardVendor.closeAllConnections_! _)

      DB.defineConnectionManager(DefaultConnectionIdentifier, standardVendor)
    }
  }
  4. Then, invoke configureDB() from inside the boot method.

How it works…

Lift offers the net.liftweb.mapper.StandardDBVendor class, which we can use to create connections to the database easily.

This class takes four arguments: driver, URL, user, and password. These are described as follows:

  • driver : The driver argument is the JDBC driver that we will use, that is, org.postgresql.Driver
  • URL : The URL argument is the JDBC URL that the driver will use, that is, jdbc:postgresql:liftbook
  • user and password : The user and password arguments are the values you set when you created the user in the database.

After creating the default vendor, we need to bind it to a Java Naming and Directory Interface (JNDI) name that will be used by Lift to manage the connection with the database. To create this bind, we invoked the defineConnectionManager method from the DB object. This method adds the connection identifier and the database vendor into a HashMap, using the connection identifier as the key and the database vendor as the value. The DefaultConnectionIdentifier object provides a default JNDI name that we can use without having to worry about creating our own connection identifier. You can create your own connection identifier if you want. You just need to create an object that extends ConnectionIdentifier with a method called jndiName that should return a string.

Finally, we told Lift to close all the connections while shutting down the application by appending a function to the unloadHooks variable. We did this to avoid locking connections while shutting the application down.

There’s more…

It is possible to configure Lift to use a JNDI datasource instead of using the JDBC driver directly. In this way, we can allow the container to create a pool of connections and then tell Lift to use this pool.

To use a JNDI datasource, we will need to perform the following steps:

  1. Create a file called jetty-env.xml in the WEB-INF folder under src/main/webapp/ with the following content: 
    <!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" 
    "http://www.eclipse.org/jetty/configure.dtd">
<Configure class="org.eclipse.jetty.webapp.WebAppContext">
    <New id="dsliftbook" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg>jdbc/dsliftbook</Arg>
        <Arg>
            <New class="org.postgresql.ds.PGSimpleDataSource">
                <Set name="User">place here the user you've created</Set>
                <Set name="Password">place here the user password</Set>
                <Set name="DatabaseName">liftbook</Set>
                <Set name="ServerName">localhost</Set>
                <Set name="PortNumber">5432</Set>
            </New>
        </Arg>
    </New>
</Configure>
  2. Add the following line into the build .sbt file: 
    env in Compile := Some(file("./src/main/webapp/WEB-INF/jetty-env.xml") asFile)
  3. Remove all the jetty dependencies and add the following: 
        "org.eclipse.jetty" % "jetty-webapp" % "8.0.4.v20111024"  % "container",
    "org.eclipse.jetty" % "jetty-plus" % "8.0.4.v20111024"  % "container",
  4. Add the following code into the web.xml file. 
        <resource-ref>
        <res-ref-name>jdbc/dsliftbook</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
  5. Remove the configureDB method.
  6. Replace the invocation of configureDB method with the following line of code: 
    DefaultConnectionIdentifier.jndiName = "jdbc/dsliftbook"

The creation of the file jetty-envy.xml and the change we made in the web.xml file were to create the datasource in jetty and make it available to Lift.

Since the connections will be managed by jetty now, we don’t need to append hooks so that Lift can close the connections or any other configuration when shutting down the application. All we need to do is tell Lift to get the connections from the JNDI datasource that we have configured into the jetty. We do this by setting the jndiName variable of the default connection identifier, DefaultConnectionIdentifier as follows:

DefaultConnectionIdentifier.jndiName = "jdbc/dsliftbook"

The change we’ve made to the build.sbt file was to make the jetty-env.xml file available to the embedded jetty. So, we can use it when we get the application started by using the container:start command.

See also…

Mapping a table to a Scala class

Now that we know how to connect Lift applications to the database, the next step is to learn how to create mappings between a database table and a Scala object using Mapper.

Getting ready

We will re-use the project we created in the previous recipe since it already has the connection configured.

How to do it…

Carry out the following steps to map a table into a Scala object using Mapper:

  1. Create a new file named Contact.scala inside the model package under src/main/scala/code/ with the following code: 
    package code.model

import net.liftweb.mapper.{MappedString, LongKeyedMetaMapper, LongKeyedMapper,
     IdPK}

class Contact extends LongKeyedMapper[Contact] with IdPK {
  def getSingleton = Contact

  object name extends MappedString(this, 100)
}

object Contact extends Contact with LongKeyedMetaMapper[Contact] {
  override def dbTableName = "contacts"
}
  2. Add the following import statement in the Boot.scala file: 
    import code.model.Contact
  3. Add the following code into the boot method of the Boot class: 
       Schemifier.schemify(
      true, Schemifier.infoF _,
      Contact
    )
  4. Create a new snippet named Contacts with the following code: 
    package code.snippet

import code.model.Contact
import scala.xml.Text
import net.liftweb.util.BindHelpers._

class Contacts {
  def prepareContacts_!() {
    Contact.findAll().map(_.delete_!)
    val contactsNames = "John" :: "Joe" :: "Lisa" :: Nil
    contactsNames.foreach(Contact.create.name(_).save())

  }

  def list = {
    prepareContacts_!()

    "li *" #> Contact.findAll().map {
      c => {         c.name.get
      }
    }
  }
}
  5. Edit the index.html file by replacing the content of the div element with main as the value of id using the following code : 
            <div data-list="Contacts.list">
            <ul>
                <li></li>
            </ul>
        </div>
  6. Start the application.
  7. Access your local host (http://localhost:8080), and you will see a page with three names, as shown in the following screenshot:

How it works…

To work with Mapper, we use a class and an object. The first is the class that is a representation of the table; in this class we define the attributes the object will have based on the columns of the table we are mapping. The second one is the class’ companion object where we define some metadata and helper methods.

The Contact class extends the LongKeyedMapper[Contact] trait and mixes the trait IdPK. This means that we are defining a class that has an attribute called id (the primary key), and this attribute has the type Long. We are also saying that the type of this Mapper is Contact.

To define the attributes that our class will have, we need to create objects that extend “something”. This “something” is the type of the column. So, when we say an object name extends MappedString(this, 100), we are telling Lift that our Contact class will have an attribute called name, which will be a string that can be 100 characters long. After defining the basics, we need to tell our Mapper where it can get the metadata about the database table. This is done by defining the getSingleton method. The Contact object is the object that Mapper uses to get the database table metadata. By default, Lift will use the name of this object as the table name. Since we don’t want our table to be called contact but contacts, we’ve overridden the method dbTableName.

What we have done here is created an object called Contact, which is a representation of a table in the database called contacts that has two columns: id and name. Here, id is the primary key and of the type Long, and name is of the type String, which will be mapped to a varchar datatype.

This is all we need to map a database table to a Scala class, and now that we’ve got the mapping done, we can use it. To demonstrate how to use the mapping, we have created the snippet Contacts.

This snippet has two methods. The list method does two things; it first invokes the prepareContacts_!() method, and then invokes the findAll method from the Contact companion object. The prepareContacts_!() method also does two things: it first deletes all the contacts from the database and then creates three contacts: John, Joe, and Lisa. To delete all the contacts from the database, it first fetches all of them using the findAll method, which executes a select * from contacts query and returns a list of Contact objects, one for each existing row in the table. Then, it iterates over the collection using the foreach method and for each contact, it invokes the delete_! method which as you can imagine will execute a delete from contacts where id = contactId query is valid.

After deleting all the contacts from the database table, it iterates the contactsNames list, and for each element it invokes the create method of the Contact companion object, which then creates an instance of the Contact class. Once we have the instance of the Contact class, we can set the value of the name attribute by passing the value instance.name(value).

You can chain commands while working with Mapper objects because they return themselves. For example, let’s say our Contact class has firstName and lastName as attributes. Then, we could do something like this to create and save a new contact:

Contact.create.firstName(“John”).lastName(“Doe”).save()

Finally, we invoke the save() method of the instance, which will make Lift execute an insert query, thus saving the data into the database by creating a new record.

Getting back to the list method, we fetch all the contacts again by invoking the findAll method, and then create a li tag for each contact we have fetched from the database. The content of the li tags is the contact name, which we get by calling the get method of the attribute we want the value of. So, when you say contact.name.get, you are telling Lift that you want to get the value of the name attribute from the contact object, which is an instance of the Contact class.

There’s more…

Lift comes with a variety of built-in field types that we can use; MappedString is just one of them. The others include, MappedInt, MappedLong, and MappedBoolean.

All these fields come with some built-in features such as the toForm method, which returns the HTML needed to generate a form field, and the validate method that validates the value of the field.

By default, Lift will use the name of the object as the name of the table’s column; for example, if you define your object as name—as we did—Lift will assume that the column name is name.

Lift comes with a built-in list of database-reserved words such as limit, order, user, and so on. If the attribute you are mapping is a database-reserved word, Lift will append _c at the end of the column’s name when using Schemifier. For example, if you create an attribute called user, Lift will create a database column called user_c.

You can change this behavior by overriding the dbColumnName method, as shown in the following code:

object name extends MappedString(this, 100) {
    override def dbColumnName = "some_new_name"
  }

In this case, we are telling Lift that the name of the column is some_new_name. We have seen that you can fetch data from the database using the findAll method. However, this method will fetch every single row from the database.

To avoid this, you can filter the result using the By object; for example, let’s say you want to get only the contacts with the name Joe. To accomplish this, you would add a By object as the parameter of the findAll method as follows:

Contact.findAll(By(Contact.name, "Joe")).

There are also other filters such as ByList and NotBy. And if for some reason the features offered by Mapper to build select queries aren’t enough, you can use methods such as findAllByPreparedStatement and findAllByInsecureSQL where you can use raw SQL to build the queries.

The last thing left to talk about here is how this example would work if we didn’t create any table in the database. Well, I hope you remember that we added the following lines of code to the Boot.scala file:

 Schemifier.schemify(
      true, Schemifier.infoF _,
      Contact
    )

As it turns out, the Schemifier object is a helper object that assures that the database has the correct schema based on a list of MetaMappers. This means that for each MetaMapper we pass to the Schemifier object, the object will compare the MetaMapper with the database schema and act accordingly so that both the MetaMapper and the database schema match. So, in our example, Schemifier created the table for us. If you change MetaMapper by adding attributes, Schemifier will create the proper columns in the table.

See also…

Creating one-to-many relationships

In the previous recipe, we learned how to map a Scala class to a database table using Mapper. However, we have mapped a simple class with only one attribute, and of course, we will not face this while dealing with real-world applications.

We will probably need to work with more complex data such as the one having one-to-many or many-to-many relationships. An example of this kind of relationship would be an application for a store where you’ll have customers and orders, and we need to associate each customer with the orders he or she placed. This means that one customer can have many orders.

In this recipe, we will learn how to create such relationships using Mapper.

Getting ready

We will modify the code from the last section by adding a one-to-many relationship into the Contact class. You can use the same project from before or duplicate it and create a new project.

How to do it…

Carry out the following steps:

  1. Create a new class named Phone into the model package under src/main/scala/code/ using the following code: 
    package code.model

import net.liftweb.mapper._ 

class Phone extends LongKeyedMapper[Phone] with IdPK { 
  def getSingleton = Phone 

  object number extends MappedString(this, 20) 

  object contact extends MappedLongForeignKey(this, Contact) 
} 

object Phone extends Phone with LongKeyedMetaMapper[Phone] { 
  override def dbTableName = "phones" 
}
  2. Change the Contact class declaration from: 
    class Contact extends LongKeyedMapper[Contact] with IdPK

    To:

    class Contact extends LongKeyedMapper[Contact]
with IdPK
with OneToMany[Long, Contact]
  3. Add the attribute that will hold the phone numbers to the Contact class as follows: 
    object phones extends MappedOneToMany(Phone, Phone.contact, 
    OrderBy(Phone.id, Ascending)) with Owned[Phone] with Cascade[Phone]
  4. Insert the new class into the Schemifier list of parameters. It should look like the following code: 
        Schemifier.schemify( 
      true, Schemifier.infoF _, 
      Contact, 
      Phone 
    )
  5. In the Contacts snippet, replace the import statement from the following code: 
    import code.model.Contact

    To:

    import code.model._
  6. Modify the Contacts.prepareContacts_! method to associate a phone number to each contact. Your method should be similar to the one in the following lines of code: 
      def prepareContacts_!() {
    Contact.findAll().map(_.delete_!)
    val contactsNames = "John" :: "Joe" :: "Lisa" :: Nil
    val phones = "5555-5555" :: "5555-4444" :: "5555-3333"  :: "5555-2222" :: 
    "5555-1111" :: Nil

    contactsNames.map(name => {
      val contact = Contact.create.name(name)
      val phone = Phone.create.number(phones((new Random()).nextInt(5))).saveMe()
      contact.phones.append(phone)
      contact.save()
    })
  }
  7. Replace the list method’s code with the following code: 
      def list = {
    prepareContacts_!()

    ".contact *" #> Contact.findAll().map {
      contact => {
        ".name *" #> contact.name.get &
        ".phone *" #> contact.phones.map(_.number.get)
      }
    }
  }
  8. In the index.html file, replace the ul tag with the one in the following code snippet: 
     <ul> 
                <li class="contact"> 
                    <span class="name"></span> 
                    <ul> 
                        <li class="phone"></li> 
                    </ul> 
                </li> 
            </ul>
  9. Start the application.
  10. Access http://localhost:8080.

Now you will see a web page with three names and their phone numbers, as shown in the following screenshot:

How it works…

In order to create a one-to-many relationship, we have mapped a second table called phone in the same way we’ve created the Contact class. The difference here is that we have added a new attribute called contact. This attribute extends MappedLongForeignKe y, which is the class that we need to use to tell Lift that this attribute is a foreign key from another table.

In this case, we are telling Lift that contact is a foreign key from the contacts table in the phones table. The first parameter is the owner, which is the class that owns the foreign key, and the second parameter is MetaMapper, which maps the parent table.

After mapping the phones table and telling Lift that it has a foreign key, we need to tell Lift what the “one” side of the one-to-many relationship will be.

To do this, we need to mix the OneToMany trait in the Contact class. This trait will add features to manage the one-to-many relationship in the Contact class. Then, we need to add one attribute to hold the collection of children records; in other words, we need to add an attribute to hold the contact’s phone numbers.

Note that the phones attribute extends MappedOneToMany, and that the first two parameters of the MappedOneToMany constructor are Phone and Phone.contact. This means that we are telling Lift that this attribute will hold records from the phones table and that it should use the contact attribute from the Phone MetaMapper to do the join. The last parameter, which is optional, is the OrderBy object. We’ve added it to tell Lift that it should order, in an ascending order, the list of phone numbers by their id.

We also added a few more traits into the phones attribute to show some nice features. One of the traits is Owned; this trait tells Lift to delete all orphan fields before saving the record. This means that if, for some reason, there are any records in the child table that has no owner in the parent table, Lift will delete them when you invoke the save method, helping you keep your databases consistent and clean.

Another trait we’ve added is the Cascade trait. As its name implies, it tells Lift to delete all the child records while deleting the parent record; for example, if you invoke contact.delete_! for a given contact instance, Lift will delete all phone records associated with this contact instance. As you can see, Lift offers an easy and handy way to deal with one-to-many relationships.

See also…

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here