mybatis分頁的實現（mysql）

標籤：

初學mybatis分頁查詢；包括無條件分頁和有條件分頁

Student.java

package cn.buaa.mybatis.app3;public class Student {private Integer id;private String name;private Double sal;public Student(Integer id, String name, Double sal) {super();this.id = id;this.name = name;this.sal = sal;}public Student() {super();}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Double getSal() {return sal;}public void setSal(Double sal) {this.sal = sal;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", sal=" + sal + "]";}}

StudentDao.java

package cn.buaa.mybatis.app3;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import cn.buaa.mybatis.util.MybatisUtil;/** * 持久層 *  * @author 梧桐下的茵 *  */public class StudentDao {/** * 增加學生 */public void add(Student student) throws Exception {SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();sqlSession.insert(Student.class.getName() + ".add", student);// 事物提交sqlSession.commit();} catch (Exception e) {e.printStackTrace();sqlSession.rollback();throw e;} finally {MybatisUtil.closeSqlSession();}}/** * 無條件分頁 */public List<Student> findAllWithFy(int start,int size) throws Exception {SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();Map<String,Object> map = new LinkedHashMap<String,Object>();map.put("pstart", start);map.put("psize", size);List<Student> studentList = sqlSession.selectList(Student.class.getName() + ".findAllWithFy", map);return studentList;} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}/** * 有條件分頁 */public List<Student> findAllByNameWithFy(String name,int start,int size) throws Exception {SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();Map<String,Object> map = new LinkedHashMap<String,Object>();map.put("pname", "%"+name+"%");map.put("pstart", start);map.put("psize", size);List<Student> studentList = sqlSession.selectList(Student.class.getName() + ".findAllByNameWithFy", map);return studentList;} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}// 測試public static void main(String[] args) throws Exception {StudentDao dao = new StudentDao();/*for (int i = 1; i < 11; i++) {dao.add(new Student(i, "呵呵", 7000D));}*/System.out.println("---------------第一頁--------------");List<Student> studentList = dao.findAllByNameWithFy("呵",0, 3);for(Student student :studentList){System.out.println(student.toString());}System.out.println("---------------第二頁--------------");List<Student> studentList2 = dao.findAllByNameWithFy("呵",3, 3);for(Student student :studentList2){System.out.println(student.toString());}System.out.println("---------------第三頁--------------");List<Student> studentList3= dao.findAllByNameWithFy("呵",6, 3);for(Student student :studentList3){System.out.println(student.toString());}System.out.println("---------------第四頁--------------");List<Student> studentList4= dao.findAllByNameWithFy("呵",9, 3);for(Student student :studentList4){System.out.println(student.toString());}}}

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="cn.buaa.mybatis.app3.Student">         <resultMap type="cn.buaa.mybatis.app3.Student" id="studentMap">        <id property="id" column="t_id"/>        <result property="name" column="t_name"/>        <result property="sal" column="t_sal"/>    </resultMap>            <insert id="add" parameterType="cn.buaa.mybatis.app3.Student" >                insert into students (t_id,t_name,t_sal) values(#{id},#{name},#{sal})       </insert>              <!--   無條件查詢-->       <select id="findAllWithFy" parameterType="map" resultMap="studentMap">                select t_id,t_name,t_sal from students                limit #{pstart},#{psize}       </select>             <!--   有條件查詢-->       <select id="findAllByNameWithFy" parameterType="map" resultMap="studentMap">                select t_id,t_name,t_sal from students                where t_name like #{pname}                limit #{pstart},#{psize}       </select></mapper>

mybatis.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 resource="db.properties"/>        <!-- 設定類型別名 -->        <typeAliases >                <typeAlias type="cn.buaa.mybatis.app1.Student" alias="student"/>        </typeAliases>        <!--   設定預設的串連環境資訊 -->        <environments default="mysql_developer">               <!--    串連環境資訊  ，隨便起一個唯一的名字 -->                <environment id="mysql_developer">                        <!--   mybatis使用jdbc事物管理方式 -->                        <transactionManager type="jdbc"></transactionManager>                        <!--     mybatis使用串連池方式來擷取串連 -->                        <dataSource type="pooled">                                <property name="driver" value="com.mysql.jdbc.Driver"/>                                <property name="url" value="jdbc:mysql://localhost:3306/emp"/>                                <property name="username" value="root"/>                                <property name="password" value="123456"/>                        </dataSource>                </environment>                                <!--    串連環境資訊  ，隨便起一個唯一的名字 -->                <environment id="oracle_developer">                        <!--   mybatis使用jdbc事物管理方式 -->                        <transactionManager type="jdbc"></transactionManager>                        <!--     mybatis使用串連池方式來擷取串連 -->                        <dataSource type="pooled">                                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>                                <property name="url" value="jdbc:oracle:thin:@localhost:1521/orcl"/>                                <property name="username" value="zhangdong"/>                                <property name="password" value="123456"/>                        </dataSource>                </environment>        </environments>        <!--    載入對應檔-->        <mappers>                <mapper resource="cn/buaa/mybatis/app1/studentMapper.xml"/>                <mapper resource="cn/buaa/mybatis/app2/studentMapper.xml"/>                <mapper resource="cn/buaa/mybatis/app3/studentMapper.xml"/>        </mappers></configuration>

db.properties

mysql.driver=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://127.0.0.1:3306/empmysql.username=rootmysql.password=123456oracle.driver=oracle.jdbc.driver.OracleDriveroracle.url=jdbc:oracle:thin:@127.0.0.1:1521:orcloracle.username=rootoracle.password=123456

mybatis分頁的實現（mysql）