0

I have a table where I can select a single entry like this:

SELECT * FROM `table` WHERE `id` = 1;

I know that I could easily select entries by multiple ids like this:

SELECT * FROM `table` WHERE `id` IN (1,2,3);

But now I have implemented this in Java with MariaDB/J Connector, using java.sql classes:

public ArrayDeque<DataType> getItems(long... ids) {
    ArrayDeque<DataType> deque = new ArrayDeque<DataType>();
    PreparedStatement st = null;
    ResultSet resultSet = null;
    try {
        st = getConnection().prepareStatement("SELECT * FROM `table` WHERE `id` = ?;");
        st.setLong(1, id);
        resultSet = st.executeQuery();
        while (resultSet.next()) {
            deque.add(parseResultSetToDataType(resultSet));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close(resultSet, st);
    }
    return deque;
}

Now, basically I need to put a ? for every variable I want to insert. Since the amount of selected Ids can vary, I can't put a static IN (?,?,?) there. The dirty solution that comes to my mind is generate (?,?,?) based on the size of long... ids -- but is there a better, 'cleaner' solution to this?

user2015253
  • 1,263
  • 4
  • 14
  • 25
  • Other than using a library on top of JDBC that does that for you, no. – JB Nizet Apr 16 '17 at 14:32
  • PreparedStatement has a `setArray` method that can be used for this purpose with some databases. Unfortunately, several database drivers, including MySql's, don't support it. – GreyBeardedGeek Apr 16 '17 at 14:49
  • See [this](http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause), it might help as few options explained there. – Sabir Khan Apr 16 '17 at 14:51

1 Answers1

-1
    PreparedStatement statement = connection.prepareStatement("Select * from table where id in (?)");
    Array array = statement.getConnection().createArrayOf("int", new Object[]{1, 2,3});
    statement.setArray(1, array);
Khalil M
  • 1,788
  • 2
  • 22
  • 36