Connection.createArrayOf throws SQLFeatureNotSupportedException

user_7605967 0
Viewed 63

I want to use Connection.createArrayOf method to create a java.sql.Array to use in a prepared statement, but all of the following implementations throw Feature Not Supported Exception

Is there no implementation of this method? Is it deprecated? Is there any other way to initialize java.sql.Array? Or have I imported the wrong package?

com.mysql.cj.jdbc.ConnectionImpl:

  public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
    try {
      throw SQLError.createSQLFeatureNotSupportedException();
    } catch (CJException var4) {
      throw SQLExceptionsMapping.translateException(var4, this.getExceptionInterceptor());
    }
  }

com.mysql.cj.jdbc.ConnectionWrapper:

  public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
    try {
      this.checkClosed();

      try {
        return this.mc.createArrayOf(typeName, elements);
      } catch (SQLException var5) {
        this.checkAndFireConnectionError(var5);
        return null;
      }
    } catch (CJException var6) {
      throw SQLExceptionsMapping.translateException(var6, super.exceptionInterceptor);
    }
  }

com.zaxxer.hikari.pool.HikariProxyConnection:

  public Array createArrayOf(String var1, Object[] var2) throws SQLException {
    try {
      return super.delegate.createArrayOf(var1, var2);
    } catch (SQLException var4) {
      throw this.checkException(var4);
    }
  }

com.mysql.cj.jdbc.ha.MultiHostMySQLConnection:

  public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
    try {
      return this.getActiveMySQLConnection().createArrayOf(typeName, elements);
    } catch (CJException var4) {
      throw SQLExceptionsMapping.translateException(var4, this.getExceptionInterceptor());
    }
  }
jdbc java
2 Answers

I had the same issue for a while but then, I chose to use NamedParameterJdbcTemplate. You can use MapSqlParameterSource to directly map Collections in your IN query.

  public List<SomeClass> getSomeRecords(List<Integer> someIds, Integer someId) {
    String sql =
        "SELECT * FORM some_table st WHERE st.a IN (:someIds) AND st.b = :someId;";

    Map<String, Object> paramsMap = new HashMap<>();
    paramsMap.put("someIds", someIds);
    paramsMap.put("someId", someId);

    SqlParameterSource parameters = new MapSqlParameterSource(paramsMap);
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    RowMapper<SomeClass> rowMapper = new NestedRowMapper<>(SomeClass.class);
    return jdbcTemplate.query(sql, parameters, rowMapper);
  }
user_183713160

MySQL does not support ARRAY data types. However, as you've tagged spring-data-jpa in this question, you can write your query methods to take a List and let Spring handle it.

e.g.

List<SomeClass> findBySomeFieldIn(List<Integer> ids);

Or, here's a simple prepared statement built with a list of Integer in an IN statement.

List<Integer> ints = new ArrayList<>();

String sql = String.format("SELECT * FROM some_table WHERE some_field IN (%s)",
    ints.stream()
    .map(String::valueOf)
    .collect(Collectors.joining(", ")));

PreparedStatement ps = connection.prepareStatement(sql);

If your list had 1,2,3,4 in it, this would generate SQL that reads

SELECT * FROM some_table WHERE some_field IN (1, 2, 3, 4)
user_8885800
Related