ZB-038-02MyBatis的xml方式

Mapper核心有两种实现

  • Mapper:接口由MyBatis动态代理 (上一篇就是这种方式)
    • 优点：方便
    • 缺点：SQL复杂的时候不够方便
  • Mapper:用XML编写复杂SQL
    • 优点：可以方便地使用MyBatis强大功能
    • 缺点：SQL和代码分离

使用xml写复杂sql

  • 继续看文档的Exploring Mapped SQL Statements部分

  • 新建 src/main/resources/db/myBatis/UserMapper.xml
    内容如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    <?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="org.mybatis.example.BlogMapper">
      <select id="selectBlog" resultType="Blog">
        select * from Blog where id = #{id}
      </select>
    </mapper>

  • 修改内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    <?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 这个随便写 就是用来管理你的mapper-->
    <mapper namespace="com.sql.xml.UserMapper">
        <select id="selectUser" resultType="map">
        select * from user
      </select>
    </mapper>

  • 把我们的UserMapper.xml 添加到config.xml里

    1
    2
    3
    4
    <mappers>
        <!--<mapper class="com.github.hcsp.sql.Sql$UserMapper"/>-->
        <mapper resource="db/myBatis/UserMapper.xml"/>
    </mappers>

  • 如何用这个xml呢？ 很明显 xml里的sql和代码分离了，让你来回找目录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    public static void main(String[] args) throws SQLException, IOException {
        String resource = "db/myBatis/config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
         
            // xml方式
            System.out.println( session.selectList("com.sql.xml.UserMapper.selectUser"));
        }
    }

    // 如果报错可能你没添加这个 UserMapper.xml到 config.xml里

Mapper的一切

  • parameterType
    • 参数的 ${} 和 #{}
    • 参数是按照 JavaBean约定读取的 getter/setter

  • resultType

    • typeAlias 简化 resultType

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      # 在UserMapper.xml resultType 很长
      <mapper namespace="com.sql.xml.UserMapper">
          <select id="selectUser" resultType="com.github.hcsp.sql.Sql$User">
          select * from user
          </select>
      </mapper>


      # typeAlias 帮你做一个映射 别名 注意它必须放在前面
      # 第一步修改 config.xml 
      <configuration>
          <settings>
              <setting name="logImpl" value="LOG4J"/>
          </settings>
          <!-- 添加别名映射 -->
          <typeAliases>
              <typeAlias alias="User" type="com.github.hcsp.sql.Sql$User"/>
          </typeAliases>
          <environments default="development">
              <environment id="development">
                  <transactionManager type="JDBC"/>
                  <dataSource type="POOLED">
                      <property name="driver" value="org.h2.Driver"/>
                      <property name="url" value="jdbc:h2:file:/Users/huangjiaxi/Downloads/practise-select-sql/target/test"/>
                      <property name="username" value="root"/>
                      <property name="password" value="Jxi1Oxc92qSj"/>
                  </dataSource>
              </environment>
          </environments>
          <mappers>
              <mapper resource="db/myBatis/UserMapper.xml"/>
          </mappers>
      </configuration>

      # 第二步修改 UserMapper.xml
      <mapper namespace="com.sql.xml.UserMapper">
          <select id="selectUser" resultType="User">
          select * from user
          </select>
      </mapper>

    • 写参数是按照 Java Bean约定的 getter/setter

  • Association

返回为map

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 修改UserMapper.xml
<mapper namespace="com.sql.xml.UserMapper">
    <select id="selectUser" resultType="map">
      select * from user
    </select>
</mapper>

# 此时返回的是 map
# 如下
[
{CREATED_AT=2019-10-31 21:30:05.072, ADDRESS=beijing, TEL=tel1, UPDATED_AT=2019-10-31 21:30:05.072, ID=1, NAME=zhangsan}, 
{CREATED_AT=2019-10-31 21:30:05.072, ADDRESS=shanghai, TEL=tel2, UPDATED_AT=2019-10-31 21:30:05.072, ID=2, NAME=lisi}, 
{CREATED_AT=2019-10-31 21:30:05.072, ADDRESS=shanghai, TEL=tel3, UPDATED_AT=2019-10-31 21:30:05.072, ID=3, NAME=wangwu},
{CREATED_AT=2019-10-31 21:30:05.072, ADDRESS=shenzhen, TEL=tel4, UPDATED_AT=2019-10-31 21:30:05.072, ID=4, NAME=zhangsan}
]

我想返回的是一个User

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 修改UserMapper.xml
# com.github.hcsp.sql.Sql$User  "$"代表连接的是内部类
<mapper namespace="com.sql.xml.UserMapper">
    <select id="selectUser" resultType="com.github.hcsp.sql.Sql$User">
      select * from user
    </select>
</mapper>

# 返回数据为
[
User{id=1, name='zhangsan', tel='tel1', address='beijing'}, 
User{id=2, name='lisi', tel='tel2', address='shanghai'}, 
User{id=3, name='wangwu', tel='tel3', address='shanghai'}, 
User{id=4, name='zhangsan', tel='tel4', address='shenzhen'}
]

他们的值是怎么设置进去的？

答案是：Java Bean

1
2
3
如果没有 setId() 会咋样？

答案是 扫这个 Bean 是否是 id 这个 field

需求变更，我要找id=1的用户

修改 UserMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<mapper namespace="com.sql.xml.UserMapper">
    <select id="selectUser" resultType="User">
      select * from user where id=#{id}
    </select>
</mapper>

修改 之前的查询语句
public static void main(String[] args) throws SQLException, IOException {
    // ...

    try (SqlSession session = sqlSessionFactory.openSession()) {

        User user = new User();
        user.id = 3;
        // 传递一个 user 对象进去
        System.out.println( session.selectList(
                    "com.sql.xml.UserMapper.selectUser",
                        user
                    )
        );

    // ...

    }
}

// 返回
DEBUG [main] - ==>  Preparing: select * from user where id=? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
[User{id=3, name='wangwu', tel='tel3', address='shanghai'}]

参数的 ${} 和 #{}的区别

  • #{} 把传入的值带入， 防sql注入
  • ${} 替换这个值,它会带来 “注入” 问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
User user = new User();
user.name = "wangwu";
// 传递一个 user 对象进去
System.out.println( session.selectList(
            "com.sql.xml.UserMapper.selectUser",
                user
            )
);

# 如果 sql是这样
select * from user where name=#{name}
select * from user where name="wangwu"


# 如果 sql是这样
select * from user where name=${name}
select * from user where name=wangwu // 这会报错的
你只能这样
select * from user where name='${name}'

这个User对象是必须的吗？

不是：你可以用map

1
2
3
4
5
6
7
Map<String,String> param = new HashMap<>();
param.put("name","wangwu");
System.out.println( session.selectList(
        "com.sql.xml.UserMapper.selectUser",
        param
        )
);