Jdbi: BindList can't find my named parameter in query and throws exception

[johanneszink]

I got a SQL Query with a named parameter :featureIds

const val selectGeoCoordinates = "SELECT gg.feature_id, gg.latitude, gg.longitude, gg.order, gg.group FROM geo.geo_coordinates as gg WHERE gg.feature_id IN (:featureIds);"

When I try to bind a list of Ints to the named parameter

val values: List<Int> = listOf(4138, 1752, 1161, 371, 1860, 5498)
val geoCoordinatesList = geo.withHandle<List<GeoCoordinates>, Exception> { handle ->
                handle.createQuery(selectGeoCoordinates).bindList("featureIds", values)
                        .mapTo<GeoCoordinates>().list()
            }

I get the following exception on execution:

    Caused by: org.jdbi.v3.core.statement.UnableToExecuteStatementException: Unable to execute, no named parameter matches 'featureIds'. [statement:"SELECT gg.feature_id, gg.latitude, gg.longitude, gg.order, gg.group FROM geo.geo_coordinates as gg WHERE gg.feature_id IN :featureIds ;", rewritten:"SELECT gg.feature_id, gg.latitude, gg.longitude, gg.order, gg.group FROM geo.geo_coordinates as gg WHERE gg.feature_id IN :featureIds ;", parsed:"ParsedSql{sql='SELECT gg.feature_id, gg.latitude, gg.longitude, gg.order, gg.group FROM geo.geo_coordinates as gg WHERE gg.feature_id IN ? ;', parameters=ParsedParameters{positional=false, parameterNames=[featureIds]}}", arguments:{ positional:{}, named:{__featureIds_0:4138,__featureIds_4:1860,__featureIds_3:371,__featureIds_2:1161,__featureIds_1:1752,__featureIds_5:5498}, finder:[]}]
    at org.jdbi.v3.core.statement.ArgumentBinder.lambda$bindNamed$0(ArgumentBinder.java:58)
    at java.util.Optional.orElseThrow(Optional.java:290)
    at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:57)
    at org.jdbi.v3.core.statement.ArgumentBinder.bind(ArgumentBinder.java:27)
    at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1443)
    at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:59)
    at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:53)
    at org.jdbi.v3.core.result.ResultIterable.stream(ResultIterable.java:141)
    at org.jdbi.v3.core.result.ResultIterable.collect(ResultIterable.java:197)
    at org.jdbi.v3.core.result.ResultIterable.list(ResultIterable.java:186)

If I use .bind("featureIds", 4138) it works, therefore I assume the naming is correct.

Anyone else got this? Any help is welcome as I'm totally puzzled on this one :(

[johanneszink]

Okay... I stumbled over a solution while trying StringTemplateEngine. When I put the parameter for the list in <> it works.
"SELECT gg.feature_id, gg.latitude, gg.longitude, gg.order, gg.group FROM geo.geo_coordinates as gg WHERE gg.feature_id IN (<featureIds>);"

.bindList("featureIds", values) works.

Maybe add this to the documentation and provide an example for bindList ? :)

[qualidafial]

Yep, bindList actually expands defines <featureIds> as e.g. :__featureIds_0, :__featureIds_1, ...etc and binds the list elements to those substitute variables.

If you are using Postgres, you can bind lists as SQL arrays using where featureIds = any(:featureIds) in the query and using bindByType("featureIds", featureIds, new GenericType<List<Long>>() {}).

[DeaconDesperado]

Not to zombie, but when using bindMap with a Map<String, Object> for whom any single key contains a List, this doesn't seem to work. It appears such parameters need to be bound separately from the map with bindList. Is this accurate?

Example:

  a,b,c
from
  table
where
  a = :some_string
  and b = ANY(<list_of_strings>)

With a binding of:

.bindMap(Map.of(
  'some_string', 'foobar',
  'list_of_strings', Lists.newArrayList('q', 'r', 's')
))

Will trigger:

org.jdbi.v3.core.statement.UnableToCreateStatementException: Undefined attribute for token '<list_of_strings>' 

[stevenschlansker]

That's correct. bindMap only binds arguments, whereas bindList also needs to define attributes so the templating works as expected. They aren't set up to work together, although that might be possible to change in the future.