AJAX Fetch Data from Database in JSP and Servlet with JSONArray




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.


AJAX Fetch Data from Database in JSP and Servlet using jQuery | programming-free.com



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,


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,

AJAX Fetch Data from Database in JSP and Servlet using jQuery | programming-free.com

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


AJAX Fetch Data from Database in JSP and Servlet using jQuery | programming-free.com




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!


advertise here

Related

Jquery 5198932614575966874

Post a Comment

  1. please can you provide the demo of pure client and server using json rpc that should be communicate with database(Mysql in my case)

    ReplyDelete
  2. Nice demo. I am trying to implement returning an ArrayList of String[] instead of Objects. I am having trouble parsing the result.

    I get back a string of [["field1:value1a", "field2:value2a"]["field1:value1b", "field2:value2b"], ...], which I think is fine.

    when I try to parse the results into a table in my jQuery jsp, I get a new row for each result, but no data... :-( I suspect the data is not in a key/value pair format?
    $.get('http://server/ActionServlet',
    {searchLibrary:"JPMMST",
    searchName:"SSTLesseeSQL",
    filterFields:"NMA3LF.VendorNo:Abbreviation",
    filterValues:filterVal,
    listFields:"VendorNo:VendorType:Abbreviation:VendorName" })
    .done(function(responseJson) {
    if(responseJson!=null){
    $("#dataTable").find("tr:gt(0)").remove();
    var table1 = $("#dataTable");
    $.each(responseJson, function(key,value) {
    var rowNew = $("tr-td-td-td-td");
    rowNew.children().eq(0).text(value['VendorNo']);
    rowNew.children().eq(1).text(value['VendorType']);
    rowNew.children().eq(2).text(value['Abbreviation']);
    rowNew.children().eq(3).text(value['VendorName']);
    rowNew.appendTo(table1);
    });
    }
    })
    .fail(function() { alert("AJAX fail"); })
    ;
    fdTableSort.init("dataTable");
    $("#tablediv").show();

    ReplyDelete
  3. Thanks very much. You have helped me a great deal. I like the way you explain these things. The examples are very clear. I like the Idea of screen shots because they tell what to expect and so they add on the motivation. Tha...............nx

    ReplyDelete
    Replies
    1. Hai very Thanks alot,this example is helped to me for my module to get data dynamically from mysql in servlet using JsonArray.and ur website plz provide more some tutorials on Spring-mvc,hibernate.Thank u

      Delete
  4. Thank you. u have helped me so much.. I need some more help . I have a textbox in display.jsp page and i want to send the parameter of that text box to fetchdata.java so that i can use that value in SQL Query where condition and retrive a definite result.

    ReplyDelete
  5. How Can i make values displayed in 1st column to Href .

    ReplyDelete
  6. var AB = value['name'];
    39 var HL = $('< input type="radio" id="radio1" name="radio1" onclick= function1('+AB+')/>'); HL.appendTo(rowNew.children().eq(0));
    40 rowNew.children().eq(1).text(value['name']);
    41

    i have changed the caode as above but i am getting
    ReferenceError: Afzal is not defined

    view(Afzal);
    when i click on corresponding radio button.

    ReplyDelete
  7. if any body can help please replay to above question .

    ReplyDelete
  8. i am having problem of import javax.servlet.annotation.WebServlet;
    I am using eclipse 3.0 and Apcahe Tomcat 6.0

    plz provide the link for jar file for it or those jar file....

    ReplyDelete
    Replies
    1. Hi,

      Look for servlet-api.jar file above version 3.0 and add it to your Project's WEB-INF/lib folder.

      I also suggest you to use Tomcat version 7.0 instead of version 6.0.

      Hope this helps!

      Delete
  9. Hi, all is fine except I want to use the returned JSON value of e.g. 'status' to determine what image tag to show in the table. When I put in a valid image tag in the cell, I see the literal text and it is not rendered like a normal table would be. I tried some simple markup 'Hello World' and the bolding was ignored and the cell rendered at text with the and strings in the displayed text.

    I can't view source to see what the actual HTML is.

    App Status
    APP 1
    APP 2
    APP 3

    Here are some test lines setting the 2nd column (img was shorted to im so I could publish this):
    rowNew.children().eq(1).text(value['imgTag']); // Literal text
    rowNew.children().eq(1).text(value['']); // Nothing
    rowNew.children().eq(1).text(img); // Literal text, not the image.
    rowNew.children().eq(1).text(value['Hello World']); // Nothing
    rowNew.children().eq(1).text('Hello World'); // Literal text, not the marked up html content.

    - Thanks -

    ReplyDelete
  10. I found postings about needing html encoding to embed markup in JSON.

    ReplyDelete
    Replies
    1. Hi Valon can you please let me know how you have done it

      Delete
  11. Thanks for this example.
    Question:
    here are you have the limit on the data to be fetched in the batch of 10 record what if there are more data inside the table. In that case how are we supposed to show them into the next table without removing the limit 10.

    ReplyDelete
    Replies
    1. You have to do server side paging for that, if you want to fetch next set of 10 records inside your table. This post does not cover this topic.

      Thanks,
      Priya

      Delete
  12. Hi Priya Can you please let me know how to use the Aggregate functions on the DB table and populate it on top of the table from the above example.
    For example:
    Last updated date: Max(Modified date) -> from DB
    ========================
    and the table col 1 | col 2 | col3|...

    In addition I have one more question: how to get a specific value from DB for example status and display an image corresponding to status

    Please help

    ReplyDelete
  13. i dont undestandig give code;.
    whta are Gson,JsonElement element = gson.toJsonTree(country,
    new TypeToken>() {
    }.getType());
    JsonArray jsonArray = element.getAsJsonArray();......................


    Gson gson = new Gson();
    JsonElement element = gson.toJsonTree(country,
    new TypeToken>() {
    }.getType());
    JsonArray jsonArray = element.getAsJsonArray();

    ReplyDelete
  14. hi priya..i am doing project on jsp.am unable to insert image with some other data and retrive with some other data and image...can u plz help me...otherwise suggest me how to do...
    thanks in advance

    ReplyDelete
  15. hey when i click on submit it not display any data...whats wrong in it? i created database so as table insert values in it too

    Properties prop = new Properties();

    InputStream inputStream = FetchData.class.getClassLoader().getResourceAsStream("/db.properties");

    prop.load(inputStream);

    String driver = prop.getProperty("jdbc:mysql:");

    String url = prop.getProperty("localhost:3306/country_db");

    String user = prop.getProperty("root");

    String password = prop.getProperty("");

    Class.forName(driver);

    connection = DriverManager.getConnection("localhost:3306/country_db", "root", "");

    } 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 getAllCountries() {

    connection = FetchData.getConnection();

    ArrayList countryList = new ArrayList();

    try {

    Statement statement = connection.createStatement();

    ResultSet rs = statement.executeQuery("select * from country");

    ReplyDelete
  16. hi priya..i have doubt like editing the comment...
    whenever click on edit link should load a servlet response through ajax..
    for updating am passing the id value to the servlet as an argument..

    ReplyDelete
  17. Excellent article Priya, this is exactly what I was looking for. Many Thanks.

    ReplyDelete
  18. thank you very much for this

    ReplyDelete
  19. thanks for sharing this with us..

    ReplyDelete
  20. thanks for the code ....how to insert anchor tag inside script(inside td) which helps me to redirect to another page....thnks for the help....

    ReplyDelete
  21. thanks a lot, amazing example continue posting such good stuff!

    ReplyDelete
    Replies
    1. Most welcome and thank you for the feedback!

      Delete
    2. ArrayList country=new ArrayList();
      country=FetchData.getAllCountries();
      Gson gson = new Gson();
      JsonElement element = gson.toJsonTree(country, new TypeToken>() {}.getType());
      JsonArray jsonArray = element.getAsJsonArray();
      response.setContentType("application/json");
      response.getWriter().print(jsonArray);
      ------------------------------------------------------------------------------------------------
      I want this response(jsonArray) to bind with a combobox in jsp. If I use this jsonArray as response then it prints as list of objects in combobox but i want it to be displayed as string. How can be done this?

      Delete

emo-but-icon

Currency Converter

Built using AngularJS and ASP.NET Web API

SUBSCRIBE


item