Fetch data from table and store in hashmap

Question

I have a table with 5 columns (id |state_abbrevation | state_name | area_code | cities ). I have to store all the values as key value pair where key= state_abbrevation and Value = (area_code) cities.

Also state_abbrevation has duplicates. A sample data is shown below:

id | state_abbrevation |  state_name  | area_code |       cities        
----+-------------------+--------------+-----------+---------------------
  1 | AK                | Alaska       |       907 | Juneau
  2 | AL                | Alabama      |       205 | Birmingham
  3 | AL                | Alabama      |       251 | Mobile
  4 | AL                | Alabama      |       256 | Huntsville
  5 | AL                | Alabama      |       334 | Montgomery
  6 | AL                | Alabama      |       938 | Florence/Huntsville
  7 | AR                | Arkansas     |       479 | Ft. Smith
  8 | AR                | Arkansas     |       501 | Little Rock
  9 | AR                | Arkansas     |       870 | Jonesboro
 10 | AZ                | Arizona      |       480 | Scottsdale
 11 | AZ                | Arizona      |       520 | Tucson
 12 | AZ                | Arizona      |       602 | Phoenix
 13 | AZ                | Arizona      |       623 | Glendale
 14 | AZ                | Arizona      |       928 | Flagstaff
 15 | CA                | California   |       209 | Modesto
 16 | CA                | California   |       213 | Los Angeles
 17 | CA                | California   |       310 | West Hollywood
 18 | CA                | California   |       323 | Hollywood

What's the best solution to store in key value pair where key = AL & Value = Area code and City for all state_abbrevation= AL.

Example for Hashmap I want: KEY, VALUE AK, (907) Juneau AL, (205) Birmingham (251) Mobile (256) Huntsville (938) Florence .......and so on. Here's my working code using Hibernate:

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

public class HibernateCriteriaExamples {

public static void main(String[] args) {
    try {
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        Criteria criteria = session.createCriteria(State.class);
        //List<State> stateList = criteria.list();

        List<String> stateAbbrevationList = criteria.setProjection(Projections.distinct(Projections.property("stateAbbrevation"))).list();
        HashMap<String,List> cityAreacodeAndState = new HashMap<String,List>();

        for(int i=0; i<stateAbbrevationList.size();i++)
          {
            String abbrevation = stateAbbrevationList.get(i);
            //System.out.println(abbrevation);
            Criteria criteriaareaCodeWithCity = session.createCriteria(State.class);
            List<State> stateObject = criteriaareaCodeWithCity.add(Restrictions.eq("stateAbbrevation", abbrevation)).list();
            List<String> formattedAreaCodeAndCity =new ArrayList<String>();
            for(int j=0; j<stateObject.size();j++)
              {
                State state = (State)stateObject.get(j);
                int a = state.getAreacode();
                String b = state.getCities();
                String c = "("+a+")  "+b;
            //  System.out.println(c);
                formattedAreaCodeAndCity.add(c);
              }
            cityAreacodeAndState.put(abbrevation, formattedAreaCodeAndCity);

          }
        System.out.println("---------------PRINTING REQUIRED DATA------------------");

         for (HashMap.Entry<String,List> formattedAreaCodeAndCity1 : cityAreacodeAndState.entrySet())
          {
              System.out.println(formattedAreaCodeAndCity1.getKey() + "," + formattedAreaCodeAndCity1.getValue());
          }     

        tx.commit();
        sessionFactory.close();
    } catch (HibernateException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

}

Create a `HashMap`, iterate over the result, add the data to the hash map. What exactly is your problem? What is the error you get? What does your code look like? — , Jan 25 '17 at 11:46
Why dont you use VO and put that VO in Hashmap against Id or as per your requirement of "state_abbrevation". This way you will have all the values stored and can be easily retrieved. But if you want to store in a Map for a single Row wiht all the other columns as values, check this link [http://stackoverflow.com/questions/3093863/how-to-create-a-multimapk-v-from-a-mapk-collectionv] — murthy, Jan 25 '17 at 11:49
@a_horse_with_no_name is right, you just need to iterate and adding to the HashMap the values to the keys, and new keys when necessary (if the key doesn't exists) nothing more, nothing less — Amnor, Jan 25 '17 at 13:18

score 1 · Answer 1 · answered Jan 25 '17 at 11:50

You can use HashMap as well as List.

Your HashMap key will be state_abbrevation and your value (i.e., List) will contain area_code and cities.

HashMap<String, List<String>> data = new HashMap<>();
..
while(rs.next()) {
    List<String> temp = new ArrayList<>();
    temp.add(rs.getString("area_code"));
    temp.add(rs.getString("cities"));

    data.put(rs.getString("state_abbrevation"), temp);
}

to get the data related to state CA

data.get("CA").get(0) //   -> gives area_code related to state "CA"
data.get("CA").get(1) //   -> gives cities related to state "CA"

score 0 · Answer 2 · edited Feb 28 '17 at 22:03

As was mentioned in comments, you can use value object to keep data for state:

public Map<String, State> getStatesMap() {
    ... execute query and get resultSet
    Map<String, State> result = new HashMap<>();
    while (resultSet.next()) {
        result.put(
                resultSet.getString("state_abbrevation"),
                new State(
                        resultSet.getString("area_code"),
                        resultSet.getString("cities")
                )
        );
    }
    return result;
}

private class State {
    private final String area;
    private final String cities;


    public State(String area, String cities) {
        this.area = area;
        this.cities = cities;
    }

    public String getArea() {
        return area;
    }

    public String getCities() {
        return cities;
    }
}

Fetch data from table and store in hashmap

2 Answers2