• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to use ajax to populate data from database to combobox of html

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello , I would like to to know how to get Data from database to combobox like we have example of country-state-district which automatically populates combo box on onchange event of combo box . Thnks
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Firstly, it's not a "combo box". Please read HtmlHasNoComboBox. Correct terminology is important.

Secondly, there was a topic on this subject just the other say. Please SearchFirst.

Short version: use Ajax on the change event of the first dropdown to fetch the new options to load into the second (and third, and fourth...). jQuery makes it almost trivial.
 
Bartender
Posts: 2856
10
Firefox Browser Fedora Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have moved this to a more appropriate forum.
 
Amit Ghorpade
Bartender
Posts: 2856
10
Firefox Browser Fedora Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In addition ot what Bear said above, here is an example of AJAX query.
 
TahirAhmad Dar
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is how i implemented it .
first i created two tables in database named as country and state
1)create table country(countryid number(6),countryname varchar2(30));
2)create table state(stateid number(6),statename varchar2(30),countryid number(6));
then i wrote simple jsp code to retrieve data from database which populates data of country table and then i called javascript function showState which gets data from getState.jsp... see how
getcountry.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Populate Using Ajax</title>
<script>
function showState(str){
var xmlhttp;

if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("state").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getstate.jsp?q="+str,true);
xmlhttp.send();
}
</script>


</head>
<body>
Country : <select onchange="showState(this.value)">
<%
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
try {

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
}
catch (SQLException e) {
e.printStackTrace();
return;
}

PreparedStatement stmt=null;
stmt=connection.prepareStatement("select * from country");
ResultSet rs=null;
rs= stmt.executeQuery();

