项目实施-02 （后端开发）

前言

本次项目的重点在于用户行为的综合分析，即平台计算处理系统。后端用户系统没有复杂的业务逻辑，只实现简单的CRUD。

整体框架

库表设计

本次项目采用 MySQL数据库

DROP TABLE IF EXISTS t_user;
set character_set_results=utf8;
set character_set_client=utf8;
CREATE TABLE t_user  (
  id int primary key AUTO_INCREMENT,
  name varchar(32) unique ,
  password varchar(128) ,
  sex tinyint(1) ,
  photo varchar(255) ,
  birthDay date,
  email varchar(128)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

入口类

package com.baizhi;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.baizhi.dao")
public class UsermodelApplication {
   

    public static void main(String[] args) {
   
        SpringApplication.run(UsermodelApplication.class, args);
    }

}

实体类

引入lombok技术

package com.baizhi.entities;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class User implements Serializable {
   
    private Integer id; //数据库自动递增
    private String name;
    private String password;
    private boolean sex;//false-女 ; true-男
    private Date birthDay;
    private String photo;//头像
    private String email;

}

dao接口

package com.baizhi.dao;

import com.baizhi.entities.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface IUserDAO {
   
    //------------------增
    //提供一个增添用户的方法（注册）
    void saveUser(User user);

    //------------------查
    //提供一个按照 用户名 和 密码 查询的方法（登录）
    User queryUserByNameAndPassword(User user);
    //提供一个按照用户ID查询的方法
    User queryUserById(Integer id);
    //提供一个按照关键字分页查询的方法
    List<User> queryUserByPage(
            @Param(value = "pageNow") Integer pageNow,
            @Param(value = "pageSize") Integer pageSize,
            @Param(value = "column") String column,
            @Param(value = "value") Object value);
    //提供一个按照关键字查询用户总数目的方法
    int queryCount(
            @Param(value = "column") String column,
            @Param(value = "value") Object value);

    //------------------改
    //提供一个更新用户信息的方法
    void updateUser(User user);

    //------------------删
    //提供一个删除用户的方法
    void deleteByUserId(Integer id);
}

Mapper文件

<?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.baizhi.dao.IUserDAO">
    <!--开启二级缓存-->
    <cache></cache>

    <!--公用sql文件的字段提取-->
    <sql id="defined01" >
        id,name,password,sex,photo,birthDay,email
    </sql>

    <!-- 增 -->
    <insert id="saveUser" parameterType="User">
        insert into t_user(id,name,password,sex,photo,birthDay,email)
            values (#{id},#{name},#{password},#{sex},#{photo,jdbcType=VARCHAR},#{birthDay},#{email})
    </insert>

    <!-- 删 -->
    <delete id="deleteByUserId">
        delete from t_user where id=#{id}
    </delete>

    <!-- 改 -->
    <update id="updateUser" parameterType="User">
        update t_user
        <set>
            <if test="name!=null and name!=''">
                name =#{name},
            </if>
            <if test="password!=null and password!=''">
                password =#{password},
            </if>
            <if test="sex!=null and sex!=''">
                sex =#{sex},
            </if>
            <if test="birthDay!=null">
                birthDay =#{birthDay},
            </if>
            <if test="photo!=''">
                photo =#{photo},
            </if>
            <if test="email!=null and email!=''">
                email =#{email},
            </if>
        </set>
        where id =#{id}
    </update>

    <!-- 查 -->
    <!-- 按照ID查一个 -->
    <select id="queryUserById" resultType="User">
        select <include refid="defined01"/> from t_user where id=#{id}
    </select>
    <!--按照用户名和密码查-->
    <select id="queryUserByNameAndPassword" parameterType="User" resultType="User">
        select <include refid="defined01"/> from t_user where name=#{name} and password=#{password}
    </select>
    <!-- 按照关键字分页查 -->
    <select id="queryUserByPage" resultType="User">
        select <include refid="defined01"/> from t_user
        <where>
            <if test="'name'==column">
               and name like  concat(concat('%',#{value}),'%')
            </if>
            <if test="'password'==column">
                and password like concat(concat('%',#{value}),'%')
            </if>
            <if test="'sex'==column">
                and sex = #{value}
            </if>
            <if test="'birthDay'==column">
                and birthDay = #{value}
            </if>
            <if test="'photo'==column">
                and photo like  concat(concat('%',#{value}),'%')
            </if>
            <if test="'email'==column">
                and email like concat(concat('%',#{value}),'%')
            </if>
        </where>
          limit ${(pageNow-1)*pageSize},#{pageSize}
    </select>
    <!-- 按照关键字查询用户总数目 -->
    <select id="queryCount" resultType="int">
        select count(*) from t_user
        <where>
            <if test="'name'==column">
                and name = #{value}
            </if>
            <if test="'password'==column">
                and password = #{value}
            </if>
            <if test="'sex'==column">
                and sex = #{value}
            </if>
            <if test="'birthDay'==column">
                and birthDay = #{value}
            </if>
            <if test="'photo'==column">
                and photo = #{value}
            </if>
            <if test="'email'==column">
                and email = #{value}
            </if>
        </where>
    </select>

</mapper>

补充

动态 SQL
foreach 标签用法：
接口：

List<User> selectMany(@Param("ids") List<Integer> ids);

对应的SQL语句：

<!--查多个-->
<select id="selectMany" resultType="User">
	select <include refid="defined01"/> from t_user where id in
		<foreach collection="ids" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
</select>

业务层

接口

package com.baizhi.servoce;

import com.baizhi.entities.User;