Android SQLite Tutorial :
Whenever an application needs to store large amount of data then using sqlite is more preferable than other repository system like SharedPreferences or saving data in files.
Android has built in SQLite database implementation. It is available locally over the device(mobile & tablet) and contain data in text format. It carry light weight data and suitable with many languages. So, it doesn’t required any administration or setup procedure of the database.
SQLite is an open-source relational database i.e. used to perform database operations on android devices such as storing, manipulating or retrieving persistent data from the database.
It is embedded in android bydefault. So, there is no need to perform any database setup or administration task.
Here, we are going to see the example of sqlite to store and fetch the data. Data is displayed in the logcat. For displaying data on the spinner or listview, move to the next page.
onCreate(SQLiteDatabase sqLiteDatabase) method is called only once throughout the application lifecycle. It will be called whenever there is a first call to getReadableDatabase() or getWritableDatabase() function available in super SQLiteOpenHelper class. So SQLiteOpenHelper class call the onCreate() method after creating database and instantiate SQLiteDatabase object. Database name is passed in constructor call.
SQLiteOpenHelper : class provides the functionality to use the SQLite database.
The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. For performing any database operation, you have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class.
step-1 : Create Activity Main.XML :
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/activity_main"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="8dp"
android:paddingLeft="8dp"
android:paddingRight="8dp"
android:paddingTop="8dp"
android:background="@android:color/holo_blue_dark">
<TextView
android:text="User Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentTop="true"
android:layout_marginTop="12dp"
android:id="@+id/textView"
android:textSize="18sp"
android:textStyle="bold|italic"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:gravity="center" />
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:inputType="textPersonName"
android:ems="10"
android:id="@+id/editName"
android:textStyle="bold|italic"
android:layout_below="@+id/textView"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
android:hint="Enter Name"
android:gravity="center_vertical|center" />
<TextView
android:text="password"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="13dp"
android:id="@+id/textView2"
android:textStyle="bold|italic"
android:textSize="18sp"
android:layout_below="@+id/editName"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
android:gravity="center"
android:hint="Enter Password" />
<Button
android:text="view_data"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/button2"
android:textSize="18sp"
android:onClick="viewdata"
android:textStyle="bold|italic"
android:layout_alignBaseline="@+id/button"
android:layout_alignBottom="@+id/button"
android:layout_alignRight="@+id/button4"
android:layout_alignEnd="@+id/button4" />
<Button
android:text="add_user"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/button"
android:textStyle="bold|italic"
android:textSize="18sp"
android:onClick="addUser"
android:layout_marginLeft="28dp"
android:layout_marginStart="28dp"
android:layout_below="@+id/editPass"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_marginTop="23dp" />
<Button
android:text="update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/button3"
android:onClick="update"
android:textStyle="normal|bold"
android:layout_below="@+id/editText3"
android:layout_alignLeft="@+id/button4"
android:layout_alignStart="@+id/button4"
android:layout_marginTop="13dp" />
<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:inputType="textPersonName"
android:ems="10"
android:id="@+id/editText6"
android:layout_alignTop="@+id/button4"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:freezesText="false"
android:hint="Enter Name to Delete Data"
android:layout_toLeftOf="@+id/button2"
android:layout_toStartOf="@+id/button2" />
<Button
android:text="delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="21dp"
android:layout_marginEnd="21dp"
android:id="@+id/button4"
android:onClick="delete"
android:textStyle="normal|bold"
tools:ignore="RelativeOverlap"
android:layout_marginBottom="41dp"
android:layout_alignParentBottom="true"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true" />
<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:inputType="textPersonName"
android:ems="10"
android:layout_marginTop="47dp"
android:id="@+id/editText3"
android:textStyle="bold|italic"
android:textSize="14sp"
android:layout_below="@+id/button"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_marginLeft="7dp"
android:layout_marginStart="7dp"
android:hint="Current Name" />
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:inputType="textPassword"
android:ems="10"
android:layout_marginTop="11dp"
android:id="@+id/editPass"
android:hint="Enter Password"
android:gravity="center_vertical|center"
android:textSize="18sp"
android:layout_below="@+id/textView2"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:textAllCaps="false"
android:textStyle="normal|bold" />
<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:inputType="textPersonName"
android:ems="10"
android:id="@+id/editText5"
android:textStyle="bold|italic"
android:textSize="14sp"
android:hint="New Name"
android:layout_alignTop="@+id/button3"
android:layout_alignLeft="@+id/editText3"
android:layout_alignStart="@+id/editText3"
android:layout_marginTop="32dp" />
</RelativeLayout>
Step-2: Create Activity Main :
package com.like.exampleofsqllite;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
public class MainActivity extends AppCompatActivity {
EditText Name, Pass , updateold, updatenew, delete;
myDbAdapter helper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Name= (EditText) findViewById(R.id.editName);
Pass= (EditText) findViewById(R.id.editPass);
updateold= (EditText) findViewById(R.id.editText3);
updatenew= (EditText) findViewById(R.id.editText5);
delete = (EditText) findViewById(R.id.editText6);
helper = new myDbAdapter(this);
}
public void addUser(View view)
{
String t1 = Name.getText().toString();
String t2 = Pass.getText().toString();
if(t1.isEmpty() || t2.isEmpty())
{
Toast.makeText(MainActivity.this, "Enter Both Name and Password", Toast.LENGTH_SHORT).show();
}
else
{
long id = helper.insertData(t1,t2);
if(id<=0)
{
Toast.makeText(MainActivity.this, "Insertion Unsuccessful", Toast.LENGTH_SHORT).show();
Name.setText("");
Pass.setText("");
} else
{
Toast.makeText(MainActivity.this, "Insertion Successful", Toast.LENGTH_SHORT).show();
Name.setText("");
Pass.setText("");
}
}
}
public void viewdata(View view)
{
String data = helper.getData();
Toast.makeText(MainActivity.this, data, Toast.LENGTH_SHORT).show();
}
public void update( View view)
{
String u1 = updateold.getText().toString();
String u2 = updatenew.getText().toString();
if(u1.isEmpty() || u2.isEmpty())
{
Toast.makeText(MainActivity.this, "Enter Data", Toast.LENGTH_SHORT).show();
}
else
{
int a= helper.updateName( u1, u2);
if(a<=0)
{
Toast.makeText(MainActivity.this, "Unsuccessful", Toast.LENGTH_SHORT).show();
updateold.setText("");
updatenew.setText("");
} else {
Toast.makeText(MainActivity.this, "Updated", Toast.LENGTH_SHORT).show();
updateold.setText("");
updatenew.setText("");
}
}
}
public void delete( View view)
{
String uname = delete.getText().toString();
if(uname.isEmpty())
{
Toast.makeText(MainActivity.this, "Enter Data", Toast.LENGTH_SHORT).show();
;
}
else{
int a= helper.delete(uname);
if(a<=0)
{
Toast.makeText(MainActivity.this, "Unsuccessful", Toast.LENGTH_SHORT).show();
delete.setText("");
}
else
{
Toast.makeText(MainActivity.this, "DELETED", Toast.LENGTH_SHORT).show();
delete.setText("");
}
}
}
}
Step-3 Create SQLiteOpenHelper Class :
package com.like.exampleofsqllite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
//Create Class OF SQLiteOpenHelper here Name of that class is (myDbAdapter) class is used for databasecreation and version management.
// For performing any database operation, that class whose only function is to provide for the creation, modification,
// and deletion of tables in the database.
public class myDbAdapter {
myDbHelper myhelper;
// use of Constructor : Create a helper object to create, open, and/or manage a database.
public myDbAdapter(Context context)
{
myhelper = new myDbHelper(context);
}
public long insertData(String name, String pass)
{
SQLiteDatabase dbb = myhelper.getWritableDatabase();
// Use of Content values: Content values are key pair values, which are updated or inserted into the database.
//ContentValues is a maplike class that matches a value to a String key.
// It contains multiple overloaded put methods that enforce type safety
ContentValues contentValues = new ContentValues();
contentValues.put(myDbHelper.NAME, name);
contentValues.put(myDbHelper.MyPASSWORD, pass);
long id = dbb.insert(myDbHelper.TABLE_NAME, null , contentValues);
return id;
}
public String getData()
{
SQLiteDatabase db = myhelper.getWritableDatabase();
String[] columns = {myDbHelper.UID,myDbHelper.NAME,myDbHelper.MyPASSWORD};
//Use of It is an object that is used to make the connection for executing SQL queries.
// It acts as middleware between SQLite database connection and SQL query.
// It is created after giving connection to SQLite database
//In SQL, a cursor is a temporary workstation that is allocated by the database server during the execution of a statement.
// It is a database object that allows us to access data of one row at a time.
// This concept of SQL is useful when the user wants to update the rows of the table one by one.
Cursor cursor =db.query(myDbHelper.TABLE_NAME,columns,null,null,null,null,null);
StringBuffer buffer= new StringBuffer();
while (cursor.moveToNext())
{
int cid =cursor.getInt(cursor.getColumnIndex(myDbHelper.UID));
String name =cursor.getString(cursor.getColumnIndex(myDbHelper.NAME));
String password =cursor.getString(cursor.getColumnIndex(myDbHelper.MyPASSWORD));
buffer.append(cid+ " " + name + " " + password +" \n");
}
return buffer.toString();
}
public int delete(String uname)
{
SQLiteDatabase db = myhelper.getWritableDatabase();
String[] whereArgs ={uname};
int count =db.delete(myDbHelper.TABLE_NAME ,myDbHelper.NAME+" = ?",whereArgs);
return count;
}
public int updateName(String oldName , String newName)
{
SQLiteDatabase db = myhelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(myDbHelper.NAME,newName);
String[] whereArgs= {oldName};
int count =db.update(myDbHelper.TABLE_NAME,contentValues, myDbHelper.NAME+" = ?",whereArgs );
return count;
}
static class myDbHelper extends SQLiteOpenHelper
{
private static final String DATABASE_NAME = "myDatabase"; // Database Name
private static final String TABLE_NAME = "myTable"; // Table Name
private static final int DATABASE_Version = 1; // Database Version
private static final String UID="_id"; // Column I (Primary Key)
private static final String NAME = "Name"; //Column II
private static final String MyPASSWORD= "Password"; // Column III
private static final String CREATE_TABLE = "CREATE TABLE "+TABLE_NAME+
" ("+UID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+NAME+" VARCHAR(255) ,"+ MyPASSWORD+" VARCHAR(225));";
private static final String DROP_TABLE ="DROP TABLE IF EXISTS "+TABLE_NAME;
private Context context;
public myDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_Version);
this.context=context;
}
public void onCreate(SQLiteDatabase db) {
try {
//execSQL(String sql): Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
// In your example, to create the table, you would call db. execSQL(DATABASE_CREATE)
// which would execute your table create statement on your application's SQLite database.
db.execSQL(CREATE_TABLE);
} catch (Exception e) {
Toast.makeText(context, e.getMessage(), Toast.LENGTH_SHORT).show();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
Toast.makeText(context, "OnUpgrade", Toast.LENGTH_SHORT).show();
db.execSQL(DROP_TABLE);
onCreate(db);
}catch (Exception e) {
Toast.makeText(context, e.getMessage(), Toast.LENGTH_SHORT).show();
}
}
}
}