How to select multiple column from database using scala Play2.6 and with proper json response header

0
votes

I have one table User details Table and it contains below Columns

User_ID,Name,email,Mobile_no,firstname,lastname,Address

I want to select these 4 columns(User_ID,Name,email,Mobile_no) data whose user id is 1 and i want the response as in proper JSON Format

How to do that In Scala Play 2.6 with Slick Till now I have done this

import play.api.mvc._
//import slick.driver.H2Driver.api._
import slick.jdbc.H2Profile.api._
import Services.HorseImageServices
import scala.concurrent.{Await, Future}
import scala.concurrent.duration._
import com.google.gson.Gson

case class userss(User_ID:String,Name:String,email:String,Mobile_no:String)
class Testing @Inject()(cc: ControllerComponents) extends AbstractController(cc){


  def getPasswqord(username :String) = Action{
    import play.api.libs.json.{JsPath, JsValue, Json, Writes}
    val dbConfig = Database.forURL("jdbc:mysql://localhost:3306/equineapp?user=root&password=123456", driver = "com.mysql.jdbc.Driver")
    val a1=(sql"""select User_ID,Name,email,Mobile_no from equineapp.user_details_table where email=$username or Mobile_no=$username """.as[(String,String,String,String)]     )
    val res = Await.result(dbConfig.run(a1), 1000 seconds)

    val gson: Gson = new GsonBuilder().setFieldNamingPolicy(FieldNamingPolicy.UPPER_CAMEL_CASE).create

    //   val json = Json.toJson(res)
    //select User_ID,Name,email,Mobile_no from equineapp.user_details_table where email='' or Mobile_no=''
   // val writes : Writes[(String, String,String,String)] = (JsPath \ "password").write[String] and (JsPath \ "password").write[String]
    import play.api.libs.json._
    import play.api.libs.functional.syntax._

    implicit val locationWrites: Writes[(userss)] = (
      (JsPath \ "userid").write[String] and
        (JsPath \ "Name").write[String] and
        (JsPath \ "Email").write[String] and
        (JsPath \ "Mobile").write[String]
      )(unlift(userss.unapply))
    def toJson[T](sequence: Seq[T])(implicit writes: Writes[T]): JsValue = {
      Json.toJson(sequence)
    }

    implicit val residentWrites = Json.writes[(String,String,String,String)]
    Ok(toJson(res)(residentWrites))

   // Ok(Json.toJson(res.map(gson.toJson).map(Json.parse)))

  }

}

from this i am getting response in a format

[{"_1":"86","_2":"Abhinay","_3":"[email protected]","_4":"9739239812"}]

Expected output format is

[{"userid":"86","Name":"Abhinay","Email":"[email protected]","Mobile":"9739239812"}]

2
scalaplayframeworkslick

2 Answers

0
votes

You are using the wrong custom writer.

You need to use locationWrites instead of residentWrites

Your solution: Ok(toJson(res)(locationWrites))

0
votes

Suppose you have case class:

case class User(userId: String, name: String, email: String, mobilePhone: String)

To be able to transform result of sql"""select User_ID,Name,email,Mobile_no from equineapp.user_details_table where email=$username or Mobile_no=$username """ to User you need to define implicit GetResult:

implicit val getUserResult = GetResult(r => User(r.nextString, r.nextString, r.nextString, r.nextString))

Bring this getUserResult into scope where you call database and transform result of query to case class sql"""your sql query""".as[User]

To be able to convert User instance to json define Companion object with custom Writes[(User)]:

object User {
  implicit val jsonWrites: Writes[(User)] = (
      (JsPath \ "userid").write[String] and
        (JsPath \ "Name").write[String] and
        (JsPath \ "Email").write[String] and
        (JsPath \ "Mobile").write[String]
      )(unlift(User.unapply))
}

Now you can transform result to json in controller in this way(Json writes will be find automatically because it defined in companion object):

Ok(toJson(res))