In this tutorial we are going to discuss about how we can insert, delete and update in SQLite Database using android programming in detail.
Basic Terms:
SQLiteOpenHelper Class: A helper class to manage database creation and version management.
SQLiteDatabase Class: SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.
ContentValues Class: ContentValue class lets you put information inside an object in the form of Key-Value pairs for columns and their value. The object can then be passed to the insert() method of an instance of the SQLiteDatabase class to insert or update your WritableDatabase.
STEPS:
[sociallocker]
1. Before starting this tutorial, you need to have basic knowledge of android but if you are new to android programming it’s OK. it’s not a big deal you will learn gradually. So, in this first step, you need to have Android Studio installed in your system, i am using android studio of version 3.1.2. If you don’t have, download it from here: https://www.android.com/ and then set up AVD (Android Virtual Device). It may be either android studio built-in AVD or any other third party AVD like genymotion. One thing you need to take care while creating AVD is the AVD should be of api level below 22. This step complete the basic setup require to write our code and run it.
2. Open Android Studio, Click on file>new project. Give your application name whatever you want and then click next, select phone and tablet and then click next and then select empty activity and click next and then click on finish. It will take some time to build your gradle.
3. Goto app>res>layout>activity_main.xml by click the project field from left pane of your android studio and write the following code inside the ConstraintLayout tag. or Drag and Drop five plainText and three Button in your Layout. Now you have to change the properties of these controls (Copy the xml code from below). For more Detail, watch the video. In this step we complete the user interface part.
<pre><android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<EditText
android:id="@+id/txtName"
android:layout_width="355dp"
android:layout_height="53dp"
android:layout_marginBottom="74dp"
android:layout_marginLeft="16dp"
android:layout_marginStart="16dp"
android:layout_marginTop="77dp"
android:ems="10"
android:hint="Name"
android:inputType="textPersonName"
android:textSize="25sp"
app:layout_constraintBottom_toBottomOf="@+id/txtAdd"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<EditText
android:id="@+id/txtId"
android:layout_width="362dp"
android:layout_height="52dp"
android:layout_marginTop="16dp"
android:ems="10"
android:hint="Enter ID"
android:inputType="textPersonName"
android:textSize="25sp"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<EditText
android:id="@+id/txtAdd"
android:layout_width="357dp"
android:layout_height="wrap_content"
android:layout_marginLeft="16dp"
android:layout_marginStart="16dp"
android:layout_marginTop="149dp"
android:ems="10"
android:hint="Address"
android:inputType="textPersonName"
android:textSize="25sp"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<EditText
android:id="@+id/txtPhone"
android:layout_width="357dp"
android:layout_height="50dp"
android:layout_marginBottom="73dp"
android:layout_marginLeft="16dp"
android:layout_marginStart="16dp"
android:layout_marginTop="66dp"
android:ems="10"
android:hint="Phone"
android:inputType="textPersonName"
android:textSize="25sp"
app:layout_constraintBottom_toBottomOf="@+id/txtEmail"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="@+id/txtAdd" />
<EditText
android:id="@+id/txtEmail"
android:layout_width="357dp"
android:layout_height="59dp"
android:layout_marginBottom="89dp"
android:layout_marginLeft="24dp"
android:layout_marginStart="24dp"
android:layout_marginTop="92dp"
android:ems="10"
android:hint="Email"
android:inputType="textPersonName"
android:textSize="25sp"
app:layout_constraintBottom_toBottomOf="@+id/btnInsert"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toBottomOf="@+id/txtAdd"
app:layout_constraintVertical_bias="1.0" />
<Button
android:id="@+id/btnDlt"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="@+id/btnInsert" />
<Button
android:id="@+id/btnInsert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginBottom="84dp"
android:layout_marginLeft="16dp"
android:layout_marginStart="16dp"
android:text="Insert"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintStart_toStartOf="parent" />
<Button
android:id="@+id/btnUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginEnd="27dp"
android:layout_marginRight="27dp"
android:text="Update"
app:layout_constraintBaseline_toBaselineOf="@+id/btnDlt"
app:layout_constraintEnd_toEndOf="parent" />
</android.support.constraint.ConstraintLayout></pre>
4. Now from the left top pane you may see app folder. Expand that folder and then go to java folder again expand top folder under java folder. Now right click over the top folder and goto new>java class. click ok and give the class name as DatabaseHelper and click OK. Now inside that class write the following code.
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME="IDU.db";
public static final String TABLE_NAME="IDU";
//COLS
public static final String COLS_1="ID";
public static final String COLS_2="name";
public static final String COLS_3="address";
public static final String COLS_4="phone";
public static final String COLS_5="email";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " +TABLE_NAME+ "(ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, address TEXT, phone TEXT, email TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
onCreate(db);
}
}
5. Now go to MainActivity.java class and declare each control inside the class.
Button _btnInsert, _btnDelete, _btnUpdate; EditText _txtID, _txtName, _txtAdd, _txtPhone, _txtEmail; SQLiteOpenHelper openHelper; SQLiteDatabase db;
6. Now inside the onCreate method initialize each controls as:
_btnInsert=(Button)findViewById(R.id.btnInsert); _btnDelete=(Button)findViewById(R.id.btnDlt); _btnUpdate=(Button)findViewById(R.id.btnUpdate); _txtID=(EditText)findViewById(R.id.txtId); _txtName=(EditText)findViewById(R.id.txtName); _txtAdd=(EditText)findViewById(R.id.txtAdd); _txtPhone=(EditText)findViewById(R.id.txtPhone); _txtEmail=(EditText)findViewById(R.id.txtEmail); openHelper=new DatabaseHelper(this);
7. Now we are going to insert data in our database. So, create the listening event of the button and write the following code:
_btnInsert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String name =_txtName.getText().toString();
String address = _txtAdd.getText().toString();
String phone = _txtPhone.getText().toString();
String email= _txtEmail.getText().toString();
db=openHelper.getWritableDatabase();
insertData(name, address, phone, email);
Toast.makeText(getApplicationContext(), "INSERTED SUCCESSFULLY", Toast.LENGTH_LONG).show();
}
});
8. Now outside the onCreate method but inside the class create new method as:
public void insertData(String name, String address, String phone, String email){
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COLS_2, name);
contentValues.put(DatabaseHelper.COLS_3, address);
contentValues.put(DatabaseHelper.COLS_4, phone);
contentValues.put(DatabaseHelper.COLS_5, email);
long id = db.insert(DatabaseHelper.TABLE_NAME, null, contentValues);
}
Now Run your application either in builtin emulator, your real phone or in any third party emulator and type some value in the form and then click insert button.
Now you have to see whether the data is inserted in our database or not. In order to view the data of our database, you have to install one new application that is DB Browser for SQLite. After installing it open it but before this you have to save your application database from android studio to your PC. So, at the left buttom corner you may see Device File Explorer click that option now expand data folder; again expand data folder now scroll down and you have to find you application name. Again expand your apps and there you may see database folder; again expand database folder and you will be able to see your database having extension .db. Now save that file somewhere in your PC and from the file menu of DB Browser for SQLite go to open database; select your database and click ok. Now goto broswer data and from there select your database table. Finally you may see inserted data.
9. Now we are going to Delete some value from our database. So again create the listening event of your delete button as:
_btnDelete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db=openHelper.getWritableDatabase();
String id = _txtID.getText().toString();
deleteData(id);
Toast.makeText(getApplicationContext(), "Deleted successfully", Toast.LENGTH_LONG).show();
}
});
10. Now create new method outside the onCreate method but inside the class as:
public boolean deleteData(String id){
return db.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper.COLS_1 + "=?", new String[]{id})>0;
}
Run your application, enter the id of your data that you want to delete and click that delete button.
11. Now we are going to update the data of our database. So you have to create the listening event of the update button as well:
_btnUpdate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String name =_txtName.getText().toString();
String address = _txtAdd.getText().toString();
String phone = _txtPhone.getText().toString();
String email= _txtEmail.getText().toString();
db=openHelper.getWritableDatabase();
updateData(name, address, phone, email);
Toast.makeText(getApplicationContext(), "UPDATED SUCCESSFULLY", Toast.LENGTH_LONG).show();
}
});
12. Similar process; outside the onCreate method but inside the class create one method as:
public boolean updateData(String name, String address, String phone, String email){
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COLS_2, name);
contentValues.put(DatabaseHelper.COLS_3, address);
contentValues.put(DatabaseHelper.COLS_4, phone);
contentValues.put(DatabaseHelper.COLS_5, email);
String id = _txtID.getText().toString();
return db.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper.COLS_1 + "=?", new String[]{id})>0;
}
Run your apps and enter the id of the data that you want to update and enter the new information in the form and click that update button. That’s it. 🙂
[/sociallocker]
if you have any confusion, watch the following videos: