Mybatis实现查询分页、使用注解开发
一、MySQL分页查询语句
select * from user limit #{startIndex},#{pageSize};
# startIndex : 起始位置 ,默认是0开始
# pageSize :页面大小
例如:
select * from user limit 0,3
二、Mybatis中使用limit实现分页
接口:
//查询全部用户实现分页 List<User>selectUserByLimit(Map<String,Integer> map);
编写对应mapper映射文件的方法
<selectid="selectUserByLimit"parameterType="Map"resultType="User"> select * from mybatis.user limit #{startIndex},#{pageSize}</select>
测试模拟分页
@TestpublicvoidselectUserByLimit(){//创建sqlSession SqlSessionFactory sqlSessionFactory= MyBatisUtils.getSqlSessionFactory(); SqlSession sqlSession= sqlSessionFactory.openSession();//模拟前端数据数据int currentPage=2;//当前是第几页int pageSize=2;//页面大小 Map<String, Integer> map=newHashMap<String, Integer>(); map.put("startIndex",(currentPage-1)*pageSize); map.put("pageSize",pageSize);//测试 UserDao mapper= sqlSession.getMapper(UserDao.class); List<User> users= mapper.selectUserByLimit(map);for(User user: users){ System.out.println(user);} sqlSession.close();//关闭连接}
三、Mybatis中使用RowBounds实现分页
接口
//查询全部用户实现分页使用RowBounds List<User>selectUserByRowBounds();
Mapper映射文件
<selectid="selectUserByRowBounds"resultType="User"> select * from mybatis.user</select>
测试模拟分页
@TestpublicvoidselectUserByRowBounds(){//创建sqlSession SqlSessionFactory sqlSessionFactory= MyBatisUtils.getSqlSessionFactory(); SqlSession sqlSession= sqlSessionFactory.openSession();int currentPage=2;//当前页int pageSize=2;//页面大小 RowBounds rowBounds=newRowBounds((currentPage-1)* pageSize, pageSize);//注意点;使用RowBounds就不能使用getMapper了//selectList: 接收一个List//selectMap: 接收一个Map//selectOne : 接收只有一个对象的时候 List<User> users= sqlSession.selectList("org.westos.dao.UserDao.selectUserByRowBounds", null, rowBounds);for(User user: users){ System.out.println(user);}}
limit 和 rowBounds区别
- rowBounds 本质就是封装了limit
- limit 是在SQL层面实现分页
- rowBounds 在代码层面实现分页
四、Mybatis使用注解开发
注解可以替代一些xml文件中的配置,CRUD的注解:
- @insert() 插入数据
- @delete() 删除数据
- @update() 更新数据
- @select() 查询数据
UserMapper接口
package org.westos.dao;import org.apache.ibatis.annotations.Delete;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.Update;import org.westos.pojo.User;import java.util.List;publicinterfaceUserMapper{//查询全部用户@Select("select * from user")
List<User>selectUser();//通过ID查询用户@Select("select * from user where id = #{uid}")
UserselectUserById(@param("uid")int id);//添加用户@Insert("insert into user (id,name,pwd) values (#{id},#{name},#{pwd})")intaddUser(User user);//修改用户信息@Update("update user set name = #{name},pwd = #{pwd} where id = #{id}")intupdateUser(User user);//删除用户@Delete("delete from user where id=#{id}")intdeleteUserById(@param("id")int id);}
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><!--修改配置文件--><propertiesresource="database.properties"/><!--配置别名--><typeAliases><typeAliastype="org.westos.pojo.User"alias="User"/><packagename="org.westos.pojo"/></typeAliases><!--配置环境,这里可以有多套环境 default代表默认的是那一套--><environmentsdefault="development"><!--配置一套环境 id .环境的名字--><environmentid="development"><!--transactionManager:事务管理,type:jdbc--><transactionManagertype="JDBC"/><!--dataSource 数据源--><dataSourcetype="POOLED"><propertyname="driver"value="${driver}"/><!--xml中不允许&符号直接出现,我们需要使用 & 代替--><propertyname="url"value="${url}"/><propertyname="username"value="${username}"/><propertyname="password"value="${password}"/></dataSource></environment></environments><!--关联映射文件--><mappers><!--class对应的是一个接口类--><!--resource对应的是一个接口类的映射文件--><!--<mapper resource="org/westos/dao/userMapper.xml"/>--><mapperclass="org.westos.dao.UserMapper"/></mappers></configuration>
测试类
package org.westos.dao;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.junit.Test;import org.westos.pojo.User;import org.westos.utils.MybatisUtils;import java.sql.ParameterMetaData;import java.util.Date;import java.util.HashMap;import java.util.List;publicclassUserMapperTest{@TestpublicvoidselectUser(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
UserMapper mapper= sqlSession.getMapper(UserMapper.class);
List<User> users= mapper.selectUser();for(User user: users){
System.out.println(user);}}@TestpublicvoidselectUserById(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
UserMapper mapper= sqlSession.getMapper(UserMapper.class);
User user= mapper.selectUserById(3);
System.out.println(user);}@TestpublicvoidinsertUser(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
UserMapper mapper= sqlSession.getMapper(UserMapper.class);int i= mapper.addUser(newUser(8,"小明","236546"));
System.out.println(i);}@TestpublicvoidupdateUser(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
UserMapper mapper= sqlSession.getMapper(UserMapper.class);int i= mapper.updateUser(newUser(3,"橘右京","12344"));
System.out.println(i);}@Testpublicvoiddelete(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
UserMapper mapper= sqlSession.getMapper(UserMapper.class);
mapper.deleteUserById(8);}}
注意:
- 需开启事务自动提交
- 对于基本类型的数据@param参数尽量都写上,引用类型不需要;如果方法有多个参数,就必须填写。执行操作时以@param中的参数名为准