MyBatis3-基于注解的示例

在基于注解的示例中，可以简化编写XML的过程，全部采用注解方式进行编写，并在注解上写SQL语句，语句和XML的语句保持一致，并且可以省略掉XML文件不用引入的好处。但还有一点，基于注解的方式还没有百分百覆盖所有XML标签，所有还是有一点缺陷。

在org.apache.ibatis.annotations包下包含了所有注解。

下面引用官方文档的说明http://www.mybatis.org/mybatis-3/zh/java-api.html：

映射器注解：

因为最初设计时，MyBatis是一个XML驱动的框架。配置信息是基于XML的，而且映射语句也是定义在XML中的。而到了MyBatis3，有新的可用的选择了。MyBatis3构建在基于全面而且强大的Java配置API之上。这个配置API是基于XML的MyBatis配置的基础，也是新的基于注解配置的基础。注解提供了一种简单的方式来实现简单映射语句，而不会引入大量的开销。

注意：不幸的是，Java注解限制了它们的表现和灵活。尽管很多时间都花在调查，设计和实验上，最强大的MyBatis映射不能用注解来构建，那并不可笑。C#属性（做示例）就没有这些限制，因此MyBatis.NET将会比XML有更丰富的选择。也就是说，基于Java注解的配置离不开它的特性。

注解有下面这些：

