-- 创建数据库
if exit forum_management_information_system1 drop database `forum_management_information_system1`;
CREATE SCHEMA `forum_management_information_system1` DEFAULT CHARACTER SET utf8 ;
 
-- 创建管理员信息表
CREATE TABLE `admin` (
  `adminID` int NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
  `adminName` varchar(20) NOT NULL COMMENT '管理员名称',
  `adminPassword` varchar(20) NOT NULL COMMENT '管理员密码',
  PRIMARY KEY (`adminID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='管理员信息表';
 
-- 创建用户信息表
CREATE TABLE `user` (
  `uID` int NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `userName` varchar(20) NOT NULL COMMENT '用户昵称',
  `userPassword` varchar(20) NOT NULL COMMENT '用户密码',
  `userSex` varchar(20) NOT NULL COMMENT '用户性别',
  `userAge` int NOT NULL COMMENT '用户生日',
  `userOccupation` varchar(20) NOT NULL COMMENT '用户职业',
  `userHobby` varchar(20) NOT NULL COMMENT '用户爱好',
  `userPoint` int NOT NULL DEFAULT '0' COMMENT '用户积分',
  `userClass` int NOT NULL DEFAULT '0' COMMENT '用户等级',
  `userRegister` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户注册时间',
  PRIMARY KEY (`uID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3 COMMENT='用户信息表';
 
-- 板块信息表
CREATE TABLE `section` (
  `sID` int NOT NULL AUTO_INCREMENT COMMENT '板块编号',
  `uID` int NOT NULL COMMENT '版主编号（用户编号）',
  `sName` varchar(50) NOT NULL COMMENT '板块名称',
  `sStatement` text NOT NULL COMMENT '板块说明',
  `sClickCount` int NOT NULL DEFAULT '0' COMMENT '板块点击次数',
  `sTopicCount` int NOT NULL DEFAULT '0' COMMENT '板块主题数',
  PRIMARY KEY (`sID`),
  KEY `s_uID_idx` (`uID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COMMENT='板块信息表';
 
-- 主贴信息表
CREATE TABLE `topic` (
  `tID` int NOT NULL AUTO_INCREMENT COMMENT '主贴编号',
  `sID` int NOT NULL COMMENT '主贴板块编号',
  `uID` int NOT NULL COMMENT '主贴用户编号',
  `tTopic` varchar(20) NOT NULL COMMENT '主贴标题',
  `tContent` text NOT NULL COMMENT '主贴内容',
  `tTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '主贴时间',
  `tReplyCount` int NOT NULL DEFAULT '0' COMMENT '主贴回复次数',
  `tClickCount` int NOT NULL DEFAULT '0' COMMENT '主贴点击次数',
  PRIMARY KEY (`tID`),
  KEY `t_uID_idx` (`uID`),
  KEY `t_sID_idx` (`sID`),
  CONSTRAINT `t_sID` FOREIGN KEY (`sID`) REFERENCES `section` (`sID`),
  CONSTRAINT `t_uID` FOREIGN KEY (`uID`) REFERENCES `user` (`uID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 COMMENT='主贴信息表';
 
-- 回复贴信息表
CREATE TABLE `reply` (
  `rID` int NOT NULL AUTO_INCREMENT COMMENT '回帖编号',
  `tID` int NOT NULL COMMENT '回帖主题帖子编号（主贴信息表）',
  `uID` int NOT NULL COMMENT '回帖的用户编号',
  `rTopic` varchar(20) NOT NULL DEFAULT '回帖' COMMENT '回帖标题',
  `rContent` text NOT NULL COMMENT '回帖内容',
  `rTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '回帖时间',
  `rClickCount` int NOT NULL DEFAULT '0' COMMENT '回帖点击次数',
  PRIMARY KEY (`rID`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 COMMENT='回复贴信息表';
 
-- 初始数据 （增加一个用户）
INSERT INTO `forum_management_information_system1`.`user` (`userName`, `userPassword`, `userSex`, `userAge`, `userOccupation`, `userHobby`) VALUES ('管理员', '123456', '男', '18', '管理员', '你猜');
 
-- 初始化板块数据
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '网易云', '小时候枕头上全是口水，长大后枕头上全是泪水；小时候微笑是一个心情，长大后微笑是一种表情；小时候哭着哭着就笑了，长大后笑着笑着就哭了。我们终于到了小时候最羡慕的年龄，但却没有成为小时候最想成为的人。——网易云音乐热评《当我已不再那么年轻》');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '码农', '码农Coding Peasant(s)：可以指在程序设计某个专业领域中的专业人士，或是从事软体撰写，程序开发、维护的专业人员。但一般Coder特指进行编写代码的编码员。');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '电影', '电影，也被称为运动画面或动态画面，即“映画”，是作品视觉艺术形式，通过使用移动图像来表达沟通思想，故事，认知，情感，价值观，或各类大气模拟体验。这些图像通常伴随着声音，很少有其他感官刺激。 “电影”一词是电影摄影的缩写，通常用于指代电影制作和电影业，以及由此产生的艺术形式。');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '体育', '（ physical education ，缩写 PE 或 P.E. ），是一种复杂的社会文化现象，它是一种以身体与智力活动为基本手段，根据人体生长发育、技能形成和机能提高等规律，达到促进全面发育、提高身体素质与全面教育水平、增强体质与提高运动能力、改善生活方式与提高生活质量的一种有意识、有目的、有组织的社会活动。');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '游戏', '游戏（英文: Game）是所有哺乳类动物，特别是灵长类动物学习生存的第一步。它是一种基于物质需求满足之上的，在一些特定时间、空间范围内遵循某种特定规则的，追求精神世界需求满足的社会行为方式，但同时这种行为方式也是哺乳类动物或者灵长类动物所需的一种降压减排的方式，不管是在出生幼年期，或者发育期，成熟期都会需要的一种行为方式。');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '旅行', '“旅”是旅行，外出，即为了实现某一目的而在空间上从甲地到乙地的行进过程；“游”是外出游览、观光、娱乐，即为达到这些目的所作的旅行。二者合起来即旅游。所以，旅行偏重于行，旅游不但有“行”，且有观光、娱乐含义。');
INSERT INTO `forum_management_information_system1`.`section` (`uID`, `sName`, `sStatement`) VALUES ('1', '明星', ' (拼音míng xīng，注音ㄇㄧㄥˊ ㄒㄧㄥ)汉语 词语 ，古书上指金星；旧时指 交际 场中有名的女子；现指在某个领域内有一定影响力的人物。 泛指有名的 演员 、 歌手 、 艺人 、 运动员 等。');

 1.  DBUtil 类（JDBC连接数据库）

