How to fill WHERE IN parameter in PreparedStatement

Question

That is my query.

String SELECT_USERS_FROM_GROUPS = "select * from user where group_id in ?";

I need to select users from groups that come in a list:

For example, the list could be.

long[] groupIdList = { 1, 2 };

Here is the my code:

public List<User> getUsersFromGroups(long[] groupIdList) {

        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        List<User> userList = null;
        User user;

        try {

            connection = Connector.getConnection();
            statement = connection.prepareStatement(SELECT_USERS_FROM_GROUPS);

            Array groupIdArray = connection.createArrayOf("LONG", groupIdList);
            statement.setArray(1, groupIdArray);

            rs = statement.executeQuery();

            userList = new ArrayList<User>();
            while (rs.next()) {
                user = new User();    
                user = fillUser(rs);    
                userList.add(user);
            }

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            ResourcesUtil.release(rs, statement, connection);
        }

        return userList;

    }

But I get an exception trying line: Array groupIdArray = connection.createArrayOf("LONG", groupIdList);

Can somebody help me to correct what is wrong, or guide to another possible solution. Thank you;

-- EDIT

The exception:

ERROR UserDao:138 - 
java.sql.SQLFeatureNotSupportedException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at java.lang.Class.newInstance(Unknown Source)
    at com.mysql.jdbc.SQLError.notImplemented(SQLError.java:1350)
    at com.mysql.jdbc.JDBC4Connection.createArrayOf(JDBC4Connection.java:55)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.createArrayOf(NewProxyConnection.java:589)
    at com.thehuxley.data.dao.UserDao.getUsersFromGroups(UserDao.java:120)

This exception says this feature is not supported by database or driver. — Ivan Ivanov, Sep 29 '14 at 17:48
Which database/driver are you using? Most databases I'm familiar with support type "Long" in some form. The first argument to createArrayOf is an SQL type name that the database recognizes so you may just need to change the first argument you are passing. — strwils, Sep 29 '14 at 18:02
which dbm are you using? mysql doesn't support arrays, as per http://stackoverflow.com/questions/17842211/how-to-use-an-arraylist-as-a-prepared-statement-parameter see the second answer for a solution. — ths, Sep 29 '14 at 18:10
I tried to change LONG to BIGINT. It also didn't work. @strwils — MariaH, Sep 29 '14 at 18:21
BIGINT is the correct type to use so if it did not work, it may be that the driver does not support the method. I could not find any info on that method in the docs for the driver. — strwils, Sep 29 '14 at 19:17

score 1 · Answer 1 · edited May 23 '17 at 11:33

JDBC prepared statements only support IN clauses with a known number of arguments, each value must be represented in the original query:

select * from user where group_id in (?, ?)

Parameters are set just like any other parameters using statement.setXXX methods. If you need a variable number of parameters, you must generate the query string dynamically (providing the correct number of ? in the IN clause).

See also: PreparedStatement IN clause alternatives?

score 0 · Answer 2 · edited Sep 29 '14 at 17:53

0

The SQLFeatureNotSupportedException is thrown when:

The JDBC driver does not support this data type

So looks like your database does not support the "LONG" datatype.

edited Sep 29 '14 at 17:53

Kick Buttowski

6,709
13
37
58

answered Sep 29 '14 at 17:50

proulxs

498
2
13

How to fill WHERE IN parameter in PreparedStatement

2 Answers2