Reading serialized object from mariadb with Anorm

I have implemented reading serialized object with plain JDBC, and now want to use Anorm 2.3.8.

Plain JDBC Scala code is as bellow:

def loadModel(rName: String, rPdbCode: String) = {
  //Connection Initialization
    Class.forName("org.mariadb.jdbc.Driver")
    val jdbcUrl = s"jdbc:mysql://172.17.0.2:3306/db_profile?user=root&password=root"
    val connection = DriverManager.getConnection(jdbcUrl)

    //Reading Pre-trained model from Database
    var model: InductiveClassifier[MLlibSVM, LabeledPoint] = null
    if (!(connection.isClosed())) {

      val sqlRead = connection.prepareStatement("SELECT r_model FROM MODELS WHERE r_name = ? and r_pdbCode = ?")
      sqlRead.setString(1, rName)
      sqlRead.setString(2, rPdbCode)
      val rs = sqlRead.executeQuery()
      rs.next()

      val modelStream = rs.getObject("r_model").asInstanceOf[Array[Byte]]
      val modelBaip = new ByteArrayInputStream(modelStream)
      val modelOis = new ObjectInputStream(modelBaip)
      model = modelOis.readObject().asInstanceOf[InductiveClassifier[MLlibSVM, LabeledPoint]]

      rs.close
      sqlRead.close
      connection.close()
    } else {
      println("MariaDb Connection is Close")
      System.exit(1)
    }
    model
}

Now I want to load my serialized model with amAnorm so everything is consistent with rest of my application and I only use the default connection.

Following is my effort but I can't convert Stream[Row] to Array[Byte] as it raises an exception:

ClassCastException: scala.collection.immutable.Stream$Cons cannot be cast to [B]

The Anorm 2.3.8 code is as bellow.

def loadModel(rName: String, rPdbCode: String) = {
    //Connection Initialization
    var model: InductiveClassifier[MLlibSVM, LabeledPoint] = null
    DB.withConnection { implicit c =>
      val results = SQL(
        """
          | SELECT r_model
          | FROM MODELS
          | WHERE r_name={r_name} 
          | AND r_pdbCode={r_pdbCode};
        """.stripMargin).on(
          "r_name" -> rName,
          "r_pdbCode" -> rPdbCode).apply()
      val byteArray = results.asInstanceOf[Array[Byte]]
      val modelBaip = new ByteArrayInputStream(byteArray)
      val modelOis = new ObjectInputStream(modelBaip)
      model = modelOis.readObject().asInstanceOf[InductiveClassifier[MLlibSVM, LabeledPoint]]

    }

    model
}

This code is from Anorm 2.5 but hopefully it is not that different from what it was earlier. Assuming we have Model defined as

case class Model(i: Int, s: String) extends Serializable

here is loadModel and test-wrapper testLoadModel that calls it using a simplified table

import anorm._
import java.io._

def loadModel(rName: String): Model = {
  db.withConnection { implicit c =>
    val result = SQL"""
        SELECT r_model
        FROM MODELS
        WHERE r_name=${rName}
      """.as(SqlParser.byteArray("r_model").single)

    Logger.info(s"result ${result.getClass} => $result")
    deserialize[Model](result)
  }
}

def serialize(obj: Serializable): Array[Byte] = {
  val outBuf = new ByteArrayOutputStream()
  val out = new ObjectOutputStream(outBuf)
  out.writeObject(obj)
  out.flush()
  outBuf.toByteArray
}

def deserialize[T](byteArray: Array[Byte]): T = {
  val ois = new ObjectInputStream(new ByteArrayInputStream(byteArray))
  ois.readObject().asInstanceOf[T]
}

def testLoadModel(): Unit = {
  db.withConnection { implicit c =>
    val createRes = SQL(
      """
        |DROP TABLE   IF EXISTS MODELS;
        |
        |CREATE TABLE MODELS(
        | r_name  VARCHAR(50) PRIMARY KEY NOT NULL,
        | r_model  VARBINARY NOT NULL
        |         );
      """.
        stripMargin).execute()
    Logger.info(s"Create result = $createRes")
    val rName = "rName"
    val m0 = Model(42, "Abc")
    val ser0 = serialize(m0)

    val insertRes = SQL(
      """
        | insert into MODELS values ({r_name},{r_model})
      """
        .stripMargin).on("r_name" -> rName, "r_model" -> ser0).executeInsert()
    Logger.info(s"Insert result = $insertRes")

    val m1 = loadModel(rName)
    Logger.info(s"m0 = $m0")
    Logger.info(s"m1 = $m1")
  }
}

The main trick seems to be in using .as(SqlParser.byteArray("r_model").single).

Note that you might want to use .singleOpt instead if existence of the record is not guaranteed. Also it might make sense to use LIMIT 1 SQL clause in you query to get better performance.