How to perform a mySQL query on one JSP page and display the results on another?

Question

I am building a simple web app using Java, JSP, HTML, Apache Tomcat 10, MySQL, Connector J and Jakarta Servlet.

The user sees three buttons. Each button runs a query and redirects to a new page to display results.

Q: How can I display the results of each query on a new page?

Note: I can access the ResultSet object currently and that has allowed me to print the data to the console. But I am stuck trying to figure out how to use this ResultSet information on any page other than the one it is produced on.

I have included below my JSP file for reference. Thanks in advance.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.io.*, java.util.*,java.sql.*"%>
<%@ page import="jakarta.servlet.http.*, jakarta.servlet.*"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Query Page</title>
</head>
<body>
    <form method="post">
        <label for="button_A">Submit to display results of query 1 on new page</label> <input
            type="submit" name="button_A" value="Submit1" />
        <hr />
        <label for="button_B">Submit to display results of query 2 on new page</label> <input
            type="submit" name="button_B" value="Submit2" />
        <hr />
        <label for="button_C">Submit to display results of query 3 on new page</label> <input
            type="submit" name="button_C" value="Submit3" />
    </form>

    <%
    String button_A = request.getParameter("button_A");

    if ("Submit1".equals(button_A)) {
        queryDatabase();
        String redirectURL = "QueryResult1.jsp";
        response.sendRedirect(redirectURL);
    }

    String button_B = request.getParameter("button_B");

    if ("Submit2".equals(button_B)) {
        queryDatabase();
        String redirectURL = "QueryResult2.jsp";
        response.sendRedirect(redirectURL);
    }

    String button_C = request.getParameter("button_C");

    if ("Submit3".equals(button_C)) {
        queryDatabase();
        String redirectURL = "QueryResult3.jsp";
        response.sendRedirect(redirectURL);
    }
    %>

    <%!public void queryDatabase() {
        String connectionURL = "jdbc:mysql://127.0.0.1:3306/?user=coms363";
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(connectionURL, "coms363", "password");

            statement = connection.createStatement();

            ResultSet resultSet = null;
            String sqlQuery = "";
            String outputString = "";
            sqlQuery = "select id, ssn from employees where name = 'John'";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sqlQuery);
            while (resultSet.next()) {
                outputString += resultSet.getInt("id") + "....";
                outputString += resultSet.getInt("ssn") + "\n";
            }

            System.out.println(outputString);

            statement.executeBatch();

        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Error:" + e.getMessage());
            e.printStackTrace();
        }
    }%>

</body>
</html>

Image of submit buttons for visual:

score 1 · Answer 1 · answered Sep 23 '21 at 05:00

Data Access, Service, Controller and View layers should be separated. These layers are organized into packages.

In the Data Layer, you should define methods for the CRUD operations on your data. For example, readAll, readEmployeeById, etc. So all your code related to the database (jdbc, hibernate, ...) will be here.

In the Service, you could implement the business logic. You business logic will call the data layer.

The Controller layer will contains your Servlets. These servlets listen to HttpRequests. The controller is liable for calling the service. The controller do not visualize data but forward it to the view which is your JSP files.

The view is your JSP which only visualize the results.

So on your home page you will see your buttons. When you click on a button then you will call a Servlet. The servlet calls the service which uses the Data Access Object for data managing. The controller will forward the requet to the JSP and add the fetched data to the requet. In your JSP you can get these data and you should only visualize it.

Although I understand that Data Access, Service, Controller and View can be separated, that's not what I asked for in this question. Would be helpful if the above answer included any specific way to pass ResultSet object to another page or retrieve that information from another page. Thanks. — 3n6in33r, Sep 23 '21 at 15:12

How to perform a mySQL query on one JSP page and display the results on another?

1 Answers1