Mybatis XML方式的基本用法

创建项目

• 创建Maven项目
• 添加Mybatis依赖

<!--mybatis-->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.3</version>
</dependency>

• 添加Log4j、JUnit和Mysql依赖

<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

<!--测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

<!--日志-->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.26</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.7.26</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

配置Mybatis

创建配置文件

在src/main/resources下创建mabatis-config.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>
        <!--指定使用log4j输出日志-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <typeAliases>
        <!--配置包的别名
           这样在使用类的时候, 不需要写包名部分，只写类型即可
        -->
        <package name="com._54programer.xml.domain"/>
    </typeAliases>

    <!--数据库配置-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--方式1
            配置完整路径的映射配置文件-->
        <mapper resource="com/_54programer/xml/mapper/UserMapper.xml"/>
        <mapper resource="com/_54programer/xml/mapper/RoleMapper.xml"/>
        <!--方式2
            这种配置方式, 会先去查找com._54programer.xml.mapper包下所以的接口
            然后循环所有接口, 将接口com._54programer.xml.mapper.UserMapper转为还com/_54programer/xml/mapper/UserMapper.xml
            然后搜索xml资源, 搜到就解析xml
        -->
        <!-- 
        <package name="com._54programer.xml.mapper"/> -->
    </mappers>

</configuration>

创建数据库表、实体类和映射文件

• 数据库表

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `user_password` varchar(50) DEFAULT NULL COMMENT '密码',
  `user_email` varchar(50) DEFAULT 'test@qq.com' COMMENT '邮箱',
  `user_info` text DEFAULT NULL COMMENT '简介',
  `head_img` blob DEFAULT NULL COMMENT '头像',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表' |

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) DEFAULT NULL COMMENT '角色名',
  `enabled` int(11) DEFAULT NULL COMMENT '有效标志',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表' |

