JSP for loop with database results

Question

Im using JSP for an application for college. Corresponding with the JSP there is a connection to a database which i must retrieve the information and display it in a drop down list so the user can select one option to proceed with a registration form. I am able to connect to the DB no problem and output the information in the drop down but its only getting the very first row that was entered into the database.

String[] course_codes = dbclass.SelectRow("SELECT DISTINCT Course_code FROM Stream ;");
                %><form action="successtwo.jsp">
                    <label>Select Course code</label> 
                    <select name="stream">
                         <%for(String course_code: course_codes){
                         out.println("<option value="+course_code+">"+course_code+"</option>");
}%>
                   </select><br>

So I have done something like this but I also have done this.

String[] course_codes = dbclass.SelectRow("SELECT DISTINCT Course_code FROM Stream ;");
                %><form action="successtwo.jsp">
                    <label>Select Course code</label> 
                    <select name="stream">
                         <%for(String course_code: course_codes){%>

                       <option value="<%=course_code%>"><%=course_code%></option> 

                           <%}%>
                   </select><br>

In the database there is more than one Course_code and also the same Course_code can appear hence why i use distinct. Here is the dbClass.java where the SelectRow method is.

 public String[] SelectRow(String SQLquery)
{
    String Result[];
    // Send an SQL query to a database and return the *single column* result in an array of strings
    try {// Make connection to database
        statementObject = connectionObject.createStatement();

        ResultSet statementResult = statementObject.executeQuery(SQLquery); //Should connection be left open?

        ResultSetMetaData rsmd = statementResult.getMetaData();
        int nrOfColumns = rsmd.getColumnCount();

        Result = new String[nrOfColumns];

        statementResult.next();

        int currentCounter = 0;

        while (currentCounter<nrOfColumns) // While there are rows to process
        {
            // Get the first cell in the current row
            Result[currentCounter] = statementResult.getString(currentCounter+1);
            currentCounter++;

        }
        // Close the link to the database when finished

    } catch (Exception e) {
        System.err.println("Select problems with SQL " + SQLquery);
        System.err.println("Select problem is " + e.getMessage());
        Result = new String[0]; //Need to setup result array to avoid initialisation error
        writeLogSQL(SQLquery + " caused error " + e.getMessage());
        }
    writeLogSQL(SQLquery + "worked ");
    return Result;
} // End SelectRow

Any ideas?

Answer 1

public String[] SelectColumn(String SQLquery)
{
    String Result[];
    // Send an SQL query to a database and return the *single column* result in an array of strings
    try {// Make connection to database
        statementObject = connectionObject.createStatement(); //Should connection be left open?

        ResultSet statementResult = statementObject.executeQuery(SQLquery);

        // Start solution from http://www.coderanch.com/t/303346/JDBC/java/find-number-rows-resultset
        int rowcount = 0;
        if (statementResult.last()) {
            rowcount = statementResult.getRow();
            statementResult.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
            }
        // End solution from http://www.coderanch.com/t/303346/JDBC/java/find-number-rows-resultset

        Result = new String[rowcount];

        int currentCounter = 0;

        while (statementResult.next()) // While there are rows to process
        {
            // Get the first cell in the current row
            Result[currentCounter] = statementResult.getString(1);
            currentCounter++;

        }
        // Close the link to the database when finished
    } catch (Exception e) {
        System.err.println("Select problems with SQL " + SQLquery);
        System.err.println("Select problem is " + e.getMessage());
        Result = new String[0]; //Need to setup result array to avoid initialisation error
        writeLogSQL(SQLquery + " caused error " + e.getMessage());
        }
    writeLogSQL(SQLquery + "worked ");
    return Result;
} // End Select

This worked...

Answer 2

Use resultSet.next() to move resultSet to the next Row.

To Loop through the resultSet you could use something like this

while (rs.next()) {
    Result[currentCounter] = statementResult.getString("columnName");
}

Here is a sample http://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html