casting mysql.Array to String[]

Question

I am using the Java Mysql connector and I have a problem with extracting results:

This is the query and prepared statement that needed to be executed:

String statement1 = " select sentence from sentences ; ";
PreparedStatement preparedstatement = conn.prepareStatement(statement1);
ResultSet res = preparedstatement.executeQuery();

Now I try to get the results in an array, so I use:

Array temp = res.getArray("sentence"); //(1)
String[] temp2 = (String[])temp.getArray();      //(2)

(1) returns a mysql.array which contains a locator, which is a logical pointer to the SQL ARRAY on the server, and (2) should do a typecast of temp and return a String[], however I am getting this error

java.sql.SQLFeatureNotSupportedException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at java.lang.Class.newInstance(Class.java:374)
    at com.mysql.jdbc.SQLError.notImplemented(SQLError.java:1342)
    at com.mysql.jdbc.ResultSetImpl.getArray(ResultSetImpl.java:1226)
    at com.mysql.jdbc.ResultSetImpl.getArray(ResultSetImpl.java:1243)
    at wikiparse.Wikiparse3.running(Wikiparse3.java:62) // this where I wrote (1) in my java file
    at wikiparse.ActivateWikiparse3.main(ActivateWikiparse3.java:27)

Any ideas?

I think after exection of preparedstatement.executeQuery(), the data is already on your side, rather than server side. — Rugal
– Rugal, Commented Jan 14, 2014 at 8:34
@Rugal In the following statement, the ResultSet method getArray returns the value stored in the column ZIPS of the current row as the java.sql.Array object z: Array z = rs.getArray("ZIPS"); The variable z contains a locator, which is a logical pointer to the SQL ARRAY on the server; it does not contain the elements of the ARRAY itself. Being a logical pointer, z can be used to manipulate the array on the server. — kolonel
– kolonel, Commented Jan 14, 2014 at 8:44
This question appears to be off-topic because the documentation the OP links to in the question explains in the first sentence why this will not work with MySQL. — Brian Roach
– Brian Roach, Commented Jan 14, 2014 at 8:47
@kolonel can you see the poster use postgres rather than mysql? — Rugal
– Rugal, Commented Jan 14, 2014 at 8:47

Brian Roach · Accepted Answer · 2014-01-14 09:24:33Z

2

You should iterate througth the ResultSet:

List<String> temp = new ArrayList<String>();
String statement1 = " select sentence from sentences  ";
PreparedStatement preparedstatement = conn.prepareStatement(statement1);
try
{
    ResultSet res = preparedstatement.executeQuery();

    while (res.next())
    {
       temp.add(res.getString("sentence"));
    }
}
finally{
    preparedstatement.close();
}
String[] temp2 = temp.toArray(new String[0]);

edited Jan 14, 2014 at 9:24

Brian Roach

77k12 gold badges140 silver badges164 bronze badges

answered Jan 14, 2014 at 8:35

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

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

5 Comments

ADS Over a year ago

ArrayList doesn't implement or extend Array. Did you compile your code?

Brian Roach Over a year ago

This literally makes no sense.

Oscar Pérez Over a year ago

Excuse-me, but.... why? This code gets all the values from a database table column and stores them in a memory array...

Brian Roach Over a year ago

Array temp = new ArrayList(); (As ADS pointed out) ? Please, explain. (I think this is just a typo)

Brian Roach Over a year ago

I went ahead and fixed it ;)

Sinto · Accepted Answer · 2014-01-14 08:40:50Z

2

MySQL doesn't have an ARRAY type, so this method is not implemented.

See http://forums.mysql.com/read.php?39,160525,160573

answered Jan 14, 2014 at 8:40

Sinto

9305 silver badges10 bronze badges

5 Comments

kolonel Over a year ago

This is from the tutorials: docs.oracle.com/javase/tutorial/jdbc/basics/array.html

Brian Roach Over a year ago

@kolonel Yes, and the very first sentence says : "Note: MySQL and Java DB currently do not support the ARRAY SQL data type" ... not to mention because of that you couldn't possibly have that in your table definition.

kolonel Over a year ago

@BrianRoach but it says this

In the following statement, the ResultSet method getArray returns the value stored in the column ZIPS of the current row as the java.sql.Array object z:  Array z = rs.getArray("ZIPS"); The variable z contains a locator, which is a logical pointer to the SQL ARRAY on the server; it does not contain the elements of the ARRAY itself. Being a logical pointer, z can be used to manipulate the array on the server.

kolonel Over a year ago

@BrianRoach There is also this post http://stackoverflow.com/questions/14935016/convert-a-result-set-from-sql-array-to-array-of-strings

Brian Roach Over a year ago

Apparently you also don't understand that MySQL and PostgreSQL are completely different databases with different feature sets.

Brian Roach · Accepted Answer · 2014-01-14 09:20:08Z

0

There is another tutorial which shows how to retrieve data from ResultSet.

In your case it will be

List<String> result = new LinkedList<>();
while (while (rs.next()) {
    result.add(rs.getString(1));
}

edited Jan 14, 2014 at 9:20

Brian Roach

77k12 gold badges140 silver badges164 bronze badges

answered Jan 14, 2014 at 8:54

ADS

7063 silver badges14 bronze badges

Comments

Brian Roach · Accepted Answer · 2014-01-14 09:48:39Z

0

Based on the extensive comments, you seem to have a fundamental misunderstanding of how a database works, and what the ARRAY type would mean even if MySQL supported it (which it does not).

well I want to iterate over the values in the ResultSet object , I could do that using the getString() method but then I was trying to see if there was a direct way to do it.

When you query a database, it returns a set of rows from your table that match the query. The JDBC represents this as a ResultSet. To get all the results for a given column, yes, you have to iterate through the ResultSet:

List<String> myList = new ArrayList<String>();
while (res.next()) {
    myList.add(res.getString("sentence"));
}

There's no shortcut method to have the ResultSet do this for you and return a List

Edit to add: The ARRAY type in PostgreSQL (and possibly others) is an entirely different beast. It allows you to store an array of data in a single row and retrieve it that way. That's what the getArray() and the associated Array class is for.

edited Jan 14, 2014 at 9:48

answered Jan 14, 2014 at 9:01

Brian Roach

77k12 gold badges140 silver badges164 bronze badges

5 Comments

kolonel Over a year ago

the extensive comments are because I am trying to understand something , I was trying to see if something could be done in a certain way. The code in your answer is what I already have.

Brian Roach Over a year ago

ESP is on the fritz. That little piece of information would have improved your question dramatically; we would have at least immediately understood your confusion. To be clear, you're confusing a datatype (and the methods/classes to access it) available in a database that is not MySQL with a data structure (ResultSet) in Java.

kolonel Over a year ago

You need to add a throws clause in your code because unless you do it wont work. It seems you have a fundamental misunderstanding on how the ResultSet class works.

Brian Roach Over a year ago

Troll harder. That was weak.

kolonel Over a year ago

I'm not a troll, never.

Collectives™ on Stack Overflow

casting mysql.Array to String[]

4 Answers 4

5 Comments

5 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

5 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related