Exposed: Postgres: Allow casts in prepared statements

[hannesstruss]

For certain data types like jsonb, Postgres requires casts like so:

val stmt = "insert into Recipes (id, title, ingredients) values (?, ?, ?::jsonb)"

I have found some support for casts in Expression<*>, but I can't piece together how to use that for my insert query – appears like it's not meant to be used for that.

What might be a solution though: let QueryBuilder ask ColumnType for the placeholder to use in prepared statements (default: ?, but overridable). QueryBuilder already has access to the ColumnType, so this should be a minimal change.

Happy to take a stab at this if the direction makes sense!

[hannesstruss]

This change would do the trick for me:

diff --git a/src/main/kotlin/org/jetbrains/exposed/sql/ColumnType.kt b/src/main/kotlin/org/jetbrains/exposed/sql/ColumnType.kt
index 1ec2a1b..06fe18d 100644
--- a/src/main/kotlin/org/jetbrains/exposed/sql/ColumnType.kt
+++ b/src/main/kotlin/org/jetbrains/exposed/sql/ColumnType.kt
@@ -61,6 +61,8 @@ abstract class ColumnType(var nullable: Boolean = false, var autoinc: Boolean =
     override fun toString(): String {
         return sqlType()
     }
+
+    open fun statementPlaceholder() = "?"
 }

 class EntityIDColumnType<T:Any>(val idColumn: Column<T>, autoinc: Boolean = idColumn.columnType.autoinc): ColumnType(autoinc = autoinc) {
@@ -334,4 +336,4 @@ class UUIDColumnType() : ColumnType(autoinc = false) {
         else -> error("Unexpected value of type UUID: $value")
     }

-}
\ No newline at end of file
+}
diff --git a/src/main/kotlin/org/jetbrains/exposed/sql/Expression.kt b/src/main/kotlin/org/jetbrains/exposed/sql/Expression.kt
index 7eecfee..9511413 100644
--- a/src/main/kotlin/org/jetbrains/exposed/sql/Expression.kt
+++ b/src/main/kotlin/org/jetbrains/exposed/sql/Expression.kt
@@ -13,7 +13,7 @@ class QueryBuilder(val prepared: Boolean) {
         return argumentsAndStrings.map {
             if (prepared) {
                 args.add(sqlType to it.first)
-                "?"
+                sqlType.statementPlaceholder()
             } else {
                 it.second
             }

[Tapac]

Hi @hannesstruss , can you link me to PostgreSQL documentation describing that feature?

[hannesstruss]

@Tapac see 4.2.9 here: https://www.postgresql.org/docs/9.6/static/sql-expressions.html

Instead of the ?::jsonb syntax the portable CAST(? AS jsonb) can also be used, but that doesn't make a difference in my case, since QueryBuilder always emits "?" as the placeholder for prepared statements.

I did however just find out that overriding ColumnType.setParameter and using stmt.setObject works when using a PGobject its type set to jsonb. That seems to be the idiomatic way – closing this, thanks!

[salman-ahmad]

@hannesstruss

can you please share your solution i.e., sample code? Thank you!

[hannesstruss]

@msalman86 sure, e.g. for a list of strings:

class StringListColumnType : ColumnType() {
  private val gson = Gson()
  private val listOfString = object : TypeToken<List<String>>() {}.type

  override fun sqlType() = "jsonb"

  override fun setParameter(stmt: PreparedStatement, index: Int, value: Any?) {
    val json = gson.toJson(value, listOfString)
    val obj = PGobject()
    obj.type = "jsonb"
    obj.value = json
    stmt.setObject(index, obj)
  }

  override fun valueFromDB(value: Any): Any {
    if (value is PGobject) {
      return gson.fromJson(value.value, listOfString)
    }
    throw RuntimeException("Can't parse object: $value")
  }
}