Convert a Result Set from SQL Array to Array of Strings
Solution 1
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.
Solution 2
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();
Solution 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()));
}
Solution 4
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);
}
}
}
Comments
-
Matt over 3 years
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 errorType 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(); }
-
Matt about 11 yearsThis 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. -
Matt about 11 yearsThank 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. -
TheWhiteRabbit about 11 years@Matt edited and verified, getArray() will return Array, and Array.getArray() returns java array.
-
Matt about 11 yearsCorrect, 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. -
DerMike about 7 yearsWhat is this
o
in line 3? -
yglodt about 7 yearso is an instance of an object with a property listProperty of type List<Integer>
-
BlooB over 5 yearsI up voted your solution, however please add some description to your answer.
-
Cameron Hudson about 5 yearsFor those who have tried this recently, the
java.sql.Array.getArray()
method now does nothing but throw anSQLFeatureNotImplemented
exception, so we'll have to find another approach.