MySQL command SELECT values in column - JDBC

Question

I'm using MySQL commands via JDBC (Java) to make changes to my database. I have implemented the following method to return the values of a column. The goal is to have the location in the column (row) correspond with their location in the array (index). This works with String columns, but with numerical columns, the ResultSet seems to place them in ascending order, thus making their positioning in the returned String array not reflect their positioning in the column. 'rs' is a ResultSet reference variable.

public String[] getColumnContents(String tableName, String columnName) {
    String sql = "SELECT " + columnName + " FROM " + tableName;
    String[] results = new String[SQLManager.getColumnLength(tableName, columnName)];

    try {
       rs = statement.executeQuery(sql);

       for (int counter = 0; rs.next(); counter++) {
            results[counter] = rs.getString(columnName);
       }

    } catch (SQLException e) {
       e.printStackTrace();
    }

    return results;

}

when you are dealing with numerical columns why are using rs.getString ? — Shamis Shukoor
– Shamis Shukoor, Commented Nov 25, 2012 at 4:44
Because this method is used to return the values of columns of multiple data types. — Joel Christophel
– Joel Christophel, Commented Nov 25, 2012 at 4:49
What exactly does getColumnLength() do? The counter in your loop counts the rows returned by the query. You cannot know how many rows the ResultSet will return, so I doubt getColumnLengt() really calculates the number ofr rows. You should use a dynamically adjusting List instead of a fixed sized array when dealing with result sets from the database. — user330315
– user330315, Commented Nov 25, 2012 at 8:56

Joel Christophel · Accepted Answer · 2012-11-26 04:16:44Z

1

It's as simple as adding an ORDER BY clause to the SQL command. Here's my working method:

public String[] getColumnContents(String tableName, String columnName) {
String sql = "SELECT " + columnName + " FROM " + tableName + " ORDER BY " + columnName1 + " ASC, " + columnName2 + " ASC";

String[] results = new String[SQLManager.getColumnLength(tableName, columnName)];

try {
    rs = statement.executeQuery(sql);

    for (int counter = 0; rs.next(); counter++) {
        results[counter] = rs.getString(columnName);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

return results;

}

answered Nov 26, 2012 at 4:16

Joel Christophel

2,6634 gold badges35 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

MySQL command SELECT values in column - JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related