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());
}
}
}
