Collection of enums is not properly mapped to query params

[aalster]

Method addParameter maps enum by it's name. But enums wrapped in a collection are not working properly.

Example:

create table enum_test (id int auto_increment primary key, value varchar(20));
insert into enum_test (value) values ('MONDAY');

| id |  value |
|----|--------|
|  1 | MONDAY |

class Scratch {
    enum Day {
        MONDAY, TUESDAY
    }

    public static void main(String[] args) {
        Sql2o sql2o = // ...
        try (Connection connection = sql2o.open()) {
            connection.createQuery("SELECT * FROM enum_test WHERE value IN (:value)")
                    .addParameter("value", List.of(Day.MONDAY, Day.TUESDAY)) // Here is the problem
                    .executeAndFetchTable()
                    .asList()
                    .forEach(System.out::println); // Prints nothing, db returns 0 rows
        }
    }
}

Debugging shows this query in preparedStatement:

SELECT * FROM enum_test WHERE value IN (** STREAM DATA **,** STREAM DATA **)

Same happens for addParameter("value", Day.MONDAY, Day.TUESDAY), addParameter("value", List.of(Day.MONDAY, Day.TUESDAY).toArray())

Query works properly for addParameter("value", Day.MONDAY):

13:46:31.447 [main] DEBUG org.sql2o.Query - Executing query:
SELECT * FROM enum_test WHERE value IN (?)
13:46:31.491 [main] DEBUG org.sql2o.Query - total: 44 ms, execution: 39 ms, reading and parsing: 5 ms; executed [null]
{id=1, value=MONDAY}

sql2o v1.6.0, java 17

[aaberg]

Hi @aalster, You are correct that this is not supported. Each parameter in an SQL query is mapped to a JDBC parameter, and JDBC doesn't support your use case. So, this is not a problem with Sql2o but rather a limitation on JDBC. Check out this Stackoverflow for an example of how to fix it with JDBC directly. You can do something similar with sql2o. https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause