基于jdbcTemplate实现物理分页

众所周知，在物联网世界里，我们大部分的操作是来自查询，我们面试经常被问到的QPS其实就是针对查询的，说到查询，根据实际的场景也一般分为单个查询和批量查询，例如：查询会员的详情信息是单个查询，查询会员列表就是典型的批量查询，说到批量查询那么每次查询的数量就要受限，DB单次查询量限制，网络传输带宽限制，应用程序接收数据量大小限制等等，那么这时候分页查询变得非常必要，每次查询出指定大小的单页数据，翻页的时候调整分页参数再次查询。

目前来说，有很多持久层框架都实现了分页功能，比如Hibernate，Mybatis以及专门的分页查询pagehelper等等，也有一部分企业没有使用持久层框架，直接基于Spring提供的JdbcTemplate操作数据库，查询的时候需要自己写分页查询逻辑，导致了大量重复的代码，

也有一些公司使用了mybatis框架，在mybatis的拦截器层写逻辑完成物理分页。

接下来，我们就基于JdbcTemplate使用mybatis拦截器物理分页的思想完成分页操作。

新建项目&引入依赖

项目pom文件引入一下基本依赖：

编写实现逻辑

1）数据操作接口

/**

* 自定义jdbc操作

*

* @author Typhoon

* @date 2018-05-06 14:38 Sunday

* @since V2.0.0

*/

public interface CustomJdbcOperations extends JdbcOperations {

/**

* 分页查询

*

* @author Typhoon

* @date 2018-05-06 14:50 Sunday

* @param sql

* @param rowMapper

* @param pageQuery

* @param args

* @return

* @throws DataAccessException

*/

<T> PageResult<T> page(String sql, RowMapper<T> rowMapper,PageQuery pageQuery, Object... args) throws DataAccessException;

}

2）jdbcTemplate扩展实现

/**

* jdbcTemplate扩展实现

*

* @author Typhoon

* @date 2018-05-26 09:55 Saturday

*/

public class CustomJdbcTemplate extends JdbcTemplate implements CustomJdbcOperations {

public CustomJdbcTemplate() {

}

/**

* Construct a new JdbcTemplate, given a DataSource to obtain connections from.

* <p>Note: This will not trigger initialization of the exception translator.

* @param dataSource the JDBC DataSource to obtain connections from

*/

public CustomJdbcTemplate(DataSource dataSource) {

super(dataSource);

}

public CustomJdbcTemplate(DataSource dataSource, boolean lazyInit) {

super(dataSource, lazyInit);

}

@Override

public <T> PageResult<T> page(String sql, RowMapper<T> rowMapper, PageQuery pageQuery, Object... args)

throws DataAccessException {

PageInfo page = new PageInfo();

int pageSize = pageQuery.getPageSize();

if(pageSize >= 5000) {

logger.warn("CustomJdbcTemplate#page pageSize has overhead 5000;pageSize:"+pageSize);

}

int offset = (pageQuery.getPageIndex() - 1) * pageSize;

StringBuffer countSql = new StringBuffer();

countSql.append(MySql5PageHepler.getCountString(sql));

int totalRows = this.queryForObject(countSql.toString(), args, Integer.class);

page.init(totalRows, pageSize, pageQuery.getPageIndex());

if(totalRows <= 0) {//查询总数没有数据，就没有必要再去查询具体数据

return PageResult.wrap(page, null);

}

String pageSql = MySql5Dialect.getLimitString(sql, offset, pageSize);

List<T> list = this.query(pageSql, rowMapper, args);

return PageResult.wrap(page, list);

}

}

该类实现了CustomJdbcOperations，拥有了操作数据的功能，并且继承了JdbcTemplate，也即拥有了数据操作的默认实现，我们写这个类的目的就是在保证原jdbcTemplate功能的基础上扩展出分页查询能力.

CustomJdbcTemplate是核心类，其分页查询的原理是：

I）解析原生sql得到查询分页数据的countSql

II）查询总行数并初始化分页参数，如果总行数小于等于0，直接返回，不在继续查询业务数据

III）将原生sql封装成分页查询sql(带limit)

IV）查询业务数据并封装成分页查询结果返回

3）解析counSql

/**

* 得到查询总数的sql

*/

public static String getCountString(String querySelect )

