Database is important thing in programming. Many of our code always use data to be processed and saved. Just like any other programming environment, Android support database programming too. You can use default database supported by android, SQLiteDatabase. So, in this tutotial we are going to create database and making table in database. After that we will perform operation like, insert, update, delete and display data from databse.
We are going to use DatabaseHelper class, this class extends SQLiteOpenHelper. Now SQLiteOpenHelper is as shown below.
A helper class to manage database creation and version management.
You create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int)and optionally onOpen(SQLiteDatabase), and this class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.
We are also going to use class SQLiteDatabase, It is described as following,
Exposes methods to manage a SQLite database.
SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.
See the Notepad sample application in the SDK for an example of creating and managing a database.
Database names must be unique within an application, not across all applications.
Now, coming to our example, database name is “books” and database table is “titles” and we have following list of columns in our table titles
“_id”;
“isbn”;
“title”;
“publisher”;
Following is code of file DBAdapter.class, this handles all the functionality of database like creating database and table, insert, delete and update of table and fetching values from table. You can download pd file here DBAdapter
public class DBAdapter {
public static final String KEY_ROWID = "_id";
public static final String KEY_ISBN = "isbn";
public static final String KEY_TITLE = "title";
public static final String KEY_PUBLISHER = "publisher";
private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "books";
private static final String DATABASE_TABLE = "titles";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE =
"create table titles (_id integer primary key autoincrement, "
+ "isbn text not null, title text not null, "
+ "publisher text not null);";
private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;
public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}
private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS titles");
onCreate(db);
}
}
//---opens the database---
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}
//---closes the database---
public void close()
{
DBHelper.close();
}
//---insert a title into the database---
public long insertTitle(String isbn, String title, String publisher)
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_ISBN, isbn);
initialValues.put(KEY_TITLE, title);
initialValues.put(KEY_PUBLISHER, publisher);
return db.insert(DATABASE_TABLE, null, initialValues);
}
//---deletes a particular title---
public boolean deleteTitle(long rowId)
{
return db.delete(DATABASE_TABLE, KEY_ROWID +
"=" + rowId, null) > 0;
}
//---retrieves all the titles---
public Cursor getAllTitles()
{
return db.query(DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_ISBN,
KEY_TITLE,
KEY_PUBLISHER},
null,null,null,null,null);
}
//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException
{
Cursor mCursor =
db.query(true, DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_ISBN,
KEY_TITLE,
KEY_PUBLISHER
},
KEY_ROWID + "=" + rowId,
null,null,null,null,null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
//---updates a title---
public boolean updateTitle(long rowId, String isbn,
String title, String publisher)
{
ContentValues args = new ContentValues();
args.put(KEY_ISBN, isbn);
args.put(KEY_TITLE, title);
args.put(KEY_PUBLISHER, publisher);
return db.update(DATABASE_TABLE, args,
KEY_ROWID + "=" + rowId, null) > 0;
}
}
Now coming to our main file DatabaseActivity.class that extends Activity. Here we will create object of class DBAdapter and use the functionality of it. Here is the whole code. You can download pd file here DatabaseActivity.class
public class DatabaseActivity extends Activity {
DBAdapter db;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
db = new DBAdapter(this);
System.out.println("bool1");
insert();
disAll();
//update();
//dis(3);
//del(5);
}
private void update(){
db.open();
if (db.updateTitle(1,
"0470285818",
"C# 2008 Programmer's Reference",
"Wrox Press"))
Toast.makeText(this, "Update successful.",
Toast.LENGTH_LONG).show();
else
Toast.makeText(this, "Update failed.",
Toast.LENGTH_LONG).show();
//-------------------
//---retrieve the same title to verify---
Cursor c = db.getTitle(1);
if (c.moveToFirst())
DisplayTitle(c);
else
Toast.makeText(this, "No title found",
Toast.LENGTH_LONG).show();
//-------------------
db.close();
}
private void insert(){
db.open();
long id;
id = db.insertTitle(
"0470285819",
"Teach yourself Java",
"Wrox");
id = db.insertTitle(
"047017661y",
"Professional Windows 07 Gadgets Programming",
"Wrox");
System.out.println("bool211");
db.close();
}
private void disAll(){
db.open();
/*Cursor c = db.getAllTitles();
if (c.moveToFirst())
{
do {
System.out.println("bool2");
DisplayTitle(c);
} while (c.moveToNext());
}
else
System.out.println("boo3l");*/
try{
Cursor c = db.getAllTitles();
if (c.moveToFirst())
{
do {
System.out.println("bool2");
DisplayTitle(c);
} while (c.moveToNext());
}
}catch(Exception e){
System.out.println(e);
}
db.close();
}
private void dis(int j){
db.open();
Cursor c = db.getTitle(j);
if (c.moveToFirst())
DisplayTitle(c);
else
Toast.makeText(this, "No title found",
Toast.LENGTH_LONG).show();
db.close();
}
private void del(int j){
db.open();
if (db.deleteTitle(j))
Toast.makeText(this, "Delete successful.",
Toast.LENGTH_LONG).show();
else
Toast.makeText(this, "Delete failed.",
Toast.LENGTH_LONG).show();
db.close();
}
public void DisplayTitle(Cursor c)
{
System.out.println("bool");
Toast.makeText(this,
"id: " + c.getString(0) + "\n" +
"ISBN: " + c.getString(1) + "\n" +
"TITLE: " + c.getString(2) + "\n" +
"PUBLISHER: " + c.getString(3),
Toast.LENGTH_LONG).show();
}
}
Following are the links of other Post on Android.
Android : ContextMenu & SubMenu
Android : Sliding Drawer
Android Countdown Timer
Getting started with Android Map View: Displaying a location by Marker
Simple Progress Bar in Android using Thread
Android listview with image and text
Android : Connecting to MySQL using PHP
Manage & Developed by Sptechnolab.com
10 comments
Lenn Dolling
April 25, 2011 at 11:14 am (UTC 5.5) Link to this comment
whoo hoo! Nice database example.
[Translate]
Madhuri
July 6, 2011 at 5:35 am (UTC 5.5) Link to this comment
The tutorial is good. But do we need to change anything in main.xml and in manifest file? Also when I try to run the application it is displaying the application has stopped unexpectedly. Please try again. Can you tell me where I went wrong?
[Translate]
venkat
August 20, 2011 at 4:15 pm (UTC 5.5) Link to this comment
The material is nice to understand. Is there a need to change anything in main.xml and in manifest file?when Iam trying to run the application Iam getting that the application has stopped unexpectedly. Can you tell me the reason? Plz…..
[Translate]
Viren Gujariya
August 29, 2011 at 3:13 pm (UTC 5.5) Link to this comment
Tutorial is the best. i have wasted almost 4 to 5 hours to get database related tutorials. but this one is really helpful and successfully implemented without any errors… Thanks…
[Translate]
Ashvin Ajadiya
January 31, 2012 at 5:33 am (UTC 5.5) Link to this comment
Nice tutorial G8…Helpfull…!!!!!!!!!!
[Translate]
Vcitor
February 7, 2012 at 4:38 am (UTC 5.5) Link to this comment
Thanks for the awesome tutorial it was extremely helpful!
[Translate]
pradeep
February 17, 2012 at 8:26 am (UTC 5.5) Link to this comment
it is very nice tut, could u pls provide tuts on content providers and services in android
,i disparately need it……..
thanks in advance
pradeep.
[Translate]
Adnan
March 1, 2012 at 12:34 pm (UTC 5.5) Link to this comment
Thanks for presenting the great tutorial but I have one problem, how to delete all the rows from table with single command.
I have tried all the ways like
db.delete(table_name,null,null); but still not worked.
Can you help out me…
[Translate]
Adnan
March 2, 2012 at 5:46 am (UTC 5.5) Link to this comment
ok ok I got the solution…
[Translate]
Aji
April 17, 2012 at 12:33 pm (UTC 5.5) Link to this comment
hello bhargav I’m new in android programing
your tutorial is great I saw a very simple coding for android sqlite but when I try it but I found problem in DatabaseActivity there a lot of warning
here some warning I get :
The method update() from the type DatabaseActivity is never used locally.
The local variable id is never read.
The method dis(int) from the type DatabaseActivity is never used locally.
The method del(int) from the type DatabaseActivity is never used locally.
and do we need the xml coding too..??
I hope you can help me..or can you sent me the project of this tutorial
thanks.
[Translate]