package com.artfess.base.util; import java.util.List; import java.util.Map; import com.artfess.base.query.PageBean; import com.artfess.base.query.PageList; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.baomidou.mybatisplus.core.parser.SqlInfo; import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory; import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel; import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.IDialect; import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils; /** * 支持分页查询的jdbcTemplate * @author liyanggui * @date 2020-04-21 */ public class JdbcTemplateUtil { /** * @param originalSql * @param pageBean * @param elementType */ public static PageList queryForListWithPage(String originalSql, PageBean pageBean, Class elementType) { return queryForListWithPage(originalSql,null,pageBean,elementType); } /** *
	 * 分页查询sql支持参数
	 * 
* @param * @param originalSql * @param args * @param pageBean * @param elementType * @return */ public static PageList queryForListWithPage(String originalSql, Object[] args, PageBean pageBean, Class elementType) { JdbcTemplate jdbcTemplate = AppUtil.getBean(JdbcTemplate.class); PageList pageList = new PageList(); String dialectSql = initPageList(jdbcTemplate,originalSql,pageBean,pageList); Object[] pageArgs = getPageArgs(pageBean); Object[] newArgs =pageArgs; if(BeanUtils.isNotEmpty(args)) { newArgs = ArrayUtil.concat(args, pageArgs); } List queryForList = jdbcTemplate.queryForList(dialectSql,newArgs,elementType); pageList.setRows(queryForList); return pageList; } /** * @param originalSql * @param pageBean */ public static PageList> queryForListWithPage(String originalSql,PageBean pageBean) { return queryForListWithPage(originalSql,null,pageBean); } /** * 分页查询 * @param originalSql * @param args * @param pageBean * @return */ public static PageList> queryForListWithPage(String originalSql, Object[] args, PageBean pageBean) { JdbcTemplate jdbcTemplate = AppUtil.getBean(JdbcTemplate.class); PageList> pageList = new PageList>(); String dialectSql = initPageList(jdbcTemplate,originalSql,pageBean,pageList); Object[] pageArgs = getPageArgs(pageBean); Object[] newArgs =pageArgs; if(BeanUtils.isNotEmpty(args)) { newArgs = ArrayUtil.concat(args, pageArgs); } List> queryForList = jdbcTemplate.queryForList(dialectSql,newArgs); pageList.setRows(queryForList); return pageList; } /** * @param originalSql * @param pageBean */ public static PageList query(String originalSql,PageBean pageBean,RowMapper rowMapper) { return query(originalSql,null,pageBean,rowMapper); } /** *
	 * 分页查询
	 * 
* @param * @param originalSql * @param args * @param pageBean * @param rowMapper * @return */ public static PageList query(String originalSql, Object[] args, PageBean pageBean,RowMapper rowMapper) { JdbcTemplate jdbcTemplate = AppUtil.getBean(JdbcTemplate.class); PageList pageList = new PageList(); Object[] pageArgs = getPageArgs(pageBean); Object[] newArgs =pageArgs; if(BeanUtils.isNotEmpty(args)) { newArgs = ArrayUtil.concat(args, pageArgs); } String dialectSql = initPageList(jdbcTemplate,originalSql,args,pageBean,pageList); List query = jdbcTemplate.query(dialectSql, newArgs, rowMapper); pageList.setRows(query); return pageList; } /** * 获取总条数 * @param * @param jdbcTemplate * @param originalSql * @param pageBean * @return */ private static String initPageList(JdbcTemplate jdbcTemplate, String originalSql, PageBean pageBean,PageList pageList ) { return initPageList(jdbcTemplate,originalSql,null,pageBean,pageList); } private static String initPageList(JdbcTemplate jdbcTemplate, String originalSql, Object[] args, PageBean pageBean, PageList pageList) { SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(true, null, originalSql); String countSql = sqlInfo.getSql(); IDialect dialect = DialectFactory.getDialect(SQLUtil.getDbTypeObj()); DialectModel model = dialect.buildPaginationSql(originalSql, pageBean.getPage(), pageBean.getPageSize()); String dialectSql = model.getDialectSql(); Long total = 0L; if(BeanUtils.isNotEmpty(args)) { total = jdbcTemplate.queryForObject(countSql,args, Long.class); }else { total = jdbcTemplate.queryForObject(countSql, Long.class); } pageList.setPage(pageBean.getPage()); pageList.setPageSize(pageBean.getPageSize()); pageList.setTotal(total); return dialectSql; } private static Object[] getPageArgs(PageBean pageBean) { Object[] args = new Object[2]; args[0] = pageBean.getLimit(); args[1] = pageBean.getOffset(); return args; } }