Reputation: 249

Displaying MySQL Query Results from Servlet to JSP

I am trying to store the results of my query in a string, and print them to the bottom of my JSP page by passing that string to it. Right now, the JSP page displays fine initially, but nothing is happening when I click the button to post the command. Earlier when I accessed the servlet from an html page, and printed all my output to out using a PrintWriter, I got the results to display, but they would display on a separate page.

1) Is it a good idea to store out in this way, or should I make it something different than a string?

2) How do I get the results of the query to post to the JSP page?

databaseServlet.java

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

@SuppressWarnings("serial")

public class databaseServlet extends HttpServlet {
    private Connection conn;
    private Statement statement;

    public void init(ServletConfig config) throws ServletException {
        try {
            Class.forName(config.getInitParameter("databaseDriver"));
            conn = DriverManager.getConnection(
                    config.getInitParameter("databaseName"),
                    config.getInitParameter("username"),
                    config.getInitParameter("password"));
            statement = conn.createStatement();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String out = "\n";

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

        if (query.toString().toLowerCase().contains("select")) {
            //SELECT Queries
            try {
                ResultSet resultSet = statement.executeQuery(query.toString());
                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                for(int i = 1; i<= numberOfColumns; i++){
                    out.concat(metaData.getColumnName(i));
                }
                out.concat("\n");

                while (resultSet.next()){
                    for (int i = 1; i <= numberOfColumns; i++){
                        out.concat((String) resultSet.getObject(i));
                    }
                    out.concat("\n");
                 }
            }
            catch (Exception f) {
                f.printStackTrace();
            }
        }
        else if (query.toString().toLowerCase().contains("delete") || query.toLowerCase().contains("insert")) {
            //DELETE and INSERT commands
            try {
                conn.prepareStatement(query.toString()).executeUpdate(query.toString());
                out = "\t\t Database has been updated!";
            }
            catch (Exception l){
                l.printStackTrace();
            }
        }
        else {
            //Not a valid response
            out = "\t\t Not a valid command or query!";
        }
        RequestDispatcher dispatcher = request.getRequestDispatcher("/dbServlet.jsp");
        dispatcher.forward(request,  response);
        request.setAttribute("queryResults", out);
    }
}

dbServlet.jsp

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- dbServlet.html -->
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
    <title>MySQL Servlet</title>
    <style type="text/css">
        body{background-color: green;}
    </style>
</head>
<body>
    <h1>This is the MySQL Servlet</h1>
    <form action = "/database/database" method = "post">
    <p>
        <label>Enter your query and click the button to invoke a MySQL Servlet
            <textarea name = "query" cols="20" rows="5"></textarea>
            <input type = "submit" value = "Run MySQL Servlet" />
            <input type = "reset" value = "Clear Command" />
        </label>
    </p>
    </form>
    <hr>
    <%= 
        request.getAttribute("queryResults");
    %>
</body>
</html>

Upvotes: 1

Answers (3)

Yogendra Singh

Reputation: 34367

1) Is it a good idea to store out in this way, or should I make it something different than a string?

NO. Don't mix the presentation logic in Java code. Leaverage your JSP for that purpose I would advice you to use JAVA objects and store the row wise values in one object instance. Put all the objects in a collection and use the same in JSP for display. Same goes with column names.

2) How do I get the results of the query to post to the JSP page?

In your current format of queryResults, just print the results using = operator or out.println method in your JSP as:

       <hr>
       <%=request.getAttribute("queryResults"); %>

      <% out.println(request.getAttribute("queryResults"));%>

But if you decide t use collection as adviced in answer1, then get the collection back from the request, iterate and print the results, e.g. if you decide to use List<String[]> where String[] maps one row data then:

 <TABLE id="results">
 <% List<String> columns = (List<String>)request.getAttribute("queryColumns");
    List<String[]> results = (List<String[]>)request.getAttribute("queryResults"); 
    out.println("<TR>");
    for(String columnName: columns ){
       out.println("<TD>"+columnName+"</TD>");
    }
    out.println("</TR>");
    //print data
    for(String[] rowData: results){
       out.println("<TR>");
       for(String data: rowData){
          out.println("<TD>"+data+"</TD>");
       }
       out.println("</TR>");
    }
    %>
   </TABLE>

Upvotes: 0

Thilo

Reputation: 262464

1) Is it a good idea to store out in this way, or should I make it something different than a string?

Since this is tabular data, I'd use something that preserves that structure, so that the JSP can piece it apart easily for customized formatting. Bold headers, putting it in an HTML table and stuff. Either some custom bean, or maybe just a List<String[]>.

2) How do I get the results of the query to post to the JSP page?

What you are doing now (request.setAttribute) should work. However, you need to set the attribute before you forward the request.

You could then print the String you now have like this:

<%= request.getAttribute("queryResults") %>

Or if you go with a table-structure

<% List<String[]> rows = request.getAttribute("queryResults"); %>

and then loop over that.

Upvotes: 0

Shamis Shukoor

Reputation: 2515

dispatcher.forward(request,  response);
request.setAttribute("queryResults", out);

It should be like this

request.setAttribute("queryResults", out);
dispatcher.forward(request,  response);

Before the request is dispatched the attributes has to be set

Upvotes: 3

Displaying MySQL Query Results from Servlet to JSP

Answers (3)

Related Questions