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 }

View Code

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     }

View Code

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 }

View Code

 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 }

View Code

4.Dao代码:

1 @MyBatisDao
2 public interface UserDao  extends CrudDao<User>{
3     public void truncateTable();
4     public int insertBath(List<User>list);
5 }

View Code

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 }

View Code

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>

View Code

 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>

View Code