How to dynamically populate the drop down list in my JSP page from the database?

java jquery html ajax jsp

22,213

As you get data through Ajax call so you should populate data on Servlet.

   @WebServlet("/populate")
   public class PopulateData extends HttpServlet{

      public void doGet(....){
         Class.forName("org.postgresql.Driver");
         Connection con = DriverManager.getConnection(
            "jdbc:postgresql://localhost/test","root", "root!");

        Statement s = con.createStatement();
        String sql ="SELECT USERS FROM ACCOUNT";
        ResultSet rs = s.executeQuery(sql);

        List<String> list = new ArrayList<String>();

        while (rs.next()) {
          list.add(rs.getString("USERS"));
        }
       String json = new Gson().toJson(list);
       response.getWriter().write(json);
      }
  }

Now you can populate json data to test.jsp page through ajax call.

AKIWEB

Updated on July 07, 2022

Comments

AKIWEB almost 2 years
I am working with JSP and Ajax for the first time. I am trying to get one column data from database and populate it in my drop down list in my JSP page using Ajax call. I don't want to refresh the page so that is the reason, I am making aN Ajax call.

Here is my jsfiddle which has Process button and as soon as I click Process button, it will show an empty drop down list as of now. This is in my another test.jsp page.

I have a table as account and I need to make this select query from the jsp -
```
SELECT USERS FROM ACCOUNT;
```
As soon as I am clicking Process button, I need to execute above SQL query on my POSTGRESQL database using Ajax. And whatever users, I am getting back from the database, I need to populate those USERS in my drop down list as shown in my above jsfiddle.

Below is my JSP page (databasecall.jsp) in which I am making a call to my database to get all the USERS -
```
<%@page contentType="text/html; charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<%
    response.setContentType("application/json");

    try {
        // Step 1. Load the JDBC driver
        Class.forName("org.postgresql.Driver");

        // Step 2. Create a Connection object
        Connection con = DriverManager.getConnection(
                "jdbc:postgresql://localhost/test","root", "root!");

        Statement s = con.createStatement();

        String sql ="SELECT USERS FROM ACCOUNT";
        ResultSet rs = s.executeQuery(sql);

        while (rs.next()) {
            // what to do here?
        }
        rs.close();
        s.close();
        con.close();
    } catch (Exception e3) {
        e3.printStackTrace();
    }
%>
```
Problem Statement:-

Now my question is, how do I populate all the USERS data which I got from the database in my drop down list in the test.jsp page? Meaning, somehow I need to call this JSP on the Process button click and then pass all the users data which we got and then dynamically populate the drop down list?

Suppose if I am getting 10 USERS from the database, then the drop down list should have 10 users in it.

Is this possible to do?
- NcDreamy almost 10 years
  
  You should take a look at this link: stackoverflow.com/questions/22550913/…
- AKIWEB almost 10 years
  
  @ncdreamy Thanks for the link. It is close to what I am doing. But is there any way to pass the USERS data from databasecall.jsp page to test.jsp page and then iterate it and show the results?
- Santino 'Sonny' Corleone almost 10 years
  
  @akiiddweeber have u got ur answer?
- AKIWEB almost 10 years
  
  @Santino'Sonny'Corleone, The solution isn't working for me correctly :(
AKIWEB almost 10 years

Thanks Masud for the help. In my case, I am getting USERS data from databasecall.jsp page and my html code is in test.jsp page. Is there any way, I can pass the USERS data in some object to test.jsp page and then iterate it and populate the results in drop down list. In your example, it has to be in same jsp page, database call and the actual html as well I guess.
Masudul almost 10 years

@akiiddweeber, You can store data on request or session attribute.
AKIWEB almost 10 years

I see. As I am just a beginner in JSP. If possible can you provide an example for that as well?
AKIWEB almost 10 years

Thanks for your suggestion. I can for sure write the same code in Java instead of in JSP, let's say it is abc.java then how would I make this work for my current example?
AKIWEB almost 10 years

Let's say, it returns me back a list of all the user's from the java code.
AKIWEB almost 10 years

In the above example, your code is in .java right? let's say that database logic is in databasecall.java. Now how would I call this databasecall.java class from test.jsp through Ajax and populate it then?
Masudul almost 10 years

@akiiddweeber, Yes Servlet is .java file. Look at my link at bottom of my answer for details answer.
Abhishek Mishra almost 10 years

i mistype something in answer which now i corrected ,check out the data value which you get in response ,just loop out that and assign that value usging jquery to respective field in jsp
AKIWEB almost 10 years

I see. In my project, I already have one servlet which has doGet method and it is doing some other work. So can we have another servlet with doGet method as well?