注解	目标	相对应的XML	描述
@CacheNamespace	类	<cache>	为给定的命名空间（比如类）配置缓存。属性：implemetation，eviction，flushInterval，size，readWrite，blocking和properties。
@Property	N/A	<property>	指定属性值或占位符（可以由mybatis-config.xml中定义的配置属性替换）。属性：name，value。（在MyBatis3.4.2+上可用）
@CacheNamespaceRef	类	<cacheRef>	参照另外一个命名空间的缓存来使用。属性：value，name。如果使用此注释，则应指定value或name属性。value属性指定指定命名空间的java类型（命名空间名称成为指定的java类型的FQCN），对于name属性（此属性自3.4.2起可用）指定命名空间的名称。
@ConstructorArgs	方法	<constructor>	收集一组结果传递给一个劫夺对象的构造方法。属性：value，是形式参数的数组。
@Arg	N/A	<arg> <idArg>	单独的构造方法参数，是ConstructorArgs集合的一部分。属性：id，column，javaType，typeHandler。id属性是布尔值，来标识用于比较的属性，和<idArg>XML元素相似。
@TypeDiscriminator	方法	<discriminator>	一组实例值被用来决定结果映射的表现。属性：column，javaType，jdbcType，typeHandler，cases。cases属性就是实例的数组。
@Case	N/A	<case>	单独实例的值和它对应的映射。属性：value，type，results。Results属性是结果数组，因此这个注解和实际的ResultMap很相似，由下面的Results注解指定。
@Results	方法	<resultMap>	结果映射的列表，包含了一个特别结果列如何被映射到属性或字段的详情。属性：value，id。value属性是Result注解的数组。这个id的属性是结果映射的名称。
@Result	N/A	<result> <id>	在列和属性或字段之间的单独结果映射。属性：id，column，property，javaType，jdbcType，typeHandler，one，many。id属性是一个布尔值，表示了应该被用于比较（和在XML映射中的<id>相似）的属性。one属性是单独的联系，和<association>相似，而many属性是对集合而言的，和<collection>相似。它们这样命名是为了避免名称冲突。
@One	N/A	<association>	复杂类型的单独属性值映射。属性：select，已映射语句（也就是映射器方法）的完全限定名，它可以加载合适类型的实例。注意：联合映射在注解API中是不支持的。这是因为Java注解的限制，不允许循环引用。fetchType会覆盖全局的配置参数lazyLoadingEnabled。
@Many	N/A	<collection>	映射到复杂类型的集合属性。属性：select，已映射语句（也就是映射器方法）的全限定名，它可以加载合适类型的实例的集合，fetchType会覆盖全局的配置参数lazyLoadingEnabled。注意联合映射在注解API中是不支持的。这是因为Java注解的限制，不允许循环引用
@MapKey	方法		复杂类型的集合属性映射。属性：select，是映射语句（也就是映射器方法）的完全限定名，它可以加载合适类型的一组实例。注意：联合映射在Java注解中是不支持的。这是因为Java注解的限制，不允许循环引用。
@Options	方法	映射语句的属性	这个注解提供访问交换和配置选项的宽广范围，它们通常在映射语句上作为属性出现。而不是将每条语句注解变复杂，Options注解提供连贯清晰的方式来访问它们。属性：useCache=true，flushCache=FlushCachePolicy.DEFAULT，resultSetType=FORWARD_ONLY，statementType=PREPARED，fetchSize=-1，timeout=-1useGeneratedKeys=false，keyProperty=”id”，keyColumn=””，resultSets=””。理解Java注解是很重要的，因为没有办法来指定“null”作为值。因此，一旦你使用了Options注解，语句就受所有默认值的支配。要注意什么样的默认值来避免不期望的行为。
@Insert @Update @Delete @Select	方法	<insert> <update> <delete> <select>	这些注解中的每一个代表了执行的真实SQL。它们每一个都使用字符串数组（或单独的字符串）。如果传递的是字符串数组，它们由每个分隔它们的单独空间串联起来。这就当用Java代码构建SQL时避免了“丢失空间”的问题。然而，如果你喜欢，也欢迎你串联单独的字符串。属性：value，这是字符串数组用来组成单独的SQL语句。
@InsertProvider @UpdateProvider @DeleteProvider @SelectProvider	方法	<insert> <update> <delete> <select>	这些可选的SQL注解允许你指定一个类名和一个方法在执行时来返回运行允许创建动态的SQL。基于执行的映射语句，MyBatis会实例化这个类，然后执行由provider指定的方法.该方法可以有选择地接受参数对象.（InMyBatis3.4orlater，it'sallowmultipleparameters）属性：type，method。type属性是类。method属性是方法名。注意：这节之后是对类的讨论，它可以帮助你以干净，容于阅读的方式来构建动态SQL。
@Param	Parameter	N/A	如果你的映射器的方法需要多个参数，这个注解可以被应用于映射器的方法参数来给每个参数一个名字。否则，多参数将会以它们的顺序位置来被命名（不包括任何RowBounds参数）比如。#{param1}，#{param2}等，这是默认的。使用@Param（“person”），参数应该被命名为#{person}。
@SelectKey	方法	<selectKey>	该注解复制了<selectKey>的功能，用在注解了@Insert，@InsertProvider，@Updateor@UpdateProvider的方法上。在其他方法上将被忽略。如果你指定了一个@SelectKey注解，然后Mybatis将忽略任何生成的key属性通过设置@Options，或者配置属性。属性：statement是要执行的sql语句的字符串数组，keyProperty是需要更新为新值的参数对象属性，before可以是true或者false分别代表sql语句应该在执行insert之前或者之后，resultType是keyProperty的Java类型，statementType是语句的类型，取Statement，PreparedStatement和CallableStatement对应的STATEMENT，PREPARED或者CALLABLE其中一个，默认是PREPARED。
@ResultMap	方法	N/A	这个注解给@Select或者@SelectProvider提供在XML映射中的<resultMap>的id。这使得注解的select可以复用那些定义在XML中的ResultMap。如果同一select注解中还存在@Results或者@ConstructorArgs，那么这两个注解将被此注解覆盖。
@ResultType	Method	N/A	当使用结果处理器时启用此注解。这种情况下，返回类型为void，所以Mybatis必须有一种方式决定对象的类型，用于构造每行数据。如果有XML的结果映射，使用@ResultMap注解。如果结果类型在XML的<select>节点中指定了，就不需要其他的注解了。其他情况下则使用此注解。比如，如果@Select注解在一个方法上将使用结果处理器，返回类型必须是void并且这个注解（或者@ResultMap）是必须的。这个注解将被忽略除非返回类型是void。
@Flush	方法	N/A	如果这个注解使用了，它将调用定义在Mapper接口中的SqlSession#flushStatements方法。（Mybatis3.3或者以上）

映射申明样例：

这个例子展示了如何使用@SelectKey注解来在插入前读取数据库序列的值：

@Insert("insert into table3 (id, name) values(#{nameId}, #{name})")
@SelectKey(statement="call next value for TestSequence", keyProperty="nameId", before=true, resultType=int.class)
int insertTable3(Name name);

