Springboot2模块系列:thymeleaf+Bootstrap(二)前后端结合--数据库设计及操作

1 数据库

1.0 数据库结构

CREATE DATABASE `data_repository` DEFAULT CHARACTER SET utf8;
USE `data_repository`;

DROP TABLE IF EXISTS `userinfos`;
CREATE TABLE `userinfos`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) DEFAULT NULL,
    `password` VARCHAR(255) DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `sex` VARCHAR(255) DEFAULT NULL,
    `position` VARCHAR(255) DEFAULT NULL,
    `telephone_num` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2 实体类

package com.company.web.po;

import java.io.Serializable;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@JsonIgnoreProperties(value={"handler"})
public class UserInfos implements Serializable{
    private Integer id;
    private String username;
    private String password;
    private String email;
    private String sex;
    private String position;
    private String telephoneNum;

    public void setId(Integer id){
        this.id = id;
    }

    public Integer getId(){
        return id;
    }
    
    public void setUsername(String username){
        this.username = username;
    }

    public String getUsername(){
        return username;
    }

    public void setPassword(String password){
        this.password = password;
    }

    public String getPassword(){
        return password;
    }

    public void setEmail(String email){
        this.email = email;
    }

    public String getEmail(){
        return email;
    }

    public void setSex(String sex){
        this.sex = sex;
    }

    public String getSex(){
        return sex;
    }

    public void setPosition(String position){
        this.position = position;
    }

    public String getPosition(){
        return position;
    }

    public void setTelephoneNum(String telephoneNum){
        this.telephoneNum = telephoneNum;
    }

    public String getTelephoneNum(){
        return telephoneNum;
    }
}

2 数据库操作

2.1 增删改查

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.company.web.mapper.UserInfosMapper">
    <resultMap id="userInfosMap" type="com.company.web.po.UserInfos">
        <result property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
        <result property="sex" column="sex"/>
        <result property="position" column="position"/>
        <result property="telephoneNum" column="telephone_num"/>
    </resultMap>
    <insert id="addUser" parameterType="map">
        insert into userinfos 
        (username, password, email, sex, position, telephone_num)
        values 
        (#{username}, #{password}, #{email}, #{sex}, #{position}, #{telephoneNum})
    </insert>
    <delete id="deleteUser" parameterType="integer">
        delete from userinfos where id=#{id}
    </delete>
    <update id="editUser" parameterType="map">
        update userinfos set username=#{username}, password=#{password}, email=#{email}, sex=#{sex}, position=#{position}, telephone_num=#{telephoneNum}
        where id=#{id}
    </update>
    <select id="queryUser" parameterType="map" resultMap="userInfosMap">
        select * from userinfos
        <where>
            <if test="username != ''">
                and username like "%"#{username}"%"
            </if>
        </where>
    </select>
    <select id="queryUserWithId" parameterType="integer" resultMap="userInfosMap">
        select * from userinfos where id=#{id}
    </select>
    <select id="loginByEmail" parameterType="string" resultMap="userInfosMap">
        select username, email, password from userinfos where email=#{email}
    </select>
</mapper>