Updating Android SQLite database table while user operates App and avoid errors/poor user experience

Question

My Android App has a products table in an SQLite database that comes pre-populated when the user installs it. The table can be updated from an Azure web service.

Instead of returning only the updated records from the web service and populating the SQLite table with the updated records, I want to simply return the 2900 records to the App. This is because most of the products will have changed. On opening the App, the products table is deleted using an SQL query and the ksoap2 response is sent to Product objects which are inserted into the database.

While this update to the products table is happening, I would like the user to be able to use the App without disruption. If the Products table has been deleted, then they cannot operate the App properly.

What are my options? Could I populate a temporary Product table and when the service has finished populating it, I could copy it to the 'live' Product table. Or could I completely delete the Product table and rename the temporary table to "Product"?

Or am I going about this completely wrong. Any advice would be most appreciated. Code extract below:

 try {

          productDatasource.open();
          productDatasource.deleteProductTable();
          productDatasource.close();
          ArrayList<Product> arrProduct = new ArrayList<Product>();
          SoapObject request = new SoapObject(NAMESPACE, PRODUCT_METHOD_NAME); 

         SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
         envelope.dotNet = true;
         envelope.setOutputSoapObject(request);
         System.out.println("startit");
         HttpTransportSE ht = new HttpTransportSE(URL);
         ht.debug = true;
         ht.call(SOAP_ACTION_PRODUCT, envelope);

         SoapObject response = (SoapObject) envelope.getResponse();

         productDatasource.open();
         productDatasource.deleteProductTable();
         Product[] products = new Product[response.getPropertyCount()];

         for (int i = 0; i < products.length; i++) {

             SoapObject prodObj = (SoapObject)response.getProperty(i);
             Product product = new Product();

             product.setProductID(Integer.parseInt(prodObj.getProperty(10).toString()));
             product.setProductName(prodObj.getProperty(11).toString());
             product.setFKCategoryID(Integer.parseInt(prodObj.getProperty(5).toString()));
             product.setFKSubCategoryID(Integer.parseInt(prodObj.getProperty(13).toString()));
             product.setFKBrandID(Integer.parseInt(prodObj.getProperty(2).toString()));      

             productDatasource.createProduct(product);
         }

Answer 1

Use a ContentProvider together with a CursorLoader so that your UI remains fluid while the content is updating. Use BulkInsert to add rows faster. If you can just update records instead of replacing them, then add a column that lets you mark the record as needing update. For information on getting started with SQL databases on Android, see http://developer.android.com/guide/topics/data/data-storage.html#db and http://developer.android.com/training/basics/data-storage/databases.html.

Answer 2

The DBHelper class follows the Respsitory pattern. Where the access to the underlaying data is contained in a "Repository" class. This frees up your main logic to concentrate on the business logic and pushes all the data handling responsibility to the Repository. Depending on the complexity of your application the Repository may act as a "Facade" where the calls are simply proxied through to a Repository responsible for the domain object.

The code below creates a very simple template for the DBHelper

public class DBHelper 
{

    Context context;
    private SQLiteDatabase db;
    private final String DB_NAME = "MYDataBase";
    private final int DB_VERSION = 1;
    private final String TABLE_NAME = "Animal";
    private final String TABLE_ROW_ID = "id";
    private final String TABLE_ROW_ONE = "animal_name";
    private final String TABLE_ROW_TWO = "animal_bio";


    public DBHelper(Context context)
    {
        this.context = context;
        CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
        this.db = helper.getWritableDatabase();
    }

    public static byte[] getBitmapAsByteArray(Bitmap bitmap)
     {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        bitmap.compress(CompressFormat.PNG, 0, outputStream);       
        return outputStream.toByteArray();
    }   

    public void addRow(String name, String bio)
    {

        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, name);
        values.put(TABLE_ROW_TWO, bio);


        try
        {
            db.insert(TABLE_NAME, null, values);
            Log.w("database message","Insert successfully");
        }
        catch(Exception e)
        {
            Log.e("DB ERROR", e.toString());
            //e.printStackTrace();
        }
    }
    public void deleteRow(long rowID)
    {
        try 
        {
            db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);
                Log.w("database message","delete successfully");
        }
        catch (Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }

    public void updateRow(long rowID,String name,String bio)
    {


        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, name);
        values.put(TABLE_ROW_TWO, bio);

        try 
        {
            db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);
            Log.w("database message","Update successfully");
        }
        catch (Exception e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    }
    public int count_row()
    {
        int row=0;
            Cursor cursor;

        try
        {
                    cursor = db.query(
                    TABLE_NAME,
                    new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
                    null, null, null, null, null
            );

            cursor.moveToFirst();

            if (!cursor.isAfterLast())
            {
                do
                {
                    row++;
                }

                while (cursor.moveToNext());
            }
        }
        catch (SQLException e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    Log.w("row count..",""+row);
        return row;
    }



    public void getRow(long rowID)
    {
        Cursor cursor;

        try
        {
            cursor = db.query
            (
                    TABLE_NAME,
                    new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
                    TABLE_ROW_ID + "=" + rowID,
                    null, null, null, null, null
            );

            cursor.moveToFirst();

            if (!cursor.isAfterLast())
            {
                do
                {                   
                    Log.w("row ",""+cursor.getLong(0));
                    Log.w("row ",""+cursor.getString(1));
                    Log.w("row ",""+cursor.getFloat(2));

                }

                while (cursor.moveToNext());
            }
            cursor.close();
        }
        catch (SQLException e) 
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }
    public void getAllRows()
    {


        int i=0;
            Cursor cursor;

        try
        {
                    cursor = db.query(
                    TABLE_NAME,
                    new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
                    null, null, null, null, null
            );

            cursor.moveToFirst();

            if (!cursor.isAfterLast())
            {
                do
                {
                    Log.w("row "+i,""+cursor.getLong(0));
                    Log.w("row "+i,""+cursor.getString(1));
                    Log.w("row "+i,""+cursor.getFloat(2));

                    i++;
                }

                while (cursor.moveToNext());
            }
        }
        catch (SQLException e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }

    }
    private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
    {
        public CustomSQLiteOpenHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            String newTableQueryString = "create table " +
                                        TABLE_NAME +
                                        " (" +
                                        TABLE_ROW_ID + " integer primary key autoincrement not null," +
                                        TABLE_ROW_ONE + " text," +
                                        TABLE_ROW_TWO + " text" +
                                        ");";
                db.execSQL(newTableQueryString);

        }


        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {

        }
    }
}

public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    DBHelper database = new DBHelper(this);
    database.addRow("name1","bio1"); 
    database.addRow("name2","bio2");        
    database.addRow("name3","bio3"); 
    database.addRow("name4","bio4"); 
    database.addRow("name5","bio5");

    database.getAllRows();

    database.updateRow(2,"name20","bio20");

    database.getAllRows();

    database.deleteRow(2);

    database.getAllRows();
}

more detail http://mel-tools.mit.edu/code/SimpleContentProvider/doc/edu/mit/mobile/android/content/class-use/DBHelper.html

copy DBHelper class in your application I hope it is useful.....