How to get the value of a column using sql.Array in Java?

Question

I wrote a code which execute the query, stores the result set. I am storing the result set column-wise in different Arrays, using getArray(columnLabel) method.

The code is here :

Array imported_by = null;
Array imported_by_ad = null;
Array active_directory_identity_value = null;
while (rs.next()) {
        imported_by = rs.getArray(column_id);
        imported_by_ad = rs.getArray(column_id + 1);
        active_directory_identity_value = rs.getArray(column_id + 2);
        row_count++;
        }

Now I would like to print the result set index-by-index that is what we usually do while printing List:

for(int i=0;i<=row_count;i++){
          System.out.println(imported_by.get(i) + " " + imported_by_ad.get(i) + " " 
                             + active_directory_identity_value.get(i));

get(i) method cannot be used for object type Array, but is there any other method to store the resultset in some list and access/read it as much and whenever I can?

Let me know :)

Thanks.

Update: I also type cast the variable but unfortunately it is throwing exception now: The updated code is :

try {
        int row_count = 0;
        ArrayList<String> imported_by = null;
        ArrayList<String> imported_by_ad = null;
        ArrayList<String> active_directory_identity_value = null;
        while (rs.next()) {
            imported_by = (ArrayList<String>) rs.getArray(column_id);
            imported_by_ad = (ArrayList<String>) rs.getArray(column_id + 1);
            active_directory_identity_value = (ArrayList<String>) rs.getArray(column_id + 2);
            row_count++;
        }

}

and the exception is

Exception in thread "main" java.lang.UnsupportedOperationException at sun.jdbc.odbc.JdbcOdbcResultSet.getArray(JdbcOdbcResultSet.java:4395)

Are you just trying to pull each value out of your imported_by and imported_by_add arrays? You don't access elements in an array by a get method (that's for an arraylist). You access them by specifying the element you want - imported_by[i]. — Andrew, Nov 08 '13 at 19:29
I think you misunderstood the question, I know that we can use getInt(ColumnIndex) or ColumnLabel here, what I want to have the copy of resultset as much accessible in any direction as I can, one way I thought to store it to the array and then retrieve the results. — user1582498, Nov 08 '13 at 19:33
What exactly are you trying to do? This might be helpful for you: [http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html] — Andrew, Nov 08 '13 at 19:33
Oh okay, yeah I got confused with sql.Array, let me take a look at the link :) Thanks man :) — user1582498, Nov 08 '13 at 19:35
You can convert the SQL array to a standard array of the appropriate data type. FOr example, if imported_by is a string: `String[] importedBy = (String[])imported_by.getArray();` — Andrew, Nov 08 '13 at 19:39

score 1 · Answer 1 · answered Nov 08 '13 at 19:46

ResultSet.getArray(int columnIndex) : Retrieves the value of the designated column in the current row of this ResultSet object as an java.sql.Array.

Array data = result.getArray(1);

to retrieve the contents of the SQL ARRAY value designated by this Array object in the form of an array in the Java programming language, you will need to call data.getArray(). For example, assuming that data is an java.sql.Array of String type value:

String strData[] = (String[]) data.getArray();

Now you can access the array as a regular java array with index.

Please read: Using Array Objects

@user1582498, as you are new welcome to stack overflow. However, the way this site works is that, you should accept the answer by clicking the tick mark that satisfies your question the most. The answer is your choice. Check out the [about page](http://stackoverflow.com/about) for details. — Sage, Nov 11 '13 at 07:41

score 0 · Answer 2 · answered Nov 08 '13 at 19:27

0

You should check out the Rowset API. It allows you to keep ResultSets as offline copies or even make modifications through them back into the database.

answered Nov 08 '13 at 19:27

Kayaman

72,141
5
83
121

@user1582498, the `RowSet API` reference won't give you much information, i am afraid – Sage Nov 08 '13 at 19:48
@Sage True, the javadoc is less than helpful, but the tutorial explains things pretty well. – Kayaman Nov 09 '13 at 07:53

score 0 · Accepted Answer · answered Nov 09 '13 at 10:43

I have been able to store the result set using Lucene Document(org.apache.lucene.document.Document), storing the fields to the document.

This might not be the perfect solution but it does work perfectly :) The sample code is as below :

ResultSet rs = st.executeQuery(query);
        rs = st.executeQuery(query);
        StandardAnalyzer analyzer = new StandardAnalyzer(Version.LUCENE_CURRENT);
        Directory index = new RAMDirectory();
        IndexWriterConfig config = new IndexWriterConfig(Version.LUCENE_CURRENT, analyzer);
        org.apache.lucene.document.Document doc = new org.apache.lucene.document.Document();
        try {
            while (rs.next()) {
                String imported_by = rs.getString(1);
                Field field = new Field("imported_by", imported_by , Field.Store.YES,          Field.Index.NOT_ANALYZED);
                doc.add(field);
                IndexWriter writer = new IndexWriter(index, config);
                writer.addDocument(doc);
                writer.close();
            }
            printDocument(doc);
        } catch (IOException ex) {
            Logger.getLogger(CIMTPFS_Roche.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(CIMTPFS_Roche.class.getName()).log(Level.SEVERE, null, ex);
        }

}

That will just write the resultset to the Document and index it, later we can use the following function to get the string values from the Document :)

public void printDocument(org.apache.lucene.document.Document doc) {
    List<IndexableField> fields = doc.getFields();
    for (int i = 0; i < fields.size(); i++) {
        System.out.println(fields.get(i).stringValue());
    }

}

Hope it helps :)

score 0 · Answer 4 · answered Nov 12 '13 at 08:20

Yups, this will help by using Lucene Doc and indexing it:

public void printDocument(org.apache.lucene.document.Document doc) {
List<IndexableField> fields = doc.getFields();
for (int i = 0; i < fields.size(); i++) {
    System.out.println(fields.get(i).stringValue());
}

}

How to get the value of a column using sql.Array in Java?

4 Answers4