您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

新聞發布系統升級版：JSP/Servlet + MySQL 完整架構與核心模組實現

編輯：MySQL 綜合教學

新聞發布系統升級版：架構與核心模組實現

基於 JSP/Servlet + MySQL + Commons-DbUtils 的輕量級新聞發布系統，採用 MVC 分層，含登入驗證、新聞增刪改查、主題管理、會話追蹤完整實現。

系統架構

表現層：JSP + Servlet
業務層：Service 類別
資料層：BaseDao + QueryRunner (Commons-DbUtils)
資料庫：MySQL（user、news、topic 三表）

核心資料表

CREATE TABLE `user` (
  `uname` VARCHAR(50) PRIMARY KEY,
  `upwd` VARCHAR(100) NOT NULL
);

CREATE TABLE `topic` (
  `tid` INT PRIMARY KEY AUTO_INCREMENT,
  `tname` VARCHAR(100) NOT NULL
);

CREATE TABLE `news` (
  `nid` INT PRIMARY KEY AUTO_INCREMENT,
  `ntitle` VARCHAR(200) NOT NULL,
  `ncontent` TEXT,
  `npubtime` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `tid` INT,
  `uname` VARCHAR(50),
  FOREIGN KEY (tid) REFERENCES topic(tid),
  FOREIGN KEY (uname) REFERENCES user(uname)
);

BaseDao 連線管理

public class BaseDao {
    private static final String URL = "jdbc:mysql://localhost:3306/newsdb?useSSL=false";
    private static final String USER = "root";
    private static final String PASS = "123456";
    
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }
}

登入模組

@WebServlet("/login")
public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String uname = req.getParameter("uname");
        String upwd = req.getParameter("upwd");
        
        String sql = "SELECT COUNT(1) FROM user WHERE uname=? AND upwd=?";
        QueryRunner qr = new QueryRunner();
        try (Connection conn = new BaseDao().getConnection()) {
            Long count = qr.query(conn, sql, new ScalarHandler(), uname, upwd);
            if (count > 0) {
                HttpSession session = req.getSession();
                session.setAttribute("uname", uname);
                Cookie cookie = new Cookie("uname", uname);
                cookie.setMaxAge(60*60*24);
                resp.addCookie(cookie);
                resp.sendRedirect(req.getContextPath() + "/admin.jsp");
            } else {
                resp.sendRedirect(req.getContextPath() + "/index.jsp");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

新聞 CRUD 核心邏輯

// 新增
String sql = "INSERT INTO news(ntitle, ncontent, tid, uname) VALUES(?,?,?,?)";
qr.update(conn, sql, title, content, tid, uname);

// 列表（分頁）
sql = "SELECT n.*, t.tname FROM news n LEFT JOIN topic t ON n.tid=t.tid ORDER BY n.npubtime DESC LIMIT ?,?";
List list = qr.query(conn, sql, new BeanListHandler(News.class), offset, pageSize);

// 編輯回顯
sql = "SELECT * FROM news WHERE nid=?";
News news = qr.query(conn, sql, new BeanHandler(News.class), nid);

// 更新
sql = "UPDATE news SET ntitle=?, ncontent=?, tid=? WHERE nid=?";
qr.update(conn, sql, title, content, tid, nid);

// 刪除
sql = "DELETE FROM news WHERE nid=?";
qr.update(conn, sql, nid);