使用 Spring + SpringMVC + Hibernat

2018-03-25 本文已影响0人 Winnndy

二、数据库、dao层、pojo类配置

1、数据库配置

本项目的目标是搭建一个简易论坛，基本功能包含注册、登陆、发帖、评论，进阶功能包含收藏和订阅，因此数据库中应当包含如下这些表：

（1）user表（用户表）:

<1>userID(用户ID，INT型，设置为主键，不能为空，自动增长）
<2>password(密码，VARCHAR型，不能为空)
<3>username(用户名，VARCHAR型，不能为空)

（2）note表（帖子表）：

<1>noteID(帖子ID，INT型，设置为主键，不能为空，自动增长)
<2>noteTitle(帖子标题，VARCHAR型，不能为空)
<3>noteContent(帖子内容，VARCHAR型，不能为空)
<4>time(发帖时间，TIMESTAMP型，不能为空)
<5>userID(发帖用户ID，INT型，不能为空)
<6>FK_note_user(与user表的多对一外键，级联删除，禁止修改)

（3）comment表（评论表）：

<1>commentID(评论ID，INT型，设置为主键，不能为空，自动增长)
<2>content(评论内容，VARCHAR型，不能为空)
<3>time(评论时间，TIMESTAMP型，不能为空)
<4>userID(评论用户ID，INT型，不能为空)
<5>noteID(帖子ID，INT型，不能为空)
<6>replyID(所回复的评论ID，INT型）
<7>FK_comment_user(与user表的多对一外键，级联删除，禁止修改)
<8>FK_comment_note(与note表的多对一外键，级联删除，禁止修改)

（4）collection表（收藏表）：

<1>collectionID(收藏ID，INT型，设置为主键，不能为空，自动增长)
<2>userID(收藏用户ID，INT型，不能为空)
<3>noteID(收藏帖子ID，INT型，不能为空)
<4>FK_collection_user(与user表的多对一外键，级联删除，禁止修改)
<5>FK_collection_note(与note表的多对一外键，级联删除，禁止修改)

SQL语句：

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema bbs
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema bbs
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bbs` DEFAULT CHARACTER SET utf8 ;
USE `bbs` ;

-- -----------------------------------------------------
-- Table `bbs`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bbs`.`user` (
  `userID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `password` VARCHAR(10) NOT NULL,
  `username` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`userID`))
ENGINE = InnoDB
AUTO_INCREMENT = 21
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bbs`.`note`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bbs`.`note` (
  `noteID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userID` INT(10) UNSIGNED NOT NULL,
  `noteTitle` VARCHAR(45) NOT NULL,
  `noteContent` VARCHAR(100) NOT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`noteID`),
  INDEX `FK_note_user` (`userID` ASC),
  CONSTRAINT `FK_note_user`
    FOREIGN KEY (`userID`)
    REFERENCES `bbs`.`user` (`userID`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 9
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bbs`.`collection`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bbs`.`collection` (
  `userID` INT(10) UNSIGNED NOT NULL,
  `noteID` INT(10) UNSIGNED NOT NULL,
  `collectionID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`collectionID`),
  INDEX `FK_collection_user_idx` (`userID` ASC),
  INDEX `FK_collection_note_idx` (`noteID` ASC),
  CONSTRAINT `FK_collection_note`
    FOREIGN KEY (`noteID`)
    REFERENCES `bbs`.`note` (`noteID`)
    ON DELETE CASCADE,
  CONSTRAINT `FK_collection_user`
    FOREIGN KEY (`userID`)
    REFERENCES `bbs`.`user` (`userID`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 31
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bbs`.`comment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bbs`.`comment` (
  `commentID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userID` INT(10) UNSIGNED NOT NULL,
  `noteID` INT(10) UNSIGNED NOT NULL,
  `content` VARCHAR(100) NOT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `replyID` INT(10) NULL DEFAULT NULL,
  PRIMARY KEY (`commentID`),
  INDEX `FK_comment_user_idx` (`userID` ASC),
  INDEX `FK_comment_note_idx` (`noteID` ASC),
  CONSTRAINT `FK_comment_note`
    FOREIGN KEY (`noteID`)
    REFERENCES `bbs`.`note` (`noteID`)
    ON DELETE CASCADE,
  CONSTRAINT `FK_comment_user`
    FOREIGN KEY (`userID`)
    REFERENCES `bbs`.`user` (`userID`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 29
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bbs`.`subscription`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bbs`.`subscription` (
  `userID` INT(10) UNSIGNED NOT NULL,
  `subuserID` INT(10) UNSIGNED NOT NULL,
  `subscriptionID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`subscriptionID`),
  INDEX `FK_subscription_user_idx` (`userID` ASC),
  INDEX `FK_subscription_subuser_idx` (`subuserID` ASC),
  CONSTRAINT `FK_subscription_subuser`
    FOREIGN KEY (`subuserID`)
    REFERENCES `bbs`.`user` (`userID`)
    ON DELETE CASCADE,
  CONSTRAINT `FK_subscription_user`
    FOREIGN KEY (`userID`)
    REFERENCES `bbs`.`user` (`userID`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 13
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

2、dao层、pojo类配置

(1)在DB Browse中选择自己要使用的表，右键选择Hibernate Reverse Engineering

(2)按照下图所示进行配置后点击finish生成dao层、pojo类

(3)在applicationContext.xml中名为seesionfactory的bean中添加mappingResources属性，代码如下：

<property name="mappingResources">
    <list>
        <value>com/sxy/pojo/Comment.hbm.xml</value>
        <value>com/sxy/pojo/Note.hbm.xml</value>
        <value>com/sxy/pojo/User.hbm.xml</value>
        <value>com/sxy/pojo/Collection.hbm.xml</value>
        <value>com/sxy/pojo/Subscription.hbm.xml</value>
    </list>
</property>

(4)在每个表的配置文件（.hbm.xml）中对多对一外键（many-to-one）的属性进行如下配置：

lazy="false" fetch="join"

激活外连接查询并关闭延迟加载