while(rs.next()){
%>

<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>
<div id="state">
State :
<select>
<option>Select State</option>
</select>
</div>
</body>
</html>
see getstate.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<%!int i;%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>State Page</title>
</head>
<body>
State :
<select>
<%
String str=request.getParameter("q");

i=Integer.parseInt(str);
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
try {

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
}
catch (SQLException e) {
e.printStackTrace();
return;
}

PreparedStatement stmt=null;
stmt=connection.prepareStatement("select * from state where countryid='"+i+"'");
ResultSet rs=null;
rs= stmt.executeQuery();

while(rs.next()){
%>
<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>

</body>
</html>
This worked for me i hope it will work for all those who are looking to retrieve data using ajax in jsp.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Wow, lots of poor practices were used there. Java code inside the JSP. Doing JDBC in the view. Not using a connection pool.

I'd spend some time learning how to write modern JSP and servlet web apps using good practices such as:
  • USing the JSTL and EL in JSP pages in place of obsolete Java scriptlets.
  • Properly structuring the web app such that the view, controllers and model are properly separated.
  • Use a connection pool.
  • Use jQuery or another library to do Ajax.


  •  
    TahirAhmad Dar
    Greenhorn
    Posts: 12
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Well soon we will be doing it using JQuery hopefully this was just a way how we can implement it anyways ... sometimes output matters more then following best practices and while learning things we should focus on concepts
    thanks for pointing out itz poor practice , hopefully we will be using best practice soon , learning never ends ...........
     
    Sheriff
    Posts: 17644
    300
    Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    TahirAhmad Dar wrote:sometimes output matters more then following best practices and while learning things we should focus on concepts



    This is a cop out and a contradiction. While learning concepts, you need to adhere to good practices as much as you can. What will most likely happen here is that you will not go back to this for a while then move on to some other project, leaving this mess for someone else to try to understand and clean up. This attitude is totally irresponsible and unprofessional, IMO, and it's what contributes to a lot of pain, suffering, and loss of time and money in our profession. It's your mess, clean it up.
     
    TahirAhmad Dar
    Greenhorn
    Posts: 12
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Ya Better is to use 3-tier MVC approach . thanks anyways
     
    TahirAhmad Dar
    Greenhorn
    Posts: 12
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    This is how we can do with jQuery,json,gson and using MVC Architecture in JavaEE
    1)create table in oracle as
    create table country(countryid number(10),countryname varchar2(50));
    create table state(stateid number(10),statename varchar2(50),countryid number(10));
    create table city(cityid number(10),cityname varchar2(50),stateid number(10));
    2)create view.jsp in webcontents in eclipse juno
    Write code in jsp as :
    <%@ page pageEncoding="UTF-8" %>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <!DOCTYPE html>
    <html lang="en">
    <head>
    <title></title>
    <script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
    <script type="text/javascript" >
    function getCountry(){

    $.getJSON('country.do', function(data) {

    ddl2=$("#country");

    if (data) {
    $.each(data, function(key, value) {

    ddl2.append($('<option/>').val(key).text(value));
    });
    } else {
    ddl2.append($('<option/>').text('Select' + ddl));
    }
    });

    }






    $(document).ready(function() {
    getCountry();
    getTechnology();
    $("#country").change(function() {

    fillOptions('country');
    });
    $("#state").change(function() {

    fillOptions('state');
    });
    });

    function fillOptions(dropdownId) {

    // $("#test1").text($('#'+dropdownId).val());
    //$("#ddi").text(dropdownId);

    var dropdown = $('#' + dropdownId);
    var ddl2;
    if(dropdownId=="country"){
    ddl="state";
    ddl2=$("#state");


    //$("#dd").text(dropdown);

    $.getJSON('state.do?type=' + dropdownId + '&value=' + $(dropdown).val(), function(data) {

    $('option', ddl2).remove(); // Clean old options first.
    if (data) {
    $.each(data, function(key, value) {
    // $("#ddi").text(value);
    ddl2.append($('<option/>').val(key).text(value));
    });
    } else {
    ddl2.append($('<option/>').text('Please select' + ddl));
    }
    });
    }
    else{
    ddl="city";
    ddl2=$("#city");
    $.getJSON('city.do?type=' + dropdownId + '&value=' + $(dropdown).val(), function(data) {

    $('option', ddl2).remove(); // Clean old options first.
    if (data) {
    $.each(data, function(key, value) {
    // $("#ddi").text(value);
    ddl2.append($('<option/>').val(key).text(value));
    });
    } else {
    ddl2.append($('<option/>').text('Please select' + ddl));
    }
    });
    }
    }
    </script>
    </head>
    <body>
    <form>
    <select id="country" name="country">
    <option>Select Country</option>
    </select>
    <select id="state" name="state">
    <option>Select state</option>
    </select>
    <select id="city" name="city">
    <option>Select city</option>
    </select>




    </form>
    </body>
    </html>

    3)create database connection class at java resources folder in eclipse. Name it package as
    Com.json.database (you can name it anything as it is just name of package)
    Then create a class named as DatabaseConnection in com.json.database package created above
    And write the following code:
    package com.json.database;
    import java.sql.*;

    public class DatabaseConnection {
    public static Connection connection=null;
    static{
    try{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","java","java");
    }catch(Exception e){
    e.printStackTrace();
    }
    }
    public static DatabaseConnection getConnection(){
    DatabaseConnection db=null;
    if(db==null){
    db=new DatabaseConnection();
    }
    return db;
    }

    }


    4) create servlet at java resources folder in eclipse. Name its package as
    Com.json.jsoncontroller (you can name it anything as it is just name of package)
    Then create a class named as JsonController in com.json.jsoncontroller package created above
    And write the following code:
    package com.json.controller;

    import java.io.IOException;
    import com.google.gson.Gson;


    import java.util.HashMap;

    import java.util.Map;
    import com.json.service.*;


    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    @WebServlet("*.do")

    public class JsonController extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    String uri = request.getRequestURI();

    JsonService service=new JsonService();
    if(uri.endsWith("country.do")){
    Map<Integer ,String> countries=new HashMap<>();
    countries=service.getCountry();
    String json = new Gson().toJson(countries); // Convert Java object to JSON string.

    response.setContentType("application/json"); // Inform client that you're returning JSON.
    response.setCharacterEncoding("UTF-8"); // Important if you want world domination.
    response.getWriter().write(json);
    }

    if(uri.endsWith("state.do")){
    int countryId=Integer.parseInt(request.getParameter("value"));
    Map<Integer,String> states=new HashMap<>();
    states=service.getState(countryId);



    String json = new Gson().toJson(states); // Convert Java object to JSON string.

    response.setContentType("application/json"); // Inform client that you're returning JSON.
    response.setCharacterEncoding("UTF-8"); // Important if you want world domination.
    response.getWriter().write(json); // Write JSON string to response.

    }
    if(uri.endsWith("city.do")){
    int stateId=Integer.parseInt(request.getParameter("value"));
    Map<Integer,String> states=new HashMap<>();
    states=service.getCity(stateId);



    String json = new Gson().toJson(states); // Convert Java object to JSON string.

    response.setContentType("application/json"); // Inform client that you're returning JSON.
    response.setCharacterEncoding("UTF-8"); // Important if you want world domination.
    response.getWriter().write(json); // Write JSON string to response.
    ;
    }


    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }

    }

    5)create package con.json.service in Java resources – >src folder in eclipse then create class JsonService in same package then write the following code:
    package com.json.service;

    import com.json.dao.JsonDao;

    import java.util.*;
    public class JsonService {
    private JsonDao dao;

    public JsonService() {
    dao = new JsonDao();
    }

    public Map<Integer, String> getCountry()
    {
    return dao.getCountry();
    }

    public Map<Integer, String> getState(int countryId) {

    return dao.getState(countryId);
    }

    public Map<Integer, String> getCity(int stateId) {
    return dao.getCity(stateId);
    }


    }
    }

    6) create package con.json.dao in Java resources – >src folder in eclipse then create class JsonDao in same package then write the following code:
    package com.json.dao;

    import java.util.HashMap;
    import java.util.Map;
    import java.sql.*;
    import com.json.database.DatabaseConnection;


    public class JsonDao {

    public Map<Integer, String> getCountry() {

    Map<Integer ,String> countries=new HashMap<>();
    try{
    PreparedStatement ps=DatabaseConnection.connection.prepareStatement("select * from country");
    ResultSet rs=ps.executeQuery();
    while(rs.next()){
    countries.put(rs.getInt(1),rs.getString(2));

    }
    }catch(Exception e){
    e.printStackTrace();

    }
    return countries;
    }

    public Map<Integer, String> getState(int countryId) {
    // TODO Auto-generated method stub
    Map<Integer ,String> states=new HashMap<>();
    try{
    PreparedStatement ps=DatabaseConnection.connection.prepareStatement("select * from state where countryid="+countryId);
    ResultSet rs=ps.executeQuery();
    while(rs.next()){
    states.put(rs.getInt(1),rs.getString(2));

    }
    }catch(Exception e){
    e.printStackTrace();

    }
    return states;
    }

    public Map<Integer, String> getCity(int stateId) {
    Map<Integer ,String> states=new HashMap<>();
    try{
    PreparedStatement ps=DatabaseConnection.connection.prepareStatement("select * from city where stateid="+stateId);
    ResultSet rs=ps.executeQuery();
    while(rs.next()){
    states.put(rs.getInt(1),rs.getString(2));

    }
    }catch(Exception e){
    e.printStackTrace();

    }
    return states;
    }

    public Map<Integer, String> getTechnology() {
    Map<Integer ,String> tech=new HashMap<>();
    try{
    PreparedStatement ps=DatabaseConnection.connection.prepareStatement("select * from technology");
    ResultSet rs=ps.executeQuery();
    while(rs.next()){
    tech.put(rs.getInt(1),rs.getString(2));

    }
    }catch(Exception e){
    e.printStackTrace();

    }
    return tech;
    }

    }

    You are done with Layered approach which has MVC pattern ………. hope this helps who look for same as I was looking a week before….. Thanks
    you can also confirure web.xml as :
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
    <display-name>JSON EXAMPLE</display-name>
    <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>view.jsp</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
    </welcome-file-list>
    </web-app>
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic