List<T> as value for parameter in IN clause

[n4d13]

Hello,
i'm trying to express a query of type "SELECT * from X where X.id IN(?)" as, for example, in Hibernate and i cant make it work.
My hope is that a simple solution can be implemented, but i dont figure how.
For completion, here is my code:

override fun get(woeidList: List<Int>): List<WeatherInfo> {
        return using(sessionOf(datasource)) { session ->
            session.run(queryOf(GET_BY_LIST, woeidList).map(toWeatherInfo).asList)
        }
    }

and the query is:

private val GET_BY_LIST = """SELECT WOEID, WIND_CHILL, WIND_DIRECTION,
                              WIND_SPEED, ATM_HUMIDITY, ATM_PRESSURE,
                              ATM_RISING, ATM_VISIBILITY, CON_CODE,
                              CON_DATE, CON_TEMP, CON_TEXT
                              FROM MONITORS_HISTORY
                              WHERE WOEID IN(?) """

Any advise?

TY

[seratch]

Thanks. I've not checked Hibernate's solution yet but does the library modify WOEID IN(?)part? I have met the need in my Scala library like this: https://github.com/scalikejdbc/scalikejdbc/blob/92176eb2ff4b64f2e0ca20bb49ab228ca7354f0b/scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala#L88-L94

[pierrelucveilleux]

Nice to have or at least to document it.

use woeidList.joinToString()

override fun get(woeidList: List<Int>): List<WeatherInfo> { return using(sessionOf(datasource)) { session -> session.run(queryOf(GET_BY_LIST, woeidList.joinToString()).map(toWeatherInfo).asList) } }

[ar-tama]

@seratch
Hi,
I'm also stuck in same situation. woeidList.joinToString() will be string, so PreparedStatement.setParam seems to decide it as String.

Of course we have a workaround, "Add placeholders as much as we needed"... but I think we can be more lazy :P Could you tell me your opinions?

Actual generated:

SELECT woeid
FROM monitors_history
WHERE woeid in ('1, 2, 3')

Expected:
like DBIx::Sunny in Perl

SELECT woeid
FROM monitors_history
WHERE woeid in (1, 2, 3) // <- Removed quote

FYI my environment:

HikariCP 3.4.5
kotliquery: 1.3.1
kotlin: 1.3.72
MySQL: 5.6, 8.x

Thanks for your time!