Limit of displaying rows in DataTables

Question

I use Datatables 2.0. How can I set the limit of display number of rows?

I have 1000 rows that are all showing on one page. At Datatables 1.9 it was going with 'iDisplaylenght'. But in Datatable 2.0 I've tried to set 'lenght:10' like in this documentation: http://next.datatables.net/manual/server-side but it doesn't work.

EDIT:

on this link above you can see my problem. It doesn't matter which number I choose from the checkbox.

I have found out, with the link from @Julian(thanks), that if I don´t use the ajax call, it works fine. But if I get my data from the server-side all of my data are showing. See the link. So it must be a problem on the server-side?!

@SuppressWarnings("unchecked")
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String sql = "SELECT UID,LastName,FirstName FROM employe LIMIT 1000";
    
    int rows=0;
    int count = 0;
    JSONArray arrayback = new JSONArray();
    JSONObject object = new JSONObject();
    PrintWriter out = response.getWriter();
    
    try {

        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/check","****","****");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData meta = rs.getMetaData();
        rows= meta.getColumnCount();        
        
        while(rs.next())
        {
            JSONArray array = new JSONArray();
            array.add(rs.getString("UID"));
            array.add(rs.getString("LastName"));
            array.add(rs.getString("FirstName"));
            count++;
            arrayback.add(array);
            
        }
        
        object.put("aaData", arrayback);
        object.put("sEcho",1);
        object.put("iTotalRecords", count);
        object.put("iTotalDisplayRecords", count);      
        out.print(object);          
        
    
        con.close();
}catch(exception e)
{
e.printStackTrace();
    }
}

This is my server code.

Answer 1

Are you using server-side processing for your datatable? Because your link to the docs points to the server-side configuration for Datatables. Imho 1000 rows should be easily rendered by the client via "classic" Datatable-methods. Also the options pointed out on your linked page are for the server-side script to process the correct data for the table. That means, the length value tells the server(!) how many rows he has to return. So your page have nothing to do with the "client-side" setup of the datatable.

So, to adjust the displayed number of rows on the "client-side" use the pageLength value:

$(document).ready(function() {
    $('#example').dataTable( {
        "pageLength": 20 
    } );
} );

You can also adjust the lengthMenu to setup the dropdown-selection where the user can specify how much rows he wants to see per page:

$(document).ready(function() {
    $('#example').dataTable( {
        "lengthMenu": [20, 40, 60, 80, 100],
        "pageLength": 20
    } );
} );

See a working demo here: http://jsfiddle.net/vf5wA/2/

EDIT:

I think your problem is, that you are returning the full set of items per each single request. As far as I know, Datatables works a little bit different. On your SQL-Statement you are setting a LIMIT to 1000, that means that 1000 rows will be returned by your api. But the Datatable gives you an parameter "iDisplayLength" which should be passed to your api via AJAX-Call. The number of items specified in "iDisplayLength" will then set the LIMIT for your SQL-Query.

E.g.:

AJAX-Call: https://api.example.com/datatable/something?iDisplayLength=50

And on your server, the "iDisplayLength"-Parameter will set the LIMIT for querying:

String sql = "SELECT UID,LastName,FirstName FROM employe LIMIT {iDisplayLength}";
object.put("iTotalRecords", {iDisplayLength});

I would try that, but I have never used server-side processing for Datatables, so this is just a shot in the dark.

Answer 2

You need to use lengthMenu & pageLength property.

"lengthMenu": [ 10, 25, 50, 75, 100 ]
"pageLength": 50

lengthMenu give you option to select desired size from dropdown & pageLength provide default size of page. If you dont use pageLength property first value in lengthMenu array is used as page length.