Android SQLite search by name

Question

I gave an example for working with SQLite in Android from here: http://www.cnblogs.com/pangblog/p/3327696.html

And I'm struggling to get contact by name. How to change the GetContact function to search by name?

DatabaseHandler:

public class DatabaseHandler extends SQLiteOpenHelper { //Database Version private static final int DATABASE_VERSION = 1; //Database Name private static final String DATABASE_NAME = "contactsManager"; //Contacts table name private static final String TABLE_CONTACTS = "contacts"; //Contacts Table Columns names private static final String KEY_ID = "id"; private static final String KEY_NAME = "name"; private static final String KEY_PH_NUM = "phone_number"; public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } // Creating Tables : CREATE TABLE table_name (column_name column_type); @Override public void onCreate(SQLiteDatabase db) { String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_PH_NUM + " TEXT" + ")"; db.execSQL(CREATE_CONTACTS_TABLE); } // Upgrading database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Drop older table if existed db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS); // Create tables again onCreate(db); } //Adding new contact void addContact(Contact contact) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_NAME, contact.getName()); // Contact Name values.put(KEY_PH_NUM, contact.getPhoneNumber()); // Contact Phone // Inserting Row db.insert(TABLE_CONTACTS, null, values); db.close(); // Closing database connection } // Getting single contact Contact getContact(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID, KEY_NAME, KEY_PH_NUM }, KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null, null, null); if (cursor != null) cursor.moveToFirst(); Contact contact = new Contact(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2)); return contact; } // Getting All Contacts public List<Contact> getAllContacts() { List<Contact> contactList = new ArrayList<Contact>(); // Select All Query ：SELECT * FROM tableName WHERE criteria String selectQuery = "SELECT * FROM " + TABLE_CONTACTS; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { Contact contact = new Contact(); contact.setID(Integer.parseInt(cursor.getString(0))); contact.setName(cursor.getString(1)); contact.setPhoneNumber(cursor.getString(2)); // Adding contact to list contactList.add(contact); } while (cursor.moveToNext()); } return contactList; } } 

Contact:

 public class Contact { int _id; String _name; String _phone_number; public Contact() { } public Contact(int id, String name, String phone_number) { this._id = id; this._name = name; this._phone_number = phone_number; } public Contact(String name, String phone_number) { this._name = name; this._phone_number = phone_number; } public int getID() { return this._id; } public void setID(int id) { this._id = id; } public String getName() { return this._name; } public void setName(String name) { this._name = name; } public String getPhoneNumber() { return this._phone_number; } public void setPhoneNumber(String phone_number) { this._phone_number = phone_number; } } 

Answer 1

The where clause in the database query in getContact is currently id = ? ? replaced by the id parameter. What you need to do to search by name is to change this part.

 // Getting single contact Contact getContact(String name) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID, KEY_NAME, KEY_PH_NUM }, KEY_NAME + "=?", new String[] { name }, null, null, null, null); //... 

You should also slightly modify the “working” code, as it is not very secure. The cursor may not be null (in practice), but cursor.moveToFirst() may / may not succeed if the database does not have such a name. If this fails, you will get an exception in cursor.getString(0) , because the cursor does not have a row to receive data.

Drop the null check and see if the cursor can move to the first position (not empty). You should also close the cursor as soon as you no longer need it.

 Contact contact = null; if (cursor.moveToFirst()) { contact = new Contact(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2)); } cursor.close(); // can return null if no contact was found. return contact; 

Answer 2

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String , java.lang.String [], java.lang.String, java.lang.String [ ], java. lang.String, java.lang.String, java.lang.String, java.lang.String)

use the documentation.

to do

 db.query(TABLE_CONTACTS, new String[] { KEY_ID, KEY_NAME, KEY_PH_NUM }, KEY_NAME+ "=?", new String[] { String.valueOf(NAME) }, null, null, null, null); 

and click the NAME variable at the beginning of the function.

Answer 3

You can search the following code in SQLite;

In MainActivity;

  search.addTextChangedListener(new TextWatcher() { public void afterTextChanged(Editable s) { } public void beforeTextChanged(CharSequence s, int start, int count, int after) { } public void onTextChanged(CharSequence s, int start, int before, int count) { adapter.getFilter().filter(s.toString()); } }); adapter.setFilterQueryProvider(new FilterQueryProvider() { public Cursor runQuery(CharSequence constraint) { return //Here you can filter data by any row , just change text replace of "subject" dbManager.fetchdatabyfilter(constraint.toString(),"subject"); } }); 

DatabaseHelper.java

  public Cursor fetchdatabyfilter(String inputText,String filtercolumn) throws SQLException { Cursor row = null; String query = "SELECT * FROM "+DatabaseHelper.TABLE_NAME; if (inputText == null || inputText.length () == 0) { row = database.rawQuery(query, null); }else { query = "SELECT * FROM "+DatabaseHelper.TABLE_NAME+" WHERE "+filtercolumn+" like '%"+inputText+"%'"; row = database.rawQuery(query, null); } if (row != null) { row.moveToFirst(); } return row; }