MyBatis3 开发环境搭建

1. 准备工作

下载并导入SQL数据文件：测试数据库脚本
下载相关JAR文件
1. MyBatis3核心jar文件：mybatis-3.2.2.jar
2. Mysql数据库驱动文件：mysql-connector-java-5.1.0-bin.jar

2. MyBatis3开发环境搭建

创建 JavaWeb 项目 MyBatisDemo
将相关JAR文件复制到WebRoot/WEB-INF/lib目录下
创建源码目录 resources 和 test

3. 创建配置文件

在 resources 目录下创建 mybatis-config.xml 文件

在 mybatis-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>
<!-- 配置多套运行环境 default:默认执行的环境 -->
 <environments default="dev">
     <!-- 配置运行环境 id:配置环境名称，不能重复 -->
     <environment id="dev">
         <!-- 配置事务管理器 type：事务管理器类型 -->
         <transactionManager type="JDBC"/>
         <!-- 配置数据源 type：数据源类型 -->
         <dataSource type="POOLED">
             <!-- 设置驱动 -->
             <property name="driver" value="com.mysql.jdbc.Driver"/>
             <!-- 设置连接字符串 -->
             <property name="url" value="jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&amp;characterEncoding=utf-8"/>
             <!-- 用户名 -->
             <property name="username" value="root"/>
             <!-- 密码 -->
             <property name="password" value="ok"/>
         </dataSource>
     </environment>
 </environments>
</configuration>

4. 创建XML映射文件

在 src 目录中创建包 dao.user
在 dao.user 包中创建文件 UserMapper.xml

在 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="dao.user.UserMapper">
 <!-- 查询语句 id:唯一标识 resultType:返回值类型 -->
 <select id="getCount" resultType="int">
     select count(1) as count from smbms_user
 </select>
</mapper>

在 mybatis-config.xml 中配置 XML映射文件

<mappers>
 <mapper resource="dao/user/UserMapper.xml"/>
</mappers>

5. 使用MyBatis3访问数据库

在 test 目录中创建 单元测试文件 UserTest.java

在 UserTest.java 中键入以下内容:

import java.io.IOException;
import java.io.InputStream;
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.Test;
public class UserMapperTest {
 @Test
 public void test() {
     int count = 0;
     SqlSession sqlSession = null;
     try {
         InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
         sqlSession = factory.openSession();
         count = sqlSession.selectOne("dao.user.UserMapper.getCount");
         System.out.println(count);
     } catch (IOException e) {
         e.printStackTrace();
     }finally{
         if (null != sqlSession) {
             sqlSession.close();
         }
     }
 }
}

MyBatis3 工具封装类

package util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * MyBatis工具类
 * @author yacon
 *
 */
public class MyBatisUtil {
    // 声明SqlSessionFactory对象
    private static SqlSessionFactory factory = null;

    /**
     * 创建SqlSessionFactory对象
     */
    static{
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建SqlSession对象
     * @return
     */
    public static SqlSession createSqlSession(){
        return factory.openSession(false);
    }

    /**
     * 关闭SqlSession对象
     * @param sqlSession
     */
    public static void closeSqlSession(SqlSession sqlSession){
        if(null != sqlSession){
            sqlSession.close();
        }
    }
}

MyBatis3 配置数据源

静态配置

将数据库信息直接写在环境中

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8"/>
            <property name="username" value="root"/>
            <property name="password" value="ok"/>
        </dataSource>
    </environment>
</environments>

动态配置

通过指定外部 properties 文件实现

在 resources 目录下创建 database.properties 文件

在 database.properties 文件中键入以下代码：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8
username=root
password=ok

修改 mybatis-config.xml 文件，具体内容如下：

<properties resource="database.properties" />
<environments default="development">
 <environment id="development">
     <transactionManager type="JDBC"></transactionManager>
     <dataSource type="POOLED">
         <property name="driver" value="${driver}" />
         <property name="url" value="${url}" />
         <property name="username" value="${username}" />
         <property name="password" value="${password}" />
     </dataSource>
 </environment>
</environments>

在properties节点中配置

<properties>
    <property name="driver" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8" />
    <property name="username" value="root" />
    <property name="password" value="ok" />
</properties>
<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${driver}" />
            <property name="url" value="${url}" />
            <property name="username" value="${username}" />
            <property name="password" value="${password}" />
        </dataSource>
    </environment>
</environments>

混合模式实现

<properties resource="database.properties">
    <property name="driver" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8" />
    <property name="username" value="root" />
    <property name="password" value="ok" />
</properties>
<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${driver}" />
            <property name="url" value="${url}" />
            <property name="username" value="${username}" />
            <property name="password" value="${password}" />
        </dataSource>
    </environment>
</environments>

MyBatis3 日志实现

准备工作

下载Log4j核心文件： log4j-1.2.17.jar
下载Log4j配置文件：log4j.properties

部署 Log4j

将 log4j-1.2.17.jar 文件拷贝到 WebRoot/WEB-INF/lib 目录下
将 log4j.properties 文件拷贝到 resources 目录下

配置 Log4j
打开mybatis-config.xml配置文件，并添加如下代码：

<settings>
    <setting name="logImpl" value="LOG4J" />
</settings>

MyBatis3 通过接口进行数据库访问

1. 创建接口

在 dao.user 包中，创建 UserMapper.java 接口文件

在 UserMapper.java 文件中键入如下代码：

package dao.user;
public interface UserMapper {
 public int count();
}

2. 修改单元测试文件

package test.user;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import dao.user.UserMapper;
import utils.MybatisUtil;
public class UserTest {
    @Test
    public void getCount() {
        SqlSession session = null;
        try {
            session = MybatisUtil.openSession();
            int count = session.getMapper(UserMapper.class).getCount();
            System.out.println(count);
        } finally{
            MybatisUtil.closeSession(session);
        }
    }
}

MyBatis3 映射器的注册

直接注册映射器文件

<mappers>
    <mapper resource="dao/user/UserMapper.xml" />
    <mapper resource="dao/role/RoleMapper.xml" />
</mappers>

通过包名自动注册

该方法会自动寻找并注册当前包及其子包中的映射器文件

<mappers>
    <package name="dao"/>
</mappers>

MyBatis3 创建POJO类

什么是POJO类

POJO类即为持久化类，是指其实例状态需要被MyBatis持久化到数据库中的类。通常情况下，POJO类的类名与数据表名相同，属性名与字段名相同，但这不是必须的，因为MyBatis的映射机制可以解决POJO类中的属性名与数据表字段名不一致的问题。

创建POJO类

在 src 目录中创建包 pojo
在 pojo 包中创建 User.java 文件

在 User.java 文件中键入如下代码：

package pojo;
import java.util.Date;
public class User {
 private Integer id;
 private String userCode;
 private String userName;
 private String userPassword;
 private Integer gender;
 private Date birthday;
 private String phone;
 private String address;
 private Integer userRole;
 private Integer createdBy;
 private Date creationDate;
 private Integer modifyBy;
 private Date modifyDate;
 public Integer getId() {
     return id;
 }
 public void setId(Integer id) {
     this.id = id;
 }
 public String getUserCode() {
     return userCode;
 }
 public void setUserCode(String userCode) {
     this.userCode = userCode;
 }
 public String getUserName() {
     return userName;
 }
 public void setUserName(String userName) {
     this.userName = userName;
 }
 public String getUserPassword() {
     return userPassword;
 }
 public void setUserPassword(String userPassword) {
     this.userPassword = userPassword;
 }
 public Integer getGender() {
     return gender;
 }
 public void setGender(Integer gender) {
     this.gender = gender;
 }
 public Date getBirthday() {
     return birthday;
 }
 public void setBirthday(Date birthday) {
     this.birthday = birthday;
 }
 public String getPhone() {
     return phone;
 }
 public void setPhone(String phone) {
     this.phone = phone;
 }
 public String getAddress() {
     return address;
 }
 public void setAddress(String address) {
     this.address = address;
 }
 public Integer getUserRole() {
     return userRole;
 }
 public void setUserRole(Integer userRole) {
     this.userRole = userRole;
 }
 public Integer getCreatedBy() {
     return createdBy;
 }
 public void setCreatedBy(Integer createdBy) {
     this.createdBy = createdBy;
 }
 public Date getCreationDate() {
     return creationDate;
 }
 public void setCreationDate(Date creationDate) {
     this.creationDate = creationDate;
 }
 public Integer getModifyBy() {
     return modifyBy;
 }
 public void setModifyBy(Integer modifyBy) {
     this.modifyBy = modifyBy;
 }
 public Date getModifyDate() {
     return modifyDate;
 }
 public void setModifyDate(Date modifyDate) {
     this.modifyDate = modifyDate;
 }
}

数据库类型与java类型对照表

数据库类型	java类型
int	Integer
bigint	Integer
varchar	String
date	java.util.Date
datetime	java.util.Date

MyBatis3 向SQL语句传参

传入一个参数

在 dao.user.UserMapper 接口中，新增如下方法：
```
public int del(int id);
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<delete id="del" parameterType="int">
DELETE FROM smbms.smbms_user
WHERE
id = #{id}
</delete>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void del() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     int res = session.getMapper(UserMapper.class).del(17);
     if (res > 0) {
         session.commit();
         System.out.println("删除成功");
     } else {
         session.rollback();
         System.out.println("删除失败");
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

传入多个参数

POJO对象实现

在 dao.user.UserMapper 接口中，新增如下方法：
```
public int add(User user);
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<insert id="add" parameterType="pojo.User">
 INSERT INTO smbms.smbms_user
 (
 userCode,
 userName,
 userPassword,
 gender,
 birthday,
 phone,
 address,
 userRole,
 createdBy,
 creationDate,
 modifyBy,
 modifyDate
 )
 VALUES
 (
 #{userCode},
 #{userName},
 #{userPassword},
 #{gender},
 #{birthday},
 #{phone},
 #{address},
 #{userRole},
 #{createdBy},
 #{creationDate},
 #{modifyBy},
 #{modifyDate}
 );
</insert>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
 public void add() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     User user = new User();
     user.setUserCode("zhangsan");
     user.setUserName("张三");
     user.setUserPassword("123456");
     user.setGender(1);
     user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1988-10-11"));
     user.setPhone("1895111111");
     user.setAddress("北京市长安路1号");
     user.setUserRole(1);
     user.setCreatedBy(1);
     user.setCreationDate(new SimpleDateFormat("yyy-MM-dd").parse("2019-4-1"));
     user.setModifyBy(1);
     user.setModifyDate(new SimpleDateFormat("yyy-MM-dd").parse("2019-5-1"));
     int res = session.getMapper(UserMapper.class).add(user);
     if (res > 0) {
         session.commit();
         System.out.println("插入成功");
     }else{
         session.rollback();
         System.out.println("插入失败");
     }
 } catch (ParseException e) {
     e.printStackTrace();
 } finally {
     MybatisUtil.closeSession(session);
 }
}

Map对象实现

在 dao.user.UserMapper 接口中，新增如下方法：
```
public int login(Map<String, String> map);
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="login" parameterType="Map" resultType="int">
 SELECT
     COUNT(1)
 FROM
     smbms_user
 WHERE
     userCode = #{userCode}
     AND userPassword = #{userPassword}
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void login() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     Map<String, String> map = new HashMap<String, String>();
     map.put("userCode", "admin");
     map.put("userPassword", "1234567");
     int res = session.getMapper(UserMapper.class).login(map);
     if(res > 0){
         System.out.println("登陆成功");
     }else{
         System.out.println("登陆失败");
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

注解实现

在 dao.user.UserMapper 接口中，新增如下方法：

public int login2(@Param("userCode")String userCode, @Param("userPassword")String userPassword);

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="login2" resultType="int">
 SELECT
     COUNT(1)
 FROM
     smbms_user
 WHERE
     userCode = #{userCode}
     AND userPassword = #{userPassword}
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void login2() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     int res = session.getMapper(UserMapper.class).login2("admin","1234567");
     if(res > 0){
         System.out.println("登陆成功");
     }else{
         System.out.println("登陆失败");
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

MyBatis3 类型别名

给具体的类取别名

<typeAliases>
    <typeAlias type="pojo.User" alias="User"/>
    <typeAlias type="pojo.Role" alias="Role"/>
</typeAliases>

给指定包下的类取别名

<typeAliases>
    <package name="pojo"/>
</typeAliases>

MyBatis3 获取查询结果

单表查询

查询单个值

在 dao.user.UserMapper 接口中，新增如下方法：

public String getUserName(@Param("userCode") String userCode);

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getUserName" resultType="String">
 SELECT
     userName
 FROM
     smbms_user
 WHERE
     userCode = #{userCode}
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getUserName(){
 SqlSession session = null;
 try{
     session = MybatisUtil.openSession();
     String userName = session.getMapper(UserMapper.class).getUserName("admin");
     System.out.println(userName);
 } finally {
     MybatisUtil.closeSession(session);
 }
}

查询一行数据

在 dao.user.UserMapper 接口中，新增如下方法：
```
public User getUserById(@Param("id") int id);
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getUserById" resultType="pojo.User">
 SELECT
     *
 FROM
     smbms_user
 WHERE
     id = #{id}
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getUserById(){
 SqlSession session = null;
 try{
     session = MybatisUtil.openSession();
     User user = session.getMapper(UserMapper.class).getUserById(1);
     System.out.println(user.getUserCode());
     System.out.println(user.getUserName());
 }finally {
     MybatisUtil.closeSession(session);
 }
}

