JSP 连接数据库
JDBC 在 JSP 里的写法、为什么不该写在页面里、以及正确分层。
Key Points页面里不写 JDBC。用 Servlet 或 Service 查好数据放进 request，JSP 只负责渲染。
先看错误示范
JSP
<%-- 能跑，但不要这么写 --%>
<%@ page import="java.sql.*" %>
<%
Class.forName("com.mysql.cj.jdbc.Driver");
Connection c = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/app", "root", "123456");
Statement st = c.createStatement();
ResultSet rs = st.executeQuery(
"SELECT * FROM users WHERE name = '" + request.getParameter("name") + "'");
while (rs.next()) {
out.println("<p>" + rs.getString("name") + "</p>");
}
rs.close(); st.close(); c.close();
%>This snippet has four defects: the password is hardcoded in the page, string-concatenated SQL invites injection, the connection has no exception safety, and unescaped output enables XSS. Any one is enough to compromise the system.
正确做法：分层
Java
// UserDao.java —— SQL 只出现在这里
public class UserDao {
private final DataSource ds;
public UserDao(DataSource ds) { this.ds = ds; }
public List<User> search(String keyword) throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE name LIKE ?";
List<User> list = new ArrayList<>();
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, "%" + keyword + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
u.setEmail(rs.getString("email"));
list.add(u);
}
}
}
return list;
}
}Java
// UserServlet.java —— 取参数、调 DAO、放进 request、转发
@WebServlet("/users")
public class UserServlet extends HttpServlet {
private UserDao dao;
@Override
public void init() {
DataSource ds = (DataSource) getServletContext().getAttribute("ds");
dao = new UserDao(ds);
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String kw = req.getParameter("kw");
try {
req.setAttribute("users", dao.search(kw == null ? "" : kw));
} catch (SQLException e) {
throw new ServletException(e);
}
req.getRequestDispatcher("/WEB-INF/views/users.jsp").forward(req, resp);
}
}JSP
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<table>
<c:forEach var="u" items="${users}">
<tr>
<td>${u.id}</td>
<td><c:out value="${u.name}"/></td>
<td><c:out value="${u.email}"/></td>
</tr>
</c:forEach>
</table>数据源配置
XML
<Resource name="jdbc/app" auth="Container"
type="javax.sql.DataSource"
maxTotal="20" maxIdle="10"
username="app" password="secret"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/app?useUnicode=true&characterEncoding=utf8"/>XML
<resource-ref>
<res-ref-name>jdbc/app</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>DriverManager.getConnection per request costs a handshake and authentication — tens of milliseconds — and high concurrency exhausts the database. A pool keeps connections warm and returns them instead of closing. Non-negotiable in production.关闭顺序
Java
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
// 用 rs
} // 逆序自动关闭：rs → ps → ctry-with-resources closes in reverse declaration order, which matches JDBC's requirement. A hand-written finally in the wrong order (closing the Connection first) makes rs.close() throw and swallow the real error.
先看错误示范
JSP
<%-- 能跑，但不要这么写 --%>
<%@ page import="java.sql.*" %>
<%
Class.forName("com.mysql.cj.jdbc.Driver");
Connection c = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/app", "root", "123456");
Statement st = c.createStatement();
ResultSet rs = st.executeQuery(
"SELECT * FROM users WHERE name = '" + request.getParameter("name") + "'");
while (rs.next()) {
out.println("<p>" + rs.getString("name") + "</p>");
}
rs.close(); st.close(); c.close();
%>这段代码有四个问题：密码硬编码在页面里、SQL 拼接导致注入、连接没有异常保护、输出没有转义导致 XSS。任何一个都足以让系统被攻破。
正确做法：分层
Java
// UserDao.java —— SQL 只出现在这里
public class UserDao {
private final DataSource ds;
public UserDao(DataSource ds) { this.ds = ds; }
public List<User> search(String keyword) throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE name LIKE ?";
List<User> list = new ArrayList<>();
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, "%" + keyword + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
u.setEmail(rs.getString("email"));
list.add(u);
}
}
}
return list;
}
}Java
// UserServlet.java —— 取参数、调 DAO、放进 request、转发
@WebServlet("/users")
public class UserServlet extends HttpServlet {
private UserDao dao;
@Override
public void init() {
DataSource ds = (DataSource) getServletContext().getAttribute("ds");
dao = new UserDao(ds);
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String kw = req.getParameter("kw");
try {
req.setAttribute("users", dao.search(kw == null ? "" : kw));
} catch (SQLException e) {
throw new ServletException(e);
}
req.getRequestDispatcher("/WEB-INF/views/users.jsp").forward(req, resp);
}
}JSP
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<table>
<c:forEach var="u" items="${users}">
<tr>
<td>${u.id}</td>
<td><c:out value="${u.name}"/></td>
<td><c:out value="${u.email}"/></td>
</tr>
</c:forEach>
</table>数据源配置
XML
<Resource name="jdbc/app" auth="Container"
type="javax.sql.DataSource"
maxTotal="20" maxIdle="10"
username="app" password="secret"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/app?useUnicode=true&characterEncoding=utf8"/>XML
<resource-ref>
<res-ref-name>jdbc/app</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>每个请求
DriverManager.getConnection 要握手 + 认证，几十毫秒起步，并发一高数据库连接数直接打满。连接池预先建好连接复用，用完还回去而不是关掉——这是生产环境的硬要求。关闭顺序
Java
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
// 用 rs
} // 逆序自动关闭：rs → ps → ctry-with-resources 按声明的逆序关闭，正好符合 JDBC 的要求。手写 finally 时如果顺序反了（先关 Connection），后面的 rs.close() 会抛异常，反而吞掉真正的错误。