Here's a step-by-step procedure for implementing a custom paging using the spring JdbcTemplate method:
Step one to create the Splitpageresultsetextractor class:
Package com.utils;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import java.util.List;
Import org.springframework.dao.DataAccessException;
Import Org.springframework.jdbc.core.ResultSetExtractor;
Import Org.springframework.jdbc.core.RowMapper;
Import Org.springframework.util.Assert;
public class Splitpageresultsetextractor implements ResultSetExtractor {
Private final int startindex;//start line number
Private final int pagesize;//per page record number
Private final RowMapper rowmapper;//line wrapper
Public Splitpageresultsetextractor (RowMapper rowmapper, int startIndex, int pageSize) {
Assert.notnull (RowMapper, "RowMapper is required");
This.rowmapper = RowMapper;
This.startindex = StartIndex;
This.pagesize = pageSize;
}
/**
* Processing the result set, which is automatically invoked by the interface and should not be called outside of the class
*/
Public Object Extractdata (ResultSet rs) throws SQLException,
DataAccessException {
List result = new ArrayList ();
Rs.first ();
Rs.relative (startIndex-1);
int count=0;
while (Rs.next ()) {
count++;
Result.add (This.rowMapper.mapRow (RS, startindex+count));
System.out.println (Rs.getboolean (1));
if (count = = pageSize) {
Break
}
}
return result;
}
/**
* Not using this method if the data is large, you cannot query the data
*/
Public Object Extractdatas (ResultSet rs) throws SQLException,
DataAccessException {
List result = new ArrayList ();
int rownum = 0;
int end = StartIndex + pageSize;
Point:while (Rs.next ()) {
++rownum;
if (RowNum < StartIndex) {
Continue point;
else if (rownum >= end) {
Break point;
} else {
Result.add (This.rowMapper.mapRow (RS, rownum));
}
}
return result;
}
}
Step two, create the Jdbctemplateextend class:
Package com.utils;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.util.List;
Import Java.util.Map;
Import Java.sql.Types;
Import Javax.sql.DataSource;
Import org.springframework.dao.DataAccessException;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.PreparedStatementCreator;
Import Org.springframework.jdbc.core.PreparedStatementCreatorFactory;
Import Org.springframework.jdbc.core.RowMapper;
public class Jdbctemplateextend extends JdbcTemplate {
@Override
Public List queryForList (String arg0, object[] arg1)
Throws DataAccessException {
Return Super.queryforlist (arg0, arg1);
}
Private DataSource DataSource;
/**
* Default constructor, calling this method initialization, requires calling Setdatasource to set the data source
*/
Public Jdbctemplateextend () {
}
/**
* Initial Builder
*
* @param dataSource
* Data source
*/
Public Jdbctemplateextend (DataSource DataSource) {
This.datasource = DataSource;
Super.setdatasource (DataSource);
}
/**
* General Paging Query <br>
* <b> If the results combined relatively large should call Setfetchsize () and Setmaxrow two methods to control, otherwise memory overflow </b>
*/
@SuppressWarnings ("Unchecked")
Public list<map> QUERYSP (String sql, int startrow, int rowscount)
Throws DataAccessException {
return QUERYSP (SQL, StartRow, Rowscount, Getcolumnmaprowmapper ());
}
/**
* Custom Line Wrapper query <br>
* <b> If the results combined relatively large should call Setfetchsize () and Setmaxrow two methods to control, otherwise memory overflow </b>
* */
@SuppressWarnings ("Unchecked")
Public list<map> QUERYSP (String sql, int startrow, int rowscount,
RowMapper rowmapper) throws DataAccessException {
Return (List) query (SQL, new Splitpageresultsetextractor (RowMapper,
StartRow, Rowscount));
}
/**
* General Paging Query <br>
* <b> If the results combined relatively large should call Setfetchsize () and Setmaxrow two methods to control, otherwise memory overflow </b>
*
* @see #setFetchSize (int)
* @see #setMaxRows (int)
* @param sql
* SQL statement for query
* @param startrow
* Start line
* @param rowscount
* Number of rows fetched
* @return
* @throws DataAccessException
*/
@SuppressWarnings ("Unchecked")
Public list<map> queryforlistpagination (String sql, object[] arg1,
int startrow, int rowscount) throws DataAccessException {
return querypagination (SQL, arg1, StartRow, Rowscount,
Getcolumnmaprowmapper ());
}
/**
* General Paging Query <br>
* <b> If the results combined relatively large should call Setfetchsize () and Setmaxrow two methods to control, otherwise memory overflow </b>
*
* @see #setFetchSize (int)
* @see #setMaxRows (int)
* @param types[]
* Pass the type value of the parameter Java.sql.Types.VARCHAR
* @param sql
* SQL statement for query
* @param startrow
* Start line
* @param rowscount
* Number of rows fetched
* @return
* @throws DataAccessException
*/
@SuppressWarnings ("Unchecked")
Public list<map> queryforlistpagination (String sql, object[] arg1,int [] types,
int startrow, int rowscount) throws DataAccessException {
return querypagination (SQL, Arg1,types, StartRow, Rowscount,
Getcolumnmaprowmapper ());
}
private list<map> querypagination (String sql, final object[] arg1,int [] types,
int startrow, int rowscount, RowMapper columnmaprowmapper) {
preparedstatementcreatorfactory factory = New PreparedStatementCreatorFactory (
sql, types);
factory.setresultsettype (resultset.type_scroll_insensitive);
preparedstatementcreator PSC = Factory
.newpreparedstatementcreator ( ARG1);
return (List) query (PSC, new Splitpageresultsetextractor (
ColumnMapRowMapper, StartRow, Rowscount));
}
private list<map> querypagination (String sql, final object[] arg1,
int StartRow, int Rowscount, RowMapper columnmaprowmapper) {
int[] types = new Int[arg1.length];
for (int i = 0; i < arg1.length; i++) {
types[i] = Types.varchar;
&NBSP;&NBSP}
preparedstatementcreatorfactory factory = new PreparedStatementCreatorFactory (
sql, types);
factory.setresultsettype (resultset.type_scroll_insensitive);
//Factory.setresultsettype (resultset.type_scroll_sensitive);
preparedstatementcreator PSC = Factory
.newpreparedstatementcreator ( ARG1);
return (List) query (PSC, new Splitpageresultsetextractor (
ColumnMapRowMapper, StartRow, Rowscount));
}
Public DataSource Getdatasource () {
return dataSource;
}
public void Setdatasource (DataSource DataSource) {
This.datasource = DataSource;
Super.setdatasource (DataSource);
}
}
Called in the Step three DAO layer:
Public List Getinto (String plateauclientname, int notecount,int start,int pagesize)
{
Jdbctemplateextend jdbcextend = new Jdbctemplateextend ();
Jdbcextend.setdatasource (Jdbctemplate.getdatasource ());//Set data connection source
String sql = " SELECT * from Userphonedc where plateauclientname = ' ' +plateauclientname+ ' and notecount = "+notecount;
list = JDBCEXTEND.QUERYSP (sql, start, pagesize);
//Parameter interpretation:
sql:sql Action statement.
start: Start record line
PageSize: Displays the number of records per page
return list;
}