aspose file tools*
The moose likes HTML, CSS and JavaScript and the fly likes How to use ajax to populate data from database to combobox of html Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » HTML, CSS and JavaScript
Bookmark "How to use ajax to populate data from database to combobox of html" Watch "How to use ajax to populate data from database to combobox of html" New topic
Author

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

TahirAhmad Dar
Greenhorn

Joined: Oct 13, 2012
Posts: 12
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
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60058
    
  65

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Amit Ghorpade
Bartender

Joined: Jun 06, 2007
Posts: 2712
    
    5

I have moved this to a more appropriate forum.


SCJP, SCWCD.
|Asking Good Questions|
Amit Ghorpade
Bartender

Joined: Jun 06, 2007
Posts: 2712
    
    5

In addition ot what Bear said above, here is an example of AJAX query.
TahirAhmad Dar
Greenhorn

Joined: Oct 13, 2012
Posts: 12
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("jdbcracle:thin:@localhost:1521rcl","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("jdbcracle:thin:@localhost:1521rcl","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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60058
    
  65

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

    Joined: Oct 13, 2012
    Posts: 12
    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 ...........
    Junilu Lacar
    Bartender

    Joined: Feb 26, 2001
    Posts: 4419
        
        5

    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.


    Junilu - [How to Ask Questions] [How to Answer Questions]
    TahirAhmad Dar
    Greenhorn

    Joined: Oct 13, 2012
    Posts: 12
    Ya Better is to use 3-tier MVC approach . thanks anyways
    TahirAhmad Dar
    Greenhorn

    Joined: Oct 13, 2012
    Posts: 12
    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("jdbcracle: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>
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to use ajax to populate data from database to combobox of html
     
    Similar Threads
    render combo box
    State of requested data
    Combobox Script Required
    Data Caching Best Practices ?
    Keep the combobox entries after the form submission