数据库设计:
Blog表(博客属性):
- 标题、内容、首图、标记、浏览次数、赞赏开启、版权开启、评论开启、是否发布、创建时间、更新时间、描述
Type表(分类属性):
- 分类名称
User表(用户属性):
- 昵称、用户名、密码、邮箱、类型、头像、创建时间、更新时间
Comment表(评论属性):
- 昵称、邮箱、头像、评论内容、创建时间、博客id、父评论id、管理员id
Friend表(留言属性):
- 昵称、邮箱、头像、留言内容、创建时间、父留言id、管理员id
Message表(友链属性):
- 网址、名称、创建时间、图片地址
Picture表(相册属性):
- 图片地址、图片描述、图片名称、拍摄时间地点
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;
技术栈:
1.前端
- JS框架:JQuery
- CSS框架:Semantic UI官网
- Markdown编辑器:编辑器 Markdown
- 代码高亮:代码高亮 prism
- 动画效果:动画 animate.css
- 文章目录:目录生成 Tocbot
- 音乐盒:zplayer
- 照片墙:lightbox插件
2.后端
- 核心框架:SpringBoot 2.2.5
- 项目构建:jdk1.8、Maven 3
- 持久层框架:Mybatis
- 模板框架:Thymeleaf
- 分页插件:PageHelper
- 加密:MD5加密
- 运行环境:Linux(Docker)
3.数据库
- MySQL 5.7
二、功能需求
因为是个人博客,所以没有做用户权限管理,只是简单的区分了一下普通用户和管理员用户,这里就根据普通用户和管理员用户来讲述功能需求,其实从上一篇博文的前端页面就能大致的看出需求了
1.普通用户
- 查看文章信息:文章列表、推荐文章、文章标题、文章内容、发布时间、访问量以及评论等信息
- 查看分类文章:分类列表、分类文章信息
- 查看时间轴:按照文章时间发布顺序查看文章
- 搜索文章:导航栏右边搜索框根据关键字搜索
- 听音乐:上一曲、下一曲、音量控制、播放顺序控制、查看歌词等
- 留言:留言并回复
- 查看友链:查看并访问博主在友链页面添加的友链连接
- 查看相册信息:相册列表、照片名称、照片拍摄地点、时间、照片描述
2.管理员用户(栈主)
- 拥有普通用户所有功能权限
- 登录:在主页路径下加“/admin”,可进入登录页面,根据数据库的用户名和密码进行登录
- 文章管理:查询文章列表、新增文章、编辑文章、删除文章、搜索文章
- 分类管理:查询分类列表、新增分类、编辑分类、删除分类
- 友链管理:查询友链列表、新增友链、编辑友链、删除友链
- 相册管理:查询相册列表、新增照片、编辑照片、删除照片
- 消息管理:登录后恢复评论留言会显示栈主的头像信息,并能显示删除消息按键,可以对消息进行删除
1.数据表
- 博客数据表:t_blog
- 分类数据表:t_type
- 用户数据表:t_user
- 评论数据表:t_comment
- 留言数据表:t_message
- 友链数据表:t_friend
- 相册数据表:t_picture
2.实体关系
- 博客和分类是多对一的关系:一个博客对应一个分类,一个分类可以对应多个博客
- 博客和用户是多对一的关系:一个博客对应一个用户,一个用户可以对应多个博客
- 博客和评论是一对多的关系:一个博客可以对应多个评论,一个评论对应一个博客
- 评论和回复是一对多的关系:一个评论可以对应多个回复,一个回复对应一个评论
- 留言和评论是一样的,还有友链和相册数据表和其他表没有关联