@Transactional(propagation = Propagation.NOT_SUPPORTED)
public List<?> pageOracleParams(String sql, Object[] args, Pager page, Class<?> clazz) {
int count = this.getCountItems(sql);
page.setItems(count);
StringBuilder pageSql = new StringBuilder();
pageSql.append("select * from (select t.*, rownum counts from(").append(sql).append(")t)");
pageSql.append(" where counts between ");
pageSql.append(page.getOffset() + 1);
pageSql.append(" and ");
pageSql.append((page.getOffset() + page.getMaxPageItems()));
List<?> list =
this.jdbcTemplate.query(pageSql.toString(), args, new BeanPropertyRowMapper(clazz));
return list;
}
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public List<?> pageMySqlParams(String sql, Object[] args, Pager page, Class<?> clazz) {
StringBuilder countSql = new StringBuilder();
countSql.append("select count(*) from (").append(sql).append(") as c");
int count = 0;
count = this.getCountItems(sql);
page.setItems(count);
StringBuilder pageSql = new StringBuilder();
pageSql.append("select t.* from(").append(sql).append(") as t");
pageSql.append(" limit ");
pageSql.append(page.getOffset());
pageSql.append(" , ");
pageSql.append(page.getMaxPageItems());
List<?> list =
this.jdbcTemplate.query(pageSql.toString(), args, new BeanPropertyRowMapper(clazz));
return list;
}
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public List<?> pageMsSqlParams(
String sql, Object[] args, Pager page, Class<?> clazz, String orderItem) {
StringBuilder countSql = new StringBuilder();
countSql.append("select count(*) from (").append(sql).append(") as c");
int count = 0;
count = this.getCountItems(sql);
page.setItems(count);
StringBuilder pageSql = new StringBuilder();
// count M
int m = 0;
if (page.getMaxPageNumber() == page.getNoncePage()) {
m = page.getItems();
} else {
m = page.getNoncePage() * page.getMaxPageItems();
}
// count N
int n = (page.getNoncePage() - 1) * page.getMaxPageItems() + 1;
int start = m - n + 1;
int end = n - 1;
pageSql.append("select top ").append(start).append(" * from ( ");
pageSql
.append(sql)
.append(" ) t where ")
.append(orderItem)
.append(" not in (select top ")
.append(end)
.append(" ")
.append(orderItem);
pageSql
.append(" from (")
.append(sql)
.append(" ) tt order by ")
.append(orderItem)
.append(" desc ) order by ");
pageSql.append(orderItem).append(" desc");
List<?> list =
this.jdbcTemplate.query(pageSql.toString(), args, new BeanPropertyRowMapper(clazz));
return list;
}
