newbie trying DataTables for first time

ashiers

Hi There,

I'm a Java programmer and trying DataTables for the first time for the front end of a Web Application I'm working on. I don't use PHP for the back end. Instead I am using, for now, a Java Server Page named data2.jsp. For it I've managed to recreate the PHP page from the online example: http://www.datatables.net/release-datatables/examples/data_sources/server_side.html. It still requires some work, but for now, I just wanted to get a basic funtionality as the online example. I'm hoping the person reading this knows Java and can help.

So far, my data2.jsp page is querying the database and obtaining the first 10 records of a total of 661 records. That's fine. However, under the table I get: "Showing 1 to 10 of 10 entries (filtered from 661 total entries)". Also, the "Next" button is disabled. I didn't expect either of these to happen. I was expecting the same as what I see in the example posted online where it should be saying: "Showing 1 to 10 of 661 entries", and the "Next" button enabled so that I can click it and retrieve the next set of 10 records.

I am posting my code from the data2.jsp page in the hopes that someone can read it over and tell me what I need to change so that the DataTable will do what I expect. Thanks in advance.

PS> I don't know why it is, but for some reason a couple of variables below in my code: iDisplayStart and iDisplayLength, are being surrounded by anchor tags. There's something screwy with the way code is being formatted. The variables are supposed to be as follows:

int iDisplayStart = -1;
int iDisplayLength = -1;

and

try{
iDisplayStart = Integer.parseInt(request.getParameter("iDisplayStart"));
iDisplayLength = Integer.parseInt(request.getParameter("iDisplayLength"));
sEcho = Integer.parseInt(request.getParameter("sEcho"));
}
catch(NumberFormatException nfe){}


Alan

[code]
<%@ page import="java.lang.*,java.util.*,java.sql.*,net.sf.json.JSONArray" %>

<%

String result = "";
StringBuilder output = new StringBuilder();
int iDisplayStart = -1;
int iDisplayLength = -1;
int sEcho = -1;
String limit = "";
int iTotal = 0;

try{
iDisplayStart = Integer.parseInt(request.getParameter("iDisplayStart"));
iDisplayLength = Integer.parseInt(request.getParameter("iDisplayLength"));
sEcho = Integer.parseInt(request.getParameter("sEcho"));
}
catch(NumberFormatException nfe){}

//System.out.println("iDisplayStart: " + iDisplayStart + " iDisplayLength: " + iDisplayLength);

if(iDisplayStart != -1 && iDisplayLength != -1)
limit = "LIMIT " + iDisplayStart + ", " + iDisplayLength;

//Check the database.
Connection conn = null;
Statement st = null;
ResultSet rs = null;
int rows = 0;
String[][] data = null;


try{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fcs_db", "root", "ponyboy");
st = conn.createStatement();
String query = "";
if(!limit.equals(""))
query = "SELECT emp.FIRSTNAME, emp.LASTNAME, emp.TITLE, emp.EMAIL_ADDRESS FROM employees as emp ORDER BY LASTNAME " + limit + ";";
else
query = "SELECT emp.FIRSTNAME, emp.LASTNAME, emp.TITLE, emp.EMAIL_ADDRESS FROM employees as emp ORDER BY LASTNAME;";
//System.out.println(query);
rs = st.executeQuery(query);
rs.last();
rows = rs.getRow();
System.out.println("rows = " + rows);
rs.first();
data = new String[rows][4];
for(int i = 0; i < rows; i++)
{
for(int j = 0; j < data[0].length; j++)
{
if(j == 0)
data[i][j] = rs.getString("FIRSTNAME");
else if(j == 1)
data[i][j] = rs.getString("LASTNAME");
else if(j == 2)
data[i][j] = rs.getString("TITLE");
else if(j == 3)
data[i][j] = rs.getString("EMAIL_ADDRESS");
}
rs.next();
}

}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(rs != null) rs.close();
if(st != null) st.close();
if(conn != null) conn.close();
}

try{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fcs_db", "root", "ponyboy");
st = conn.createStatement();
String query = "SELECT COUNT(*) AS TOTAL FROM employees;";

//System.out.println(query);
rs = st.executeQuery(query);
rs.first();
iTotal = rs.getInt("TOTAL");

}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(rs != null) rs.close();
if(st != null) st.close();
if(conn != null) conn.close();
}

//System.out.println("data.length = " + data.length);
output.append("{\"sEcho\": " + sEcho + ", \"iTotalRecords\": \"" + iTotal + "\", \"iTotalDisplayRecords\": \"" + rows + "\", \"aaData\": " );
JSONArray jsonArray = JSONArray.fromObject( data );
output.append(jsonArray.toString());
output.append("}");


out.println(output.toString());



%>

[/code]

allan

I don't know Java, but it sounds to me like you are returning what is required for DataTables server-side processing mode. From the documentation ( http://datatables.net/usage/server-side ) :

> iTotalRecords - Total records, before filtering (i.e. the total number of records in the database)

> iTotalDisplayRecords- Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)

Whereas it sounds like you are returning the number for rows for the page - which DataTables could easily get from aaData.length if it needed.

Allan