Connecting Liftweb to MySQL

After successfully creating a liftweb app, here is how to connect liftweb to mysql and get things done.

The structure of the liftweb app is as follows:

prayag@prayag:~/workspace_lift/gwitter/gwitter$ ls -l
total 92
-rw-rw-r-- 1 prayag prayag 18432 Oct 28 10:45 lift_proto.db.h2.db
-rw-rw-r-- 1 prayag prayag 42000 Oct 28 10:45 lift_proto.db.trace.db
-rw-rw-r-- 1 prayag prayag  7459 Oct 28 11:10 pom.xml
drwxrwxr-x 3 prayag prayag  4096 Oct 16 12:22 project
drwxrwxr-x 4 prayag prayag  4096 Oct 16 12:22 src
drwxrwxr-x 9 prayag prayag  4096 Oct 28 11:14 target

STEP 1 configure db properties

prayag@prayag:~/workspace_lift/gwitter/gwitter$ sudo kate src/main/resources/props/default.props 

# Properties in this file will be read when running in dev mode
db.dialect=org.hibernate.dialect.MySQL5Dialect
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost/Gwitter
db.user=root
db.password=root

STEP 2 configure Database Vendor in Boot.scala

prayag@prayag:~/workspace_lift/gwitter/gwitter$ sudo kate src/main/scala/bootstrap/liftweb/Boot.scala

/**
 * A class that's instantiated early and run.  It allows the application
 * to modify lift's environment
 */
class Boot {
  def boot {
    //add the following code in method boot
    if (!DB.jndiJdbcConnAvailable_?) {
      println("jndiJdbcConnAvailable_ : " + jndiJdbcConnAvailable_);
      val vendor = 
 new StandardDBVendor(Props.get("db.driver") openOr "org.h2.Driver",
        Props.get("db.url") openOr "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",
        Props.get("db.user"), 
        Props.get("db.password"))

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

      DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)
    }//end of jndiJdbcConnAvailable_
 }//end of boot
}

STEP 3 Add maven dependency for mysql in pom.xml

<dependencies>
   ....
   .....
   .......
    <!-- for LiftConsole -->
    <dependency>
      <groupId>org.scala-lang</groupId>
      <artifactId>scala-compiler</artifactId>
      <version>${scala.version}</version>
      <scope>test</scope>
    </dependency>

    <!-- mysql dependency-->
    <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.17</version>
     <type>jar</type>
    </dependency>
  </dependencies>

STEP 4 Update maven dependency
prayag@prayag:~/workspace_lift/gwitter/gwitter$ mvn install

STEP 5 - Have a look at model
http://exploring.liftweb.net/master/index-2.html#lst:PocketChange-User-entity

prayag@prayag:~/workspace_lift/gwitter/gwitter$ sudo kate src/main/scala/com/zam/gwitter/model/User.scala

5.A)
Create a User class extending the Mapper base class MegaProtoUser, which provides default fields and methods
for a User.

class User extends MegaProtoUser[User] {
  def getSingleton = User // what's the "meta" server
                          // reference to the companion object below

  // define an additional field for a personal essay
  object textArea extends MappedTextarea(this, 2048) {
    override def textareaRows  = 10
    override def textareaCols = 50
    override def displayName = "Personal Essay"
  }
  
  //def findUser : User = 
    /User.find(By(User.id, this.id))
}

5.B)
Create a "companion object" to the User class (above).
The companion object is a "singleton" object that shares the same name as its companion class.
It provides global (i.e. non-instance) methods and fields, such as find, dbTableName, dbIndexes, etc.

object User extends User with MetaMegaProtoUser[User] {
  override def dbTableName = "User" // define the DB table name, I love to use singular names for tables
  override def screenWrap = Full( <lift:surround with="default" at="content">
           <lift:bind />
       </lift:surround>
      )
  // define the order fields will appear in forms and output
  override def fieldOrder = List(id, 
                                 firstName, 
                                 lastName, 
                                 email,
                                 locale, 
                                 timezone, 
                                 password, 
                                 textArea)

