Row display limit in DataTables

Question

I am using Datatables 2.0. How can I set a limit on the number of rows displayed. I have 1000 lines and they all appear on one page. In Datatables 1.9, this happened with "iDisplaylenght". But in Datatable 2.0 i'v tried to set "length: 10", as in this document: http://next.datatables.net/manual/server-side but it does not work.

Thank you very much!

EDIT:

http://img4web.com/g/GC8P7

from this link above u can see my problem. It doesn’t matter which number I choose from the checkbox.

I found out with a 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 my data will show. See Link. So this is a server side problem ?!

@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

Do you use server side processing for your data type? Because your doc link points to server-side configuration for Datatables. Imho 1000 lines should be easily visualized by the client using the "classic" Datatable methods. Also, the parameters indicated on your linked page apply to the server side of the script to process the correct data for the table. This means that the length value tells the server (!) How many lines it should return. Thus, your page has nothing to do with setting a "datatable" on the client side.

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 configure lengthMenu to set a drop-down list in which the user can specify how many lines he wants to see on the 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 a full set of elements for each individual request. As far as I know, datatables works a little differently. In your SQL-Statement, you set LIMIT to 1000, which means that 1000 rows will be returned by your api. But Datatable gives you the parameter "iDisplayLength", which should be passed to your api via AJAX-Call. The number of elements specified in "iDisplayLength", then set the LIMIT for your SQL query.

eg:.

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

And on your server, the "iDisplayLength" parameter will set the LIMIT for the request:

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

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

Answer 2

You need to use the property lengthMenu and pageLength .

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

lengthMenu gives you the option to select the size you want from the drop-down list and pageLength specify the default page size. If you are not using the pageLength property, the first value in the lengthMenu array lengthMenu used as the page length.