package forum.util;
 
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import lombok.SneakyThrows;
 
import javax.sql.DataSource;
import java.sql.Connection;
 
/**
 * @author haomin
 * @date 2022/06/29 17:23
 **/
public class DBUtil {
    private static final DataSource dataSource;
 
    static {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        // 数据库路径要正确
        mysqlDataSource.setUrl("jdbc:mysql:///forum_management_information_system1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        dataSource = mysqlDataSource;
    }
 
    @SneakyThrows
    public static Connection connection() {
        return dataSource.getConnection();
    }
 
}

2. 创建实体类

package forum.model;
 
import lombok.Data;
 
// 使用@Data注解在类上时，不用再手动添加get/set等方法了，简化代码
@Data
public class User {
    public Integer uID;
    public String username;
    public String password;
    public String userSex;
    public Integer userAge;
    public String userOccupation;
    public String userHobby;
    public Integer userPoint;
    public Integer userClass;
    public String userRegister;
}

2.2 Section

package forum.model;
 
import lombok.Data;
 
@Data
public class Section {
    public Integer sID;
    public Integer uID;
    public String sName;
    public String sStatement;
    public Integer sClickCount;
    public Integer sTopicCount;
}

2.3 Topic

package forum.model;
 
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
 
import java.util.Date;
 
@Data
public class Topic {
    public Integer tID;
    public Integer sID;
    public Integer uID;
    public String username;
    public String tTopic;
    public String tContent;
    //@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public String tTime;
    public Integer tReplyCount;
    public Integer tClickCount;
}

2.4 Reply

package forum.model;
 
import com.fasterxml.jackson.annotation.JsonFormat;
 
import java.util.Date;
 
public class Reply {
    public Integer rID;
    public Integer tID;
    public Integer uID;
    public String username;
    public String rTopic;
    public String rContent;
    public String rTime;
    public Integer rClickCount;
}

2.5 Key

为实现一个搜索功能，新建了一个Keyword实体

package forum.model;
 
import lombok.Data;
 
@Data
public class Keyword {
    public String keyword;
}

3. 封装对数据库的增删查改类

package forum.dao;
 
import forum.model.User;
import forum.util.DBUtil;
import lombok.SneakyThrows;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @author haomin
 * @date 2022/06/29 17:22
 **/
 
// 从数据库查找user数据的方法类
public class UserDao {
 
