finding a row in jsp with a preparedstatement in SQL

Question

Looking for help trying to find a given row in a preparedstatement in SQL. I have the SQL working. Giving me all the info I need but when I try to call it in JSP it only gives me the first row.

How can I call the next row?

I Call the info of the first row like this:

<%=mails.getString("Col 1")%>
<%=mails.getString("Col 2")%>
<%=mails.getString("Col 3")%>
 ....
<%=mails.getString("Col n")%>

=====================

extra info if anyone needs it:

mails is the ResultSet of

SELECT * FROM raw_purp_rec WHERE batchno=?;

output:

           | Col 1 | Col 2 | Col 3|....| Col n |
    Row1       1        A       B     .     S
    Row2       2        Z       Z     .     Q
    Row3       3        E       M     .     L
                        ....
    Row7       7        W       E     .     X

I want to get the info from more than the first row without ending the ResultSet.

Connection con = null;
PreparedStatement sendLogin = null;
ResultSet resultTest = null;
protected class Mail{
        public Mail(){
            try{
                con = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            sendLogin = con.prepareStatement(
            "SELECT * "
            + "FROM raw_purp_rec "
            + "where raw_purp_rec.batchno= ?;");

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


        public ResultSet getMail(String thing){
            try{
                sendLogin.setString(1, thing);

                resultTest = sendLogin.executeQuery();
            }catch(Exception e){
                e.printStackTrace();
            }
            return resultTest;
        }

}

using a constructor of:

String packet = request.getParameter("name");
Mail mail = new Mail();
ResultSet mails = mail.getMail(packet);

Do you know what a `ResultSet` is? I think you should 1/ read https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html 2/ stop using a `ResultSet` in some JSP 3/ profits — , Aug 30 '17 at 17:18
see also https://stackoverflow.com/questions/3177733/how-to-avoid-java-code-in-jsp-files?rq=1 — , Aug 30 '17 at 17:22

score 0 · Answer 1 · answered Aug 30 '17 at 17:19

I think it is best to use an intermediate POJO where you will read in the resultset and then visualize it instead of trying to directly access the resultset. If you MUST access the resultset directly from the JSP you need to use Scriptlets which is a terrible idea.

score 0 · Answer 2 · answered Aug 30 '17 at 17:44

You need to Iterate through the rows Using result Set Interface Object

Example :-

 while (resultSet.next()) {
                System.out.println("Printing result...");

Below is the sample Code whihc you can use for the reference purpose.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetExample {

    public static void main(String[] args) {
        // The credentials that we need to have available for the connection to the database.
        String username = "myusername";
        String password = "mypassword";
        String databaseName = "albums";

        Connection connect = null;
        Statement statement = null;

        try {
            // Load the MySQL driver.
            Class.forName("com.mysql.jdbc.Driver");

            // Setup the connection to the database.
            // Take notice at the different variables that are needed here:
            //      1. The name of the database and its location (currently localhost)
            //      2. A valid username/password for the connection.
            connect = DriverManager.getConnection("jdbc:mysql://localhost/"
                    + databaseName + "?"
                    + "user=" + username
                    + "&password=" + password);

            // Create the statement to be used to get the results.
            statement = connect.createStatement();

            // Create a query to use.
            String query = "SELECT * FROM table ORDER BY year";

            // Execute the query and get the result set, which contains
            // all the results returned from the database.
            ResultSet resultSet = statement.executeQuery(query);

            // We loop through the rows that were returned, and we can access the information
          use the appropriate methods.
            while (resultSet.next()) {
                System.out.println("Printing result...");

                // Now we can fetch the data by column name, save and use them!
                String albumName = resultSet.getString("name");
                String artist = resultSet.getString("artist");
                int year = resultSet.getInt("year");

                System.out.println("\tAlbum: " + albumName + 
                        ", by Artist: " + artist + 
                        ", released in: " + year);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // We have to close the connection and release the resources used.
            // Closing the statement results in closing the resultSet as well.
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

finding a row in jsp with a preparedstatement in SQL

2 Answers2