Post

Retrieve data from database using jsp, json and ajax

Posted
By Hibare 7 min read

From the past one month I’m working on a project using JSP. It involved retrieving MySQL database content using jQuery Ajax and display to the user. Problem was how to format the data at server side and transferred so that it can be easily processed at the client side. The straight forward answer was to use JSON (JavaScript Object Notation). JSP doesn’t provide library to format the data in JSON format. So, the plan was to use external jar files.

Before proceeding further, i’ll assume that you are well aware with JDBC concepts and Netbeans software (although you can use any IDE). Cutting the story, just jump to main topic. There are many JSON libraries are available to use with JSP (just Google it).

let’s create a project which retrieves the content from jspAjax table in test database (MySQL) whenever user clicks on “fetch content” button and display it to the user in the tabular form.

Pre-requisites

  • json library: download one from here
  • mysql connector: download one from here
  • Jquery script: download one from here

Steps

  1. Create a database named test if does not exist already. Under test database create a table named jspAjax with 3 fields as following-

    Column name Data type Length
    FirstName varchar 50
    LastName varchar 50
    Location varchar 100

    Note: You are free to choose any names, but remember to use same name in the subsequent code.

  2. Insert few rows into the above created table.

  3. Open Netbeans and create a new java web applicatin project. Name the project whatever you want.

  4. Add json-\*\*\*.jar and mysql-connector-\*\*\*.jar to the project library.

  5. Open index.html file and create a button titled fetch content. We want the button to make an AJAX request whenever user clicks on it. Add attribute onclick with value fetch() as follow-

    1

     <button onclick="fetch();">Fetch content</button>

    Here fetch() is a javascript function which is invoked whenever the user clicks the button. This function contains the necessary jQuery AJAX code to make request to the server.

  6. Include jquery.js file in the index.html file in the head section as follow-

    1

     <script src="js/jquery.js"></script>

    Note : here jquery.js resides in the js directory which is in the web directory of the project.

  7. Next create a table with empty body where the actual data is displayed

    1
2
3
4
5
6
7
8
9
10
11
12

     <table border="1px" width="500px;">
     <thead>
         <tr>
             <th>First Name</th>
             <th>Last Name</th>
             <th>Location</th>
         </tr>
     </thead>
     <tbody id="content">
                
     </tbody>
 </table>

    Here body of the table is given an id. This id is used to identify the tag between which the retrieved data is to be displayed

  8. Now we have to define the fetch() function which is invoked whenever the user clicks on the button. This fetch() function contains the jQuery code to make ajax request. As it is a javascript function, we have to define the function between script tags.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

     <script>
     function fetch() {
            
         $.ajax({                        
                 url:"fetch.jsp",
                 dataType:"json",
                 success:function(res){
                         var data="";
                         for(i=0;i<res.length;i++){
                         var p=JSON.parse(res[i]);
                         data+="<tr><td>"+p.FirstName+"</td><td>"+p.LastName+"</td><td>"+p.Location+"</td></tr>";
                         }
                         $('#content').html(data);
                 },
                 error:function() {
                     alert("error occurred");
                 }
             });
     }
 </script>

    As we can see in the above code, the function fetch() contains the ajax call. Lets go through the ajax call.
    url : is the url of the page which processes our request, fetches data from the database and return it to the ajax call (user).
    dataType : specifies the return type of the data. Here it is json as we are expecting data in json format from the server.
    success : it specifies that on success of ajax call execute the function defined it. Here in the above code on successful ajax call, the returned data in the json format from the server is stored in the res variable. The for loop is used to traverse through the json array. In javascript object the data is stored in the key/value pair. For every json array element, we parse the data using JSON.parse method of javascript. This converts the data into a javascript object. The value stored in the object can be retrieved with the key using dot notation. For every object we create a row of the table with the data fields in it. The data is displayed to the user using $().html() of jQuery. Note here the id of table body is used to specify the location on the web page where the data is to be displayed.
    error : is invoked whenever ajax call fails.

    In the end the index.html file looks as follow-

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

     <!DOCTYPE html>
 <html>
     <head>
         <title>JSP, AJAX and JSON demo</title>
         <script src="js/jquery.js"></script>
            
     </head>
     <body>
     <button onclick="fetch();">Fetch content</button>
     <br/>
                    
     <table border="1px" width="500px;">
             <thead>
                 <tr>
                     <th>First Name</th>
                     <th>Last Name</th>
                     <th>Location</th>
                 </tr>
             </thead>
             <tbody id="content"></tbody>
         </table>
     <script>
             function fetch() {
            
                 $.ajax({
                            
                     url:"fetch.jsp",
                     dataType:"json",
                     success:function(res){
                         var data="";
                         for(i=0;i<res.length;i++){
                             var p=JSON.parse(res[i]);
                             data+="<tr><td>"+p.FirstName+"</td><td>"+p.LastName+"</td><td>"+p.Location+"</td></tr>";
                         }
                         $('#status').html("Status : Content fetched");
                         $('#content').html(data);
                     },
                     error:function() {
                         $alert("error occurred");
                     }
                 });
         }
         </script>
     </body>
 </html>

  9. Next we have to create fetch.jsp file which processes requests from the ajax. Head over to the web directory of the project and create fetch.jsp file. Open it and delete any content that was generated automatically.

  10. Inside fetch.jsp import java.sql.* and org.json.* as follow-

    1

    <%@page import="java.sql.*,org.json.*;" %>

  11. Create some final string variable that defines jdbc driver, url, username and password of the mysql as follow-

    1
