实现分页查询

在实体类Student设置属性，并获取getter setter方法

    private Integer pageNo;//页码
    private Integer rowCount;//单页查询行数

//此处为getset方法

IStudentDao设置接口

    public List<Student> query(Student student);
    public Integer queryCount(Student student);

StudentDao实现接口

    @Resource
    private SqlSession sqlSession;

    @Override
    public List<Student> query(Student student) {
        if(student.getPageNo() != null){
            Integer pageNo = student.getPageNo();
            student.setPageNo((pageNo-1)*student.getRowCount());
        }

        return sqlSession.selectList("com.st.dao.IStudentDao.query", student);
    }

    @Override
    public Integer queryCount(Student student) {
        return sqlSession.selectOne("com.st.dao.IStudentDao.queryCount",student);
    }

LoginService查询方法

   public List<Student> query(Student student){
        List<Student> list = studentDao.query(student);
        return list;
    }

1.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="com.st.dao.IStudentDao"><!--namespace属性关联dao层的接口-->
    <!-- 结果集映射关系 -->
    <resultMap id="baseMap" type="student">
        <result column="id"     property="id"    jdbcType="INTEGER"></result>
        <result column="s_name" property="sName" jdbcType="VARCHAR"></result>
        <result column="s_sex"  property="sSex"  jdbcType="INTEGER"></result>
    </resultMap>


    <!-- id属性对应接口的签名方法 resultMap指定结果集映射关系-->
    <select id="query" parameterType="student" resultMap="baseMap">
        select * from student
        <where>
            <if test="id != null"> and id = #{id}</if>
            <if test="sName != null and sName != ''"> and s_name = #{sName}</if>
            <if test="sSex != null"> and s_sex = #{sSex}</if>
        </where>
        <if test="pageNo != null">
            limit #{pageNo},#{rowCount}
        </if>
    </select>

    <select id="queryCount" parameterType="student" resultType="java.lang.Integer">
        select count(*) from student
        <where>
            <if test="id != null"> and id = #{id}</if>
            <if test="sName != null and sName != ''"> and s_name = #{sName}</if>
            <if test="sSex != null"> and s_sex = #{sSex}</if>
        </where>
    </select>

    <!--<select id="selectAllByPage" parameterType="com.st.model.Page" resultType="com.st.model.Student">
        select * from student LIMIT #{offset},#{pageSize}
    </select>-->
</mapper>

前端页面

<div>
    <button th:onclick="jump(1)">首页</button>
    <button th:onclick="jump([[${pageNo - 1}]])">上一页</button>
    <span th:text="${pageNo}+'/'+${pageCount}+'页'"></span>
    <button th:onclick="jump([[${pageNo + 1}]])">下一页</button>
    <button th:onclick="jump([[${pageCount}]])">尾页</button>
</div>
<script>
    function jump(pageNo){
        var params = $("form").serialize();
        var url = "query?" + params + "&pageNo=" + pageNo;
        console.info(url);
        window.location.href = url;
    }
</script>

实现更新操作

1.更新按钮绑定点击事件

<button th:onclick="update([[${stu}]])"></button>

<script>
	function update(stu){
        window.location.href="toUpdate?id="+stu.id+"&sName="+stu.sName+"&sSex="+stu.sSex;
    }
</script>

    //    DI依赖注入，把容器内的对象取出
    @Autowired
    private User user;
    @Autowired
    private LoginService loginService;
    @Autowired
    private IStudentDao studentDao;
	
	
 //跳转到更新页面
    @RequestMapping("toUpdate")
    public ModelAndView toUpdate(Student student) {
        List<Student> list = studentDao.query(student);
        list.add(student);
        ModelAndView mv = new ModelAndView();
        mv.addObject("stu",list.get(0));
        mv.setViewName("stuUpdate");
        return mv;
    }
    //更新
    @RequestMapping("update")
    public String update(Student student) {
//        ModelAndView mv = new ModelAndView();
        int count = studentDao.update(student);
        if(count>0){
            //跳转页面
//            请求转发到列表页forward：另一个controller的映射路径/方法的映射路径
//            return "forward:query";
//            重定向
            return "redirect:http://localhost:8080/ssm/my/query";
        }else {
            return "error";
        }
    }

  @Override
    public Integer update(Student student) {

        return sqlSession.update("com.st.dao.IStudentDao.update", student);
    }

<update id="update" parameterType="student">
        update student
        <set>
            <if test="sName != null and sName != ''">s_name = #{sName},</if>
            <if test="sSex != null">s_sex = #{sSex},</if>
        </set>
        <where>
            <if test="id != null">and id = #{id}</if>
        </where>
    </update>

添加并返回主键

mapper

<!--
       插入并返回主键
       useGeneratedKeys设置是否使用JDBC的getGenereatedKeys()获取新增记录的主键并赋值给keyProperty指定的对象变量
       keyProperty声明对应的实体类主键变量
       -->
    <insert id="add"  parameterType="student" useGeneratedKeys="true" keyProperty="id">
        insert into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sName != null and sName != ''">s_name,</if>
            <if test="sSex != null">s_sex,</if>
        </trim>
        <trim prefix="VALUES(" suffix=")" suffixOverrides=",">
            <if test="sName != null and sName != ''">#{sName},</if>
            <if test="sSex != null">#{sSex},</if>
        </trim>
    </insert>

controller

    //插入并返回主键
    @Autowired
    private IStudentDao studentDao;
    @RequestMapping("add")
    public void add(Student student){
        //返回事务影响行数
        Integer count = studentDao.add(student);
        System.out.println("受影响行数："+count);
        System.out.println("id="+student.getId());
    }

实例化dao

   @Override
    public Integer add(Student student) {
        return sqlSession.insert("com.st.dao.IStudentDao.add", student);
    }

批量插入

    <!--批量插入-->
    <insert id="addBatch" parameterType="java.util.List">
	<!-- 
		使用批量插入需要在jdbc：url中添加allowMultiQueries=true
		作用：
		1.可以在sql语句后携带分号，实现多语句执行
		2.可以执行批处理，同时发出多个SQL语句
		
		collection被遍历的结果集
		item 本次遍历获取的元素
		index 当前迭代的次数
		separator：每次一遍历之后，语句末尾拼接指定字符
	-->
        <foreach collection="list" item="stu" separator=";">
            insert into student
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="stu.sName != null and stu.sName != ''">s_name,</if>
                <if test="stu.sSex != null">s_sex,</if>
            </trim>
            <trim prefix="VALUES(" suffix=")" suffixOverrides=",">
                <if test="stu.sName != null and stu.sName != ''">#{stu.sName},</if>
                <if test="stu.sSex != null">#{stu.sSex},</if>
            </trim>
        </foreach>
        
    </insert>

dao实例层

 @Override
    public Integer addBatch(List<Student> list) {
        return sqlSession.insert("com.st.dao.IStudentDao.addBatch",list);
    }

controller内方法

    @RequestMapping("addBatch")
    @ResponseBody
    public String addBatch(){
        List<Student> list = new ArrayList<>();
        list.add(new Student("123",1));
        list.add(new Student("321",0));
        list.add(new Student("456",1));
        list.add(new Student("654",0));

        Integer count = studentDao.addBatch(list);
        return count.toString();
    }

封装字段或sql语句

<!--封装sql语句-->
<sql id="cs">id,s_name,s_sex</sql>

  <select id="query" parameterType="student" resultMap="baseMap">
        select <include refid="cs"/> from student
        <where>
            <if test="id != null"> and id = #{id}</if>
            <if test="sName != null and sName != ''"> and s_name = #{sName}</if>
            <if test="sSex != null"> and s_sex = #{sSex}</if>
        </where>
        <if test="pageNo != null">
            limit #{pageNo},#{rowCount}
        </if>
    </select>