Android Studio - How to add 1300 rows to SQLite table?

Javierungo
July 14, 2021
90 views
0 votes
2 Answers

In Android Studio, I need to create a SQLite table for a list of cities and population. It would have 1300 rows aprox. The list is in a 2 columns Excel file so I can copy it from there to my code.

I have a class to manage DB and a method to create the table, but I don´t know how to add the list of cities-population to the table.

This is the part of my code I have to create the table:

String crearTablaCities = "CREATE TABLE "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT, NAME_CITY TEXT, POPULATION_CITY INTEGER)";
        db.execSQL(crearTablaCities);

I know how to add one row getting values from somewhere, but I don´t know how to add more than 1300 rows automatically so that I don´t have to write or modify more than 1300 lines by hand.

    public void insertCity(ObjectCity newCity){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY", newCity.getCityID());
        cv.put("NAME_CITY", newCity.getCityName());
        cv.put("POPULATION_CITY", newCity.getCityPopulation());

        db.insert(MI_TABLA_CITIES,null,cv);

        //db.close();
    }

I don´t know how to face the next step. Can someone please give me some hint to find the way to do it? Thanks a lot in advance

Answers

There are 3 relatively simple ways that you can accomplish this.

a) Create a formula that generates the code on your behalf and then copy and paste the generated code to a suitable place.

b) Save the data as a csv and copy the file as an asset you can then open and read the file extracting the data and inserting it perhaps in the onCreate method of the database helper.

c) Save the data as a csv and use an SQLite tool to import and load the data and to then copy that database as an asset and then use that database.

Option A is a very simple, assuming that you have something like :-

And your code shows that you have an insertCity method that takes a City object. Assuming that you construct a City using City("Tokyo",37339804) then to insert a single city, your code could be something like :-

`insert(new ObjectCity("Tokyo",37339804L));`

Long for population has been assumed.

Then you could enter a formula in cell C1 as ="insertCity(new ObjectCity("&CHAR(34)&A1&CHAR(34)&","&B1&"L));". You can then copy this cell to c2-c1300 (c2-c17 using the spreadsheet above). The resultant Speadsheet would be like:-

You can then just drop copy and paste the generated code (cells c1-c1300) into a suitable place.

Here’s a working example:-

The ObjectCity class :-

public class ObjectCity {

    private String cityName;
    private Long cityPopulation;

    public ObjectCity(String cityName, Long cityPopulation) {
        this.cityName = cityName;
        this.cityPopulation = cityPopulation;
    }

    public String getCityName() {
        return cityName;
    }

    public Long getCityPopulation() {
        return cityPopulation;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public void setCityPopulation(Long cityPopulation) {
        this.cityPopulation = cityPopulation;
    }
}

The DatabaseHelper DBHelper class (based upon the code in the question) :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String MI_TABLA_CITIES = "city";

    public DBHelper(Context context) {
        super(context, "theDatabase", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crearTablaCities = "CREATE TABLE "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT, NAME_CITY TEXT, POPULATION_CITY INTEGER)";
        db.execSQL(crearTablaCities);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insertCity(ObjectCity newCity){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY", newCity.getCityID());
        cv.put("NAME_CITY", newCity.getCityName());
        cv.put("POPULATION_CITY", newCity.getCityPopulation());
        db.insert(MI_TABLA_CITIES,null,cv);
        //db.close();

    }

    /* function to load the country data */
    public void loadData() {
        if(DatabaseUtils.queryNumEntries(this.getWritableDatabase(),MI_TABLA_CITIES)> 0) return;

        insertCity(new ObjectCity("Tokyo",37339804L));
        insertCity(new ObjectCity("Delhi",31181376L));
        insertCity(new ObjectCity("Shanghai",27795702L));
        insertCity(new ObjectCity("Sao Paulo",22237472L));
        insertCity(new ObjectCity("Mexico City",21918936L));
        insertCity(new ObjectCity("Dhaka",21741090L));
        insertCity(new ObjectCity("Cairo",21322750L));
        insertCity(new ObjectCity("Beijing",20896820L));
        insertCity(new ObjectCity("Mumbai",20667656L));
        insertCity(new ObjectCity("Osaka",19110616L));
        insertCity(new ObjectCity("Karachi",16459472L));
        insertCity(new ObjectCity("Chongqing",16382376L));
        insertCity(new ObjectCity("Istanbul",15415197L));
        insertCity(new ObjectCity("Buenos Aires",15257673L));
        insertCity(new ObjectCity("Kolkata",14974073L));
        insertCity(new ObjectCity("Kinshasa",14970460L));
        insertCity(new ObjectCity("Lagos",14862111L));
    }
}

Note the loadData method.
- This uses the DatabaseUtils queryNumEntries method to check if any data exists and only loads the data if there are no rows in the table.
- The rest of the code was copy and pasted from the spreadsheet as above (2nd image).

Last an invoking activity MainActivity :-

public class MainActivity extends AppCompatActivity {

    DBHelper db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = new DBHelper(this);
        db.loadData(); //<<<<< LOADS THE CITY DATA (if not already loaded)
    }
}

