🤞目录🤞 💖一. 准备工作 💎1) 创建 web 项目 💎2) 创建目录结构 💎3) 配置 pom.xml 💖二. 数据库操作代
🤞目录🤞
【大家好,我是爱干饭的猿,如果喜欢这篇文章,点个赞👍,关注一下吧,后续会持续分享其他项目的相关操作和linux相关知识】
上一篇文章:《前端项目 - 论坛信息管理系统(纯页面)》
本篇文章介绍的《论坛信息管理系统》源码链接放在文章末尾了,感兴趣的朋友可以看看。
上一篇文章中我们完成了论坛信息管理系统的前端页面,现在我们可以基于模板的方式,通过服务器把数据渲染到页面中,然后直接返回完整的页面给浏览器。
目前比较主流的开发方式是“前后端分离”的方式,在这种方式下服务端不关注页面的内容,而是网页提供的数据。
网页可以通过ajax的方式和服务器之间交互数据,网页拿到数据之后再根据数据的内容渲染到页面上。
此篇文章仅展示重要代码思路过程,项目源码在 gitee 中 链接: 《论坛信息管理系统》
效果展示
论坛列表页:
论坛主贴页:
论坛回帖页:
用户写帖子页:
用户个人信息页:
论坛登录页:
论坛注册页:
4.0.0 org.example forum-inforation-management-system 1.0-SNAPSHOT war 1.8 1.8 javax.servlet javax.servlet-api 3.1.0 provided Mysql mysql-connector-java 5.1.49 com.fasterxml.jackson.core jackson-databind 2.12.4 org.projectlombok lombok 1.18.20 compile
- 管理员信息表(管理员编号,管理员名称,管理员密码)
- 用户信息表(用户编号,用户名称,用户密码,用户生日,用户性别,用户性别,用户生日,用户职业,用户爱好,用户帖子数,用户等级,用户注册日期)
- 版块信息表(版块编号,版主编号,版块名称,版块说明,版块点击次数,版块主题数)
- 主贴信息表(主贴编号,主贴板块编号,主贴用户编号,主贴回复次数,主贴标题,主贴内容,主贴时间,主贴回复次数,主贴点击次数)
- 回复贴信息表(回帖编号,回帖主题帖子编号,回帖的用户编号,回帖标题,回帖内容,回帖时间,回帖点击次数)
根据在ER图中的实体,创建库表结构
db.sql
-- 创建数据库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,注音ㄇㄧㄥˊ ㄒㄧㄥ)汉语 词语 ,古书上指金星;旧时指 交际 场中有名的女子;现指在某个领域内有一定影响力的人物。 泛指有名的 演员 、 歌手 、 艺人 、 运动员 等。');
package forum.util;import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import lombok.SneakyThrows;import javax.sql.DataSource;import java.sql.Connection;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(); }}
一般放对象的目录命名为model,根据model目录,其中存放的都是各种对象,例如 User 类、Topic 类、Reply 类。
使用@Data注解在类上时,不用再手动添加get/set等方法了,简化代码
package forum.model;import lombok.Data;// 使用@Data注解在类上时,不用再手动添加get/set等方法了,简化代码@Datapublic 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;}
package forum.model;import lombok.Data;@Datapublic class Section { public Integer sID; public Integer uID; public String sName; public String sStatement; public Integer sClickCount; public Integer sTopicCount;}
package forum.model;import com.fasterxml.jackson.annotation.JSONFormat;import lombok.Data;import java.util.Date;@Datapublic 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;}
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;}
为实现一个搜索功能,新建了一个Keyword实体
package forum.model;import lombok.Data;@Datapublic class Keyword { public String keyword;}
一般放数据库增删查改的目录命名为dao,根据dao目录,其中存放的都是各种数据库增删查改方法类,例如 UserDao 类 , SectionDao 类。
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;// 从数据库查找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); } } }}
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;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(); } } }}
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;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(); } } }}
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;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("插入完成"); } } }}
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;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(); } } }}
先把前端页面拷贝到当前项目
前端页面可见于上一篇博客:《前端项目 - 论坛信息管理系统(纯页面)》
{ "currentUser" : { "uID" : 17, "username" : "张三", "password" : "123", "userSex" : "男", "userAge" : 18, "userOccupation" : "大数据开发工程师", "userHobby" : "敲代码", "userPoint" : 1, "userClass" : 1, "userRegister" : "2022-07-08 15:01", "uid" : 17 }, "pointCount" : 1, "classCount" : 1, "sectionList" : [ { "sID" : 12, "uID" : 1, "sName" : "网易云", "sStatement" : "小时候枕头上全是口水,长大后枕头上全是泪水;小时候微笑是一个心情,长大后微笑是一种表情;小时候哭着哭着就笑了,长大后笑着笑着就哭了。我们终于到了小时候最羡慕的年龄,但却没有成为小时候最想成为的人。——网易云音乐热评《当我已不再那么年轻》", "sClickCount" : 2, "sTopicCount" : 3, "uid" : 1, "sname" : "网易云", "sclickCount" : 2, "sstatement" : "小时候枕头上全是口水,长大后枕头上全是泪水;小时候微笑是一个心情,长大后微笑是一种表情;小时候哭着哭着就笑了,长大后笑着笑着就哭了。我们终于到了小时候最羡慕的年龄,但却没有成为小时候最想成为的人。——网易云音乐热评《当我已不再那么年轻》" "sid" : 12, "stopicCount" : 3 }, ...] "topicList" : null}
package forum.servlet;import com.fasterxml.jackson.databind.ObjectMapper;import forum.dao.KeyDao;import forum.model.Keyword;import forum.model.SectionListResult;import forum.model.User;import forum.service.SectionService;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.Http.httpservlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import java.io.IOException;@WebServlet("/section-list.json")public class SectionListJsonServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { User currentUser = null; Keyword key = null; System.out.println("开始"); HttpSession session = req.getSession(false); if(session != null){ currentUser = (User) session.getAttribute("currentUser"); } String keyword = new KeyDao().selectKey(); SectionService sectionService = new SectionService(); SectionListResult result = null; if(keyword == null) { result = sectionService.sectionListResult(currentUser); System.out.println("执行第一个"); }else { System.out.println(keyword); result = sectionService.sectionListResult(currentUser,keyword); System.out.println("执行第二个"); } ObjectMapper objectMapper = new ObjectMapper(); String jsonString = objectMapper.writeValueAsString(result); resp.setCharacterEncoding("utf-8"); resp.setContentType("application/json"); resp.getWriter().printf(jsonString); }}
// 1. request /json/article_list.json// 2. update dom treefunction renderAuthor(currentUser) { document.querySelector('.author-username').textContent = currentUser.username document.querySelector('.author-userSex').textContent = currentUser.userSex document.querySelector('.author-userAge').textContent = currentUser.userAge document.querySelector('.author-userOccupation').textContent = currentUser.userOccupation document.querySelector('.author-userHobby').textContent = "爱好:" + currentUser.userHobby}function renderCount(pointCount, classCount) { document.querySelector('.author-userPoint').textContent = pointCount document.querySelector('.author-userClass').textContent = classCount}function renderSectionList(sectionList, topicList) { console.log(sectionList) var container = document.querySelector('.section') console.log(container) for (var i in sectionList) { var section = sectionList[i] console.log(section) var html = `` + `` + ` ${section.sName}
` + `版块ID: ${section.sID}` + `${section.sStatement}
` + ` ` + `` + ` 浏览量` + ` ${section.sClickCount}` + `` + `` + `主贴数` + `${section.sTopicCount}` + `` + `` + `` + `查看详情>>` + `` + ` ` container.innerHTML += html }}function renderTopicList(topicList) { console.log(topicList) var container = document.querySelector('.topic') console.log(container) for (var i in topicList) { var topic = topicList[i] console.log(topic) var html = `` + `` + `${topic.tTopic}
` + ` ${topic.tTime}
` + ` 贴主: ${topic.username}` + ` ${topic.tContent}
` + ` ` + ` ` + `浏览量` + ` ${topic.tClickCount}` + `` + `` + `回复` + `${topic.tReplyCount}` + `` + ` ` + `` + `查看回复>>` + `` + ` ` container.innerHTML += html }}var xhr = new XMLHttpRequest()xhr.open('get', '/section-list.json')xhr.onload = function() { //alert(this.responseText); var data = JSON.parse(this.responseText) if(!data.currentUser){ // 重定向 // location = '/login.html' location.assign('/login.html') return } renderAuthor(data.currentUser) renderCount(data.pointCount, data.classCount) renderSectionList(data.sectionList) renderTopicList(data.topicList)}xhr.send()
实现效果:
{ "currentUser" : { "uID" : 17, "username" : "张三", "password" : "123", "userSex" : "男", "userAge" : 18, "userOccupation" : "大数据开发工程师", "userHobby" : "敲代码", "userPoint" : 1, "userClass" : 1, "userRegister" : "2022-07-08 15:01", "uid" : 17 }, "pointCount" : 1, "classCount" : 1, "topicList" : [ { "tID" : 16, "sID" : 12, "uID" : 11, "username" : "管理员", "tTopic" : "窃取天上三分景", "tContent" : "小狐狸想去人间很久了,临行之前,她的姥姥赠她了一件宝物芙蓉面。 小狐狸得了这芙蓉面,欢喜极了,她摸了摸芙蓉面道:“姥姥,我得了芙蓉面也能行走人世吗?” “是啊,只要你戴上芙蓉面,这世上任何人的脸,都可以幻化,可你是妖,就算有了芙蓉面,也得学世人的姿态。” 小狐狸拿着芙蓉面思索半天,", "tTime" : "2022-07-08 13:50", "tReplyCount" : 4, "tClickCount" : 4, "uid" : 11, "treplyCount" : 4, "tclickCount" : 4, "ttime" : "2022-07-08 13:50", "tid" : 16, "tcontent" : "小狐狸想去人间很久了,临行之前,她的姥姥赠她了一件宝物芙蓉面。 小狐狸得了这芙蓉面,欢喜极了,她摸了摸芙蓉面道:“姥姥,我得了芙蓉面也能行走人世吗?” “是啊,只要你戴上芙蓉面,这世上任何人的脸,都可以幻化,可你是妖,就算有了芙蓉面,也得学世人的姿态。” 小狐狸拿着芙蓉面思索半天,", "ttopic" : "窃取天上三分景", "sid" : 12 },...]}
package forum.servlet;import com.fasterxml.jackson.databind.ObjectMapper;import forum.dao.KeyDao;import forum.model.Keyword;import forum.model.SectionListResult;import forum.model.User;import forum.service.SectionService;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import java.io.IOException;@WebServlet("/section-list.json")public class SectionListJsonServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { User currentUser = null; Keyword key = null; System.out.println("开始"); HttpSession session = req.getSession(false); if(session != null){ currentUser = (User) session.getAttribute("currentUser"); } String keyword = new KeyDao().selectKey(); SectionService sectionService = new SectionService(); SectionListResult result = null; if(keyword == null) { result = sectionService.sectionListResult(currentUser); System.out.println("执行第一个"); }else { System.out.println(keyword); result = sectionService.sectionListResult(currentUser,keyword); System.out.println("执行第二个"); } ObjectMapper objectMapper = new ObjectMapper(); String jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result); resp.setCharacterEncoding("utf-8"); resp.setContentType("application/json"); resp.getWriter().printf(jsonString); }}
// 1. request /json/article_list.json// 2. update dom treefunction renderAuthor(currentUser) { document.querySelector('.author-username').textContent = currentUser.username document.querySelector('.author-userSex').textContent = currentUser.userSex document.querySelector('.author-userAge').textContent = currentUser.userAge document.querySelector('.author-userOccupation').textContent = currentUser.userOccupation document.querySelector('.author-userHobby').textContent = "爱好:" + currentUser.userHobby}function renderCount(pointCount, classCount) { document.querySelector('.author-userPoint').textContent = pointCount document.querySelector('.author-userClass').textContent = classCount}function renderTopicList(topicList) { console.log(topicList) var container = document.querySelector('.topic') console.log(container) for (var i in topicList) { var topic = topicList[i] console.log(topic) var html = `` + `` + `${topic.tTopic}
` + ` ${topic.tTime}
` + ` 贴主: ${topic.username}` + ` ${topic.tContent}
` + ` ` + ` ` + `浏览量` + ` ${topic.tClickCount}` + `` + `` + `回复` + `${topic.tReplyCount}` + `` + ` ` + `` + `查看回复>>` + `` + ` ` container.innerHTML += html }}var xhr = new XMLHttpRequest()xhr.open('get', '/topic-list.json' + location.search)xhr.onload = function() { // 打印 json内容 alert(this.responseText); console.log(this.responseText) var data = JSON.parse(this.responseText) if(!data.currentUser){ // 重定向 // location = '/login.html' location.assign('/login.html') return } renderAuthor(data.currentUser) renderCount(data.pointCount, data.classCount) renderTopicList(data.topicList)}xhr.send()
实现效果:
{ "currentUser" : { "uID" : 17, "username" : "张三", "password" : "123", "userSex" : "男", "userAge" : 18, "userOccupation" : "大数据开发工程师", "userHobby" : "敲代码", "userPoint" : 1, "userClass" : 1, "userRegister" : "2022-07-08 15:01", "uid" : 17 }, "pointCount" : 1, "classCount" : 1, "replyList" : [ { "rID" : 39, "tID" : 16, "uID" : 17, "username" : "张三", "rTopic" : "回帖", "rContent" : "hello word", "rTime" : "2022-07-08 15:04", "rClickCount" : 0 }, { "rID" : 32, "tID" : 16, "uID" : 11, "username" : "管理员", "rTopic" : "回帖", "rContent" : "有些人为了上前排连尊严都不要了,您怎么看呢?父亲?", "rTime" : "2022-07-08 13:54", "rClickCount" : 0 }], "tid" : 16, "topic" : { "tID" : 16, "sID" : 12, "uID" : 11, "username" : "管理员", "tTopic" : "窃取天上三分景", "tContent" : "小狐狸想去人间很久了,临行之前,她的姥姥赠她了一件宝物芙蓉面。 小狐狸得了这芙蓉面,欢喜极了,她摸了摸芙蓉面道:“姥姥,我得了芙蓉面也能行走人世吗?” “是啊,只要你戴上芙蓉面,这世上任何人的脸,都可以幻化,可你是妖,就算有了芙蓉面,也得学世人的姿态。” 小狐狸拿着芙蓉面思索半天,", "tTime" : "2022-07-08 13:50", "tReplyCount" : 4, "tClickCount" : 6, "uid" : 11, "treplyCount" : 4, "tclickCount" : 6, "ttime" : "2022-07-08 13:50", "tid" : 16, "tcontent" : "小狐狸想去人间很久了,临行之前,她的姥姥赠她了一件宝物芙蓉面。 小狐狸得了这芙蓉面,欢喜极了,她摸了摸芙蓉面道:“姥姥,我得了芙蓉面也能行走人世吗?” “是啊,只要你戴上芙蓉面,这世上任何人的脸,都可以幻化,可你是妖,就算有了芙蓉面,也得学世人的姿态。” 小狐狸拿着芙蓉面思索半天,", "ttopic" : "窃取天上三分景", "sid" : 12 }}
package forum.servlet;import com.fasterxml.jackson.databind.ObjectMapper;import forum.dao.TopicDao;import forum.model.ReplyListResult;import forum.model.User;import forum.service.ReplyService;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import java.io.IOException;@WebServlet("/reply-list.json")public class ReplyListServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String tidString = req.getParameter("tid"); int tid = Integer.parseInt(tidString); User currentUser = null; HttpSession session = req.getSession(false); if(session != null){ currentUser = (User)session.getAttribute("currentUser"); } // 浏览一次回帖页面,浏览次数加1 new TopicDao().update(tid); ReplyService replyService = new ReplyService(); ReplyListResult result = replyService.replyListResult(currentUser,tid); result.tid = tid; ObjectMapper objectMapper = new ObjectMapper(); String jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result); resp.setCharacterEncoding("utf-8"); resp.setContentType("application/json"); resp.getWriter().println(jsonString); }}
function renderAuthor(currentUser) { document.querySelector('.author-username').textContent = currentUser.username document.querySelector('.author-userSex').textContent = currentUser.userSex document.querySelector('.author-userAge').textContent = currentUser.userAge document.querySelector('.author-userOccupation').textContent = currentUser.userOccupation document.querySelector('.author-userHobby').textContent = "爱好:" + currentUser.userHobby}function renderCount(pointCount, classCount) { document.querySelector('.author-userPoint').textContent = pointCount document.querySelector('.author-userClass').textContent = classCount}function renderDefineTid(tid){ document.querySelector('.tid').value = tid;}function renderReplyList(topic,replyList){ console.log(replyList) var container = document.querySelector('.reply') console.log(container) // 主贴 var html = `` + `` + `${topic.tTopic}
` + ` ${topic.tTime}
` + ` 贴主: ${topic.username}` + ` ${topic.tContent}
` + ` ` + ` ` + `浏览量` + ` ${topic.tClickCount}` + `` + ` ` + ` ` container.innerHTML += html //回帖 for(var i in replyList){ var reply = replyList[i] console.log(reply) var html = `` + `` + `${reply.rTopic}
` + ` ${reply.rTime}
` + ` ${reply.username}` + ` ${reply.rContent}
` + ` ` container.innerHTML += html }}var xhr = new XMLHttpRequest()xhr.open('get','/reply-list.json' + location.search)xhr.onload = function() { //alert(this.responseText); console.log(this.responseText) var data = JSON.parse(this.responseText) if(!data.currentUser){ location.assign('/login.html') return } renderAuthor(data.currentUser) renderCount(data.pointCount, data.classCount) renderReplyList(data.topic,data.replyList) renderDefineTid(data.tid)}xhr.send()
实现效果:
个人中心的json数据来自于/section-list.json
{ "currentUser" : { "uID" : 17, "username" : "张三", "password" : "123", "userSex" : "男", "userAge" : 18, "userOccupation" : "大数据开发工程师", "userHobby" : "敲代码", "userPoint" : 1, "userClass" : 1, "userRegister" : "2022-07-08 15:01", "uid" : 17 }}
个人中心的服务器代码来自于/section-list.json,和列表页服务器代码相同,再次不在赘述。
function renderAuthor(currentUser) { document.querySelector('.author-username').value = currentUser.username document.querySelector('.author-userSex').value = currentUser.userSex document.querySelector('.author-userAge').value = currentUser.userAge document.querySelector('.author-userOccupation').value = currentUser.userOccupation document.querySelector('.author-userHobby').value = currentUser.userHobby document.querySelector('.author-userPoint').value = currentUser.userPoint document.querySelector('.author-userClass').value = currentUser.userClass document.querySelector('.author-userRegister').value = currentUser.userRegister}var xhr = new XMLHttpRequest()xhr.open('get', '/section-list.json')xhr.onload = function() { //alert(this.responseText); var data = JSON.parse(this.responseText) if(!data.currentUser){ // 重定向 // location = '/login.html' location.assign('/login.html') return } renderAuthor(data.currentUser)}xhr.send()
实现效果:
[请求] post /editor.do
package forum.servlet;import forum.dao.TopicDao;import forum.model.User;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import java.io.IOException;@WebServlet("/editor.do")public class EditorDoServlet extends HttpServlet { @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String title = req.getParameter("title"); String sidString = req.getParameter("type"); int sid = Integer.parseInt(sidString); String content = req.getParameter("editor-markdown-doc"); User currentUser = null; HttpSession session = req.getSession(false); if (session != null) { currentUser = (User) session.getAttribute("currentUser"); } if (currentUser == null) { // 说明没有登录 resp.sendRedirect("/login.html"); return; } TopicDao topicDao = new TopicDao(); topicDao.insert(sid,currentUser.uID,title,content); // 重定向到文章详情页 resp.sendRedirect("/detail.html?sid=" + sid); }}
写帖子 来源地址:https://blog.csdn.net/m0_62218217/article/details/125810816
--结束END--
本文标题: 前端+后端项目 - 论坛信息管理系统(Web+servlet+MySQL+JDBC)
本文链接: https://www.lsjlt.com/news/383893.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0