@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; }