mybatis基于xml和注解查询

一. Mybatis介绍

1.mybatis基础环境搭建

1. 创建数据库

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_emp
-- ----------------------------
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(50) DEFAULT NULL,
  `emp_salary` double(15,5) DEFAULT NULL,
  `emp_age` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_emp
-- ----------------------------
INSERT INTO `t_emp` VALUES ('1', 'zzhua', '15000.00000', '25');
INSERT INTO `t_emp` VALUES ('2', 'ls', '13000.00000', '24');
INSERT INTO `t_emp` VALUES ('3', 'zj', '14000.00000', '22');

2. 创建maven工程，导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zzhua</groupId>
    <artifactId>common-mapper</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--版本管理-->
    <properties>
        <spring.version>5.0.2.RELEASE</spring.version>
        <mybatis.version>3.5.2</mybatis.version>
        <mybatis.spring.version>1.3.3</mybatis.spring.version>
        <mysql.version>5.1.47</mysql.version>
        <druid.version>1.0.9</druid.version>
        <log4j.version>1.2.17</log4j.version>
        <pagehelper.version>5.1.10</pagehelper.version>
    </properties>


    <dependencies>
        <!--导入spring依赖开始-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--导入spring依赖结束-->

        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <!--数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!--导入mybatis相关依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis.spring.version}</version>
        </dependency>

         <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>${pagehelper.version}</version>
        </dependency>

        <!--log4j日志-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${log4j.version}</version>
        </dependency>

        <!--spring测试相关开始-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--spring测试相关结束-->

    </dependencies>
    
</project>

3. 编写配置文件

1. db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis-demo?useUnicode=true&characterEncoding=utf8&useSSL=true
user=root
password=root

#maxActive \u6700\u5927\u7684\u8FDE\u63A5\u6570
maxActive=20
#initialSize \u521D\u59CB\u5316\u8FDE\u63A5\u7684\u4E2A\u6570
initialSize=1
#maxWait \u6700\u5927\u7B49\u5F85\u65F6\u95F4
maxWait=60000
#minIdle \u6700\u5C0F\u6D3B\u8DC3\u8FDE\u63A5
minIdle=1

filters=stat,log4j,wall

2. log4j.properties打印sql配置

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration... 
log4j.logger.com.zzhua.dao=DEBUG 
# Console output...
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打印sql语句中dao所在的包

3. application-dao.xml整合mybatis相关配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--
        dao层的配置：核心是要产生 Mapper代理类对象
        1.引入数据库配置信息
        2.数据源配置
        3.SqlSessionFactory
        4.产生Mapper接口的代理类对象
     -->
    <!-- 1.引入数据库配置信息 -->
    <context:property-placeholder location="classpath:db.properties"  system-properties-mode="FALLBACK"/>
    <!--2.数据源配置 -->
    <!-- 	 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
             <property name="driverClassName" value="${driver}"></property>
             <property name="url" value="${url}"></property>
             <property name="username" value="${user}"></property>
             <property name="password" value="${password}"></property>
         </bean> -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${driver}"></property>
        <property name="url" value="${url}"></property>
        <property name="username" value="${user}"></property>
        <property name="password" value="${password}"></property>
        <property name="maxActive" value="${maxActive}"></property>
        <property name="initialSize" value="${initialSize}"></property>
        <property name="maxWait" value="${maxWait}"></property>
        <property name="filters" value="${filters}"></property>
    </bean>


    <!-- 3.SqlSessionFactory -->
    <bean id="sqlSessionFactory"  class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="mapperLocations" value="classpath*:mybatis/mapper/*.xml"></property>
        <property name="configLocation" value="classpath:mybatis/mybatis.cfg.xml"></property>
    </bean>

    <!-- 4.产生Mapper接口的代理类对象  -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 需要生成代理类对象的mapper接口包 -->
        <property name="basePackage" value="com.zzhua.dao"></property>
        <!-- sqlSessionFactory 的name  用于为代理类中生成SqlSession -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>


</beans>

4. 编写mybatis.cfg.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">
<!-- mybatis的核心配置文件 -->
<configuration>
    <!-- 配置 mybatis 设置  -->
    <settings>
        <!-- 配置 日志-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!-- 配置别名 -->
    <typeAliases>
        <package name="com.zzhua.entity"/>
    </typeAliases>
    <!-- 配置分页插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor" >
            <property name="helperDialect" value="mysql"/>
        </plugin>
    </plugins>

</configuration>

5. 生成实体类、mapper接口、mappe.xml

public class Emp{              /*省略setter、getter和toString方法*/
    private Integer empId;
    private String empName;
    private Double empSalary;
    private Integer empAge;
}

public interface EmpDao {
    int deleteByPrimaryKey(Integer empId);

    int insert(Emp record);

    int insertSelective(Emp record);

    Emp selectByPrimaryKey(Integer empId);

    List<Emp> selectAll(Emp emp);

    int updateByPrimaryKeySelective(Emp record);

    int updateByPrimaryKey(Emp record);
}

