MyBatis学习笔记 - (2)MyBatis 分页查询、动态

上一篇 MyBatis学习笔记 - (1)MyBatis CRUD基本使用，本文在上一篇案例基础上添加分页查询、动态添加、动态查询、动态插入、动态删除功能。
StudentDaoImpl.java中分别添加以下方法，在StudentMapper.xml中添加对应的映射：

1.分页查询(根据索引和每页数据量)

    /**
     * 分页查询
     * */
    @Override
    public List<Student> findByPager(int pager, int count) {
        SqlSession sqlSession = MybatisUtil.getSqlSesson();
        Map<String, Integer> map = new LinkedHashMap<>();
        map.put("offset", pager * count);
        map.put("count", count);
        List<Student> studentList = sqlSession.selectList(Student.class.getName() + ".findByPager", map);
        sqlSession.commit();
        return studentList;
    }

分页查询数据sql 语句：

select  * from students  limit ? offset ?  

对应StudentMapper.xml 配置

   <!--分页查询-->
    <select id="findByPager" parameterType="map" resultType="com.test.mybatis.Student">
        select * from students limit #{count } offset #{offset}
    </select>

2.动态添加（根据id、name、height是否为空自动拼接sql语句）

    /**
     * 动态添加
     */
    @Override
    public void addDynamic(Integer id, String name, Float height) {
        Student student = new Student(id, name, height);
        SqlSession sqlSession = MybatisUtil.getSqlSesson();
        sqlSession.insert(Student.class.getName() + ".addDynamic", student);
        sqlSession.commit();
    }

添加数据sql 语句：

insert  into  students (s_id , s_name , s_height) values (? , ? , ? ) 

对应StudentMapper.xml 配置

    <!--动态添加-->
    <sql id="key">
        <trim suffixOverrides=",">
            <if test="id!=null">
                s_id,
            </if>
            <if test="name!=null">
                s_name,
            </if>
            <if test="height!=null">
                s_height,
            </if>
        </trim>
    </sql>

    <sql id="value">
        <trim suffixOverrides=",">
            <if test="id!=null">
                #{id},
            </if>
            <if test="name!=null">
                #{name},
            </if>
            <if test="height!=null">
                #{height},
            </if>
        </trim>
    </sql>

    <insert id="addDynamic" parameterType="com.test.mybatis.Student">
        insert into students (<include refid="key"/>) values (<include refid="value"/>)
    </insert>

3.动态删除（根据id、name、height是否为空自动拼接sql语句，实现指定条件删除）

    /**
     * 动态删除
     */
    @Override
    public void deleteDynamic(Integer [] ids ) {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSesson();
            sqlSession.delete(Student.class.getName() + ".deleteDynamic",ids);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            System.out.println(e.getMessage());
            throw e;
        } finally {
            MybatisUtil.closeSqlSesson();
        }

    }

删除数据sql 语句：

delete  students  where  s_id in ( ?,?,?) 

对应StudentMapper.xml 配置

    <delete id="deleteDynamic">
        delete from students where s_id in
        <foreach collection="array" open="(" close=")" separator="," item="ids">
            #{ids}
        </foreach>
    </delete>

3.动态查询（根据id、name、height是否为空自动拼接sql语句，实现多条件查询）

    /**
     * 动态查询
     */
    @Override
    public List<Student> findDynamic(Integer id, String name, Float height) {
        SqlSession sqlSession = null;
        Map<String, Object> map = new LinkedHashMap<>();
        map.put("id", id);
        map.put("name", name);
        map.put("height", height);
        List<Student> studentList;
        try {
            sqlSession = MybatisUtil.getSqlSesson();
            studentList = sqlSession.selectList(Student.class.getName() + ".findDynamic", map);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            System.out.println(e.getMessage());
            throw e;
        } finally {
            MybatisUtil.closeSqlSesson();
        }
        return studentList;
    }

查询数据sql 语句：

select * from students  where  s_id = ?  and s_name = ? and s_height = ? 

对应StudentMapper.xml 配置

    <select id="findDynamic" parameterType="map" resultType="com.test.mybatis.Student">
        select * from students
        <where>
            <if test="id!=null">
                and s_id=#{id}
            </if>
            <if test="name!=null">
                and s_name=#{name}
            </if>
            <if test="height!=null">
                and s_height=#{height}
            </if>

        </where>

    </select>

4.动态更新（根据id确定记录后，根据其他字段是否为空进行自动拼接sql语句，实现指定字段更新）

    /**
     * 动态更新
     */
    @Override
    public void updateDynamic(Integer id, String name, Float height) {
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSesson();
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("id", id);
            map.put("name", name);
            map.put("height", height);
            sqlSession.update(Student.class.getName()+".updateDynamic",map);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            System.out.println(e.getMessage());
            throw e;
        } finally {
            MybatisUtil.closeSqlSesson();
        }
    }

更新数据sql 语句：

update students set s_name = ? , s_height = ? where s_id = ? 

对应StudentMapper.xml 配置

    <update id="updateDynamic" parameterType="map">
        update students
        <set>
            <if test="name!=null">
                s_name=#{name},
            </if>
            <if test="height!=null">
                s_height=#{height},
            </if>
        </set>
        where s_id=#{id}
    </update>