Convert a Result Set from SQL Array to Array of Strings

Question

I am querying the information_schema.columns table in my PostgreSQL database. Using a table name, the result set finds all the column names, type, and whether it is nullable (except for the primary key, 'id'). This is the query being used:

SELECT column_name, is_nullable,data_type FROM information_schema.columns
WHERE lower(table_name) = lower('TABLE1') AND column_name != 'id'
ORDER BY ordinal_position;

I have a string array for each of these results and I am trying to use the ResultSet method getArray(String columnLabel) to avoid looping through the results. I want to store the returned Arrays in the string arrays, but get a type mismatch error

Type mismatch: cannot convert from Array to String[]

Is there a way to convert or typecast the SQL Array object to a String[]?

Relevant Code:

String[] columnName, type, nullable;

//Get Field Names, Type, & Nullability 
String query = "SELECT column_name, is_nullable,data_type FROM information_schema.columns "
        + "WHERE lower(table_name) = lower('"+tableName+"') AND column_name != 'id' "
        + "ORDER BY ordinal_position";

try{
    ResultSet rs = Query.executeQueryWithRS(c, query);
    columnName = rs.getArray(rs.getArray("column_name"));
    type = rs.getArray("data_type");
    nullable = rs.getArray("is_nullable");
}catch (Exception e) {
    e.printStackTrace();
}

Community · Accepted Answer · 2020-06-20 09:12:55Z

68

Use:

Array a = rs.getArray("is_nullable");
String[] nullable = (String[])a.getArray();

As explained here

Array is SQL type, getArray() returns an object to cast to java array.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 18, 2013 at 11:24

BobTheBuilder

19.4k6 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

Thank you, I had tried using rs.getArray("is_nullable").getArray() but I don't think I had typecast it. This appears to have solved my problem.

Cameron Hudson Over a year ago

For those who have tried this recently, the java.sql.Array.getArray() method now does nothing but throw an SQLFeatureNotImplemented exception, so we'll have to find another approach.

Vincent Over a year ago

This solution won't work for some drivers, see stackoverflow.com/q/55181619/489856

TheWhiteRabbit · Accepted Answer · 2013-02-18 11:40:06Z

4

Generalize the Array to Object

    Object[] type; //this is generic can use String[] directly
    Array rsArray;

    rsArray = rs.getArray("data_type");
    type = (Object [])rsArray.getArray();

Use it loop as string:

type[i].toString();

edited Feb 18, 2013 at 11:40

answered Feb 18, 2013 at 11:22

TheWhiteRabbit

15.8k4 gold badges35 silver badges57 bronze badges

3 Comments

Matt Over a year ago

This still causes an error. "Type mismatch: cannot convert from Array to Object[]". I think the major problem is that the ResultSet getArray method returns a java.sql Array, not the java.util.Arrays type under the java.lang Object class. Here is the javadoc on the method I'm using.

TheWhiteRabbit Over a year ago

@Matt edited and verified, getArray() will return Array, and Array.getArray() returns java array.

Matt Over a year ago

Correct, this would now work, but it requires the unnecessary use of toString(). In a case where unknown or a variety of objects are contained in the ResultSet, generalizing to Object[] may be more useful. In my case, I only need Strings.

yglodt · Accepted Answer · 2017-03-17 08:18:17Z

3

How to set an ArrayList property from an SQL Array:

Array a = rs.getArray("col"); // smallint[] column
if (a != null) {
    yourObject.setListProperty(Arrays.asList((Integer[]) a.getArray()));
}

edited Mar 17, 2017 at 8:18

answered Jul 12, 2016 at 19:28

yglodt

14.6k15 gold badges102 silver badges138 bronze badges

2 Comments

DerMike Over a year ago

What is this o in line 3?

yglodt Over a year ago

o is an instance of an object with a property listProperty of type List<Integer>

Rajeev Ranjan · Accepted Answer · 2018-10-11 14:49:29Z

0

this can be helpful

Object[] balance = (Object[]) ((Array) attributes[29]).getArray();
        for (Object bal : balance) {

            Object [] balObj =(Object[]) ((Array) bal).getArray();
            for(Object obj : balObj){
                Struct s= (Struct)obj;
                if(s != null ){
                    String [] str = (String[]) s.getAttributes();
                    System.out.println(str);
                }

            }

        }

answered Oct 11, 2018 at 14:49

Rajeev Ranjan

2793 gold badges11 silver badges20 bronze badges

1 Comment

BlooB Over a year ago

I up voted your solution, however please add some description to your answer.

F. P. Freely · Accepted Answer · 2024-07-31 17:13:19Z

-1

In Kotlin,

(resultSet.getArray(resultName)!!.array as Array<*>).toList()

answered Jul 31, 2024 at 17:13

F. P. Freely

1,16616 silver badges27 bronze badges

Collectives™ on Stack Overflow

Convert a Result Set from SQL Array to Array of Strings

5 Answers 5

3 Comments

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related