Home>

Using Servlet/JSP to display customer information extracted from database (MySQL) with table tag

Error message
Type Exception Report
Message An exception occurred processing [/WEB-INF/views/list.jsp] at line [23]
The server encountered an unexpected condition that prevented it from fulfilling the request.
exception
org.apache.jasper.JasperException: An exception occurred processing [/WEB-INF/views/list.jsp] at line [23]
20:</tr>
twenty one:             
22:<%
23: for (HashMap<String, String>columns: rows) {
24:%>
25:<tr>
26:<td><% = columns.get ("name")%></td>

Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handleJspException (JspServletWrapper.java:593)
    org.apache.jasper.servlet.JspServletWrapper.service (JspServletWrapper.java:482)
    org.apache.jasper.servlet.JspServlet.serviceJspFile (JspServlet.java:386)
    org.apache.jasper.servlet.JspServlet.service (JspServlet.java:330)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter (WsFilter.java:53)
    ListOutput.doGet (ListOutput.java:54)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:634)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter (WsFilter.java:53)
Cause
java.lang.NullPointerException
    org.apache.jsp.WEB_002dINF.views.list_jsp._jspService (list_jsp.java:187)
    org.apache.jasper.runtime.HttpJspBase.service (HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:741)
    org.apache.jasper.servlet.JspServletWrapper.service (JspServletWrapper.java:444)
    org.apache.jasper.servlet.JspServlet.serviceJspFile (JspServlet.java:386)
    org.apache.jasper.servlet.JspServlet.service (JspServlet.java:330)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter (WsFilter.java:53)
    ListOutput.doGet (ListOutput.java:54)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:634)
    javax.servlet.http.HttpServlet.service (HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter (WsFilter.java:53)
Applicable source code
import java.io. *;
import java.util. *;
import javax.servlet. *;
import javax.servlet.http. *;
import java.sql. *;
@SuppressWarnings ("serial")
public class ListOutput extends HttpServlet {
    public void doGet (HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException {
        String url = "jdbc: mysql: // localhost/kokyaku";
        String user = "root";
        String password = "";
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace ();
        }
        try (Connection connection = DriverManager.getConnection (url, user, password);
            PreparedStatement statement = connection.prepareStatement ("SELECT customers.name, kana, prefectures.name, city, tel FROM customers LEFT JOIN prefectures ON pref_id = prefectures.id");
            ResultSet results = statement.executeQuery ()) {
            ArrayList<HashMap<String, String>>rows = new ArrayList<HashMap<String, String>>();
            while (results.next ()) {
                HashMap<String, String>columns = new HashMap<String, String>();
                String customer_name = results.getString ("customers.name");
                columns.put ("customer_name", customer_name);
                String kana = results.getString ("kana");
                columns.put ("kana", kana);
                String pref = results.getString ("prefectures.name");
                columns.put ("pref", pref);
                String city = results.getString ("city");
                columns.put ("city", city);
                String tel = results.getString ("tel");
                columns.put ("tel", tel);
                rows.add (columns);}
            req.setAttribute ("rows", rows);
        } catch (Exception e) {
            req.setAttribute ("message", "Exception:" + e.getMessage ());
        }
        req.setAttribute ("title", "customer list");
        String view = "WEB-INF/views/list.jsp";
        RequestDispatcher dispatcher = req.getRequestDispatcher (view);
        dispatcher.forward (req, res);
    }
}
<% @ page language = "java" contentType = "text/html;charset = utf-8" pageEncoding = "utf-8"%>
<% @ page import = "java.util. *"%>
<% @ include file = "heading.jsp"%>
    <div>
    <% String message = request.getAttribute ("message"). toString ();%>
    <%
        ArrayList<HashMap<String, String>>rows = (ArrayList<HashMap<String, String>>) request.getAttribute ("rows");
    %>
    <p><% = message%></p>
    <table id = "list_customers">
        <tbody>
            <tr>
                <th>Company Name</th>
                <th>Company Name Kana</th>
                <th>prefecture</th>
                <th>City</th>
                <th>phone number</th>
                <th></th>
                <th></th>
            </tr>
            <%
            for (HashMap<String, String>columns: rows) {
            %>
            <tr>
                <td><% = columns.get ("name")%></td>
                <td><% = columns.get ("kana")%></td>
                <td><% = columns.get ("pref")%></td>
                <td><% = columns.get ("city")%></td>
                <td><% = columns.get ("tel")%></td>
                <td>Edit</td>
                <td>Delete</td>
            </tr>
            <%}%>
        </tbody>
    </table>
    </div>
</div>
</body>
<script type = "text/javascript"><% @ include file = "../ js/script.js"%></script>
</html>

※"heading.jsp"contains the header part.

Tried

From the content of the message, I thought that rows (ArrayList) contained Null, but I didn't understand why Null was contained.

We have confirmed that the following output can be obtained by accessing MySQL from the terminal and executing the SQL specified in PreparedStatement.

+ --------------- + ------------------------------ ----- + ----------- + -------------- + ------------ +
| name | kana | name | city | tel |
+ --------------- + --------------------------------- -+ ----------- + -------------- + ------------ +
A Co., Ltd. | Tokyo | Katsushika City | 0300000000 |
| B Co., Ltd. | Kabushiki Guy Shabby | Tokyo | Edogawa Ward | 0300000000 |
| C Co., Ltd. | Kabushiki Gaisya | Tokyo | Adachi-ku | 0300000000 |
D Co., Ltd. | Kabushiki Guy Shady | Tokyo | Chuo Ward | 0300000000 |
E | Co., Ltd. | Kabushiki Gaishai | Tokyo | Toshima Ward | 0300000000 |
+ --------------- + --------------------------------- -+ ----------- + -------------- + ------------ +

Supplemental information (FW/tool version etc.)

CentOS Linux release 7.5.1804 (Core)

Server version: Apache Tomcat/9.0.10
Server built: Jun 20 2018 17:32:21 UTC
Server number: 9.0.10.0
OS Name: Linux
OS Version: 4.17.8-1.el7.elrepo.x86_64
Architecture: amd64
JVM Version: 1.8.0_181-b13
JVM Vendor: Oracle Corporation

mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64)

  • Answer # 1

    Is MySQL JDBC driver placed in Tomcat's CLASSPATH?

    try {
        Class.forName ("com.mysql.jdbc.Driver");
    } catch (Exception e) {
        e.printStackTrace ();
    }


    If the driver fails to load here, continue processing

    } catch (Exception e) {
        req.setAttribute ("message", "Exception:" + e.getMessage ());
    }


    Here,"message"contains an exception message androwsis notsetAttribute, so it will be null.
    So please comment out the table and check for any error messages.