Database data insertion

Question

public class DataBaseHandler extends SQLiteOpenHelper {
 private static String DATABASE_NAME = "UnitDatabase";
 private static String MEASUREMENT_TYPE_TABLE = "measurement_types";
 private static String idCol = "id";
 private static String typeCol = "type";
 private static int DATABASE_VERSION = 1;
 private static String[] measurementType = new String[] {"acceleration", "angles", "area", "astronomical",
 "density", "energy", "force", "frequency", "length/distance", "power", "pressure", "speed",
 "temperature", "torque", "volume", "weight"};
 public DataBaseHandler(Context context) {
 super(context, DATABASE_NAME, null, DATABASE_VERSION);
 }
 @Override
 public void onCreate(SQLiteDatabase db) {
 String create_table = "CREATE TABLE " + MEASUREMENT_TYPE_TABLE +
 "(" + idCol + " integer primary key autoincrement " +
 typeCol + " varchar(255) not null ";
 db.execSQL(create_table);
 }
 /**
 * Populate the table containing measurement types
 * @param db
 */
 public void populateMeasurementTable(SQLiteDatabase db) {
 db = this.getWritableDatabase();
 for(int i = 0; i < measurementType.length; i++) {
 ContentValues values = new ContentValues();
 values.put(typeCol, measurementType[i]);
 db.insert(MEASUREMENT_TYPE_TABLE, null, values);
 }
 }
}

I thought I would post my code first and then ask the question. My question is with my implementation of the last method (populateMeasurementTable()). I wanted to be able to insert multiple values in to the table and this is the way I am going to do it, however I don't think it is an efficient way of doing it especially if I have larger arrays such as:

private static String[] densityUnitTypes = new String[] { "grain/cubic foot", "grain/cubic inch",
 "grain/gallon [UK]", "grain/gallon [US]", "grain/ounce [UK]", "grain/ounce [US]", "grain/quart [UK]",
 "grain/quart [US]", "gram/cubic centimeter", "gram/cubic kilometer", "gram/cubic meter",
 "gram/cubic millimeter", "gram/kiloliter", "gram/liter", "gram/litre", "gram/microliter", "gram/milliliter",
 "hectogram/cubic centimeter", "hectogram/cubic kilometer", "hectogram/cubic meter",
 "hectogram/cubic micrometer", "hectogram/cubic millimeter", "hectogram/hectoliter", "hectogram/kiloliter",
 "hectogram/liter", "hectogram/litre", "hectogram/microliter", "hectogram/milliliter",
 "kilogram/cubic centimeter", "kilogram/cubic kilometer", "kilogram/cubic meter",
 "kilogram/cubic micrometer", "kilogram/cubic millimeter", "kilogram/kiloliter", "kilogram/liter",
 "kilogram/litre", "kilogram/microliter", "kilogram/milliliter", "kiloton/cubic mile [UK]",
 "kiloton/cubic mile [US]", "kiloton/cubic yard [UK]", "kiloton/cubic yard [US]", "kilotonne/cubic meter",
 "kilotonne/kiloliter", "kilotonne/liter", "kilotonne/litre", "microgram/cubic centimeter",
 "microgram/cubic kilometer", "microgram/cubic meter", "microgram/cubic micrometer",
 "microgram/cubic millimeter", "microgram/cubic nanometer", "microgram/kiloliter", "microgram/liter",
 "microgram/litre", "microgram/microliter", "microgram/milliliter", "milligram/cubic centimeter",
 "milligram/cubic kilometer", "milligram/cubic meter", "milligram/cubic millimeter",
 "milligram/kiloliter", "milligram/liter", "milligram/litre", "milligram/microliter", "milligram/milliliter",
 "nanogram/cubic centimeter", "nanogram/cubic kilometer", "nanogram/cubic meter",
 "nanogram/cubic millimeter", "nanogram/kiloliter", "nanogram/liter", "nanogram/litre",
 "nanogram/microliter", "nanogram/milliliter", "ounce/cubic foot", "ounce/cubic inch", "ounce/gallon [UK]",
 "ounce/gallon [US]", "pound/cubic foot", "pound/cubic inch", "pound/cubic mile", "pound/cubic yard",
 "pound/gallon [UK]", "pound/gallon [US]", "tonne/cubic kilometer", "tonne/cubic meter", "tonne/kiloliter",
 "tonne/liter", "tonne/litre", "water [0°C, solid]", "water [20°C]", "water [4°C]" };

I had thought about using the bulkInsert(Uri uri, ContentValues[] values) method from the ContentResolver class in the API however, I felt that writing own provider class to extend the ContentProvider class and then writing my own implementation of the bulkInsert() method was a little nuclear for this - although it would allow me to use transactions which I have read a far more efficient way to carry out the task.

So my questions are:

• How efficient is the way I have chosen to do it?
• Would I be better of writing my own implementation of the bulkInsert() method?
• Is there a different more efficient way? (for example writing the raw SQL out to insert multiple values)

Answer 1

For exact knowledge about finding out which is the most effective, the best way to find that out is to time using:

long start = System.nanoTime();
... perform operations ...
long stop = System.nanoTime();
double milliSecondsElapsed = (stop - start) / 1000000.0;

I think your way of doing it currently is quite good.

I found that there is another way of doing it but it seems to involve writing a seemingly SQL query that looks like this:

INSERT INTO Contacts 
SELECT 'ae0caa6a-8ff6-d63f-0253-110b20ac2127' AS ID, 'xxx' AS FirstName, 'xxx' AS LastName, '9008987887' AS PhoneNumber, '[email protected]' AS EmailId, 'Yes' AS Status 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', '[email protected]', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', '[email protected]', 'Yes'

Unless that alternative way of inserting rows to the database improves performance significantly, I would stick to the way that you are doing it now. I imagine that the code required to write this SQL statement would be quite ugly (if you would want to transform your current String[]/ContentValues approach into this SQL statement). Especially considering that I assume you only perform this mass-insertion once. Besides, the code required to transform into SQL also takes time to execute of course, which might neglect the performance increase you would get from doing the mass-insertion with a single SQL statement.

Also, your current approach is very easy to read and understand.

As for whether or not you should write a bulkInsert() method, you could do it just for the challenge of it... if you don't have anything better to do :)

Summary

Stick to what you are using right now.

• user2405469: Thank you, I will stick to doing it this way for now, maybe I will have a go at implementing the other method at a later time for the sheer fun of it :)

Answer 2

You have some constants in your code. Why are they not final? Also the naming for idCol and typeCol is confusing me. Why not ID_COLUMN and TYPE_COLUMN.

Same also goes for measurementTypes these all are constants. Name and use them as such.

• user2405469: the final that is missing is my mistake as for naming the variables...we think differently? I don't know...perhaps a suggestion for my real question? thank you for pointing out the discrepancies though.
• Vogel612: Well the are factual constants, right? Why not name them as constants then?
• user2405469: As I said, it was a mistake...
• user2405469: But this is still not an answer to the real question.
• Jamal: @user2405469: CR is a little different than other sites in this regard. Reviewers are free to comment on anything in the code, not just the specific questions. If a reviewer doesn't answer them, then that probably means that they don't know how to answer them, but still have other things to point out. Others may still come along to answer these questions if the first reviewer doesn't.