Spring Boot + Nuxt + Debian（五）：spring boot + mybatis + mysql业务逻辑

前言

本文记录后端的业务流程

一、本项目持久层框架为mybatis（version：1.3.5）

        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>${mybatis-generator.version}</version>
        </dependency>

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="D:\software\SoftEnviron\apache-maven-3.6.1\maven-repository\mysql\mysql-connector-java\8.0.17\mysql-connector-java-8.0.17.jar"/>
    <context id="context">
        <commentGenerator>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3308/evaluate?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=GMT"
                        userId="root" password="root">
            <!-- 加入此注解保证只生成自己需要的User类 -->
            <property name="nullCatalogMeansCurrent" value="true"/>
        </jdbcConnection>
        <!-- 类型转换 -->
        <javaTypeResolver>
            <!--默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer true，把JDBC DECIMAL和 NUMERIC 类型解析为java.math.BigDecimal -->
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <javaModelGenerator targetPackage="com.zq.evaluate.entity" targetProject="MAVEN"/>
        <sqlMapGenerator targetPackage="com.zq.evaluate.entity" targetProject="MAVEN"/>
        <javaClientGenerator targetPackage="com.zq.evaluate.repository" targetProject="MAVEN" type="XMLMAPPER"/>
        <table tableName="user" domainObjectName="UserEntity" mapperName="UserRepository" schema="evaluate">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
        <table tableName="permission" domainObjectName="PermissionEntity" mapperName="PermissionRepository" schema="evaluate">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
        <table tableName="role" domainObjectName="RoleEntity" mapperName="RoleRepository" schema="evaluate">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
    </context>
</generatorConfiguration>

二、数据库为MySQL，贴几条我用到的命令

①mysql唯一约束性

查看：

SHOW KEYS FROM 数据表;

增加 ：

ALTER TABLE 数据表 ADD UNIQUE(字段名); 

删除 ：

ALTER TABLE 数据表 DROP IND;

②某字段等于行数

SET @num=0;
UPDATE 数据表 SET 字段=(@num:=@num+1)

③给自增ID重设起始值

ALTER TABLE 数据表 auto_increment = 1;

注：oracle设置自增

①oracle序列查询

select * from user_sequences where sequence_name = 'SEQ_数据表';

②创建序列，索引

create sequence SEQ_数据表
minvalue 100000
maxvalue 999999
start with 100000
increment by 1
cache 2  --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---NOCACHE防止跳号
cycle;  --循环,当达到最大值时,不是从start with设置的值开始循环。而是从1开始循环

③使用

INSERT INTO 数据表(ID) VALUES(SEQ_数据表.NEXTVAL);

三、表结构设计及项目配置完成，执行mybatis-generator:generate

1.xml文件位置：在repository/src/main/resources下新建mybatis文件夹

文件夹	作用：修改表结构后只需覆盖generated中的xml即可
generated	存放自动生成的xml文件
manual	存放自己编写的xml文件

2.entity及entityExample放到java/com/././entity下，无需更改

四.数据访问层

1.重复型管理——泛型

/**
 * @author **
 * @date Dec. 31, 2019
 * @description mybatis基础接口，所有repository接口均继承该接口
 */
public interface MybatisBaseRepository<T, PK extends Serializable, E> {
    long countByExample(E example);

    int deleteByExample(E example);

    int deleteByPrimaryKey(PK id);

    int insert(T record);

    int insertSelective(T record);

    List<T> selectByExample(E example);

    T selectByPrimaryKey(PK id);

    int updateByExampleSelective(@Param("record") T record, @Param("example") E example);

    int updateByExample(@Param("record") T record, @Param("example") E example);

    int updateByPrimaryKeySelective(T record);

    int updateByPrimaryKey(T record);

    int logicalDeletion(String code);

    int batchLogicalDeletion(@Param("codeList") List<String> codeList);

    int batchDelete(@Param("codeList") List<String> codeList);
}

2.CollegeRepository 对应4.3

@Repository
public interface CollegeRepository extends MybatisBaseRepository<CollegeEntity, Integer, CollegeEntityExample> {
    int batchInsert(@Param("insertList") List<CollegeBO> collegeBOList);

    List<CollegeBO> selectByCondition(@Param("collegeBO") CollegeBO collegeBO);
}

3.CollegeRepository.xml对应4.2

<?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.zq.evaluate.repository.CollegeRepository">
    <update id="logicalDeletion">
        UPDATE college
            SET STATUS = ABS( STATUS - 1 )
        WHERE
            code = #{code}
    </update>
    <update id="batchLogicalDeletion" parameterType="list">
        UPDATE college
        SET STATUS = ABS( STATUS -1 )
        WHERE
        <foreach collection="codeList" index="index" item="cl" separator="or">
            code = #{cl}
        </foreach>
    </update>
    <delete id="batchDelete" parameterType="list">
        DELETE FROM college WHERE
        <foreach collection="codeList" index="index" item="cl" separator="or">
            code = #{cl}
        </foreach>
    </delete>
    <insert id="batchInsert" parameterType="list">
        INSERT INTO college ( code, name )
        VALUES
        <foreach collection="insertList" index="index" item="sbl" separator=",">
            ( #{sbl.code}, #{sbl.name} )
        </foreach>
    </insert>
    <select id="selectByCondition" resultType="com.zq.evaluate.common.bo.college.CollegeBO">
        SELECT
        *
        FROM
        college
        <where>
            <if test="collegeBO!=null">
                <if test="collegeBO.code!=null and collegeBO.code!=''">
                    AND code = #{collegeBO.code}
                </if>
                <if test="collegeBO.name!=null and collegeBO.name!=''">
                    AND name like CONCAT('%',#{collegeBO.name},'%')
                </if>
            </if>
        </where>
    </select>
</mapper>

五、业务层(小型快速开发故没有使用serviceImpl)

/**
 * @author **
 * @date Jan. 03, 2020
 * @description 学院业务层
 */
@Service
public class CollegeService extends CrudBaseService<CollegeRepository, CollegeEntity, Integer, CollegeEntityExample> {
    /**
     * @return int
     * @author **
     * @date Jan. 06, 2020
     * @Param collegeBOList
     * @description 批量添加
     */
    public int batchInsert(List<CollegeBO> collegeBOList) {
        return dao.batchInsert(collegeBOList);
    }

    /**
     * @return list
     * @author **
     * @date Jan. 07, 2020
     * @Param collegeBO
     * @description 根据条件查询学院
     */
    public List<CollegeBO> select(CollegeBO collegeBO) {
        logger.info("查询学院");
        return dao.selectByCondition(collegeBO);
    }
}

六、总结