SQLite database is a relational database which is embedded in android system and is used to store structured data in android apps. In this post, I am going to show how to create database and tables, query, insert, modify and delete data.
Android provides SQLite api to manage SQLite database. Helper class SQLiteOpenHelper is used to perform all database operation as it efficiently manages closing and opening database. First you need to instantiate SQLiteOpenHelper object by passing data base name you want to create or open to constructor.
To get data base, you need to call getReadableDatabase() or getWritableDatabase() methods depending on the type of operations you want to perform. It is a best practice to close database in onDestry() call back method of the activity the database is associated with.
To insert data into SQLite database table, you need to create ContentValues object putting key and values. Key is a column name and value is the data to be stored in column for the row. Then call SQLiteOpenHelper insert() method passing table name and content values.
public void addBill(View v) {
ContentValues contentValues = new ContentValues();
EditText billId = (EditText)findViewById(R.id.billId);
contentValues.put(BillContract.BILL_ID, billId.getText().toString());
EditText billerName = (EditText)findViewById(R.id.billerName);
contentValues.put(BillContract.BILLER_NAME, billerName.getText().toString());
EditText billDate = (EditText)findViewById(R.id.billDate);
contentValues.put(BillContract.BILL_DATE, billDate.getText().toString());
EditText billAmt = (EditText)findViewById(R.id.billAmt);
contentValues.put(BillContract.BILL_AMT, billAmt.getText().toString());
EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());
EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.insert(BillContract.TABLE_NAME, null, contentValues);
}
To update sqlite table data, you need to call update() method of SQLiteOpenHelper passing where clause, selection values and content values.
public void updateBill(View v) {
String billId = ((EditText)findViewById(R.id.billId)).getText().toString();
ContentValues contentValues = new ContentValues();
EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());
EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());
String mSelectionClause = BillContract.BILL_ID+ " = ?";
String[] mSelectionArgs = {billId};
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.update(BillContract.TABLE_NAME,contentValues,mSelectionClause,mSelectionArgs);
}
To delete records in sqlite table, you need to call delete method of SQLiteOpenHelper passing where clause and selection values.
public void deleteBill(View view){
String billId = ((EditText)findViewById(R.id.billId)).getText().toString();
String mSelectionClause = BillContract.BILL_ID+ " = ?";
String[] mSelectionArgs = {billId};
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.delete(BillContract.TABLE_NAME,mSelectionClause,mSelectionArgs);
}
To query sqlite database you need to call query method of SQLiteOpenHelper passing list of columns you want to retrieve values for, selection clause and selection values.
public void getBillInfo(View view){
String paymentStatus = ((EditText)findViewById(R.id.paymentStatus)).getText().toString();
String[] mProjection =
{
"_id",
BillContract.BILL_ID,
BillContract.BILLER_NAME,
BillContract.BILL_DATE,
BillContract.BILL_AMT,
};
String selection = BillContract.PAY_STATUS+ " = ?";
String[] selectionArgs = {paymentStatus};
String orderBy = BillContract.BILL_DATE+" DESC";
SQLiteDatabase billDB = sqLiteOpenHelper.getReadableDatabase();
Cursor cur = billDB.query(BillContract.TABLE_NAME, mProjection, selection, selectionArgs, null, null,orderBy );
String[] cursorColumns =
{
BillContract.BILLER_NAME,
BillContract.BILL_DATE,
BillContract.BILL_AMT
};
int[] viewIds = {R.id.billerName, R.id.billDate, R.id.billAmt};
SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(
this,
R.layout.bill_row,
cur,
cursorColumns,
viewIds,
0);
billLst.setAdapter(simpleCursorAdapter);
}
package com.zoftino.content;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private String sql;
MySQLiteOpenHelper(Context context, String dbName, String msql) {
super(context, dbName, null, 1);
sql = msql;
}
public void onCreate(SQLiteDatabase db) {
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
package com.zoftino.content;
public class BillContract {
public static final String DB_NAME = "zoftino_bills";
public static final String TABLE_NAME = "bills";
public static final String BILL_ID= "BILL_ID";
public static final String BILLER_NAME= "BILLER_NAME";
public static final String BILL_DATE= "BILL_DATE";
public static final String BILL_AMT= "BILL_AMT";
public static final String PAY_MODE= "PAY_MODE";
public static final String PAY_STATUS= "PAY_STATUS";
}
package com.zoftino.content;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.EditText;
import android.widget.ListView;
public class SQLiteDatabaseActivity extends AppCompatActivity {
private MySQLiteOpenHelper sqLiteOpenHelper;
private SQLiteDatabase billsDB;
private static final String SQL_CREATE_TABLE = "CREATE TABLE " +
BillContract.TABLE_NAME +
"(" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
BillContract.BILL_ID +" TEXT, " +
BillContract.BILLER_NAME +" TEXT, " +
BillContract.BILL_DATE + " NUMERIC, " +
BillContract.BILL_AMT + " REAL, " +
BillContract.PAY_MODE + " TEXT, " +
BillContract.PAY_STATUS + " TEXT)";
ListView billLst;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.sqlite_activity);
sqLiteOpenHelper = new MySQLiteOpenHelper(this, BillContract.DB_NAME, SQL_CREATE_TABLE );
billLst = (ListView) findViewById(R.id.billsLst);
}
@Override
protected void onDestroy() {
sqLiteOpenHelper.close();
super.onDestroy();
}
public void addBill(View v) {
ContentValues contentValues = new ContentValues();
EditText billId = (EditText)findViewById(R.id.billId);
contentValues.put(BillContract.BILL_ID, billId.getText().toString());
EditText billerName = (EditText)findViewById(R.id.billerName);
contentValues.put(BillContract.BILLER_NAME, billerName.getText().toString());
EditText billDate = (EditText)findViewById(R.id.billDate);
contentValues.put(BillContract.BILL_DATE, billDate.getText().toString());
EditText billAmt = (EditText)findViewById(R.id.billAmt);
contentValues.put(BillContract.BILL_AMT, billAmt.getText().toString());
EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());
EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.insert(BillContract.TABLE_NAME, null, contentValues);
}
public void updateBill(View v) {
String billId = ((EditText)findViewById(R.id.billId)).getText().toString();
ContentValues contentValues = new ContentValues();
EditText modeOfPayment = (EditText)findViewById(R.id.modeOfPayment);
contentValues.put(BillContract.PAY_MODE, modeOfPayment.getText().toString());
EditText paymentStatus = (EditText)findViewById(R.id.paymentStatus);
contentValues.put(BillContract.PAY_STATUS, paymentStatus.getText().toString());
String mSelectionClause = BillContract.BILL_ID+ " = ?";
String[] mSelectionArgs = {billId};
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.update(BillContract.TABLE_NAME,contentValues,mSelectionClause,mSelectionArgs);
}
public void deleteBill(View view){
String billId = ((EditText)findViewById(R.id.billId)).getText().toString();
String mSelectionClause = BillContract.BILL_ID+ " = ?";
String[] mSelectionArgs = {billId};
SQLiteDatabase billDB = sqLiteOpenHelper.getWritableDatabase();
billDB.delete(BillContract.TABLE_NAME,mSelectionClause,mSelectionArgs);
}
public void getBillInfo(View view){
String paymentStatus = ((EditText)findViewById(R.id.paymentStatus)).getText().toString();
String[] mProjection =
{
"_id",
BillContract.BILL_ID,
BillContract.BILLER_NAME,
BillContract.BILL_DATE,
BillContract.BILL_AMT,
};
String selection = BillContract.PAY_STATUS+ " = ?";
String[] selectionArgs = {paymentStatus};
String orderBy = BillContract.BILL_DATE+" DESC";
SQLiteDatabase billDB = sqLiteOpenHelper.getReadableDatabase();
Cursor cur = billDB.query(BillContract.TABLE_NAME, mProjection, selection, selectionArgs, null, null,orderBy );
String[] cursorColumns =
{
BillContract.BILLER_NAME,
BillContract.BILL_DATE,
BillContract.BILL_AMT
};
int[] viewIds = {R.id.billerName, R.id.billDate, R.id.billAmt};
SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(
this,
R.layout.bill_row,
cur,
cursorColumns,
viewIds,
0);
billLst.setAdapter(simpleCursorAdapter);
}
}
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/activity_sqlite"
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"
android:orientation="vertical"
tools:context="com.zoftino.content.SQLiteDatabaseActivity">
<EditText android:id="@+id/billId"
android:hint="Bill Id"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<EditText android:id="@+id/billerName"
android:hint="Bill Name"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<EditText android:id="@+id/billDate"
android:hint="Bill Date"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<EditText android:id="@+id/billAmt"
android:hint="Bill Amount"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<EditText android:id="@+id/modeOfPayment"
android:hint="Payment Mode"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<EditText android:id="@+id/paymentStatus"
android:hint="Payment Status"
android:layout_width="match_parent"
android:layout_height="wrap_content" ></EditText>
<Button
android:id="@+id/button2"
android:text="Add Bill"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="addBill"></Button>
<Button
android:id="@+id/button3"
android:text="Get Bill Info"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="getBillInfo"></Button>
<Button
android:id="@+id/button4"
android:text="Update Bill"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="updateBill"></Button>
<Button
android:id="@+id/button5"
android:text="Delete Bill"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="deleteBill"></Button>
<ListView android:id="@+id/billsLst"
android:layout_width="match_parent"
android:layout_height="match_parent"></ListView>
</LinearLayout>
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/bill_row"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:paddingTop="@dimen/activity_vertical_margin">
<TextView android:id="@+id/billerName" android:layout_width="match_parent"
android:layout_height="wrap_content" ></TextView>
<TextView android:id="@+id/billDate" android:layout_width="match_parent"
android:layout_height="wrap_content" ></TextView>
<TextView android:id="@+id/billAmt" android:layout_width="match_parent"
android:layout_height="wrap_content" ></TextView>
</LinearLayout>