Retrieving records from a CSV file with a geographical hierarchy

Question

I have the following scenario and I would like some review or suggestions. I have a CSV file with lot of records. The records are basically as follows:

1. There are names of counties in Kenya.
2. These counties have each unique id.
3. The counties now have sub counties and the sub counties are related to the county by the countyid.
4. Now the sub counties also have unique id.
5. The sub counties have wards under them and they are related to the sub counties by the sub countyid.

Now, let's say I want to know a record such as I pass the county name and it gives me a record in the following order:

Ward,subcounty,county

My approach (the raw approach):

1. When a user keys in the county name: open the file, read through the record and extract the county id.
2. Then use that county id to extract the sub counties under that county and extract the record and put them in a list.
3. Now, extract the sub county id from the list and and then open the file and read through the records and extract the wards with the same sub county and put them in a list.

So far so good. But honestly, opening and closing the file thrice for extracting record and calling three different functions doesn't feel the right approach at all. This is a very naive approach and I would like to know how i can combine the operations in an efficient manner.

So, let's say if a user enters the name of a county. I open the file once. Extract all the sub counties under that county and get the sub county id. Then extract all the wards under each and every of those sub counties. Then I close the file.

Here is a snippet of the record:

A county named "Mombasa" with county id - 1. These are the sub counties as produced by my program:

[48,1,Sub-County,Changamwe, 49,1,Sub-County,Jomvu, 50,1,Sub-County,Kisauni, 51,1,Sub-County,Nyali, 52,1,Sub-County,Likoni, 53,1,Sub-County,Mvita]

So, let's say now the idea is to extract the wards under the sub-county ("Nyali") with sub county id - 51 and these are the results:

[353,51,Ward,Frere Town, 354,51,Ward,Ziwa La Ng'ombe, 355,51,Ward,Mkomani, 356,51,Ward,Kongowea, 357,51,Ward,Kadzandani]

Here is my code:

package main;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;

public class Recursion {

     public static void main(String[] args) {

        System.out.println(getCountyId("Mombasa"));

        System.out.println(getsubcounty("1"));

        System.out.println(getwards("51"));
     }


     public static int getCountyId(String countyname){
         String csvFile = "C:/Users/user/workspace/SpringProjects/JavaRecursionTest/src/main/records.csv";
            BufferedReader br = null;
            String line = "";
            ArrayList<String> data = new ArrayList<String>();        
            try {

                br = new BufferedReader(new FileReader(csvFile));

                while((line=br.readLine())!=null){
                    if(line.contains(countyname)){
                        for(String a : line.split(","))
                        {
                           String row =a.trim();
                           data.add(row);
                        }
                    }

                }





            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (br != null) {
                    try {
                        br.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
         //pass countyname and get countyid

            return Integer.parseInt((data.get(0)));
     }


     public static ArrayList<String> getsubcounty(String countyid){


         //pass countyid and get subcounty names

         String csvFile = "C:/Users/user/workspace/SpringProjects/JavaRecursionTest/src/main/records.csv";
            BufferedReader br = null;
            String line = "";
            ArrayList<String> data = new ArrayList<String>();        
            try {

                br = new BufferedReader(new FileReader(csvFile));

                while((line=br.readLine())!=null){
                    if(line.contains("Sub-County")){
                        for(String a : line.split(","))
                        {
                            if(a.equals(countyid)){

                                data.add(line);
                            }

                        }

                    }

                }





            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (br != null) {
                    try {
                        br.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
         //pass countyname and get countyid
            System.out.println(data.size());
            return data;
     }


     public static ArrayList<String> getwards(String subcountyid){

        //pass subcounty id and get wards 

         String csvFile = "C:/Users/user/workspace/SpringProjects/JavaRecursionTest/src/main/records.csv";
            BufferedReader br = null;
            String line = "";
            ArrayList<String> data = new ArrayList<String>();        
            try {

                br = new BufferedReader(new FileReader(csvFile));

                while((line=br.readLine())!=null){
                    if(line.contains("Ward")){
                        for(String a : line.split(","))
                        {
                            if(a.equals(subcountyid)){

                                data.add(line);
                            }

                        }

                    }

                }





            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (br != null) {
                    try {
                        br.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
         //pass countyname and get countyid
            System.out.println(data.size());
            return data;


     }
}

Answer 1

Indeed it's very wasteful to read a CSV file 3 times to extract different parts from it. It would be better to read the file once, populate a custom data structure with all the relevant content, and then use that data structure to answer to your needs.

The code duplication of reading the CSV file is also a problem. I suspect you copy-pasted the first method and made minor modifications. That's a bad habit, try to resist it, and use functions to avoid copy-pasting.

The exception handling in file reading operations is old-fashioned, a better technique exists using try-with-resources.

Taking the above suggestions into account, the program should look more like this:

  public static Data readCsvFile(String path) {
    Data data = new Data();

    try (BufferedReader br = new BufferedReader(new FileReader(path))) {
      String line;
      while ((line = br.readLine()) != null) {
        String[] parts = line.split(",");
        if (parts[2].equals("Sub-County")) {
          // TODO: data.add, data.put, ...
          // ...
        } else if (parts[2].equals("Ward")) {
          // TODO: data.add, data.put, ...
          // ...
        }
      }
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    return data;
  }

  public static void main(String[] args) {
    Data data = readCsvFile("...");
    System.out.println(data.getCountyId("Mombasa"));
    System.out.println(data.getSubCounty("1"));
    System.out.println(data.getWards("51"));
  }

The Data class and the TODOs are for you to implement.