How to insert an Image data int mySql mediumtext field using java/scala?

Question

I am trying to insert an image file into mediumtext field of mySql database. The idea is to get the byteStream from image file using Java IO and the converting the byteStream to a reader (Which i call character Stream of UTF-8) . Then I am inserting the reader data into mysql mediumtext field . But while I am running my code I am getting Error saying invalid string . Both code and logs are provided below .Please advise me , if i am missing anything here .

Note:-I am successful to store data in longblob field

Code:-

 object SimpleApp extends App{

        Class.forName("com.mysql.jdbc.Driver")
        val jdbcUsername = "root"
        val jdbcPassword = "mysqladmin"
        val jdbcHostname = "XXXXXXX"
        val jdbcPort = 3306
        val jdbcDatabase ="XXXXXX"

         val ImgPath = new File("C:\\Users\\ezswapr\\IdeaProjects\\DataStructure\\org.priyaranjan.ds\\src\\output\\" + "Desert.jpg")
         val fileinput = new FileInputStream(ImgPath)
         val reader =new InputStreamReader(fileinput,"UTF-8") // Converting byte sream to character stream of UTF-8


         val jdbcUrl = s"jdbc:mysql://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}?user=${jdbcUsername}&password=${jdbcPassword}"
         val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
         val stmt = connection.createStatement();
         val sql= s""" create table test_blob (data mediumtext) DEFAULT CHARSET=utf8"""
         stmt.executeUpdate(sql)

         val ps= connection.prepareStatement("insert into test_blob(data) values(?);")
         ps.setCharacterStream(1,reader)
         ps.execute()
         connection.isClosed()
    }

Error:-

Exception in thread "main" java.sql.SQLException: Incorrect string value: '\xF0\x94\x91\xA9\xEF\xBF...' for column 'data' at row 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:116)
    at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1867)
    at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1155)
    at SimpleApp$.delayedEndpoint$SimpleApp$1(SarojCode.scala:74)
    at SimpleApp$delayedInit$body.apply(SarojCode.scala:18)
    at scala.Function0$class.apply$mcV$sp(Function0.scala:34)
    at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.collection.immutable.List.foreach(List.scala:381)
    at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:35)
    at scala.App$class.main(App.scala:76)
    at SimpleApp$.main(SarojCode.scala:18)
    at SimpleApp.main(SarojCode.scala)

Answer 1

If you must store image data in your dbms, you should use MEDIUMBLOB rather than MEDIUMTEXT data. Why? Images (JPEG, PNG, GIF, and the like) contain binary data, but both Java and MySQL want to interpret your image data as text (utf8 text in your case). But they gack on your image because it's not valid text.

With this line of code you are asserting that your file contains data in the utf8 format and it should be converted to Java's internal Unicode representation as it is read from the file into RAM.

val reader =new InputStreamReader(fileinput,"UTF-8")

That's not true. Your JPEG file does not contain UTF-8 data. It makes no sense to try to turn it into Unicode characters. That's what your incorrect string value exception means.

But, Pro tip: Store image content in a file system and store the path to the image in the database. Why? Serving the image out over the web is faster that way. Plus, writing the code to save and retrieve images from a DBMS gives you a pain in the neck, as you have discovered.