Query arrays of data-CodePudding

How can I query data from my database and assign it to an array of strings? In this attempt I noticed I would receive an out of bounds error before I included the resultSet.next() call since it seems that ResultSet starts at 0 and is not called like a list / array (meaning you can access the contents with its index).

public String[][] retrieveNameAndLocation() {
    final String table = "customers";

    try {
        ResultSet resultSet = statement.executeQuery(
                "SELECT "  
                    "first_name,"  
                    "location"  
                " FROM "   table
        );

        resultSet.next();
        final String[] names = (String[]) (resultSet.getArray(1).getArray());
        final String[] location = (String[]) (resultSet.getArray(2)).getArray();
        final String[][] nameAndCountry = {names, location};

        resultSet.close();
        return nameAndCountry;
    } catch (SQLException  e) {
        e.printStackTrace();
    }
    return null;
}

Anyways the above code resulted in a SQLFeatureNotSupportedException. My next attempt was to simply call the the columns by name since I noticed it was an option inside of getArray, however that also resulted in the not supported exception.

public String[][] retrieveNameAndLocation() {
    final String table = "customers";

    try {
        ResultSet resultSet = statement.executeQuery(
                "SELECT "  
                    "first_name,"  
                    "location"  
                " FROM "   table
        );

        resultSet.next();
        final String[] names = (String[]) (resultSet.getArray("first_name").getArray());
        final String[] location = (String[]) (resultSet.getArray("location")).getArray();
        final String[][] nameAndCountry = {names, location};

        resultSet.close();
        return nameAndCountry;
    } catch (SQLException  e) {
        e.printStackTrace();
    }
    return null;
}

I am not really sure why I need to include resultSet.next() because it seems like it's just broken since why would they include an option to query columns if they forced you to loop through the indexes?

CodePudding user response：

I think you misunderstand the purpose of method getArray. Some DBMSs, like Oracle, have "array" data types. Hence the getArray method – to query a database table column whose type is an array type. I have no experience with MySQL but it appears that it does not have an array type. Hence the JDBC driver for MySQL does not need to implement the getArray method and that's why you get the SQLFeatureNotSupportedException.

You need to iterate through the ResultSet and build up your array. However since you usually don't know how many rows there are in a ResultSet, I usually use a List and then, if required, convert it to an array because when you declare an array you need to know its size.

I would also define a record and declare a List of records.
(Note that below code is not compiled and not tested since I don't have your database and I can't simulate it since the code in your question is not a minimal, reproducible example.)

public record NameAndCountry(String name, String location) {
    public static java.util.List<NameAndCountry> retrieveNameAndLocation() {
        final String table = "customers";
        try {
            ResultSet resultSet = statement.executeQuery(
                "SELECT "  
                    "first_name,"  
                    "location"  
                " FROM "   table
            );
            java.util.List<NameAndCountry> list = new java.util.ArrayList<>();
            while (resultSet.next()) {
                String name = resultSet.getString(1);
                String location = resultSet.getString(2);
                NameAndCountry row = new NameAndCountry(name, location);
                list.add(row);
            }
        } catch (SQLException  e) {
            e.printStackTrace();
        }
        return list;
    }
}