Simple message board implemented by JavaWeb+MySQL

Hello, dear friends, I'm the Bug terminator ~. Unconsciously, I've been on holiday for 2 weeks. I've come to Monday. Today, I decided to update the article. Today, I'll bring you some experience in my learning process and avoid pitfalls for my friends. Let's share today

Message board requirements

The above figure shows the style of the comment page

The comment page is mainly divided into three parts: login status area, comment area (comment and reply operation area), comment list area (display all comments, replies, and page turning controls)

1. Login status area:

   1.1 users can see the list without logging in, but they must log in before they can comment and reply.

   1.2 users can see the comment area without logging in

   1.3 the user can see the reply button without logging in

   1.4 when the user is not logged in, the registration and login hyperlinks are displayed at the top of the comment area. Click to enter the registration and login pages respectively.

   1.5 login status, displayed above the comment area: Welcome XXXX, logout.

   1.6 logout is a hyperlink. Click logout to enter the unregistered state.

2. Comment area:

   2.1 comment status is displayed by default.

   2.2 in the comment status, only textarea control and Comment button are displayed.

   Click the reply button of a comment to switch the status to the reply status for the comment.

   2.3 in the reply status, the following will be displayed above the textarea in the comment area:
   I want to reply to XXX's comment at 19:23, August 9, 2021: I heard 123 again

   2.4 in the reply status, the Comment button changes to reply

   2.5 in the reply status, a button "return to comment status" is displayed on the right side of the Comment button. Click to enter the comment status.

   That is, the comment area has two statuses: comment status and reply status

   2.6 comment status: comments are published, and reply status is to reply to a comment.

   2.7 reply status is for a comment.

3. Comment list:

   Displays a list of all comments.

   Display commenter, comment time and comment content.

   There is a reply button on the right side of each comment. Click reply to enter the reply status for this comment.

   There is a delete button on the right of your own comments in login status

   For other people's comments, the delete button can't be seen on the right.

   If a comment has a reply, a reply list is displayed under the comment.

   Each reply displays the respondent, reply time and reply content.

   In login status, the current login person can see the delete button on the right of his reply record.

   Comments to achieve paging, do not need to query.

See the following effect:

data sheet

User table

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `realname` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Comment form

CREATE TABLE `t_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT 'Commentator id，Changes to the user table id',
  `pl_content` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Content of comments',
  `pl_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Reply Form

CREATE TABLE `t_revert` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pl_id` int(11) NOT NULL COMMENT 'Commentator id，corresponding t_comment.id',
  `user_id` int(11) DEFAULT NULL COMMENT 'Respondent id，Corresponding to the currently logged in account.id',
  `hf_content` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Content of reply',
  `hf_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Reply time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Project structure

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-UvXEmeAN-1628998069593)(C:\Users \ Wang Huicheng \ appdata \ roaming \ typora user images \ image-20210815110929467. PNG)]

Partial code

IndexServlet

@WebServlet("/index")
public class IndexServlet extends HttpServlet{

