Database – Best Practices for Reading from a File and Inserting into a Database

databasefilespatterns-and-practices

I am making an Android application which reads several JSON files and inserts the read information into tables in my database.
For instance, there are three files:

  1. countries.json

    {
    "countries": [
        {
            "name": "United States",
            "org_ids": [
                {
                    "id": 1
                },
                {
                    "id": 2
                },
                {
                    "id": 3
                }
            ]
        },
        {
            "name": "China",
            "org_ids": [
                {
                    "id": 1
                },
                {
                    "id": 2
                }
            ]
        },
        {
            "name": "Hong Kong",
            "org_ids": [
                {
                    "id": 3
                },
                {
                    "id": 4
                },
                {
                    "id": 5
                }
            ]
        }
    ]
}

  2. orgs.json

    {
    "orgs": [
        {
            "id": 1,
            "name": "UNO"
        },
        {
            "id": 2,
            "name": "Organization of American States"
        },
        {
            "id": 3,
            "name": "INTERPOL"
        },
        {
            "id": 4,
            "name": "European Union"
        },
        {
            "id": 5,
            "name": "The Commonwealth of Nations"
        }
    ]
}

  3. continents.json

    {
    "continents": [
        {
            "id": 1,
            "name": "Africa"
        },
        {
            "id": 2,
            "name": "North America"
        },
        {
            "id": 3,
            "name": "South America"
        },
        {
            "id": 4,
            "name": "Asia"
        },
        {
            "id": 5,
            "name": "Europe"
        },
        {
            "id": 6,
            "name": "Australia"
        }
    ]
}

Please don't pay attention to the contents of my JSONs, they don't contain real members of the listed international organizations. It's just an example.

I see two ways.

THE FORMER WAY.

Store each country with its data to a class instance and define a SparseArray contains a country ID as a key and a CountryInfo object as a value. 

/** Each item contains country's ID (key) and respective data (value) */
    private SparseArray<CountryInfo> mCountriesInfo = new SparseArray<JsonParser.CountryInfo>();

After reading everything from the files, the data from the SparseArray will be inserted into the database.

    private class CountryInfo {

    String mCountryName;
    int continentId;
    String mContinentName;
    /** Contains IDs (keys) and names (values) of organizations */
    SparseArray<String> mOrgNames = new SparseArray<String>();

}

Drawbacks.

  • The more JSONS (or the more complex JSONs) the more complex the class
    for storing.
  • Data duplication.

Benefits

  • Reading and inserting does not depend on each other.

THE LATTER WAY.

Read and insert without preliminary saving.

Drawbacks.

  • Database logic and reading logic are mixed.

Benefits.

  • No any additional classes.
  • No data duplication.

Which way is a better practice?

Best Answer

(This is a Java question, I guess?)

I am sure you can write a JSON reader which takes an arbitrary processing function and applies that function to every record. The "processing function" may be passed in a functional way, or if you have only older Java versions available, in form of an interface. This gives you reading logic without any database logic. Then, you provide a specific processing function / interface implementation containing the "database insert" logic.

As a result, you get separated database and reading logic without the drawbacks from your first approach.

This is also known as Visitor pattern.

Related Topic