<?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.zzhua.dao.EmpDao">
  <resultMap id="BaseResultMap" type="com.zzhua.entity.Emp">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
    <result column="emp_age" jdbcType="INTEGER" property="empAge" />
  </resultMap>
  <sql id="Base_Column_List">
    emp_id, emp_name, emp_salary, emp_age
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_emp
    where emp_id = #{empId,jdbcType=INTEGER}
  </select>
  <select id="selectAll" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_emp
    <where>
      <if test="empId!=null">
        and emp_id = #{empId}
      </if>
      <if test="empName!=null and empName!=''">
        and emp_name = #{empName}
      </if>
      <if test="emp_salary!=null">
        and emp_salary = #{empSalary}
      </if>
      <if test="emp_age!=null">
        and empAge = #{empAge}
      </if>
    </where>
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_emp
    where emp_id = #{empId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" keyColumn="emp_id" keyProperty="empId" parameterType="com.zzhua.entity.Emp" useGeneratedKeys="true">
    insert into t_emp (emp_name, emp_salary, emp_age
      )
    values (#{empName,jdbcType=VARCHAR}, #{empSalary,jdbcType=DOUBLE}, #{empAge,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" keyColumn="emp_id" keyProperty="empId" parameterType="com.zzhua.entity.Emp" useGeneratedKeys="true">
    insert into t_emp
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="empName != null">
        emp_name,
      </if>
      <if test="empSalary != null">
        emp_salary,
      </if>
      <if test="empAge != null">
        emp_age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="empName != null">
        #{empName,jdbcType=VARCHAR},
      </if>
      <if test="empSalary != null">
        #{empSalary,jdbcType=DOUBLE},
      </if>
      <if test="empAge != null">
        #{empAge,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zzhua.entity.Emp">
    update t_emp
    <set>
      <if test="empName != null">
        emp_name = #{empName,jdbcType=VARCHAR},
      </if>
      <if test="empSalary != null">
        emp_salary = #{empSalary,jdbcType=DOUBLE},
      </if>
      <if test="empAge != null">
        emp_age = #{empAge,jdbcType=INTEGER},
      </if>
    </set>
    where emp_id = #{empId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zzhua.entity.Emp">
    update t_emp
    set emp_name = #{empName,jdbcType=VARCHAR},
      emp_salary = #{empSalary,jdbcType=DOUBLE},
      emp_age = #{empAge,jdbcType=INTEGER}
    where emp_id = #{empId,jdbcType=INTEGER}
  </update>
</mapper>

6. 测试Mapper

package com.zzhua;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.zzhua.dao.EmpDao;
import com.zzhua.entity.Emp;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private EmpDao empDao;

    @Test
    public void testEmpDao(){
        Emp emp = empDao.selectByPrimaryKey(1);
        System.out.println(emp);
    }

    @Test
    public void testPage(){
        Page<Emp> empPage = PageHelper.startPage(1, 2);
        List<Emp> empList = empDao.selectAll(null);

        System.out.println("一共有多少页?"+empPage.getPages());
        System.out.println("当前第几页？"+empPage.getPageNum());
        System.out.println("每页多少条？"+empPage.getPageSize());
        System.out.println("一共多少条"+empPage.getTotal());
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        System.out.println(empPage.getResult());
    }

}

7. 使用Mybatis注解

基于以上工程，在EmpDao中直接使用注解，无须其它扫描配置，即可

@Select("select * from t_emp")
@Results({ // @Results设置结果集与属性映射关系
    @Result(id = true,column = "emp_id",property = "empId"),
    @Result(column = "emp_name",property = "empName"),
    @Result(column = "emp_salary",property = "empSalary"),
    @Result(column = "emp_age",property = "empAge")
})
List<Emp> getAll();

@Options(useGeneratedKeys = true, keyProperty = "empId") // 返回主键给参数emp
@Insert("insert into t_emp(emp_name,emp_salary,emp_age)values(#{empName},#{empSalary},#{empAge})")
int addEmp(Emp emp);

测试通过

@Autowired
private EmpDao empDao;

@Test
public void test02(){
    List<Emp> all = empDao.getAll();
    System.out.println(all);
}

8. 环境搭建总结

1. sqlSessionFactory
	- dataSource            数据源
	- mapper.xml            sql语句
	- mybatis.cfg.xml       mybatis核心配置文件
2. MapperScannerConfigurer  【sql语句可以在mapper接口注解里】
	- sqlSessionFacotry     【可以从sqlsessionFactory的mapper.xml里】
	- mapper接口

2. 级联查询