查询多行数据

在 dao.user.UserMapper 接口中，新增如下方法：
```
public List<User> getList();
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getList" resultType="pojo.User">
 SELECT
     *
 FROM
     smbms_user
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getList() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     List<User> users = session.getMapper(UserMapper.class).getList();
     for (User user : users) {
         System.out.println(user.getUserCode() + ":"
                 + user.getUserName());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

模糊查询

在 dao.user.UserMapper 接口中，新增如下方法：

public List<User> getListByName(@Param("userName") String userName);

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getListByName" resultType="pojo.User">
 SELECT
     *
 FROM
     smbms_user
 WHERE
     userName LIKE CONCAT('%',#{userName},'%')
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getListByName() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     List<User> users = session.getMapper(UserMapper.class).getListByName("孙");
     for (User user : users) {
         System.out.println(user.getUserCode() + ":"
                 + user.getUserName());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

分页查询

在 dao.user.UserMapper 接口中，新增如下方法：

public List<User> getPage(@Param("currentPageNo")Integer currentPageNo,  @Param("pageSize")Integer pageSize);

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getPage" resultType="User">
 SELECT
     *
 FROM
     smbms_user
 LIMIT
     #{currentPageNo},#{pageSize}
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getPage(){
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     Integer currentPageNo = 2;
     Integer pageSize = 2;
     List<User> users = session.getMapper(UserMapper.class).getPage((currentPageNo - 1) * pageSize
             , pageSize);
     for (User user : users) {
         System.out.println(user.getUserCode() + ":" + user.getUserName());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

多表联合查询

主表引用附表字段

在 pojo.User 类中，新增如下属性和方法：

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

在 dao.user.UserMapper 接口中，新增如下方法：
```
public List<User> getListJoinRole();
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getListJoinRole" resultType="pojo.User">
 SELECT
     u.userCode,u.userName,r.roleName
 FROM
     smbms_user u,smbms_role r
 WHERE
     u.userRole = r.id
</select>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getListJoinRole() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     List<User> users = session.getMapper(UserMapper.class).getListJoinRole();
     for (User user : users) {
         System.out.println(user.getUserCode() + ":"
                 + user.getUserName() + ":" + user.getRoleName());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

主表引用附表POJO对象

在 pojo 包中创建 role 表的映射类 Role

package pojo;
import java.util.Date;
public class Role {
 private Integer id;
 private String roleCode;
 private String roleName;
 private Integer createdBy;
 private Date createDate;
 private Integer modifyBy;
 private Date modifyDate;
 public Integer getId() {
     return id;
 }
 public void setId(Integer id) {
     this.id = id;
 }
 public String getRoleCode() {
     return roleCode;
 }
 public void setRoleCode(String roleCode) {
     this.roleCode = roleCode;
 }
 public String getRoleName() {
     return roleName;
 }
 public void setRoleName(String roleName) {
     this.roleName = roleName;
 }
 public Integer getCreatedBy() {
     return createdBy;
 }
 public void setCreatedBy(Integer createdBy) {
     this.createdBy = createdBy;
 }
 public Date getCreateDate() {
     return createDate;
 }
 public void setCreateDate(Date createDate) {
     this.createDate = createDate;
 }
 public Integer getModifyBy() {
     return modifyBy;
 }
 public void setModifyBy(Integer modifyBy) {
     this.modifyBy = modifyBy;
 }
 public Date getModifyDate() {
     return modifyDate;
 }
 public void setModifyDate(Date modifyDate) {
     this.modifyDate = modifyDate;
 }
}

在 pojo.User 类中，新增如下属性和方法：

private Role role;
public Role getRole() {
 return role;
}
public void setRole(Role role) {
 this.role = role;
}

在 dao.user.UserMapper 接口中，新增如下方法：
```
public List<User> getListJoinRole();
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getListJoinRole" resultMap="getListJoinRoleResultMap">
 SELECT
 u.userCode,u.userName,r.roleName
 FROM
 smbms_user u,smbms_role r
 WHERE
 u.userRole = r.id
</select>
<resultMap type="pojo.User" id="getListJoinRoleResultMap">
 <result column="userCode" property="userCode" />
 <result column="userName" property="userName" />
 <association property="role" javaType="pojo.Role">
     <result column="roleName" property="roleName" />
 </association>
</resultMap>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getListJoinRole() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     List<User> users = session.getMapper(UserMapper.class).getListJoinRole();
     for (User user : users) {
         System.out.println(user.getUserCode() + ":"
                 + user.getUserName() + ":" + user.getRoleName());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

主表引用附表POJO对象集合

在 pojo 包中创建 address 表的映射类 Address

package pojo;
import java.util.Date;
public class Address {
 private Integer id;
 private String contact;
 private String addressDesc;
 private String postCode;
 private String tel;
 private Integer createdBy;
 private Date createdDate;
 private Integer modifyBy;
 private Date modifyDate;
 private Integer userId;
 public Integer getId() {
     return id;
 }
 public void setId(Integer id) {
     this.id = id;
 }
 public String getContact() {
     return contact;
 }
 public void setContact(String contact) {
     this.contact = contact;
 }
 public String getAddressDesc() {
     return addressDesc;
 }
 public void setAddressDesc(String addressDesc) {
     this.addressDesc = addressDesc;
 }
 public String getPostCode() {
     return postCode;
 }
 public void setPostCode(String postCode) {
     this.postCode = postCode;
 }
 public String getTel() {
     return tel;
 }
 public void setTel(String tel) {
     this.tel = tel;
 }
 public Integer getCreatedBy() {
     return createdBy;
 }
 public void setCreatedBy(Integer createdBy) {
     this.createdBy = createdBy;
 }
 public Date getCreatedDate() {
     return createdDate;
 }
 public void setCreatedDate(Date createdDate) {
     this.createdDate = createdDate;
 }
 public Integer getModifyBy() {
     return modifyBy;
 }
 public void setModifyBy(Integer modifyBy) {
     this.modifyBy = modifyBy;
 }
 public Date getModifyDate() {
     return modifyDate;
 }
 public void setModifyDate(Date modifyDate) {
     this.modifyDate = modifyDate;
 }
 public Integer getUserId() {
     return userId;
 }
 public void setUserId(Integer userId) {
     this.userId = userId;
 }
}

在 pojo.User 类中，新增如下属性和方法：

private List<Address> addresses;
public List<Address> getAddresses() {
 return addresses;
}
public void setAddresses(List<Address> addresses) {
 this.addresses = addresses;
}

在 dao.user.UserMapper 接口中，新增如下方法：
```
public User getUserAddress(@Param("id") int id);
```

在 dao.user.UserMapper.xml 映射文件中添加如下代码：

<select id="getUserAddress" resultMap="getUserAddressMap">
 SELECT
     a.contact, a.addressDesc, u.userName
 FROM
     smbms_address a, smbms_user u
 WHERE
     a.userId = u.id AND
     userId = 1
</select>
<resultMap type="pojo.User" id="getUserAddressMap">
 <result column="userName" property="userName"/>
 <collection property="addresses" ofType="pojo.Address">
     <result column="contact" property="contact"/>
     <result column="addressDesc" property="addressDesc"/>
 </collection>
</resultMap>

在单元测试文件 test.user.UserTest 类中添加如下方法：

@Test
public void getUserAddress() {
 SqlSession session = null;
 try {
     session = MybatisUtil.openSession();
     User user = session.getMapper(UserMapper.class).getUserAddress(1);
     System.out.println(user.getUserName());
     for (Address address : user.getAddresses()) {
         System.out.println(address.getContact() + ":" + address.getAddressDesc());
     }
 } finally {
     MybatisUtil.closeSession(session);
 }
}

MyBatis3 结果集映射机制

POJO类属性名与数据表字段名不一致的情况

在 pojo 包下创建类 pojo.Role2，并键入如下代码：

package pojo;
import java.util.Date;
public class Role2 {
 private Integer id;
 private String roleCode;
 private String name; // 表字段 roleName
 private Integer createdBy;
 private Date creationDate;
 private Integer modifyBy;
 private Date modifyDate;
 public Integer getId() {
     return id;
 }
 public void setId(Integer id) {
     this.id = id;
 }
 public String getRoleCode() {
     return roleCode;
 }
 public void setRoleCode(String roleCode) {
     this.roleCode = roleCode;
 }
 public String getName() {
     return name;
 }
 public void setName(String name) {
     this.name = name;
 }
 public Integer getCreatedBy() {
     return createdBy;
 }
 public void setCreatedBy(Integer createdBy) {
     this.createdBy = createdBy;
 }
 public Date getCreationDate() {
     return creationDate;
 }
 public void setCreationDate(Date creationDate) {
     this.creationDate = creationDate;
 }
 public Integer getModifyBy() {
     return modifyBy;
 }
 public void setModifyBy(Integer modifyBy) {
     this.modifyBy = modifyBy;
 }
 public Date getModifyDate() {
     return modifyDate;
 }
 public void setModifyDate(Date modifyDate) {
     this.modifyDate = modifyDate;
 }
}

在 src 目录下创建包 dao.role

在包 dao.role 下创建 RoleMapper.java 接口，并键入如下代码：

package dao.role;
import java.util.List;
import pojo.Role2;
public interface RoleMapper {
 public List<Role2> getRoleList();
}

在包 dao.role 下创建 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="dao.role.RoleMapper">
 <select id="getRole" resultMap="getRoleMap">
     SELECT * FROM smbms_role
 </select>
 <resultMap type="pojo.Role2" id="getRoleMap">
     <result property="id" column="id" />
     <result property="roleCode" column="roleCode" />
     <result property="name" column="roleName" />
     <result property="createdBy" column="createdBy" />
     <result property="creationDate" column="creationDate" />
     <result property="modifyBy" column="modifyBy" />
     <result property="modifyDate" column="modifyDate" />
 </resultMap>
</mapper>

在 test 目录下创建包 test.role

在 test.role 目录下创建单元测试类 RoleTest.java，并键入如下代码：

package smbms.role.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Role2;
import dao.role.RoleMapper;
import utils.MybatisUtil;
public class RoleTest {
 @Test
 public void getRoleListTest() {
     SqlSession session = null;
     try {
         session = MybatisUtil.openSession();
         List<Role2> roleList = session.getMapper(RoleMapper.class)
                 .getRoleList();
         for (Role2 role2 : roleList) {
         }
     } finally {
         MybatisUtil.closeSession(session);
     }
 }
}

自动映射行为

当字段名和属性名部分相同时，可以通过 autoMappingBehavior 配置项类指定是否需要自动映射

取消自动映射

NONE 表示取消自动映射

<settings>
    <setting name="autoMappingBehavior" value="NONE"/>
</settings>

完全自动映射

FULL 会自动映射任意复杂的结果集（无论是否嵌套）

<settings>
    <setting name="autoMappingBehavior" value="FULL"/>
</settings>

部分自动映射

PARTIAL 只会自动映射没有定义嵌套结果集映射的结果集

<settings>
    <setting name="autoMappingBehavior" value="PARTIAL"/>
</settings>

MyBatis3 动态SQL映射

if

接口

public List<User> getUserList(@Param("rid")Integer rid);

映射

<select id="getUserList" resultType="user">
    SELECT * FROM smbms_user
    <if test="rid != null">
        WHERE userRole=#{rid}
    </if>
</select>

测试

List<User> users = session.getMapper(UserMapper.class).getUserList(2);
List<User> users = session.getMapper(UserMapper.class).getUserList(null);

where-if / trim-if

接口

public List<User> getUserList(@Param("rid")Integer rid, @Param("name")String name);

映射

<select id="getUserList" resultType="User">
    SELECT * FROM smbms_user
    <where>
        <if test="name != null and name != ''">
            AND userName LIKE CONCAT('%',#{name},'%')
        </if>
        <if test="rid != null">
            AND userRole = #{rid}
        </if>
    </where>
</select>
<!-- 或 -->
<select id="getUserList" resultType="User">
    SELECT * FROM smbms_user
    <trim prefix="where" prefixOverrides="and|or">
        <if test="name != null and name != ''">
            AND userName LIKE CONCAT('%',#{name},'%')
        </if>
        <if test="rid != null">
            AND userRole = #{rid}
        </if>
    </trim>
</select>

测试

List<User> users1 = sqlSession.getMapper(UserMapper.class).getUserList(null, null);
List<User> users2 = sqlSession.getMapper(UserMapper.class).getUserList(null, 3);
List<User> users3 = sqlSession.getMapper(UserMapper.class).getUserList("孙", null);
List<User> users4 = sqlSession.getMapper(UserMapper.class).getUserList("孙", 3);

foreach

接口

public List<User> getUserListInId(@Param("rids")Integer[] rids);

映射

<select id="getUserListInId" resultType="pojo.User">
    SELECT * FROM smbms_user WHERE id in
    <foreach collection="rids" item="rid" open="(" separator="," close=")">
        #{rid}
    </foreach>
</select>

测试

Integer[] rids = {1,3,5};
List<User> users = sqlSession.getMapper(UserMapper.class).getUserListInId(rids);

choose-when-otherwise

接口

public List<User> getUserList(@Param("rid")Integer rid, @Param("name")String name);

映射

<select id="getUserList" resultType="user">
    SELECT * FROM smbms_user
        <choose>
            <when test="rid != null">
                WHERE userRole = #{rid}
            </when>
            <when test="name != null">
                WHERE userName LIKE CONCAT('%',#{name}, '%')
            </when>
        </choose>
</select>

测试

session.getMapper(UserMapper.class).getUserList(2,"孙");
session.getMapper(UserMapper.class).getUserList(null,"孙");

set-if / trim-if

接口

public Integer setUser(User user);

映射

<update id="setUser">
    UPDATE smbms.smbms_user
        <set>
            <if test="userCode != null">userCode = #{userCode} , </if>
            <if test="userName != null">userName = #{userName}, </if>
            <if test="userPassword != null">userPassword = #{userPassword} ,</if>
            <if test="gender != null">gender = #{gender} , </if>
            <if test="birthday != null">birthday = #{birthday} , </if>
            <if test="phone != null">phone = #{phone} , </if>
            <if test="address != null">address = #{address} , </if>
            <if test="userRole != null">userRole = #{userRole} ,</if>
            <if test="createdBy != null">createdBy = #{createdBy} , </if>
            <if test="creationDate != null">creationDate = #{creationDate} , </if>
            <if test="modifyBy != null">modifyBy = #{modifyBy} , </if>
            <if test="modifyDate != null">modifyDate = #{modifyDate} , </if>
        </set>
        WHERE id = #{id}
</update>
<!-- 或 -->
<update id="saveUser">
    UPDATE smbms_user
    <trim prefix="set" suffixOverrides="," suffix="WHERE id=#{id}">
        <if test="userCode != null">userCode = #{userCode} , </if>
        <if test="userName != null">userName = #{userName}, </if>
        <if test="userPassword != null">userPassword = #{userPassword} ,</if>
        <if test="gender != null">gender = #{gender} , </if>
        <if test="birthday != null">birthday = #{birthday} , </if>
        <if test="phone != null">phone = #{phone} , </if>
        <if test="address != null">address = #{address} , </if>
        <if test="userRole != null">userRole = #{userRole} ,</if>
        <if test="createdBy != null">createdBy = #{createdBy} , </if>
        <if test="creationDate != null">creationDate = #{creationDate} , </if>
        <if test="modifyBy != null">modifyBy = #{modifyBy} , </if>
        <if test="modifyDate != null">modifyDate = #{modifyDate} , </if>
    </trim>
</update>

测试

User user = new User();
user.setId(1);
user.setUserCode("admin1");
user.setUserName("管理员");

posted @ 2021-01-05 15:37 编码小高阅读(66) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

编码小高

MyBatis3 开发环境搭建

MyBatis3 开发环境搭建

MyBatis3 工具封装类

MyBatis3 配置数据源

静态配置

动态配置

MyBatis3 日志实现

MyBatis3 通过接口进行数据库访问

MyBatis3 映射器的注册

直接注册映射器文件

通过包名自动注册

MyBatis3 创建POJO类

什么是POJO类

创建POJO类

数据库类型与java类型对照表

MyBatis3 向SQL语句传参

传入一个参数

传入多个参数

POJO对象实现

Map对象实现

注解实现

MyBatis3 类型别名

给具体的类取别名

给指定包下的类取别名

MyBatis3 获取查询结果

单表查询

查询单个值

查询一行数据

查询多行数据

模糊查询

分页查询

多表联合查询

主表引用附表字段

主表引用附表POJO对象

主表引用附表POJO对象集合

MyBatis3 结果集映射机制

POJO类属性名与数据表字段名不一致的情况

自动映射行为

取消自动映射

完全自动映射

部分自动映射

MyBatis3 动态SQL映射

if

where-if / trim-if

foreach

choose-when-otherwise

set-if / trim-if

公告