【SpringBoot搭建个人博客】- 技术需求（二）

刺骨的言语ヽ痛彻心扉 2021-09-21 07:10 325阅读 0赞

> 博客地址：[https://onestar.newstar.net.cn/][https_onestar.newstar.net.cn]
> 
> 源码领取方式一：
> 
>  *  扫一扫文末二维码，关注公众号，后台回复【博客】，即可领取源码
> 
> 源码领取方式二：
> 
>  *  前端页面源码地址：[https://github.com/oneStarLR/myblog-page][https_github.com_oneStarLR_myblog-page]
>  *  以jpa为持久层源码地址：[https://github.com/oneStarLR/myblog-jpa][https_github.com_oneStarLR_myblog-jpa]
>  *  以mybatis为持久层源码地址：[https://github.com/oneStarLR/myblog-mybaits][https_github.com_oneStarLR_myblog-mybaits]
>  *  欢迎给star以鼓励(^\_−)☆

本文将从技术栈、功能需求、数据库设计来讲述，前端页面展示可以看我上一篇文章[：【SpringBoot搭建个人博客】- 前端页面展示（一）][SpringBoot_-]

### 一、技术栈 ###

1.前端

*  JS框架：JQuery
 *  CSS框架：[Semantic UI官网][Semantic UI]
 *  Markdown编辑器：[编辑器 Markdown][Markdown]
 *  代码高亮：[代码高亮 prism][prism]
 *  动画效果：[动画 animate.css][animate.css]
 *  文章目录：[目录生成 Tocbot][Tocbot]
 *  音乐盒[：zplayer][zplayer]
 *  照片墙[：lightbox插件][lightbox]

2.后端

*  核心框架：SpringBoot 2.2.5
 *  项目构建：jdk1.8、Maven 3
 *  持久层框架：Mybatis
 *  模板框架：Thymeleaf
 *  分页插件：PageHelper
 *  加密：MD5加密
 *  运行环境：腾讯云Centos7

3.数据库

*  MySQL 5.7

### 二、功能需求 ###

因为是个人博客，所以没有做用户权限管理，只是简单的区分了一下普通用户和管理员用户，这里就根据普通用户和管理员用户来讲述功能需求，其实从上一篇博文的前端页面就能大致的看出需求了

1.普通用户

*  查看文章信息：文章列表、推荐文章、文章标题、文章内容、发布时间、访问量以及评论等信息
 *  查看分类文章：分类列表、分类文章信息
 *  查看时间轴：按照文章时间发布顺序查看文章
 *  搜索文章：导航栏右边搜索框根据关键字搜索
 *  听音乐：上一曲、下一曲、音量控制、播放顺序控制、查看歌词等
 *  留言：留言并回复
 *  查看友链：查看并访问博主在友链页面添加的友链连接
 *  查看相册信息：相册列表、照片名称、照片拍摄地点、时间、照片描述

2.管理员用户（栈主）

*  拥有普通用户所有功能权限
 *  登录：在主页路径下加“/admin”，可进入登录页面，根据数据库的用户名和密码进行登录
 *  文章管理：查询文章列表、新增文章、编辑文章、删除文章、搜索文章
 *  分类管理：查询分类列表、新增分类、编辑分类、删除分类
 *  友链管理：查询友链列表、新增友链、编辑友链、删除友链
 *  相册管理：查询相册列表、新增照片、编辑照片、删除照片
 *  消息管理：登录后恢复评论留言会显示栈主的头像信息，并能显示删除消息按键，可以对消息进行删除

### 三、数据库设计 ###

> 由于博主最开始是使用jpa作为持久层开发此博客的，数据表是由jpa框架自动生成的，在使用mybatis为持久层的时候就沿用了jpa生成的数据库，但是对评论表和留言表进行了改动，如果同样是先用jpa，再用mybatis开发的伙伴这里要注意一下，如果直接使用mybatis开发的则可以忽略

1.数据表

*  博客数据表：t\_blog
 *  分类数据表：t\_type
 *  用户数据表：t\_user
 *  评论数据表：t\_comment
 *  留言数据表：t\_message
 *  友链数据表：t\_friend
 *  相册数据表：t\_picture

2.实体关系

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70][]

*  博客和分类是多对一的关系：一个博客对应一个分类，一个分类可以对应多个博客
 *  博客和用户是多对一的关系：一个博客对应一个用户，一个用户可以对应多个博客
 *  博客和评论是一对多的关系：一个博客可以对应多个评论，一个评论对应一个博客
 *  评论和回复是一对多的关系：一个评论可以对应多个回复，一个回复对应一个评论

> 留言和评论是一样的，还有友链和相册数据表和其他表没有关联

3.实体属性

博客属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 1][]

分类属性：

![2020050111462720.jpeg][]

用户属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 2][]

评论属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 3][]

留言属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 4][]

友链属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 5][]

相册属性：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 6][]

*  博客属性：标题、内容、首图、标记、浏览次数、赞赏开启、版权开启、评论开启、是否发布、创建时间、更新时间、描述
 *  分类属性：分类名称
 *  用户属性：昵称、用户名、密码、邮箱、类型、头像、创建时间、更新时间
 *  评论属性：昵称、邮箱、头像、评论内容、创建时间、博客id、父评论id、管理员id
 *  留言属性：昵称、邮箱、头像、留言内容、创建时间、父留言id、管理员id
 *  友链属性：网址、名称、创建时间、图片地址
 *  相册属性：图片地址、图片描述、图片名称、拍摄时间地点

4.表结构

博客表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 7][]

分类表：

![20200501000131605.jpeg][]

用户表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 8][]

评论表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 9][]

留言表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 10][]

友链表：

![20200501000132677.jpeg][]

相册表：

![20200501000132676.jpeg][]