2
3
4

    final String jdbcdriver="com.mysql.jdbc.Driver";
final String url="jdbc:mysql://localhost/test";
final String user="your_username";
final String pass="your_password";

  12. Create some Connection, Statement and ResultSet variables and connect to the database as follows-

    1
2
3
4
5
6
7
8
9
10

    Connection con = null;
Statement stmt = null;
ResultSet result = null;
            
try{
    Class.forName(jdbcdriver);
    con = DriverManager.getConnection(url,user,pass);
}catch(ClassNotFoundException e){
}catch(SQLException e){}

  13. Create a query selecting all the values from jspajax table and execute it as follow-

    1
2
3
4

    String query;
stmt = con.createStatement();
query = "SELECT * FROM jspAjax";
result = stmt.executeQuery(query);

  14. Write code to check for at-least one rows is available in the result set.

    1
2

    if(!result.next()){
    out.print("0");

  15. Create a JSONArray to hold all the JSONObjects.

    1
2

    }else{
    JSONArray array=new JSONArray();

  16. Loop through all the rows in the result set. For each row create a json object and add values from the result set into the object using put() method. When the object is created add that object to the previous created JSONArray.

    1
2
3
4
5
6
7

    do{
    JSONObject obj = new JSONObject();
    obj.put("FirstName",result.getString("FirstName"));
    obj.put("LastName",result.getString("LastName"));
    obj.put("Location",result.getString("Location"));
    array.put(obj.toString());
}while(result.next());

  17. Send the resultant array to the ajax call using out.print() method

    1

    out.print(array);

In the final fetch.jsp should look like following (add required jdbc code)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

<%@page import="java.sql.*,org.json.*;" %>

<% 
    final String jdbcdriver="com.mysql.jdbc.Driver";
    final String url="jdbc:mysql://localhost/test";
    final String user="your_username";
    final String pass="your_password";
    
    Connection con = null;
    Statement stmt = null;
    ResultSet result = null;
        
    try{
        Class.forName(jdbcdriver);
        con = DriverManager.getConnection(url,user,pass);
    }catch(ClassNotFoundException e){
    }catch(SQLException e){}
    
    try{
        
        String query;
        
        stmt = con.createStatement();
        
        query = "SELECT * FROM jspAjax";
        result = stmt.executeQuery(query);
        
        if(!result.next()){
            out.print("0");
        }else{
            JSONArray array=new JSONArray();
            do{
                JSONObject obj = new JSONObject();
                obj.put("FirstName",result.getString("FirstName"));
                obj.put("LastName",result.getString("LastName"));
                obj.put("Location",result.getString("Location"));
                array.put(obj.toString());
            }while(result.next());
            out.print(array);
        }
        
    }catch(SQLException e){
        out.print("Exception: "+e);
    }
    finally{
        if(stmt != null){
            try{
                stmt.close();
            }catch(SQLException e){}
        }
        if(con != null){
            try{
                con.close();
            }catch(SQLException e){}
        }
    }
%>

When everything is set right you are good go. Run the project and see the effect for yourself.

Note : You can also download this project from here and just run it.

Dev, FullStack
dev web java js ui
This post is licensed under CC BY 4.0 by the author.