    // 用户帖子数
    @SneakyThrows
    public int selectPointCountByUid(int uid){
        String sql = "select count(*) from topic where uID = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,uid);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
                    int count = rs.getInt("count(*)");
                    updatePointCountByUid(uid,count);
                    return count;
                }
            }
        }
    }
 
    // 更新主贴回复数
    @SneakyThrows
    public void updatePointCountByUid(int uID, int count) {
        String sql = "update user set userPoint = ? where uID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,count);
                ps.setInt(2,uID);
 
                ps.executeUpdate();
            }
        }
    }
 
    // 用户等级
    public int selectClassCountByUid(int uid){
        int point = new UserDao().selectPointCountByUid(uid);
        int classCount = 0;
        if(point == 0){
            classCount = 0;
        }else if(point < 5){
            classCount = 1;
        }else if(point < 15){
            classCount = 2;
        }else if(point < 30){
            classCount = 3;
        }else {
            classCount = 4;
        }
        updateClassCountByUid(uid,classCount);
        return classCount;
    }
 
    @SneakyThrows
    private void updateClassCountByUid(int uID, int classCount) {
        String sql = "update user set userClass = ? where uID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,classCount);
                ps.setInt(2,uID);
 
                ps.executeUpdate();
            }
        }
 
    }
 
    // 查询用户ByUsernameAndPassword
    @SneakyThrows
    public User selectOneByUsernameAndPassword(String username,String password){
        String sql = "select uID, userSex, userAge,userOccupation, userHobby, userPoint, userClass, userRegister from user where userName = ? and userPassword = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setString(1,username);
                ps.setString(2,password);
 
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    if(!rs.next()){
                        return null;
                    }
 
                    User user = new User();
                    user.uID = rs.getInt("uID");
                    user.username = username;
                    user.password = password;
                    user.userSex = rs.getString("userSex");
                    user.userAge = rs.getInt("userAge");
                    user.userOccupation = rs.getString("userOccupation");
                    user.userHobby = rs.getString("userHobby");
                    user.userPoint = rs.getInt("userPoint");
                    user.userClass = rs.getInt("userClass");
                    String Time = rs.getString("userRegister");
                    user.userRegister = Time.substring(0,Time.length()-5);
 
                    return user;
                }
            }
        }
    }
 
    // 查询用户名ByUid
    @SneakyThrows
    public String selectUserNameByUid(int uid){
        String sql = "select username from user where uID = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,uid);
                try (ResultSet rs = ps.executeQuery()){
                    if(!rs.next()){
                        return null;
                    }
                    String username = rs.getString("username");
 
                    return username;
                }
            }
        }
    }
 
    // 新建用户
    @SneakyThrows
    public User insert(String username, String password, String sex, int age, String occupation, String hobby) {
        String sql = "insert into user (username,userPassword,userSex,userAge,userOccupation,userHobby) values (?, ?, ?, ?, ?, ?)";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setString(1,username);
                ps.setString(2,password);
                ps.setString(3,sex);
                ps.setInt(4,age);
                ps.setString(5,occupation);
                ps.setString(6,hobby);
 
                ps.executeUpdate();
                System.out.println("新建用户完成");
                return new UserDao().selectOneByUsernameAndPassword(username,password);
            }
        }
    }
}

3.2 SectionDao

package forum.dao;
 
import forum.model.Section;
import forum.util.DBUtil;
import lombok.SneakyThrows;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author haomin
 * @date 2022/06/30 11:32
 **/
public class SectionDao {
 
    // 得到板块集合ByUid
    @SneakyThrows
    public List
 selectListByUid(){
        List
 list = new ArrayList<>();
        String sql = "select sID, uID, sName, sStatement, sClickCount, sTopicCount from section;";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)){
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Section section = new Section();
                        section.sID = rs.getInt("sID");
                        section.uID = rs.getInt("uID");
                        section.sName = rs.getString("sName");
                        section.sStatement = rs.getString("sStatement");
                        section.sClickCount = rs.getInt("sClickCount");
                        section.sTopicCount = new TopicDao().sTopicCount(section.sID);
 
                        list.add(section);
                    }
                }
            }
        }
        return list;
    }
 
    // 得到板块集合ByKeyWord
    @SneakyThrows
    public List
 selectSectionListByKeyword(String keyword) {
        List
 list = new ArrayList<>();
        String sql = "select * from section where sName like ?;";
 
        System.out.println(sql);
        try (Connection c = DBUtil.connection()) {
            System.out.println(sql);
 
            try (PreparedStatement ps = c.prepareStatement(sql)){
                System.out.println(sql);
                String key = "%"+keyword+"%";
                ps.setString(1,key);
                System.out.println(sql);
 
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Section section = new Section();
                        section.sID = rs.getInt("sID");
                        section.uID = rs.getInt("uID");
                        section.sName = rs.getString("sName");
                        section.sStatement = rs.getString("sStatement");
                        section.sClickCount = rs.getInt("sClickCount");
                        section.sTopicCount = new TopicDao().sTopicCount(section.sID);
 
                        list.add(section);
                    }
                }
            }
        }
        return list;
    }
 
    // 更新浏览次数
    @SneakyThrows
    public void update(int sid) {
        String sql = "update section set sClickCount = sClickCount+1 where sid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,sid);
 
                ps.executeUpdate();
            }
        }
    }
}

