AJAX Fetch Data from Database in JSP and Servlet with JSONArray

DOWNLOAD

I had previously written two posts about implementing AJAX in Java web applications. One is about making ajax calls to Servlet & update JSP page with response using jQuery and the other is about implementing cascading dropdownlists in AJAX using JSON and jQuery. One more scenario where AJAX implementation is much desirable is while fetching data from database. So in this post, I am going to explain on how to fetch data from MySql database in JSP and Servlet with JSON and jQuery.

In one of my posts, I wrote about what is JSON and how it can be used to convert complex Java Objects like lists, maps, arrays to Javascript Strings that can be parsed using jQuery. To fetch number of rows containing data from database, multiple JSON objects should be returned from Servlet using JSONArray. There are many JSON libraries available and I am going to use google's gson library in this example. 

1. First step is to download all required libraries,

• GSON
• MySql Java Connector

2. Create a Web Application and add the downloaded jar files to WEB-INF/lib folder.

3. Create a model class that represents structure of data in your database table. In this example, I am using mysql country table with the following structure,

-------------------------+------+-----+---------+-------+

| Field          | Type  | Null | Key | Default | Extra |

-------------------------+------+-----+---------+-------+

| Code           | char(3)| NO  | PRI |         |       |

| Name           | char(52)| NO |     |         |       |

| Continent      | char(100)| NO|     | Asia    |       |

| Region         | char(26)| NO |     |         |       |

| Population     | int(11) | NO |     | 0       |       |

| Capital        | int(11)| YES |     | NULL    |       |

I suggest you to create a table with the same structure. Create a class and name it as "Countries.java". Copy and paste the below code in it.

public class Countries {
 
 public Countries(String code,String name, String continent,String region,int population, String capital )
        {      
          this.setCode(code);
          this.setName(name);
          this.setContinent(continent);
          this.setRegion(region);
          this.setPopulation(population);
          this.setCapital(capital);
        }
 
 public Countries() {
  
 }

    private String code;
    private String name;
    private String continent;
    private String region;
    private int population;   
    private String capital;
    
      
    public void setCode(String code) {
  this.code = code;
 }
 public String getCode() {
  return code;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getName() {
  return name;
 }

 public void setContinent(String continent) {
  this.continent = continent;
 }

 public String getContinent() {
  return continent;
 }

 public void setRegion(String region) {
  this.region = region;
 }

 public String getRegion() {
  return region;
 }

 
 public void setPopulation(int population) {
  this.population = population;
 }

 public int getPopulation() {
  return population;
 }

 
 public void setCapital(String capital) {
  this.capital = capital;
 }

 public String getCapital() {
  return capital;
 }
}

4. Create a class and name it as 'FetchData.java'. Copy and paste the below code. This class contains code to connect to database and retrieve values from it.

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;


public class FetchData {

    private static Connection connection = null;