CREATE TABLE `sys_user_role` (
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户角色关联表'

• 实体类

/**
 * 用户表
 */
public class SysUser implements Serializable {
    /**
     * 用户ID
     * 不要使用基本数据类型
     * 原因: 如果使用long id, 那么id默认为0, 如果在动态语句中使用 id != null进行判断, 结果总会为true
     */
    private Long id;
    /**
     * 用户名
     */
    private String userName;
    /**
     * 密码
     */
    private String userPassword;
    /**
     * 邮箱
     */
    private String userEmail;
    /**
     * 简介
     */
    private String userInfo;
    /**
     * 头像
     * byte[] 这个类型一般对应数据库中的BLOB、LONGVARBINARY以及一些二进制流有关的字段类型
     */
    private byte[] headImg;
    /**
     * 创建时间
     */
    private Date createTime;

    省略getter、setter方法
}

/**
 * 角色表
 */
public class SysRole implements Serializable {
    /**
     * 角色ID
     */
    private Long id;
    /**
     * 角色名
     */
    private String roleName;
    /**
     * 有效标志
     */
    private Integer enabled;
    /**
     * 创建人
     */
    private String createBy;
    /**
     * 创建时间
     */
    private Date createTime;
    省略getter、setter方法
}

• 映射文件

/resources/com/_54programer/xml/mapper/UserMapper.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">
<mapper namespace="com._54programer.xml.mapper.UserMapper">

</mapper>

/resources/com/_54programer/xml/mapper/RoleMapper.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">
<mapper namespace="com._54programer.xml.mapper.RoleMapper">

</mapper>

• 配置Log4j

/resources/log4j.properties

# 全局配置
log4j.rootLogger=ERROR, stdout

# mybatis日志配置
# mybatis日志最低级别是TRACE，在这个日志级别下，会输出sql执行的详细信息，特别适合在开发时使用
log4j.logger.com._54programer.xml.mapper=TRACE

# 控制台输出配置
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

到这个我们的Mybatis环境就配置好了

使用Mybatis进行增删该查

Select用法

• 根据ID查询用户信息

package com._54programer.xml.mapper;
import com._54programer.xml.domain.SysUser;

public interface UserMapper {
    SysUser selectById(Long id);
}

<?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 namespace="com._54programer.xml.mapper.UserMapper">
    <!--
        resultMap: 用于配置java对象的属性和查询结果列的对应关系
        id: 跟select中resultMap对应
        type: java对象类型
        property: java对象属性
        column: 数据库列名
        jdbcType: 列对应的数据库类型
    -->
    <resultMap id="userMap" type="com._54programer.xml.domain.SysUser">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="userPassword" column="user_password"/>
        <result property="userEmail" column="user_email"/>
        <result property="userInfo" column="user_info"/>
        <result property="headImg" column="head_img" jdbcType="BLOB"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>
    <!--
        select: 查询标签
        id: 唯一标示符，通过id关联接口中的方法
        resultMap: 设置返回值的类型和映射关系
        #{id}: mybatis中预编译参数的一种方式, id是参数名
    -->
    <select id="selectById" resultMap="userMap">
        select * from sys_user where id = #{id}
    </select>
</mapper>

package com._54programer.xml.mapper;

import com._54programer.xml.domain.SysUser;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;

public class UserMapperTest {

    private static SqlSessionFactory sqlSessionFactory;

    @BeforeClass
    public static void init(){
        try {
            //1.通过Resources工具类将mybatis-config.xml读入Reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //2.创建SqlSessionFactory, 在创建SqlSessionFactory的过程中
            //首先解析mybatis-config.xml配置文件, 然后根据mappers配置读取全部的Mapper.xml进行具体的解析
            //解析完成后, 创建SqlSessionFactory就具有所有的属性配置和执行sql的信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void TestSelectById(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        SysUser sysUser = mapper.selectById(1L);
        sqlSession.close();
    }
}

运行输出:
DEBUG [main] - ==>  Preparing: select * from sys_user where id = ? 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] - <==        Row: 1, admin, 123456, admin@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
DEBUG [main] - <==      Total: 1

• 查询全部用户信息

public interface UserMapper {
    List<SysUser> selectAll();
}

<?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="com._54programer.xml.mapper.UserMapper">

    <!--
        resultType: 返回值类型
    -->
    <select id="selectAll" resultType="com._54programer.xml.domain.SysUser">
        select id,
            user_name userName,
            user_password userPassword,
            user_email userEmail,
            user_info userInfo,
            head_img headImg,
            create_time createTime
            from sys_user
    </select>

</mapper>

@Test
public void TestSelectAll(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<SysUser> sysUsers = mapper.selectAll();
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: select id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime from sys_user 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <==        Row: 1, admin, 123456, admin@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
TRACE [main] - <==        Row: 1001, test, 123456, test@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
DEBUG [main] - <==      Total: 2

Mybatis可以自动将以下画线方式命名的数据库列映射到Java对象的驼峰式命名属性中。所以我们还可以这样写

<select id="selectAll" resultType="com._54programer.xml.domain.SysUser">
    select id,
        user_name,
        user_password,
        user_email,
        user_info,
        head_img,
        create_time
        from sys_user
</select>

@Test
public void TestSelectAll(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<SysUser> sysUsers = mapper.selectAll();
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: select id, user_name, user_password, user_email, user_info, head_img, create_time from sys_user 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] - <==        Row: 1, admin, 123456, admin@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
TRACE [main] - <==        Row: 1001, test, 123456, test@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
DEBUG [main] - <==      Total: 2

• 多表关联查询

方式1: 重新定义一个继承SysUser的扩展类SysUserExtend

public class SysUserExtend extends SysUser {
    private String roleName;

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}

public interface UserMapper {
    List<SysUserExtend> selectUserRole(Long id);
}

<select id="selectUserRole" resultType="com._54programer.xml.domain.SysUserExtend">
    SELECT
    u.id, u.user_email, u.user_info, u.user_name, u.user_password, u.head_img, u.create_time,
    r.role_name
    from sys_user u
    LEFT JOIN sys_user_role ur on u.id = ur.user_id
    LEFT JOIN sys_role r on r.id = ur.role_id
    WHERE u.id = #{id}
</select>

@Test
public void TestselectUserRole(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<SysUserExtend> sysUserExtends = mapper.selectUserRole(1L);
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: SELECT u.id, u.user_email, u.user_info, u.user_name, u.user_password, u.head_img, u.create_time, r.role_name from sys_user u LEFT JOIN sys_user_role ur on u.id = ur.user_id LEFT JOIN sys_role r on r.id = ur.role_id WHERE u.id = ? 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, user_email, user_info, user_name, user_password, head_img, create_time, role_name
TRACE [main] - <==        Row: 1, admin@qq.com, <<BLOB>>, admin, 123456, <<BLOB>>, 2016-06-07 01:11:12.0, 管理员
TRACE [main] - <==        Row: 1, admin@qq.com, <<BLOB>>, admin, 123456, <<BLOB>>, 2016-06-07 01:11:12.0, 普通用户
DEBUG [main] - <==      Total: 2

方式2: 在SysUser中增加SysRole对象

public class SysUser implements Serializable {

    private Long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private byte[] headImg;
    private Date createTime;

    //在SysUser中增加SysRole对象
    private SysRole role;

public interface UserMapper {
    List<SysUser> selectUserRole(Long id);
}

<select id="selectUserRole" resultType="com._54programer.xml.domain.SysUser">
    SELECT
    u.id, u.user_email, u.user_info, u.user_name, u.user_password, u.head_img, u.create_time,
    r.role_name as "role.roleName"
    from sys_user u
    LEFT JOIN sys_user_role ur on u.id = ur.user_id
    LEFT JOIN sys_role r on r.id = ur.role_id
    WHERE u.id = #{id}
</select>

@Test
public void TestselectUserRole(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    mapper.selectUserRole(1L);
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: SELECT u.id, u.user_email, u.user_info, u.user_name, u.user_password, u.head_img, u.create_time, r.role_name as "role.roleName" from sys_user u LEFT JOIN sys_user_role ur on u.id = ur.user_id LEFT JOIN sys_role r on r.id = ur.role_id WHERE u.id = ? 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, user_email, user_info, user_name, user_password, head_img, create_time, role.roleName
TRACE [main] - <==        Row: 1, admin@qq.com, <<BLOB>>, admin, 123456, <<BLOB>>, 2016-06-07 01:11:12.0, 管理员
TRACE [main] - <==        Row: 1, admin@qq.com, <<BLOB>>, admin, 123456, <<BLOB>>, 2016-06-07 01:11:12.0, 普通用户
DEBUG [main] - <==      Total: 2

Insert

• 插入一条数据

public interface UserMapper {
    int addUser(SysUser sysUser);
}

<insert id="addUser">
    insert into sys_user(
        id, user_name, user_password, user_email, user_info, head_img, create_time)
    values(
        #{id}, #{userName}, #{userPassword}, #{userEmail},
        #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP}
    )
</insert>

@Test
public void TestAddUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    SysUser sysUser = new SysUser();
    sysUser.setCreateTime(new Date());
    sysUser.setUserEmail("test1@qq.com");
    sysUser.setUserInfo("test1...");
    sysUser.setUserName("sn");
    sysUser.setUserPassword("123456");
    mapper.addUser(sysUser);
    sqlSession.commit();
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: insert into sys_user( id, user_name, user_password, user_email, user_info, head_img, create_time) values( ?, ?, ?, ?, ?, ?, ? ) 
DEBUG [main] - ==> Parameters: null, sn(String), 123456(String), test1@qq.com(String), test1...(String), null, 2019-11-19 13:41:03.726(Timestamp)
DEBUG [main] - <==    Updates: 1

• 使用JDBC方式返回主键ID
  这种回写主键的方法只适用于支持主键自增的数据库。有些数据库（如 Oracle ）不提供主键自增的功能。

public interface UserMapper {
    int addUserGetId(SysUser sysUser);
}

<!--
    useGeneratedKeys: 设为true, Mybatis会使用JDBC的getGeneratedKeys方法来获取数据库内部生成的主键
    获得主键后将其赋值给keyProperty配置的id属性
-->
<insert id="addUserGetId" useGeneratedKeys="true" keyProperty="id">
    insert into sys_user(
        id, user_name, user_password, user_email, user_info, head_img, create_time)
    values(
        #{id}, #{userName}, #{userPassword}, #{userEmail},
        #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP}
    )
</insert>

@Test
public void TestaddUserGetId(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    SysUser sysUser = new SysUser();
    sysUser.setCreateTime(new Date());
    sysUser.setUserEmail("test1@qq.com");
    sysUser.setUserInfo("test1...");
    sysUser.setUserName("sn");
    sysUser.setUserPassword("123456");
    mapper.addUserGetId(sysUser);
    //输出新增记录的ID
    System.out.println("主键ID: "+sysUser.getId());
    sqlSession.commit();
    sqlSession.close();
}
输出:
DEBUG [main] - ==>  Preparing: insert into sys_user( id, user_name, user_password, user_email, user_info, head_img, create_time) values( ?, ?, ?, ?, ?, ?, ? ) 
DEBUG [main] - ==> Parameters: null, sn(String), 123456(String), test1@qq.com(String), test1...(String), null, 2019-11-19 13:50:06.426(Timestamp)
DEBUG [main] - <==    Updates: 1
主键ID: 1037

• 使用selectKey返回主键ID
  这种方式不仅适用于不提供主键自增功能的数据库，也适用于提供主键自增功能的数据库。

<!--
    resultType: 返回值类型
    order: order属性的设置和使用的数据库有关, 在mysql数据库中, order属性值设置AFTER,
           因为当前记录的主键值在insert语句执行成功后才能获取, 在Oracle, order的值要
           设置为BEFORE, 因为Oracle中需要先从序列获取值然后再作为主键插入到数据库中
-->
<insert id="addUserGetId">
    insert into sys_user(
        id, user_name, user_password, user_email, user_info, head_img, create_time)
    values(
        #{id}, #{userName}, #{userPassword}, #{userEmail},
        #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP}
    )
    <selectKey keyColumn="id" resultType="long" keyProperty="id" order="AFTER">
        select last_insert_id()
    </selectKey>
</insert>

@Test
public void TestaddUserGetId(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    SysUser sysUser = new SysUser();
    sysUser.setCreateTime(new Date());
    sysUser.setUserEmail("test1@qq.com");
    sysUser.setUserInfo("test1...");
    sysUser.setUserName("sn");
    sysUser.setUserPassword("123456");
    mapper.addUserGetId(sysUser);
    //输出新增记录的ID
    System.out.println(sysUser.getId());
    sqlSession.commit();
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: insert into sys_user( id, user_name, user_password, user_email, user_info, head_img, create_time) values( ?, ?, ?, ?, ?, ?, ? ) 
DEBUG [main] - ==> Parameters: null, sn(String), 123456(String), test1@qq.com(String), test1...(String), null, 2019-11-19 14:00:07.414(Timestamp)
DEBUG [main] - <==    Updates: 1
DEBUG [main] - ==>  Preparing: select last_insert_id() 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: last_insert_id()
TRACE [main] - <==        Row: 1038
DEBUG [main] - <==      Total: 1
1038

Update

public interface UserMapper {
    int updateUserById(SysUser sysUser);
}

<update id="updateUserById">
    update sys_user
    set user_name = #{userName},
        user_password = #{userPassword},
        user_email = #{userEmail},
        user_info = #{userInfo},
        head_img = #{headImg, jdbcType=BLOB},
        create_time = #{createTime, jdbcType=TIMESTAMP}
    where id = #{id}
</update>

@Test
public void TestUpdateUserById(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    SysUser sysUser = new SysUser();
    sysUser.setId(1037L);
    sysUser.setCreateTime(new Date());
    sysUser.setUserEmail("test2@qq.com");
    sysUser.setUserInfo("test2...");
    sysUser.setUserName("sn");
    sysUser.setUserPassword("123456789");
    int result = mapper.updateUserById(sysUser);
    System.out.println(result);
    sqlSession.commit();
    sqlSession.close();
}
输出:
DEBUG [main] - ==>  Preparing: update sys_user set user_name = ?, user_password = ?, user_email = ?, user_info = ?, head_img = ?, create_time = ? where id = ? 
DEBUG [main] - ==> Parameters: sn(String), 123456789(String), test2@qq.com(String), test2...(String), null, 2019-11-19 14:13:50.226(Timestamp), 1037(Long)
DEBUG [main] - <==    Updates: 1
1

Delete

public interface UserMapper {
    int deleteUserById(Long id);
}

<update id="deleteUserById">
    delete from sys_user where id = #{id}
</update>

@Test
public void TestDeleteUserById(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int result = mapper.deleteUserById(1037L);
    System.out.println(result);
    sqlSession.commit();
    sqlSession.close();
}
输出:
DEBUG [main] - ==>  Preparing: delete from sys_user where id = ? 
DEBUG [main] - ==> Parameters: 1037(Long)
DEBUG [main] - <==    Updates: 1
1

多参数

我们尝试下传多个参数进行数据查询

public interface UserMapper {
    SysUser findUserByIdAndUsername(Long id, String user_name);
}

<select id="findUserByIdAndUsername" resultType="com._54programer.xml.domain.SysUser">
    select id,
        user_name,
        user_password,
        user_email,
        user_info,
        head_img,
        create_time
        from sys_user
        where id = #{id} and user_name = #{user_name}
</select>

@Test
public void TestFindByIdAndUsername(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    SysUser sysUser = mapper.findUserByIdAndUsername(1L, "admin");
    sqlSession.close();
}

运行:
报错了！！！
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: 
Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]

怎么解决? 在参数钱添加@Param注解即可。

public interface UserMapper {
    SysUser findUserByIdAndUsername(@Param("id") Long id, @Param("user_name") String user_name);
}

再测试，这次成功了

DEBUG [main] - ==>  Preparing: select id, user_name, user_password, user_email, user_info, head_img, create_time from sys_user where id = ? and user_name = ? 
DEBUG [main] - ==> Parameters: 1(Long), admin(String)
TRACE [main] - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] - <==        Row: 1, admin, 123456, admin@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
DEBUG [main] - <==      Total: 1

给参数配置@Param注解后，Mybatis就会自动将参数封装成Map类型，@param会作为Map中的key，因此sql就可以通过配置的注解值来使用参数。

Mybatis动态SQL

if

• 查询条件

<!--
    test: 必须属性, 属性值是一个符合OGNL要求的判断表达式
    userName != null : 适用于任何类型, 用于判断属性值是否为空
    userName != '' : 仅适用于字符串类型, 用于判断是否为空字符串
    and or : 相当于 && ||
-->
<select id="selectByUser" resultType="com._54programer.xml.domain.SysUser">
    select id,
        user_name,
        user_password,
        user_email,
        user_info,
        head_img,
        create_time
        from sys_user
        where 1=1
        <if test="userName != null and userName != ''">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != null and userEmail != ''">
            and user_email = #{userEmail}
        </if>
</select>

• 更新字段

<!--
    每个if标签的sql语句后面都有一个逗号
    如果全部为null或'', 就会报错, 所以在最后拼接了一个id = #{id}
-->
<update id="updateUserById">
    update sys_user
    set
        <if test="userName != null and userName != ''">
        user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != ''">
        user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != ''">
        user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != ''">
        user_info = #{userInfo},
        </if>
        <if test="headImg != null">
        head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
        create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id}
    where id = #{id}
</update>

choose

<!--
    choose when otherwise
    可以让逻辑更为严密
    加入userName=null或'', 会查询出所有用户的信息
-->
<select id="selectByUser" resultType="com._54programer.xml.domain.SysUser">
    select id,
    user_name,
    user_password,
    user_email,
    user_info,
    head_img,
    create_time
    from sys_user
    where 1=1
    <choose>
        <when test="userName != null and userName != ''">
            and user_name = #{userName}
        </when>
        <otherwise>
            and 1=2
        </otherwise>
    </choose>
</select>

where

<!--
    where里都不成立, 不会拼接where 1=1
    where里有条件成立, 会自动拼接where 1=1
-->
<select id="selectByUser" resultType="com._54programer.xml.domain.SysUser">
    select id,
    user_name,
    user_password,
    user_email,
    user_info,
    head_img,
    create_time
    from sys_user
    <where>
        <if test="userName != null and userName != ''">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != null and userEmail != ''">
            and user_email = #{userEmail}
        </if>
    </where>
</select>

foreach

• in查询

<!--
    collection: 循环迭代的属性名
    item: 变量名，值为从法代对象中取出的每一个值。
    index: 索引的属性名
    open: 整个循环内容开头的字符串
    close: 整个循环内容结尾的字符串
    separator: 每次循环的分隔符
-->
<select id="selectByID" resultType="com._54programer.xml.domain.SysUser">
    select id,
        user_name,
        user_password,
        user_email,
        user_info,
        head_img,
        create_time
        from sys_user
        where id in
        <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
            #{id}
        </foreach>
</select>

• 批量插入

<insert id="addUser">
    insert into sys_user(
        id, user_name, user_password, user_email, user_info, head_img, create_time)
    values(
    <foreach collection="list" item="user" separator=",">
        #{user.id}, #{user.userName}, #{user.userPassword}, #{user.userEmail},
        #{user.userInfo}, #{user.headImg, jdbcType=BLOB}, #{user.createTime, jdbcType=TIMESTAMP}
    </foreach>
    )
</insert>

• 动态更新

<!--
    MyBati在内部的上下文中默认值_parameter
-->
<update id="updateUserById">
    update sys_user
    set
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>

Mapper接口动态代理实现原理

我们可能会有一个疑问，为什么Mapper接口没有实现类却能被正常使用？
这是因为Mybatis在Mapper接口上使用了动态代理。

这是我们刚才定义的一个Mapper接口:

public interface UserMapper {
    List<SysUser> selectAll();
}

我们使用Java动态代理方式创建一个代理类

public class MyMapperProxy<T> implements InvocationHandler {

    private Class<T> mapperInterface;
    private SqlSession sqlSession;

    public MyMapperProxy(Class<T> mapperInterface, SqlSession sqlSession) {
        this.mapperInterface = mapperInterface;
        this.sqlSession = sqlSession;
    }

    /**
     * 这里只是简单实现
     * 不考虑接口方法中的参数和方法的返回值
     */
    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        List<T> list = sqlSession.selectList(mapperInterface.getCanonicalName() + "." + method.getName());
        return list;
    }

}

@Test
public void Test1(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    MyMapperProxy userMapperProxy = new MyMapperProxy(UserMapper.class, sqlSession);
    UserMapper userMapper = (UserMapper) Proxy.newProxyInstance(Thread.currentThread().getContextClassLoader(), new Class[]{UserMapper.class}, userMapperProxy);
    userMapper.selectAll();
    sqlSession.close();
}

输出:
DEBUG [main] - ==>  Preparing: select id, user_name, user_password, user_email, user_info, head_img, create_time from sys_user 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
TRACE [main] - <==        Row: 1, admin, 123456, admin@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
TRACE [main] - <==        Row: 1001, test, 123456, test@qq.com, <<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
TRACE [main] - <==        Row: 1036, sn, 123456, test1@qq.com, <<BLOB>>, <<BLOB>>, 2019-11-19 13:41:03.0
TRACE [main] - <==        Row: 1038, sn, 123456, test1@qq.com, <<BLOB>>, <<BLOB>>, 2019-11-19 14:00:07.0
DEBUG [main] - <==      Total: 4

从代理类中可以看到，当调用一个接口的方法时，会先通过接口的全限定名称和当前调用的方法名的组合得到这个方法id ，这个 id 的值就是映射 XML中namespace 和具体方法 id的组合。所以可以在代理方法中使用 sqlSession 以命名空间的方式调用方法。通过这种方式可以将接口和 XML 文件中的方法关联起来。这种代理方式和常规代理的不同之处在于，这里没有对某个具体类进行代理，而是通过代理转化成了对其他代码的调用。