Problem with pagination + Server-side processing

rlucolli

Hello,

Firstly thank DataTables for this wonderful component.

I made some changes since I'm using JSP + Oracle for the Server-side as:

[code]






<%@page import="org.json.simple.JSONArray"%>
<%@page import="org.json.simple.JSONObject"%>
<%@page import="org.json.*"%>
<%@page import="classes.dominio.ConnectionFactoryOracle"%>


<%@page import="java.util.*"%>

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.*"%>

<%


String[] cols = { "COD_PRODUTO", "FILIAL", "PRODUTO", "TIPO", "GRUPO" };
String table = "v_posicao_estoque";

JSONObject result = new JSONObject();
JSONArray array = new JSONArray();
int amount = 10;
int start = 0;
int echo = 0;
int col = 0;

String engine = "COD_PRODUTO";
String browser = "FILIAL";
String platform = "PRODUTO";
String version = "TIPO";
String grade = "GRUPO";


String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sEcho = request.getParameter("sEcho");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");

engine = request.getParameter("sSearch_0");
browser = request.getParameter("sSearch_1");
platform = request.getParameter("sSearch_2");
version = request.getParameter("sSearch_3");
grade = request.getParameter("sSearch_4");

List sArray = new ArrayList();
if (!engine.equals("")) {
String sEngine = " COD_PRODUTO like '%" + engine + "%'";
sArray.add(sEngine);
//or combine the above two steps as:
//sArray.add(" engine like '%" + engine + "%'");
//the same as followings
}
if (!browser.equals("")) {
String sBrowser = " FILIAL like '%" + browser + "%'";
sArray.add(sBrowser);
}
if (!platform.equals("")) {
String sPlatform = " PRODUTO like '%" + platform + "%'";
sArray.add(sPlatform);
}
if (!version.equals("")) {
String sVersion = " TIPO like '%" + version + "%'";
sArray.add(sVersion);
}
if (!grade.equals("")) {
String sGrade = " GRUPO like '%" + grade + "%'";
sArray.add(sGrade);
}

String individualSearch = "";
if(sArray.size()==1){
individualSearch = sArray.get(0);
}else if(sArray.size()>1){
for(int i=0;i 100)
amount = 10;
}
if (sEcho != null) {
echo = Integer.parseInt(sEcho);
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 5)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}
String colName = cols[col];
int total = 0;
//Connection conn = ConnectManager.getConnection();
Connection conexao = null;
conexao = ConnectionFactoryOracle.getConexao();

try {
String sql = "SELECT count(PRODUTO) as CONT FROM "+table;
PreparedStatement ps = (PreparedStatement) conexao.prepareStatement(sql);
ResultSet rs = ps.executeQuery();


if(rs.next()){
total = rs.getInt("CONT");


}
}catch(Exception e){
throw new Exception ("Falha na conexão com o banco " + e);
}
int totalAfterFilter = total;
//result.put("sEcho",echo);

try {
String searchSQL = "";
String sql = "select * "
+ "from ( select "
+ " topn.*, ROWNUM rnum from (SELECT * FROM "+table;
String searchTerm = request.getParameter("sSearch");
String globeSearch = " where (COD_PRODUTO like '%"+searchTerm+"%'"
+ " or FILIAL like '%"+searchTerm+"%'"
+ " or PRODUTO like '%"+searchTerm+"%'"
+ " or TIPO like '%"+searchTerm+"%'"
+ " or GRUPO like '%"+searchTerm+"%')";
if(searchTerm!=""&&individualSearch!=""){
searchSQL = globeSearch + " and " + individualSearch;
}
else if(individualSearch!=""){
searchSQL = " where " + individualSearch;
}else if(searchTerm!=""){
searchSQL=globeSearch;
}
sql += searchSQL;
sql += " order by " + colName + " " + dir;
sql += " ) topn where ROWNUM <= '"+amount+"' ) where rnum > '"+start+"'";
// sql += " limit " + start + ", " + amount;

PreparedStatement ps = (PreparedStatement) conexao.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
JSONArray ja = new JSONArray();



ja.add(rs.getString("COD_PRODUTO"));
ja.add(rs.getString("FILIAL"));
ja.add(rs.getString("PRODUTO"));
ja.add(rs.getString("TIPO"));
ja.add(rs.getString("GRUPO"));
array.add(ja);




}
String sql2 = "SELECT count(PRODUTO) as CONT FROM "+table;
if (searchTerm != "") {
sql2 += searchSQL;
PreparedStatement ps2 = (PreparedStatement) conexao.prepareStatement(sql2);
ResultSet rs2 = ps2.executeQuery();
if (rs2.next()) {
totalAfterFilter = rs2.getInt("CONT");
}
}
result.put("iTotalRecords", total);
result.put("iTotalDisplayRecords", totalAfterFilter);
result.put("aaData", array);


response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
out.print(result);
conexao.close();
} catch (Exception e) {
out.print(e);
}
%>
[/code]

the plugin worked perfectly at first, then noticed that the pages do not sail.

What I observed in requests from chrome / firebug is the event of the first page sends iDisplayStart = 0 & iDisplayLength = 50, other pages requirement when he continues DisplayLength DisplayStart = 50 and changing according to the number requested.

Below startup:
[code]
$(document).ready(function() {
oTable = $('#datatable').dataTable( {
"bProcessing": true,
"bServerSide": true,
"bPaginate" : true,
"bLengthChange": true,
"bScrollCollapse" : true,
"sPaginationType": "full_numbers",
"iDisplayLength": 20,
"sAjaxSource": "ajax/consultaProdutos.jsp"



}



);}

);
[/code]


Can anyone help me?

rlucolli

ahh, sorry for my poor english :/

rlucolli

SOLVED!

Oracle is not paging as MySQL so I created a variable to handle the end of line:

[code]
//create this before
amount = amount + start;

sql += searchSQL;
sql += " order by " + colName + " " + dir;
sql += " ) topn where ROWNUM <= '"+amount+"' ) where rnum > '"+start+"'";
[/code]