Blog Archive

jQuery accordion nested list in JSP using MySQL data

In this example we create an nested layout starting with Continents, then Regions within the Continents and and finally the countries that belong to that Continent/Region. We take the help of the jQuery UI get this done very easily if we conform to a standard layout. The accordion container markup needs pairs of headers and content panels. If you use a different element for the header, specify the header-option with an appropriate selector, eg. header: 'a.header'. The content element must be always next to its header.


Nested accordion layout in JSP using jQuery

HTML source code with jQuery accordion UI

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1" import="java.util.*, com.as400samplecode.util.*"%>
<!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">
<meta name="robots" content="noindex,nofollow" />
<title>JSP nested accordion Layout using jQuery UI</title>
<link rel="stylesheet" href="/resources/themes/master.css" type="text/css" />
<style>

</style>
<link
 href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.0/themes/base/jquery-ui.css"
 rel="stylesheet" type="text/css" />
<script
 src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js"
 type="text/javascript"></script>
<script
 src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.0/jquery-ui.min.js"
 type="text/javascript"></script>
<script src="/resources/scripts/mysamplecode.js" type="text/javascript"></script>

<script type="text/javascript">
$(document).ready(function() {

 var icons = {
            header: "ui-icon-circle-arrow-e",
            activeHeader: "ui-icon-circle-arrow-s"
        };
 
 $(function() {
        $( ".accordionLayout" ).accordion({
         icons: icons,
         heightStyle: "content"
        });
    });
 
 
});
</script>
</head>
<body>

<% 
 
 CountryInformation countryInformation = new CountryInformation();
 ArrayList<Continent> continentList = countryInformation.getContinents();
%>
    
 <div id="allContent">
 <%@include file="/header.jsp"%>
 <div class="accordionLayout">
  
  <% 
   for (Continent continent : continentList){
  %>
   <h2><%= continent.getName() %></h2>
   <div class="accordionLayout">
   
   <% 
    ArrayList<Region> regionList = continent.getRegionList();
    for (Region region : regionList){
   %>
    <h3><%= region.getName() %></h3>
    <div class="accordionLayout">
     
    <% 
     ArrayList<Country> countryList = region.getCountryList();
     for (Country country : countryList){
    %>
     <h3><%= country.getName() %></h3>
     <div>
      <p>Code: <%= country.getCode() %></p>
      <p>Life Expectancy: <%= country.getLifeExpectancy() %></p>
      <p>GNP: <%= country.getGnp() %></p>
     </div>
    <%
     }
    %>
    </div>
   <%
    }
   %>
   </div>
  <%
   }
  %>

 </div>
 <%@include file="/footer.jsp"%>
 </div>
</body>
</html>

Java Object for Continent

package com.as400samplecode.util;

import java.util.ArrayList;

public class Continent {
 
 String name = null;
 ArrayList<Region> regionList = null;
 
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public ArrayList<Region> getRegionList() {
  return regionList;
 }
 public void setRegionList(ArrayList<Region> regionList) {
  this.regionList = regionList;
 }
 
} 

Java Object for Region

package com.as400samplecode.util;

import java.util.ArrayList;

public class Region {
 
 String name = null;
 ArrayList<Country> countryList = null;
 
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public ArrayList<Country> getCountryList() {
  return countryList;
 }
 public void setCountryList(ArrayList<Country> countryList) {
  this.countryList = countryList;
 }
 
} 

Java Object for Country

package com.as400samplecode.util;

public class Country {
 
 String code = null;
 String name = null;
 Double lifeExpectancy = null;
 Double gnp = null;
 
