3

I have a db with 10 tables and each with 5 to 10 columns and am expecting not more than 500 records for the whole db.

I want to implement a search feature in the app where I search the whole db for a keyword and display a custom listview for all the results.

I tried searching for ways to implement this but haven't found a solution for SQLite in android. There are SQL server implementations of this.

The way I see it at the moment is to write a query for every table and search every column and then filter the result to populate my custom listview.

public Cursor searchDB(String query) { 

return db.query(true, DB_TABLE, 
new String[] { KEY_ROWID, KEY_COLUMN1, KEY_COLUMN2, ...  KEY_COLUMN10},
KEY_COLUMN1 + " LIKE" + "'%" + query + "%' OR " + 
KEY_COLUMN2 + " LIKE" + "'%" + query + "%' OR " +  ... 
KEY_COLUMN10 + " LIKE" + "'%" + query + "%'", 
null, null, null, null, null); 
}

Appreciate opinions on this matter and any help with implementation.

Community
  • 1
  • 1
Aman Agarwal
  • 589
  • 1
  • 4
  • 22

2 Answers2

2

I would move all the search stuff as one database-view into the database

CREATE VIEW [search] AS
select 't1' as tab, id as id, col1+col2+col3 as key from t1
union
select 't2' as tab, id as id, colA+colB+colC as key from t2

and have a simple query to find the results

select tab, id from search  where key like '%12%'

If you later decide that you need an additional search-field or an additional search-table all you have to do is updating the database view

k3b
  • 14,517
  • 7
  • 53
  • 85
0

More effective I think would be query like:

select KEY_COLUMN1+' '+KEY_COLUMN2+' '+KEY_COLUMN3 from mytable
where KEY_COLUMN1+' '+KEY_COLUMN2+' '+KEY_COLUMN3 like %pattern%
Barmaley
  • 16,638
  • 18
  • 73
  • 146