Android SQLite Database

Android provides several ways to store user and app data. SQLite is one way of storing the application data, locally, on the device. SQLite supports all the relational database features common on the Oracle databases or Microsoft SQL Server. In order to access this database it must be used the API provided by Android which is available in the android.database.sqlite package.

Image source: google.com

DBAdapter

When implementing a database on Android, a common practice is to create a class which interacts with the SQLite database and also with the activities of the app. This class is called DBAdapter and contains all the necessary code for creating the tables and the assigned fields. All of the operations regarding for inserting, updating, and deleting records are in the DBAdapter.

Image source: https://lecturesnippets.com/

SQLiteOpenHelper Class

This class is used for opening, creating and upgrading the database. The method onCreate() is the one used when the database table is created. The method onUpgrade() is used when the structure of a database table is changed, for example when a new field is added or the type of a field is changed.

In order to be used, the class SQLiteOpenHelper must be extended by a class from the app.

 

SQLiteDatabase Class

The SQLiteDatabase class is used to create a SQLiteDatabase object. This object uses the DatabaseHelper object to open the writable database. The SQLiteDatabase object can then use the standard insert(), update(), delete(), and query() operations to manipulate the records within the database table.

 

Cursor Class

To navigate on the queried database records Android provides a class called Cursor. More details on that here. The class contains the methods: moveToFirst(), moveToNext(), moveToPrevious() and moveToPosition().

 

In this tutorial we will implement an example of storing users in the SQLite database. We will use a table named User. This table contains three fields: id (INT), name (TEXT), phone_number(TEXT).

User Table Structure

User.java

public class User
{
    //private variables
    int _id;
    String _name;
    String _phone_number;
 
    // Empty constructor
    public User()
    {
    }
 
    // constructor
    public User(int id, String name, String _phone_number)
    {
       this._id = id;
       this._name = name;
       this._phone_number = _phone_number;
    }
 
    // constructor
    public User(String name, String _phone_number)
    {
       this._name = name;
       this._phone_number = _phone_number;
    }
 
     // getting ID
     public int getID()
     {
        return this._id;
     }
 
     // setting id
     public void setID(int id)
     {
        this._id = id;
     }

     // getting name
     public String getName()
     {
        return this._name;
     }
 
     // setting name
     public void setName(String name)
     {
        this._name = name;
     } 

     // getting phone number
     public String getPhoneNumber()
     {
        return this._phone_number;
     }

     // setting phone number
     public void setPhoneNumber(String phone_number)
     {
        this._phone_number = phone_number;
     }
 
     // toString
     @Override
     public String toString()
     {
         return "User [_id=" + _id + ", _name=" + _name + ",  _phone_number=" + _phone_number + "]";
     }
}

 

 Declare static variables

// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "DBUsers";

// Users table name
private static final String TABLE_User = "User";

// Users Table Columns names
// Primary key - User ID
private static final String KEY_ID = "id";

// User Name
private static final String KEY_NAME = "name";

// User Phone Number
private static final String KEY_PH_NO = "phone_number";

 

Creating Table USER

@Override
public void onCreate(SQLiteDatabase db)
{
    String CREATE_User_TABLE = "CREATE TABLE " + TABLE_User+ "("
    + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT,"
    + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_UserS_TABLE);
}

 

Upgrading database

Used when you want to change a column type or add new field. First of all you need to save
the current database version to check if you must add some changes

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_UserS);

    // Create tables again
    onCreate(db);
}

 

All CRUD(Create, Read, Update, Delete) Operations

Adding new User

void addUser(User User)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, User.getName()); // User Name
    values.put(KEY_PH_NO, User.getPhoneNumber()); // User Phone

    // Inserting Row
    db.insert(TABLE_UserS, null, values); 
    db.close(); // Closing database connection

}

 

Getting single User by ID

User getUser(int id)
{
     SQLiteDatabase db = this.getReadableDatabase(); 
     Cursor cursor = db.query(TABLE_User, new String[] { KEY_ID,
     KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
     new String[] { String.valueOf(id) }, null, null, null, null);
     if (cursor != null)
     cursor.moveToFirst();

     User User = new User(Integer.parseInt(cursor.getString(0)),
     cursor.getString(1), cursor.getString(2));

     // return User
     return User;
}

 

Getting All Users

public List<User> getAllUsers()
{
    List<User> UserList = new ArrayList<User>();

    // Select All Users Query
    String selectQuery = "SELECT * FROM " + TABLE_UserS;

    // Open writable Database
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // Looping through all rows and adding to list using the Cursor // object
    if (cursor.moveToFirst())
    {
      do
      {
           // Getting the current User
           User User = new User();
           User.setID(Integer.parseInt(cursor.getString(0)));
           User.setName(cursor.getString(1));
           User.setPhoneNumber(cursor.getString(2));

           // Adding User to list
           UserList.add(User);

        } while (cursor.moveToNext());
     }
      return UserList;
}

Updating single User

public int updateUser(User User)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, User.getName());
    values.put(KEY_PH_NO, User.getPhoneNumber());

    // updating row using the User ID field
    return db.update(TABLE_User, values, KEY_ID + " = ?",
    new String[] { String.valueOf(User.getID()) });
}

 Deleting single User

public void deleteUser(User User)
{
    SQLiteDatabase db = this.getWritableDatabase();

    //deleting row using the User ID field
    db.delete(TABLE_User, KEY_ID + " = ?",
    new String[] { String.valueOf(User.getID()) });

    db.close();
}

 

How to use?

public class AndroidSQLiteActivity extends Activity
{
    /** Called when the activity is first created. */

    @Override

    public void onCreate(Bundle savedInstanceState)
    {
          super.onCreate(savedInstanceState);
          setContentView(R.layout.main);
          DatabaseHandler db = new DatabaseHandler(this);

          /** CRUD Operations* */

          // Inserting Users
          Log.d("Insert: ", "Add users ..");
          db.addUser(new User("User1", "9100000000"));
          db.addUser(new User("User2", "9199999999"));
          db.addUser(new User("User3", "9522222222"));
          db.addUser(new User("User4", "9533333333"));

          // Get all Users
          Log.e("Reading: ", "Reading all Users..");
          List<User> Users = db.getAllUsers();

           for (User cn : Users)
           {
               Log.e("Name: ", cn.toString());
           }
        }
}