如何设置一个String []参数到本机查询？(How can I set a String[] p

这是我的PostgreSQL的功能：

salvarArquivoGeometricoCasoZeroPOINT
(dimensao text,tableName text,tuplas text[],srid text)

它有一个text[]参数，我想一个Java传递String[]将它从我的JPQL：

public String salvarGeometriaCaso0(String[] tuplas,FileDto arquivo){
        Query query =
        em().createNativeQuery("select 
salvarArquivoGeometricoCasoZeroPOINT(?1,?2,?3,?4)");
        query.setParameter(1,arquivo.getGeo());//String
        query.setParameter(2,arquivo.getTable());/String
        query.setParameter(3,tuplas);//String[]
        query.setParameter(4,arquivo.getSrid());//String
        return (String) query.getSingleResult();//function returns a Text, so cast to String
}

上面的代码失败，出现异常：

ERROR] Internal Exception: org.postgresql.util.PSQLException: Can not infer a SQL
type to use for an instance of [Ljava.lang.String;. 
Use setObject () with an explicit Types value to specify the type to use.

所以我不知道如何从EclipseLink的调用我的函数。

Answer 1:

Testing by passing a Java array of type String[] to PreparedStatement.setObject(...) results in the behaviour you report. It appears that PgJDBC doesn't accept a Java array as an argument to PreparedStatement.setObject(), with or without a Types.ARRAY parameter.

Compliance

The JDBC spec, 16.5 "Array Objects", suggests that the JDBC Array exists partly so the client doesn't have to copy big arrays into memory, they can be used by reference. I'm not too sure whether the JDBC driver is required to accept raw Java arrays as parameters. All the spec code refers to java.sql.Array and the spec makes it clear that arrays are mapped via the Array interface in Appendix B and elsewhere. In a quick search/reading I could find no mention of passing raw Java arrays other than byte[] as parameters or returning them as results.

However, in §16.5.4 the JDBC4.2 draft spec reads:

A Java array may be passed as an input parameter by calling the method
PreparedSatement.setObject.

though all the rest of the code there refers to Array objects. Do they mean Array by "a Java array"? Or do they mean a raw native Java array like String[]?

It looks to me like clients are supposed to use the java.sql.Array interface via Connection.createArrayOf(...), so EclipseLink is probably doing the wrong thing.

What do do about it

Try updating EclipseLink to 2.4 in the hopes it uses the commonly specified method of passing arrays to JDBC via a java.sql.Array object.

You may also need to annotate the mapping with @Array, an EclipseLink extension. See also this forum thread re 2.3 and bug 361701.

It appears you may have to implement your own type handler for EclipseLink to override its behaviour. To correctly set an array parameter via PgJDBC you must use:

    Array sqlArray = conn.createArrayOf("text", strArray);
    pstmt.setArray(1, sqlArray);
    pstmt.executeUpdate();

... where conn and pstmt are a java.sql.Connection and a PreparedStatement respectively, and strArray is a String[] instance.

See Custom data types in the eclipselink wiki.

On a side note, the use of a string type name to specify the array's data type in createArrayOf seems kind of insane given the existence of java.sql.Types. It makes portability much harder; the above code won't run on (say) Oracle, because Oracle wants VARCHAR not text as a type name.

Note: The unit test org/postgresql/test/jdbc2/ArrayTest.java has ArrayTest.testSetArray(), which at line 166 tests:

    pstmt.setObject(1, arr);
    pstmt.executeUpdate();

... however the type of arr is java.sql.Array, not int[]. It's a JDBC array type, not a regular Java array.

Answer 2:

我很晚回答。

该解决方案是使用PostgreSQL内置的功能，这无疑为我工作一种变通方法。

参考博客

1）转换字符串数组，逗号分隔的字符串

如果您正在使用Java8，这是很容易。其他选项是这里

String commaSeparatedString = String.join(",",stringArray); // Java8 feature

2）PostgreSQL的内置函数string_to_array（）

你可以找到其他PostgreSQL的阵列功能，在这里

// tableName ( name text, string_array_column_name text[] )

String query = "insert into tableName(name,string_array_column_name ) values(?, string_to_array(?,',') )";


int[] types = new int[] { Types.VARCHAR, Types.VARCHAR};

Object[] psParams = new Object[] {"Dhruvil Thaker",commaSeparatedString };

jdbcTemplate.batchUpdate(query, psParams ,types); // assuming you have jdbctemplate instance

Answer 3:

看来的EclipseLink不解决的bug 361701.通过@Craig林格提及。

作为一个参数使用JDBC而不的EclipseLink的唯一的方式来传递的String []。检查代码。

Connection con = ConnectionHelper.getConnection();
        Array tArray = con.createArrayOf("text", tuplas);
        PreparedStatement pstm =
        con.prepareStatement("select salvarArquivoGeometricoCasoZeroPOINT(?,?,?,?)");
        pstm.setString(1,arquivo.getGeoType());
        pstm.setString(2,arquivo.getTable());
        pstm.setArray(3,tArray);
        pstm.setString(4,arquivo.getSrid());
        rs = pstm.executeQuery();

ConnectionHelper这是我的java.sql.Connection类。

我感谢你们的帮助：@Craig林格和@马特球，谢谢。