Passing array parameter in prepare statement - getting "java.sql.SQLFeatureNotSupportedException"

Question

I am facing error java.sql.SQLFeatureNotSupportedException in my prepare statement. I am using Mysql database.

Below is my code.

class tmp {
public static void main(String arg[]) {

    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost/sample", "root", "root");
        PreparedStatement pst = conn
                .prepareStatement("select * from userinfo where firstname in(?)");

        String[] Parameter = { "user1", "Administrator" };
        Array sqlArray = conn.createArrayOf("VARCHAR", Parameter);
        pst.setArray(1, sqlArray);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    } catch (Exception e) {
        e.printStackTrace();
        }
    }
}

deHaar · Accepted Answer · 2018-11-15 15:23:27Z

9

For Mysql -

Setting array is not possible in Mysql.

Instead of that you can form a query for (?,?,..) in the loop and same way for setting values.

String[] Parameter = { "user1", "Administrator" };
String query = "select * from userinfo where firstname in (";
String temp = "";

for(i = 0; i < Parameter.length; i++) {
  temp += ",?";
}

temp = temp.replaceFirst(",", "");
temp += ")";
query = query + temp;

PreparedStatement pst = conn.prepareStatement(query);

so query becomes

select * from userinfo where firstname in (?,?)

and pass values also using loop.

For Oracle -

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
String[] Parameter = { "user1", "Administrator" };
java.sql.Array sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);
.
.
pstmt.setArray(1, sqlArray);

edited Nov 15, 2018 at 15:23

deHaar

18.7k11 gold badges48 silver badges57 bronze badges

answered Jul 2, 2014 at 10:10

Ninad Pingale

7,1095 gold badges36 silver badges58 bronze badges

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

5 Comments

Ravinder Reddy Over a year ago

Your answer is for MySQL or Oracle?

Mayur Over a year ago

It gives me the error "java.lang.ClassCastException: com.mysql.jdbc.JDBC4Connection cannot be cast to oracle.jdbc.OracleConnection"

Ninad Pingale Over a year ago

Sorry that was for oracle.

Mayur Over a year ago

how can i do it with Mysql

Ninad Pingale Over a year ago

I have edited my answer..create (?,?...) programatically

fujy · Accepted Answer · 2017-02-09 10:55:39Z

4

Error message is very clear. And MySQL does not support custom data types.

Currently MySQL is supporting only:

Or, you can use each of the input values as a set of values of IN function in MySQL.

Change your JAVA code as follows:

StringBuilder sbSql = new StringBuilder( 1024 );
sbSql.append( "select * from userinfo where firstname in(" );

for( int i=0; i < Parameter.length; i++ ) {
  if( i > 0 ) sbSql.append( "," );
  sbSql.append( " ?" );
} // for
sbSql.append( " )" );
PreparedStatement pst = conn.prepareStatement( sbSql.toString() );

for( int i=0; i < Parameter.length; i++ ) {
  pst.setString( i+1, Parameter[ i ] );
} // for

ResultSet rs = pst.executeQuery();

edited Feb 9, 2017 at 10:55

fujy

5,3015 gold badges33 silver badges51 bronze badges

answered Jul 2, 2014 at 10:19

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

5 Comments

Ravinder Reddy Over a year ago

How are you storing values in firstname column. What is its data type?

Mayur Over a year ago

The datatype is string

Mayur Over a year ago

by taking the multiple value or single parameter

Ravinder Reddy Over a year ago

You mean to say that user1 and Administrator can go as two different records?

Ravinder Reddy Over a year ago

Ok. You have an answer edited corrected. You can follow that.

Sathya · Accepted Answer · 2020-01-31 19:35:08Z

-1

Convert List to a comma separated String and use it.

Class Tmp {
        public static void main(String arg[]) {

            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost/sample", "root", "root");

                // Consider this list is already constructed
                List<String> parameter = new ArrayList<String>();
                parameter.add("user1");
                parameter.add("Administrator");

                String parameterStr = "'" + String.join("','", parameter) + "'";
                PreparedStatement pst = conn.prepareStatement("select * from userinfo where firstname in(" + parameterStr + ")");

                ResultSet rs = pst.executeQuery();
                while (rs.next()) {
                    System.out.println(rs.getInt(1));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

edited Jan 31, 2020 at 19:35

answered Jan 31, 2020 at 18:02

Sathya

2331 gold badge5 silver badges13 bronze badges

3 Comments

Alexey Osetsky Over a year ago

Will it be sql injection?

Vlad Ganshin Over a year ago

yes, it leads to sql injection.

Bagus Tesa Over a year ago

this leads to sql injection which defeats the purpose of the prepared statement which op needed. i have no idea how this answer passes the review.

Collectives™ on Stack Overflow

Passing array parameter in prepare statement - getting "java.sql.SQLFeatureNotSupportedException"

3 Answers 3

5 Comments

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related