Result

Running the above and using Android Studio’s Database Inspector reveals:-

Following on from the first answer re:-

c) Save the data as a csv and use an SQLite tool to import and load the data and to then copy that database as an asset and then use that database.

Then a csv of the raw sheet was saved using Excel. This was imported into the SQLite Tool Navicat for SQLite (there are other such tools but I prefer Navicat):-

The database and connection were closed (saves the database).

In Android studio, the App was right clicked in Android View and New then Directory was selected(clicked) and from the dialog src/Android/assets was selected (aka created the assets folder in app/src/main (can be done outside of Android Studio)).

The database file (named thedatabase.db) was copied into the newly created assets folder.

The Database Helper class DBHelperB was written as per :-

public class DBHelperB extends SQLiteOpenHelper {


    /*
        Note database created in SQLite tool and data imported from spreadsheet
        database file is the copied into the assets folder (after creating the folder)
     */
    public static final String DBNAME = "thedatabase.db"; //<<<< asset file name must match
    public static final String MI_TABLA_CITIES = "city";

    private static volatile DBHelperB instance = null;

    // Prevent construction outside
    private DBHelperB(Context context) {
        super(context, "theDatabase", null, 1);
    }

    // instead of using db = new DBHelper(this); 
    // this allows db = DBHelperB.getInstance(); 
    // i.e. a single instance (aka a singleton) is retrieved
    // IMPORTANTLY this also copies the asset DB if not already loaded 
    public static DBHelperB getDBInstance(Context context) {
        if (instance == null) {
            getAssetDB(context); // <<<<< Copies ASSET DB
            instance = new DBHelperB(context);
        }
        return instance;
    }

    /* Won't be called as the DB will exists */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String crearTablaCities = "CREATE TABLE "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT, NAME_CITY TEXT, POPULATION_CITY INTEGER)";
        db.execSQL(crearTablaCities);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insertCity(ObjectCity newCity){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY", newCity.getCityID());
        cv.put("NAME_CITY", newCity.getCityName());
        cv.put("POPULATION_CITY", newCity.getCityPopulation());
        db.insert(MI_TABLA_CITIES,null,cv);
        //db.close();

    }

    /* 
        Copies the DB from the assets folder unless the db already exists
        Note if this fails a runtime exception will occur. It is then
        IMPORTANT to look at the log to determine the cause
        More often than not a failure is due to the asset not having been copied correctly
    
     */
    private static void getAssetDB(Context context) {
        File dbFile = context.getDatabasePath(DBNAME);
        InputStream asset;
        OutputStream db;
        byte[] buffer = new byte[4096];
        if (dbFile.exists()) return; // Database exists so no need to copy
        /* Ensure that the data/data/<package_name>/databases folder exists */
        if (!dbFile.getParentFile().exists()) {
            dbFile.getParentFile().mkdirs();
        }
        try {
            asset = context.getAssets().open(DBNAME);
            db = new FileOutputStream(dbFile);
            int length;
            while ((length = asset.read(buffer)) > 0) {
                db.write(buffer,0,length);
            }
            db.flush();
            db.close();
            asset.close();

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to copy asset file");
        }
    }
}

MainActivity was then amended to be :-

public class MainActivity extends AppCompatActivity {

    DBHelper db;
    DBHelperB assetdb; //ADDED

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = new DBHelper(this);
        db.loadData();
        // ADDED >
        assetdb = DBHelperB.getDBInstance(this); /* will copy the asset db if need be */
        /* Database inspector doesn't like this data so show data another way */
        Cursor csr = assetdb.getWritableDatabase().query("city",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
    }
}

see comments

The result, after running, as per the log includes :-

2021-07-15 09:16:54.454 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@374559d
2021-07-15 09:16:54.455 I/System.out: 0 {
2021-07-15 09:16:54.455 I/System.out:    ID_CITY=1
2021-07-15 09:16:54.455 I/System.out:    NAME_CITY=Tokyo
2021-07-15 09:16:54.455 I/System.out:    POPULATION_CITY=37339804
2021-07-15 09:16:54.455 I/System.out: }
2021-07-15 09:16:54.455 I/System.out: 1 {
2021-07-15 09:16:54.456 I/System.out:    ID_CITY=2
2021-07-15 09:16:54.456 I/System.out:    NAME_CITY=Delhi
2021-07-15 09:16:54.456 I/System.out:    POPULATION_CITY=31181376
2021-07-15 09:16:54.456 I/System.out: }
2021-07-15 09:16:54.456 I/System.out: 2 {
2021-07-15 09:16:54.456 I/System.out:    ID_CITY=3
2021-07-15 09:16:54.456 I/System.out:    NAME_CITY=Shanghai
2021-07-15 09:16:54.456 I/System.out:    POPULATION_CITY=27795702
2021-07-15 09:16:54.456 I/System.out: }
....

Please signup or login to give your own answer.

Click here to cancel reply.

Android Studio – How to add 1300 rows to SQLite table?

Answers