3.3 TopicDao

package forum.dao;
 
import forum.model.Section;
import forum.model.Topic;
import forum.model.User;
import forum.util.DBUtil;
import lombok.SneakyThrows;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author haomin
 * @date 2022/06/30 16:38
 **/
public class TopicDao {
 
    // 查询板块帖子数
    @SneakyThrows
    public int sTopicCount(Integer sID) {
        String sql = "select count(*) from topic where sID = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sID);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
                    int count = rs.getInt("count(*)");
 
                    updateSTopicCount(sID, count);
                    return count;
                }
            }
        }
    }
 
    // 更新板块帖子数
    @SneakyThrows
    public void updateSTopicCount(int sID, int count) {
        String sql = "update section set sTopicCount = ? where sID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,count);
                ps.setInt(2,sID);
 
                ps.executeUpdate();
            }
        }
    }
 
    // 得到主贴集合BySid
    @SneakyThrows
    public List selectListByUid(int sid){
        List list = new ArrayList<>();
        String sql = "select tID, uID, tTopic, tContent, tTime, tReplyCount, tClickCount from topic where sID = ? order by tTime desc";
        try(Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sid);
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Topic topic = new Topic();
                        topic.tID = rs.getInt("tID");
                        topic.sID = sid;
                        topic.uID = rs.getInt("uID");
                        topic.username = new UserDao().selectUserNameByUid(topic.uID);
                        topic.tTopic = rs.getString("tTopic");
                        topic.tContent = rs.getString("tContent");
                        String Time = rs.getString("tTime");
                        topic.tTime = Time.substring(0,Time.length()-5);
 
                        topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                        topic.tClickCount = rs.getInt("tClickCount");
 
                        list.add(topic);
                    }
                }
            }
        }
        return list;
    }
 
    // 得到主贴集合ByKeyword
    @SneakyThrows
    public List selectTopicListByKeyword(String keyword) {
        List list = new ArrayList<>();
        String sql = "select * from topic where tTopic like ?;";
 
        System.out.println(sql);
        try (Connection c = DBUtil.connection()) {
            System.out.println(sql);
 
            try (PreparedStatement ps = c.prepareStatement(sql)){
                System.out.println(sql);
                String key = "%"+keyword+"%";
                ps.setString(1,key);
                System.out.println(sql);
 
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Topic topic = new Topic();
                        topic.tID = rs.getInt("tID");
                        topic.sID = rs.getInt("sId");
                        topic.uID = rs.getInt("uID");
                        topic.username = new UserDao().selectUserNameByUid(topic.uID);
                        topic.tTopic = rs.getString("tTopic");
                        topic.tContent = rs.getString("tContent");
                        String Time = rs.getString("tTime");
                        topic.tTime = Time.substring(0,Time.length()-5);
 
                        topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                        topic.tClickCount = rs.getInt("tClickCount");
 
                        list.add(topic);
                    }
                }
            }
        }
 
        return list;
    }
 
    // 查询一个主贴ByTid
    @SneakyThrows
    public Topic selectTopicByTid(int tid) {
        String sql = "select sID, uID, tTopic, tContent, tTime, tReplyCount, tClickCount from topic where tID = ?";
        try(Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,tid);
 
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
 
                    Topic topic = new Topic();
                    topic.tID = tid;
                    topic.sID = rs.getInt("sID");
                    topic.uID = rs.getInt("uID");
                    topic.username = new UserDao().selectUserNameByUid(topic.uID);
                    topic.tTopic = rs.getString("tTopic");
                    topic.tContent = rs.getString("tContent");
                    String Time = rs.getString("tTime");
                    topic.tTime = Time.substring(0,Time.length()-5);
 
                    topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                    topic.tClickCount = rs.getInt("tClickCount");
 
                    return topic;
                }
            }
        }
    }
 
    // 插入主贴
    @SneakyThrows
    public void insert(int sid, Integer uID, String title, String content) {
        String sql = "insert into topic (sID, uID, tTopic, tContent) values (?, ?, ?, ?)";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sid);
                ps.setInt(2,uID);
                ps.setString(3,title);
                ps.setString(4,content);
 
                ps.executeUpdate();
                System.out.println("插入完成");
            }
        }
    }
 
    // 更新主贴浏览次数
    @SneakyThrows
    public void update(int tid) {
        String sql = "update topic set tClickCount = tClickCount+1 where tid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,tid);
 
                ps.executeUpdate();
            }
        }
    }
 
 
}

