java - JSP for 循环与数据库结果

Question

我正在使用 JSP 来申请大学。与 JSP 相对应的是，有一个到数据库的连接，我必须检索该信息并将其显示在下拉列表中，以便用户可以选择一个选项来继续填写注册表单。我能够毫无问题地连接到数据库并在下拉列表中输出信息，但它只能获取输入数据库的第一行。

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>

所以我做了类似的事情，但我也做了这个。

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>

在数据库中，有多个 Course_code，并且可能会出现相同的 Course_code，这就是为什么我使用不同的。这是 SelectRow 方法所在的 dbClass.java。

 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

有什么想法吗？

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

这有效...