 public String getCode() {
  return code;
 }
 public void setCode(String code) {
  this.code = code;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 
 public Double getLifeExpectancy() {
  return lifeExpectancy;
 }
 public void setLifeExpectancy(Double lifeExpectancy) {
  this.lifeExpectancy = lifeExpectancy;
 }
 public Double getGnp() {
  return gnp;
 }
 public void setGnp(Double gnp) {
  this.gnp = gnp;
 }
 
} 

Java Utility class for fetching data from MySQL

package com.as400samplecode.util;

import java.sql.Connection;          
import java.sql.ResultSet;           
import java.sql.SQLException;        
import java.sql.PreparedStatement;   
import java.util.ArrayList;

import javax.naming.Context;         
import javax.naming.InitialContext;  
import javax.sql.DataSource;         

public class CountryInformation {            

 
 public ArrayList<Continent> getContinents() {  

  Connection conn = null;             
  PreparedStatement stmt = null;      
  String sql = null;
  
  ArrayList<Continent> continentList = new ArrayList<Continent>();    

  try {       
   Context ctx = (Context) new InitialContext().lookup("java:comp/env");
   conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

   sql = "Select distinct(continent) as c from country order by concat(c)"; 
   stmt = conn.prepareStatement(sql);
   ResultSet rs = stmt.executeQuery();  
   
   while(rs.next()){ 
    Continent continent = new Continent();
    String name = rs.getString(1).trim();
    continent.setName(name);
    continent.setRegionList(getRegions(name));
    continentList.add(continent);
   }                                                                          

   rs.close();                                                                
   stmt.close();                                                              
   stmt = null;                                                               


   conn.close();                                                              
   conn = null;                                                    

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

  finally {                                                        
   
   if (stmt != null) {                                             
    try {                                                          
     stmt.close();                                                 
    } catch (SQLException sqlex) {                                 
     // ignore -- as we can't do anything about it here            
    }                                                              

    stmt = null;                                             
   }                                                         

   if (conn != null) {                                       
    try {                                                    
     conn.close();                                           
    } catch (SQLException sqlex) {                           
     // ignore -- as we can't do anything about it here      
    }                                                        

    conn = null;                                             
   }                                                         
  }               

  return continentList;

 }   

 public ArrayList<Region> getRegions(String continent) {  

  Connection conn = null;             
  PreparedStatement stmt = null;      
  String sql = null;
  
  ArrayList<Region> regionList = new ArrayList<Region>();    

  try {       
   Context ctx = (Context) new InitialContext().lookup("java:comp/env");
   conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

   sql = "Select distinct(region) as r from country where continent = ? order by r"; 
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, continent.trim());
   ResultSet rs = stmt.executeQuery();  
   
   while(rs.next()){ 
    Region region = new Region();
    String name = rs.getString(1).trim();
    region.setName(name);
    region.setCountryList(getCountries(continent, name));
    regionList.add(region);
   }                                                                          

   rs.close();                                                                
   stmt.close();                                                              
   stmt = null;                                                               


   conn.close();                                                              
   conn = null;                                                    

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

  finally {                                                        
   if (stmt != null) {                                             
    try {                                                          
     stmt.close();                                                 
    } catch (SQLException sqlex) {                                 
     // ignore -- as we can't do anything about it here            
    }                                                              

    stmt = null;                                             
   }                                                         

   if (conn != null) {                                       
    try {                                                    
     conn.close();                                           
    } catch (SQLException sqlex) {                           
     // ignore -- as we can't do anything about it here      
    }                                                        

    conn = null;                                             
   }                                                         
  }               

  return regionList;

 }   

 public ArrayList<Country> getCountries(String continent, String region) {  

  Connection conn = null;             
  PreparedStatement stmt = null;      
  String sql = null;
  
  ArrayList<Country> countryList = new ArrayList<Country>();    

  try {       
   Context ctx = (Context) new InitialContext().lookup("java:comp/env");
   conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

   sql = "Select * from country where continent = ? and region = ? order by name,code"; 
   stmt = conn.prepareStatement(sql);
   stmt.setString(1, continent.trim());
   stmt.setString(2, region.trim());
   ResultSet rs = stmt.executeQuery();  
   
   while(rs.next()){ 
    Country country = new Country();
    country.setCode(rs.getString("code").trim());
    country.setName(rs.getString("name").trim());
    country.setLifeExpectancy(rs.getString("lifeExpectancy") == null ? new Double(0) : Double.parseDouble(rs.getString("lifeExpectancy").trim()));
    country.setGnp(rs.getString("gnp") == null ? new Double(0)  : Double.parseDouble(rs.getString("gnp").trim()));
    countryList.add(country);
   }                                                                          

   rs.close();                                                                
   stmt.close();                                                              
   stmt = null;                                                               


   conn.close();                                                              
   conn = null;                                                    

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

  finally {                                                        
   
   if (stmt != null) {                                             
    try {                                                          
     stmt.close();                                                 
    } catch (SQLException sqlex) {                                 
     // ignore -- as we can't do anything about it here            
    }                                                              

    stmt = null;                                             
   }                                                         

   if (conn != null) {                                       
    try {                                                    
     conn.close();                                           
    } catch (SQLException sqlex) {                           
     // ignore -- as we can't do anything about it here      
    }                                                        

    conn = null;                                             
   }                                                         
  }               

  return countryList;

 }   


}   

References