Mybatis 使用Mapper接口的Sql动态代码方式进行CURD和分页查询

1、Maven的pom.xml

2、配置文件

2.1、db.properties

2.2、mybatis.xml

2.3、log4j.xml

3、MybatisUtil工具类

4、Mapper映射文件

<?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.mcs.mapper.EmployeeMapper">
    <resultMap id="employeeResultMap" type="com.mcs.entity.Employee">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
        <result column="birthday" property="birthday" jdbcType="DATE" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="telephone" property="telephone" jdbcType="VARCHAR" />
        <result column="cellphone" property="cellphone" jdbcType="VARCHAR" />
        <result column="address" property="address" jdbcType="VARCHAR" />
        <result column="department_id" property="departmentId" jdbcType="INTEGER" />
    </resultMap>

    <!-- 新增职员，并返回插入后的ID值 -->
    <insert id="add" keyColumn="id" keyProperty="id" useGeneratedKeys="true" parameterType="Employee">
        insert into t_employee
        ( name, sex, birthday, email, telephone, cellphone, address, department_id )
        values
        ( #{name}, #{sex}, #{birthday}, #{email}, #{telephone}, #{cellphone}, #{address}, #{departmentId} )
    </insert>

    <update id="updateById" parameterType="Employee">
        update t_employee
        set name = #{name,jdbcType=VARCHAR},
        sex = #{sex,jdbcType=VARCHAR},
        birthday = #{birthday,jdbcType=DATE},
        email = #{email,jdbcType=VARCHAR},
        telephone = #{telephone,jdbcType=VARCHAR},
        cellphone = #{cellphone,jdbcType=VARCHAR},
        address = #{address,jdbcType=VARCHAR},
        department_id = #{departmentId,jdbcType=INTEGER}
        where id = #{id,jdbcType=INTEGER}
    </update>

    <delete id="deleteById" parameterType="Integer">
        delete from t_employee
        where id = #{id}
    </delete>

    <select id="findById" parameterType="Integer" resultMap="employeeResultMap">
        select
        id,name, sex, birthday, email, telephone, cellphone, address, department_id
        from t_employee
        where id = #{id}
    </select>

    <!-- 基本字段 -->
    <sql id="baseColumn">
        id,name, sex, birthday, email, telephone, cellphone, address, department_id
    </sql>


    <sql id="whereParam">
        <where>
            <if test="id!=null">
                id = #{id}
            </if>
            <if test="name!=null">
                name like #{name}
            </if>
            <if test="sex!=null">
                sex = #{sex}
            </if>
            <if test="departmentId!=null">
                department_id = #{departmentId}
            </if>
        </where>
    </sql>
    <!-- 动态查询与分页 -->
    <select id="findListByDynamic" parameterType="EmployeeCustom" resultMap="employeeResultMap">
        select
        <include refid="baseColumn"></include>
        from t_employee
        <include refid="whereParam"></include>
        <if test="pageNo!=null">
            <if test="pageSize!=null">
                limit #{pageNo}, #{pageSize}
            </if>
        </if>
    </select>
    <select id="findListByDynamicCount" parameterType="EmployeeCustom" resultType="Long">
        select count(id) totalNumber
        from t_employee
        <include refid="whereParam"></include>
    </select>

    <!-- 动态更新 -->
    <update id="dynamicUpdateById" parameterType="Employee">
        update t_employee
        <!-- set标签自动判断哪个是最后一个字段，会自动去掉最后一个,号 -->
        <set>
            <if test="name!=null">
                name = #{name},
            </if>
            <if test="sex!=null">
                sex = #{sex},
            </if>
            <if test="birthday!=null">
                birthday = #{birthday},
            </if>
            <if test="email!=null">
                email = #{email},
            </if>
            <if test="telephone!=null">
                telephone = #{telephone},
            </if>
            <if test="cellphone!=null">
                cellphone = #{cellphone},
            </if>
            <if test="address!=null">
                address = #{address},
            </if>
            <if test="departmentId!=null">
                department_id = #{departmentId},
            </if>
        </set>
        where id = #{id}
    </update>

    <!-- 动态批量删除，参数：Integer[] ids delete from t_employee where id in (10,12,13) -->
    <delete id="dynamicDeleteByArray">
        delete from t_employee where id in
        <!-- foreach用于迭代数组元素 open表示开始符号 close表示结束符合 separator表示元素间的分隔符 item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同 #{ids}表示数组中的每个元素值 -->
        <foreach collection="array" open="(" close=")" separator="," item="ids">
            #{ids}
        </foreach>
    </delete>

    <!-- 动态批量删除，参数：List<Integer> ids delete from t_employee where id in (10,12,13) -->
    <delete id="dynamicDeleteByList">
        delete from t_employee where id in
        <foreach collection="list" open="(" close=")" separator="," item="ids">
            #{ids}
        </foreach>
    </delete>

    
    <sql id="key">
        <!-- 去掉最后一个, -->
        <trim suffixOverrides=",">
            <if test="name!=null">
                name,
            </if>
            <if test="sex!=null">
                sex,
            </if>
            <if test="birthday!=null">
                birthday,
            </if>
            <if test="email!=null">
                email,
            </if>
            <if test="telephone!=null">
                telephone,
            </if>
            <if test="cellphone!=null">
                cellphone,
            </if>
            <if test="address!=null">
                address,
            </if>
            <if test="departmentId!=null">
                department_id,
            </if>
        </trim>
    </sql>
    <sql id="value">
        <!-- 去掉最后一个, -->
        <trim suffixOverrides=",">
            <if test="name!=null">
                #{name},
            </if>
            <if test="sex!=null">
                #{sex},
            </if>
            <if test="birthday!=null">
                #{birthday},
            </if>
            <if test="email!=null">
                #{email},
            </if>
            <if test="telephone!=null">
                #{telephone},
            </if>
            <if test="cellphone!=null">
                #{cellphone},
            </if>
            <if test="address!=null">
                #{address},
            </if>
            <if test="departmentId!=null">
                #{departmentId},
            </if>
        </trim>
    </sql>
    <!-- 动态增加 -->
    <insert id="dynamicInsert" parameterType="Employee">
        insert into t_employee(<include refid="key"/>) values(<include refid="value"/>)
    </insert>
    
    

</mapper>

5、Mapper映射文件对应的接口文件

package com.mcs.mapper;

import java.util.List;

import com.mcs.entity.Employee;
import com.mcs.entity.EmployeeCustom;

public interface EmployeeMapper {
    /**
     * 新增员工
     */
    public void add(Employee employee) throws Exception;
    /**
     * 根据Id修改员工
     */
    public void updateById(Employee employee) throws Exception;
    /**
     * 根据ID删除员工
     */
    public void deleteById(Integer id) throws Exception;
    /**
     * 根据ID查找员工
     */
    public Employee findById(Integer id) throws Exception;
    /**
     * 根据输入参数，动态查找员工，可分页
     */
    public List<Employee> findListByDynamic(EmployeeCustom employeeCustom) throws Exception;
    /**
     * 根据输入参数，动态合计员工记录数量
     */
    public Long findListByDynamicCount(EmployeeCustom employeeCustom) throws Exception;
    /**
     * 根据输入参数，动态更新
     */
    public void dynamicUpdateById(Employee employee) throws Exception;
    /**
     * 根据输入的Array参数，动态删除
     */
    public void dynamicDeleteByArray(Integer[] ids) throws Exception;
    /**
     * 根据输入List参数，动态删除
     */
    public void dynamicDeleteByList(List<Integer> ids) throws Exception;
    /**
     * 根据输入参数，动态插入
     */
    public void dynamicInsert(Employee employee) throws Exception;
}

此文件应与Mapper在同一命名空间下

6、测试代码

package com.mcs.test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.mcs.entity.Employee;
import com.mcs.entity.EmployeeCustom;
import com.mcs.mapper.EmployeeMapper;
import com.mcs.util.MybatisUtil;

public class TestEmployeeMapper {
    /**
     * Logger for this class
     */
    private static final Logger logger = Logger.getLogger(TestEmployeeMapper.class);

    private EmployeeMapper employeeMapper;
    private SqlSession sqlSession = null;

    @Before
    public void init() {
        sqlSession = MybatisUtil.getSqlSession();
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }

    @After
    public void destory() {
        MybatisUtil.closeSqlSession();
    }

    @Test
    public void testFindById() throws Exception {
        Employee employee = employeeMapper.findById(1);
        logger.debug(employee);
    }

    @Test
    public void testFindAll() throws Exception {
        List<Employee> employees = employeeMapper.findListByDynamic(null);
        logger.debug(employees);
        Long totalNumber = employeeMapper.findListByDynamicCount(null);
        logger.debug("共" + totalNumber + "条记录");
    }

    @Test
    public void testAdd() throws Exception {
        Employee employee = new Employee();
        employee.setName("赵小凤");
        employee.setSex("female");
        employee.setBirthday(new Date());
        employee.setEmail("xiaofeng@126.com");
        try {
            employeeMapper.add(employee);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }

        logger.debug(employee);
    }

    @Test
    public void testEditById() throws Exception {
        Employee employee = employeeMapper.findById(13);
        employee.setDepartmentId(3);
        employee.setAddress("天津");

        try {
            employeeMapper.updateById(employee);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }

        logger.debug(employee);
    }

    @Test
    public void testDeleteById() throws Exception {
        Employee employee = employeeMapper.findById(13);
        logger.debug(employee);
        try {
            employeeMapper.deleteById(13);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }

        logger.debug("已成功删除员工：" + employee.getName());
    }

    @Test
    public void testFindListByParam() throws Exception {
        EmployeeCustom employeeCustom = new EmployeeCustom();
        employeeCustom.setSex("male");
        employeeCustom.setPageNo(0 * 5);
        employeeCustom.setPageSize(5);

        List<Employee> employees = employeeMapper.findListByDynamic(employeeCustom);
        for (Employee employee : employees) {
            logger.debug(employee.getName());
        }

        Long totalNumber = employeeMapper.findListByDynamicCount(employeeCustom);
        if (employees.size() > 0) {
            logger.debug("当前第" + (employeeCustom.getPageNo() + 1) + "页");
            logger.debug("每页" + employeeCustom.getPageSize() + "条记录");
            logger.debug("共" + totalNumber + "条记录");
        }
    }

    @Test
    public void testDynamicUpdateByID() throws Exception {
        Employee employee = new Employee();
        employee.setId(13);
        employee.setName("张丽");

        try {
            employeeMapper.dynamicUpdateById(employee);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }

    }

    @Test
    public void testDynamicDeleteByArray() throws Exception {
        Integer[] ids = new Integer[] { 10, 12, 13 };
        try {
            employeeMapper.dynamicDeleteByArray(ids);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }
    }

    @Test
    public void testDynamicDeleteByList() throws Exception {
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(10);
        ids.add(12);
        ids.add(13);

        try {
            employeeMapper.dynamicDeleteByList(ids);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }
    }
    
    @Test
    public void testDynamicInsert() throws Exception {
        Employee employee = new Employee();
        employee.setName("赵小梅");
        employee.setSex("female");

        try {
            employeeMapper.dynamicInsert(employee);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }
    }

}