Skip to main content

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, transactional SQL 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.

Comments

Popular posts from this blog

Simple ListView Adapter and list item select

When you are using a listview in your applications many a times you will write your own adapter to display item. But if your list is very simple showing a list of strings, you can use inbuilt adapters like ArrayAdapter, SimpleCursorAdapter etc.

ArrayAdapterLet us look at an example

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" > <ListView android:layout_height="wrap_content" android:id="@+id/listView1" android:layout_width="match_parent" android:layout_margin="20dp"> </ListView> </LinearLayout>

And add these lines to the onCreate method of the activity.

super.onCreate(savedInstanceState); …

Drawables in Android - Layer drawable

Let us see how to use layer drawable. You can have two or more bitmaps on different layers to create such a drawable

Using xml:

You should use layer-list in your xml file to create layerdrawable. Here is layer.xml
<?xml version="1.0" encoding="utf-8"?> <layer-list xmlns:android="http://schemas.android.com/apk/res/android"> <item> <bitmap android:src="@drawable/whiteicon" android:gravity="top|left"/> </item> <item> <bitmap android:src="@drawable/blueicon" android:gravity="top|left"/> </item> <item> <bitmap android:src="@drawable/redicon" android:gravity="top|left"/> </item> </layer-list>
We are using three different bitmaps whiteicon.png, redicon.png and blueicon.png which are present in /res/drawable/mdpi folder. All these are of different sizes and aligned to top left. This drawab…

DatePickers in Android

DatePicker is a view which lets the user select a date showing a calendar or 3 spinners for dd, mm and yy.

Let us see how to use this.
Easy Method: To use a DatePicker you can use DatePickerDialog. This can be created and shown programmatically too.

publicvoidonCreate(Bundle b){/*********/ Button btn =(Button)findViewById(R.id.btn); btn.setOnClickListener(new View.OnClickListener(){@OverridepublicvoidonClick(View v){ showPickerDialog();}});}privatevoidshowPickerDialog(){ DatePickerDialog dtPickerDlg =new DatePickerDialog(this,this, 2017,10,20); dtPickerDialog.show();}

In our xml file, let us have a button and in the onclick listener of the button, let us display the date picker dialog - we call showPickerDialog.

In showPickerDialog,  we are using a random date to initialize.

The first  parameter is the context. Second parameter is the class which implements the interface OnDateSetListener. Next three parameters are year, month (Jan is 0) and …