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