How to handle the database the correct way in android

Question

Well I have question regarding SQLite DB. I have created an activity with a list view such that it displays the next activity on click of an item. This is the main activity which is displayed first when the app starts. The activity is such that during the initialization it inserts the values in the tables. The tables in the DB have the field with a primary key and unique fields. I have declared the fields as unique so that the duplicate fields don't get on adding in the next row. Just it should avoid inserting the same values in the new row. The initialization and inserting of values is declared in the onCreate of the first activity itself. But here the problem appears is that when this application is closed and again opened it writes the error message in Logcat, "Error inserting values in ButterflyDB". I know that this problem appears bcoz there is already a database and the fields are declared as unique. Also it takes loads of time to display the activity as it calls the insertion functions in the first activity. After few delays of time, the first screen appears bcoz the application is inserting the values in the DB each time the first screen appears.

My code for this is as follows:

package com.myambitionconsultants.butterflyworld;

import android.app.ListActivity;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class ButterflyWorldActivity extends ListActivity  {
    private static final String TAG = ButterflyWorldActivity.class.getSimpleName();
    /**
     * Declare the variables and objects.
     * ButterflyDBAdapter - To initialize the DB, create the tables and store the values in it. 
     * ImageIDStorage - To store the Image drawable IDs into the table.
     * String[], int[] - To access the arrays declared in the data.xml file.
     * String - SQL statements to run when this activity is displayed.
     * Cursor - To access the records in the row of the table to display. 
     */
    ButterflyDBAdapter dbAdapter;
    ImageIDStorage imgIDStorage;
    ButterflyDetailsStorage detailsStorage;
    String[] levels, butterfly_common_names, butterfly_scientific_names, image_path, cat_id;
    int [] drawableID;
    int [] butterfly_id;
    String sql_butterfly_cat, sql_drawable_id;
    Cursor cur;




    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        /**
         * Initialize the declared variables.
         * dbAdapter - Initialize with ButterflyDBAdapter object.
         * imgIDStorage - Initialize with ImageIDStorage object.
         * 
         * levels, butterfly_common_names, butterfly_scientific_names, image_path, butterfly_id, cat_id 
         * - Use the getStringArray() method to load each and every value from the XML.
         */

        dbAdapter = new ButterflyDBAdapter(this);
        imgIDStorage = new ImageIDStorage(this);
        detailsStorage = new ButterflyDetailsStorage(this);
        dbAdapter.createTable();
        levels = getResources().getStringArray(R.array.butterfly_categories);
        butterfly_common_names = getResources().getStringArray(R.array.butterfly_common_names);
        butterfly_scientific_names = getResources().getStringArray(R.array.butterfly_scientific_names);
        //image_path = getResources().getStringArray(R.array.image_path);
        butterfly_id = getResources().getIntArray(R.array.butterfly_id);
        cat_id = getResources().getStringArray(R.array.category_id);
        //drawables = getResources().getStringArray(R.array.drawable_array);

        /**
         * Method Calls.
         * Insert the Butterfly Categories, Butterflies, ImageIDs in the database.
         */
        insertButterflyCategory();
        insertButterfly();
        imgIDStorage.insertImageID_1();
        imgIDStorage.insertImageID_2();
        detailsStorage.insertButterflyDetails();
        /**
         * SQL statement to select the Category Name from the ButterflyCategory Table.
         * Initialize the cursor to execute the query from the table.
         */
        sql_butterfly_cat = "Select " + ButterflyDBAdapter.COL_CATEGORY_NAME + " from " + ButterflyDBAdapter.BUTTERFLY_CATEGORY;
        cur =  dbAdapter.getSQLiteDB().rawQuery(sql_butterfly_cat, null);


        /**
         * After retrieving the rows from the cursor, store the category name values in the array.
         * Move the cursor to the first.
         * Till the last row value in the table, keep on adding the category names in the array. 
         */

        //After retrieving the rows from the cursor, store the category name values in the array.
        String[] category = new String[cur.getCount()];
        Log.i(TAG, "Count: " + cur.getCount());

        cur.moveToFirst(); //Move the cursor to the first row.
        for(int i = 0; i < cur.getCount(); i++){
            //Till the last row value in the table, keep on adding the category names in the array.
            category[i] =cur.getString(cur.getColumnIndex(ButterflyDBAdapter.COL_CATEGORY_NAME)); 
            //Move the cursor to the next row
            cur.moveToNext();
        }

        /**
         * Use the setListAdapter() method to display the category names in the list.
         */
        setListAdapter(new ArrayAdapter<String>(this, R.layout.list_item, category));

        /**
         * Use the getListView() method.
         */
        ListView lv = getListView();
        lv.setTextFilterEnabled(true);

        /**
         * Manage the cursor
         */
        startManagingCursor(cur);

      /**
       * Determine which category was clicked in the list.
       * Pass the category name as extra in the bundle.
       * Display the next activity with the values passed through the bundle.
       */
      lv.setOnItemClickListener(new OnItemClickListener() {
        public void onItemClick(AdapterView<?> parent, View view,
            int position, long id) {

            String category_name = ((TextView) view).getText().toString();
            Intent i = new Intent(ButterflyWorldActivity.this, ShowCategory.class);
            //Intent i2 = new Intent(ButterflyWorldActivity.this, .class);
            i.putExtra("category_name", category_name);
            Bundle bundle = new Bundle();

            switch(position){
            case 0:
                bundle.putString("category_name", category_name);
                //bundle.putInt("drawable1", R.drawable.tailed_jay_1);
                //bundle.putInt("drawable2", R.drawable.blue_bottle_1);
                //bundle.putInt("drawable3", R.drawable.common_raven_1);
                i.putExtras(bundle);
                startActivity(i);
                break;
            case 1:
                bundle.putString("category_name", category_name);
                //bundle.putInt("drawable1", R.drawable.common_wanderer_1);
                //bundle.putInt("drawable2", R.drawable.common_jezabel_1);
                //bundle.putInt("drawable3", R.drawable.psyche_1);
                i.putExtras(bundle);
                startActivity(i);
                break;
            case 2:
                bundle.putString("category_name", category_name);
                //bundle.putInt("drawable1", R.drawable.common_silverline_1);
                //bundle.putInt("drawable2", R.drawable.grass_jewel_1);
                //bundle.putInt("drawable3", R.drawable.monkey_puzzle_1);
                i.putExtras(bundle);
                startActivity(i);
                break;
            case 3:
                bundle.putString("category_name", category_name);
                //bundle.putInt("drawable1", R.drawable.leopard_1);
                //bundle.putInt("drawable1", R.drawable.common_silverline_1);
                //bundle.putInt("drawable2", R.drawable.peacock_pansy_1);
                //bundle.putInt("drawable3", R.drawable.blue_oakleaf_1);
                i.putExtras(bundle);
                startActivity(i);
                break;
            case 4:
                bundle.putString("category_name", category_name);
                //bundle.putInt("drawable1", R.drawable.chestnut_bob_1);
                //bundle.putInt("drawable2", R.drawable.golden_angle_1);
                //bundle.putInt("drawable3", R.drawable.grass_demon_1);
                //bundle.putInt("drawable3", R.drawable.psyche_1);
                i.putExtras(bundle);
                startActivity(i);
                break;
            }          
        }

      });

    }


    /**
     * Insert the butterfly category names into the database.
     * The number of values in the levels[] determines the number of 
     * rows to be put in the ButterflyCategory table.  
     */
    public void insertButterflyCategory(){

        for(int i = 0; i<levels.length;i++){
            ContentValues cv = new ContentValues();
            //cv.put(ButterflyDBAdapter.COL_CATEGORY_ID, i+1);
            cv.put(ButterflyDBAdapter.COL_CATEGORY_NAME, levels[i]);

            try{
                dbAdapter.getSQLiteDB().insert(ButterflyDBAdapter.BUTTERFLY_CATEGORY, null, cv);
                //dbAdapter.getSQLiteDB().insertWithOnConflict(ButterflyDBAdapter.BUTTERFLY_CATEGORY, null, cv,SQLiteDatabase.CONFLICT_IGNORE);
                //dbAdapter.getSQLiteDB().insertOrThrow(ButterflyDBAdapter.BUTTERFLY_CATEGORY, null, cv);
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        Log.i(TAG, "Category Values inserted in " + ButterflyDBAdapter.BUTTERFLY_CATEGORY);
    }
    /**
     * Insert the butterfly details such as category id, common name, scientific name and detailed info into the database.
     * The number of values in the butterfly_common_names[] determines the number of 
     * rows to be put in the Butterfly table.  
     */
    public void insertButterfly(){
        for(int i = 0; i<butterfly_common_names.length;i++){
            ContentValues cv = new ContentValues();

            cv.put(ButterflyDBAdapter.COL_BUTTERFLY_COMMON_NAME, butterfly_common_names[i]);
            cv.put(ButterflyDBAdapter.COL_CAT_ID, Integer.parseInt(cat_id[i]));
            cv.put(ButterflyDBAdapter.COL_BUTTERFLY_SCIENTIFIC_NAME, butterfly_scientific_names[i]);
            try{
                dbAdapter.getSQLiteDB().insert(ButterflyDBAdapter.BUTTERFLY, null, cv);
                //dbAdapter.getSQLiteDB().insertWithOnConflict(ButterflyDBAdapter.BUTTERFLY_CATEGORY, null, cv,SQLiteDatabase.CONFLICT_IGNORE);
                //dbAdapter.getSQLiteDB().insertOrThrow(ButterflyDBAdapter.BUTTERFLY_CATEGORY, null, cv);
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        Log.i(TAG, "Butterfly Values inserted in " + ButterflyDBAdapter.BUTTERFLY);
    }

    /**
     * Close the database and cursor on finish of activity.
     */
    @Override
    protected void onDestroy() {
        // TODO Auto-generated method stub
        super.onDestroy();
        dbAdapter.getSQLiteDB().close();
        cur.close();
        imgIDStorage.closeDB();
        detailsStorage.closeDB();
        //super.onDestroy();
    }

    /**
     * Close the database and cursor on finish of activity.
     */
    @Override
    protected void onPause() {
        // TOD Auto-generated method stub
        super.onPause();
        dbAdapter.getSQLiteDB().close();
        cur.close();
        imgIDStorage.closeDB();
        detailsStorage.closeDB();
        //super.onPause();
    }
}

The DDMS for this is as follows:

12-09 11:38:53.299: E/Database(1375): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
12-09 11:38:53.299: E/Database(1375):   at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
12-09 11:38:53.299: E/Database(1375):   at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:55)
12-09 11:38:53.299: E/Database(1375):   at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1549)
12-09 11:38:53.299: E/Database(1375):   at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
12-09 11:38:53.299: E/Database(1375):   at com.myambitionconsultants.butterflyworld.ButterflyWorldActivity.insertButterflyCategory(ButterflyWorldActivity.java:204)
12-09 11:38:53.299: E/Database(1375):   at com.myambitionconsultants.butterflyworld.ButterflyWorldActivity.onCreate(ButterflyWorldActivity.java:78)
12-09 11:38:53.299: E/Database(1375):   at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
12-09 11:38:53.299: E/Database(1375):   at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
12-09 11:38:53.299: E/Database(1375):   at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
12-09 11:38:53.299: E/Database(1375):   at android.app.ActivityThread.access$2300(ActivityThread.java:125)
12-09 11:38:53.299: E/Database(1375):   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
12-09 11:38:53.299: E/Database(1375):   at android.os.Handler.dispatchMessage(Handler.java:99)
12-09 11:38:53.299: E/Database(1375):   at android.os.Looper.loop(Looper.java:123)
12-09 11:38:53.299: E/Database(1375):   at android.app.ActivityThread.main(ActivityThread.java:4627)
12-09 11:38:53.299: E/Database(1375):   at java.lang.reflect.Method.invokeNative(Native Method)
12-09 11:38:53.299: E/Database(1375):   at java.lang.reflect.Method.invoke(Method.java:521)
12-09 11:38:53.299: E/Database(1375):   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
12-09 11:38:53.299: E/Database(1375):   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
12-09 11:38:53.299: E/Database(1375):   at dalvik.system.NativeStart.main(Native Method)
12-09 11:38:53.369: E/Database(1375): Error inserting CategoryName=Pieridae
12-09 11:38:53.369: E/Database(1375): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
12-09 11:38:53.369: E/Database(1375):   at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
12-09 11:38:53.369: E/Database(1375):   at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:55)
12-09 11:38:53.369: E/Database(1375):   at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1549)
12-09 11:38:53.369: E/Database(1375):   at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
12-09 11:38:53.369: E/Database(1375):   at com.myambitionconsultants.butterflyworld.ButterflyWorldActivity.insertButterflyCategory(ButterflyWorldActivity.java:204)
12-09 11:38:53.369: E/Database(1375):   at com.myambitionconsultants.butterflyworld.ButterflyWorldActivity.onCreate(ButterflyWorldActivity.java:78)
12-09 11:38:53.369: E/Database(1375):   at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
12-09 11:38:53.369: E/Database(1375):   at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
12-09 11:38:53.369: E/Database(1375):   at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
12-09 11:38:53.369: E/Database(1375):   at android.app.ActivityThread.access$2300(ActivityThread.java:125)
12-09 11:38:53.369: E/Database(1375):   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
12-09 11:38:53.369: E/Database(1375):   at android.os.Handler.dispatchMessage(Handler.java:99)
12-09 11:38:53.369: E/Database(1375):   at android.os.Looper.loop(Looper.java:123)
12-09 11:38:53.369: E/Database(1375):   at android.app.ActivityThread.main(ActivityThread.java:4627)
12-09 11:38:53.369: E/Database(1375):   at java.lang.reflect.Method.invokeNative(Native Method)
12-09 11:38:53.369: E/Database(1375):   at java.lang.reflect.Method.invoke(Method.java:521)
12-09 11:38:53.369: E/Database(1375):   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
12-09 11:38:53.369: E/Database(1375):   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
12-09 11:38:53.369: E/Database(1375):   at dalvik.system.NativeStart.main(Native Method)

How to handle such kinds of errors?

Answer 1

When looking on your log the error is "constraint failed" which means you are trying to insert null value to not null column or the column which has the primary key.

Else you are trying to insert the duplicate values into the column which has the primary key or unique key.

Then you can delete the table before inserting the values. Since the values are inserted at the first time app launches and from the next time onwards app will try to insert the same values.

Answer 2

I do not understand why you create and populate the database in Activity.onCreate(). This is only need to be done once, on first launch.

In your ButterflyDBAdapter, do you have a class extending SQLiteOpenHelper ? If so, you should create and populate your database in the onCreate() handler from SQLiteOpenHelper. Can you post the ButterflyDBAdapter code ?

Answer 3

You should probably move all of the code for populating the database, all of the methods for transferring data from your resources to the database, into the actual ButterflyDBAdapter. Within the onCreate() method of the adapter, you can check to see if the database already exists, and if not, populate it with your set of default data. You can then also expose some methods for inserting and retrieving data from the database. Within your ButterflyWorldActivity, you then only need to interact with the database via those methods.