SpringBoot-集成mybatis

一.添加相关依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--记载mybatis整合springboot的jar-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        
        <!--mysql的jdbc驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
</dependencies>

二.核心配置文件配置mapper配置文件的所在位置

#指定mapper配置文件所在位置
mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml

三.配置数据源

#配置数据源
spring:
  datasource:
    name: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false

四.扫描mapper接口(二选一)

1.@Mapper

在mapper接口上添加@mapper接口

2.@MapperScan

在运行主类上添加@MapperScan(“com.liangqiu11.springboot2x_02_mybatis.mapper”),括号中为mapper接口所在包

五.mapper的xml模板

<?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">
<!-- namespace(命名空间) 用来防止 sql 的命名冲突的 -->
<mapper namespace="mapper.StudentMapper">
    <!-- #{sname} 用来获取 Student 参数对象中的 sname属性-->
    <!-- useGeneratedKeys="true" 是告诉 mybatis 要使用由数据库产生的主键值 -->
    <!-- keyProperty="主键对应的属性名" -->
    <insert id="abc" parameterType="domain.Student"
            useGeneratedKeys="true" keyProperty="sid">
        insert into student(sid, sname, birthday, sex)
          values ( null, #{name}, #{birthday}, #{sex})
    </insert>

    <delete id="delete" parameterType="int">
        delete from student where sid = #{sid}
    </delete>

    <select id="findAll" resultType="domain.Student">
      select sid,sname name,birthday,sex from student
    </select>

    <select id="findById" resultType="domain.Student" parameterType="int">
        select sid,sname name,birthday,sex from student where sid = #{sid}
    </select>

    <!-- 更新所有列
    <update id="update" parameterType="domain.Student">
        update student set sname=#{name}, birthday=#{birthday}, sex=#{sex} where sid=#{sid}
    </update>
    -->

    <!-- 动态更新列
    Student stu = new Student();
    stu.setSid(1001);
    stu.setSex("男");

    update student set sex=#{sex} where sid=#{sid}
    用  set 标签可以去除多余的逗号
    -->
    <update id="update" parameterType="domain.Student">
        update student
        <set>
            <if test="name != null">
                sname=#{name},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
            <if test="sex != null">
                sex=#{sex},
            </if>
        </set>
        where sid=#{sid}
    </update>

    <!-- m , n
    java.util.Map -> map
    java.util.List -> list
    java.lang.String -> string
    map.put("m", 0);
    map.put("n", 5);
    -->
    <select id="findByPage" parameterType="map" resultType="domain.Student">
        select sid,sname name,birthday,sex from student limit #{m}, #{n}
    </select>


    <!--
    (1008,1011,1012
    delete from student where id in (1008,1011,1012)-->
    <delete id="deleteByIds" parameterType="list">
        delete from student where sid in
        <foreach collection="list" item="x" open="(" close=")" separator=",">#{x}</foreach>
    </delete>

    <!-- map
    sname=?
    birthday=?
    sex=?
    -->
    <!--<select id="findByCondition" parameterType="map" resultType="domain.Student">
        select sid,sname name,birthday,sex from student
          where 1=1
          <if test="sname!=null">
              and sname = #{sname}
          </if>
          <if test="birthday!=null">
              and birthday = #{birthday}
          </if>
          <if test="sex != null">
              and sex = #{sex}
          </if>
    </select>-->

    <select id="findByCondition" parameterType="map" resultType="domain.Student">
        select sid,sname name,birthday,sex from student
        <where> <!-- 可以用 where 去除多余的 and -->
            <if test="sname!=null">
                and sname = #{sname}
            </if>
            <if test="birthday!=null">
                and birthday = #{birthday}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>

    <!-- 方法1： 对每个特殊字符进行转义，可读性查 < &lt;   > &gt;
         方法2： xml 中的 CDATA 块， 把特殊符号写在 CDATA 块中
    -->
    <!--<select id="findByLt" parameterType="int" resultType="domain.Student">
        select sid,sname name,birthday,sex from student where sid &lt; #{sid}
    </select>-->
    <!--<select id="findByLt" parameterType="int" resultType="domain.Student">
      <![CDATA[
        select sid,sname name,birthday,sex from student where sid < #{sid}
      ]]>
    </select>-->
    <select id="findByLt" parameterType="int" resultType="domain.Student">
        select sid,sname name,birthday,sex from student where sid <![CDATA[ < ]]> #{sid}
    </select>

    <!-- map
    map.put(m, 0)
    map.put(n, 5)
    // select sid,sname name,birthday,sex from student limit 0, 5    使用  ${} 直接把值拼入了sql字符串
            * 因为直接拼接字符串，所以会有sql注入攻击问题
            * 可以替换sql语句中任意的部分
    // select sid,sname name,birthday,sex from student limit ?, ?    使用  #{} 把#{}占位符先用?替换，在后续代码中给?赋值
            * #{} 只能占位一个值，不能占位表名、列名、关键字
    -->
    <select id="findByPage2" parameterType="map" resultType="domain.Student">
        select sid,sname name,birthday,sex from student order by #{column} desc limit ${m}, ${n}
    </select>

</mapper>