{

querySelect = getLineSql( querySelect );

int orderIndex = getLastOrderInsertPoint( querySelect );

int formIndex = getAfterFromInsertPoint( querySelect );

String select = querySelect.substring( 0, formIndex );

// 如果SELECT 中包含 DISTINCT 只能在外层包含COUNT

if ( select.toLowerCase().indexOf( "select distinct" ) != -1 || querySelect.toLowerCase().indexOf( "group by" ) != -1 )

{

return new StringBuffer( querySelect.length() ).append( "select count(1) count from (" ).append( querySelect.substring( 0, orderIndex ) ).append( " ) t" ).toString();

}

else

{

return new StringBuffer( querySelect.length() ).append( "select count(1) count " ).append( querySelect.substring( formIndex, orderIndex ) ).toString();

}

}

4）解析分页查询sql

/**

* 得到分页的SQL

*

* @param offset 偏移量

* @param limit 位置

* @return 分页SQL

*/

public static String getLimitString( String querySelect, int offset, int limit )

{

querySelect = getLineSql( querySelect );

String sql = querySelect + " limit " + offset + " ," + limit;

return sql;

}

暴露CustomJdbcTemplate

此处我们为了方便测试，依旧使用之前的无spring配置操作JdbcTemplate:

/**

* 简化数据源配置类

*

* @author Typhoon

* @date 2017-08-22 11:27 Tuesday

* @since V1.3.1

*/

public class DataSourceUtils {

public static Properties p = null;

public static String confile = "jdbc.properties";

static {

p = new Properties();

InputStream inputStream = null;

try {

inputStream = DataSourceUtils.class.getClassLoader().getResourceAsStream(confile);

inputStream = new BufferedInputStream(inputStream);

p.load(inputStream);

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (inputStream != null) {

inputStream.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

private static DataSource getDataSource() {

try {

return DruidDataSourceFactory.createDataSource(p);

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

/**

* 获取自定义jdbcTemplate

*

* @return

*/

public static CustomJdbcTemplate getCustomJdbcTemplate() {

DataSource dataSource = getDataSource();

CustomJdbcTemplate customJdbcTemplate = new CustomJdbcTemplate(dataSource);

return customJdbcTemplate;

}

}

编写单元测试

1）Dao操作层

/**

* 用户操作dao

*

* @author Typhoon

* @date 2017-08-22 15:32 Tuesday

* @since V1.3.1

*/

public class UserDao {

/**

* 获取jdbcTemplate

*/

private CustomJdbcTemplate jdbcTemplate = DataSourceUtils.getCustomJdbcTemplate();

/**

* 原生JdbcTemplate查询

*/

public User selectById(Long id) {

List<User> list = jdbcTemplate.query(" select * from User where id = ? limit 1 ", BeanPropertyRowMapper.newInstance(User.class),id);

if(null == list || list.isEmpty()) {

return null;

}

return list.get(0);

}

/**

* 扩展接口分页查询

*/

public PageResult<User> page(PageQuery pageQuery,Map<String,Object> params) {

StringBuilder sql = new StringBuilder();

List<Object> paramList = new ArrayList<>();

sql.append(" select * from User where 1 = 1 ");

Object id = params.get("id");

if(null != id) {

sql.append(" and ").append(" id =? ");

paramList.add(id);

}

Object name = params.get("name");

if(null != name) {

sql.append(" and ").append(" name = ? ");

paramList.add(name);

}

sql.append(" order by createTime desc ");

return this.jdbcTemplate.page(sql.toString(), BeanPropertyRowMapper.newInstance(User.class), pageQuery, paramList.toArray());

}

}

2）单元测试

原生查询：

扩展分页查询:

可以看到我们扩展后的CustomJdbcTemplate在保留原生JdbcTemplate功能的基础上
，实现了DB查询的通用分页。

总结

     经过上边的描述，我们已经基于原生JdbcTemplate扩展并实现了分页的功能，
在一些不想使用持久层框架或者觉得持久层框架太重的项目中可以考虑对原生
JdbcTemplate扩展来实现一些业务通用或者定制化的功能。
    当然这只是一种实现方式，另外一种实现方式是我们把Dao层通用的操作
提出来抽象到BaseDao层来实现，具体的业务Dao只需继承BaseDao就可以使用
更新查询的功能，同样可以达到和扩展JdbcTemplate一样的效果。
     此篇暂且讲到这里，希望给大家在日常开发中带来帮助！

代码地址:

https://gitee.com/ScorpioAeolus/spring-jdbctemplate.git