Servlet 数据库访问
JDBC 连接、预编译语句、连接池，以及在 Servlet 中的正确位置。
Key PointsServlet 里不写 JDBC，只调 DAO；SQL 一律用 PreparedStatement 传参，绝不拼字符串。
分层：Servlet 不该碰 SQL
Text
Browser
↓ HTTP
Servlet ← 取参数、校验、调 Service、选视图
↓
Service ← 业务规则、事务边界
↓
DAO ← SQL、ResultSet 映射
↓
DataSource ← 连接池Writing JDBC in a servlet makes "change a page, change the SQL" the norm and kills testability. Push SQL into a DAO so the servlet only sees objects.
DAO 的写法
Java
public class UserDao {
private final DataSource ds;
public UserDao(DataSource ds) {
this.ds = ds;
}
public User findById(int id) throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE id = ?";
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
u.setEmail(rs.getString("email"));
return u;
}
}
}
public int insert(User u) throws SQLException {
String sql = "INSERT INTO users(name, email) VALUES (?, ?)";
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, u.getName());
ps.setString(2, u.getEmail());
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return keys.getInt(1);
}
}
}
}为什么必须用预编译语句
Java
// 错误：拼字符串，参数里有 ' OR '1'='1 就直接绕过
String bad = "SELECT * FROM users WHERE name = '" + name + "'";
// 正确：参数化，驱动负责转义，值永远当值处理
String good = "SELECT * FROM users WHERE name = ?";
ps.setString(1, name);Text
输入：' OR '1'='1
拼接后：SELECT * FROM users WHERE name = '' OR '1'='1'
结果 ：返回全表 —— 注入成功
参数化后：值整体作为字符串比较，查不到任何行连接池
XML
<Resource name="jdbc/app" auth="Container"
type="javax.sql.DataSource"
maxTotal="20" maxIdle="10" maxWaitMillis="3000"
username="app" password="secret"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/app?useUnicode=true&characterEncoding=utf8"/>Opening a connection per request costs a handshake plus authentication — tens of milliseconds, and high concurrency exhausts the database. A pool keeps connections warm and hands them back instead of closing.
Always close connections in
finally, or use try-with-resources. Leaked connections drain the pool and every request blocks until timeout — a failure that only shows up under load.分层：Servlet 不该碰 SQL
Text
Browser
↓ HTTP
Servlet ← 取参数、校验、调 Service、选视图
↓
Service ← 业务规则、事务边界
↓
DAO ← SQL、ResultSet 映射
↓
DataSource ← 连接池Servlet 里直接写 JDBC，会让「换一个页面就要改 SQL」变成常态，也没法单测。把 SQL 收进 DAO，Servlet 只面对对象。
DAO 的写法
Java
public class UserDao {
private final DataSource ds;
public UserDao(DataSource ds) {
this.ds = ds;
}
public User findById(int id) throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE id = ?";
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
User u = new User();
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
u.setEmail(rs.getString("email"));
return u;
}
}
}
public int insert(User u) throws SQLException {
String sql = "INSERT INTO users(name, email) VALUES (?, ?)";
try (Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, u.getName());
ps.setString(2, u.getEmail());
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return keys.getInt(1);
}
}
}
}为什么必须用预编译语句
Java
// 错误：拼字符串，参数里有 ' OR '1'='1 就直接绕过
String bad = "SELECT * FROM users WHERE name = '" + name + "'";
// 正确：参数化，驱动负责转义，值永远当值处理
String good = "SELECT * FROM users WHERE name = ?";
ps.setString(1, name);Text
输入：' OR '1'='1
拼接后：SELECT * FROM users WHERE name = '' OR '1'='1'
结果 ：返回全表 —— 注入成功
参数化后：值整体作为字符串比较，查不到任何行连接池
XML
<Resource name="jdbc/app" auth="Container"
type="javax.sql.DataSource"
maxTotal="20" maxIdle="10" maxWaitMillis="3000"
username="app" password="secret"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/app?useUnicode=true&characterEncoding=utf8"/>每次请求新建连接要三次握手 + 认证，几十毫秒起步，并发一高数据库直接被打满。连接池预先建好一批连接复用，用完还回去而不是关掉。
连接必须在 finally 里关，或者用 try-with-resources。忘记关连接，池子很快耗尽，表现为请求全部卡住直到超时——这类问题在压力测试里才会暴露。