Using Sqlite database in android

To quote from the website of sqlite
"SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactionalSQL database engine."
It is small database which an android programmer can use conveniently in his/her program.

Let us learn how to use sqlite database in steps.

Opening database : openOrCreateDatabase fuction can be used to open an existing db or create a new one if it does not exist.
SQLiteDatabase mydb;
mydb = mContext.openOrCreateDatabase("mysqdb",
SQLiteDatabase.OPEN_READWRITE, null);

 First parameter is name of database and second one is mode of opening the file. Third parameter if present will be cursorfactory which is used to allow sub classes of cursor to be returned from a query. You can even use openDatabase function. On success the db is returned. The function throws SqliteException if there is error.

Next let us try to create a table with 3 fields - id, name and salary. We need to have a string which stores the create table sql statement.
String sqlStatement = "create table if not exists  nameSal" +  
"(_id Integer PRIMARY KEY AUTOINCREMENT ,Name text, salary Integer)";
mydb.execSQL(sqlStatement);

Note that _id field is made primary key as well as auto increment which will ensure each row gets unique _id automatically.
if not exists clause ensures that if the table is already present, there will not be any error. And the function call just returns.

execSQL will execute the SQL statement and creates the table.

Next let us see, how to add records to the table. To add records to the table, you can use contentValues and insert statement.
ContentValues v=new ContentValues();
 v.put("name", "Anil");
 v.put("salary", 24000);  
 mydb.insert("nameSal" , null, v);

You store key-value pairs in contentValues and then use insert function which takes table name as first parameter, nullColumnhack as second parameter and contentValue as third parameter.

Next let us consider how to extract data from our table. A query function can be used for this purpose. Query will return a cursor.
mCursor = mydb.query(nameSal, null, null, null, null,
null, null);


Here first argument is the table name to query.
Second argument is the column list to return- if null all columns are returnred
Third argument is selection like where clause in select statement excluding where
Fourth argument are the values to be filled for ? in selection 
Fifth argument is group by clause 
sixth argument is having clause which denotes which row groups to be returned
seventh argument is order by column
eighth argument is maximum number of columns to be returned


Let us look at another example using query
Cursor c = mydb.query("nameSal",new String[]{"name","salary"},
"salary > 26000",
null,
null,null,
"salary ASC ",null);
Here the query will return the columns name and salary given by second argument, where salary is greater than 26000 (the condition is given by 3rd argument). The rows returned will be arranged in ascending order of salary(7th argument).

Once we get the cursor, we can iterate through the rows using loop. Look at the example below.
c.moveToFirst();
do{ String name = c.getString(0); int salary = c.getInt(1); Toast.makeText(this, "name is "+name+" salary is "+salary, 2000).show(); }while(c.moveToNext());

c.moveToNext method will return false when the end is reached.

Using multiselect list with a dialog

AlertDialog is a versatile dialog. It can be used even for displaying a list where multiple values can be checked.

First you create a alertdialog.
AlertDialog.Builder  d = new AlertDialog.Builder(yourcontext);


Next you create the array which holds the items to be displayed

 String elements [] = {"Burger","Pizza","Cake","Coke","Fruits"};


Next you add multichoiceitems to the dialog

d.setMultiChoiceItems(elements, null , new OnMultiChoiceClickListener() {
 @Override
 public void onClick(DialogInterface dialog, int which, boolean isChecked) {
       if(isChecked){
          String str = elements[which];
          Toast.makeText(youractivity.this,
                    "you have selected"+str,
                     Toast.LENGTH_LONG).show();
        }
   }
 });
d.setPositiveButton("Save", new OnClickListener() {
   @Override
   public void onClick(DialogInterface dialog, int which) {
yourOnClickMethod();
   }
});
d.show();
Now our dialog looks something like this

 The second parameter to setMultiChoiceItems is a boolean array, which will have true for all items which must be checked. If you do not want any to be checked, it can be null as we have used.
e.g
boolean selected[]= {true,false,false,true,true};
d.setMultiChoiceItems(elements,selected,
new OnMultiChoiceClickListener(){
                    --------
                    -------
});
With this modification, initially first, fourth and fifth items will be checked.