Displaying Database Records in jQuery DataTable

Hey guys, Bushan here, welcome back to B2 Tech! Today in this article, I will show you how to use jQuery Datatable and how to display the database records in jQuery Datatable so let’s get started!



If we are creating any kind of Web application then displaying the records in web page is very very common thing. If the records is just 10 or 20 then we can display it in a HTML table but if the records is more than 100 or 200 then we need some kind of Grid, which will take care of everything like displaying the records in a table, pagination, searching, sorting and even exporting the data to PDF or excel. jQuery Datatable will gives us all these options, so we don’t need to write all these boiler plate code, all we need to do is pass the database records to this jQuery Datatable and it will take care of everything, enough talking let’s see how to use it!

The first step is to get the data from a Database –

We will call a Java Web Service to get the data from a Database.

http://localhost:8037/spring-mvc-restfull-crud-example/book

Here is the JSON data when we make a HTTP GET request,



[
    {
        "id": 64,
        "title": "Walden",
        "author": "Henry David Thoreau"
    },
    {
        "id": 63,
        "title": "One night at call centre",
        "author": "Chethan Bhagath"
    },
    {
        "id": 59,
        "title": "Three mistakes of my life",
        "author": "chethan bhagath"
    },
    {
        "id": 60,
        "title": "Two states",
        "author": "chethan bhagath"
    },
    {
        "id": 61,
        "title": "I too had a love story",
        "author": "Ravinder singh"
    },
    {
        "id": 62,
        "title": "Half girlfriend",
        "author": "chethan bhagath"
    },
    {
        "id": 65,
        "title": "Two Lives",
        "author": "Vikram Seth"
    },
    {
        "id": 66,
        "title": "Lives of the poets",
        "author": "Samuel Johnson"
    },
    {
        "id": 67,
        "title": "Wuthering Heights",
        "author": "Emily Bronte"
    },
    {
        "id": 68,
        "title": "Lord of flies",
        "author": "William Golding"
    },
    {
        "id": 69,
        "title": "Shadow Lines",
        "author": "Amitav Ghosh"
    },
    {
        "id": 70,
        "title": "The Great Indian Novel",
        "author": "Shashi Tharoor"
    },
    {
        "id": 71,
        "title": "The Blue Umbrella",
        "author": "Ruskin Bond"
    },
    {
        "id": 72,
        "title": "The Laughing Monsters",
        "author": "Denis Johnson"
    }
]

I have created this Web Service using Spring REST and Hibernate. When we make a HTTP GET request, it will gives us all the records in a JSON format. Later we will call this web service from jQuery Ajax method.

NOTE: You can create this web service using any server side technology, the goal of this web service is to retrieve the database records in JSON format.



The second step is to create a HTML page and include 3 libraries –

  1. jQuery core library
    <script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js”></script>
  2. jQuery dataTable css library
    <link rel=”stylesheet” type=”text/css” href=”https://cdn.datatables.net/1.10.18/css/jquery.dataTables.css”/>
  3. jQuery dataTable js library
    <script type=”text/javascript” src=”https://cdn.datatables.net/1.10.18/js/jquery.dataTables.js”></script>

The third step is to create a table inside the html –

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Page Title</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.css"/>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.js"></script>
</head>
<body>
    <div style = "width: 700px; padding: 5px; border: 1px solid black;">
        <table id = "datatable">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Title</th>
                    <th>Author</th>
                </tr>
            </thead>
        </table>
    </div>
</body>
</html>

The fourth step is to call jQuery Ajax method –

    <script type = "text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: "http://localhost:8037/spring-mvc-restfull-crud-example/book",
                method: "GET",
                dataType: "json",
                success: function (data) {
                    $("#datatable").dataTable({
                        data: data,
                        sort: false,
                        searching: false,
                        paging: false,
                        columns: [
                            {'data': 'id'},
                            {'data': 'title'},
                            {'data': 'author'},
                        ]
                    })
                }
            })
        })
    </script>



At this point, save the file and open it in a browser, you will see the following output. All the records are displayed in the datatable.

Here is the complete source code

datatableExample.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Page Title</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.css"/>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.js"></script>
    <script type = "text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: "http://localhost:8037/spring-mvc-restfull-crud-example/book",
                method: "GET",
                dataType: "json",
                success: function (data) {
                    $("#datatable").dataTable({
                        data: data,
                        sort: false,
                        searching: false,
                        paging: false,
                        columns: [
                            {'data': 'id'},
                            {'data': 'title'},
                            {'data': 'author'},
                        ]
                    })
                }
            })
        })
    </script>
</head>
<body>
    <div style = "width: 700px; padding: 5px; border: 1px solid black;">
        <table id = "datatable">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Title</th>
                    <th>Author</th>
                </tr>
            </thead>
        </table>
    </div>
</body>
</html>

That’s it for this article, I hope this article is helped you in one or the other way, if you have any questions ask me in the comment section, I will try to answer it.



Download the fileDownload

 

About the author

Bushan Sirgur

Well, I am Bushan Sirgur from Banglore, India. Currently, I am working as a Software Developer in a Service Base Company. I am interested in JAVA/J2EE, Angular 2, JavaScript, jQuery, MongoDB.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *