在准备语句中传递数组参数 - 获取“java.sql.SQLFeatureNotSupportedException”

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

提问by Mayur

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

我在准备语句中遇到错误 java.sql.SQLFeatureNotSupportedException。我正在使用 Mysql 数据库。

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();
        }
    }
}

采纳答案by Ninad Pingale

For Mysql-

对于Mysql-

Setting array is not possible in Mysql.

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 (?,?)

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

and pass values also using loop.

并使用循环传递值。

For Oracle-

对于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);

回答by Ravinder Reddy

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

错误信息非常清楚。并且MySQL不支持自定义数据类型。

Currently MySQL is supporting only:

目前 MySQL 仅支持：

1. Numeric Type
2. Date and Time Type
3. String Type

1. 数字类型
2. 日期和时间类型
3. 字符串类型

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

或者，您可以将每个输入值用作 中的一组IN函数值MySQL。

Change your JAVAcode as follows:

更改您的JAVA代码如下：

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();

回答by Sathya

Convert List to a comma separated String and use it.

将 List 转换为逗号分隔的 String 并使用它。

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();
            }
        }
    }