MyBatis 持久层

一、#和$的区别

$接收的参数不经过处理直接拼接到SQL上，属于一种SQL注入，安全性较低，容易被成为攻击数据库的方式，常用于构建动态表名、动态列名、动态条件。#接收的参数会经过处理后转化成字符串，安全性高，常用于接收前端传到后端的SQL搜索条件。

二、动态SQL

新增操作,使用主键回填,回填到数据模型里的id属性
<insert id="insert" useGeneratedKeys="true" keyProperty="id"> 
    insert into system_user 
    <trim prefix="(" prefixOverrides="." suffix=")" suffixOverrides=",">
      .name, -> 添加前缀'(',删除前缀'.',添加后缀')',删除后缀','
    </trim>
    values (#{name})
</insert>
trim里的原始内容: .name,
prefix:给原始内容添加前缀'(' -> (.name,
prefixOverrides:给原始内容删除前缀'.' -> (name,
suffixOverrides:给原始内容删除后缀',' -> (name
suffix:给原始内容添加后缀')' -> (name)
prefixOverrides\suffixOverrides="AND|OR"; -> 删除可能性的前缀

删除操作
<delete id="delete" parameterType="com.wjx.SystemUser">
    delete from system_user where id = #{id}
</delete>

更新操作
<update id="update" parameterType="">
    update system_user
    <set> -> 自动清除多余的逗号,避免SQL语法错误
        <if test="name != null and name != '' ">
            name = #{name},
        </if>
    </set>
    where id = #{id}
</update>

数据映射集,column指数据库列名,property指数据模型属性名,名称相同可以不配置
查询语句会把结果存储到column中,通过映射关系,将结果设置到数据模型上
一对一或者一对多查询时,通过{key=value}的形式将传参通过column属性传递到子查询语句中
鉴别器根据某些条件决定级联的方案,例如根据性别获取人的体检信息
<resultMap id="BaseResultMap" type="com.wjx.SystemUser">
    <constructor> -> 配置数据模型的构造方法
      <idArg column="id" javaType="java.lang.Integer"/>
      <arg column="name" javaType="java.lang.String"/>
    </constructor>
    <id column="id" property="id" /> -> ID列,重复会删除多余的数据
    <result column="name" property="name" /> -> 普通数据字段列
    <association property="role" column="{uid=su.id}" select="role"/> -> 一对一
    <collection property="permission" column="id" /> -> 一对多
    <discriminator javaType="long" column="sex"> -> 鉴别器
      <case value="1" resultMap="male"/> -> 查询男性体检表
      <case value="0" resultMap="female"/> -> 查询女性体检表
    </discriminator>
</resultMap>

<resultMap id="male" type="com.wjx.male" extends="BaseResultMap">
    <association property="health" column="id" select="male"/>
    select * from health_male where user_id = #{id} -> 子查询(male)
</resultMap>

<resultMap id="female" type="com.wjx.female" extends="BaseResultMap">
    <association property="health" column="id" select="female"/>
    select * from health_female where user_id = #{id} -> 子查询(female)
</resultMap>

主查询语句
<select id="user" resultType="com.wjx.SystemUser">
    select * from system_user su
    <where> -> 当有条件成立才生成WHERE语句
        <if test="list.size() != 0">
            su.id in
            <foreach collection="list" index="index" item="id"
                     open="(" close=")" separator=","> -> 接收集合参数并循环遍历
                #{id}
            </foreach>
        </if>
        <choose>
            <when test="name != null and name != ''">
              and su.name = #{name}
            </when>
            <otherwise>
              and su.name is not null
            </otherwise>
        </choose>
    </where>
</select>

子查询语句
<select id="role" resultType="com.wjx.SystemRole">
    select sr.* from system_role sr
    inner join system_user_role sur on sur.role_id = sr.id
    where sur.user_id = #{uid}
</select>

SQL片段
<sql id="sql">
  id,name,sex
</sql>
select <include refid="sql"/> from system_user

三、存储过程

DROP PROCEDURE SCORE; -> MySQL

CREATE PROCEDURE SCORE(IN date VARCHAR(10)) -> 统计学生每次测验的总分
BEGIN
    DROP TABLE IF EXISTS provisionally;
    CREATE TEMPORARY TABLE provisionally
    (
        name VARCHAR(20),
        score INT(11),
        date VARCHAR(20)
    );
    INSERT INTO provisionally (name,score,date)
    SELECT student.name,SUM(exam.score),exam.date FROM student
    INNER JOIN exam ON student.id = exam.student_id
    GROUP BY student.name,exam.date;
    IF date IS NULL THEN 
        SELECT * FROM provisionally ORDER BY score DESC;
    ELSE
        SELECT * FROM provisionally WHERE provisionally.date = date
        ORDER BY score DESC;
    END IF;
END

CALL SCORE(null); -> 查出所有的信息

public interface StudentExamDao{ -> Mybatis
  List<LinkedHashMap<String, Object>> score(String date);
}

<select id="score" statementType="CALLABLE" resultType="java.util.LinkedHashMap">
    {CALL SCORE(＃{date})};
</select>

四、生成代码

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>1.2.4</version>
</dependency>

<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.3.5</version>
    <scope>compile</scope>
    <optional>true</optional>
</dependency>

mapper.not-empty=false
mapper.identity=MYSQL

<?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>
    <context id="MysqlContext" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>

        <!--代码生成基类-->
        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <property name="mappers" value="springboot.wjx.generator.GeneratorMapper"/>
        </plugin>

        <!--数据库连接-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/springboot"
                        userId="root"
                        password="123456">
        </jdbcConnection>

        <!-- 对于生成的pojo所在包 -->
        <javaModelGenerator targetPackage="springboot.wjx.pojo" targetProject="src/main/java"/>

        <!-- 对于生成的mapper所在目录 -->
        <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources"/>

        <!-- 配置mapper对应的java映射 -->
        <javaClientGenerator targetPackage="springboot.wjx.mapper"
                             targetProject="src/main/java" type="XMLMAPPER"/>

        <!--生成代码的表-->
        <table tableName="user"></table>

    </context>
</generatorConfiguration>

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface GeneratorMapper<T> extends Mapper<T>, MySqlMapper<T> {
    // 特别注意，该接口不能被SpringBoot容器扫描，否则会报错
}

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

public class GeneratorApplication {
    public static void generator() throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        //指定逆向工程配置文件
        File configFile = new File("generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }

    public static void main(String[] args) throws Exception {
        try {
            generator();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

• 分页插件及配置

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.3</version>
</dependency>

pagehelper.helper-dialect=mysql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql

五、配置文件

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.1</version>
</dependency>

mybatis.type-aliases-package=springboot.wjx.pojo
mybatis.mapper-locations=classpath*:mapper/*.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings> -> 输出运行日志
        <setting name="logImpl" value="SLF4J"/>
        <setting name="cacheEnabled" value="true"/>
    </settings>

    <typeAliases> -> 定义别名
        <package name="com.wjx.pojo"/>
    </typeAliases>
</configuration>