A practical simple example of jQuery Datatable

Goal:

Using the JQuery Datatable to construct the list of data, and to add or hide the corresponding columns, the data display requirements have been met. At the same time, JQuery Datatable powerful feature support: Sorting, paging, searching, and so on.

Query Datatable can support the data fully loaded into the local after the data list, sorting, paging, search and other functions will be brought, do not need us to care, in this main explanation through the background dynamic loading data, has reached in large data to improve efficiency before the effect.

1. Paging through the background

2. Sort by the background

3. Search through the background

The specific use method:

1. First build the list of data we need, and the page display table.

<table id= "DataTable" Width= "100%" border= "1" >
  <thead>
    <tr>
      <th>ID</th>
      <th>first name</th>
      <th>last name</th>
      <th>Operation</th>
    </tr>
  <thead>
</table>

The form is simple enough to have a table defined with a good ID and a good header definition.

2. We can go to the jquery DataTable Web site to download a jquery and jquery DataTable of the JS Library. Https://datatables.net/examples/server_side/simple.html.

3. Then introduce the two files into the paging file, and note that the jquery library must be in the front, because the pages are loaded in a sequential order to ensure that the following extensions can be used to jquery. At the same time, download the latest JQuery Datatable version, as it is more concise and more functional in its wording and parameters. "Note: Parameter differences are stated in the Appendix"

4. Write front-end code. We are using Ajax to make requests to the background, so when you configure the DataTable, add {"ServerSide": true} to make sure that the page requests the background at load time, and that it also requests the background every time the DataTable is manipulated. "Attach: If you want to add a little load effect, you can add {" Processing ": true}".

Configure request Background URL: {"ajax": "Load"}

5. Configuration data returns a specific column. In a Datatable, attribute columns is used to configure the properties of specific columns, including the corresponding data column names, whether to support searching, whether to display, whether to support sorting, and so on. Configure our specific columns based on the above page. As follows:

