Explain database programming in Android.
- For database programming, android framework provides rich set of classes and methods. The most important classes are SQLiteOpenHelper and SQLiteDatabase.
- There is no inbuilt UI tool for creating database in android framework, but you can use third party tool. So for creating database follow the following steps.
Procedure for creating the database.
Create a new class that extends from SQLiteOpenHelper class. Implement the
constructor, onCreate() and
onUpgrade() methods.
publicclass DBHelper extendsSQLiteOpenHelper
{
public DBHelper(Context context, String name, CursorFactory factory,
int version)
{
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
publicvoidonCreate(SQLiteDatabase arg0)
{
// TODO Auto-generated method stub
}
@Override
publicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
// TODO Auto-generated method stub
}
}
SQLiteOpenHelper class creates the new database according to your schema. It opens the database if exists and upgrades it, as necessary.
The constructor call super (context, name, factory, version); is used to setup the database.
onCreate() method will be called for the first time when the Android application is run. The code for creation of database table should be written in onCreate() method.
onUpgrade() method is called when your database or table needs to be upgraded. Use this method to drop tables, add tables, add or delete any column in the table. Always remember that each time you have to change the version number.
SQLiteDatabase is another important class that is provided by Android frame work. It contains methods to create table, and execute SQL commands.
For example: execSQL(String SQL) Return type of this method is void. So, this method does not work for those SQL queries, which return some records or value.
For example: select query statement will not work with execSQL method. This method works fine with statements like Create table, Drop table, Alter table, etc. If SQL statement is not correct, then it will throw SQLException. Multiple statements separated semicolons are not supported.
Define the Schema.
Database schema means, defining the structure of database and table. Define the database name, table name, columns name and its type, triggers if exists, aggregate function if exists and so on.
| ID | Name | Address |
|---|
| 1 | XYZ | Pune |
| 2 | ABC | Delhi |
| 3 | PQR | Bangalore |
Suppose that database name is
MyDatabase and table name is
MyTable that has three columns as above.
First we will declare the following string and integer variable.
private static final String DATABASE_NAME = ”MyDatabase” ;
private static final String TABLE_NAME = ”MyTable” ;
private static final String UID = ”_ID” ;
private static final String NAME = ”Name” ;
private static final String ADDRESS = ”Address” ;
private static final int VERSION_NO = 1;
Note: The above written schema should be written in sub class of
SQLiteOpenHelper.
Process for inserting data into database table.
ContentValues class is used to insert the data into database table. It works in key value pair. Referring the above table, suppose you want to insert the ID, name and Address field data, then the code should be as given below:
ContentValues values = new ContentValues();
values.put(DBHelper.UID, id);
values.put(DBHelper.NAME, name);
values.put(DBHelper.ADDRESS, address);
long result = db.insert(DBHelper.TABLE_NAME, null, values);
Here
DBHelper is the user defined sub class that extends from
SQLiteOpenHelper.
For more detail please refer below given example.- Insert() method takes three parameters. The first parameter is the table name, second parameter is NullColumnHack, and third parameter is contentvalues object.
- Inserting a completely empty contentvalues in table simply does not work, but if you want to insert complete null value in all fields of a particular record, then specify the name of any one column in your table that can be null.
For example
ContentValues values = new ContentValues();
long result = db.insert(DBHelper.TABLE_NAME, DBHelper.NAME, values);
Process for deleting data from database table.
For deleting records from table, SQLiteDatabase has delete() method that takes three parameters and returns integer type (affected number of rows).
Int delete (String table,String whereClause, String [] whereArgs)- The first parameter is the name of table.
- Second parameter is where clause. If you pass null value, then all records will be deleted. You can also include? It will be replaced by values from whereArgs.
- The third parameter is whereArgs. Whatever value you provide in this argument, question mark in DBHelper.NAME+ "=?” replaced by whereArgs value.
SQLiteDatabase db = getWritableDatabase();
String []whereArgs = {“careerRide”};
int i = db.delete(DBHelper.TABLE_NAME, DBHelper.NAME+ " =?", whereArgs);
Give example to create database and select the record using SQLiteOpenHelper and SQLiteDatabse class.
Following is the content of res/layout/activity_main.xml file:
<RelativeLayoutxmlns:android = "http://schemas.android.com/apk/res/android"
xmlns:tools = "http://schemas.android.com/tools"
android:layout_width = "match_parent"
android:layout_height = "match_parent"
android:paddingBottom = "@dimen/activity_vertical_margin"
android:paddingLeft = "@dimen/activity_horizontal_margin"
android:paddingRight = "@dimen/activity_horizontal_margin"
android:paddingTop = "@dimen/activity_vertical_margin"
tools:context = ".MainActivity">
<Button
android:id = "@+id/btnNext"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignBottom = "@+id/btnSave"
android:layout_marginLeft = "35dp"
android:layout_toRightOf = "@+id/btnSave"
android:text = "Show Data"
android:onClick = "showData"
/>
<TextView
android:id = "@+id/textView2"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignLeft = "@+id/textView1"
android:layout_marginTop = "70dp"
android:text = "Name"
android:textSize = "15dp"
/>
<Button
android:id = "@+id/btnSave"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_below = "@+id/textView2"
android:layout_marginTop = "96dp"
android:layout_toLeftOf = "@+id/editText1"
android:text = "Save"
android:onClick = "saveData"
/>
<TextView
android:id = "@+id/textView1"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignLeft = "@+id/btnSave"
android:layout_alignParentTop = "true"
android:layout_marginTop = "30dp"
android:text = "UserID"
android:textSize = "15dp"/>
<requestFocus/>
<EditText
android:id = "@+id/txtUserID"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_above = "@+id/txtName"
android:layout_alignLeft = "@+id/txtName"
android:ems = "10"/>
<TextView
android:id = "@+id/textView3"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignLeft = "@+id/textView2"
android:layout_below = "@+id/textView2"
android:layout_marginTop = "26dp"
android:text = "Address"
android:textSize = "15dp"
/>
<EditText
android:id = "@+id/txtAddress"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignBaseline = "@+id/textView3"
android:layout_alignBottom = "@+id/textView3"
android:layout_toRightOf = "@+id/btnSave"
android:ems = "10"/>
<EditText
android:id = "@+id/txtName"
android:layout_width = "wrap_content"
android:layout_height = "wrap_content"
android:layout_alignBaseline = "@+id/textView2"
android:layout_alignBottom = "@+id/textView2"
android:layout_alignLeft = "@+id/txtAddress"
android:ems = "10"/>
</RelativeLayout>
Following is the content of the modified DBHelper.java file
Create a sub class that will extend from SQLiteOpenHelper
publicclass DBHelper extends SQLiteOpenHelper
{
privatestaticfinal String DATABASE_NAME = "MyDatabase" ;
privatestaticfinal String TABLE_NAME = "MyTable" ;
privatestaticfinal String UID = "_ID" ;
privatestaticfinal String NAME = "Name" ;
privatestaticfinal String ADDRESS = "Address" ;
privatestaticfinalintVERSION_NO = 2;
privatestaticfinal String CREATE_TABLE = "create table "+TABLE_NAME+"("+UID+" integer,"+NAME+" varchar,"+ADDRESS+");";
privatestaticfinal String DROP_TABLE = "drop table if exists "+TABLE_NAME;
Context c;
public DBHelper(Context context)
{
super(context, NAME, null, VERSION_NO);
c = context;
Toast.makeText(c, "Constructor is called",Toast.LENGTH_LONG).show();
}
@Override
publicvoid onCreate(SQLiteDatabase db)
{
// TODO Auto-generated method stub
db.execSQL(CREATE_TABLE);
Toast.makeText(c, "onCreate method is called",Toast.LENGTH_LONG).show();
}
@Override
publicvoid onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
// TODO Auto-generated method stub
db.execSQL(DROP_TABLE);
onCreate(db);
Toast.makeText(c, "onUpgrade method is called",Toast.LENGTH_LONG).show();
}
publiclong addUser(String id, String name, String address)
{
SQLiteDatabase db=getWritableDatabase();
ContentValues values=new ContentValues();
values.put(DBHelper.UID, id);
values.put(DBHelper.NAME, name);
values.put(DBHelper.ADDRESS, address);
long result = db.insert(DBHelper.TABLE_NAME, null, values);
return result;
}
public StringBuffer getRecords()
{
StringBuffer res=null;
SQLiteDatabase db=getWritableDatabase();
String []columns = {DBHelper.UID,DBHelper.NAME,DBHelper.ADDRESS};
Cursor cursor=db.query(DBHelper.TABLE_NAME, columns, null, null, null, null, null);
while(cursor.moveToNext())
{
String ID = cursor.getString(0);
String Name = cursor.getString (1);
String Address = cursor.getString(2);
StringBuffer sb = new StringBuffer();
res = sb.append("ID="+ID+"\n Name="+Name+"\n Address="+Address);
}
returnres;
}
}
Following is the content of the modified MainActivity.java file
publicclass MainActivity extends Activity
{
EditText txtID,txtUserName,txtUserAddress;
DBHelper obj;
@Override
protectedvoid onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
txtID = (EditText)findViewById(R.id.txtUserID);
txtUserName = (EditText)findViewById(R.id.txtName);
txtUserAddress = (EditText)findViewById(R.id.txtAddress);
}
publicvoid saveData(View v)
{
String id = txtID.getText().toString();
String name = txtUserName.getText().toString();
String address = txtUserAddress.getText().toString();
obj = new DBHelper(this);
long result = obj.addUser(id, name, address);
if(result>0)
{
Toast.makeText(this, "Record saved successfully",Toast.LENGTH_LONG).show();
}
else
{
Toast.makeText(this, "Problem in saving the record",Toast.LENGTH_LONG).show();
}
}
publicvoid showData(View v)
{
obj = new DBHelper(this);
StringBuffer sb = obj.getRecords();
Toast.makeText(this,sb,Toast.LENGTH_LONG).show();
}
}
