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

Question

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?

You should take a look at this link: http://stackoverflow.com/questions/22550913/how-to-fetch-the-dropdown-values-from-database-and-display-in-jsp — NcDreamy, Jun 06 '14 at 04:40
@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? — AKIWEB, Jun 06 '14 at 04:42
@Santino'Sonny'Corleone, The solution isn't working for me correctly :( — AKIWEB, Jun 06 '14 at 13:49

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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.

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. – AKIWEB Jun 06 '14 at 04:44
@akiiddweeber, You can store data on `request` or `session` attribute. – Masudul Jun 06 '14 at 04:45
I see. As I am just a beginner in JSP. If possible can you provide an example for that as well? – AKIWEB Jun 06 '14 at 04:46
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? – AKIWEB Jun 06 '14 at 05:03
@akiiddweeber, Yes Servlet is .java file. Look at my link at bottom of my answer for details answer. – Masudul Jun 06 '14 at 05:05
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? – AKIWEB Jun 06 '14 at 05:30

Abhishek Mishra · Answer 2 · 2014-06-06T05:09:10.777

0

Writing java code is jsp is very bad habit ,use jquery and do all DB stuff in java code

$.ajax({
                type: "POST",
                url: "URL",
                data: "firstName=Aidy&lastName=F", // the data in form-encoded format, ie as it would appear on a querystring

                success: function (data) {
                   assign the return value which is in data to your hmtl 
                }
            });

edited Jun 06 '14 at 05:09

answered Jun 06 '14 at 04:54

Abhishek Mishra

611
4
11

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 Jun 06 '14 at 04:57
Let's say, it returns me back a list of all the user's from the java code. – AKIWEB Jun 06 '14 at 04:58
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 – Abhishek Mishra Jun 06 '14 at 05:11

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

2 Answers2

See also: