How do I select where variable in an array in Java? [duplicate]

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
– Rizwan, Commented Aug 12, 2017 at 4:02
As per the docs tutorial on oracle site, it mention the very first line MySQL and javaDb does not support ARRAY data type docs.oracle.com/javase/tutorial/jdbc/basics/array.html — Rizwan
– Rizwan, Commented Aug 12, 2017 at 4:22

Rafa · Accepted Answer · 2017-08-12 05:10:31Z

1

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.

answered Aug 12, 2017 at 5:10

Rafa

2,0273 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HMA Over a year ago

awesome, that worked exactly the way i wanted, thanks, can't upvote dont have enough reputations

Mark Rotteveel Over a year ago

@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.

Rizwan · Accepted Answer · 2017-08-12 04:52:57Z

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, 2017 at 4:52

Rizwan

2,44724 silver badges27 bronze badges

3 Comments

HMA Over a year ago

This method is already implemented, here is a screenshot from my IDE: i.sstatic.net/xTvFk.png

Rizwan Over a year ago

@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

HMA Over a year ago

thanks, you were right, @Rafa answer shares your insight about not using this method.

Collectives™ on Stack Overflow

How do I select where variable in an array in Java? [duplicate]

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related