SQLite is a lightweight relational database management system and provides a robust and efficient way to store and manage structured data locally on Android devices and 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 configuration for database file creation.
• It is widely used for database solutions in Android as well 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.
• Export the database file from your Android device or emulator and view it using an SQLite database browser tool.

Supported Data Types

Other data types must be converted into one of the above type, before inserting into the database. Booleans are stored as INTEGERs, with numbers 0 being false and 1 being true.

For Data Security, any sensitive data has to be encrypted. Sensitive data can be encrypted using the Android Keystore API or SQLCipher library for enhanced security.

Steps to Store and Retrieve data by SQLite

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, for fields titled as Name, Job Title, Phone number and Email, and for one button for adding employee information as "Add Employee".

2. In the activity's onCreate method, initialize the EditText views and set an OnClickListener for the "Add Employee" button.

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

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 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. Cursor can be closed by returning getAllEmployees() method:

    
    
Cursor cursor = dbHandler.getAllEmployees();

// use the Cursor to get employee data

cursor.close();
    
        



Tweet