创建数据库

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_dept
-- ----------------------------
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
  `d_id` int(11) NOT NULL AUTO_INCREMENT,
  `d_name` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`d_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- ----------------------------
-- Records of t_dept
-- ----------------------------
INSERT INTO `t_dept` VALUES ('1', '开发部');
INSERT INTO `t_dept` VALUES ('2', '业务部');

生成实体类

public class Dept {             /*省略setter、getter和toString方法*/
    private Integer dId;
    private String dName;
    
}

生成mapper.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.zzhua.dao.DeptDao">
  <resultMap id="BaseResultMap" type="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    <!--@Table t_dept-->
    <id column="d_id" jdbcType="INTEGER" property="dId" />
    <result column="d_name" jdbcType="VARCHAR" property="dName" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    d_id, d_name
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from t_dept
    where d_id = #{dId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--@mbg.generated-->
    delete from t_dept
    where d_id = #{dId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    insert into t_dept (d_id, d_name)
    values (#{dId,jdbcType=INTEGER}, #{dName,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    insert into t_dept
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="dId != null">
        d_id,
      </if>
      <if test="dName != null">
        d_name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="dId != null">
        #{dId,jdbcType=INTEGER},
      </if>
      <if test="dName != null">
        #{dName,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    update t_dept
    <set>
      <if test="dName != null">
        d_name = #{dName,jdbcType=VARCHAR},
      </if>
    </set>
    where d_id = #{dId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    update t_dept
    set d_name = #{dName,jdbcType=VARCHAR}
    where d_id = #{dId,jdbcType=INTEGER}
  </update>
</mapper>

生成mapper接口

public interface DeptDao {
    int deleteByPrimaryKey(Integer dId);

    int insert(Dept record);

    int insertSelective(Dept record);

    Dept selectByPrimaryKey(Integer dId);

    int updateByPrimaryKeySelective(Dept record);

    int updateByPrimaryKey(Dept record);
}

1.一对一

一个员工对应一个部门： 查询一个员工，同时把他的部门查询出来

1. xml实现

public class Emp{

    private String empName;

    private Double empSalary;

    private Integer empAge;

    private Dept dept;    // 添加部门属性
}

​ EmpMapper.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.zzhua.dao.EmpDao">
    <!-- 员工原本的查询结果列名与实体类属性映射规则 -->
  <resultMap id="BaseResultMap" type="com.zzhua.entity.Emp">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
    <result column="emp_age" jdbcType="INTEGER" property="empAge" />
  </resultMap>
    
	<!-- 继承原来的映射规则，扩展规则-->
    <!-- 使用association将查询结果映射成员工的部门属性-->
  <resultMap id="extendedMap" type="com.zzhua.entity.Emp" extends="BaseResultMap">
    <association property="dept" javaType="com.zzhua.entity.Dept">
      <id column="d_id" property="dId"/>
      <result column="d_name" property="dName"/>
    </association>
  </resultMap>
    
    <!-- 指定使用的映射规则 extendedMap-->
  <select id="selectByPrimaryKey" resultMap="extendedMap">
    select * from t_emp e left join t_dept d on e.emp_id = d.d_id
    where e.emp_id = #{empId}
  </select>

</mapper>

EmpDao

public interface EmpDao{
    Emp selectByPrimaryKey(@Param("empId") Integer empId);
}

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private EmpDao empDao;

    @Test
    public void test05(){
        Emp emp = empDao.selectByPrimaryKey(1);
        System.out.println(emp);
    }
}

2.注解实现

EmpDao

@Select("select * from t_emp e left join t_dept d on e.emp_id = d.d_id"+ 
        "where e.emp_id = #{empId}")
@Results(value = {
        @Result(id = true, column = "emp_id", property = "empId"),
        @Result(column = "emp_name", property = "empName"),
        @Result(column = "emp_salary", property = "empSalary"),
        @Result(column = "emp_age", property = "empAge"),
   						 //根据查询结果列名d_id，去查询部门，再把结果设置给emp的部门属性
        @Result(column = "d_id",property = "dept", 
                one = @One(select = "com.zzhua.dao.DeptDao.selectById",
                           fetchType = FetchType.EAGER))
})
Emp selectById(Integer empId);

DeptDao

@Select("select * from t_dept where d_id = #{id}")
@Results({
        @Result(id = true,column = "d_id",property ="dId"),
        @Result(id = true,column = "d_name",property ="dName")
})
Dept selectById(@Param("id") Integer id);

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private EmpDao empDao;

    @Test
    public void test05(){
        Emp emp = empDao.selectById(1);
        System.out.println(emp);
    }
}

2.一对多

一个部门对应多个员工：查询部门，同时把这个部门下的所有员工查询出来

1.xml实现

Dept

public class Dept {
    private Integer dId;

    private String dName;
    
    private List<Emp> empList;  // 添加员工的List集合

}

DeptMapper.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.zzhua.dao.DeptDao">
    <!--部门查询结果与实体类的属性映射-->
  <resultMap id="BaseResultMap" type="com.zzhua.entity.Dept">
    <!--@mbg.generated-->
    <!--@Table t_dept-->
    <id column="d_id" jdbcType="INTEGER" property="dId" />
    <result column="d_name" jdbcType="VARCHAR" property="dName" />
  </resultMap>
    
    <!-- 继承原来的映射规则，扩展映射规则-->
  <resultMap id="ExtendedResultMap" type="com.zzhua.entity.Dept" extends="BaseResultMap">
   <collection property="empList" ofType="com.zzhua.entity.Emp" >
     <id column="emp_id" property="empId"/>
     <result column="emp_name" property="empName"/>
     <result column="emp_salary" property="empSalary"/>
     <result column="emp_age" property="empAge"/>
   </collection>
  </resultMap>
                                       <!--对mybatis的查询结果，使用扩展的映射规则-->
  <select id="selectByPrimaryKey"            
          parameterType="java.lang.Integer" resultMap="ExtendedResultMap">
    select *
    from t_dept d left join t_emp e on d.d_id = e.d_id
    where d.d_id = #{dId,jdbcType=INTEGER}
  </select>
    
</mapper>

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private DeptDao deptDao;

    @Test
    public void test05(){
        Dept dept = deptDao.selectByPrimaryKey(1);
        System.out.println(dept);
    }
}

2.注解实现

DeptDao

@Select("select * from t_dept where d_id = #{id} ")
@Results({
        @Result(id = true,column = "d_id",property = "dId"),
        @Result(column = "d_name",property = "dName"),
        @Result(column = "d_id",property = "empList",
                <!--根据部门id，查询这个部门下的所有员工集合，再把结果设置给部门的empList属性-->
                many = @Many(select = "com.zzhua.dao.EmpDao.selectEmpsByDid",
                             fetchType = FetchType.EAGER))
})
Dept selectById(@Param("id") Integer id);

EmpDao

@Select("select * from t_emp where d_id = #{id} ")
@Results({
        @Result(id = true, column = "emp_id", property = "empId"),
        @Result(column = "emp_name", property = "empName"),
        @Result(column = "emp_salary", property = "empSalary"),
        @Result(column = "emp_age", property = "empAge")
})
List<Emp> selectEmpsByDid(@Param("id") Integer id);

测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private DeptDao deptDao;

    @Test
    public void test05(){
        Dept dept = deptDao.selectById(1);
        System.out.println(dept);
    }
}

3.多对多

Customer

public class Customer {
    private Integer id;
    private String customerName;
    private List<Goods> goodsList;
}

Goods

public class Goods {
    private Integer id;
    private String goodsName;
    private List<Customer> customerList;
}

CustomerMapper

public interface CustomerMapper {
    
    Customer getById(Integer id);

    List<Customer> getBy2Id(@Param("id1") int id1, @Param("id2") int id2);
}

CustomerMapper.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.zzhua.dao.CustomerMapper">
  <resultMap id="BaseResultMap" type="com.zzhua.entity.Customer">
    <!--@mbg.generated-->
    <!--@Table t_customer-->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="customer_name" jdbcType="VARCHAR" property="customerName" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, customer_name
  </sql>

  <resultMap id="ExtendedMap" type="Customer" extends="BaseResultMap">
    <collection property="goodsList" ofType="com.zzhua.entity.Goods">
      <result column="gid" property="id"/>
      <result column="goods_name" property="goodsName"/>
    </collection>
  </resultMap>
    
  <select id="getById" resultMap="ExtendedMap">
    select * from t_customer c,t_cg cg,t_goods g
    where c.id = cg.cid and g.id = cg.gid and c.id = #{id};
  </select>

  <select id="getBy2Id" resultMap="ExtendedMap">
    select * from t_customer c,t_cg cg,t_goods g
    where c.id = cg.cid and g.id = cg.gid and (c.id = #{param1} or c.id = #{param2})
  </select>
    
</mapper>

测试

1.getById查询结果

​ 顾客表 t_custome 顾客购买商品的记录表 t_cg、 商品表 t_goods

顾客ID	顾客名字	顾客ID	商品ID	商品ID	商品名称
id	customer_name	c_id	g_id	id1	goods_name
1	a	1	1	1	笔记本电脑
1	a	1	2	2	移动硬盘
1	a	1	3	3	鼠标

// 一个客户买了3件商品，将3种商品封装到这个顾客的商品列表里
@Test
public void test007() {
    Customer customer = customerMapper.getById(1);
    System.out.println("---------------------------");
    System.out.println(customer.getCustomerName());
    for (Goods goods : customer.getGoodsList()) {
        System.out.println(goods);
    }
    System.out.println("---------------------------");
}
// 测试结果
---------------------------
a
Goods{id=1, goodsName='笔记本电脑', customerList=null}
Goods{id=2, goodsName='移动硬盘', customerList=null}
Goods{id=3, goodsName='鼠标', customerList=null}
---------------------------

2.getBy2Id查询结果

顾客ID	顾客名字	顾客ID	商品ID	商品ID	商品名称
id	customer_name	c_id	g_id	id1	goods_name
1	a	1	1	1	笔记本电脑
1	a	1	2	2	移动硬盘
1	a	1	3	3	鼠标
2	b	2	1	1	笔记本电脑
2	b	2	2	2	移动硬盘

// 查询了2位顾客，他们分别买了不同的商品；将他们各自的商品封装到他们的商品集合属性里
@Test
public void test008() {
    List<Customer> customerList = customerMapper.getBy2Id(1, 2);
    for (Customer customer : customerList) {
        System.out.println("---------------------------");
        System.out.println(customer.getCustomerName());
        for (Goods goods : customer.getGoodsList()) {
            System.out.println(goods);
        }
        System.out.println("---------------------------");
    }

}

// 测试结果
---------------------------
a
Goods{id=1, goodsName='笔记本电脑', customerList=null}
Goods{id=2, goodsName='移动硬盘', customerList=null}
Goods{id=3, goodsName='鼠标', customerList=null}
---------------------------
---------------------------
b
Goods{id=2, goodsName='移动硬盘', customerList=null}
Goods{id=3, goodsName='鼠标', customerList=null}
---------------------------

4.总结

1.在mybatis中，核心就是先用sql语句，查询出结果。一定要先把sql语句写出来，获取查询结果集。

2.查询结果出来后，剩下的就是指定封映射装的规则。

• 简单的就是直接使用result指定，sql查询结果后的表的字段，与实体类的属性映射
• 稍微复杂点的就是使用association、collection指定封装规则

3.延迟加载

• 延迟加载也叫懒加载，在查询的时候，有时候没有必要用到其它的信息，这时，可以用延迟加载。

• 比如查询员工信息的时候，并不需要查询员工对应的部门信息，而我们的sql是通过员工表和部门表连接查询得到结果集。将结果集封装成员工对象。在这个过程里，多了一个连接查询的步骤。因此，可以使用延迟加载，先只查询员工信息(不连接部门表)，在需要查询该员工对应的部门信息时，再执行查询部门的sql语句

// 员工查询接口
Emp getById(@Param("id") Integer id);

// 对应的resultMap和sql
<resultMap id="BaseResultMap" type="com.zzhua.entity.Emp">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
    <result column="emp_age" jdbcType="INTEGER" property="empAge" />
</resultMap>
    
<resultMap id="LazyLoadMap" type="com.zzhua.entity.Emp" extends="BaseResultMap">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
    <result column="emp_age" jdbcType="INTEGER" property="empAge" />
    
                  <!--在这里设置懒加载-->
    <association property="dept" javaType="com.zzhua.entity.Dept" column="d_id"
                 fetchType="lazy" select="com.zzhua.dao.DeptDao.getByDId" >
    </association>
</resultMap>

                   <!--执行的sql语句-->
<select id="getById" resultMap="LazyLoadMap">
  select * from t_emp where emp_id = #{id}
</select>

测试

@Test
public void test002(){
    Emp emp = empDao.getById(1);
    System.out.println(emp.getEmpName()); // 没有用到部门信息，因此没有加载部门信息
    System.out.println("-------------------------");
    System.out.println(emp.getDept());    // 用到了部门信息，此时再加载
}
// 打印结果
DEBUG [main] - ==>  Preparing: select * from t_emp where emp_id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
zzhua
-------------------------
DEBUG [main] - ==>  Preparing: select * from t_dept where d_id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Dept{dId=1, dName='开发部', empList=null}

4.mybatis缓存

通过使用缓存，可以减少对数据库的访问次数，第一次查询时，将查询结果放入缓存；第二次查询时，直接从缓存中获取数据。mybatis缓存分为一级缓存和二级缓存。

环境准备

• 修改mybatis.cfg.xml，打开详细的sql执行过程日志

<settings>
    <!-- 配置 日志-->
    <!--<setting name="logImpl" value="LOG4J"/>-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

• 添加findById方法

  • EmpDao.xml

  <select id="findById" resultMap="BaseResultMap">
    select * from t_emp where emp_id = #{id}
  </select>
  

  • EmpDao接口

  Emp findById(@Param("id") Integer id);
  

• 测试

  @Test
  public void test002(){
     empDao.findById(1);
     empDao.findById(1);
  }
  // 执行结果，很显然查询了两次，
  // mybatis和spring集成后，每次查询都会创建sqlSession对象
  Creating a new SqlSession // 创建sqlsession
  ==>  Preparing: select * from t_emp where emp_id = ? 
  ==> Parameters: 1(Integer)
  <==    Columns: emp_id, emp_name, emp_salary, emp_age, d_id
  <==        Row: 1, zzhua, 15000.00000, 25, 1
  <==      Total: 1
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@20bd8be5]
  --------------------
  Creating a new SqlSession // 创建sqlsession
  ==>  Preparing: select * from t_emp where emp_id = ? 
  ==> Parameters: 1(Integer)
  <==    Columns: emp_id, emp_name, emp_salary, emp_age, d_id
  <==        Row: 1, zzhua, 15000.00000, 25, 1
  <==      Total: 1
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@72437d8d]
  

1. 一级缓存

配置事务

• 概念

  • sqlsession级别，默认开启

  • sqlsession对象中有一个hashmap用于存储缓存数据，不同的sqlsession之间缓存数据互不影响。

  • 一级缓存的作用域是SqlSession范围级别的，在同一个Sqlsession中，执行两次相同的sql语句时，第一次执行完毕，会将结果保存到缓存中，第二次查询时，会直接从缓存中获取数据。

  • 一旦sqlsession执行了DML(增删改)操作，Mybatis会将缓存数据清空，来保证数据的准确定。

• 使用一级缓存

  mybatis一级缓存是sqlsession级别，并且是默认开启的，但是每次查询都会创建一个sqlSession对象。

  需要开启事务，因为同一个事务中spring使用的是同一个SqlSession，才会走一级缓存

  • spring-tx.xml配置事务

  <?xml version="1.0" encoding="UTF-8"?>
  <beans xmlns="http://www.springframework.org/schema/beans"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
         xmlns:context="http://www.springframework.org/schema/context"
         xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
      <!-- 1,声明事务管理器 -->
      <bean id="transactionManager"class=
            "org.springframework.jdbc.datasource.DataSourceTransactionManager">        
          <property name="dataSource" ref="dataSource"></property>
      </bean>
      
      <!-- 2,启动注解事务 -->
       <tx:annotation-driven/>
      
  </beans>
  

  • 测试

  @RunWith(SpringJUnit4ClassRunner.class)
  @ContextConfiguration(locations = 
                        {"classpath:spring-dao.xml","classpath:spring-service.xml"})
  public class SpringTest {
  
      @Autowired
      private EmpService empService;
  
      @Test
      @Transactional
      public void test002(){
         	empService.findById(1);
          System.out.println("--------------------");
         	empService.findById(1);
      }
  }
  // 执行结果 开启事务后，在同一个事务里，用的是同一个sqlSession，第二次查询走了sqlSession的缓存
  Creating a new SqlSession
  ==>  Preparing: select * from t_emp where emp_id = ? 
  ==> Parameters: 1(Integer)
  <==    Columns: emp_id, emp_name, emp_salary, emp_age, d_id
  <==        Row: 1, zzhua, 15000.00000, 25, 1
  <==      Total: 1
  Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f67593e]
  --------------------
  Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f67593e] from current transaction
  Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f67593e]
  Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f67593e]
  Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f67593e]
  

2. 二级缓存

• 概念

  • 二级缓存默认关闭，需要手动开启
  • 二级缓存是跨SqlSession的，与执行的sql语句的id挂钩，只要执行的sql语句的id一样，二级缓存生效

• 使用二级缓存

  因为二级缓存是跨sqlsession的，所以可以不需要使用事务(即每次查询都会创建一个新的sqlSession)。

  • EmpDao.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.zzhua.dao.EmpDao">
      <resultMap id="BaseResultMap" type="com.zzhua.entity.Emp">
          <id column="emp_id" jdbcType="INTEGER" property="empId" />
          <result column="emp_name" jdbcType="VARCHAR" property="empName" />
          <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
          <result column="emp_age" jdbcType="INTEGER" property="empAge" />
      </resultMap>
      
      <!--开启使用二级缓存-->
   	<cache/>
      
      <select id="findById" resultMap="BaseResultMap">
          select * from t_emp where emp_id = #{id}
      </select>
      
  </mapper>
  

  • mybatis.cfg.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>
          <!-- 配置 日志-->
          <!--<setting name="logImpl" value="LOG4J"/>-->
          <!--打印详细的sql执行过程-->
          <setting name="logImpl" value="STDOUT_LOGGING"/>
          <!--开启二级缓存,此处不配置也可以生效-->        
          <setting name="cacheEnabled" value="true"/>
      </settings>
  
      <typeAliases>
          <package name="com.zzhua.entity"/>
      </typeAliases>
  
      <plugins>
          <plugin interceptor="com.github.pagehelper.PageInterceptor" >
              <property name="helperDialect" value="mysql"/>
          </plugin>
      </plugins>
  
  </configuration>
  

  • 测试

  @RunWith(SpringJUnit4ClassRunner.class)
  @ContextConfiguration(locations = 
                        {"classpath:spring-dao.xml","classpath:spring-tx.xml"})
  
  public class SpringTest {
      
      @Autowired
      private EmpDao empDao;
  
      @Test
  //    @Transactional  // 不开启事务(即每次查询都会创建新的sqlSession)
      public void test002(){
         	empDao.findById(1);
          System.out.println("--------------------");
         	empDao.findById(1);
      }
  }
  
  // 执行结果
  Creating a new SqlSession
  SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a5a4e19] was not registered for synchronization because synchronization is not active
  Cache Hit Ratio [com.zzhua.dao.EmpDao]: 0.0  // 没有从缓存中找到
  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@20435c40] will not be managed by Spring
  ==>  Preparing: select * from t_emp where emp_id = ? 
  ==> Parameters: 1(Integer)
  <==    Columns: emp_id, emp_name, emp_salary, emp_age, d_id
  <==        Row: 1, zzhua, 15000.00000, 25, 1
  <==      Total: 1
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a5a4e19]
  --------------------
  Creating a new SqlSession
  SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a87f8ec] was not registered for synchronization because synchronization is not active
  Cache Hit Ratio [com.zzhua.dao.EmpDao]: 0.5 // 命中缓存
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a87f8ec]
  

整合第三方缓存

• 导入ehcache的依赖

  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-ehcache</artifactId>
      <version>1.0.0</version>
  </dependency>
  <dependency>
      <groupId>net.sf.ehcache</groupId>
      <artifactId>ehcache-core</artifactId>
      <version>2.4.3</version>
  </dependency>
  

• 编写ehcache.xml

  <?xml version="1.0" encoding="UTF-8"?>
  <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd">
      <diskStore/>
      <defaultCache maxElementsInMemory="1000"
                    maxElementsOnDisk="10000000"
                    eternal="false"
                    overflowToDisk="false"
                    timeToIdleSeconds="120"
                    timeToLiveSeconds="120"
                    diskExpiryThreadIntervalSeconds="120"
                    memoryStoreEvictionPolicy="LRU">
      </defaultCache>
  </ehcache>
  

• 开启缓存

  mybatis.cfg.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">
  <!-- mybatis的核心配置文件 -->
  <configuration>
      <!-- 配置 mybatis 设置  -->
      <settings>
          <!-- 配置 日志-->
          <!--<setting name="logImpl" value="LOG4J"/>-->
          <setting name="logImpl" value="STDOUT_LOGGING"/>
          <setting name="cacheEnabled" value="true"/>
      </settings>
      <!-- 配置别名 -->
      <typeAliases>
          <package name="com.zzhua.entity"/>
      </typeAliases>
      <!-- 配置分页插件 -->
      <plugins>
          <plugin interceptor="com.github.pagehelper.PageInterceptor" >
              <property name="helperDialect" value="mysql"/>
          </plugin>
      </plugins>
  
  </configuration>
  

  EmpDao.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.zzhua.dao.EmpDao">
    <cache type="org.mybatis.caches.ehcache.EhcacheCache">
        <!--缓存创建之后，最后一次访问时间至缓存失效的时间间隔-->
      <property name="timeToIdleSeconds" value="3600"/>
        <!--缓存从创建时间起至失效的时间间隔-->
      <property name="timeToLiveSeconds" value="3600"/>
        <!--缓存回收策略,LRU表示移除最近最少使用的对象-->
      <property name="memoryStoreEvictionPolicy" value="LRU"/>
    </cache>
    <resultMap id="BaseResultMap" type="com.zzhua.entity.Emp">
      <id column="emp_id" jdbcType="INTEGER" property="empId" />
      <result column="emp_name" jdbcType="VARCHAR" property="empName" />
      <result column="emp_salary" jdbcType="DOUBLE" property="empSalary" />
      <result column="emp_age" jdbcType="INTEGER" property="empAge" />
    </resultMap>
  
    <select id="findById" resultMap="BaseResultMap">
      select * from t_emp where emp_id = #{id}
    </select>
  
  </mapper>
  

• 实体类不需要实现Serializable接口

• 测试

  @Test
  //@Transactional // 不使用事务
  public void test002() {
      empDao.findById(1);
      System.out.println("--------------------");
      empDao.findById(1);
  }
  
  //执行结果
  Creating a new SqlSession
  SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e2059ae] was not registered for synchronization because synchronization is not active
  Cache Hit Ratio [com.zzhua.dao.EmpDao]: 0.0
  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@1608bcbd] will not be managed by Spring
  ==>  Preparing: select * from t_emp where emp_id = ? 
  ==> Parameters: 1(Integer)
  <==    Columns: emp_id, emp_name, emp_salary, emp_age, d_id
  <==        Row: 1, zzhua, 15000.00000, 25, 1
  <==      Total: 1
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e2059ae]
  --------------------
  Creating a new SqlSession
  SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@74960bfa] was not registered for synchronization because synchronization is not active
  Cache Hit Ratio [com.zzhua.dao.EmpDao]: 0.5
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@74960bfa]
  

5.动态sql

if 标签

if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

<if test="name != null and name != ''">
    and NAME = #{name}
</if>

where 标签

当 name 值为 null 时，查询语句会出现 “WHERE AND” 的情况，解决该情况除了将"WHERE"改为“WHERE 1=1”之外，还可以利用 where标签。

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT
    <where>
        <if test="name!=null and name!='' ">
            NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
        </if>
        <if test="hobby!= null and hobby!= '' ">
            AND hobby = #{hobby}
        </if>
    </where>
</select>

Set标签

<update id="updateUser" parameterType="com.pojo.User">  
    UPDATE user  
    <set>  
        <if test="username!= null and username != '' ">  
            username = #{username},  
        </if>  
        <if test="sex!= null and sex!= '' ">  
           sex = #{sex},  
        </if>  
        <if test="birthday != null ">  
            birthday = #{birthday},  
        </if>  
    </set>  
    WHERE user_id = #{userid}      
</update>  

trim标签

<select id="selectByNameOrHobby" resultMap="BaseResultMap">
	select * from student 
	<trim prefix="WHERE" prefixOverrides="AND | OR">
		<if test="name != null and name.length()>0"> 
			AND name=#{name}
		</if>
		<if test="hobby != null and hobby.length()>0">
			AND hobby=#{hobby}
		</if>
	</trim>
</select>

foreach 标签

主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中

<!-- in查询所有，不分页 -->
<select id="selectIn" resultMap="BaseResultMap">
    select name,hobby from student 
    where id in
    <foreach item="item" index="index" collection="list" open="(" 
             separator="," close=")">
        #{item}
    </foreach>
</select>

choose 标签

按顺序判断 when 中的条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when
的条件都不满则时，则执行 otherwise 中的 sql

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE 1=1
    <where>
        <choose>
            <when test="Name!=null and student!='' ">
                AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
            </when>
            <when test="hobby!= null and hobby!= '' ">
                AND hobby = #{hobby}
            </when>
            <otherwise>
                AND AGE = 15
            </otherwise>
        </choose>
    </where>
</select>

二. 集成tkMapper

1. tk基础环境搭建

1. 导入依赖

基于以上工程，pom.xml中继续加入tkmapper的依赖

<!-- https://mvnrepository.com/artifact/tk.mybatis/mapper -->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper</artifactId>
    <version>${tkmapper.version}</version>
</dependency>

2. 修改MapperScannerConfigurer

org.mybatis.spring.mapper.MapperScannerConfigurer修改为org.mybatis.spring.mapper.MapperScannerConfigurer

<bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 需要生成代理类对象的mapper接口包 -->
        <property name="basePackage" value="com.zzhua.dao"></property>
        <!-- sqlSessionFactory 的name  用于为代理类中生成SqlSession -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean>

3. 修改实体类、mapper接口

@Table(name="t_emp")  //  建立实体类和数据库表之间的对应关系
public class Emp{
    @Id               //  定义主键
    @GeneratedValue(strategy=GenerationType.IDENTITY)   //  自增主键，并返回给对象，
    // @GeneratedValue(strategy=GenerationType.IDENTITY,
    //   generator = "select SEQ_ID.nextval from dual"  //  序列主键
    // )
    private Integer empId;
                   
    @Column(name = "emp_name")  //  建立属性和数据库表字段之间的对应关系，
    private String empName;     //  默认支持驼峰式,此处可省略

    private Double empSalary;   

    private Integer empAge;
    
    @Transient                  //  标记不与数据库表字段对应的实体类字段
    privateStringotherThings;   
    
    //...省略setter、getter方法
}

public interface EmpDao extends Mapper<Emp>{

}

4. 测试

测试成功

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:application.context-dao.xml"})
public class SpringTest {

    @Autowired
    private EmpDao empDao;

    @Test
    public void test05(){
        Emp emp = empDao.selectByPrimaryKey(1);
        System.out.println(emp);
    }
}

注意：

1. 可以继续使用mapper.xml文件，但是需要删除掉自动生成了sql语句，因为tkmapper已经帮我们自动生成了sql语句，因此不能有重复！
2. mapper接口继承tkMapper中的Mapper接口，就有了基础的增删改查的方法，但是没有多表查询的功能。因此，要用到多表查询，还是须在mapper.xml文件中自己写！

2.常用方法

1. selectOne

实体类封装查询条件生成 WHERE 子句的规则 
- 使用非空的值生成 WHERE 子句
- 在条件表达式中使用 “=” 进行比较 

要求必须返回一个实体类结果，如果有多个，则会抛出异常

2. xxxByPrimaryKey

需要使用@Id 主键明确标记和数据库表主键字段对应的实体类字段
否则通用 Mapper 会将所有实体类字段作为联合主键

3. 3xxxSelective 方法

非主键字段如果为 null 值，则不加入到 SQL 语句

三、springboot整合mybatis分页查询

插件注入即可

sqlSessionFactory中注入Interceptor，而PageInterceptor是它的一个实现，

PageInteceptor中定义了default_dialect_class = “com.github.pagehelper.PageHelper”;

下面进入源码中其实是把properties配置给了pageHelper

@Bean
PageInterceptor pageInterceptor(){
    PageInterceptor pageInterceptor = new PageInterceptor();
    Properties properties = new Properties();
    properties.setProperty("helperDialect", "mysql");
    pageInterceptor.setProperties(properties);  // 由此可进入源码，
    return pageInterceptor;
}

SpringBoot整合mybatis，注解，分页查询

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.zzhua</groupId>
    <artifactId>demo-mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo-mybatis</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.7</version>
        </dependency>

        <!--可改为手动注册分页插件-->
        <!--
		<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
		-->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

yml文件配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/zblog?serverTimezone=UTC
    username: root
    password: root
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

启动类

@SpringBootApplication
@MapperScan("com.zzhua.mapper")
public class DemoMybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoMybatisApplication.class, args);
    }

    @Bean
    PageInterceptor pageInterceptor(){ // 
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "mysql");
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }

}

自动生成文件

public interface TagMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Tag record);

    int insertSelective(Tag record);

    Tag selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Tag record);

    int updateByPrimaryKey(Tag record);

    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "tagName",column = "tag_name"),
            @Result(property = "createTime",column = "create_time")
    })
    @Select("select * from t_tag t1  right join t_article_tag t2 on t1.id = t2.tag_id
            where t2.article_id = #{articleId} ")
    List<Tag> findTags(@Param("articleId") Integer articleId);

}

public interface ArticleMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Article record);

    int insertSelective(Article record);

    Article selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Article record);

    int updateByPrimaryKey(Article record);

    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "content", column = "content"),
            @Result(property = "tagList",column = "id",
                    many = @Many(select = "com.zzhua.mapper.TagMapper.findTags"))
    })
    @Select("select * from t_article")
    List<ArticleVo> findAll();
}

四、springboot整合mybatisplus

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.zzhua</groupId>
    <artifactId>demo-mybatisplus</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo-mybatisplus</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.6</version>
        </dependency>




        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.2</version>
        </dependency>

        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.30</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

yml

# DataSource Config
spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://127.0.0.1:3306/test?serverTimezone=UTC
    username: root
    password: root

spy.properties

#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

代码生成器

package com.zzhua;

import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.po.TableInfo;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class CodeGenerator {

    /**
     * <p>
     * 读取控制台内容
     * </p>
     */
    public static String scanner(String tip) {
        Scanner scanner = new Scanner(System.in);
        StringBuilder help = new StringBuilder();
        help.append("请输入" + tip + "：");
        System.out.println(help.toString());
        if (scanner.hasNext()) {
            String ipt = scanner.next();
            if (StringUtils.isNotEmpty(ipt)) {
                return ipt;
            }
        }
        throw new MybatisPlusException("请输入正确的" + tip + "！");
    }

    public static void main(String[] args) {
        // 代码生成器
        AutoGenerator mpg = new AutoGenerator();

        // set freemarker engine
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());

        // 全局配置
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("zzhua");
        gc.setOpen(false);
        // gc.setSwagger2(true); 实体属性 Swagger2 注解
        mpg.setGlobalConfig(gc);

        // 数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&useSSL=false&characterEncoding=utf8");
        // dsc.setSchemaName("public");
        dsc.setDriverName("com.mysql.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("root");
        mpg.setDataSource(dsc);

        // 包配置
        PackageConfig pc = new PackageConfig();
        pc.setModuleName(scanner("模块名"));
        pc.setParent("com.zzhua");
        mpg.setPackageInfo(pc);

        // 自定义配置
        InjectionConfig cfg = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };

        // 如果模板引擎是 freemarker
        String templatePath = "/templates/mapper.xml.ftl";
        // 如果模板引擎是 velocity
        // String templatePath = "/templates/mapper.xml.vm";

        // 自定义输出配置
        List<FileOutConfig> focList = new ArrayList<>();
        // 自定义配置会被优先输出
        focList.add(new FileOutConfig(templatePath) {
            @Override
            public String outputFile(TableInfo tableInfo) {
                // 自定义输出文件名 ， 如果你 Entity 设置了前后缀、此处注意 xml 的名称会跟着发生变化！！
                return projectPath + "/src/main/resources/mapper/" + pc.getModuleName()
                        + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
            }
        });
        /*
        cfg.setFileCreate(new IFileCreate() {
            @Override
            public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) {
                // 判断自定义文件夹是否需要创建
                checkDir("调用默认方法创建的目录，自定义目录用");
                if (fileType == FileType.MAPPER) {
                    // 已经生成 mapper 文件判断存在，不想重新生成返回 false
                    return !new File(filePath).exists();
                }
                // 允许生成模板文件
                return true;
            }
        });
        */
        cfg.setFileOutConfigList(focList);
        mpg.setCfg(cfg);

        // 配置模板
        TemplateConfig templateConfig = new TemplateConfig();

        // 配置自定义输出模板
        //指定自定义模板路径，注意不要带上.ftl/.vm, 会根据使用的模板引擎自动识别
        // templateConfig.setEntity("templates/entity2.java");
        // templateConfig.setService();
        // templateConfig.setController();

        templateConfig.setXml(null);
        mpg.setTemplate(templateConfig);

        // 策略配置
        StrategyConfig strategy = new StrategyConfig();
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        // strategy.setSuperEntityClass("你自己的父类实体,没有就不用设置!");
        strategy.setEntityLombokModel(true);
        strategy.setRestControllerStyle(true);
        // 公共父类
        //strategy.setSuperControllerClass("你自己的父类控制器,没有就不用设置!");
        // 写于父类中的公共字段
        //strategy.setSuperEntityColumns("id");
        strategy.setInclude(scanner("表名，多个英文逗号分割").split(","));
        strategy.setControllerMappingHyphenStyle(true);
//        strategy.setTablePrefix(pc.getModuleName() + "_");
        strategy.setTablePrefix("t_");
        mpg.setStrategy(strategy);
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());
        mpg.execute();
    }

}

实体类

/**
 * <p>
 * 
 * </p>
 *
 * @author zzhua
 * @since 2020-06-24
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("t_user")
@ToString
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String name;

    private LocalDate birth;

    private String rightSign;


}

添加自定义方法

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author zzhua
 * @since 2020-06-24
 */
public interface UserMapper extends BaseMapper<User> {
    @Select("select * from t_user")
    IPage<User> paging(Page page, QueryWrapper wrapper);

    @Select("select * from t_user ${ew.customSqlSegment}")
    List<User> getList(@Param(Constants.WRAPPER)QueryWrapper wrapper);

}

其它具体做法参看官网即可