$ (document). Ready (function () {
    $ ("#datatable"). DataTable ({
      "processing": True,
      "ServerSide": true,
      "ajax": "Load",
      "columns": [
        {"Data": "id", "bsortable": false},
        {"Data": "FirstName"},
        {"Data" : "LastName"}
      ]
    });
  

The first column ID, set to not allow sorting. can also be added without displaying: {"visible": false}

6. At this time for the background, the returned data must be in accordance with certain specifications. As follows:

{
  "draw": 2,
  "Recordstotal": One,
  "recordsfiltered": One, "
  data": [
    {
      "id": 1,
      " FirstName ":" Troy ","
      lastName ":" Young "
    },
    {
      " id ": 2,
      " FirstName ":" Alice ",
      " LastName ": LL"
    },
    {
      "id": 3,
      "FirstName": "Larry",
      "LastName": "Bird"
    }
    // ......
  ]
}

Parameter explanation:

Draw: Indicates the number of requests

Recordstotal: Total Record number

Recordsfiltered: Total number of records filtered

Data: An array of concrete objects

7. Last column Operation, we do not have any data to put our common operations columns, such as modify links. The Datatable provides the custom column Columndef s property, and his value is an array object, as shown in the following code:

$ (document). Ready (function () {
    $ ("#datatable"). DataTable ({
      "processing": True,
      "ServerSide": true,
      "ajax": "Load",
      "columns": [
        {"Data": "id", "bsortable": false},
        {"Data": "FirstName"},
        {"Data" : "LastName"}
      ],
      "columndefs": [
        {
          "targets": [3],
          "data": "id",
          "Render": function ( Data, type, full) {return
            "<a href= '/update?id=" + Data + "' >Update</a> ';
          }
        }
      ]
    });
  });

Targets: The target column that represents the specific action required, starting with the subscript 0

Data: Represents the property name of a column that we need

Render: Returns what needs to be displayed. Here we can modify the style in the column, add the specific content

Attribute list: Data, previous attribute value in attribute definition

T ype, unknown

Full, all data values can be obtained by the property column name

8. Let's look at how to search, sort, and page details. Because just to do demo, so use the simplest jdbc+servlet way to achieve.

First of all, let's see, the DataTable gives us the parameters that are passed in making the request:

=============== Request paramerters ================
draw:1
columns[0][data]: ID
columns[0][name]:
columns[0][searchable]: True
columns[0][orderable]: True
Columns[0][search][value]:
columns[0][ Search][regex]: false
Columns[1][data]: FirstName
columns[1][name]:
columns[1][searchable]: True
columns[1][orderable]: True
Columns[1][search][value]:
Columns[1][search][regex]: false
Columns[2][data]: LastName
columns[2][name]:
columns[2][searchable]: True
columns[2][orderable]: True
Columns[2][search][value]:
Columns[2][search][regex]: false
Order[0][column]: 0
order[0 ][DIR]: ASC
start:0
length:10
search[value]:
Search[regex]: false
_: 1399345292266
=============== Request paramerters ================

The useful data is start, length, Order[0][column], Order[0][dir], Search[value]. Specific parameter meaning:

Start: actually record position

Length: Number of pages displayed

Order[0][column]: Because it is a two-dimensional table, so only one dimension needs to be sorted, so the order's subscript is not 0. The attribute represents the order in which the columns need to be sorted.

Order[0][dir]: Sort Way ASC | DESC

Search[value]: The value in the Search input box

9. These properties are useful for sorting, searching, and paging the background. "Note: Because it is a two-dimensional table, and only the operation of a column, nature is one-dimensional, so the order subscript is always 1." The future operation of two-dimensional tables remains to be studied. 】

First look at the DAO layer code that contains these features:

Public list<data> loaddatalist (int pageSize, int startrecord, string sortcolumn, String sortdir, String Searchvalue
    ) {list<data> results = new arraylist<data> ();
               
    StringBuffer sql = new StringBuffer ("SELECT * from data");
    For search string[] Columnsname = {"id", "firstName", "LastName"};
    Boolean searchable = false; if (Searchvalue!= null &&! "".
      Equals (Searchvalue)) {sql.append ("where");
    searchable = true;
      } if (searchable) {StringBuffer temp = new StringBuffer ();
      for (String column:columnsname) {temp.append (column+ "like '%" + searchvalue + "% ' or");
    } sql.append (temp.substring (0, Temp.length ()-3));
               
    //For Order Sql.append ("ORDER BY" + SortColumn + "" + Sortdir + "");
    for pagination sql.append ("Limit?,?");
               
    System.out.println (Sql.tostring ());
    try {  stmt = Conn.preparestatement (sql.tostring ());
      Stmt.setint (1, Startrecord);
                 
      Stmt.setint (2, Startrecord + pageSize);
      ResultSet rs = Stmt.executequery ();
        while (Rs.next ()) {Data data = new data ();
        Data.setid (Rs.getint (1));
        Data.setfirstname (rs.getstring (2));
                   
        Data.setlastname (Rs.getstring (3));
      Results.add (data);
    The catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();
  return results; }

In the DAO layer, the statistic code is similar, just remove the split-page and sorted SQL concatenation.

We need to convert our data to JSON to return to the front end, and also to display the total number of records, filtered total number of records. So we need a unified class to encapsulate this data. Since more than one object in a system needs to be shown to the front end, it is uniformly done as a DataTable encapsulation class.

Package com.web.vo;
Import java.util.List;
/**
 * This VO used to generate the JSON data for data table, so please ensure the attribute name is correct.
 * If you are want to define the fields name by yourself, please visit:https://datatables.net
 * @author Troyyang *
  
   * @param <T>
 * * Public
class Datavo<t> {
    private int draw;//Client request times
    Private I NT Recordstotal; Total records number without conditions
    private int recordsfiltered.//Total records number with conditions
    Private list<t> data; The data we should display on the page
    //Getter and setter method
}
  

Everything is available, only the front and back interaction code. The simplest servlet is used here.

for pagination int pageSize = 10;
  int startrecord = 0;
  String size = request.getparameter ("Length"); if (! "".
  Equals (size) && size!= null) {pageSize = Integer.parseint (size);
  String CurrentRecord = Request.getparameter ("Start"); if (! "".
  Equals (CurrentRecord) && currentrecord!= null) {Startrecord = Integer.parseint (CurrentRecord);
  }//For sortable String SortOrder = Request.getparameter ("order[0][column]");
  String Sortdir = Request.getparameter ("Order[0][dir]");
  System.out.println ("SortOrder:" + sortOrder);
         
  System.out.println ("Sortdir:" + sortdir);
  For search String Searchvalue = Request.getparameter ("search[value]");
  int count = 0;
  list<data> results = new arraylist<data> ();
  Count = Dao.count ();
  Results = Dao.loaddatalist (pageSize, Startrecord, Columnsname[integer.parseint (SortOrder)], Sortdir, searchvalue);
  datavo<data> result = new datavo<data> (); Result.setdraw (IntegEr.parseint (Request.getparameter ("draw") = = null?
  "0": Request.getparameter ("Draw")) + 1);
  Result.setdata (results);
  Result.setrecordstotal (count);
  Result.setrecordsfiltered (count);
  Gson Gson = new Gson ();
  String output = Gson.tojson (result);
  System.out.println ("Output JSON: \ n" + output);
  PrintWriter out = Response.getwriter ();
  Out.write (output);
  Out.flush (); Out.close ();

Appendix:

Prior to using the JQuery Datatable 1.10 version, the request must be made using Sajaxsource, but the request data differs from the current version of the request data as follows:

=============== Request paramerters ================
secho:1
icolumns:4
scolumns:,,,
idisplaystart:0
idisplaylength:10
mdataprop_0:id
ssearch_0:
bregex_0:false
bsearchable_0: True
bsortable_0:false
mdataprop_1:firstname
ssearch_1:
bregex_1:false
bsearchable_1: True
bsortable_1:true
mdataprop_2:lastname
ssearch_2:
bregex_2:false
bsearchable_2: True
bsortable_2:true
mdataprop_3:id
ssearch_3:
bregex_3:false
bsearchable_3:true
bsortable_3:true
ssearch:
bregex:false
isortcol_0:0
ssortdir_0:asc
isortingcols : 1
_: 1399515247114
=============== Request paramerters ================