	public static final Integer pageSize = 5;
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//Gets the current number of pages
		String strPageNumb = req.getParameter("pageNumb");
		Integer pageNumb = 1;
		if (!StringUtils.isEmpty(strPageNumb)) {
			pageNumb = Integer.valueOf(strPageNumb);
		}
		CommentService commentService = new CommentServiceImpl();
		try {
			PageInfo pager = commentService.page(pageNumb, pageSize);
			req.setAttribute("pager", pager);
			req.getRequestDispatcher("/WEB-INF/comment.jsp")
			.forward(req, resp);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}

CommentServlet

@WebServlet("/comment")
public class CommentServlet extends HttpServlet{

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//Get the data, the id of the current commenter and the content of the comment
		String content = req.getParameter("content");
		User user = (User)req.getSession().getAttribute("user");
		Integer userId = user.getId();
		CommentService commentService = new CommentServiceImpl();
		try {
			if (commentService.saveComment(userId, content)) {
				resp.sendRedirect(req.getContextPath()+"/index");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

RevertServlet

@WebServlet("/revert")
public class RevertServlet extends HttpServlet{

	RevertService RevertService = new RevertServiceImpl();
	CommentService CommentService = new CommentServiceImpl();
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//Get the record id of the reply
		String pl_id = req.getParameter("pl_id");
		try {
			Comment comment = CommentService.queryById(Integer.valueOf(pl_id));
			System.out.println("123");
			req.setAttribute("comment", comment);
			req.getRequestDispatcher("/WEB-INF/revert.jsp")
			.forward(req, resp);
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//Get the specific information of the reply
		String plId = req.getParameter("pl_id");
		String liuYan = req.getParameter("liuYan");
		System.out.println(plId);
		User user = (User) req.getSession().getAttribute("user");
		Integer userId = user.getId();
		try {
			if (RevertService.saveRevert(plId, liuYan, userId)) {
				resp.sendRedirect(req.getContextPath()+"/index");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

**Dao layer core code**

Connect database base class

BaseDao

public class BaseDao {

	private static DataSource ds = null;

	public QueryRunner initQueryRunner() throws Exception {
		String dbFile = this.getClass().getClassLoader().getResource("/").getFile();
		dbFile = dbFile.substring(1) + "db.properties";
		
		FileReader fr = new FileReader(dbFile);
		
		Properties pro = new Properties();
		pro.load(fr);
		
//		DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		if (ds == null) {
			ds = DruidDataSourceFactory.createDataSource(pro);
		}
		QueryRunner qur = new QueryRunner(ds);
		System.out.println(ds);
		return qur;
	}
}

CommentDaoImpl

public class CommentDaoImpl extends BaseDao implements CommentDao{

	@Override
	public List<Comment> page(Integer pageNumb, Integer pageSize) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "select * from t_comment order by pl_time desc limit ?, ? ";
		return qur.query(sql, new BeanListHandler<Comment>(Comment.class), (pageNumb - 1) * pageSize, pageSize);
	}

	@Override
	public Comment queryById(Integer id) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "select * from t_comment where id = ?";
		return qur.query(sql, new BeanHandler<Comment>(Comment.class), id);
	}

	@Override
	public Integer delete(String pl_id) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "delete from t_comment where id = ?";
		return qur.update(sql, pl_id);
	}

	@Override
	public Integer saveComment(Integer userId, String content) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "insert into t_comment (user_id, pl_content) values(?, ?)";
		//If the value returned by the update method is greater than 0, it means the increase is successful, and if the value returned is less than 0, it means failure
		return qur.update(sql, userId, content);
	}

	@Override
	public Integer getCount() throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "select count(1) from t_comment";
		Long rowCount = qur.query(sql, new ScalarHandler<Long>());
		return rowCount.intValue();
	}

	
}

RevertDaoImpl

public class RevertDaoImpl extends BaseDao implements RevertDao{

	@Override
	public Integer saveRevert(String plId, String liuYan, Integer userId) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "insert into t_revert (pl_id, user_id, hf_content) values(?, ?, ?)";
		System.out.println(plId);
		return qur.update(sql, plId, userId, liuYan);
	}

	@Override
	public List<Revert> getListByCommentId(Integer pl_id) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "select * from t_revert where pl_id = ?";
		return qur.query(sql, new BeanListHandler<Revert>(Revert.class), pl_id);
	}

	@Override
	public Integer delete(String hfId) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qur = initQueryRunner();
		String sql = "delete from t_revert where id = ?";
		return qur.update(sql, hfId);
	}

}

Catchwords

Today's experience sharing is coming to an end. The code has a long way to go. Some people go forward with you and solve difficulties. In the end, they will turn into your valuable experience. Every Monday, remember the projects worth writing in a week, share the code and share some ideas of doing exercises. Well, share the case this week and see you next week!

I've seen this. Give me a compliment and support the blogger~