How to search through a DB using a cursor( sqlite

Question

I am an android beginner. I am trying to design this simple application which takes the name of a city from the user using an editText View , compares that in the database , and returns the ZIP code of that city. Now ,I'm having a problem with the implementation of cursors. Please help.How can i query the database to fetch the corresponding code.

        EditText city;
    Button add,show1;
    RadioGroup choose;
    String k;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        try
        {   
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        //final ContentValues values = new ContentValues();
        city=(EditText)findViewById(R.id.editText1);
        add=(Button)findViewById(R.id.add);
        choose=(RadioGroup)findViewById(R.id.radio01);
        show1=(Button)findViewById(R.id.button1);
        k=city.getText().toString();
        createDatabase();
    }
        catch(Exception e)
        {

        }


show1.setOnClickListener(new View.OnClickListener() {


    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        db=openOrCreateDatabase(DATABASE_NAME,Context.MODE_PRIVATE,null);
        try{

        if(k == "CITY")
        {
            String[] result_columns=new String[]{"_id","CITY","CODE"};
            Cursor cursor = db.query(TABLE_NAME, result_columns, 
                    "CITY" +"=?", new String[]{"k"}, null, null, null);

    cursor.moveToFirst();

        String xnewcode=cursor.getString(0);
        Toast.makeText(activity1.this, xnewcode, Toast.LENGTH_LONG).show();
        cursor.moveToNext();

            //db.close();
        }}
        catch(Exception e)
        {
            Toast.makeText(activity1.this,"Fault in showing " + e,Toast.LENGTH_LONG).show();

        }

    }

});

Answer 1

I think that you've forgot some checks. However, it really depends on the language in which the cities are written - the SQLite operator "LIKE" is case-insensitive only for English letters. If your cities are in another language, consider storing them in upper/lower case in the database and converting the input string to upper/lower case in java (String.toUpper/LowerCase() ).

String[] result_columns=new String[]{"_id","CITY","CODE"};
        Cursor cursor = db.query(TABLE_NAME, result_columns, 
                "CITY" +" like '%?%'", new String[]{"k"}, null, null, null);

if(cursor != null)
{
  while(cursor.moveToNext())
  {
    String xnewcode=cursor.getString(1);
    Toast.makeText(activity1.this, xnewcode, Toast.LENGTH_LONG).show();
  }
}

You can store all results in an array and show them to the user, asking him to choose the right one.

EDIT: If you are not familiar, the "x LIKE Y" statement compares x to any substring from Y with length = x.length. It's pretty useful, because you can't guarantee that the user will enter the whole city name. So with my example if the user enters "york", the Cursor will return values like "new york", "yorkshire", "york" and so on.

Answer 2

 Cursor   mCursor = db.rawQuery("select ZIPCODE from TABLENAME where CITYNAME= ? ", new String[]{"NEWYORK"});

hope you got the single result only.

then to get it from(incase of string means) mCursor.getString(coloumn_id)

Answer 3

I think you forgot to get the result in loop

String[] result_columns=new String[]{"_id","CITY","CODE"};
        Cursor cursor = db.query(TABLE_NAME, result_columns, 
                "CITY" +"=?", new String[]{"k"}, null, null, null);

do {

                        String xnewcode = cursor.getString(0);
                        System.ouot.println("...display the result..."+xnewcode);
                    }while (cursor.moveToNext());
                    }

Have a look on Sample project

or

Tutorial on sqlite database for android

Thanks Deepak

Answer 4

Cursor cursor = db.rawQuery("select _id,city,code 
      FROM city where city_name like '"+s+"%' order by city", null);

s is the your string of enter characters in search box

use the below links it's very useful in android app development.

http://samir-mangroliya.blogspot.in/2012/05/android-sectioned-listview-with-search_6865.html