Integrate Jquery DataTable with Scala Play Framework

Table of contents

Reading Time: 3 minutes

In this blog post, I’ll explain the integration of DataTable jQuery plugin with Scala Playframework.

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool and provide many feaures.
->Variable length pagination
->On-the-fly filtering
->Multi-column sorting with data type detection
->Smart handling of column widths
->Display data from almost any data source
-DOM
-Javascript array
-Ajax source
-Server side processing

We are going to build application using data source server side processing. Here is the steps to integrate datatable:

1- Create Play Application

$ play new DataTable

2- Set Database
sudo -u postgres psql postgres
sudo -u postgres createdb mydb
3- Set up Database credential and Driver in application.conf

db.default.driver=org.postgresql.Driver
db.default.url=”jdbc:postgresql://localhost:5432/mydb”
db.default.user=”postgres”
db.default.password=”postgres”
4- Create Table through Slick 2.0


// A Suppliers table with 6 columns: id, name, street, city, state, zip
trait DomainComponent {
 val Suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]
 class Suppliers(tag: Tag) extends Table[Supplier](tag, "SUPPLIERS") {
 def id: Column[Int] = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
 def name: Column[String] = column[String]("SUP_NAME")
 def street: Column[String] = column[String]("STREET")
 def city: Column[String] = column[String]("CITY")
 def state: Column[String] = column[String]("STATE")
 def zip: Column[String] = column[String]("ZIP")

// Every table needs a * projection with the same type as the table's type parameter
 def * = (id, name, street, city, state, zip) <> (Supplier.tupled, Supplier.unapply)
 }
}
case class Supplier(id: Int = 0, name: String, street: String, city: String, state: String, zip: String) {
 def toJson: JsValue = Json.toJson(
 Map(
 "0" -> Json.toJson(id),
 "1" -> Json.toJson(name),
 "2" -> Json.toJson(street),
 "3" -> Json.toJson(city),
 "4" -> Json.toJson(state),
 "5" -> Json.toJson(zip)))

}

5- Download datatable library jquery.dataTables.min.js and css file jquery.dataTables.css and put those files and images in related folders

6- Define route for datatable source in routes file

# Routes
# This file defines all application routes (Higher priority routes first)
# ~~~~

# Home page
GET / controllers.Application.index
GET /search controllers.Application.list()

# Map static resources from the /public folder to the /assets URL path
GET /assets/*file controllers.Assets.at(path=”/public”, file)

7- Write Database abstraction layer (SuppliersDAL.scla)


trait SuppliersMetaData {
 def insertSupplier(supplier: Supplier): Int
 def updateSupplier(supplier: Supplier): Int
 def deleteSupplier(supplierId: Int): Int
 def getSuppliers: List[Supplier]

}

class SuppliersDAL extends SuppliersMetaData with DomainComponent {

def insertSupplier(supplier: Supplier): Int = {
 DBConfig.db.withSession { implicit session =>
 Suppliers.insert(supplier)
 }
 }

def updateSupplier(supplier: Supplier): Int = DBConfig.db.withSession { implicit session =>
 Suppliers.filter { f => f.id === supplier.id }.update(supplier)
 }

def deleteSupplier(supplierId: Int): Int = DBConfig.db.withSession { implicit session =>
 Suppliers.filter { f => f.id === supplierId }.delete
 }

def getSuppliers: List[Supplier] = DBConfig.db.withSession { implicit session =>
 Suppliers.list
 }

def getSuppliersByKeyWord(params: DataTableParam): List[Supplier] = DBConfig.db.withSession { implicit session =>
 params.sSearch.isEmpty match {
 case true => SuppliersDAL.getSuppliers.drop(params.iDisplayStart).take(params.iDisplayLength)
 case false => (for (s <- Suppliers if (s.name.startsWith(params.sSearch))) yield (s)).list
 }
 }
}

case class DataTableParam(sSearch: String, iDisplayStart: Int, iDisplayLength: Int, iSortCol: Int, sSortDir: String)
object SuppliersDAL extends SuppliersDAL

8- Define Action for datatable source (Application.scala)


def list = Action { implicit request =>

val params = DataTableParam(
 request.getQueryString("sSearch").getOrElse(""),
 request.getQueryString("iDisplayStart").getOrElse("0").toInt,
 request.getQueryString("iDisplayLength").getOrElse("10").toInt,
 request.getQueryString("iSortCol_0").getOrElse("1").toInt,
 request.getQueryString("sSortDir_0").getOrElse("asc"))

val totalSuppliers = SuppliersDAL.getSuppliers.length

val suppliers =SuppliersDAL.getSuppliersByKeyWord(params)

val suppliersOrderBy = (params.iSortCol, params.sSortDir) match {
 case (1, "asc") => suppliers.sortBy(_.name)
 case (1, "desc") => suppliers.sortWith(_.name > _.name)
 case (2, "asc") => suppliers.sortBy(_.street)
 case (2, "desc") => suppliers.sortWith(_.street > _.street)
 case (5, "asc") => suppliers.sortBy(_.zip)
 case (5, "desc") => suppliers.sortWith(_.zip > _.zip)
 case (_, _) => suppliers.sortBy(_.name)
 }

val jsonObject = Json.toJson(Map("aaData" -> suppliersOrderBy.map(_.toJson)))

val dataTableJson = Json.toJson(Map("iTotalRecords" -> totalSuppliers, "iTotalDisplayRecords" -> totalSuppliers))
 .as[JsObject].deepMerge(jsonObject.as[JsObject])

Ok(dataTableJson)
 }

9- Define Table Template for datatable and initialize datatable (index.scala.html)


<table class="display table table-striped table-bordered" id="student">
 <thead>
 <tr>
 <th>Id</th>
 <th>Name</th>
 <th>Street</th>
 <th>City</th>
 <th>State</th>
 <th>Zip</th>
 </tr>
 </thead>
 <tbody>
 </tbody>
</table>

Initialize datatable :


<script type="text/javascript">
 /* Table initialisation */
 $(document).ready(function() {
 $('#student').dataTable({
 "bProcessing" : true,
 "bServerSide" : true,
 "sAjaxSource" : "/search"
 });
 });
</script>

DataTable will look like: