Json response In scala slick

Question

I am executing Store Procedure In Scala controller class but not able to get result In proper json Format

    val dbConfig = Database.forURL("jdbc:mysql://localhost:3306/equineapp?user=root&password=123456", driver = "com.mysql.jdbc.Driver")
    val setup1 =  sql"call HorsePrfile ($HorseId);".as[(Int,String)]
    val res = Await.result(dbConfig.run(setup1), 1000 seconds)
 //   val json = Json.toJson(res)
    Ok(Json.toJson(res.toList))

can anyone tell me how to return json response from above code with header also

this is my model class

case class HorseProfile(HorseID: Int,HorsePicUrl:String)
case class HorseProfileData(HorseID: Int, HorsePicUrl:String)
object HorseForm1 {

  val form = Form(
    mapping(
      "HorseID" -> number,
      "HorsePicUrl" ->nonEmptyText ,


    )(HorseProfileData.apply)(HorseProfileData.unapply)
  )
  implicit val fooWrites: Writes[HorseProfile] = (
    (__ \ 'HorseID).write[Int] and (__ \ 'HorsePicUrl).write[String]
    )(foo => (foo.HorseID, foo.HorsePicUrl))
 // val horsepics = TableQuery[HorseProfilePicDef]

}
//
//
class HorseProfilePicDef(tag: Tag) extends Table[HorseProfile](tag, "horse_profile_image") {

  def HorseID1 = column[Int]("horseid")
  def HorsePicUrl = column[String]("horseimage")
  //def HorsePics = column[Blob]("horseimage")

 // def * = (HorseID, HorsePics)
  override def * =
    (HorseID1, HorsePicUrl) <>(HorseProfile.tupled, HorseProfile.unapply)
}

object HorseProfilePics {
  val dbConfig = Database.forURL("jdbc:mysql://localhost:3306/equineapp?user=root&password=123456", driver = "com.mysql.jdbc.Driver")
  //val dbConfig1 = dbConfig.get[JdbcProfile](Play.current)
  val horsepics = TableQuery[HorseProfilePicDef]
  implicit val HorseProfile: Writes[HorseProfile] = Json.writes[HorseProfile]
  implicit val userJsonFormat = Json.format[HorseProfile]





//  val itemWrites: OWrites[(HorseProfile)] = (
//    (__ \ "flight").write[HorseProfile]
////      (__ \ "scheduledFlight").write[ScheduledFlight] and
////      (__ \ "airline").write[Airline] and
////      (__ \ "airport1").write[Airport] and
////      (__ \ "airport2").write[Airport]
//    ).tupled
  implicit val testWriter: OWrites[HorseProfile] = Json.writes[HorseProfile]
  //val resultWrites: Writes[Seq[( HorseProfile )]] = Writes.seq(HorseProfile)
  implicit def seqWrites[T](implicit fmt: Writes[T]): Writes[Seq[T]] = new Writes[Seq[T]] {
    def writes(ts: Seq[T]) = JsArray(ts.toList.map(t => toJson(t)(fmt)))

  }


//  def HorseImage(HorseId : Int): Future[String] = {
//
//
//    val setup1 = sql"call HorsePrfile ($HorseId);".as[(Int, String)]
//
//
//    //val res = Await.result(dbConfig.run(setup1), 1000 seconds)
//    dbConfig.run(setup1).map(seqWrites =>seqWrites.toString).recover {
//      case ex: Exception => ex.getCause.getMessage
//
//
//    }
//  }



//  def writes(tweet: HorseProfile): JsValue = {
//    //  tweetSeq == Seq[(String, play.api.libs.json.JsString)]
//    val tweetSeq = Seq(
//      "username" -> JsString(tweet.HorsePicUrl)
//
//    )
//    JsObject(tweetSeq)
//  }



//  implicit val locationWrites: Writes[HorseProfile] = (
//    (JsPath \ "HorseID").write[Int] and
//      (JsPath \ "HorsePicUrl").write[String]
//    )(unlift(HorseProfile.))

  val resultWrites: Writes[Seq[(HorseProfile)]] = Writes.seq(testWriter)
//  def HorseImage( HorseId : Int): Future[String]= {
//    val a1=horsepics.filter(i => i.HorseID === HorseId)
//    dbConfig.run(horsepics.filter(_.HorseID === HorseId).result.headOption).map(results =>results.toString)
//
//   // dbConfig.run(horsepics.filter(_.HorseID === HorseId).result.headOption
//
//  }


//  def addnew(HorseId:Int,Horsepicurl:String):Future[String]=
//  {
//
//    val project = HorseProfile(HorseId,Horsepicurl)
//    dbConfig.run(HorseProfilePics+=project).map(res => "User successfully added").recover {
//      case ex: Exception => ex.getCause.getMessage
//    }
//
//    //dbConfig.run(users.insertOrUpdate(project))
//  }



  def gethorseimage(HorseId : Int):Future[Seq[HorseProfile]] = {
   // dbConfig.run(horsepics.filter(i => i.HorseID === HorseId ).result.headOption)
   val set=horsepics.filter(_.HorseID1 === HorseId )

    dbConfig.run(set.result)


  }
//

I have declared column here as string but in my database I have declared this column as BLOB and trying to insert data and retrive data as Json format

Answer 1

First of all never ever use Await.result in your actual implementation. What is the point of using Slick, if you don't want to use its concurrent abilities?

Second, you need to map the result, something like:

dbCall.map{
  returnedData => 
    ??? //The following step 3 and four here
}.recover{
  //In case something went wrong with your DB call.
  case e => InternalServerError("Db failure")
}  

Third, you need to represent your returnedData as a case class.

Fourth you use the Json writer to turn a case class into its Json representation:

implicit val someVal: OWrites[SomeCaseClass] = Json.writes[SomeCaseClass]
Json.toJson(someVal)

Update

So based on your comment, I implemented it, such that it uses your return type and turns it into Json. Here it is:

import play.api.libs.json.{Json, OWrites}

case class HorseProfile(i: Int, value: String)

val dbResult: Option[HorseProfile] = Some(HorseProfile(77,"iVBORw0KGgoAAAANSUhE"))

implicit val horseProfileWrites: OWrites[HorseProfile] = Json.writes[HorseProfile]

Json.toJson(dbResult)

And the result I'm getting is this:

res0: play.api.libs.json.JsValue = {"i":77,"value":"iVBORw0KGgoAAAANSUhE"}