【八】Spring Boot之 SpringBoot + Mybatis 增删改查 总结（XML、注解、动态SQL，一对一关联查询、一对多关联查询）

一、insert

  parameterType ，入参的全限定类名或类型别名

   keyColumn ，设置数据表自动生成的主键名。对特定数据库（如PostgreSQL），若自动生成的主键不是第一个字段则必须设置

   keyProperty ，默认值unset，用于设置getGeneratedKeys方法或selectKey子元素返回值将赋值到领域模型的哪个属性中

   useGeneratedKeys ，取值范围true|false(默认值)，设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中。MySQL和SQLServer执行auto-generated key field，因此当数据库设置好自增长主键后，可通过JDBC的getGeneratedKeys方法获取。但像Oralce等不支持auto-generated key field的数据库就不能用这种方法获取主键了

   statementType ，取值范围STATEMENT,PREPARED（默认值）,CALLABLE

   flushCache ，取值范围true(默认值)|false，设置执行该操作后是否会清空二级缓存和本地缓存

   timeout ，默认为unset（依赖jdbc驱动器的设置），设置执行该操作的最大时限，超时将抛异常

   databaseId ，取值范围oracle|mysql等，表示数据库厂家，元素内部可通过`<if test="_databaseId = 'oracle'">`来为特定数据库指定不同的sql语句

 

1.返回值为插入的记录数目

XML方式普通例子

  <insert id="insert" parameterType="com.sid.model.User" >
    insert into user (id, name, password, 
      mobile_phone)
    values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
      #{mobilePhone,jdbcType=VARCHAR})
  </insert>

注解方式

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);

2.返回值为操作后获取记录主键

xml方式 

 <insert id="insert" parameterType="com.sid.model.User" useGeneratedKeys="true" keyProperty="id" >
    insert into user (id, name, password, 
      mobile_phone)
    values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
      #{mobilePhone,jdbcType=VARCHAR})
  </insert>

注解方式

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{user.name}, #{user.age})")
@Options(useGeneratedKeys = true, keyProperty = "user.id")
int insert(@Param("user") User user);

@Options注解常用属性: 
- flushCache:刷新缓存策略，有DEFAULT,TRUE,FALSE三种值，默认DEFAULT表示刷新查询语句的缓存 
- useCache：默认true，表示使用缓存 
- fetchSize：查询时的获取数量 
- useGeneratedKeys：默认false，是否返回插入的id 
- keyProperty：实体类id属性 
- keyColumn：实体类属性对应数据库的字段

3.批量插入

1.foreach方式

mapper.java

    //ra对应roleAuthMapper.xml中的，修改的话要同时改
    void insertBatch(List<RoleAuth> ra);

mapper.xml

 <insert id="insertBatch" parameterType="List">
    INSERT INTO role_auth
    (role_id, menu_id, creator,create_time)
    VALUES
    <foreach collection ="list" item="ra" separator ="," close=";">
      (#{ra.roleId}, #{ra.menuId}, #{ra.creator}, #{ra.createTime})
    </foreach >
  </insert>

2.ExecutorType.BATCH方式

java

注意，这个opensession默认是关闭自动提交的，所以最后需要sqlSession.commit

        long start = System.currentTimeMillis();
        User user;
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH/*,false*/);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        for (int i = 0; i < 5; i++) {
            user = new User();
            user.setId(i*100L);
            user.setName("name" + i);
            user.setPassword("123");
            user.setMobilePhone("123");
            mapper.insert(user);
        }
        sqlSession.commit();
        sqlSession.close();
        long end = System.currentTimeMillis();
        System.out.println("---------------" + (start - end) + "---------------");

二、select

1.普通例子

XML方式

 <resultMap id="BaseResultMap" type="com.sid.model.User" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="mobile_phone" property="mobilePhone" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, password, mobile_phone
  </sql>  
 <select id="selectAll" resultMap="BaseResultMap"  >
    select
    <include refid="Base_Column_List" />
    from user
  </select>

注解方式

@Select("select id,name,age,mobile_phone from user where id = #{id}")
int select(@Param("id") Long id);

2.动态SQL

1.<if>

xml方式

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

注解方式（动态SQL的注解方式都一样，不一一介绍）

用script标签包围，然后像xml语法一样写

@Select("<script>"
+"SELECT * FROM BLOG WHERE state = 'ACTIVE'"
+" <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>"
+"</script>")
Blog select(@Param("title ") Long title ,@Param("author ") Author author );

2.<choose> <when> <otherwise>

类似于Java 中的 switch 语句。还是上面的例子，但是这次变为提供了“title”就按“title”查找，提供了“author”就按“author”查找的情形，若两者都没有提供，就返回所有符合条件的 BLOG

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

3.<where>

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

4.<foreach>

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

3.关联查询<association>

1.一对一关联

相当于

public class Classes{
    private Long id ;
    private String name;
    private Teacher teacher;
    省略setter getter
}

public class Teacher{
    private Long id;
    private String name;
    省略setter getter
}

 

XML方式

嵌套结果和嵌套查询。

<association javaType=‘Teacher’>指的是POJO中属性的类型

