Java 封裝 MySQL 訪問：JDBC 連接池 + DAO 模式生成 JSP

架構分層

[JSP 頁面] → [Servlet/Controller] → [Service] → [DAO] → [連接池] → [MySQL]

核心組件

1. 連接池配置

// HikariCP (推薦) 或 Druid
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/dbname?useSSL=false&serverTimezone=UTC");
config.setUsername("user");
config.setPassword("pass");
config.setMaximumPoolSize(20);
DataSource ds = new HikariDataSource(config);

2. DAO 基類

public abstract class BaseDAO {
    protected DataSource dataSource;
    
    protected Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    // 通用查詢模板
    protected  List query(String sql, RowMapper mapper, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                List list = new ArrayList<>();
                while (rs.next()) list.add(mapper.mapRow(rs));
                return list;
            }
        }
    }
}

3. 具體 DAO

public class UserDAO extends BaseDAO {
    public List findAll() {
        return query("SELECT * FROM users", rs -> new User(
            rs.getLong("id"),
            rs.getString("username"),
            rs.getString("email")
        ));
    }
}

4. Service 層

@Service
public class UserService {
    @Autowired UserDAO userDAO;
    
    public List getUsers() {
        return userDAO.findAll(); // 可加事務、緩存、業務邏輯
    }
}

5. Controller + JSP 渲染

@Controller
public class UserController {
    @Autowired UserService userService;
    
    @GetMapping("/users")
    public String list(Model model) {
        model.addAttribute("users", userService.getUsers());
        return "user/list"; // 對應 /WEB-INF/jsp/user/list.jsp
    }
}

6. JSP 使用 EL 表達式

<%@ taglib prefix="c" uri="https://java.sun.com/jsp/jstl/core" %>
<table>
  <c:forEach items="${users}" var="user">
    <tr>
      <td>${user.id}</td>
      <td>${user.username}</td>
      <td>${user.email}</td>
    </tr>
  </c:forEach>
</table>

生成靜態 JSP/HTML 文件（如需文件落地）

public void generateStaticPage() throws Exception {
    // 1. 獲取數據
    List users = userService.getUsers();
    
    // 2. 渲染模板 (可用 Velocity, Freemarker, Thymeleaf, 或簡單 StringBuilder)
    String html = renderTemplate("user_list.ftl", Map.of("users", users));
    
    // 3. 寫入文件
    Files.write(Paths.get("/var/www/html/users.html"), html.getBytes(StandardCharsets.UTF_8));
}

private String renderTemplate(String template, Map model) {
    // Freemarker 示例
    Configuration cfg = new Configuration(Configuration.VERSION_2_3_31);
    cfg.setClassForTemplateLoading(this.getClass(), "/templates");
    Template temp = cfg.getTemplate(template);
    StringWriter out = new StringWriter();
    temp.process(model, out);
    return out.toString();
}

關鍵最佳實踐

總結

Java 訪問 MySQL 標準路徑：連接池 → DAO → Service → Controller → JSP(EL)。生成靜態文件用模板引擎渲染後 IO 寫入。切記分層解耦，避免在 JSP 中直寫 JDBC 代碼。