SpringBoot学习笔记（五）——Spring Boot中使用MyBatis进阶

<setting name="cacheEnabled" value="true"/>
    <setting name="lazyLoadingEnabled" value="true"/>
    <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>

mybatis:
  type-aliases-package: com.zhangguo.mybatisdemo.entity  #类型别名包的位置
  mapper-locations: classpath:/mapper/*.xml  #映射文件位置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

<?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.zhangguo.mybatis02.mapper.studentMapper">
    <select id="selectStudentById" resultType="com.zhangguo.mybatis02.entities.Student">
        SELECT id,name,sex from student where id=#{id}
    </select>

    <select id="selectStudentsByName" parameterType="String" resultType="com.zhangguo.mybatis02.entities.Student">
      SELECT id,name,sex from student where name like '%${value}%';
    </select>

    <insert id="insertStudent" parameterType="com.zhangguo.mybatis02.entities.Student">
        insert into student(name,sex) VALUES(#{name},'${sex}')
    </insert>

    <update id="updateStudent" parameterType="com.zhangguo.mybatis02.entities.Student">
        update student set name=#{name},sex=#{sex} where id=#{id}
    </update>

    <delete id="deleteStudent" parameterType="int">
        delete from student where id=#{id}
    </delete>

</mapper>

mybatis:
  #指定包名下所有的类被自动扫描并定义默认别名，
  #mybatis会自动扫描包中的pojo类，自动定义别名，别名就是类名(首字母大写或小写都可以)
  type-aliases-package: com.zhangguo.mybatisdemo.entity  #类型别名包的位置
  mapper-locations: classpath:/mapper/*.xml  #映射文件位置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

    /**
     * 根据学生编号获得学生对象
     */
    Student selectStudentById(int id);

    <select id="selectStudentById" resultType="Student" parameterType="int">
        SELECT id,name,sex from student where id=#{id}
    </select>

    /**
     * Method: selectStudentById(int id)
     */
    @Test
    public void testSelectStudentById() throws Exception {
        Student entity=dao.selectStudentById(1);
        System.out.println(entity);
        Assert.assertNotNull(entity);
    }

    /**
     * 根据学生姓名或性别获得学生集合
     */
    List<Student> selectStudentsByNameOrSex(Map<String,Object> params);

    <select id="selectStudentsByNameOrSex" resultType="student">
        SELECT id,name,sex from student where name like '%${name}%' or sex=#{sex};
    </select>

    /**
     * Method: List<Student> selectStudentsByNameOrSex(Map<String,Object> params);
     */
    @Test
    public void selectStudentsByNameOrSex() throws Exception {
        Map<String,Object> params=new HashMap<String,Object>();
        params.put("name","Candy");
        params.put("sex","girl");
        List<Student> students=dao.selectStudentsByNameOrSex(params);

        System.out.println(students);
        Assert.assertNotNull(students);
    }

    /**
     * 根据学生姓名或性别获得学生集合
     */
    List<Student> selectStudentsByNameOrSex(@Param("realname") String name,@Param("sex") String sex);

    <select id="selectStudentsByNameOrSex" resultType="student">
        SELECT id,name,sex from student where name like '%${realname}%' or sex=#{sex};
    </select>

    /**
     * Method: selectStudentsByNameOrSex(String name,String sex)
     */
    @Test
    public void testSelectStudentsByNameOrSex() throws Exception {
        List<Student> students=dao.selectStudentsByNameOrSex("C","boy");
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    /**
     * 添加学生
     */
    int insertStudent(Student entity);

    <insert id="insertStudent" parameterType="student">
        insert into student(name,sex) VALUES(#{name},'${sex}')
    </insert>

    /**
     * Method: insertStudent
     */
    @Test
    public void testInsertStudent() throws Exception {
        Student entity=new Student();
        entity.setName("张明");
        entity.setSex("boy");

        Assert.assertEquals(1,dao.insertStudent(entity));
    }

<if test="_parameter!= null">

<if test="name!= null">

int updateByExample(@Param("user") User user, @Param("example") UserExample example);

<update id="updateByExample" parameterType="map" > 

update tb_user set id = #{user.id}, ... 

<if test="_parameter != null" > 

<include refid="Update_By_Example_Where_Clause" />

</if>

</update>

    /**
     * 删除多个学生通过编号
     */
    int deleteStudents(List<Integer> ids);

    <delete id="deleteStudents">
        delete from student where id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    /**
     * Method: deleteStudents
     */
    @Test
    public void testDeleteStudents() throws Exception {
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(10);
        ids.add(11);
        Assert.assertEquals(2,dao.deleteStudents(ids));
    }

public List<XXXBean> getXXXBeanList(List<String> list);  

<select id="getXXXBeanList" resultType="XXBean">
　　select 字段... from XXX where id in
　　<foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
　　　　#{item}  
　　</foreach>  
</select>  

foreach 最后的效果是select 字段... from XXX where id in ('1','2','3','4') 

List<User> selectByExample(UserExample example);

<where>
<foreach collection="oredCriteria" item="criteria" separator="or">
<if test="criteria.valid">
</where>

List<Category> findByIds(int... ids);

    <select id="findByIds" resultType="Category">
        SELECT id,name from category where id in
        <foreach collection="array" open="(" close=")" separator=",">
            #{ids}
        </foreach>
    </select>

    /**
     * 获得学生总数
     * */
    long selectStudentsCount();

    <select id="selectStudentsCount" resultType="long">
        SELECT count(*) from student
    </select>

    /**
     * Method: selectStudentsCount()
     */
    @Test
    public void testSelectStudentsCount() throws Exception {
        Assert.assertNotEquals(0,dao.selectStudentsCount());
    }

    /**
     * 根据学生编号获得学生对象
     */
    Student selectStudentById(int id);

    <select id="selectStudentById" resultType="Student">
        SELECT id,name,sex from student where id=#{id}
    </select>

    /**
     * 根据学生姓名获得学生集合
     */
    List<Student> selectStudentsByName(String name);

    <select id="selectStudentsByName" parameterType="String" resultType="student">
        SELECT id,name,sex from student where name like '%${value}%';
    </select>

    /**
     * 根据性别获得学生集合
     */
    List<Stu> selectStudentsBySex(String sex);

    <select id="selectStudentsBySex" parameterType="String" resultType="stu">
        SELECT id,name,sex from student where sex=#{sex};
    </select>

    /**
     * Method: selectStudentsBySex(String sex)
     */
    @Test
    public void testSelectStudentsBySex() throws Exception {
        List<Stu> students=dao.selectStudentsBySex("boy");
        System.out.println(students);
        Assert.assertNotNull(students.get(0));
    }

    <!--定义结果映射，id是引用时的编号需唯一，stu是最终被映射的类型-->
    <resultMap id="stuMap" type="stu">
        <!--映射结果,collumn表示列名，property表示属性名-->
        <result column="id" property="stu_id"></result>
        <result column="name" property="stu_name"></result>
        <result column="sex" property="stu_sex"></result>
    </resultMap>
    
    <!--resultMap指定引用的映射-->
    <select id="selectStudentsBySex" parameterType="String" resultMap="stuMap">
        SELECT id,name,sex from student where sex=#{sex};
    </select>

    <select id="selectStudentsBySex" parameterType="String" resultType="stu">
      SELECT id stu_id,name stu_name,sex as stu_sex from student where sex=#{sex};
    </select>

    @Results({
            @Result(property ="id",column = "id"),
            @Result(property = "name",column = "name")
    })
    @Select("select * from Category where id=#{id}")
    Category findById(int id);

    /**
     * 获得所有学生Map集合
     */
    List<Map<String,Object>> selectAllStudents();

    <resultMap id="stuKeyValueMap" type="HashMap">
        <result property="name" column="NAME"></result>
        <result property="value" column="VALUE"></result>
    </resultMap>

    <select id="selectAllStudents" resultMap="stuKeyValueMap">
        SELECT id NAME,name VALUE from student;
    </select>

   /**
     * Method: selectAllStudents()
     */
    @Test
    public void testSelectAllStudents() throws Exception {
        List<Map<String,Object>>  students=dao.selectAllStudents();
        System.out.println(students);
        Assert.assertNotNull(students);
    }

<resultMap id="pieMap"   type="HashMap">  
    <result property="value" column="VALUE" />  
    <result property="name" column="NAME" />  
</resultMap>

<select id="queryPieParam" parameterType="String" resultMap="pieMap">
    SELECT
    　　PLAT_NAME NAME,
        <if test='_parameter == "总量"'>
            AMOUNT VALUE
        </if>
        <if test='_parameter == "总额"'>
            TOTALS VALUE
        </if>
    FROM
        DOMAIN_PLAT_DEAL_PIE
    ORDER BY
        <if test='_parameter  == "总量"'>
            AMOUNT
        </if>
        <if test='_parameter  == "总额"'>
            TOTALS
        </if>
    ASC
</select>

<?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.zhangguo.mybatis03.dao.StudentMapper">

    <select id="selectStudentById" resultType="Student">
        SELECT id,name,sex from student where id=#{id}
    </select>

    <select id="selectStudentsCount" resultType="long">
        SELECT count(*) from student
    </select>

    <select id="selectStudentsByName" parameterType="String" resultType="student">
        SELECT id,name,sex from student where name like '%${value}%';
    </select>

    <resultMap id="stuKeyValueMap" type="HashMap">
        <result property="name" column="NAME"></result>
        <result property="value" column="VALUE"></result>
    </resultMap>

    <select id="selectAllStudents" resultMap="stuKeyValueMap">
        SELECT id NAME,name VALUE from student;
    </select>


    <!--定义结果映射，id是引用时的编号需唯一，stu是最终被映射的类型-->
    <resultMap id="stuMap" type="stu">
        <!--映射结果,collumn表示列名，property表示属性名-->
        <result column="id" property="stu_id"></result>
        <result column="name" property="stu_name"></result>
        <result column="sex" property="stu_sex"></result>
    </resultMap>

    <!--resultMap指定引用的映射-->
    <!--<select id="selectStudentsBySex" parameterType="String" resultMap="stuMap">-->
        <!--SELECT id,name,sex from student where sex=#{sex};-->
    <!--</select>-->

    <select id="selectStudentsBySex" parameterType="String" resultType="stu">
      SELECT id stu_id,name stu_name,sex as stu_sex from student where sex=#{sex};
    </select>


    <select id="selectStudentsByNameOrSex" resultType="student">
      SELECT id,name,sex from student where name like '%${realname}%' or sex=#{sex};
    </select>

    <select id="selectStudentsByIdOrSex" resultType="student">
        SELECT id,name,sex from student where id=#{no} or sex=#{sex};
    </select>


    <insert id="insertStudent" parameterType="student">
        insert into student(name,sex) VALUES(#{name},'${sex}')
    </insert>

    <update id="updateStudent" parameterType="student">
        update student set name=#{name},sex=#{sex} where id=#{id}
    </update>

    <delete id="deleteStudent" parameterType="int">
        delete from student where id=#{id}
    </delete>

    <delete id="deleteStudents">
        delete from student where id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

package com.zhangguo.mybatis03.dao;

import com.zhangguo.mybatis03.entities.Stu;
import com.zhangguo.mybatis03.entities.Student;
import com.zhangguo.mybatis03.utils.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;

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

public class StudentDao implements StudentMapper {

    /**
     * 根据学生编号获得学生对象
     */
    public Student selectStudentById(int id) {
        Student entity = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询单个对象，指定参数为3
        entity = mapper.selectStudentById(id);

        //关闭
        SqlSessionFactoryUtil.closeSession(session);

        return entity;
    }

    /**
     * 获得学生总数
     */
    public long selectStudentsCount() {
        long count = 0;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询单行单列，简单值
        count = mapper.selectStudentsCount();

        //关闭
        SqlSessionFactoryUtil.closeSession(session);

        return count;
    }


    /**
     * 根据学生姓名获得学生集合
     */
    public List<Student> selectStudentsByName(String name) {
        List<Student> entities = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询多个对象，指定参数
        entities = mapper.selectStudentsByName(name);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return entities;
    }

    /**
     * 获得所有学生Map集合
     *
     */
    public List<Map<String, Object>> selectAllStudents() {
        List<Map<String, Object>> entities = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询多个对象，指定参数
        entities = mapper.selectAllStudents();
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return entities;
    }

    /**
     * 根据性别获得学生集合
     *
     * @param sex
     */
    public List<Stu> selectStudentsBySex(String sex) {
        List<Stu> entities = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询多个对象，指定参数
        entities = mapper.selectStudentsBySex(sex);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return entities;
    }

    /**
     * 根据学生姓名或性别获得学生集合
     *
     * @param name
     * @param sex
     */
    public List<Student> selectStudentsByNameOrSex(String name, String sex) {
        List<Student> entities = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询多个对象，指定参数
        entities = mapper.selectStudentsByNameOrSex(name, sex);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return entities;
    }

    /**
     * 根据学生Id或性别获得学生集合
     *
     * @param param
     */
    public List<Student> selectStudentsByIdOrSex(Map<String, Object> param) {
        List<Student> entities = null;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //查询多个对象，指定参数
        entities = mapper.selectStudentsByIdOrSex(param);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return entities;
    }


    /**
     * 添加学生
     */
    public int insertStudent(Student entity) {
        //影响行数
        int rows = 0;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //执行添加
        rows = mapper.insertStudent(entity);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return rows;
    }

    /**
     * 更新学生
     */
    public int updateStudent(Student entity) {
        //影响行数
        int rows = 0;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //执行更新
        rows = mapper.updateStudent(entity);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return rows;
    }

    /**
     * 删除学生
     */
    public int deleteStudent(int id) {
        //影响行数
        int rows = 0;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //执行删除
        rows = mapper.deleteStudent(id);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return rows;
    }

    /**
     * 删除多个学生通过编号
     *
     * @param ids
     */
    public int deleteStudents(List<Integer> ids) {
        //影响行数
        int rows = 0;
        //打开一个会话
        SqlSession session = SqlSessionFactoryUtil.openSession(true);

        //获得一个映射器
        StudentMapper mapper = session.getMapper(StudentMapper.class);

        //执行删除
        rows = mapper.deleteStudents(ids);
        //关闭
        SqlSessionFactoryUtil.closeSession(session);
        return rows;
    }

}

package com.zhangguo.mybatis03.dao;

import com.zhangguo.mybatis03.entities.Stu;
import com.zhangguo.mybatis03.entities.Student;
import org.apache.ibatis.annotations.Param;

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

public interface StudentMapper {
    /**
     * 根据学生编号获得学生对象
     */
    Student selectStudentById(int id);

    /**
     * 获得学生总数
     * */
    long selectStudentsCount();

    /**
     * 根据学生姓名获得学生集合
     */
    List<Student> selectStudentsByName(String name);


    /**
     * 获得所有学生Map集合
     */
    List<Map<String,Object>> selectAllStudents();

    /**
     * 根据性别获得学生集合
     */
    List<Stu> selectStudentsBySex(String sex);

    /**
     * 根据学生姓名或性别获得学生集合
     */
    List<Student> selectStudentsByNameOrSex(@Param("realname") String name,@Param("sex") String sex);

    /**
     * 根据学生Id或性别获得学生集合
     */
    List<Student> selectStudentsByIdOrSex(Map<String,Object> param);


    /**
     * 添加学生
     */
    int insertStudent(Student entity);

    /**
     * 更新学生
     */
    int updateStudent(Student entity);

    /**
     * 删除学生
     */
    int deleteStudent(int id);

    /**
     * 删除多个学生通过编号
     */
    int deleteStudents(List<Integer> ids);
}

package com.zhangguo.mybatis03.dao;

import com.zhangguo.mybatis03.entities.Stu;
import com.zhangguo.mybatis03.entities.Student;
import org.junit.*;
import org.junit.runners.MethodSorters;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * StudentDao Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/26/2018</pre>
 */
@FixMethodOrder(MethodSorters.JVM)//指定测试方法按定义的顺序执行
public class StudentDaoTest {
    StudentMapper dao;
    @Before
    public void before() throws Exception {
        dao=new StudentDao();
    }

    @After
    public void after() throws Exception {
    }

    /**
     * Method: selectStudentById(int id)
     */
    @Test
    public void testSelectStudentById() throws Exception {
        Student entity=dao.selectStudentById(1);
        System.out.println(entity);
        Assert.assertNotNull(entity);
    }

    //
    /**
     * Method: selectStudentsCount()
     */
    @Test
    public void testSelectStudentsCount() throws Exception {
        Assert.assertNotEquals(0,dao.selectStudentsCount());
    }
    /**
     * Method: selectStudentsByName(String name)
     */
    @Test
    public void testSelectStudentsByName() throws Exception {
        List<Student> students=dao.selectStudentsByName("C");
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    /**
     * Method: selectAllStudents()
     */
    @Test
    public void testSelectAllStudents() throws Exception {
        List<Map<String,Object>>  students=dao.selectAllStudents();
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    /**
     * Method: selectStudentsBySex(String sex)
     */
    @Test
    public void testSelectStudentsBySex() throws Exception {
        List<Stu> students=dao.selectStudentsBySex("boy");
        System.out.println(students);
        Assert.assertNotNull(students.get(0));
    }


    /**
     * Method: selectStudentsByIdOrSex
     */
    @Test
    public void testSelectStudentsByNameOrSex() throws Exception {
        Map<String ,Object> param=new HashMap<String,Object>();
        param.put("no",1);
        param.put("sex","girl");
        List<Student> students=dao.selectStudentsByIdOrSex(param);
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    /**
     * Method: insertStudent
     */
    @Test
    public void testInsertStudent() throws Exception {
        Student entity=new Student();
        //entity.setName("张明");
        entity.setSex("boy");

        Assert.assertEquals(1,dao.insertStudent(entity));
    }

    /**
     * Method: updateStudent
     */
    @Test
    public void testUpdateStudent() throws Exception {
        Student entity=dao.selectStudentById(11);
        //entity.setName("张丽美");
        entity.setSex("girl");

        Assert.assertEquals(1,dao.updateStudent(entity));
    }

    /**
     * Method: deleteStudent
     */
    @Test
    public void testDeleteStudent() throws Exception {
        Assert.assertEquals(1,dao.deleteStudent(12));
    }



    /**
     * Method: deleteStudents
     */
    @Test
    public void testDeleteStudents() throws Exception {
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(10);
        ids.add(11);
        Assert.assertEquals(2,dao.deleteStudents(ids));
    }
} 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
 `username` varchar(64) NOT NULL COMMENT '用户名',
 `password` varchar(64) NOT NULL COMMENT '密码',
 PRIMARY KEY (`id`),
 UNIQUE KEY `users_username_uindex` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'tom', '123456');
INSERT INTO `user` VALUES ('2', 'rose', '888888');
INSERT INTO `user` VALUES ('3', 'mark', 'qwerty');
INSERT INTO `user` VALUES ('4', 'jack', 'qaz123');
INSERT INTO `user` VALUES ('5', 'mali', 'uio890');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
 `user_id` int(11) DEFAULT NULL COMMENT '用户编号',
 `realname` varchar(32) NOT NULL COMMENT '姓名',
 `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`),
 KEY `emp_user_id` (`user_id`),
 CONSTRAINT `emp_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工表';

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '1', '汤姆', 'tom@gmail.com');
INSERT INTO `emp` VALUES ('2', '2', '梅贵', 'rose@163.com');
INSERT INTO `emp` VALUES ('3', '3', '马克', 'mark@sina.com');
INSERT INTO `emp` VALUES ('4', '4', '岳翰', 'jack@gmail.com');
INSERT INTO `emp` VALUES ('5', '5', '马丽', 'mali@sina.com');

package com.zhangguo.mybatis03.entities;

/**用户POJO*/
public class User {
 private int id;
 private String username;
 private String password;

 public int getId() {
 return id;
 }

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

 public String getUsername() {
 return username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 public String getPassword() {
 return password;
 }

 public void setPassword(String password) {
 this.password = password;
 }
}

package com.zhangguo.mybatis03.entities;

/**员工POJO*/
public class Emp {
 private int id;
 /**用户编号*/
 private int user_id;
 private String realname;
 private String email;

 /**用户对象*/
 private User user;

 public int getId() {
 return id;
 }

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

 public int getUser_id() {
 return user_id;
 }

 public void setUser_id(int user_id) {
 this.user_id = user_id;
 }

 public String getRealname() {
 return realname;
 }

 public void setRealname(String realname) {
 this.realname = realname;
 }

 public String getEmail() {
 return email;
 }

 public void setEmail(String email) {
 this.email = email;
 }

 public User getUser() {
 return user;
 }

 public Emp setUser(User user) {
 this.user = user;
 return this;
 }
}

package com.zhangguo.mybatis03.dao;

import com.zhangguo.mybatis03.entities.Emp;

/**员工数据访口*/
public interface EmpMapper {

 /**获得员工通过员工编号*/
 Emp getEmpById_1(int id);

}

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhangguo.mybatis03.dao.EmpMapper">

 <!--一对一查询，方法1，通过内联接-->
 <select id="getEmpById_1" resultMap="empMap_1" parameterType="int">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email,
 `user`.username,
 `user`.`password`
 FROM
 emp
 INNER JOIN `user` ON emp.user_id = `user`.id where emp.id=#{id}
 </select>

 <!--员工关联查询结果映射-->
 <resultMap id="empMap_1" type="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--映射关系，指定属性与属性的类型-->
 <association property="user" javaType="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 </association>
 </resultMap>

</mapper>

package com.zhangguo.mybatis03.dao;

import com.zhangguo.mybatis03.entities.Emp;
import org.junit.Assert;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

/**
 * EmpDao Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/30/2018</pre>
 */
public class EmpDaoTest {
 EmpMapper empDao;
 @Before
 public void before() throws Exception {
 empDao=new EmpDao();
 }

 @After
 public void after() throws Exception {
 }

 /**
 * Method: getEmpById_1(int id)
 * 获得员工通过员工编号
 */
 @Test
 public void testGetEmpById_1() throws Exception {
 Emp entity=empDao.getEmpById_1(1);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }


} 

 /**获得员工通过员工编号，多次查询*/
 Emp getEmpById_2(int id);

 <!--一对一查询，方法2，通过多次查询（嵌套查询）-->
 <select id="getEmpById_2" resultMap="empMap_2">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email
 FROM
 emp where id=#{id}
 </select>

 <!--员工多次查询结果映射-->
 <resultMap id="empMap_2" type="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--通过外键user_id再次发起查询，调用selectUserById获得User对象-->
 <association property="user" column="user_id" select="selectUserById"></association>
 </resultMap>

 <!--根据用户编号获得用户对象-->
 <select id="selectUserById" resultType="User">
 SELECT
 `user`.id,
 `user`.username,
 `user`.`password`
 FROM
 `user` where id=#{id}
 </select>

 /**
 * Method: getEmpById_2(int id)
 * 获得员工通过员工编号，一对一方法二
 */
 @Test
 public void testGetEmpById_2() throws Exception {
 Emp entity=empDao.getEmpById_2(2);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

package com.zhangguo.mybatis03.entities;

/**员工POJO*/
public class Emp {
 private int id;
 /**用户编号*/
 private int user_id;
 private String realname;
 private String email;

 /**用户对象*/
 private User user;

 public int getId() {
 return id;
 }

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

 public int getUser_id() {
 return user_id;
 }

 public void setUser_id(int user_id) {
 this.user_id = user_id;
 }

 public String getRealname() {
 return realname;
 }

 public void setRealname(String realname) {
 this.realname = realname;
 }

 public String getEmail() {
 return email;
 }

 public void setEmail(String email) {
 this.email = email;
 }

 public User getUser() {
 return user;
 }

 public Emp setUser(User user) {
 this.user = user;
 return this;
 }

 @Override
 public String toString() {
 return "Emp{" +
 "id=" + id +
 ", user_id=" + user_id +
 ", realname='" + realname + '\'' +
 ", email='" + email + '\'' +
 ", user=" + user +
 '}';
 }
}

package com.zhangguo.mybatis03.entities;

import java.util.List;

/**用户POJO*/
public class User {
 private int id;
 private String username;
 private String password;

 /**员工集合，一个用户对象对应多个员工对象*/
 private List<Emp> emps;

 public int getId() {
 return id;
 }

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

 public String getUsername() {
 return username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 public String getPassword() {
 return password;
 }

 public void setPassword(String password) {
 this.password = password;
 }

 public List<Emp> getEmps() {
 return emps;
 }

 public User setEmps(List<Emp> emps) {
 this.emps = emps;
 return this;
 }

 @Override
 public String toString() {
 return "User{" +
 "id=" + id +
 ", username='" + username + '\'' +
 ", password='" + password + '\'' +
 ", emps=" + emps +
 '}';
 }
}

 /**获得用户通过用户编号，1对多级联查询*/
 User getUserById_1(int id);

 <!--一对多查询，方法1，通过内联接-->
 <select id="getUserById_1" resultMap="userMap_1" parameterType="int">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email,
 `user`.username,
 `user`.`password`
 FROM
 emp
 INNER JOIN `user` ON emp.user_id = `user`.id
 where `user`.id=#{id}
 </select>

 <resultMap id="userMap_1" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
<!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <collection property="emps" ofType="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 </collection>
 </resultMap>

 /**
 * Method: getUserById_1(int id)
 * 获得用户过用户编号，级联查询
 */
 @Test
 public void testGetUserById_1() throws Exception {
 User entity=empDao.getUserById_1(2);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

 <resultMap id="userMap_1" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 <!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <collection property="emps" ofType="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--映射关系，指定属性与属性的类型-->
 <association property="user" javaType="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 </association>
 </collection>
 </resultMap>

 /**获得用户通过用户编号，1对多嵌套查询*/
 User getUserById_2(int id);

 <!--一对多查询，方法2，通过嵌套查询多次-->
 <select id="getUserById_2" resultMap="userMap_2" parameterType="int">
 SELECT
 `user`.id,
 `user`.username,
 `user`.`password`
 FROM
 `user` where id=#{id}
 </select>

 <resultMap id="userMap_2" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 <!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <!--select用于指定再次查询的SQL编号，column用于指定参数列-->
 <collection property="emps" ofType="Emp" column="id" select="selectEmpById"></collection>
 </resultMap>

 <!--根据员工编号获得员工对象-->
 <select id="selectEmpById" resultType="Emp">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email
 FROM
 emp where user_id=#{id}
 </select>

 /**
 * Method: getUserById_2(int id)
 * 获得用户过用户编号，嵌套查询
 */
 @Test
 public void testGetUserById_2() throws Exception {
 User entity=empDao.getUserById_2(5);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

    /**
     * 根据学生姓名和性别获得学生集合
     */
    List<Student> selectStudentsByNameAndSex(@Param("name") String name,@Param("sex") String sex);

    <select id="selectStudentsByNameAndSex" resultType="student">
        SELECT id,name,sex from student where name=#{name} and sex=#{sex};
    </select>

    /**
     * Method: selectStudentsByNameAndSex
     */
    @Test
    public void testSelectStudentsByNameAndSex() throws Exception {
        List<Student> students=dao.selectStudentsByNameAndSex("rose",null);
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student where 1=1
        <!--如果test为真会输出中间的内容-->
        <if test="name!=null and name!=''">
            and name=#{name}
        </if>

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

    </select>

<!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 --> 
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> 
 SELECT ST.STUDENT_ID, 
 ST.STUDENT_NAME, 
 ST.STUDENT_SEX, 
 ST.STUDENT_BIRTHDAY, 
 ST.STUDENT_PHOTO, 
 ST.CLASS_ID, 
 ST.PLACE_ID 
 FROM STUDENT_TBL ST 
 WHERE 
 <if test="studentName !=null "> 
 ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') 
 </if> 
 <if test="studentSex != null and studentSex != '' "> 
 AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} 
 </if> 
 <if test="studentBirthday != null "> 
 AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} 
 </if> 
 <if test="classId != null and classId!= '' "> 
 AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} 
 </if> 
 <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' "> 
 AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} 
 </if> 
 <if test="placeId != null and placeId != '' "> 
 AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} 
 </if> 
 <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' "> 
 AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} 
 </if> 
 <if test="studentId != null and studentId != '' "> 
 AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} 
 </if> 
</select> 

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

    /**
     * Method: selectStudentsByNameAndSex
     */
    @Test
    public void testSelectStudentsByNameAndSex() throws Exception {
        List<Student> students=dao.selectStudentsByNameAndSex("a",null);
        System.out.println(students);
        Assert.assertNotNull(students);
    }

　　<select id="selectByParams" parameterType="map" resultType="user">
　　　　select * from user
　　　　<where>
　　　　　　<if test="id != null ">id=#{id}</if>
　　　　　　<if test="name != null and name.length()>0" >and name=#{name}</if>
　　　　　　<if test="gender != null and gender.length()>0">and gender = #{gender}</if>
　　　　</where>
　　</select>　　　

    <update id="updateStudent" parameterType="student">

        update student set  
        
        <if test="name!=null and name.lenght()>0">
            name=#{name} ,
        </if>

        <if test="sex!=null and sex.lenght()>0">
            sex=#{sex}
        </if>
        
        where id=#{id}
        
    </update>

    /**
     * 更新学生
     */
    int updateStudent(Student entity);

    <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name}
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

    <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name} , 
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex} , 
            </if>
        </set>
        where id=#{id}
    </update>

    /**
     * Method: updateStudent
     */
    @Test
    public void testUpdateStudent() throws Exception {
        //会将实体中的每一个字段都更新，不好
//        Student entity=dao.selectStudentById(11);
//        //entity.setName("张丽美");
//        entity.setSex("girl");
//
//        Assert.assertEquals(1,dao.updateStudent(entity));

        //不需要先执行查询
        Student student=new Student();
        student.setId(9);
        //只更新了name与sex没有关系
        student.setName("malili");
        Assert.assertEquals(1,dao.updateStudent(student));
    }

    /**
     * 根据学生编号、姓名和性别获得学生集合
     */
    List<Student> selectStudentsByNameAndSex(@Param("id") int id, @Param("name") String name,@Param("sex") String sex);

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student
        <where>
        <choose>
            <when test="id>0">
                id=#{id}
            </when>
            <when test="name!=null and name!=''">
                name=#{name}
            </when>
            <otherwise>
                sex=#{sex}
            </otherwise>
        </choose>
        </where>
    </select>

    /**
     * Method: selectStudentsByNameAndSex
     */
    @Test
    public void testSelectStudentsByNameAndSex() throws Exception {
        List<Student> students=dao.selectStudentsByNameAndSex(1,"rose","girl");
        System.out.println(students);
        Assert.assertNotNull(students);
    }

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

    <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name}
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

    <update id="updateStudent" parameterType="student">
        update student

           <trim prefix="set" suffixOverrides=",">
               <if test="name!=null and name.length()>0">
                   name=#{name},
               </if>

               <if test="sex!=null and sex.length()>0">
                   sex=#{sex},
               </if>
           </trim>

        where id=#{id}
    </update>

<trim prefix = "where" prefixOverrides="and|or" >
...
</trim>

<trim prefix="set" suffixOverrides=",">
...
</trim>

<insert id="find" resultType="Admin">
insert into admin 
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test = "aname != null and aname !='' ">
aname,
</if>
<if test = "city != null and city !='' ">
city,
</if>
<if test = "age != null and age !='' ">
age,
</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test = "aname != null and aname !='' ">
#{aname},
</if>
<if test = "city != null and city !='' ">
#{city},
</if>
<if test = "age != null and age !='' ">
#{age},
</if>
</trim>
</insert>

    <select id="selectStudentsByNameOrSex" resultType="student">
        SELECT id,name,sex from student where name like '%${realname}%' or sex=#{sex};
    </select>

    <select id="selectStudentsByIdOrSex" resultType="student">
        SELECT id,name,sex from student where id=#{no} or sex=#{sex};
    </select>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

    <!--定义sql片段-->
    <sql id="col_student">
        id,name,sex
    </sql>
    

    <select id="selectStudentsByNameOrSex" resultType="student">
        <!--引用sql片段-->
        SELECT <include refid="col_student"></include> from student where name like '%${realname}%' or sex=#{sex};
    </select>

    <select id="selectStudentsByIdOrSex" resultType="student">
        SELECT <include refid="col_student"></include> from student where id=#{no} or sex=#{sex};
    </select>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT <include refid="col_student"></include> from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select> 

    <!--定义sql片段-->
    <sql id="col_student">
        ${alias}.id,${alias}.name,${alias}.sex
    </sql>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT

        <include refid="col_student">
            <property name="alias" value="s"></property>
        </include>

        from student s

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.zhangguo.User">
 select * from user
 <where>
 <!--
 collection:指定输入对象中的集合属性
 item:每次遍历生成的对象
 open:开始遍历时的拼接字符串
 close:结束时拼接的字符串
 separator:遍历对象之间需要拼接的字符串
 select * from user where 1=1 and (id=1 or id=2 or id=3)
 -->
 <foreach collection="ids" item="id" open="and (" close=")" separator="or">
 id=#{id}
 </foreach>
 </where>
</select>

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.zhangguo.User">
 select * from user
 <where>
 <!--
 collection:指定输入对象中的集合属性
 item:每次遍历生成的对象
 open:开始遍历时的拼接字符串
 close:结束时拼接的字符串
 separator:遍历对象之间需要拼接的字符串
 select * from user where 1=1 and id in (1,2,3)
 -->
 <foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
 #{id}
 </foreach>
 </where>
 </select> 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace的名字需要跟接口的类名一致 -->
<mapper namespace="cn.bdqn.dao.UserMapper">
 <!-- 
 1、resultMap属性：type为java实体类；id为此resultMap的标识
 2、resultMap的子元素：
 id – 一般对应到数据库中该行的ID,设置此项可以提高Mybatis性能.
 result – 映射到JavaBean 的某个“简单类型”属性,String,int等.
 association – 映射到JavaBean 的某个“复杂类型”属性,其他JavaBean类.
 collection –复杂类型集合 
 -->
 
 <!--根据roleId获取用户列表: 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
 <!-- <resultMap type="User" id="seachUserResult">
 <result property="id" column="id"/>
 <result property="userCode" column="userCode"/>
 <result property="userName" column="userName"/>
 <result property="roleId" column="roleId"/>
 <result property="roleName" column="roleName"/>
 </resultMap>
 
 <select id="getUserListByRoleId" parameterType="Role" resultMap="seachUserResult">
 select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id and u.roleId = #{id}
 </select> -->
 
 <!-- 根据roleId获取用户列表 association start-->
 <resultMap type="User" id="seachUserResult">
 <result property="id" column="id"/>
 <result property="userCode" column="userCode" />
 <result property="userName" column="userName" />
 <result property="roleId" column="roleId" />
 <!-- <association property="role" javaType="Role" >
 <result property="id" column="id"/>
 <result property="roleCode" column="roleCode"/>
 <result property="roleName" column="roleName"/>
 </association> -->
 <association property="role" javaType="Role" resultMap="roleMap"/>
 </resultMap>
 
 <resultMap type="Role" id="roleMap">
 <result property="id" column="id"/> 
 <result property="roleCode" column="roleCode"/> 
 <result property="roleName" column="roleName"/> 
 </resultMap>
 
 <select id="getUserListByRoleId" parameterType="Role" resultMap="seachUserResult">
 select u.*,r.roleCode as roleCode,r.roleName as roleName from user u,role r where u.roleId = r.id and u.roleId = #{id}
 </select>
 
 <!-- association end-->
 
 <!-- 获取指定用户的地址列表(user表-address表：1对多关系) collection start-->
 <resultMap type="User" id="userMap">
 <id property="id" column="userId"/>
 <collection property="addressList" ofType="Address">
 <id property="id" column="a_id"/>
 <result property="postCode" column="postCode"/>
 <result property="addressContent" column="addressContent"/>
 </collection>
 </resultMap>
 
 <select id="getAddressListByUserId" parameterType="User" resultMap="userMap">
 select *,a.id as a_id from user u,address a where u.id=a.userId and u.id=#{id}
 </select>
 <!-- collection end -->
 
 <resultMap type="User" id="seachUser">
 <result property="id" column="id"/>
 <result property="userCode" column="userCode"/>
 <result property="userName" column="userName"/>
 <result property="roleId" column="roleId"/>
 <result property="roleName" column="roleName"/>
 </resultMap>
 
 <!-- <select id="searchUserList" parameterType="User" resultMap="seachUser">
 select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id
 and u.roleId = #{roleId}
 and u.userCode like CONCAT ('%',#{userCode},'%') //防止sql注入
 and u.userName like CONCAT ('%',#{userName},'%') 
 </select> -->
 
 <!-- 
 1、有些时候，sql语句where条件中，需要一些安全判断，例如按性别检索，如果传入的参数是空的，此时查询出的结果很可能是空的，也许我们需要参数为空时，是查出全部的信息。这是我们可以使用动态sql，增加一个判断，当参数不符合要求的时候，我们可以不去判断此查询条件。
 2、mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类: 
 if 语句 (简单的条件判断) 
 choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.
 trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀) 
 where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误) 
 set (主要用于更新时) 
 foreach (在实现 mybatis in 语句查询时特别有用) 
 -->
 
 <!-- if(判断参数) - 将实体类不为空的属性作为where条件 -->
 <select id="searchUserList" parameterType="User" resultMap="seachUser">
 select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id
 <if test="roleId!=null">
 and u.roleId = #{roleId}
 </if>
 <if test="userCode != null">
 and u.userCode like CONCAT ('%',#{userCode},'%') 
 </if>
 <if test="userName != null">
 and u.userName like CONCAT ('%',#{userName},'%') 
 </if>
 </select>
 
 
 
 
 
 <select id="count" resultType="int">
 select count(1) from user
 </select>
 
 <insert id="add" parameterType="User">
 insert into user (userCode,userName,userPassword) 
 values (#{userCode},#{userName},#{userPassword})
 </insert>
 
 <!-- if/set(判断参数) - 将实体类不为空的属性更新 --> 
 <!-- <update id="update" parameterType="User">
 update user 
 <set>
 <if test="userCode != null and userCode != ''">userCode=#{userCode},</if>
 <if test="userName != null">userName=#{userName},</if>
 <if test="userPassword != null">userPassword=#{userPassword},</if>
 <if test="roleId != null">roleId=#{roleId}</if>
 </set>
 where id=#{id}
 </update> -->
 
 <!-- if/trim代替set(判断参数) - 将实体类不为空的属性更新 --> 
 <update id="update" parameterType="User">
 update user 
 <trim prefix="set" suffixOverrides=",">
 <if test="userCode != null and userCode != ''">userCode=#{userCode},</if>
 <if test="userName != null">userName=#{userName},</if>
 <if test="userPassword != null">userPassword=#{userPassword},</if>
 <if test="roleId != null">roleId=#{roleId}</if>
 </trim>
 where id=#{id}
 </update>
 
 <!--注意： 你可以传递一个List实例或者数组作为参数对象传给MyBatis。
 当你这么做的时候，MyBatis会自动将它包装在一个Map中，用名称在作为键。
 List实例将会以“list”作为键，而数组实例将会以“array”作为键。
 配置文件中的parameterType是可以不配置的-->
 <resultMap type="User" id="userMapByDep">
 <result property="id" column="id"/>
 <result property="userCode" column="userCode"/>
 <result property="userName" column="userName"/>
 </resultMap>
 <!-- foreach(循环array参数) - 作为where中in的条件 -->
 <select id="getUserByDepId_foreach_array" resultMap="userMapByDep">
 select * from user where depId in 
 <foreach collection="array" item="depIds" open="(" separator="," close=")">
 #{depIds}
 </foreach>
 </select>
 
 <!-- foreach(循环List<String>参数) - 作为where中in的条件 -->
 <select id="getUserByDepId_foreach_list" resultMap="userMapByDep">
 select * from user where depId in 
 <foreach collection="list" item="depIdList" open="(" separator="," close=")">
 #{depIdList}
 </foreach>
 </select>
 
 
 <delete id="delete" parameterType="User">
 delete from user where id=#{id}
 </delete>
 
 <select id="getUserList" resultType="User">
 select * from user
 </select>
</mapper>

    /**
     * 删除多个学生通过编号
     */
    int deleteStudents(List<Integer> ids);

    <delete id="deleteStudents">
        delete from student where id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    /**
     * Method: deleteStudents
     */
    @Test
    public void testDeleteStudents() throws Exception {
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(8);
        ids.add(9);
        Assert.assertEquals(2,dao.deleteStudents(ids));
    }

      <!-- List<Employee> getEmpsTestInnerParameter(Employee employee); -->
      <select id="getEmpsTestInnerParameter" resultType="com.hand.mybatis.bean.Employee">
          <!-- bind:可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
          <bind name="bindeName" value="'%'+eName+'%'"/> eName是employee中一个属性值
          SELECT * FROM emp 
          <if test="_parameter!=null">
            where ename like #{bindeName}
          </if>
      </select>

请实现一个简易图书管理系统（LibSystem），实现图书管理功能，要求如下：
1、管理数据库中所有图书（Books），包含图书编号（isbn）、书名（title）、作者（author）、价格（price）、出版日期（publishDate）
2、Maven多模块+MySQL+Git+MyBatis+JUnit单元测试
3、表示层可以是AJAX或JSTL

请实现一个迷你图书管理系统（LibSystem），实现图书管理功能，要求如下：
1、管理数据库中所有图书分类（Categories），包含图书编号（id）,名称（name）
2、管理数据库中所有图书（Books），包含图书编号（isbn）、类别（categoryId，外键）书名（title）、作者（author）、价格（price）、出版日期（publishDate）、封面（cover）、详细介绍（details）
3、分页
4、多条件组件查询（3个以上的条件）
5、上传封面
6、富文本编辑器