Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.telewave.logger.page.JdbcTemplageSupport Maven / Gradle / Ivy
package com.telewave.logger.page;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Created by aiowang on 2018/7/26.
*/
public class JdbcTemplageSupport{
private JdbcTemplate jdbcTemplate;
public JdbcTemplageSupport(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
*
* @param sql
* 查询语句
* @param sqlArgs
* 查询参数List,顺序需要与sql中的?一一对应
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param pageParam
* page的参数，包括pageIndex,pageSize
* @param 泛型
* @return 分页对象
*/
public PageResult pagedQuery(String sql, List sqlArgs,
Class mappedClass, PageParam pageParam) {
return this.pagedQuery(sql, sqlArgs.toArray(), mappedClass, pageParam);
}
/**
*
* @param sql
* 查询语句
* @param sqlArgs
* 查询参数List,顺序需要与sql中的?一一对应
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param page
* 查询的页码
* @param rows
* 一页显示的行数
* @param 泛型
* @return 对页对象
*/
public PageResult pagedQuery(String sql, List sqlArgs,
Class mappedClass, int page, int rows) {
return this.pagedQuery(sql, sqlArgs.toArray(), mappedClass, page, rows);
}
/**
*
* @param sql
* 查询语句
* @param sqlArgs
* 查询参数Object数组,顺序需要与sql中的?一一对应
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param page
* 查询的页码
* @param rows
* 一页显示的行数
* @param 泛型
* @return 分页对象
*/
public PageResult pagedQuery(String sql, Object[] sqlArgs,
Class mappedClass, int page, int rows) {
return this.pagedQuery(sql, sqlArgs, mappedClass, new PageParam(page,
rows));
}
/**
*
* @param sql
* 查询语句
* @param sqlArgs
* 查询参数Object数组,顺序需要与sql中的?一一对应
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param pageParam
* page的参数，包括pageIndex,pageSize
* @param 泛型
* @return 分页对象
*/
public PageResult pagedQuery(String sql, Object[] sqlArgs,
Class mappedClass, PageParam pageParam) {
// 查询总条数
int totalCount = this.jdbcTemplate.queryForObject(this.buildCountSql(sql),
Integer.class, sqlArgs);
// 查询数据
RowMapper rowMapper = BeanPropertyRowMapper.newInstance(mappedClass);
List list = this.jdbcTemplate.query(this.buildDataSql(sql, pageParam), sqlArgs,
rowMapper);
// 组装PageResult
PageResult page = new PageResult(totalCount, list);
return page;
}
/**
*
* @param sql
* 查询语句
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param pageParam
* page的参数，包括pageIndex,pageSize
* @param sqlArgs
* 查询语句传入的参数
* @param 泛型
* @return 分页对象
*/
public PageResult pagedQuery(String sql, Class mappedClass,
PageParam pageParam, Object... sqlArgs) {
// 查询总条数
int totalCount = this.jdbcTemplate.queryForObject(this.buildCountSql(sql),
Integer.class, sqlArgs);
// 查询数据
RowMapper rowMapper = BeanPropertyRowMapper.newInstance(mappedClass);
List list = this.jdbcTemplate.query(this.buildDataSql(sql, pageParam), rowMapper,
sqlArgs);
// 组装PageResult
PageResult page = new PageResult(totalCount, list);
return page;
}
/**
*
* @param sql
* 查询语句
* @param mappedClass
* 查询的结果返回的封装CLASS
* @param page
* 查询的页码
* @param rows
* 一页显示的行数
* @param sqlArgs
* 查询语句传入的参数
* @param 泛型
* @return 分页对象
*/
public PageResult pagedQuery(String sql, Class mappedClass,
int page, int rows, Object... sqlArgs) {
return this.pagedQuery(sql, mappedClass, new PageParam(page, rows),
sqlArgs);
}
/**
* 去除SELECT *语句，便于SELECT count(*)
*/
private String removeSelect(String sql) {
int beginPosition = sql.toLowerCase().indexOf("from");
return sql.substring(beginPosition);
}
/**
* 去除order by 提高select count(*)的速度
*/
private String removeOrders(String sql) {
Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, "");
}
m.appendTail(sb);
return sb.toString();
}
private String buildCountSql(String sql) {
StringBuffer countSql = new StringBuffer();
countSql.append("SELECT COUNT(*) ");
countSql.append(this.removeOrders(this.removeSelect(sql)));
return countSql.toString();
}
private String buildDataSql(String sql, PageParam pageParam) {
StringBuffer dataSql = new StringBuffer();
dataSql.append("SELECT");
dataSql.append(" *");
dataSql.append("FROM");
dataSql.append(" (");
dataSql.append(" SELECT");
dataSql.append(" temp.*,");
dataSql.append(" ROWNUM num");
dataSql.append(" FROM");
dataSql.append(" (").append(sql).append(") temp");
dataSql.append(" WHERE");
dataSql.append(" ROWNUM <= ").append(pageParam.getEndIndex());
dataSql.append(" ) WHERE num > ").append(pageParam.getStartIndex());
return dataSql.toString();
}
}