<!-- 根据班级id查询班级和教师信息 -->
    <select id="getClass" resultMap="ClassResultMap">
        select *
        from class as c
        left join
        teacher as t on c.teacher_id = t.t_id
        where c.c_id
        = #{id}
    </select>

    <!-- 嵌套结果 -->
    <resultMap type="Classes" id="ClassResultMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" javaType="Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
    </resultMap>

    <select id="getClass2" parameterType="int"
        resultMap="ClassResultMap2">
        select * from class where c_id=#{id}
    </select>


    <!-- 嵌套查询 -->
    <resultMap type="Classes" id="ClassResultMap2">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            select="getTeacher" />
    </resultMap>

    <select id="getTeacher" parameterType="int" resultType="Teacher">
        SELECT
        t_id id, t_name name from teacher where t_id=#{id}
    </select>

注解方式@Results @Result

one=@one 代表一对一

select="com.sid.mapper.TeacherMapper.selectById" 是指的调用TeacherMapper接口的selectById方法，入参是@Result(column="teacher_id",property="teacher"中的teacher_id

fetchType=FetchType.LAZY 获取当前pojo后延迟加载对应数据

fetchType=FetchType.EAGER 获取当前pojo后立即加载对应数据

延迟加载是针对嵌套查询而言的，是指在进行查询的时候先只查询最外层的SQL，对于内层SQL将在需要使用的时候才查询出来。Mybatis的延迟加载默认是关闭的，即默认是一次就将所有的嵌套SQL一并查了将对象所有的信息都查询出来。

@Mapper
public interface ClassesMapper {

    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column="name",property="name"),
            @Result(column="teacher_id",property="teacher",
                    one=@One(
                            select="com.sid.mapper.TeacherMapper.selectById"/*,fetchType= FetchType.EAGER*/
                            /*fetchType= FetchType.LAZY*/))
    })
    @Select("select * " +
            "from classes c " +
            "where c.id = #{id}")
    public Classes selectAssociat(Long id);
}

@Mapper
public interface TeacherMapper {

    @Select("select * from teacher where id = #{id}")
    public Teacher selectById(@Param("id")Long id);
}

 

2.一对多关联

相当于

public class Classes{
    private Long id ;
    private String name;
    private Teacher teacher;
    private List<Student> students;
    省略setter getter
}

public class Teacher{
    private Long id;
    private String name;
    省略setter getter
}

public class Student{
    private Long id;
    private String name;
    省略setter getter
}

XML实现

<collection ofType=‘Student’>值得是映射到list集合属性中POJO的类型 

<select id="getClass3" parameterType="int"
        resultMap="ClassResultMap3">
        select *
        from class c
        left join teacher t on c.teacher_id =
        t.t_id
        left join student
        s on c.c_id = s.class_id
        where c.c_id = #{id}
    </select>

    <resultMap type="Classes" id="ClassResultMap3">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            javaType="Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
        <collection property="students" ofType="Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>

注解实现

 Many=@Many

select="com.sid.mapper.StudentMapper.selectByClassId" 是指的调用StudentMapper接口的selectByClassId方法，入参是@Result(column="id",property="students"中的id,即是查询完后的课程ID

    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column="name",property="name"),
            @Result(column="teacher_id",property="teacher",
                    one=@One(
                            select="com.sid.mapper.TeacherMapper.selectById"
                            )),
            @Result(column="id",property="students",
                    many=@Many(
                            select="com.sid.mapper.StudentMapper.selectByClassId"
                            ))
    })
    @Select("select * " +
            "from classes c " +
            "where c.id = #{id}")
    public Classes selectOneToMany(Long id);

@Repository
public interface StudentMapper {

    @Select("select * from student where class_id = #{classId}")
    public List<Student> selectByClassId(@Param("classId")Long classId);

}

 

三、parametertype 多个入参

1.单个入参

<select id="getUserByMobilePhone" resultMap="BaseResultMap" parameterType="java.lang.String" >
  select
  <include refid="Base_Column_List" />
  from user
  where mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
</select>

2.多个入参

XML方式

在mapper.java中使用@Param

List<WhiteList> selectAll(@Param("name")String name, @Param("url") String url);

mapper.xml中不用@parameterType

取值#{url}不用再指明jdbcType=VARCHAR 

<select id="selectAll" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from white_list
    <where>
      <if test="name!=null">
        name like #{name}
      </if>

      <if test="url!=null">
        and url like #{url}
      </if>
    </where>
  </select>

注解map方式(XML也能用map入参,@parameterType=‘java.util.Map’这里不演示)

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})")
int insertByMap(Map<String, Object> map);

注解model类方式（XML也能用model入参，@parameterType=‘com.sid.User’这里不演示）

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insertByUser(User user);

四、update

XML方式示例

<update id="updateByPrimaryKey" parameterType="com.sid.model.User" >
    update user
    set name = #{name,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
    where id = #{id,jdbcType=BIGINT}
  </update>

注解方式 

 @Update("update user set userId=#{userId},password=#{password},comment=#{comment} where userName=#{userName}")  
    public int update ( User user );  

五、delete

XML方式示例

<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from user
    where mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
  </delete>

注解方式 

@Delete("delete from user where userName = #{userName}")  
    public int delete ( String userName );