How to populate a dropdown list using AJAX in JSP

Hello friends, welcome to another blog about JSP and servlet. In this blog, I am going to share with you how to populate a dropdown list with the database using AJAX in JSP. This is gonna an amazing post to create a dynamic dependent select option using JSP, Servlet, MySQL, jQuery, and AJAX. There are many examples of dynamic dependent dropdown lists. But I thought the best example is selecting Country, State, and City. In many website registration forms, maybe you have seen that when you select a Country the state list appears and when you select the state the city list appears. So let’s learn to build a JSP project for implementing a dynamic dropdown list using Ajax, MySQL database, and Java servlet.

Prerequisite 

For this project, we need to download two jar files. The first one is MySQL Connector and the Second one is gson-2.8.6.jar. After downloading these two jar files, add those two jar files to your project library folder or build-class path. But if you are going to use the Maven project then here is the link for MySQL Connector and gson-2.8.6.jar file Maven dependency link. Just copy the dependency and paste it to your  pom.xml file. If you fetch any problems, you can visit our TechTutorial YouTube Channel for the video tutorial. Don’t forget to subscribe to the channel.

For your project problem or to create a complete project, you can 

Step-1:

I hope you set up your project properly. It’s time to create our MySQL Database and Table. In our database, we will create 3 tables for the country(id, name), state(id, c_id, name), and city(id, c_id, s_id, name). To create a database copy the below code and paste it to your MySQL Workbench query tab.

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;

CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `s_id` int(11) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  KEY `s_id` (`s_id`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `country` (`c_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `city_ibfk_2` FOREIGN KEY (`s_id`) REFERENCES `state` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `country` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(150) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `state`
--

DROP TABLE IF EXISTS `state`;

CREATE TABLE `state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id_idx` (`c_id`),
  CONSTRAINT `c_id` FOREIGN KEY (`c_id`) REFERENCES `country` (`c_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Step-2:

After then its time to connect our project with our MySQL Database. We are using the latest MySQL latest jar file. MySQL driver class name is “com.mysql.cj.jdbc.Driver”. Now create a Java class file under your source package name. In my case, I name it DBCon.java. If you do the same, then copy the below code and paste that.

package cn.techtutorial.dependentdropdownlist;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class DBCon {
    private static Connection con;

    public static Connection getCon() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/filesharing","root","almamun@123");
        }catch (ClassNotFoundException | SQLException ex) {
            ex.printStackTrace();
        } 
        return con;
    }
}

Step-3:

Until this step, I hope you connected your project with the database without any errors. If there is any error don’t forget to comment for help or watch the video tutorial Dynamic dependent drop-down list with ajax in JSP. Now we need our class for the three tables we created. This is a very simple model class with the variables, getter, and setter. 

Let’s do that step by step.

—1st, we will create a Country.java class. Create two variables, id, and name. And the Getter and setter for those two variables. For this class, the source code is down below.

package cn.techtutorial.dependentdropdownlist;


public class Country {
    int id;
    String name;

    public Country() {
    }