3.4 ReplyDao

package forum.dao;
 
import forum.model.Reply;
import forum.util.DBUtil;
import lombok.SneakyThrows;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author haomin
 * @date 2022/06/30 16:38
 **/
public class ReplyDao {
 
    // 查询主贴回复数
    @SneakyThrows
    public int tReplyCount(int tid){
        try (Connection c = DBUtil.connection()){
            String sql = "select count(*) from reply where tID = ?;";
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,tid);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
                    int count = rs.getInt("count(*)");
                    updateTReplyCount(tid,count);
                    return count;
                }
            }
        }
    }
 
    // 更新主贴回复数
    @SneakyThrows
    public void updateTReplyCount(int tID, int count) {
        String sql = "update topic set tReplyCount = ? where tID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,count);
                ps.setInt(2,tID);
 
                ps.executeUpdate();
            }
        }
    }
 
    // 根据tid查询所有回复
    @SneakyThrows
    public List selectListByTid(int tid){
        List list = new ArrayList<>();
        String sql = "select rID, uID, rTopic, rContent, rTime, rClickCount from reply where tid = ? order by rTime desc";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,tid);
                System.out.println("DEBUG: " + ps);
 
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Reply reply = new Reply();
                        reply.rID = rs.getInt("rID");
                        reply.tID = tid;
                        reply.uID = rs.getInt("uID");
                        reply.username = new UserDao().selectUserNameByUid(reply.uID);
                        reply.rTopic = rs.getString("rTopic");
                        reply.rContent = rs.getString("rContent");
                        String Time = rs.getString("rTime");
                        reply.rTime = Time.substring(0,Time.length()-5);
                        reply.rClickCount = rs.getInt("rClickCount");
 
                        list.add(reply);
                    }
                }
 
            }
        }
        return list;
    }
 
    // 插入主贴
    @SneakyThrows
    public void insert(int tid, Integer uID, String content) {
        try (Connection c = DBUtil.connection()){
            String sql = "insert into reply (tID,uID,rContent) values (?,?,?)";
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,tid);
                ps.setInt(2,uID);
                ps.setString(3,content);
 
                ps.executeUpdate();
                System.out.println("插入完成");
            }
        }
    }
}

3.5 KeyDao

package forum.dao;
 
import forum.model.Keyword;
import forum.util.DBUtil;
import lombok.SneakyThrows;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @author haomin
 * @date 2022/07/04 10:11
 **/
 
public class KeyDao {
 
    // 插入查询的关键字
    @SneakyThrows
    public void insertKey (String keyword){
        String sql = "INSERT INTO `forum_management_information_system`.`key` (`keyword`) VALUES (?);";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setString(1,keyword);
 
                ps.executeUpdate();
                System.out.println("插入完成");
            }
        }
    }
 
    // 更新主贴回复数
    @SneakyThrows
    public void updateTReplyCount(int tID, int count) {
        String sql = "update topic set tReplyCount = ? where tID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,count);
                ps.setInt(2,tID);
 
                ps.executeUpdate();
            }
        }
    }
 
    // 查询是否存在关键字
    @SneakyThrows
    public String selectKey () {
        String sql = "select keyword from forum_management_information_system.key;";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                try (ResultSet rs = ps.executeQuery()){
                    if(rs.next()){
                        String keyword = rs.getString("keyword");
                        return keyword;
                    }else {
                        return null;
                    }
 
                }
            }
        }
    }
 
    // 删除关键字
    @SneakyThrows
    public void deleteKey () {
        String sql = "DELETE FROM `forum_management_information_system`.`key` WHERE (1 = 1);";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.executeUpdate();
            }
        }
    }
}