Skip to content

Instantly share code, notes, and snippets.

@quangIO
Last active October 4, 2023 10:47
Show Gist options
  • Save quangIO/a623b5caa53c703e252d858f7a806919 to your computer and use it in GitHub Desktop.
Save quangIO/a623b5caa53c703e252d858f7a806919 to your computer and use it in GitHub Desktop.
import com.fasterxml.jackson.databind.ObjectMapper
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.ColumnType
import org.jetbrains.exposed.sql.Table
import org.postgresql.util.PGobject
import java.sql.PreparedStatement
/**
* Created by quangio.
*/
fun <T : Any> Table.jsonb(name: String, klass: Class<T>, jsonMapper: ObjectMapper): Column<T>
= registerColumn(name, Json(klass, jsonMapper))
private class Json<out T : Any>(private val klass: Class<T>, private val jsonMapper: ObjectMapper) : ColumnType() {
override fun sqlType() = "jsonb"
override fun setParameter(stmt: PreparedStatement, index: Int, value: Any?) {
val obj = PGobject()
obj.type = "jsonb"
obj.value = value as String
stmt.setObject(index, obj)
}
override fun valueFromDB(value: Any): Any {
value as PGobject
return try {
jsonMapper.readValue(value.value, klass)
} catch (e: Exception) {
e.printStackTrace()
throw RuntimeException("Can't parse JSON: $value")
}
}
override fun notNullValueToDB(value: Any): Any = jsonMapper.writeValueAsString(value)
override fun nonNullValueToString(value: Any): String = "'${jsonMapper.writeValueAsString(value)}'"
}
@alacoste
Copy link

I had trouble with this code as it happens that "valueFromDB(...)" is not always called with PGobject. Sometimes it is called with T itself (I believe it has to do with caching, not really sure though).

Here is the fix I used:

    override fun valueFromDB(value: Any): Any {
        if (value !is PGobject) {
            // We didn't receive a PGobject (the format of stuff actually coming from the DB).
            // In that case "value" should already be an object of type T.
            return value
        }

        // We received a PGobject, deserialize its String value.
        return try {
            jsonMapper.readValue(value.value, klass)
        } catch (e: Exception) {
            e.printStackTrace()
            throw RuntimeException("Can't parse JSON: $value")
        }
    }

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment