SSM搭建博客系统（三）：数据库设计

淩亂°似流年 2022-03-12 12:58 152阅读 0赞

# 一、设计思想 #

项目中计划实现的功能主要包括以下几点：

*  用户注册；
 *  用户的登录和退出；
 *  用户账号登录和注册时需要输入验证码验证；
 *  首页展示及分页，主要展示文章内容，可进行搜索，将搜索结果高亮显示；
 *  首页文章的点赞、踩和评论功能；
 *  个人主页模块，包括个人的基本信息，文章分类，发布文章，管理文章以及热门文章推荐等；
 *  书写文章功能；
 *  文章管理功能，包括文章的查看、修改和删除；
 *  个人信息修改功能；
 *  安全框架 spring-security 的整合，对不符合条件的用户或者 URL 进行拦截；
 *  记录用户登录信息，包括登录的时间、IP 等；
 *

# 二、表结构设计 #

创建数据表

/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50505 Source Host : localhost:3306 Source Database : blog Target Server Type : MYSQL Target Server Version : 50505 File Encoding : 65001 Date: 2019-03-04 22:08:43 */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for comments
    -- ----------------------------
    DROP TABLE IF EXISTS `comments`;
    CREATE TABLE `comments` (
      `com_id` bigint(20) NOT NULL,
      `con_id` bigint(20) DEFAULT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      `com_text` text COLLATE utf8_bin,
      `com_time` datetime DEFAULT NULL,
      `com_subid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `com_upvote` int(100) DEFAULT NULL,
      PRIMARY KEY (`com_id`),
      KEY `FK_COMMENTS_REFERENCE_USER_CON` (`con_id`),
      CONSTRAINT `FK_COMMENTS_REFERENCE_USER_CON` FOREIGN KEY (`con_id`) REFERENCES `user_content` (`con_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for resource
    -- ----------------------------
    DROP TABLE IF EXISTS `resource`;
    CREATE TABLE `resource` (
      `res_id` bigint(20) NOT NULL,
      `res_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `res_url` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `res_enable` tinyint(1) DEFAULT NULL,
      `res_comment` varchar(100) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`res_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for role
    -- ----------------------------
    DROP TABLE IF EXISTS `role`;
    CREATE TABLE `role` (
      `role_id` bigint(20) NOT NULL,
      `role_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `role_value` char(11) COLLATE utf8_bin DEFAULT NULL,
      `role_url` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `role_enab` tinyint(1) DEFAULT NULL,
      `role_note` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`role_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for role_resource
    -- ----------------------------
    DROP TABLE IF EXISTS `role_resource`;
    CREATE TABLE `role_resource` (
      `rr_id` bigint(20) NOT NULL,
      `role_id` bigint(20) NOT NULL,
      `res_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`rr_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for role_user
    -- ----------------------------
    DROP TABLE IF EXISTS `role_user`;
    CREATE TABLE `role_user` (
      `ru_id` bigint(20) NOT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      `role_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`ru_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for upvote
    -- ----------------------------
    DROP TABLE IF EXISTS `upvote`;
    CREATE TABLE `upvote` (
      `upu_id` bigint(20) NOT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      `user_ip` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `upv_state` tinyint(1) DEFAULT NULL,
      `upv_time` date DEFAULT NULL,
      PRIMARY KEY (`upu_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `user_id` bigint(20) NOT NULL,
      `user_email` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `user_password` varchar(225) COLLATE utf8_bin DEFAULT NULL,
      `user_phone` char(11) COLLATE utf8_bin DEFAULT NULL,
      `user_nickname` varchar(225) COLLATE utf8_bin DEFAULT NULL,
      `user_state` tinyint(1) DEFAULT NULL,
      `user_img` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `user_enable` tinyint(1) DEFAULT NULL,
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for user_content
    -- ----------------------------
    DROP TABLE IF EXISTS `user_content`;
    CREATE TABLE `user_content` (
      `con_id` bigint(20) NOT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      `con_title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `con_cate` varchar(100) COLLATE utf8_bin DEFAULT NULL,
      `con_content` text COLLATE utf8_bin,
      `con_privacy` tinyint(1) DEFAULT NULL,
      `con_time` datetime DEFAULT NULL,
      `user_img` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`con_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- ----------------------------
    -- Table structure for user_info
    -- ----------------------------
    DROP TABLE IF EXISTS `user_info`;
    CREATE TABLE `user_info` (
      `ui_id` bigint(20) NOT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      `user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `user_sex` tinyint(1) DEFAULT NULL,
      `user_brithday` date DEFAULT NULL,
      `user_hobby` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `user_address` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`ui_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    复制代码

# 三、反向生成实体类 #

## 1. 准备好生成工具包 ##

## 2. 配置生成文件 ##

generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>    
        <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">    
        <generatorConfiguration>    
          
        <classPathEntry location="mysql-connector-java-5.1.25-bin.jar"/>    
        <context id="DB2Tables" targetRuntime="MyBatis3">    
            <commentGenerator>    
                <property name="suppressDate" value="true"/>    
    
                <property name="suppressAllComments" value="true"/>    
            </commentGenerator>    
              
            <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/blog" userId="root" password="root">    
            </jdbcConnection>    
            <javaTypeResolver>    
                <property name="forceBigDecimals" value="false"/>    
            </javaTypeResolver>    
              
            <javaModelGenerator targetPackage="com.blog.entity" targetProject="src">    
                <property name="enableSubPackages" value="true"/>    
                <property name="trimStrings" value="true"/>    
            </javaModelGenerator>    
             
           <sqlMapGenerator targetPackage="com.blog.mapping" targetProject="src">    
                <property name="enableSubPackages" value="true"/>    
            </sqlMapGenerator>    
                 
            <javaClientGenerator type="XMLMAPPER" targetPackage="com.blog.dao" targetProject="src">    
                <property name="enableSubPackages" value="true"/>    
            </javaClientGenerator>    
    
            
            <table tableName="user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="user_info" domainObjectName="UserInfo" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="resource" domainObjectName="Resource" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="user_content" domainObjectName="UserContent" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="comments" domainObjectName="Comments" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="login_log" domainObjectName="LoginLog" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="role" domainObjectName="Role" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="role_resource" domainObjectName="RoleResource" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="role_user" domainObjectName="RoleUser" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="upvote" domainObjectName="Upvote" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        </context>    
    </generatorConfiguration>  
    复制代码

## 3. 在该文件中打开cmd，输入生成命令 ##

java -jar mybatis-generator-core-1.3.2.jar -configfile generatorConfig.xml -overwrite
    复制代码

## 4. 将生成结果拷贝到项目文件夹中 ##