SQLite Database for Android
SQLite is a lightweight relational database management system that provides a robust and efficient way to store and manage structured data locally on Android devices. It comes pre-installed with the Android SDK (Software Development Kit).
Some of the main features include:
- It consumes minimal memory, approximately 250 KB, during runtime.
- SQLite is serverless and doesn't require any configuration for database file creation.
- It is widely used for database solutions in Android as well as iOS applications.
- SQLite supports standard relational database features, including SQL syntax, transactions, and prepared statements.
- SQLite supports standard SQL syntax, allowing SQL queries to create, retrieve, update, and delete data in the database.
- Once created, the SQLite database is located in the data/data/package.name/databases/ folder.
- It is possible to export the database file from your Android device or emulator and view it using an SQLite database browser tool.
Supported Data Types
- NULL - For null values.
- TEXT - For text strings. Supports different encodings like UTF-8, UTF-16 BE, or UTF-32 LE.
- INTEGER - For numeric values, including both positive and negative integers.
- REAL - For floating point values.
- BLOB - For binary data. This can store images, music files, .doc, .pdf files, etc., after converting them into byte arrays.
Other data types must be converted into one of the above types before inserting them into the database. Booleans are stored as INTEGER values, with 0 representing false and 1 representing true.
For Data Security, any sensitive data should be encrypted. Sensitive data can be encrypted using the Android Keystore API or the SQLCipher library for enhanced security.
Steps to Store and Retrieve Data Using SQLite
- Extend SQLiteOpenHelper: This class helps to create and upgrade the SQLite database. You need to extend this class in a custom class and override its methods.
- Prepare the schema: Define the database name, version, table names, data types, and fields, typically using SQL statements.
- Override the onCreate() and onUpgrade() methods:
onCreate() – Called when the database is created. Define the SQL statements to create the initial database structure (tables).
onUpgrade() – Called when the database is upgraded. Specify how to handle database upgrades, such as altering tables or migrating data. - Create table fields: In the onCreate() method, define the structure of database tables using SQL CREATE TABLE statements. Each table should have columns corresponding to the fields you want to store.
- Create Data Classes: Create Java classes that represent the data entries. These classes typically correspond to the tables in the database. Use getter and setter methods to access and convert field names to objects.
- Open the database connection: To interact with the database, call getWritableDatabase() or getReadableDatabase() on your SQLiteOpenHelper subclass to obtain an instance of the SQLiteDatabase.
- Perform operations: Use the SQLiteDatabase methods like insert(), update(), delete(), and query() to perform database operations. Retrieve data using a Cursor object, which allows you to iterate through the result set.
- Display data in UI: Use adapters to map data to the UI components (e.g., ListView or RecyclerView) in response to user interactions.
- For specific application requirements: Implement error handling, content providers, and other additional features as needed.
Example: "Employee Information" SQLite Table
This table consists of four fields: Name, Job Title, Phone number, and Email.
1. Create a layout file for the activity that contains EditTexts for the employee information, including fields titled Name, Job Title, Phone number, and Email, along with a button for adding employee information, labeled "Add Employee".
2. In the activity's onCreate method, initialize the EditText views and set an OnClickListener for the "Add Employee" button:
3. Create a DatabaseHelper class that extends SQLiteOpenHelper and provides methods to create the database, create the employee table, add an employee, and retrieve all employees:public class MainActivity extends AppCompatActivity { private EditText editTextName; private EditText editTextJobTitle; private EditText editTextPhoneNo; private EditText editTextEmail; private DatabaseHelper dbHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); editTextName = findViewById(R.id.edit_text_name); editTextJobTitle = findViewById(R.id.edit_text_job_title); editTextPhoneNo = findViewById(R.id.edit_text_phone_no); editTextEmail = findViewById(R.id.edit_text_email); Button buttonAddEmployee = findViewById(R.id.button_add_employee); buttonAddEmployee.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { String name = editTextName.getText().toString(); String jobTitle = editTextJobTitle.getText().toString(); String phoneNo = editTextPhoneNo.getText().toString(); String email = editTextEmail.getText().toString(); if (TextUtils.isEmpty(name)) { editTextName.setError("Name is required"); editTextName.requestFocus(); return; } if (TextUtils.isEmpty(jobTitle)) { editTextJobTitle.setError("Job Title is required"); editTextJobTitle.requestFocus(); return; } if (TextUtils.isEmpty(phoneNo)) { editTextPhoneNo.setError("Phone No is required"); editTextPhoneNo.requestFocus(); return; } if (TextUtils.isEmpty(email)) { editTextEmail.setError("Email is required"); editTextEmail.requestFocus(); return; } long id = dbHelper.addEmployee(name, jobTitle, phoneNo, email); Toast.makeText(MainActivity.this, "Employee added with ID " + id, Toast.LENGTH_SHORT).show(); } }); dbHelper = new DatabaseHelper(this); } @Override protected void onDestroy() { super.onDestroy(); dbHelper.close(); } }
public class DatabaseHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME = "employee.db"; private static final int DATABASE_VERSION = 1; private static final String TABLE_EMPLOYEE = "employee"; private static final String COLUMN_ID = "_id"; private static final String COLUMN_NAME = "name"; private static final String COLUMN_JOB_TITLE = "job_title"; private static final String COLUMN_PHONE_NO = "phone_no"; private static final String COLUMN_EMAIL = "email"; private static final String CREATE_TABLE_EMPLOYEE = "CREATE TABLE " + TABLE_EMPLOYEE + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + " TEXT NOT NULL, " + COLUMN_JOB_TITLE + " TEXT NOT NULL, " + COLUMN_PHONE_NO + " TEXT NOT NULL, " + COLUMN_EMAIL + " TEXT NOT NULL)"; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_EMPLOYEE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEE); onCreate(db); } public long addEmployee(String name, String jobTitle, String phoneNo, String email) { SQLiteDatabase db = getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_NAME, name); values.put(COLUMN_JOB_TITLE, jobTitle); values.put(COLUMN_PHONE_NO, phoneNo); values.put(COLUMN_EMAIL, email); return db.insert(TABLE_EMPLOYEE, null, values); } public Cursor getAllEmployees() { SQLiteDatabase db = getReadableDatabase(); return db.query(TABLE_EMPLOYEE, null, null, null, null, null, null); } }
It is good practice to close the database when done to free up system resources. A Cursor can be closed after its use, like so:
Cursor cursor = dbHandler.getAllEmployees(); // use the Cursor to get employee data cursor.close();