Error in searching a database for a particular record

201 views Asked by At

I have an SQLite database table called TABLE_CONTENTS made up of objects of type contact. Each contact has 2 variables: int id and String name. I want to search the SQLite database table for a specific id. For this I've made the isPresent() method in the class DatabaseHandler . It takes an int id and searches if an object with that id is present in the table and returns true or false accordingly.

The following code creates a database and adds 2 objects into it with ids 1 and 2 . It then tries to search for id=3 using isPresent() . This should return false causing the text id 3 is absent to show . However, it's returning true and the text displayed is id 3 is present .

What is wrong in this code? What is the correct code for isPresent()?

MainActivity.java

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity
{

@Override
protected void onCreate(Bundle savedInstanceState)
{
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    DatabaseHandler db = new DatabaseHandler(this);

    // Inserting Contacts
    db.addContact(new Contact(1, "one"));
    db.addContact(new Contact(2, "two"));



    TextView text=(TextView) findViewById(R.id.text);
    text.setText(db.isPresent(3) ? "id 3 is present" : "id 3 is absent");


}

DatabaseHandler.java

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper
{
 private static final int DATABASE_VERSION=1;
 private static final String DATABASE_NAME="contactsManager";
 private static final String TABLE_CONTACTS="contacts";
 private static final String KEY_ID="id";
 private static final String KEY_NAME="name";

public DatabaseHandler(Context context)
{
    super(context, DATABASE_NAME, null, DATABASE_VERSION);}

@Override
public void onCreate(SQLiteDatabase db)
{
    String CREATE_CONTACTS_TABLE="CREATE TABLE "+TABLE_CONTACTS+"("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_NAME+" TEXT "+")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
 db.execSQL("DROP TABLE IF EXISTS"+TABLE_CONTACTS);
    onCreate(db);
}

void addContact(Contact contact)
{
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.name);
    values.put(KEY_ID, contact.id);

    // Inserting Row
    db.insert(TABLE_CONTACTS, null, values);
    //2nd argument is String containing nullColumnHack
    db.close(); // Closing database connection
}


// not working properly..always returning true
public boolean isPresent(int id)
{
    SQLiteDatabase sqldb = this.getReadableDatabase();
    String Query = "Select * from " + TABLE_CONTACTS ;
    Cursor cursor = sqldb.rawQuery(Query, null);
    cursor.moveToLast(); //if you not place this cursor.getCount() always give same integer (1) or current position of cursor.

    if(cursor.getCount()<=0)
    {
        return false;
    }

    return true;
}




Contact getContact(int id)
{
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
                    KEY_NAME}, 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));
    // return contact
    return contact;
}

public int updateContact(Contact contact)
{
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.name);


    // updating row
    return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
            new String[] { String.valueOf(contact.id) });
}


}






}

contact class

public class Contact
{
public String name;
public int id;

Contact(int id, String name)
{
    this.id=id;
    this.name=name;
}


}
2

There are 2 answers

0
Raghavendra On BEST ANSWER

Try,

As I see u haven't added any condition to select particular row. So, its just like get all not selected one. So, cursor.getCount() <= 0 is always false because u have added 2 records.

In isPresent(int id) method update the query as below and try once,

String Query = "Select * from " + TABLE_CONTACTS + " WHERE " + KEY_ID + "='"+ id + "'";
0
Vivek Mishra On

You haven't added any WHERE Clause in your query to search records according to id, therefore it always select all rows from your database which will always return count >0, so that's why your code is not working.

You have to modify your Select query to include WHERE clause in it something like this.

String yourQuery= "Select * from " + TABLE_CONTACTS + " WHERE " + KEY_ID + "='"+ id + "'";