android - 在用户操作应用程序时更新 Android SQLite 数据库表并避免错误/糟糕的用户体验

Question

我的 Android 应用程序在 SQLite 数据库中有一个产品表，该表在用户安装时预先填充。可以从 Azure Web 服务更新该表。

我不想只从 Web 服务返回更新的记录并用更新的记录填充 SQLite 表，而是想简单地将 2900 条记录返回到应用程序。这是因为大多数产品都会发生变化。打开应用程序时，使用 SQL 查询删除产品表，并将 ksoap2 响应发送到插入数据库的产品对象。

在对产品表进行此更新时，我希望用户能够在不中断的情况下使用该应用程序。如果删除了 Products 表，那么他们将无法正常运行 App。

我有哪些选择？我可以填充一个临时产品表吗？当服务完成填充它时，我可以将它复制到“实时”产品表中。或者我可以完全删除 Product 表并将临时表重命名为“Product”吗？

或者我的做法是完全错误的。任何建议将不胜感激。代码摘录如下:

 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

DBHelper 类遵循Respsitory 模式。对底层数据的访问包含在“存储库”类中。这释放了您的主要逻辑以专注于业务逻辑并将所有数据处理责任推给存储库。根据您的应用程序的复杂性，存储库可能充当“外观”，其中调用被简单地代理到负责域对象的存储库。

下面的代码为 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();
}

更多细节 http://mel-tools.mit.edu/code/SimpleContentProvider/doc/edu/mit/mobile/android/content/class-use/DBHelper.html

在您的应用程序中复制 DBHelper 类我希望它有用......