4.建表语句

/*
     Navicat MySQL Data Transfer
    
     Source Server         : myblog-localhost
     Source Server Type    : MySQL
     Source Server Version : 50717
     Source Host           : localhost:3306
     Source Schema         : myblog
    
     Target Server Type    : MySQL
     Target Server Version : 50717
     File Encoding         : 65001
    
     Date: 30/04/2020 17:02:10
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_blog
    -- ----------------------------
    DROP TABLE IF EXISTS `t_blog`;
    CREATE TABLE `t_blog`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `appreciation` bit(1) NOT NULL,
      `commentabled` bit(1) NOT NULL,
      `content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
      `create_time` datetime(0) NULL DEFAULT NULL,
      `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `first_picture` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `published` bit(1) NOT NULL,
      `recommend` bit(1) NOT NULL,
      `share_statement` bit(1) NOT NULL,
      `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `update_time` datetime(0) NULL DEFAULT NULL,
      `views` int(11) NULL DEFAULT NULL,
      `type_id` bigint(20) NULL DEFAULT NULL,
      `user_id` bigint(20) NULL DEFAULT NULL,
      `comment_count` int(255) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `FK292449gwg5yf7ocdlmswv9w4j`(`type_id`) USING BTREE,
      INDEX `FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`) USING BTREE,
      CONSTRAINT `FK292449gwg5yf7ocdlmswv9w4j` FOREIGN KEY (`type_id`) REFERENCES `t_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `FK8ky5rrsxh01nkhctmo7d48p82` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB AUTO_INCREMENT = 62 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_comment
    -- ----------------------------
    DROP TABLE IF EXISTS `t_comment`;
    CREATE TABLE `t_comment`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `create_time` datetime(0) NULL DEFAULT NULL,
      `blog_id` bigint(20) NULL DEFAULT NULL,
      `parent_comment_id` bigint(20) NULL DEFAULT NULL,
      `admin_comment` bit(1) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 28 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_friend
    -- ----------------------------
    DROP TABLE IF EXISTS `t_friend`;
    CREATE TABLE `t_friend`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `blogaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `blogname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `create_time` datetime(0) NULL DEFAULT NULL,
      `pictureaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_message
    -- ----------------------------
    DROP TABLE IF EXISTS `t_message`;
    CREATE TABLE `t_message`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `create_time` datetime(0) NULL DEFAULT NULL,
      `parent_message_id` bigint(20) NULL DEFAULT NULL,
      `admin_message` bit(1) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 100 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_picture
    -- ----------------------------
    DROP TABLE IF EXISTS `t_picture`;
    CREATE TABLE `t_picture`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `pictureaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `picturedescription` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `picturename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `picturetime` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_type
    -- ----------------------------
    DROP TABLE IF EXISTS `t_type`;
    CREATE TABLE `t_type`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for t_user
    -- ----------------------------
    DROP TABLE IF EXISTS `t_user`;
    CREATE TABLE `t_user`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `create_time` datetime(0) NULL DEFAULT NULL,
      `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `type` int(11) NULL DEFAULT NULL,
      `update_time` datetime(0) NULL DEFAULT NULL,
      `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    SET FOREIGN_KEY_CHECKS = 1;

下一篇将讲述后端框架的搭建

【点关注，不迷路，欢迎关注本站】

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 11][]

[https_onestar.newstar.net.cn]: https://onestar.newstar.net.cn/
[https_github.com_oneStarLR_myblog-page]: https://github.com/oneStarLR/myblog-page
[https_github.com_oneStarLR_myblog-jpa]: https://github.com/oneStarLR/myblog-jpa
[https_github.com_oneStarLR_myblog-mybaits]: https://github.com/oneStarLR/myblog-mybaits
[SpringBoot_-]: https://onestar.newstar.net.cn/blog/37
[Semantic UI]: https://semantic-ui.com/
[Markdown]: https://pandao.github.io/editor.md/
[prism]: https://github.com/PrismJS/prism
[animate.css]: https://daneden.github.io/animate.css/
[Tocbot]: https://tscanlin.github.io/tocbot/
[zplayer]: https://gitee.com/supperzh/zplayer
[lightbox]: https://github.com/JavaScript-Kit/jkresponsivegallery
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70]: /images/20210920/cf256a4c6c3d491dad7a04007a162f8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 1]: /images/20210920/3708d4297ef94e2dbeb63250246b6804.png
[2020050111462720.jpeg]: /images/20210920/d04c146eb05e49ddbcdab9deeca4ba15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 2]: /images/20210920/e91985b7b84441aaba04aac78f278e08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 3]: /images/20210920/9a9292416b174c91902ed27a0b8aa3c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 4]: /images/20210920/5cdef2e260a24a35a68459411ab02d4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 5]: /images/20210920/cf63b5ce99f740b48424faa7e9b08fb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 6]: /images/20210920/4a0f47027aad4f4ab63cba26abe70186.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 7]: /images/20210920/b40a6eb6d89c4518b63a01c1cebab582.png
[20200501000131605.jpeg]: /images/20210920/56d5e69e7f8948fda8fd19dcbde777e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 8]: /images/20210920/26b88bcb611c4b1484814626292043c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 9]: /images/20210920/2d4ff32a42f84cafa5aa77cf80102013.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 10]: /images/20210920/a59992349db84ae0affb6be31fc1ed98.png
[20200501000132677.jpeg]: /images/20210920/e2e5c711b4844759a7934acaf1c433c4.png
[20200501000132676.jpeg]: /images/20210920/13b8f0f1be6f4660996fb04a45d3b114.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L09uZV9MX1N0YXI_size_16_color_FFFFFF_t_70 11]: /images/20210920/b9d3bdd86b4e4c0b9748cf44dc33ff3d.png