Querying MySQL and Rendering Results in JSP Using a Servlet Controller
Tech
1
A simple request–controller–view pipeline retrieves a user-supplied key, queries MySQL with a parameterized statement, and renders the result set in JSP.
- Input page (JSP): collects the lookup key and submits it to a servlet
- Servlet: reads the input, runs a join query against MySQL, packages rows, forwards to a view JSP
- View page (JSP): iteraets over the rows and prints an HTML table
SQL used in the controller (parameterized):
SELECT s.name AS student_name,
d.`desc` AS type_desc,
e.contact AS contact_value
FROM student s
JOIN contact_ext e ON e.idcard = s.idcard
JOIN contact_desc d ON d.contact_type = e.contact_type
WHERE s.idcard = ?;
Enput page (search.jsp):
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Lookup Contacts</title>
<style>
body { font-family: system-ui, Arial, sans-serif; }
form { margin: 24px auto; max-width: 480px; }
label { display: block; margin-bottom: 8px; }
input[type=text] { width: 100%; padding: 8px; }
button { margin-top: 12px; padding: 8px 16px; }
</style>
</head>
<body>
<form action="LookupContacts" method="get">
<label for="idNumber">Student ID:</label>
<input id="idNumber" name="idNumber" type="text" value="1" />
<button type="submit">Search</button>
</form>
</body>
</html>
Servlet cnotroller (LookupContacts.java):
package com.example.web;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@WebServlet(name = "LookupContacts", urlPatterns = "/LookupContacts")
public class LookupContacts extends HttpServlet {
// JDBC configuration
private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/zdy?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8";
private static final String JDBC_USER = "root";
private static final String JDBC_PASS = "1111";
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String idNumber = request.getParameter("idNumber");
// Basic normalization (optional); allow only digits if IDs are numeric
if (idNumber == null || idNumber.isBlank()) {
request.setAttribute("rows", List.of());
request.getRequestDispatcher("/results.jsp").forward(request, response);
return;
}
String sql = "SELECT s.name AS student_name, d.`desc` AS type_desc, e.contact AS contact_value\n" +
"FROM student s\n" +
"JOIN contact_ext e ON e.idcard = s.idcard\n" +
"JOIN contact_desc d ON d.contact_type = e.contact_type\n" +
"WHERE s.idcard = ?";
List<Map<String, Object>> rows = new ArrayList<>();
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e) {
throw new ServletException("MySQL driver not found", e);
}
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, idNumber);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
Map<String, Object> m = new HashMap<>();
m.put("studentName", rs.getString("student_name"));
m.put("typeLabel", rs.getString("type_desc"));
m.put("contactData", rs.getString("contact_value"));
rows.add(m);
}
}
} catch (Exception ex) {
throw new ServletException("Database query failed", ex);
}
request.setAttribute("rows", rows);
request.getRequestDispatcher("/results.jsp").forward(request, response);
}
}
Results page (results.jsp):
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Query Results</title>
<style>
table { border-collapse: collapse; margin: 24px auto; min-width: 480px; }
th, td { border: 1px solid #d0d0d0; padding: 8px 12px; text-align: left; }
th { background: #f6f6f6; }
caption { margin-bottom: 8px; font-weight: 600; }
</style>
</head>
<body>
<table>
<caption>Contact Information</caption>
<thead>
<tr>
<th>Name</th>
<th>Description</th>
<th>Contact</th>
</tr>
</thead>
<tbody>
<c:forEach items="${rows}" var="r">
<tr>
<td>${r.studentName}</td>
<td>${r.typeLabel}</td>
<td>${r.contactData}</td>
</tr>
</c:forEach>
<c:if test="${empty rows}">
<tr>
<td colspan="3">No data found.</td>
</tr>
</c:if>
</tbody>
</table>
</body>
</html>
Notes:
- The servlet uses a PreparedStatement to avoid SQL injection.
- Add JSTL to the web application for the c:forEach tag (e.g., include the appropriate JSTL library on the classpath).