  // comment this line out to require email validations
  override def skipEmailValidation = true
}

STEP 6 Create database Gwitter at mysql server
mysql>create database Gwitter;

STEP 7 Run the app
prayag@prayag:~/workspace_lift/gwitter/gwitter$ mvn jetty:run

I get the message like the following in between :

[INFO] Scanning for projects...
...
...
...
11:18:52.922 [main] DEBUG net.liftweb.util.Props - Loaded key/value properties from resource /props/default.props
11:18:53.017 [main] DEBUG net.liftweb.mapper.MetaMapper - Initializing MetaMapper for User
11:18:53.018 [main] DEBUG net.liftweb.mapper.MetaMapper - Created FieldFinder for interface net.liftweb.mapper.MappedField
11:18:53.787 [main] DEBUG net.liftweb.db.ProtoDBVendor - Created new pool entry. name=ConnectionIdentifier(lift), poolSize=1
11:18:53.830 [main] DEBUG net.liftweb.mapper.Schemifier - Starting schemify. write=true, structureOnly=false, dbId=ConnectionIdentifier(lift), schema=esewa@10.20.222.254, tables=List(users)
11:18:53.832 [main] DEBUG net.liftweb.mapper.Schemifier - Running beforeSchemifier on table users
11:18:53.992 [main] DEBUG net.liftweb.mapper.Schemifier - Executing DDL statements
11:18:53.993 [main] DEBUG net.liftweb.mapper.Schemifier - Running afterSchemifier on table users
11:18:53.995 [main] DEBUG net.liftweb.db.ProtoDBVendor - Released connection. poolSize=1
2012-10-28 11:18:54.232:INFO::Started SelectChannelConnector@0.0.0.0:8080
[INFO] Started Jetty Server
[INFO] Starting scanner at interval of 5 seconds.

STEP 8 Check database

prayag@prayag:~$ mysql -u root -p
Enter password:

mysql> use Gwitter;

mysql> show tables;
+------------------------+
| Tables_in_Gwitter |
+-----------------------+
| users                      |
+-----------------------+
1 row in set (0.00 sec)


mysql> desc users;
+------------------+-------------------------+------+-----+-------------+--------------------+
| Field            | Type       | Null | Key | Default    | Extra                 |
+-------------------+-------------------------+------+-----+------------+--------------------+
| id                     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| firstname         | varchar(32)              | YES  |     | NULL      |                           |
| lastname          | varchar(32)              | YES  |     | NULL      |                           |
| email               | varchar(48)               | YES  | MUL | NULL|                           |
| locale              | varchar(16)               | YES  |     | NULL      |                           |
| timezone         | varchar(32)              | YES  |     | NULL      |                           |
| password_pw  | varchar(48)              | YES  |     | NULL      |                           |
| password_slt   | varchar(20)             | YES  |     | NULL      |                           |
| textarea           | varchar(2048)        | YES  |     | NULL      |                           |
| uniqueid         | varchar(32)              | YES  | MUL | NULL|                           |
| validated         | tinyint(1)                  | YES  |     | NULL      |                           |
| superuser        | tinyint(1)                 | YES  |     | NULL      |                           |
+-----------------+-------------------------+-------+-----+------------+---------------------+
12 rows in set (0.01 sec)

REFERENCES
1 - How to create database connection with MySQL in Liftweb Scala, available at http://www.jooiner.com/how-to-create-database-connection-with-mysql-in-liftweb-scala/

2 - 2.1 Defining the Model, http://exploring.liftweb.net/master/index-2.html#lst:PocketChange-User-entity

3 - Scala/Lift Database Connections, http://stackoverflow.com/a/5026630/432903

4 - Mapper, https://www.assembla.com/spaces/liftweb/wiki/Mapper

5 - pocketchange app, https://github.com/tjweir/pocketchangeapp/tree/master/PocketChange/src/main

6 - How to create Simple Mini Blog With LiftWeb, https://github.com/slashmili/LiftWeblogIn30Mins/wiki/SimpleMiniBlog