Passing an String array to a Prepared Statement Array (JDBC + SQLServer)

Question

I'm trying to pass an String array to a prepared statement, but it's returning this exception:

java.sql.SQLFeatureNotSupportedException: This operation is not supported.
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.createArrayOf(SQLServerConnection.java:2763)
    at entity.dao.getRecords(Dao.java:168)
    at entity.dao.main(Dao.java:227)

my code is:

public List<Record> getRecords() throws SQLException {
        String sql = "select * from table where clause in (?)";

        PreparedStatement ps = this.connection.prepareStatement(sql);

        List<String> strings = new ArrayList<>();
        strings.add("string1");
        strings.add("string2");
        strings.add("string3");

        Array array = this.connection.createArrayOf("VARCHAR", strings.toArray());

        ps.setArray(1, array);

        ResultSet executeQuery = ps.executeQuery();
        List<Record> records = new ArrayList<Record>();
        Record record;
        while (executeQuery.next()) {
            // ...
        }
        return records;
    }

the line of the exception is Array array = this.connection.createArrayOf("VARCHAR", strings.toArray());

it's when i try to create the Array.

I already searched how to pass the Array and everybody tells to do like this, but seems to don't work with SQLServer.

Use this: `ps.setString(1,"'comma', 'separated', 'values'");` — Jay Smith, Jul 03 '17 at 21:44
this example is only didatic. at the scenario that i'm working, i have a hundred of strings. — Henrique Santiago, Jul 03 '17 at 21:58
*"this post don't talk about sql-server"* - but it does talk about using plain JDBC, which is what you are apparently trying to do. The principle is the same. — Gord Thompson, Jul 03 '17 at 23:17
According to the stack trace, seems like the SQL Server JDBC driver doesn't support the `createArrayOf()` method. — Roberto Linares, Jul 03 '17 at 23:21
The source code for the driver is on GitHub. If you look at the stack trace, this function is, indeed, not implemented and just throws the exception in your question. — , Jul 04 '17 at 00:07
Ya, i know. The featured wasn't implemented. I already know this. I'm only searching some solution, pass an array in a "where in clause" — Henrique Santiago, Jul 04 '17 at 03:30

score 1 · Answer 1 · answered Jul 03 '17 at 22:36

1

you can use, Alternatively, a createQuery statemen (Entity Manager)

String sql= "select * from Table table where table.clause IN :clauses"; 
Query query = em.createQuery(sql, Record.class);

List<String> strings = new ArrayList<>();
        strings.add("string1");
        strings.add("string2");
        strings.add("string3");

query.setParameter("clauses", strings );
List<Record> result= query .getResultList();

answered Jul 03 '17 at 22:36

TOUZENE Mohamed Wassim

682
7
20

1

this is a solution, but i'm having troubles with JPA that i don't know how to resolve. – Henrique Santiago Jul 03 '17 at 22:47
when i insert the annotations, @Entity @Table(name="table"), in my Entity Class, the entityManager don't works anymore. – Henrique Santiago Jul 03 '17 at 22:48
can you print the error – TOUZENE Mohamed Wassim Jul 03 '17 at 22:51
`javax.persistence.PersistenceException: [PersistenceUnit: usuarios] Unable to build Hibernate SessionFactory ........ Caused by: org.hibernate.AnnotationException: No identifier specified for entity: entity.record ...........` – Henrique Santiago Jul 03 '17 at 22:56
you msut specify the id of the entity using: @Id @GeneratedValue(strategy = GenerationType.IDENTITY) – TOUZENE Mohamed Wassim Jul 03 '17 at 23:00
i did this: `@Column(name="id") @ID private long id;`, didn't work too – Henrique Santiago Jul 03 '17 at 23:02
make sure that you are using import javax.persistence.Id; for the @ID importation – TOUZENE Mohamed Wassim Jul 03 '17 at 23:18
Ya, exactly from javax.persistence – Henrique Santiago Jul 04 '17 at 03:31

Passing an String array to a Prepared Statement Array (JDBC + SQLServer)

1 Answers1