Dynamic Dropdown From Database using Spring Boot
Last Updated :
16 Jan, 2023
The concept of dynamic dropdown (or dependent dropdown) is exciting and challenging to code. Dynamic dropdown means that the values in one dropdown list are dependent on the value selected in the previous dropdown list. A simple example would be three dropdown boxes displaying names of the district, taluk, and village where the values in taluk depend on the value selected in the district, and the values in the village depend on the value selected in the taluk dropdown. Dynamic Dropdown can be implemented using the following technologies:
- Any database can be used to load the details of the districts, taluks, and villages that are to be populated in the dropdown. In this case, we will be using PostgreSQL.
- Service class to connect to the database can be implemented using Java and Spring Boot.
- HTML, CSS, JavaScript, jQuery, and AJAX can be used to implement the dropdown lists.
The best approach for this tutorial would be to start with creating and populating the databases first, coding the Java service class next, and then proceeding to design and code the dropdown lists in the webpage. It would also be helpful to visualize the approximate output of this tutorial before proceeding further.
DISTRICT SELECTED
TALUK SELECTED
VILLAGE SELECTEDNow that the output has been visualized, it's time to narrow down the details for executing the objective of this tutorial. It is recommended to create the Spring Boot project separately and the web pages separately in another project.
Project 1 for Service Method:
The procedure to create a Spring Boot project is explained in the following tutorial: Spring Boot – Service Class Example for Displaying Response Codes and Custom Error Codes. The database part consists of one CREATE command for each table, some INSERT commands for each table. The create commands and the corresponding insert commands for the tables are listed below:
CREATE TABLE district (id int SERIAL PRIMARY KEY,name varchar(50),distcode varchar(4));
insert into district (name,distcode) values('Chennai','1');
insert into district (name,distcode) values('Coimbatore','2');
CREATE TABLE taluk (id int SERIAL PRIMARY KEY,name varchar(50),distcode varchar(4),talukcode varchar(4));
insert into taluk (name,distcode,talukcode) values('Avadi','1','12');
insert into taluk (name,distcode,talukcode) values('Sulur','2','3');
CREATE TABLE village (id int SERIAL PRIMARY KEY,name varchar(50),distcode varchar(4),talukcode varchar(4),villagecode varchar(4));
insert into village (name,distcode,talukcode,villagecode) values('Pothur','1','12','15');
insert into village (name,distcode,talukcode,villagecode) values('Arasur','2','10','9');
The images of the respective databases are shown below:
DISTRICT DATABASE
TALUK DATABASE
VILLAGE DATABASEDBController.java
Java
package com.springboot.springbootdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.websocket.server.PathParam;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
@RestController
@CrossOrigin
public class DBController {
PreparedStatement ps;
Connection con;
String sql;
@GetMapping("/dist")
@CrossOrigin
public String saylistDistrict() throws SQLException
{
PreparedStatement ps;
ResultSet myRs;
JSONArray districtlist = new JSONArray();
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/test?allowPublicKeyRetrieval=true",
"postgres", "password");
sql = "SELECT distcode, name FROM district";
ps = con.prepareStatement(sql);
myRs = ps.executeQuery();
while (myRs.next()) {
JSONObject jsonobj = new JSONObject();
jsonobj.put("districtcode",
myRs.getString("distcode")
.toString()
.trim());
jsonobj.put("districtname",
myRs.getString("name")
.toString()
.trim());
districtlist.add(jsonobj);
}
System.out.println("districtlist"
+ districtlist.size());
close(con, ps, myRs);
}
catch (Exception e) {
System.out.println("getservice Exception==>"
+ e);
}
return (districtlist.toString());
}
@RequestMapping(value = "/taluk",
method = RequestMethod.GET)
@ResponseBody
@CrossOrigin
public String
ListTaluk(@RequestParam String Discode)
throws ParseException
{
String districtcode = Discode;
JSONArray taluklist = new JSONArray();
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/test?allowPublicKeyRetrieval=true",
"postgres", "password");
sql = " select * from taluk where distcode=?";
ps = con.prepareStatement(sql);
ps.setString(1, districtcode);
ResultSet res = ps.executeQuery();
while (res.next()) {
JSONObject jsontaluk = new JSONObject();
jsontaluk.put("districtcode",
res.getString("distcode")
.toString()
.trim());
jsontaluk.put("talukcode",
res.getString("talukcode")
.toString()
.trim());
jsontaluk.put("talukname",
res.getString("name")
.toString()
.trim());
taluklist.add(jsontaluk);
}
System.out.println("taluklist"
+ taluklist.size());
close(con, ps, res);
}
catch (Exception e) {
System.out.println(
"getservice Edistid1xception==>" + e);
}
return (taluklist.toString());
}
@RequestMapping(value = "/village",
method = RequestMethod.GET)
@ResponseBody
@CrossOrigin
public String
Listvillage(@RequestParam String Discode,
@RequestParam String Talukcode)
throws ParseException
{
String districtcode = Discode;
String talukcode = Talukcode;
JSONArray villagelist = new JSONArray();
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/test?allowPublicKeyRetrieval=true",
"postgres", "password");
sql = "select * from village where distcode=? and talukcode=?";
ps = con.prepareStatement(sql);
ps.setString(1, districtcode);
ps.setString(2, talukcode);
ResultSet resl = ps.executeQuery();
while (resl.next()) {
JSONObject jsonvillage = new JSONObject();
jsonvillage.put("districtcode",
resl.getString("distcode")
.toString()
.trim());
jsonvillage.put("talukcode",
resl.getString("talukcode")
.toString()
.trim());
jsonvillage.put(
"villagecode",
resl.getString("villagecode")
.toString()
.trim());
jsonvillage.put("villagename",
resl.getString("name")
.toString()
.trim());
villagelist.add(jsonvillage);
}
System.out.println("villagelist"
+ villagelist.size());
close(con, ps, resl);
}
catch (Exception e) {
System.out.println("getservice Exception==>"
+ e);
}
return (villagelist.toString());
}
private static void close(Connection myConn,
Statement myStmt,
ResultSet myRs)
{
try {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
catch (Exception exc) {
exc.printStackTrace();
}
}
}
The explanation for the above code:
- The @RestController annotation is required to identify the Java service class and it is recommended to use the @CrossOrigin annotation only when authorizing the URL from which the request was sent.
- The @GetMapping("/dist") annotation is used before the saylistDistrict() function so that the function gets invoked whenever a URL containing "/dist" is called.
- The function saylistDistrict() retrieves data from the database, processes, and returns the data in JSON format which is explained in the following sub-points:
- The database connection is established and the respective select query is invoked to retrieve the district details along with their respective codes.
- The "SELECT distcode, name FROM district" query retrieves the name of the district along with the district code which is then stored in a ResultSet "myRs". The ResultSet is then iterated and the district data is stored in a JSON object "jsonobj".
- After each district is iterated, the resultant JSONObject is added into a main JSONArray "districtlist". The database connection is closed using the close(Connection myConn, Statement myStmt, ResultSet myRs) method where the Connection, ResultSet and Statement are closed.
Note: It's important to close the database connection each time after usage in a web application. Failing to do so might result in memory leakage, performance slowdown, connection shortage when a user requests for a database connection from the database server.
- The "return(districtlist.toString());" command converts the JSONArray into a String and then returns it to the entity from which the Java method is invoked.
- Now it's time to come to the ListTaluk() function. This function also uses the same annotations as saylistDistrict() function except for the additional annotation '@RequestMapping(value = "/taluk", method = RequestMethod.GET)'
- The @RequestMapping(value = "/taluk", method = RequestMethod.GET) annotation simplifies mapping of the URL parameters with the @RequestParam annotation.
- This method is invoked when a URL contains the value mentioned in the value parameter of the @RequestMapping annotation. The method parameter mentions the request method which is the GET method in this example. RequestMethod is an in-built Java class written for this purpose and it is used before the method name separated by a dot(.)
- The @RequestParam annotation reads the distid1 value from the URL and stores the value in the 'String Discode' variable. The value Discode is then stored into a String variable 'discode'.
- The subsequent actions and commands are similar to the previous method except for some slight variations along the way which is explained in the subpoints given below:
- The query to retrieve the taluk names along with respective district codes and taluk code is "select * from taluk where distcode=?" where ? denotes the value of the district code.
- ps.setString(1,districtcode) sets the value of the district code in the query.
- The query is executed and stored in the ResultSet with the command 'ResultSet res = ps.executeQuery();'.
- "jsontaluk" is the JSONObject used to store the taluk names in each iteration. As an example, "jsontaluk.put("districtcode", res.getString("distcode").toString().trim());" is the command that stores the district values in the JSONObject.
- Similarly, in the next three lines, taluk code and taluk name is also stored in the JSONObject and the object is stored in the JSONArray "taluklist"
- The JSONArray is the returned back after converting into a String using the "return(taluklist.toString());" command.
- All the annotations used in the Listvillage() method is the same as the annotations used in the ListTaluk() annotation.
- Similar to the modus operandi in Listvillage() method, village name, district code, taluk code, and village code is retrieved using the query. The retrieved data is stored into a JSONArray which is returned in String format at the end of the method.
Project 2 for webpage and front-end
dropdown.jsp
HTML
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<script type="text/javascript" src="jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="Ajaxcall.js"></script>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<table border="1" cellpadding="10px" cellspacing="5px">
<tr>
<td>District: </td>
<td>
<select id="districtlist" name="districtlist" required>
<option disabled selected>Select District</option>
</select>
</td>
</tr>
<tr>
<td>Taluk: </td>
<td>
<select id="taluklist" name="taluklist" required>
<option disabled>Select Taluk</option>
</select>
</td>
</tr>
<tr>
<td>Village: </td>
<td>
<select id="villagelist" name="villagelist" required>
<option disabled>Select Village</option>
</select>
</td>
</tr>
</table>
</body>
</html>
The plugin jquery-3.6.0.min.js needs to be imported into the HTML project and it works best when the plugin is deployed in the local project and imported. The webpage is plain only with a basic layout and not much CSS since the scope of this tutorial is only to explain dynamic dropdown based on the database. Now that the webpage layout for the dropdowns has been coded, it's time to write the AJAX calls. A JavaScript file called Ajaxcall.js is used to populate the dropdown lists. It is also the place where the URL linked to the Java service methods is called.
Ajaxcall.js
JavaScript
var dis;
var tal;
var vill;
$(document).ready(function () {
$.ajax({
type: "GET",
url: "http://localhost:8075/dist",
data: "json",
contentType: "application/json",
success: function (data) {
let obj = $.parseJSON(data);
$.each(obj, function (key, value) {
$('#districtlist').append('<option value="' + value.districtcode + '">' + value.districtcode + '--' + value.districtname + '</option>');
});
},
error: function (data) {
$('#districtlist').append('<option>District Unavailable</option>');
},
});
/*$('#districtlist').trigger("change");*/
$('#districtlist').change(function () {
$('#taluklist').find('option').remove();
$('#taluklist').append('<option>Select taluk</option>');
$('#villagelist').find('option').remove();
$('#villagelist').append('<option>Select village</option>');
var distid1 = $('#districtlist').val();
var inputValObj = {};
alert(distid1);
inputValObj.Discode = distid1;
var inputVal = JSON.stringify(inputValObj);
alert(inputVal);
var data = inputVal.toString();
alert(data);
$.ajax({
type: "GET",
url: "http://localhost:8075/taluk?Discode=" + distid1,
/*data: 1,*/
contentType: "application/json",
success: function (data) {
let obj = $.parseJSON(data);
$.each(obj, function (key, value) {
$('#taluklist').append('<option value="' + value.talukcode + '">' + value.talukcode + '--' + value.talukname + '</option>');
});
},
error: function (data) {
$('#taluklist').append('<option>Taluk Unavailable</option>');
},
});
});
$('#taluklist').change(function () {
$('#villagelist').find('option').remove();
$('#villagelist').append('<option>Select village</option>');
var distid1 = $('#districtlist').val();
var talukid = $('#taluklist').val();
alert(distid1);
alert(talukid);
var inputValObj = {};
inputValObj.Discode = distid1;
inputValObj.talucode = talukid;
var inputVal = JSON.stringify(inputValObj);
var data = inputVal.toString();
$.ajax({
type: "GET", //POST
url: "http://localhost:8075/village?Discode=" + distid1 + "&" + "Talukcode=" + talukid,
contentType: "application/json",
success: function (data) {
let obj = $.parseJSON(data);
$.each(obj, function (key, value) {
$('#villagelist').append('<option value="' + value.villagecode + '">' + value.villagecode + '--' + value.villagename + '</option>');
});
},
error: function (data) {
$('#villagelist').append('<option>village Unavailable</option>');
},
});
});
});
When the document is ready, the saylistDistrict() function is invoked in the first AJAX call using the "http://localhost:8075/dist" URL and the extracted data goes into the success function. Now, the data is parsed and stored into a variable 'obj', then iterated using $.each in jQuery. Each entry in the iteration is then appended into the district dropdown list using the '$('#districtlist').append('<option value="' + value.districtcode+ '">' + value.districtcode + '--' + value.districtname+ '</option>');' command.
Note: 'districtlist' is the ID of the district dropdown.
When the district dropdown is changed, the jQuery '$('#districtlist').change(function () {}); is invoked and the url "http://localhost:8075/taluk?Discode="+distid1 invokes the Java function ListTaluk(). The returned data is then populated in the taluk dropdown using the ' $('#taluklist').append('<option value="' + value.talukcode + '">' + value.talukcode + '--' + value.talukname+ '</option>');' command.
The jQuery '$('#taluklist').change(function () {});' is invoked when the taluk dropdown value is changed. The url "http://localhost:8075/village?Discode="+distid1+"&"+"Talukcode="+talukid invokes the Java function Listvillage(). The returned data is then populated in the 'villagelist' dropdown using the '$('#villagelist').append('<option value="' + value.villagecode + '">' + value.villagecode + '--' + value.villagename+ '</option>')' command.
Additionally, whenever a dropdown is modified, the other dropdown values dependent on the modified dropdown are removed and the "Select" placeholder is inserted. The dropdown values are removed using the .remove() function as demonstrated in the above examples and "Select" placeholder is inserted with commands in the following template "$('#taluklist').append('<option>Select taluk</option>');".
In any Spring Boot project, there will be a Java class with the @SpringBootApplication annotation which has to be run using right click and Run As _> Java application. The first project has to be run in this way. The second project must be run on the server using right click -Run as -> Run on the server. After the dropdown.jsp has been run using the following method, the output of this tutorial can be observed.
Similar Reads
Spring Boot - Annotations
Spring Boot Annotations are a form of metadata that provides data about a spring application. Spring Boot is built on the top of the spring and contains all the features of spring. And is becoming a favorite of developers these days because of its rapid production-ready environment which enables the
7 min read
Spring Boot - Architecture
Spring Boot is built on top of the core Spring framework. It simplifies and automates Spring-based application development by reducing the need for manual configuration. Spring Boot follows a layered architecture, where each layer interacts with other layers in a hierarchical order. The official Spr
3 min read
Spring Boot Actuator
Developing and managing an application are the two most important aspects of the applicationâs life cycle. It is very important to know what is going on beneath the application. Also, when we push the application into production, managing it gradually becomes critically important. Therefore, it is a
5 min read
Spring Boot - Introduction to RESTful Web Services
RESTful Web Services REST stands for REpresentational State Transfer. It was developed by Roy Thomas Fielding, one of the principal authors of the web protocol HTTP. Consequently, REST was an architectural approach designed to make the optimum use of the HTTP protocol. It uses the concepts and verbs
5 min read
How to create a basic application in Java Spring Boot
Spring Boot is the most popular Java framework that is used for developing RESTful web applications. In this article, we will see how to create a basic Spring Boot application.Spring Initializr is a web-based tool using which we can easily generate the structure of the Spring Boot project. It also p
3 min read
How to Create a REST API using Java Spring Boot?
Representational State Transfer (REST) is a software architectural style that defines a set of constraints for creating web services. RESTful web services allow systems to access and manipulate web resources through a uniform and predefined set of stateless operations. Unlike SOAP, which exposes its
4 min read
Easiest Way to Create REST API using Spring Boot
Spring Boot is a powerful framework that makes it easy to create RESTful APIs. Creating a REST API using Spring Boot is one of the fastest and simplest ways to develop scalable and production-ready web services. Spring Boot simplifies REST API development by providing built-in features such as autom
10 min read
Java Spring Boot Microservices Sample Project
Microservices are more popular nowadays. They can be written in any language. In this article, let us see Spring Boot Microservices. in this article let us see a base project "currency-exchange-sample-service" which has a business logic and which can be invoked in another project "currency-conversio
9 min read
Difference between Spring MVC and Spring Boot
1. Spring MVC : Spring is widely used for creating scalable applications. For web applications Spring provides Spring MVC framework which is a widely used module of spring which is used to create scalable web applications. Spring MVC framework enables the separation of modules namely Model View, Con
3 min read
Spring Boot - Spring JDBC vs Spring Data JDBC
Understanding the difference between Spring JDBC and Spring Data JDBC is important for choosing the right approach to interact with relational databases in Spring Boot applications. Both frameworks serve the same purpose but differ significantly in terms of abstraction, ease of use, and developer pr
5 min read