Android SQLite Database is an Open-Source embedded SQL database engine that provides a relational database management structure for storing user defined records in the form of tables. SQLite is light weight and supports all the features of relational database. Unlike other RDMBS systems, you need not maintain any server setup for this kind of database. Android SQLite Database is a cross platform and self-contained database. Android comes in with built-in SQLite database implementation and is included in API Level 1. In this tutorial, we will teach you how to write classes to handle all SQLite operations.
If your application creates a database, it will be by default saved in the directory: data/data/APP_PACKAGE_NAME/databases/DATABASENAME.
In order to view this path, you must open DDMS->File Explorer.
The android.database package contains all the necessary classes for working with database. The android.database.sqlite package contains the SQLite specific classes.
Creating and updating database with SQLiteOpenHelper:
To create and upgrade a database in your Android application, you should create a subclass of the SQLiteOpenHelper class. In the constructor of your subclass, call the 
super() method of SQLiteOpenHelper, specifying the database name and the current database version.
In this class, you need to override the following methods to create and update your database.
• onCreate() - is called by the framework, if the database is accessed but not yet created.
• onUpgrade () - This method should be called, if the database version is updated in your application code. This method allows you to update an existing database schema or drop the existing database and recreate it via the
Both methods receive an SQLiteDatabase object as a parameter, which is the Java representation of the database.
The SQLiteOpenHelper class uses the onCreate() method.getReadableDatabase() and getWriteableDatabase() methods to access a SQLiteDatabase object either in read or write mode.
SQLiteDatabase:
SQLiteDatabase is the base class and provides methods to open, query, update and close the database.
SQLiteDatabase mainly provides the following methods:
• insert()
• update()
• delete()
• execSQL() which allows to execute an SQL statement directly.
The object ContentValues allows to define key/values. The key represents the table column identifier and the value represents the content of the table record in this column. ContentValues can be used for insertion and updation of database entries.
Queries can be created by using the rawQuery() and query() methods or the SQLiteQueryBuilder class.
rawQuery() directly accepts an SQL select statement as input.
query() provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder is a convenience class that helps to build SQL queries.
Ex:
Create MainActivity.java under src/<your packagename>.
MainActivity.java:
[java]
public class MainActivity extends Activity{
Context context;
static double lat;
double lon;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
context=this;
final EditText ed=(EditText)findViewById(R.id.editText1);
Button insert=(Button)findViewById(R.id.button1);
Button update=(Button)findViewById(R.id.button2);
Button get=(Button)findViewById(R.id.button3);
Button delete=(Button)findViewById(R.id.button4);
//on click insert
insert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Bean b=new Bean();
b.setName(ed.getText().toString());
UserDatabase data=new UserDatabase(context);
data.open();
data.insert(b);
data.close();
Toast.makeText(context, "Your data inserted successfully", Toast.LENGTH_LONG).show();
}
});
//on click update
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Bean b=new Bean();
b.setName(ed.getText().toString());
UserDatabase data=new UserDatabase(context);
data.open();
data.updateName(b, 1);
data.close();
Toast.makeText(context, "Your data is updated successfully", Toast.LENGTH_LONG).show();
}
});
//on click get
get.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
UserDatabase data=new UserDatabase(context);
data.open();
List myname=data.getName1();
data.close();
Toast.makeText(context, "Your name="+myname, Toast.LENGTH_LONG).show();
}
});
//on click delete
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
UserDatabase data=new UserDatabase(context);
data.open();
data.delete("2");
data.close();
Toast.makeText(context, "Your data is deleted successfully", Toast.LENGTH_LONG).show();
}
});
}
}
[/java]
Create UserDatabase.java class under src/<your packagename>. This class has an inner class and extends SQLiteOpenHelper.
UserDatabase .java
[java]
public class UserDatabase
{
private final static String DATABASE_NAME="User1";
private final static int DATABASE_VERSION=1;
private final static String DATABASE_TABLE="user";
private final static String KEY_NAME="name";
public static final String KEY_ROWID1="_id1";
Context context;
UserdatabaseH userH;
SQLiteDatabase sql;
public static class UserdatabaseH extends SQLiteOpenHelper
{
public UserdatabaseH(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE " + DATABASE_TABLE +" ("+
KEY_ROWID1 + " INTEGER PRIMARY KEY AUTOINCREMENT,"+
KEY_NAME + " TEXT NOT NULL "+");"
);
System.out.println("created");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("drop table if exists "+DATABASE_TABLE);
onCreate(db);
}
}
UserDatabase(Context context)
{
this.context=context;
}
public UserDatabase open() throws SQLException
{
userH=new UserdatabaseH(context);
sql=userH.getWritableDatabase();
System.out.println("created1");
return this;
}
public void close()
{
userH.close();
System.out.println("created2");
}
public void insert(Bean b)
{
ContentValues c=new ContentValues();
c.put(KEY_NAME, b.getName());
System.out.println("created3");
sql.insert(DATABASE_TABLE, null, c);
}
public List getName1()
{
ArrayList list=new ArrayList();
String name="Select * from "+DATABASE_TABLE;
String name1 = null;
Cursor c=sql.rawQuery(name, null);
if(c.moveToFirst())
{
do{
name1=c.getString(c.getColumnIndex(KEY_NAME));
list.add(name1);
System.out.println(list+"ll");
}
while(c.moveToNext());
}
return list;
}
public void updateName(Bean b,int id)
{
ContentValues cv=new ContentValues();
cv.put(KEY_NAME, b.getName());
sql.update(DATABASE_TABLE, cv, KEY_ROWID1+"="+id, null);
}
/*public void delete(){
sql.execSQL("drop table if exists "+DATABASE_TABLE);
System.out.println("deleted");
}*/
public void delete(String Id)
{
try {
sql.delete(DATABASE_TABLE, KEY_ROWID1+"="+Integer.parseInt(Id), null);
}
catch(Exception e) {
e.printStackTrace();
}
}
}
[/java]
Create activity_main.xml under res/layout folder.
activity_main.xml:
[xml]
[/xml]
Create Bean.java under src/<your packagename> which is a JavaBean class. This class provides access to properties using getter and setter methods.
Bean.java:
[java]
public class Bean {
private String name;
Bean()
{
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
[/java]
AndroidManifest.xml:
[xml]
[/xml]
Output: