How do I select where variable in an array in Java?

Question

I've been trying to select values from MySQL in Java where var IN an array, i've been reading other answers for the past 3 hours, some of them have marked accepted answers, but non of these answers solved the problem for me I tried many examples, but I keep getting the same exception :

Exception in thread "main" java.sql.SQLFeatureNotSupportedException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLFeatureNotSupportedException(SQLError.java:1172)
    at com.mysql.jdbc.JDBC4Connection.createArrayOf(JDBC4Connection.java:52)
    at aero.Connecting.search(Connecting.java:150)

Where Connecting.java:150 = java.sql.Array sqlArray = c.createArrayOf("bigint", data); (same problem with int and VARCHAR too)

Nothing I tried so far is working, some answers like this say that this is not possible to do it the way I want it (one dynamic array) but I can't accept this claim since many other answers say it is possible.

Here is what i've tried so far:

Version 1:

   public String createInListPattern(int n) {
    return StringUtils.repeat("?", ",", n);
}

     Connection c =get_conn();
              PreparedStatement s = c.prepareStatement("SELECT ID FROM users WHERE ID in ("+createInListPattern(2)+")");
          boolean status=false;
          int size=2;
            Integer[] data = new Integer[2];
            data[0]=13;
            data[1]=14;
             java.sql.Array sqlArray = c.createArrayOf("bigint", data);
                s.setArray(1, sqlArray);

        for (int i = 0; i < size; i++) {
            s.setInt(i + 1, data[i]);
        }
            ResultSet rs =s.executeQuery();
            if(rs.next()) { 
                System.out.println(rs.getInt("ID"));
                }

Version 2:

 Connection c =get_conn();
          PreparedStatement s = c.prepareStatement("SELECT ID FROM users WHERE ID in (?)");
      boolean status=false;
        Object[] data = new Object[2];
        data[0]=13;
        data[1]=14;
         java.sql.Array sqlArray = c.createArrayOf("bigint", data);
            s.setArray(1, sqlArray);
        ResultSet rs =s.executeQuery();
        if(rs.next()) { 
            System.out.println(rs.getInt("ID"));
            }

In addition to the above versions, I tried some of the following:

SO question 1
SO question 2 (My JDBC jar version is 5.x)
SO question 3

As i mentioned before, the exception I've been receiving is the same in all attempts so far and seems to be coming from the same line ( c.createArrayOf("bigint", data) ).

Any working solutions?

Screenshot

If you create a data object array as Long array and call method creatArrayOf with passing capital BIGINT for sql type still the issue remains? — Rizwan, Aug 12 '17 at 04:02
As per the docs tutorial on oracle site, it mention the very first line MySQL and javaDb does not support ARRAY data type https://docs.oracle.com/javase/tutorial/jdbc/basics/array.html — Rizwan, Aug 12 '17 at 04:22
thanks, i read it, still there must be away, otherwise why would they take this feature away without an equal alternative — HMA, Aug 12 '17 at 04:30

score 1 · Accepted Answer · answered Aug 12 '17 at 05:10

As others pointed out, this feature is not supported.

It might sound strange at first but databases are very specific in their implementations. You will find many differences in the behaviour of different JDBC drivers.

To accomplish your goal you could assemble and parameterize the Prepared Statement dynamically. Example:

public static void main(String[] args) throws SQLException {
    Connection c = getConnection();

    // Build the SQL
    StringBuilder sql = new StringBuilder("SELECT id, name FROM sof.users WHERE id IN (");
    Integer[] data = new Integer[2];
    data[0]= 2;
    data[1]= 4;
    for (int i = 0; i < data.length; i++) {
        sql.append("?,");
    }
    // Delete the last comma
    sql.delete(sql.length()-1, sql.length());
    sql.append(")");

    // Assign prepared statement parameters
    PreparedStatement s = c.prepareStatement(sql.toString());
    for (int i = 0; i < data.length; i++) {
        s.setInt(i+1, data[i]);
    }

    // Output the results
    ResultSet rs = s.executeQuery();
    while (rs.next()) {
        System.out.println("id: " + rs.getInt("id") + "; name: " + rs.getString("name") );
    }
}

If JPA is an option, then many JPA implementations handle IN clauses for you.

awesome, that worked exactly the way i wanted, thanks, can't upvote dont have enough reputations — HMA, Aug 12 '17 at 05:22
@HMA This is the same solution as the accepted answer in one of questions linked in your question, I have therefor marked it as a duplicate. — Mark Rotteveel, Aug 12 '17 at 09:04

score 0 · Answer 2 · answered Aug 12 '17 at 04:52

0

This feature clearly not being implemented/supported in the MySQL driver. You need to have workaround by not using this API call directly but instead create your own Concating values IN clause

answered Aug 12 '17 at 04:52

Rizwan

2,369
22
27

This method is already implemented, here is a screenshot from my IDE: https://i.stack.imgur.com/xTvFk.png – HMA Aug 12 '17 at 05:01
@HMA I agree with you that the method is listing on the autocomplete because it has the body available in the *JDBC4Connection* ; but I would recommend you to have a close look inside that body..where they clearly throwing the exception you got on your stack trace – Rizwan Aug 12 '17 at 05:09
thanks, you were right, @Rafa answer shares your insight about not using this method. – HMA Aug 12 '17 at 05:23

How do I select where variable in an array in Java?

2 Answers2