这个例子展示了如何使用@SelectKey注解来在插入后读取数据库识别列的值：

@Insert("insert into table2 (name) values(#{name})")
@SelectKey(statement="call identity()", keyProperty="nameId", before=false, resultType=int.class)
int insertTable2(Name name);

这个例子展示了如何使用@Flush注解去调用SqlSession#flushStatements()：

@Flush
List<BatchResult> flush();

这些例子展示了如何通过指定@Result的id属性来命名结果集：

@Results(id = "userResult", value = {
  @Result(property = "id", column = "uid", id = true),
  @Result(property = "firstName", column = "first_name"),
  @Result(property = "lastName", column = "last_name")
})
@Select("select * from users where id = #{id}")
User getUserById(Integer id);

@Results(id = "companyResults")
@ConstructorArgs({
  @Arg(property = "id", column = "cid", id = true),
  @Arg(property = "name", column = "name")
})
@Select("select * from company where id = #{id}")
Company getCompanyById(Integer id);

这个例子展示了单一参数使用@SqlProvider：

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(String name);

class UserSqlBuilder {
  public String buildGetUsersByName(final String name) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      if (name != null) {
        WHERE("name like #{value} || '%'");
      }
      ORDER_BY("id");
    }}.toString();
  }
}

这个列子展示了多参数使用@SqlProvider：

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(
    @Param("name") String name, @Param("orderByColumn") String orderByColumn);

class UserSqlBuilder {

  // If not use @Param, you should be define same arguments with mapper method
  public String buildGetUsersByName(
      final String name, final String orderByColumn) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      WHERE("name like #{name} || '%'");
      ORDER_BY(orderByColumn);
    }}.toString();
  }

  // If use @Param, you can define only arguments to be used
  public String buildGetUsersByName(@Param("orderByColumn") final String orderByColumn) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      WHERE("name like #{name} || '%'");
      ORDER_BY(orderByColumn);
    }}.toString();
  }
}

示例：

0、项目整体目录结构：

1、创建数据库、表、模拟数据，脚本如下：

