JDBC : get the type of an array from the metadata

Question

Is it possible to get the type of an array column from the JDBC metadata structure ?

re = openedConnection.getMetaData().getColumns(...)
while (rs.next())
{
  final String columnName = rs.getString("COLUMN_NAME");
  final int columnType = rs.getInt("DATA_TYPE");
  ...

type returns ARRAY but an array of what (int, text, numeric...) ?

Ideally database independent (Oracle and Postgres support this not sure about MySQL or SQL Server)

`rs.getString("TYPE_NAME")` http://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String- — , Mar 21 '17 at 14:29
Or even directly from a ResultSet (e.g. resultSet.getMetaData().getColumnType(1). (gets the type for the first column) — dsp_user, Mar 21 '17 at 14:47
@dsp_user you mean `.getColumnTypeName(1)`? `getColumnType(1)` returns the java type (which is only `ARRAY`). — pozs, Mar 21 '17 at 14:49
No, as I've written. The code is resultSet.executeQuery(); resultSet.getMetaData().getColumnType(1). (to get the type for the first column). You can even use a select that returns an empty ResultSet (the metadata will still be present) — dsp_user, Mar 21 '17 at 14:55
Sorry, I didn't understand what you asked (but now I do understand).Please, ignore my comment. — dsp_user, Mar 21 '17 at 14:58
For PostgreSQL you can use `select udt_name from information_schema.columns where table_name = ? AND column_name = ?`. Maybe Oracle's information_schema reveals it as well. — coladict, Mar 21 '17 at 16:14
Turns out Oracle uses this instead of information_schema http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm — coladict, Mar 21 '17 at 16:53
rs.getString("TYPE_NAME") looks as a nice solution for Postgres — ic3, Mar 21 '17 at 16:55
`rs.getString("TYPE_NAME")` will work for essentially every DBMS with a decent JDBC driver — , Mar 21 '17 at 17:32
@horse_with_no_name, is there an easy way (besides trying) to know what TYPE_NAME returns for all different array types in Oracle and Postgres ? — ic3, Mar 22 '17 at 09:02

MT0 · Answer 1 · 2017-03-21T16:05:47.987

Use java.sql.ResultSetMetaData.getColumnTypeName():

Oracle Setup:

CREATE OR REPLACE TYPE random_array IS TABLE OF VARCHAR2(42);
/

Java:

Class.forName("oracle.jdbc.OracleDriver");

Connection con = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:orcl",
    "username",
    "password"
);

PreparedStatement st = con.prepareStatement(
    "SELECT random_array( 'a', 'b', 'c' ) AS array FROM DUAL"
);

ResultSet rs = st.executeQuery();
while( rs.next() )
{
  String type = rs.getMetaData().getColumnTypeName(1);
  String[] data = (String[]) rs.getArray(1).getArray();
  System.out.println( type + ": " + Arrays.toString( data ) );
}

st.close();
con.close();

Output:

SCHEMA_NAME.RANDOM_ARRAY: [a, b, c]

If you then want to go back to Oracle and find the type of the collection elements you can use:

SELECT ELEM_TYPE_NAME,
       LENGTH,
       PRECISION,
       SCALE
FROM   ALL_COLL_TYPES
WHERE  TYPE_NAME = 'RANDOM_ARRAY'
AND    OWNER     = 'SCHEMA_NAME';

Which outputs:

ELEM_TYPE_NAME LENGTH PRECISION SCALE
-------------- ------ --------- -----
VARCHAR2           42

But ic3 has no query on which he/she could get the `ResultSetMetadata` from — , Mar 21 '17 at 17:32

JDBC : get the type of an array from the metadata

1 Answers1

Linked