Blog Archive

Android spinner tutorial using JSON data from MySQL and Java Sevlet

Spinner is a view that displays one child at a time and lets the user pick among them. The items in the Spinner come from the Adapter associated with this view. This tutorial will cover the following topics ...

  • Connection to MySQL database
  • Creating JSON objects from Java objects using Google Gson
  • Java Servlet taking HTTP request and sending response in JSON format
  • Android application making a HTTP post request using AsyncTask
  • Parse JSON response and create an array for spinner and ArrayList to keep the Java Objects
  • Default the spinner to a specific entry 
  • Display addition Information based on spinner selection

Before we start please download the Google Gson jar, A Java library to convert JSON to Java objects and vice-versa. Add jar to your Android project Java Build Path. Here is the link
http://code.google.com/p/google-gson/downloads/list

Before we step into the Android programming let's create the WEB Service using Java Servlet and MySQL database. The Servlet sends JSON data of Countries and some addition information about the country along with it.

Android spinner example using JSON data from MySQL and Java Servlet

Sample JSON response from the WEB Service

{"success":true,
    "countryList":[
{"code":"ABW","name":"Aruba","continent":"North America","region":"Caribbean","lifeExpectancy":78.4,"gnp":828.0,"surfaceArea":193.0,"population":103000},
{"code":"AFG","name":"Afghanistan","continent":"Asia","region":"Southern and Central Asia","lifeExpectancy":45.9,"gnp":5976.0,"surfaceArea":652090.0,"population":22720000},
{"code":"AGO","name":"Angola","continent":"Africa","region":"Central Africa","lifeExpectancy":38.3,"gnp":6648.0,"surfaceArea":1246700.0,"population":12878000},
{"code":"AIA","name":"Anguilla","continent":"North America","region":"Caribbean","lifeExpectancy":76.1,"gnp":63.2,"surfaceArea":96.0,"population":8000},
{"code":"ALB","name":"Albania","continent":"Europe","region":"Southern Europe","lifeExpectancy":71.6,"gnp":3205.0,"surfaceArea":28748.0,"population":3401200},
{"code":"AND","name":"Andorra","continent":"Europe","region":"Southern Europe","lifeExpectancy":83.5,"gnp":1630.0,"surfaceArea":468.0,"population":78000},
......
......

Source for MySQL data connection resource context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">
<Resource auth="Container"
name="jdbc/mysql"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/world"
username="root"
password="mysql"
maxIdle="10"
maxActive="200"
maxWait="5"
removeAbandoned="true"
removeAbandonedTimeout="1200"
/>
</Context>

Source for Web application config file web.xml

<?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_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>CountryWebService</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</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>
  <servlet>
    <description>Country Servlet</description>
    <display-name>CountryServlet</display-name>
    <servlet-name>CountryServlet</servlet-name>
    <servlet-class>com.as400samplecode.CountryServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>CountryServlet</servlet-name>
    <url-pattern>/CountryServlet</url-pattern>
  </servlet-mapping>
  <resource-ref>
    <description>MySQL Datasource</description>
    <res-ref-name>jdbc/mysql</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>
</web-app>

Source for Country object Java bean Country.java

package com.as400samplecode.util;

public class Country {
   
    String code = null;
    String name = null;
    String continent = null;
    String region = null;
    Double lifeExpectancy = null;
    Double gnp = null;
    Double surfaceArea = null;
    int population = 0;
   
    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 String getContinent() {
        return continent;
    }
    public void setContinent(String continent) {
        this.continent = continent;
    }
    public String getRegion() {
        return region;
    }
    public void setRegion(String region) {
        this.region = region;
    }
    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;
    }
    public Double getSurfaceArea() {
        return surfaceArea;
    }
    public void setSurfaceArea(Double surfaceArea) {
        this.surfaceArea = surfaceArea;
    }
    public int getPopulation() {
        return population;
    }
    public void setPopulation(int population) {
        this.population = population;
    }
   
}    

Source for WEB Service utility program CountryInformation.java

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 {           

    Connection conn = null;            
    PreparedStatement stmt = null;     
    String sql = null;
   
    public ArrayList<Country> getList() { 

         
        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"; 
           
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                Country country = new Country(); 
                country.setCode(rs.getString("code").trim());
                country.setName(rs.getString("name").trim());
                country.setContinent(rs.getString("continent").trim());
                country.setRegion(rs.getString("region").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()));
                country.setSurfaceArea(rs.getString("surfaceArea") == null ? new Double(0)  : Double.parseDouble(rs.getString("surfaceArea").trim()));
                country.setPopulation(rs.getString("population") == null ? 0 : Integer.parseInt(rs.getString("population").trim()));
                countryList.add(country);
            }                                                                         

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


            conn.close();                                                             
            conn = null;                                                   

        }                                                               
        catch(Exception e){System.out.println(e);}                      

        finally {                                                       
            /*                                                             
             * close any jdbc instances here that weren't                  
             * explicitly closed during normal code path, so               
             * that we don't 'leak' resources...                           
             */                                                            

            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;

    }  

}   

Source for Java Servlet sending JSON data back CountryServlet.java

package com.as400samplecode;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

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

import com.as400samplecode.util.Country;
import com.as400samplecode.util.CountryInformation;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;

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

    public CountryServlet() {
        super();
    }

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

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


        PrintWriter out = response.getWriter();
        response.setContentType("text/html");
        response.setHeader("Cache-control", "no-cache, no-store");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "-1");
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "GET,POST");
        response.setHeader("Access-Control-Allow-Headers", "Content-Type");
        response.setHeader("Access-Control-Max-Age", "86400");

        //get list of countries
        CountryInformation countryInformation = new CountryInformation();
        ArrayList<Country> countryList = countryInformation.getList();

        Gson gson = new Gson();
        JsonArray arrayObj=new JsonArray();
        for(int i=0;i<countryList.size();i++){

            Country country = countryList.get(i);
            JsonElement countryObj = gson.toJsonTree(country);   
            arrayObj.add(countryObj);
        }

        //create a new JSON object
        JsonObject myObj = new JsonObject();
        //add property as success
        myObj.addProperty("success", true);
        //add the countryList object
        myObj.add("countryList", arrayObj);
        //convert the JSON to string and send back
        out.println(myObj.toString());

        out.close();
    }
}

Click here for next Chapter