Mybatis后台物理分页查询
分页查询在日常开发中是非常常见的，往往分为逻辑分页、物理分页。如果数据量较大，对内存、网络传输的消耗非常大的时候选择物理分页。
本章节我们主要讲物理分页查询，以一个例子展开，把B库的user表每次查询100条循环查询并批量插入A库的user表中。
我们的项目框架是jeesite，首先我们为项目配置A库，B库数据源，见配置多数据源文章。下面我们将列举此功能的代码：
1.Controller代码：
1 @Controller 2 @RequestMapping(value = "${adminPath}/acm/syncuser") 3 public class SyncuserController extends BaseController { 4 @Autowired 5 private LocalUserService localUserService; 6 @Autowired 7 private RemoteUserService remoteUserService; 8 @Autowired 9 private UserDao userDao; 10 11 @ModelAttribute 12 public User get(@RequestParam(required = false) String id) { 13 if (StringUtils.isNotBlank(id)) { 14 return userService.get(id); 15 } else { 16 return new User(); 17 } 18 } 19 /** 20 *根据数据条数与每页多少条数据计算页数 21 * totalNum 数据条数 22 * pageSize 每页多少条 23 * 24 */ 25 public int pageCount (int totalNum,int pageSize){ 26 return totalNum > 0 ? ((totalNum < pageSize) ? 1 : ((totalNum % pageSize)==0 ? ((totalNum / pageSize) + 1) : (totalNum / pageSize))) : 0; 27 } 28 29 30 /** 31 * 同步 32 * 33 * @param user 34 * @param model 35 * @return 36 * @throws Exception 37 */ 38 @RequiresPermissions("acm:syncuser:edit") 39 @RequestMapping(value = "sync") 40 public String syncUserData(User user, Model model) { 41 //本地用户表保存获取的远程用户表数据的前要清空本地用户表 42 localUserService.truncateTable(); 43 //远程用户表行数 44 int count = remoteUserService.rowsCount(); 45 int totalPage = pageCount(count,Integer.parseInt(Global.getConfig("pagesize")));//每页pagesize条数据时得总共页数,pagesize在配置文件中配置 46 Map map = new HashMap(); 47 for(int i = 1;i<=totalPage;i++ ) { 48 map.put("page", (i-1)*Integer.parseInt(Global.getConfig("pagesize"))); 49 map.put("rows", Integer.parseInt(Global.getConfig("pagesize"))); 50 List<User> remoteUserList = remoteUserService.findList(map); 51 if(remoteUserList.size()>0) { 52 userDao.insertBath(remoteUserList); 53 } 54 } 55 } 56 57 }
2.Entity代码：
1 public class User extends DataEntity<User>{ 2 private String code ;//可读标识 3 private String name ;//名称 4 private String appType ; 5 private String ownerDomainId ; 6 private Integer sort;//排序号 7 private String memo ;//描述 8 private String iconId ;//图标id 9 private String url1;//扩展url字段1 10 private String url2 ;//扩展url字段2 11 private String url3 ;//扩展url字段3 12 private String url4;//扩展url字段4 13 private String secretkey ;//秘钥 14 private String scope; 15 //省略get，set方法 16 }
3.Service代码:
1 @Service 2 @Transactional(readOnly = true) 3 public class LocalUserService extends CrudService<UserDao, User>{ 4 public User get(String id) { 5 return dao.get(id); 6 } 7 public Page<User> findPage(Page<User> page, User user) { 8 user.setPage(page); 9 page.setList(dao.findList(user)); 10 return page; 11 } 12 public List<User> findList(User user) { 13 return super.findList(user); 14 } 15 16 @Transactional(readOnly = false) 17 public void update(User user) { 18 dao.update(user); 19 } 20 21 @Transactional(readOnly = false) 22 public void save(User user) { 23 dao.insert(user); 24 } 25 26 @Transactional(readOnly = false) 27 public void truncateTable() { 28 dao.truncateTable(); 29 } 30 }
1 @DynamicDataSourceAnnotation 2 @Service 3 public class RemoteUserService extends CrudService<RemoteUserDao, User>{ 4 @DynamicDataSourceAnnotation(dataSource = DataSourceContextHolder.DATA_SOURCE_DRAP) 5 public User get(String id) { 6 return dao.get(id); 7 } 8 @DynamicDataSourceAnnotation(dataSource = DataSourceContextHolder.DATA_SOURCE_DRAP) 9 public Page<User> findPage(Page<User> page, User user) { 10 user.setPage(page); 11 page.setList(dao.findList(user)); 12 return page; 13 } 14 @DynamicDataSourceAnnotation(dataSource = DataSourceContextHolder.DATA_SOURCE_DRAP) 15 public List<user> findList(Map map) { 16 return dao.findList(map); 17 } 18 @DynamicDataSourceAnnotation(dataSource = DataSourceContextHolder.DATA_SOURCE_DRAP) 19 public int rowsCount() { 20 return dao.rowsCount(); 21 } 22 23 }
4.Dao代码:
1 @MyBatisDao 2 public interface UserDao extends CrudDao<User>{ 3 public void truncateTable(); 4 public int insertBath(List<User>list); 5 }
1 @MyBatisDao 2 public interface RemoteUserDao extends CrudDao<User>{ 3 public List<User> findList(@Param("map")Map<String,Object> map); 4 public int rowsCount(); 5 }
5.xml代码:
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="dms.microservice.databak.dao.UserDao"> 4 5 <sql id="userSycnColumns"> 6 a.id AS "id", 7 a.code AS "code", 8 a.name AS "name", 9 a.app_type AS "appType", 10 a.owner_domain_id AS "ownerDomainId", 11 a.sort AS "sort", 12 a.memo AS "memo", 13 a.icon_id AS "iconId", 14 a.url1 AS "url1", 15 a.url2 AS "url2", 16 a.url3 AS "url3", 17 a.url4 AS "url4", 18 a.secret_key AS "secretkey", 19 a.scope AS "scope" 20 </sql> 21 22 23 <insert id="insertBath" parameterType="java.util.List"> 24 INSERT INTO PT_USER 25 (id , 26 code , 27 name , 28 app_type , 29 owner_domain_id , 30 sort , 31 memo, 32 icon_id , 33 url1 , 34 url2 , 35 url3 , 36 url4 , 37 secret_key , 38 scope) 39 VALUES 40 <foreach collection="list" item="item" index="index" 41 separator=","> 42 ( 43 #{item.id}, 44 #{item.code}, 45 #{item.name}, 46 #{item.appType}, 47 #{item.ownerDomainId}, 48 #{item.sort}, 49 #{item.memo}, 50 #{item.iconId}, 51 #{item.url1}, 52 #{item.url2}, 53 #{item.url3}, 54 #{item.url4}, 55 #{item.secretkey}, 56 #{item.scope} 57 ) 58 </foreach> 59 </insert> 60 61 <update id="truncateTable"> 62 truncate table PT_USER 63 </update> 64 </mapper>
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="dms.microservice.databak.dao.RemoteUserDao"> 4 5 <sql id="userSycnColumns"> 6 a.id AS "id", 7 a.code AS "code", 8 a.name AS "name", 9 a.app_type AS "appType", 10 a.owner_domain_id AS "ownerDomainId", 11 a.sort AS "sort", 12 a.memo AS "memo", 13 a.icon_id AS "iconId", 14 a.url1 AS "url1", 15 a.url2 AS "url2", 16 a.url3 AS "url3", 17 a.url4 AS "url4", 18 a.secret_key AS "secretkey", 19 a.scope AS "scope" 20 </sql> 21 22 23 <sql id="userSyncJoins"> 24 </sql> 25 26 <select id="get" resultType="PtApp"> 27 SELECT 28 <include refid="userSycnColumns"/> 29 FROM PT_USER a 30 <include refid="userSyncJoins"/> 31 WHERE a.id = #{id} 32 </select> 33 34 <select id="findList" resultType="PtApp"> 35 SELECT 36 <include refid="userSycnColumns"/> 37 FROM PT_USER a 38 <include refid="userSyncJoins"/> 39 WHERE 1=1 40 LIMIT #{map.page},#{map.rows} 41 </select> 42 43 <select id="rowsCount" resultType="int"> 44 select count(1) from PT_USER; 45 </select> 46 47 48 49 <update id="truncateTable"> 50 truncate table PT_USER 51 </update> 52 53 54 </mapper>
