java - 如何使用常规表中的内容填充 fts 虚拟表？

Question

我刚刚发现可以使用虚拟表来搜索数据库内容。然而，我已经坐了两天了，我想我每过一个小时就变得更愚蠢。有人可以给我提供一个简单的例子来说明如何做到这一点吗？

这就是我所在的位置:我有一个数据库类:

public class DBHelper {

private static final String TAG = DBHelper.class.getSimpleName();

//Database config
private static int DATABASE_VERSION = 2;
public static String DATABASE_NAME = "shoppingdatabase.db";

//Item table config
private static final String ITEM_TABLE_NAME = "item_table";
private static final String ITEM_TABLE_COLUMN_ID = "_id";
private static final String ITEM_TABLE_COLUMN_ITEM_NAME = "item_name";
private static final String ITEM_TABLE_COLUMN_ITEM_SIZE = "item_size";
private static final String ITEM_TABLE_COLUMN_ITEM_PRICE = "item_price";
private static final String ITEM_TABLE_COLUMN_ITEM_BRAND = "item_brand";
private static final String ITEM_TABLE_COLUMN_ITEM_CATAGORY = "item_catagory";

//Viritual table config
public static final String KEY_ROWID = "rowid";
public static final String KEY_ITEM = "name";
public static final String KEY_SIZE = "size";
public static final String KEY_PRICE = "price";
public static final String KEY_BRAND = "brand";
public static final String KEY_CATAGORY = "catagory";
public static final String KEY_SEARCH = "searchData";
private static final String FTS_VIRTUAL_TABLE = "ItemInfo";

private DatabaseHelper dbHelper;
private SQLiteDatabase db;

public DBHelper(Context aContext){
    dbHelper = new DatabaseHelper(aContext);
    db = dbHelper.getWritableDatabase();
}

//I use this method in my AddItem class for the user to add an item to the database

public void addItem(String iName, String iSize, String iPrice, String iBrand, String iCatagory){
    ContentValues cv = new ContentValues();
    cv.put(ITEM_TABLE_COLUMN_ITEM_NAME, iName);
    cv.put(ITEM_TABLE_COLUMN_ITEM_SIZE, iSize);
    cv.put(ITEM_TABLE_COLUMN_ITEM_PRICE, iPrice);
    cv.put(ITEM_TABLE_COLUMN_ITEM_BRAND, iBrand);
    cv.put(ITEM_TABLE_COLUMN_ITEM_CATAGORY, iCatagory);

    db.insert(ITEM_TABLE_NAME, null, cv);
}


public void populateVT() {

}

    private class DatabaseHelper extends SQLiteOpenHelper{
    public DatabaseHelper(Context aContext){
        super(aContext, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqliteDB){
        String buildSQL = "CREATE TABLE " + ITEM_TABLE_NAME + "( "
                            + ITEM_TABLE_COLUMN_ID + " INTEGER PRIMARY KEY, "
                            + ITEM_TABLE_COLUMN_ITEM_NAME + " TEXT, "
                            + ITEM_TABLE_COLUMN_ITEM_SIZE + " TEXT, "
                            + ITEM_TABLE_COLUMN_ITEM_PRICE + " TEXT, "
                            + ITEM_TABLE_COLUMN_ITEM_BRAND + " TEXT, "
                            + ITEM_TABLE_COLUMN_ITEM_CATAGORY + " TEXT )";

        String buildSQL2 = "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
                KEY_ITEM + "," +
                KEY_SIZE + "," +
                KEY_PRICE + "," +
                KEY_BRAND + "," +
                KEY_CATAGORY + "," +
                KEY_SEARCH + "," +
                " UNIQUE (" + KEY_ITEM + "));";

        Log.d(TAG, "onCreate SQL: " + buildSQL);
        Log.d(TAG, "onCreate SQL: " + buildSQL2);
        sqliteDB.execSQL(buildSQL);
        sqliteDB.execSQL(buildSQL2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqliteDB, int oldVersion, int newVersion){
        String buildSQL = "DROP TABLE IF EXISTS " + ITEM_TABLE_NAME;
        String buildSQL2 = "DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE;

        Log.d(TAG, "onUpgrade SQL: " + buildSQL);
        Log.d(TAG, "onUpgrade SQL: " + buildSQL2);
        sqliteDB.execSQL(buildSQL);
        sqliteDB.execSQL(buildSQL2);
        onCreate(sqliteDB);
    }
}

}

如何使用 populateVT() 用以下内容填充虚拟表ITEM_TABLE_NAME？我应该在哪里调用它，以便每次用户向数据库添加内容时它都会更新？

Answer 1

这就是我解决问题的方法。

在我的数据库类中，我添加了这些方法:

    public long createItem(String name, String size, String price, String brand) {

    ContentValues initialValues = new ContentValues();
    String searchValue =     name + " " + 
                            size + " " + 
                            price + " " + 
                            brand;
    initialValues.put(KEY_ITEM, name);
    initialValues.put(KEY_SIZE, size);
    initialValues.put(KEY_PRICE, price);
    initialValues.put(KEY_BRAND, brand);
    initialValues.put(KEY_CATAGORY, catagory);
    initialValues.put(KEY_SEARCH, searchValue);

    return db.insert(FTS_VIRTUAL_TABLE, null, initialValues);
}


public Cursor listAll(){
    String buildSQL = "SELECT * FROM " + ITEM_TABLE_NAME;
    Log.d(TAG, "listAll SQL: " + buildSQL);

    return db.rawQuery(buildSQL, null);
}

    public boolean deleteAllItems() {

    int doneDelete = 0;
    doneDelete = db.delete(FTS_VIRTUAL_TABLE, null , null);
    Log.w(TAG, Integer.toString(doneDelete));
    return doneDelete > 0;

}

在我的 MainActivity 中，我使用游标来迭代我的原始表并用内容填充我的虚拟表:

    @Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    setTitle("");
    dbHelper = new DBHelper(this);
    dbHelper.deleteAllCustomers();
    fillVT();
}

public void fillVT(){
    String a, b, x, y, z;
        Cursor c = dbHelper.listAll();
        if(c.moveToFirst()){
            do{
                a = c.getString(1);
                b = c.getString(2);
                x = c.getString(3);
                y = c.getString(4);
                z = c.getString(5);

                dbHelper.createCustomer(a, b, x, y, z);     
            }while(c.moveToNext());
        }
    }

@Override
protected void onActivityResult(int reqCode, int resCode, Intent data){
    super.onActivityResult(reqCode, resCode, data);

    if(reqCode == ENTER_DATA_REQUEST_CODE && resCode == RESULT_OK){
        dbHelper.addItem(data.getExtras().getString("tag_item_item_name"),
                            data.getExtras().getString("tag_item_item_size"),
                            data.getExtras().getString("tag_item_item_price"),
                            data.getExtras().getString("tag_item_item_brand"),
                            data.getExtras().getString("tag_item_item_catagory"));
        dbHelper.deleteAllCustomers();
        fillVT();
        cursorAdapter.changeCursor(dbHelper.listAll());
    }
}

我将 dbHelper.deleteAllCustomers() 和 fillVT() 放在 onActivityResult() 中，以便每次添加新项目时都会更新虚拟表