    public Country(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

—2nd Create State.java class. In this class need three variables which are id, c_id, and name and Getter and Setter for those.  For this class, the code is down below.

package cn.techtutorial.dependentdropdownlist;


public class State {
    int id;
    int countryId;
    String name;
    

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getCountryId() {
        return countryId;
    }

    public void setCountryId(int countryId) {
        this.countryId = countryId;
    }

    public String getName() {
        return name;
    }

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

—3rd, Create State.java class. In this class need four variables which are id, c_id, s_id, and name. And Getter and Setter for those.  For this class, the code is down below.

package cn.techtutorial.dependentdropdownlist;


public class City {
    int id;
    int countryId;
    int stateId;
    String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getCountryId() {
        return countryId;
    }

    public void setCountryId(int countryId) {
        this.countryId = countryId;
    }

    public int getStateId() {
        return stateId;
    }

    public void setStateId(int stateId) {
        this.stateId = stateId;
    }

    public String getName() {
        return name;
    }

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

Step-4:

To create a dynamic drop list using Ajax in JSP, we need data. I mean, we require our country, state, and city name list. To store that information we created three tables in our database, and we already connected our database with our project. Now it’s time to retrieve those data from the database. So for this operation, we will create a Data Access Object (DAO) class with three methods. Those methods will be responsible to retrieve information from the database and will return a list of data. 

Let’s check out what’s the class name and methods. You can name it any. In my case named the class is CountryStateDao.java and three methods are List<Country> getAllCountry(), List<State> getStateByCountryId(int countryId), and List<City> getCityByStateId(int stateId). I would like to explain about those methods and their responsibility. 

1. getAllCountry: In this method, we will write an SQL query that will retrieve all the country names and id from the country table in our database. And will return a List of Country model objects. Later than using the getter method in our model class we will access those data. 

2. getStateByCountryId: In this method, we will write an SQL query that will retrieve all the state information for a specific country id from the state table in our database. And will return a List of State model objects. Later than using the getter method in our model class, we will access those data. 

3. getCityByStateId: In this method, we will write an SQL query that will retrieve all the city information for a specific state id from the state table in our database. And will return a List of City model objects. Later, using the getter method in our model class, we will access those data. 

For this class the source code is down below, you can copy and paste it to your class.

package cn.techtutorial.dependentdropdownlist;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class CountryStateDao {
    Connection con;
    PreparedStatement pst;
    String query;
    ResultSet rs;
    
    public CountryStateDao(Connection con) {
        this.con = con;
    }
    
    public List<Country> getAllCountry(){
        List<Country> list  = new ArrayList<>();
        try{
            query = "select * from country";
            pst = this.con.prepareStatement(query);
            rs = pst.executeQuery();
            while(rs.next()){
                Country country = new Country();
                country.setId(rs.getInt("c_id"));
                country.setName(rs.getString("c_name"));
                list.add(country);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }
    
    
    public List<State> getStateByCountryId(int countryId){
        List<State> list = new ArrayList<>();
        try{
            query = "select * from state where c_id=?";
            pst = this.con.prepareStatement(query);
            pst.setInt(1, countryId);
            rs = pst.executeQuery();
            while(rs.next()){
                State state = new State();
                state.setId(rs.getInt("id"));
                state.setCountryId(rs.getInt("c_id"));
                state.setName(rs.getString("name"));
                list.add(state);
            }
        }catch(SQLException e){
            e.printStackTrace();
        } 
        return list;
    }
    
    
    public List<City> getCityByStateId(int stateId){
        List<City> list = new ArrayList<>();
        try{
            query = "select * from city where s_id=?";
            pst = this.con.prepareStatement(query);
            pst.setInt(1, stateId);
            rs = pst.executeQuery();
            while(rs.next()){
                City city = new City();
                city.setId(rs.getInt("id"));
                city.setCountryId(rs.getInt("c_id"));
                city.setStateId(rs.getInt("s_id"));
                city.setName(rs.getString("name"));
                list.add(city);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }
}

Step-5:

Until this, we have done about retrieving the data. I mean, we just wrote the query to get the data. But didn’t send the request to provide the data. In this section, we will do that. Using HTTP Get or Post method we will ask for our data required data. So you already understand what I mean. Now we will create a servlet and name it CountryStateservlet.java and then send a get request to the database using the DAO method. After fetching the data, we will convert the data to a JSON array object and then respond in text/html format. For this servlet, the source code is below, you can copy and paste it. For more explanation, please follow the video tutorial in Youtube

package cn.techtutorial.dependentdropdownlist;

import com.google.gson.Gson;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class GetCountryStateservlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try (PrintWriter out = response.getWriter()) {

            CountryStateDao csd = new CountryStateDao(DBCon.getCon());
            
            String op = request.getParameter("operation");
            
            if (op.equals("country")) {
                List<Country> clist = csd.getAllCountry();
                Gson json = new Gson();
                String countryList = json.toJson(clist);
                response.setContentType("text/html");
                response.getWriter().write(countryList);
            }

            if (op.equals("state")) {
                int id = Integer.parseInt(request.getParameter("id"));
                List<State> slist = csd.getStateByCountryId(id);
                Gson json = new Gson();
                String countryList = json.toJson(slist);
                response.setContentType("text/html");
                response.getWriter().write(countryList);
            }

            if (op.equals("city")) {
                int id = Integer.parseInt(request.getParameter("id"));
                List<City> citylist = csd.getCityByStateId(id);
                Gson json = new Gson();
                String countryList = json.toJson(citylist);
                response.setContentType("text/html");
                response.getWriter().write(countryList);
            }
        }
    }
}

Step:6

We have done 90% of our project. Now it’s time to design our UI section to show our dropdown list. For designing the select form, I will use Materialize CSS. Also, to use AJAX we need to insert jQuery CDN. Right-click your webapp folder and create a new index.jsp page. Using AJAX, send an HTTP GET request to the GetCountryStateServlet. After getting the JSON data to use for each loop to set that value to the selected drop-down list.

For form design and AJAX query, the source code is down below. 

<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Responsive Card Design</title>
    <link rel="stylesheet" href="style.css" />
  </head>
  <body>
    <section class="main-section">
      <span>Our Courses</span>
      <div class="container">
        <div class="item-container">
          <div class="item">
            <img src="image/item1.jpg" alt="item1" srcset="" />
            <div class="info">
              <h2>Body Building</h2>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
            </div>
            <div class="over-flow">
              <h1>Body Building</h1>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
              <div>
                <p>
                  Lorem ipsum, dolor sit amet consectetur adipisicing elit.
                  Distinctio a, iste voluptatum dolorum perferendis non?
                </p>
              </div>
              <a href="#" class="btn">Learn More</a>
            </div>
          </div>
        </div>
        <div class="item-container">
          <div class="item">
            <img src="image/item2.jpg" alt="item2" srcset="" />
            <div class="info">
              <h2>Body Building</h2>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
            </div>
            <div class="over-flow">
              <h1>Body Building</h1>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
              <div>
                <p>
                  Lorem ipsum, dolor sit amet consectetur adipisicing elit.
                  Distinctio a, iste voluptatum dolorum perferendis non?
                </p>
              </div>
              <a href="#" class="btn">Learn More</a>
            </div>
          </div>
        </div>
        <div class="item-container">
          <div class="item">
            <img src="image/item3.jpg" alt="item3" srcset="" />
            <div class="info">
              <h2>Body Building</h2>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
            </div>
            <div class="over-flow">
              <h1>Body Building</h1>
              <table>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
                <tr>
                  <td>Mon, Tues</td>
                  <td>8:30, 9:30</td>
                </tr>
              </table>
              <div>
                <p>
                  Lorem ipsum, dolor sit amet consectetur adipisicing elit.
                  Distinctio a, iste voluptatum dolorum perferendis non?
                </p>
              </div>
              <a href="#" class="btn">Learn More</a>
            </div>
          </div>
        </div>
      </div>
    </section>
    <script type="text/javascript">
      $(document).ready(function () {
        $.ajax({
          url: "GetCountryStateservlet",
          method: "GET",
          data: {
            operation: "country",
          },
          success: function (data, textStatus, jqXHR) {
            console.log(data);
            let obj = $.parseJSON(data);
            $.each(obj, function (key, value) {
              $("#country").append(
                '<option value="' + value.id + '">' + value.name + "</option>"
              );
            });
            $("select").formSelect();
          },
          error: function (jqXHR, textStatus, errorThrown) {
            $("#country").append("<option>Country Unavailable</option>");
          },
          cache: false,
        });

        $("#country").change(function () {
          $("#state").find("option").remove();
          $("#state").append("<option>Select State</option>");
          $("#city").find("option").remove();
          $("#city").append("<option>Select City</option>");

          let cid = $("#country").val();
          let data = {
            operation: "state",
            id: cid,
          };

          $.ajax({
            url: "GetCountryStateservlet",
            method: "GET",
            data: data,
            success: function (data, textStatus, jqXHR) {
              console.log(data);
              let obj = $.parseJSON(data);
              $.each(obj, function (key, value) {
                $("#state").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
              $("select").formSelect();
            },
            error: function (jqXHR, textStatus, errorThrown) {
              $("#state").append("<option>State Unavailable</option>");
            },
            cache: false,
          });
        });

        $("#state").change(function () {
          $("#city").find("option").remove();
          $("#city").append("<option>Select City</option>");

          let sid = $("#state").val();
          let data = {
            operation: "city",
            id: sid,
          };

          $.ajax({
            url: "GetCountryStateservlet",
            method: "GET",
            data: data,
            success: function (data, textStatus, jqXHR) {
              console.log(data);
              let obj = $.parseJSON(data);
              $.each(obj, function (key, value) {
                $("#city").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
              $("select").formSelect();
            },
            error: function (jqXHR, textStatus, errorThrown) {
              $("#city").append("<option>City Unavailable</option>");
            },
            cache: false,
          });
        });
      });
    </script>
  </body>
</html>

Finally, it’s complete. I hope you also did well without getting any errors. For more understanding please check our YouTube video tutorial dynamic dependent select option using jsp servlet mysql and jquery Also please like this tutorial and subscribe to my YouTube Channel and if there is an error please feel free to comment or contact me at Instagram @techtutorial.cn