CREATE DATABASE `studenttest` ;
USE `studenttest`;
DROP TABLE IF EXISTS `klass`;
CREATE TABLE `klass` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
insert  into `klass`(`id`,`name`) values (1,'一年一班');
insert  into `klass`(`id`,`name`) values (2,'一年二班');
insert  into `klass`(`id`,`name`) values (3,'一年三班');
insert  into `klass`(`id`,`name`) values (16,'四年五班');
insert  into `klass`(`id`,`name`) values (18,'二年一班1');
insert  into `klass`(`id`,`name`) values (19,'二年一班2');
insert  into `klass`(`id`,`name`) values (20,'二年一班3');
insert  into `klass`(`id`,`name`) values (21,'二年一班4');
insert  into `klass`(`id`,`name`) values (22,'二年一班5');
insert  into `klass`(`id`,`name`) values (23,'二年一班6');
insert  into `klass`(`id`,`name`) values (24,'二年一班7');
insert  into `klass`(`id`,`name`) values (25,'二年一班8');
insert  into `klass`(`id`,`name`) values (26,'二年一班9');
insert  into `klass`(`id`,`name`) values (27,'二年一班0');
insert  into `klass`(`id`,`name`) values (28,'二年一班1');
insert  into `klass`(`id`,`name`) values (29,'二年一班2');
insert  into `klass`(`id`,`name`) values (30,'二年一班3');
insert  into `klass`(`id`,`name`) values (31,'二年一班4');
insert  into `klass`(`id`,`name`) values (32,'二年一班5');
insert  into `klass`(`id`,`name`) values (33,'二年一班6');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `k_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
insert  into `student`(`id`,`name`,`k_id`) values (1,'jack',1);
insert  into `student`(`id`,`name`,`k_id`) values (2,'allen',1);
insert  into `student`(`id`,`name`,`k_id`) values (3,'marry',2);
insert  into `student`(`id`,`name`,`k_id`) values (4,'john',2);
insert  into `student`(`id`,`name`,`k_id`) values (5,'gumble',2);
insert  into `student`(`id`,`name`,`k_id`) values (6,'forest',2);
insert  into `student`(`id`,`name`,`k_id`) values (7,'tailer',3);
insert  into `student`(`id`,`name`,`k_id`) values (8,'smith',3);
insert  into `student`(`id`,`name`,`k_id`) values (9,'hebe',1);
insert  into `student`(`id`,`name`,`k_id`) values (10,'julia',3);
insert  into `student`(`id`,`name`,`k_id`) values (11,'mason',2);
insert  into `student`(`id`,`name`,`k_id`) values (12,'joe',2);
insert  into `student`(`id`,`name`,`k_id`) values (14,'angel',2);
insert  into `student`(`id`,`name`,`k_id`) values (15,'turky',2);
insert  into `student`(`id`,`name`,`k_id`) values (16,'eve',2);

2、引入POM依赖：

        <!-- mysql-connector-java -->
        <!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <!-- mybatis -->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>

3、新建config.properties配置文件，用于在MyBatis引入，使用EL表达式获取参数（基于properties配置文件进行对XML配置文件中使用EL表达式去获取变量）

username=root
password=root
driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://127.0.0.1\:3306/studenttest?useUnicode\=true&characterEncoding\=UTF-8&serverTimezone\=UTC

4、新建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>

    <!-- 引入properties配置文件 -->
    <properties resource="config.properties" />

    <!-- a full setting configuration -->
    <settings>
        <setting name="cacheEnabled" value="true" />
        <setting name="lazyLoadingEnabled" value="false" />
        <setting name="multipleResultSetsEnabled" value="true" />
        <setting name="useColumnLabel" value="true" />
        <setting name="useGeneratedKeys" value="false" />
        <setting name="autoMappingBehavior" value="PARTIAL" />
        <setting name="defaultExecutorType" value="SIMPLE" />
        <setting name="defaultStatementTimeout" value="25" />
        <setting name="safeRowBoundsEnabled" value="false" />
        <setting name="mapUnderscoreToCamelCase" value="false" />
        <setting name="localCacheScope" value="SESSION" />
        <setting name="jdbcTypeForNull" value="OTHER" />
        <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
    </settings>

    <!-- 三种种使用别称的方式  -->
    <!-- 1-逐个限定 -->
    <!-- <typeAliases>
        <typeAlias alias="class" type="com.jsoft.testmybatis.bean.Klass" />
    </typeAliases> -->
    <!-- 2-限定包, 这样此包中的bean类 别称会 默认为类名, 效果和上面一样 -->
    <typeAliases>
        <package name="com.jsoft.testmybatis.bean" />
    </typeAliases>
    <!-- 3-在类中使用 @Alias注解直接命名, 如@Alias("Klass") 效果和上面一样 -->

    <!-- 配置环境 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <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>

    <!-- 配置mapper映射文件, 所有mapper映射文件必须填在此处，如果蚕蛹注解的方式这个可以省略 -->
    <mappers>
        <mapper resource="com/jsoft/testmybatis/mapper/KlassMapper.xml" />
        <mapper resource="com/jsoft/testmybatis/mapper/StudentMapper.xml" />
    </mappers>
</configuration>

5、新建表对应的Bean

//Klass.java
package com.jsoft.testmybatis.bean;

import java.util.List;

import org.apache.ibatis.type.Alias;

/*@Alias("Klass")*/
public class Klass {

    private int id;
    private String name;
    private List<Student> students;

    public Klass() {
    }

    public Klass(int id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Klass [id=" + id + ", name=" + name + "]";
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

}

//Student.java
package com.jsoft.testmybatis.bean;

public class Student {

    private Integer id;
    private String name;
    private Klass klass;

    public Student() {
    }

    public Student(int id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", klass=" + klass + "]";
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Klass getKlass() {
        return klass;
    }

    public void setKlass(Klass klass) {
        this.klass = klass;
    }

}

7、新建Mapper

//StudentDao.java
package com.jsoft.testmybatis.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.jsoft.testmybatis.bean.Student;

public interface StudentDao {

    // sql语句 改
    final String UPDATE = "update student set name = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER}";

    // sql语句 根据id查一条
    final String RETRIEVEBYPRIMARYKEY = "select s.id as s_id, s.name as s_name, k.id as k_id, k.name as k_name from student as s, klass as k where s.k_id = k.id and s.id = #{id,jdbcType=INTEGER}";

    // sql语句 分页查询
    final String RETRIEVEBYPAGE = "select s.id as s_id, s.name as s_name, k.id as k_id, k.name as k_name from student as s, klass as k where s.k_id = k.id and s.name like concat('%',#{student.name},'%') limit #{start}, #{size}";

    // sql语句 使用分页查询同样的条件 查询总条数
    final String RETRIEVECOUNT = "select count(*) from student as s, klass as k where s.k_id = k.id and s.name like concat('%',#{student.name},'%')";

    // 增
    @Insert("insert into student(name,k_id) values(#{name,jdbcType=VARCHAR},#{klass.id,jdbcType=INTEGER})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Student student);

    // 删
    @Delete("delete from student where id = #{id,jdbcType=INTEGER}")
    int delete(Integer id);

    // 改
    @Update(UPDATE)
    int update(Student student);

    /**
     * 此处如果不写result 列名--字段名 对应的话 会自动找名字相同的 此处我写了连接查询 只要将查询 返回的列名和类中的字段对应上就可以了
     * 
     * @param id
     * @return
     */
    // 根据id查一条
    @Select(RETRIEVEBYPRIMARYKEY)
    @Results(value = { @Result(property = "id", column = "s_id"), @Result(property = "name", column = "s_name"), @Result(property = "klass.id", column = "k_id"), @Result(property = "klass.name", column = "k_name") })
    Student retrieveByPrimaryKey(Integer id);

    // 分页+条件查询
    @Select(RETRIEVEBYPAGE)
    @Results(value = { @Result(property = "id", column = "s_id"), @Result(property = "name", column = "s_name"), @Result(property = "klass.id", column = "k_id"), @Result(property = "klass.name", column = "k_name") })
    List<Student> retrieveByPage(Map<String, Object> map);

    // 使用分页查询同样的条件 查询总条数
    @Select(RETRIEVECOUNT)
    int retrieveCount(Map<String, Object> map);

}

说明：其实也是接口文件，并且是使用注解方式。

注意：如果采用注解方式的，就不用新建XML文件了，这里为了也是两种效果，直接采用了两种方式。

8、新建XML的Mapper

<!--KlassMapper.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="KlassMapper">

    <resultMap id="ResultKlass" type="Klass">
        <id column="k_id" property="id" jdbcType="INTEGER" />
        <result column="k_name" property="name" jdbcType="VARCHAR" />
    </resultMap>

    <resultMap type="Klass" id="ResultKlassStudentCollection" extends="ResultKlass">
        <collection property="students" ofType="Student">
            <id column="s_id" property="id" jdbcType="INTEGER" />
            <result column="s_name" property="name" jdbcType="VARCHAR" />
        </collection>
    </resultMap>
    
    <sql id="column_klass">
        k.id as k_id, k.name as k_name
    </sql>
    
    <sql id="column_student">
        s.id as s_id, s.name as s_name
    </sql>

    <!-- insert -->
    <!-- 使用数据库设定的自增id  useGeneratedKeys="true" keyProperty="id" -->
    <insert id="insertSelective" useGeneratedKeys="true" keyProperty="id" parameterType="Klass">
        <!-- 返回新保存的这条数据的id,在保存后,用保存前对象.getId()得到-->
        <selectKey resultType="int" keyProperty="id" order="AFTER">
            select last_insert_id()
        </selectKey>
        insert into klass
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name,jdbcType=VARCHAR}
            </if>
        </trim>
    </insert>

    <!-- delete -->
    <delete id="deleteByPrimaryKey" parameterType="int">
        delete from klass
        where id = #{id,jdbcType=INTEGER}
    </delete>
    
    <delete id="deleteByBatch" parameterType="java.util.List">
        delete from klass
        where id in 
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

    <!-- update -->
    <update id="updateByPrimaryKey" parameterType="Klass">
        update klass
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <!-- retrieve -->
    <select id="retrieveByPrimaryKey" resultMap="ResultKlass" parameterType="int">
    select
        <include refid="column_klass" />
        from klass as k
        where k.id = #{id,jdbcType=INTEGER}
    </select>

    <select id="retrieveKlassWithStudent" resultMap="ResultKlassStudentCollection" parameterType="int">
    select
        <include refid="column_klass" />,
        <include refid="column_student" />
        from klass as k, student as s 
        where k.id = s.k_id and k.id = #{id,jdbcType=INTEGER}
    </select>
    
    <select id="retrieveByPage" resultMap="ResultKlass" parameterType="map">
        select
        <include refid="column_klass" />
        from klass as k
        <trim prefix="where" prefixOverrides="and|or">
            <if test="klass != null">
                <if test="class.name != null and class.name != ''">
                    and name like concat('%',#{class.name},'%')
                </if>
            </if>
        </trim>
        limit #{start}, #{size}
    </select>

    <select id="retrieveCount" resultType="int" parameterType="map">
        select count(*)
        from company_economy_type
        <trim prefix="where" prefixOverrides="and|or">
            <if test="klass != null">
                <if test="klass.name != null and klass.name != ''">
                    and name like concat('%',#{klass.name},'%')
                </if>
            </if>
        </trim>
    </select>

    <select id="retrievePages" resultMap="ResultKlass" parameterType="map">
        select
        <include refid="column_klass" />
        from klass as k
        <trim prefix="where" prefixOverrides="and|or">
            <if test="klass != null">
                <if test="class.name != null and class.name != ''">
                    and name like concat('%',#{class.name},'%')
                </if>
            </if>
        </trim>
    </select>
</mapper>

<!--StudentMapper.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="StudentMapper">

    <resultMap id="ResultStudent" type="Student">
        <id column="s_id" property="id" jdbcType="INTEGER" />
        <result column="s_name" property="name" jdbcType="VARCHAR" />
        <association property="klass" javaType="Klass" resultMap="ResultKlass" />
    </resultMap>
    
    <resultMap id="ResultKlass" type="Klass">
        <id column="k_id" property="id" jdbcType="INTEGER" />
        <result column="k_name" property="name" jdbcType="VARCHAR" />
    </resultMap>

    <sql id="Base_Column_List">
        s.id as s_id, s.name as s_name, k.id as k_id, k.name as k_name 
    </sql>

    <!-- insert -->
    <insert id="insertSelective" useGeneratedKeys="true" keyProperty="id" parameterType="Student">
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            select last_insert_id()
        </selectKey>
        insert into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="klass != null">
                <if test="klass.id != null and klass.id > 0">
                    k_id
                </if>
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="klass != null">
                <if test="klass.id != null and klass.id > 0">
                    #{klass.id,jdbcType=INTEGER}
                </if>
            </if>
        </trim>
    </insert>

    <!-- delete -->
    <delete id="deleteByPrimaryKey" parameterType="int">
        delete from student
        where id = #{id,jdbcType=INTEGER}
    </delete>

    <!-- update -->
    <update id="updateByPrimaryKey" parameterType="Student">
        update student
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="klass != null">
                <if test="klass.id != null and klass.id > 0">
                    k_id
                </if>
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <!-- retrieve -->
    <select id="retrieveByPrimaryKey" resultMap="ResultStudent" parameterType="int">
    select
        <include refid="Base_Column_List" />
        from student as s, klass as k
        where s.k_id = k.id and s.id = #{id,jdbcType=INTEGER}
    </select>

    <select id="retrieveByPage" resultType="Student" parameterType="map">
        select
        <include refid="Base_Column_List" />
        from student as s, klass as k
        <trim prefix="where" prefixOverrides="and|or">
            s.k_id = k.id 
            <if test="student != null">
                <if test="student.name != null and student.name != ''">
                    and s.name like concat('%',#{student.name},'%')
                </if>
            </if>
        </trim>
        limit #{start}, #{size}
    </select>

    <select id="retrieveCount" resultType="int" parameterType="map">
        select
        <include refid="Base_Column_List" />
        from student as s, klass as k
        <trim prefix="where" prefixOverrides="and|or">
            s.k_id = k.id 
            <if test="student != null">
                <if test="student.name != null and student.name != ''">
                    and s.name like concat('%',#{student.name},'%')
                </if>
            </if>
        </trim>
    </select>
    
    <select id="retrieveByMultiId" resultMap="ResultStudent" parameterType="map">
        select
        <include refid="Base_Column_List" />
        from student as s, klass as k
        <where>
            s.k_id = k.id 
            <if test="list != null">
                and s.id in 
                <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    
    <select id="retrieveStudentLike" resultMap="ResultStudent" parameterType="map">
        <bind name="name" value="'%'+student.name+'%'"/>
        select
        <include refid="Base_Column_List" />
        from student as s, klass as k
        <trim prefix="where" prefixOverrides="and|or">
            s.k_id = k.id  and s.name like #{name}
        </trim>
    </select>

</mapper>

9、测试代码，这里直接采用了JUnit进行测试

　　 /***************************** 以下方法为配置文件方法 ************************************/
    // 向 班级表 插入一条数据
    public void test1_1() throws IOException {
        Klass klass = new Klass();
        klass.setName("二年一班");
        // 读取配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        // 构建SqlSessionFactory 即session工厂, 用来产生session
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        // 从session工厂中获得session
        SqlSession sqlSession = factory.openSession();
        int k = sqlSession.insert("KlassMapper.insertSelective", klass);
        // 利用 返回数 来控制 进行 提交或者是回滚操作
        if (k == 1) {
            sqlSession.commit();
        } else {
            sqlSession.rollback();
        }
        System.out.println(klass);
        // 关闭session
        sqlSession.close();
    }

    // 向 班级表 批量 插入数据
    public void test1_2() throws IOException {
        List<Klass> list = new ArrayList<Klass>();
        for (int i = 0; i < 10; i++) {
            Klass klass = new Klass();
            klass.setName("二年一班" + i);
            list.add(klass);
        }
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession(ExecutorType.BATCH);
        for (Klass k : list) {
            int i = sqlSession.insert("KlassMapper.insertSelective", k);
            sqlSession.commit();
            System.out.println(k);
        }
        sqlSession.close();
    }

    // 向 学生表中 插入一条数据 带班级信息
    public void test1_3() throws IOException {
        Student stu = new Student();
        stu.setName("hebe");
        // 设置班级, 注意看StudentMapper映射文件中的写法,如果数据库有外键关联的话, 此属性不能为空！
        stu.setKlass(new Klass(1));
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        int k = sqlSession.insert("StudentMapper.insertSelective", stu);
        sqlSession.commit();
        System.out.println(stu);
        sqlSession.close();
    }

    // 删除 班级表 一条数据
    public void test2_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.delete("KlassMapper.deleteByPrimaryKey", 17);
        sqlSession.commit();
        System.out.println(i);
        sqlSession.close();
    }

    // 批量删除 班级表 数据
    public void test2_2() throws IOException {
        List<Integer> list = new ArrayList<Integer>();
        list.add(34);
        list.add(35);
        list.add(36);
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.delete("KlassMapper.deleteByBatch", list);
        sqlSession.commit();
        System.out.println(i);
        sqlSession.close();
    }

    // 更新 班级表 一条数据 数据, 批量修改可以参照上面的批量删除
    public void test3_1() throws IOException {
        Klass klass = new Klass();
        klass.setId(16);
        klass.setName("四年五班");
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.update("KlassMapper.updateByPrimaryKey", klass);
        sqlSession.commit();
        System.out.println(i);
        sqlSession.close();
    }

    // 查询 根据id查询 一个班级
    public void test4_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Klass klass = sqlSession.selectOne("KlassMapper.retrieveByPrimaryKey", 16);
        System.out.println(klass);
        sqlSession.close();
    }

    // 分页查询① 使用自定义参数, 控制 起始索引和返回量
    public void test4_2() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("start", 0);
        map.put("size", 999);
        List<Klass> list = sqlSession.selectList("KlassMapper.retrieveByPage", map);
        System.out.println(list.size());
        sqlSession.close();
    }

    // 分页查询② 使用mybatis提供的参数, 控制 起始索引和返回量
    public void test4_3() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Map<String, Object> map = new HashMap<String, Object>();
        // 起始索引, 返回量
        RowBounds row = new RowBounds(0, 20);
        List<Klass> list = sqlSession.selectList("KlassMapper.retrievePages", map, row);
        System.out.println(list.size());
        sqlSession.close();
    }

    // 级联查询 根据学生id查询 一个带班级信息的学生对象
    public void test4_4() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Student stu = sqlSession.selectOne("StudentMapper.retrieveByPrimaryKey", 1);
        System.out.println(stu);
        sqlSession.close();
    }

    // 级联查询 根据班级id 查询 一个带学生列表的班级对象
    public void test4_5() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Klass klass = sqlSession.selectOne("KlassMapper.retrieveKlassWithStudent", 1);
        System.out.println(klass.getStudents().size());
        for (Student s : klass.getStudents()) {
            System.out.println(s);
        }
        sqlSession.close();
    }

    // 级联查询 根据班级id 查询 一个带学生列表的班级对象,使用了foreach标签, 迭代多个参数
    public void test4_6() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Map<String, Object> map = new HashMap<String, Object>();
        int[] ids = new int[] { 1, 2 };
        map.put("list", ids);
        List<Student> stuList = sqlSession.selectList("StudentMapper.retrieveByMultiId", map);
        System.out.println(stuList.size());
        for (Student s : stuList) {
            System.out.println(s);
        }
        sqlSession.close();
    }

    // 级联查询 根据班级id 查询 一个带学生列表的班级对象,使用了bind标签, 绑定参数
    public void test4_7() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = factory.openSession();
        Map<String, Object> map = new HashMap<String, Object>();
        Student stu = new Student();
        stu.setName("m");
        map.put("student", stu);
        List<Student> stuList = sqlSession.selectList("StudentMapper.retrieveStudentLike", map);
        System.out.println(stuList.size());
        for (Student s : stuList) {
            System.out.println(s);
        }
        sqlSession.close();
    }

    /***************************** 以下方法为注解方法 ************************************/
    // 插入一条数据
    public void testA1_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        // 将接口 StudentMapper 加载到配置中, 如不加载, 则会报 找不到该接口的错
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        Student stu = new Student();
        stu.setName("ella");
        stu.setKlass(new Klass(2));
        int k = stuMapper.insert(stu);
        System.out.println("插入 : " + k + " 条!");
        if (k == 1) {
            sqlSession.commit();
        } else {
            sqlSession.rollback();
        }
        System.out.println(stu);
    }

    // 删除一条数据
    public void testA2_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        int k = stuMapper.delete(17);
        System.out.println("删除 : " + k + " 条!");
        if (k == 1) {
            sqlSession.commit();
        } else {
            sqlSession.rollback();
        }
        sqlSession.close();
    }

    // 更新一条数据
    public void testA3_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        Student student = new Student(16);
        student.setName("eve");
        int k = stuMapper.update(student);
        System.out.println("更新 : " + k + " 条!");
        if (k == 1) {
            sqlSession.commit();
        } else {
            sqlSession.rollback();
        }
        sqlSession.close();
    }

    // 查 一条数据
    public void testA4_1() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        Student stu = stuMapper.retrieveByPrimaryKey(2);
        System.out.println(stu);
        sqlSession.close();
    }

    // 查 分页查询
    public void testA4_2() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        Student stu = new Student();
        stu.setName("m");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("student", stu);
        // 注意: 起始参数是指索引值, 从0开始, 注意页数和索引值得转换
        map.put("start", 0);
        // 返回量
        map.put("size", 10);
        List<Student> list = stuMapper.retrieveByPage(map);
        System.out.println("查询到  " + list.size() + " 条");
        for (Student s : list) {
            System.out.println(s);
        }
        sqlSession.close();
    }

    // 查 总页数
    public void testA4_3() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        Configuration config = factory.getConfiguration();
        config.addMapper(StudentDao.class);
        SqlSession sqlSession = factory.openSession();
        StudentDao stuMapper = sqlSession.getMapper(StudentDao.class);
        Student stu = new Student();
        stu.setName("m");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("student", stu);
        int page = stuMapper.retrieveCount(map);
        System.out.println("共查到  " + page + " 条数据");
        sqlSession.close();
    }

 

测试工程：https://github.com/easonjim/5_java_example/tree/master/mybatis/test14

 

参考：

http://computerdragon.blog.51cto.com/6235984/1399742

原文出处：

[1] Jim, MyBatis3-基于注解的示例, https://www.cnblogs.com/EasonJim/p/7070820.html