    public static Connection getConnection() {
        if (connection != null)
            return connection;
        else {
            try {
                Properties prop = new Properties();
                InputStream inputStream = FetchData.class.getClassLoader().getResourceAsStream("/db.properties");
                prop.load(inputStream);
                String driver = prop.getProperty("driver");
                String url = prop.getProperty("url");
                String user = prop.getProperty("user");
                String password = prop.getProperty("password");
                Class.forName(driver);
                connection = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return connection;
        }

    }
    
    public static ArrayList<Countries> getAllCountries() {
     connection = FetchData.getConnection();
        ArrayList<Countries> countryList = new ArrayList<Countries>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("select * from country limit 10");
        
            while(rs.next()) { 
             Countries country=new Countries();
             country.setCode(rs.getString("Code"));
             country.setName(rs.getString("Name"));
                country.setContinent(rs.getString("Continent"));
                country.setRegion(rs.getString("Region"));
             country.setPopulation(rs.getInt("Population"));
             country.setCapital(rs.getString("Capital"));
             countryList.add(country);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return countryList;
    }
}

I have created a file under src folder to store mysql database connection string properties in the name "db.properties" and retrieving values from it. Below is the contents of db.properties file.


driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbname
user=root
password=xxxxxx


5. Create a Servlet and name it as 'PopulateTable'. Copy and paste the below code. The below code retrieves data from mysql table using getAllCountries() method of FetchData class and sends JSONArray object as response.

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.reflect.TypeToken;


@WebServlet("/PopulateTable")
public class PopulateTable extends HttpServlet {
 private static final long serialVersionUID = 1L;

    public PopulateTable() {
        
    }
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    
  ArrayList<Countries> country=new ArrayList<Countries>();
  country=FetchData.getAllCountries();
  Gson gson = new Gson();
  JsonElement element = gson.toJsonTree(country, new TypeToken<List<Countries>>() {}.getType());

  JsonArray jsonArray = element.getAsJsonArray();
  response.setContentType("application/json");
  response.getWriter().print(jsonArray);
  
 }

 
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  
 }

}

Note in the above code, a gson object is invoked and then the 'country' ArrayList object of type "Countries" that contains data populated from database is serialized using toJsonTree(object,Type) method. Then this JSONElement object is converted to JSONArray and passed to the reponse object. More details on serializing data using GSON can be found here.

6. Finally create JSP page and copy paste below html code in it.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>AJAX JsonArray Example</title>
<link href='http://fonts.googleapis.com/css?family=Oxygen' rel='stylesheet' type='text/css'>
<style type="text/css">
table, td, th
{
border:1px solid green;
font-family: 'Oxygen', sans-serif;
}
th
{
background-color:green;
color:white;
}
body
{
 text-align: center;
}
.container
{
 margin-left: auto;
 margin-right: auto;
 width: 40em;
}
</style>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
 $("#tablediv").hide();
     $("#showTable").click(function(event){
           $.get('PopulateTable',function(responseJson) {
            if(responseJson!=null){
                $("#countrytable").find("tr:gt(0)").remove();
                var table1 = $("#countrytable");
                $.each(responseJson, function(key,value) { 
                     var rowNew = $("<tr><td></td><td></td><td></td><td></td><td></td><td></td></tr>");
                        rowNew.children().eq(0).text(value['code']); 
                        rowNew.children().eq(1).text(value['name']); 
                        rowNew.children().eq(2).text(value['continent']); 
                        rowNew.children().eq(3).text(value['region']); 
                        rowNew.children().eq(4).text(value['population']); 
                        rowNew.children().eq(5).text(value['capital']); 
                        rowNew.appendTo(table1);
                });
                }
            });
            $("#tablediv").show();          
  });      
});
</script>
</head>
<body class="container">
<h1>AJAX Retrieve Data from Database in Servlet and JSP using JSONArray</h1>
<input type="button" value="Show Table" id="showTable"/>
<div id="tablediv">
<table cellspacing="0" id="countrytable"> 
    <tr> 
        <th scope="col">Code</th> 
        <th scope="col">Name</th> 
        <th scope="col">Continent</th> 
        <th scope="col">Region</th> 
        <th scope="col">Population</th> 
        <th scope="col">Capital</th>          
    </tr> 
</table>
</div>
</body>
</html>

That is all! Let me explain what the above code does. I have a button "Show Table" and when the user clicks on it, it does a partial page update and displays a table with values populated from database. 

The jQuery script is the heart of this code, initially I am hiding the table on page load and when the user clicks on "Show Table" button, the first step is to clear the existing rows of the table, if any. Then an AJAX call is made to the Servlet "PopulateTable" using $.get jQuery method and  response from the servlet is received in the 'responseJson' object which is a JSONArray object. After this, response JSONArray is parsed by looping through each of its row and a new table row is created for every JSONArray record. All the new rows that are created is then appended to the table, "countrytable" and is displayed in the page.

Initially when the page loads, the page looks like this,

When 'Show Table' button is clicked, partial page update occurs and the table with data is displayed as shown below,

Updates:

Please leave your comments and views about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts.

Subscribe to GET LATEST ARTICLES!