jeudi 25 juin 2015

Sqlite FTS search returns nothing

I am new i android! I use a pre-populated sqlite database in my app and I want use full text search functionality on it,I used A class that has several methods:

public class DBAdapter {
static final String KEY_ROWID = "_id";
static final String KEY_NAME = "title";
static final String KEY_EMAIL = "content";
static final String KEY_NUM = "fav";
static final String TAG = "DBAdapter";
static final String DATABASE_NAME = "mygf";
static final String DATABASE_TABLE = "tbl";
static final int DATABASE_VERSION = 1;
static final String DATABASE_CREATE =
        "create table tbl (_id integer primary key autoincrement, "
                + "title text not null, content text not null, fav text not null);";
final Context context;
DatabaseHelper DBHelper;
SQLiteDatabase db;
  //constructor
public DBAdapter(Context ctx)
{
    this. context = ctx;
    DBHelper = new DatabaseHelper(context);
}


private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        try {
            //db.execSQL(DATABASE_CREATE);
            db.execSQL(
                    "CREATE VIRTUAL TABLE ["+DATABASE_TABLE+"] USING FTS3 (" +
                        "["+KEY_NAME+"] TEXT," +
                        "["+KEY_EMAIL+"] TEXT," +
                        "["+KEY_NUM+"] TEXT" +
                    ");"
                );

        } catch (SQLException e) {
            e.printStackTrace();
        }

        //---------------------------------


        //---------------------------------
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Log. w(TAG, "Upgrading database from version " + oldVersion +  "to "
                + newVersion +  "which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
}
//---opens the database---
public DBAdapter open() throws SQLException
{
    db = DBHelper.getWritableDatabase();
    return this;
}
//---closes the database---
public void close()
{
    DBHelper.close();
}
//---insert a contact into the database---
public long insertContact(String title, String content, String fav)
{
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, title);
    initialValues.put(KEY_EMAIL, content);
    initialValues.put(KEY_NUM, fav);
    return db.insert(DATABASE_TABLE, null, initialValues);
}
//---deletes a particular contact---
public boolean deleteContact(long rowId)
{
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
//---retrieves all the contacts---
public Cursor getAllContacts()
{
    return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
            KEY_EMAIL, KEY_NUM}, null, null, null, null, null);
}
//---retrieves a particular contact---
public Cursor getContact(long rowId) throws SQLException
{
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                            KEY_NAME, KEY_EMAIL, KEY_NUM}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
//---updates a contact---
public boolean updateContact(long rowId, String title, String content, String fav)
{
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, title);
    args.put(KEY_EMAIL, content);
    args.put(KEY_NUM, fav);
    return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}   
public boolean closed() {
    this.db.close();
    return !this.db.isOpen();
}

/**
 * The search method
 * Uses the full text search 3 virtual table and the MATCH function from SQLite to search for data
 * @see http://ift.tt/1gjfYJZ to know more about the syntax
 * @param search {@link String} with the search expression
 * @return {@link LinkedList} with the {@link String} search results
 */
public LinkedList<String> search(String search) {

    LinkedList<String> results = new LinkedList<String>();
    Cursor cursor = null;
    try{
        cursor = this.db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_EMAIL, KEY_NUM}, DATABASE_TABLE + " MATCH ?", new String[] {search}, null, null, null, null);

        if(cursor!=null && cursor.getCount()>0 && cursor.moveToFirst()){
            int iUsername = cursor.getColumnIndex(KEY_NAME);
            int iFullname = cursor.getColumnIndex(KEY_EMAIL);
            int iEmail = cursor.getColumnIndex(KEY_NUM);

            do{
                results.add(
                    new String(
                        "title: "+cursor.getString(iUsername) +
                        ", content: "+cursor.getString(iFullname) +
                        ", fav: "+cursor.getString(iEmail)
                    )
                );
            }while(cursor.moveToNext());
        }
    }catch(Exception e){
        Log.e("payam", "An error occurred while searching for "+search+": "+e.toString(), e);
    }
    //finally{
      //  if(cursor!=null && !cursor.isClosed()){
        //    cursor.close();
        //}
   // }

    return results;
}
}

I was copied my database into device and I write a search Activity:

public class Search extends Activity {

//private DBAdapter dbAdapter;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_search);

    //dbAdapter = new DBAdapter(this);
    final DBAdapter db5 = new DBAdapter(this);
    db5.open();

    ////////////////
    Cursor c = db5.getContact(3);
    Toast.makeText(getBaseContext(), c.getString(2), Toast.LENGTH_SHORT).show();

    //////////////

    Button button = (Button) findViewById(R.id.btnSearch);
    final EditText etSearch = (EditText) findViewById(R.id.etSearch);
    final TextView tvResults = (TextView) findViewById(R.id.tvResults);
    button.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {
            db5.open();
            LinkedList<String> results = db5.search(etSearch.getText().toString());

            if(results.isEmpty()){
                tvResults.setText("No results found");
            }else{
                Iterator<String> i = results.iterator();
                tvResults.setText("");
                while(i.hasNext()){
                    tvResults.setText(tvResults.getText()+i.next()+"\n");
                }
            }
        }
    });
    db5.close();
}
}

But unfortunately the search result is always nothing, I guess that the problem maybe rises from sql command in DBAdapter class:

            cursor = this.db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_EMAIL, KEY_NUM}, DATABASE_TABLE + " MATCH ?", new String[] {search}, null, null, null, null);

But I have no idea what is true form!

Aucun commentaire:

Enregistrer un commentaire