ScalikeJDBC SQLSyntaxSupport with play scala 2.4 tutorial I

Continue last tutorial about ScalikeJDBC,today i will introduce you one more thing : SQLSyntaxSupport

 


1 - what is SQLSyntaxSupport
    SQLSyntaxSupport is one step short of ORM. In other words, it’s a powerful DRY way to write SQL.
    As you known an ORM (Object-Relational Mapping) is a tool that lets you query and manipulate data from a database using an object paradigm.
    It's a completely ordinary library written in your language that encapsulates the code needed to manipulate the data, so you don't use SQL anymore, but directly an object of your language.

2 - how it work
    Continue with last tutorial : scalikejdbc with play scala 2.4 tutorial  ,i will rewrite those query with ORM
    In above tutorial we have this query :
          val emailAddress =  "hoaipt@example.com"
          val runSelectQuery : Option[String] = sql"select * from users where email = ${emailAddress}”
                                                                            .map { rs => rs.string("email") }
                                                                            .list().apply()
  Let rewrite this query with ORM
*) first define object
i will define User object:

case class User(id: String, email: String)

object User extends SQLSyntaxSupport[User] {
  // If the table name is same as snake_case'd name of this companion object,
  // you don't need to specify tableName explicitly.
  override val tableName = "users"

  def apply(u: ResultName[User])(rs: WrappedResultSet) =
    new User(rs.string(u.id), rs.string(u.email))
}

 *) let build your ORM query :
          val u = User.syntax("u")
          val emailAdd =  "hoaipt@example.com"
          val testQueryList = withSQL {select.from(User as u).where.eq(u.column("email"),emailAdd)}
                                              .map(User(u.resultName))
                                              .list().apply()

1.  withSQL { select.from(User as u)} : new way to define your query
2.  map(User(u.resultName)) : define output,this time out put is a User
3. list() : format for output ,list() return List,in this case it will return List[User]
4. apply() : just apply,you need it to finish your query

*)some example
    // use if result 1 row and only for select
    val emailAdd =  "hoaipt@example.com"
    val testQuerySingle = withSQL { select.from(User as u).where.eq(u.column("email"),emailAdd)}
                                            .map(User(u.resultName)).single().apply()
     // if your query return more than one rows,use list()
    val testQueryList = withSQL { select.from(User as u)}
                                        .map(User(u.resultName)).list().apply()
     // toList() basicly same with list()
    val testQueryToList = withSQL { select.from(User as u)}
                                            .map(User(u.resultName)).toList().apply()
     // collection() return Vestor ,and you do not need apply()
    val testQueryCollection = withSQL { select.from(User as u)}
                                                  .map(User(u.resultName)).collection()
     //return true if your query run well and false if not
    val testQueryExecute = withSQL { select.from(User as u)}
                                               .map(User(u.resultName)).execute().apply()
    //insert
    val testQueryUpdate = withSQL {insert.into(User).values("value 1","value 2","value 3")}
                                             .update().apply()

Just all for today,next tutorial i will explain more detail about SQL Syntax Support
Thanks and leave your comment