Mybatis的映射文件

一、增删改查

• BookMapper接口

public interface BookMapper {
    public Book getBookById(Integer id);
    public void addBook(Book book);
    public void updateBook(Book book);
    public void deleteBookById(Integer id);
}

• BookMapper.xml映射文件

<mapper namespace="dao.BookMapper">  接口路径
    <select id="getBookById" resultType="bean.Book">   id为接口的方法名
        SELECT * FROM RUNOOB_TBL WHERE runoob_id = #{id}
    </select>
    <insert id="addBook" parameterType="bean.Book">
        INSERT INTO RUNOOB_TBL (runoob_title,runoob_author,submission_date)
        VALUES (#{runoobTitle},#{runoobAuthor},#{submissionDate})
    </insert>
    <update id="updateBook" parameterType="bean.Book">
        UPDATE RUNOOB_TBL SET runoob_title = 
        #{runoobTitle},runoob_author = #{runoobAuthor},
        submission_date = #{submissionDate} WHERE runoob_id = #{runoobId}
    </update>
    <delete id="deleteBookById">
        DELETE FROM RUNOOB_TBL WHERE runoob_id = #{id}
    </delete>
</mapper>

二、获取自增主键的值

<insert id="addBook" parameterType="bean.Book" 
                              useGeneratedKeys="true" keyProperty="runoobId">
    INSERT INTO RUNOOB_TBL (runoob_title,runoob_author,submission_date)
    VALUES (#{runoobTitle},#{runoobAuthor},#{submissionDate})
</insert>

添加两个属性：useGeneratedKeys="true" keyProperty="runoobId"（keyProperty = "runoobId" 是指定bean的自增主键属性）

三、参数处理

• 单个参数
  • 单个参数mybatis不会做特殊处理，直接#{参数名}取值
• 多个参数
  • 多个参数mybatis会将其封装成一个map
  • key:param1,...,paramN，或者参数的索引
  • value：传入的参数值

#{}就是从map中获取指定的key的值

多参数：通过id和author参数来查询book

//接口方法
Book getBookByIdAndAuthor(Integer id,String author);
//mapper
<select id="getBookByIdAndAuthor" resultType="bean.Book">
    SELECT * FROM RUNOOB_TBL WHERE runoob_id = #{id} AND runoob_author = #{author}
</select>

产生异常

Parameter 'id' not found.
Available parameters are [arg1, arg0, param1, param2]

找不到id参数，因为默认map封装的参数是param1...paramN
因此要想这样写runoob_id = #{id} AND runoob_author = #{author},需要命名参数：在封装map对象时指定key
使用注解明确指定参数的键值

Book getBookByIdAndAuthor(@Param("id") Integer id,@Param("author") String author);

• POJO
  如果多个参数正好是业务逻辑的数据模型，可以直接传入pojo
  • #{属性名}：取出传入的pojo的属性值
• Map
  如果多个参数不是业务模型中的数据，没有对应的pojo，不经常使用，为了方便，可以传入map
  • #{key}：取出map中对应的值

//接口方法
Book getBookByMap(HashMap<String,Object> map);
<!--mapper-->
 <select id="getBookByMap" resultType="bean.Book">
        SELECT * FROM RUNOOB_TBL WHERE runoob_id = #{id} AND 
                            runoob_author = #{author}
    </select>
/**
 * 测试通过map中的Id和author查询数据
 */
HashMap<String,Object> map = new HashMap<String, Object>();
map.put("id",13);
map.put("author","shi");
Book selectBookByMap = mapper.getBookByMap(map);

• #和$的取值区别
  • #{}：可以获取map中的值或者pojo对象属性的值；
  • ${}：可以获取map中的值或者pojo对象属性的值；

区别：
#{} 是以预编译的形式，将参数设置到sql语句中，PreparedStatement；
${} 取出的值直接拼接在sql语句中，会有安全问题；
大多情况下，我们取参数的值都应该去使用#{}
原生jdbc不支持占位符的地方可以使用${}进行取值
比如分表、排序：财务表按照年份分表拆分
  select * from ${year}_salary where xxx;
  select * from tbl_employee order by ${f_name} ${order}

四、select返回List和Map集合

通过author模糊查询，返回List集合

//定义一个返回List集合的查询方法
List<Book> getBookByAuthorLike(String author);
<!--mapper-->
<!-- 返回的结果是一个集合，但是returnType是集合中的元素类型 -->
<select id="getBookByAuthorLike" resultType="bean.Book">
    SELECT * FROM RUNOOB_TBL WHERE runoob_author LIKE #{author}
</select>
/**
 * 测试按照author模糊查询
 */
List<Book> bookByAuthorLike = mapper.getBookByAuthorLike("%x%");
System.out.println("bookByAuthorLike");
for (Book b:bookByAuthorLike) {
    System.out.println(b);
}
>> 结果
Book{runoobId=2, runoobTitle='python', runoobAuthor='sxc',
                 submissionDate=Wed Jan 02 00:00:00 CST 2019}
Book{runoobId=3, runoobTitle='c', runoobAuthor='sxc',
                 submissionDate=Tue Jan 08 00:00:00 CST 2019}
Book{runoobId=4, runoobTitle='matlab', runoobAuthor='sxc',
                 submissionDate=Thu Jan 03 00:00:00 CST 2019}

通过id查询数据，数据封装成map对象，键是列名，值是对象的值，只返回一条数据

//返回值自动封装成Map对象，键是列名，值是查询到的值
Map<String,Object> getBookByIdReturnMap(Integer id);
<!-- mapper -->
<select id="getBookByIdReturnMap" resultType="map">
    SELECT * FROM RUNOOB_TBL WHERE runoob_id = #{id}
</select>

/**
 * 测试通过Id查询，返回值封装成map对象
 */
Map<String, Object> bookByIdReturnMap = mapper.getBookByIdReturnMap(2);
System.out.println("bookByIdReturnMap");
System.out.println(bookByIdReturnMap);
>> 结果
{runoob_title=python, runoob_author=sxc, 
            submission_date=2019-01-02, runoob_id=2}

通过author模糊查询，结果封装成map对象，键是主键，值是Bean对象，和上面例子不同的是这个是返回多条数据的

/**
 * 查询到一组结果，将结果封装成Map对象，键是主键id，值是查询到的值，需要指定map的键属性, 
 * 用@MapKey注解指定bean的哪个属性作为map的键
 */
@MapKey("runoob_id")  //这种写法是错误的，指定的主键是bean的属性，runoobId
Map<String,Book> getBookByAuthorLikeReturnMap(String author);
报错：
There is no getter for property named 'runoob_id' in 'class bean.Book'

<!-- mapper -->
<select id="getBookByAuthorLikeReturnMap" resultType="bean.Book">
    SELECT * FROM RUNOOB_TBL WHERE runoob_author LIKE #{author}
</select>
/**
 * 测试按照author模糊查询,结果封装成map
 */
Map<String, Book> bookByAuthorLikeReturnMap = 
                                mapper.getBookByAuthorLikeReturnMap("%x%");
System.out.println("bookByAuthorLikeReturnMap");
System.out.println(bookByAuthorLikeReturnMap);
>> 结果
{2=Book{runoobId=2, runoobTitle='python', runoobAuthor='sxc',
               submissionDate=Wed Jan 02 00:00:00 CST 2019}, 
3=Book{runoobId=3, runoobTitle='c', runoobAuthor='sxc', 
               submissionDate=Tue Jan 08 00:00:00 CST 2019}, 
4=Book{runoobId=4, runoobTitle='matlab', runoobAuthor='sxc', 
               submissionDate=Thu Jan 03 00:00:00 CST 2019}}

五、resultMap 自定义结果集的封装规则

resultType属于自动封装的，bean的属性要么和数据库的列名一样，要么使用驼峰命名法，而resultMap可以建立数据库查询返回列名和bean类的属性映射。

//接口方法
Book getBookByIdCustomMap(Integer id);

resultMap的id是它的唯一标识，用于给在下面的select标签引用
自定义数据库查询返回的列名和bean属性的映射规则
<resultMap id="MyBook" type="bean.Book">
    <id column="runoob_id" property="runoobId"/>
    <result column="runoob_title" property="runoobTitle"/>
    <result column="runoob_author" property="runoobAuthor"/>
    <result column="submission_date" property="submissionDate"/>
</resultMap>
<!-- mapper -->
<select id="getBookByIdCustomMap" resultMap="MyBook">
    SELECT * FROM RUNOOB_TBL WHERE runoob_id = #{id}
</select>
/**
 * 测试自定义封装map，resultMap
 */
Book bookByIdCustomMap = mapper.getBookByIdCustomMap(2);
System.out.println("bookByIdCustomMap");
System.out.println(bookByIdCustomMap);
>> 结果
Book{runoobId=2, runoobTitle='python', runoobAuthor='sxc',
 submissionDate=Wed Jan 02 00:00:00 CST 2019}

六、关联查询-级联属性封装结果

增加一张表用于记录runoob_tbl表中书目的store

为Store创建一个bean类,Book的bean变成

public class Book {
    private Integer runoobId;
    private String runoobTitle;
    private String runoobAuthor;
    private Date submissionDate;
    private Store store;

public class Store {
    private Integer id;
    private String storeName;

• 直接使用resultMap定义封装规则

<!--多表联查结果集封装-->
<resultMap id="myBookStore" type="bean.Book">
    <id column="runoob_id" property="runoobId"/>
    <result column="runoob_title" property="runoobTitle"/>
    <result column="runoob_author" property="runoobAuthor"/>
    <result column="submission_date" property="submissionDate"/>
    <result column="store_id" property="store.id"/>
    <result column="store_name" property="store.storeName"/>
</resultMap>
<select id="getBookAndStore" resultMap="myBookStore">
    SELECT runoob_id,runoob_title,runoob_author,submission_date,store_id,store_name
    FROM runoob_tbl r LEFT JOIN store_tbl s 
    ON r.store_id = s.id WHERE r.runoob_id = #{id}
</select>

两表联查，自定义返回结果的封装规则，column是对应返回的列，property对应待封装对象的各个属性

• 使用association标签
  association可以指定联合的javabean对象
  property = "store" 指定哪个属性是联合的对象
  javaType：指定这个属性对象的类型(不能省略)

<!--多表联查结果集封装-->
<resultMap id="myBookStore" type="bean.Book">
    <id column="runoob_id" property="runoobId"/>
    <result column="runoob_title" property="runoobTitle"/>
    <result column="runoob_author" property="runoobAuthor"/>
    <result column="submission_date" property="submissionDate"/>
    <!--<result column="store_id" property="store.id"/>-->
    <!--<result column="store_name" property="store.storeName"/>-->
    <!--2.使用association-->
    <association property="store" javaType="bean.Store">
        <id column="store_id" property="id"/>
        <result column="store_name" property="storeName"/>
    </association>
</resultMap>

• 通过分步查询来查得结果
  先根据id查到Book，使用该Book结果的store_id去store_tbl表查询store_name
  要查store_tbl表的话，需要完成StoreMapper.xml和StoreMapper接口，不多累赘

<!--分步查询查出Book
    先查runoob_tbl表得到store_id，再根据store_id去查store_tbl表的store_name
-->
<resultMap id="myBookByStep" type="bean.Book">
    <id column="runoob_id" property="runoobId"/>
    <result column="runoob_title" property="runoobTitle"/>
    <result column="runoob_author" property="runoobAuthor"/>
    <result column="submission_date" property="submissionDate"/>
    <!--association 定义关联对象的封装规则
        property:关联对象
        select:指定关联属性是通过指定的方法查询得到的
        column: 指定哪一列是select指定方法的传入参数
    -->
    <!--第二步-->
    <association property="store" select="dao.StoreMapper.getStoreById" 
                                                       column="store_id">
        <id column="id" property="id"/>
        <result column="store_name" property="storeName"/>
    </association>
</resultMap>
<!--第一步是通过id查runoob_tbl表，这样就得到用于查store_tbl表的id了-->
<select id="getBookAndStoreByIdStep" resultMap="myBookByStep">
    SELECT * FROM runoob_tbl WHERE runoob_id = #{id}
</select>

分步查询的基础上，实现延时加载，只需要添加两个设置。延时加载意思是说在用到的时候才查询，不用到就不查询了。

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

七、collection关联集合封装规则

先看下store的bean对象

public class Store {
    private Integer id;
    private String storeName;
    private List<Book> books;

查询数据，封装到bean的各个属性, books是一个Collection集合

• 使用collection标签

<resultMap id="getBooks" type="bean.Store">
    <id column="id" property="id"/>
    <result column="store_name" property="storeName"/>
    <!--collection 指定关联集合属性的封装规则
        property：指定封装的bean的collection属性
        ofType：collection容器的内容的属性
    -->
    <collection property="books" ofType="bean.Book">
        <id column="runoob_id" property="runoobId"/>
        <result column="runoob_title" property="runoobTitle"/>
        <result column="runoob_author" property="runoobAuthor"/>
        <result column="submission_date" property="submissionDate"/>
        <result column="store_id" property="store.id"/>
        <result column="store_name" property="store.storeName"/>
    </collection>
</resultMap>
<select id="getStoreWithbooks" resultMap="getBooks">
    SELECT * from store_tbl s LEFT JOIN runoob_tbl r 
    ON s.id = r.store_id WHERE s.id = #{id}
</select>

• 分步查询封装collection
  先用store_id查询store_name,再用store_id查询Book数据(list集合)

<!--StoreMapper.xml  -->
<!--分步查询，封装collection-->
<resultMap id="getBooksByStep" type="bean.Store">
    <id column="id" property="id"/>
    <result column="store_name" property="storeName"/>
    <collection property="books" select="dao.BookMapper.getBooksByStoreId" 
                                                            column="id"/>
</resultMap>
<select id="getStoreWithBooksByStep" resultMap="getBooksByStep">
    SELECT * FROM store_tbl WHERE id = #{id}
</select>

<!-- BookMapper.xml -->
通过store_id查询Books封装成list
<select id="getBooksByStoreId" resultType="bean.Book">
    SELECT * FROM runoob_tbl WHERE store_id = #{id}
</select>