Android sqlite database is a way to maintain a local database for the device which provides a best practice for any app to maintain data within.
Previously we have been through data storing options in android shared preferences, sqlite, room database.
In this blog on android sqlite database we are going to look how to insert a image in local database and fetch them using CRUD operations.
Generally we use databases in various apps they might be local or global you can find them in our blog.
There is a lot of importance for database in any project they are key aspects to serve the user.
Android sqlite database
We use databases for user login, services, communications all the fields are maintained using a database so we have to get through a proper database for creating a successful application.
Android sqlite database Project Structure :
The project structure of android sqlite database is shown in the image to make it easier for development.
AndroidManifest.xml
Include read external storage permission to access the device storage in run-time
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
activity_main.xml
Let us start by creating a layout design by adding 6 edittext’s and 4 buttons to perform database operations.
<?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:layout_width="match_parent" android:layout_height="match_parent" android:padding="40dp"> <ImageView android:id="@+id/img_user" android:layout_width="150dp" android:layout_height="150dp" android:layout_alignParentRight="true" /> <EditText android:id="@+id/txt_user_id" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="30dp" android:text="user_id" android:textSize="20dp" /> <EditText android:id="@+id/txt_user_category" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/txt_user_id" android:layout_marginTop="10dp" android:text="senior" android:textSize="20dp" /> <EditText android:id="@+id/txt_user_name" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_below="@+id/txt_user_category" android:layout_marginTop="20dp" android:text="name" android:textSize="20dp" /> <EditText android:id="@+id/txt_user_department" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_below="@+id/txt_user_name" android:layout_marginTop="10dp" android:text="department" android:textSize="20dp" /> <EditText android:id="@+id/txt_user_phone" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_below="@+id/txt_user_department" android:layout_marginTop="10dp" android:text="phone" android:textSize="20dp" /> <EditText android:id="@+id/txt_user_email" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_below="@+id/txt_user_phone" android:layout_marginTop="10dp" android:text="email" android:textSize="20dp" /> <LinearLayout android:id="@+id/btn_layout1" android:layout_below="@+id/txt_user_email" android:layout_marginTop="20dp" android:layout_width="match_parent" android:layout_height="wrap_content"> <Button android:id="@+id/btn_add" android:layout_width="0dp" android:layout_height="wrap_content" android:background="@android:color/white" android:textColor="@android:color/black" android:textStyle="bold" android:layout_weight="1" android:textSize="20dp" android:text="Add"/> <Button android:id="@+id/btn_delete" android:layout_width="0dp" android:layout_height="wrap_content" android:background="@android:color/black" android:textColor="@android:color/white" android:textStyle="bold" android:layout_weight="1" android:textSize="20dp" android:text="Delete"/> </LinearLayout> <LinearLayout android:id="@+id/btn_layout2" android:layout_below="@+id/btn_layout1" android:layout_marginTop="10dp" android:layout_width="match_parent" android:layout_height="wrap_content"> <Button android:id="@+id/btn_update" android:layout_width="0dp" android:layout_height="wrap_content" android:background="@android:color/black" android:textColor="@android:color/white" android:textStyle="bold" android:layout_weight="1" android:textSize="20dp" android:text="Update"/> <Button android:id="@+id/btn_view" android:layout_width="0dp" android:layout_height="wrap_content" android:background="@android:color/white" android:textColor="@android:color/black" android:textStyle="bold" android:layout_weight="1" android:textSize="20dp" android:text="View"/> </LinearLayout> </RelativeLayout>
AppConfig.class
This class will provide configurations for DB.
public class AppConfig { public static final String DATABASE_NAME = "UserDB"; }
UserModel.class
Now we need to design a model class to handle the user data objects by implementing getter and setter methods for the variables.Do you want to generate a automatic model class
public class UserModel { private int id; private String category; private byte[] byteBuffer; private String name; private String department; private String phone; private String email; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getCategory() { return category; } public void setCategory(String category) { this.category = category; } public byte[] getByteBuffer() { return byteBuffer; } public void setByteBuffer(byte[] byteBuffer) { this.byteBuffer = byteBuffer; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getDepartment() { return department; } public void setDepartment(String department) { this.department = department; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }
DatabaseHandler.class
We create a DatabaseHandler class by extending it with SQLiteOpenHelper. Specify CRUD operations here for
Database Version
private static final int DATABASE_VERSION = 1;
Database Key’s
private static final String TABLE_NAME = "UsersDB"; private static final String COLUMN_ID = "id"; private static final String COLUMN_CATEGORY = "category"; private static final String COLUMN_PHOTO = "photo"; private static final String COLUMN_NAME = "name"; private static final String COLUMN_DEPARTMENT = "department"; private static final String COLUMN_PHONE = "phone"; private static final String COLUMN_EMAIL = "email";
Database creation query
@Override public void onCreate(SQLiteDatabase db) { String CREATE_USER = "CREATE TABLE " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_CATEGORY + " TEXT," + COLUMN_PHOTO + " BLOB, " + COLUMN_NAME + " TEXT," + COLUMN_DEPARTMENT + " TEXT," + COLUMN_PHONE + " TEXT," + COLUMN_EMAIL + " TEXT)"; db.execSQL(CREATE_USER); }
Add user to database table
public void adduser(UserModel userModel) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_CATEGORY,userModel.getCategory()); values.put(COLUMN_PHOTO, userModel.getByteBuffer()); values.put(COLUMN_NAME, userModel.getName()); values.put(COLUMN_DEPARTMENT, userModel.getDepartment()); values.put(COLUMN_PHONE, userModel.getPhone()); values.put(COLUMN_EMAIL, userModel.getEmail()); // Inserting Row db.insert(TABLE_NAME, null, values); db.close(); }
Update database
public void updateUser(UserModel userModel, int id) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_CATEGORY,userModel.getCategory()); values.put(COLUMN_PHOTO, userModel.getByteBuffer()); values.put(COLUMN_NAME, userModel.getName()); values.put(COLUMN_DEPARTMENT, userModel.getDepartment()); values.put(COLUMN_PHONE, userModel.getPhone()); values.put(COLUMN_EMAIL, userModel.getEmail()); // Updating Row db.update(TABLE_NAME, values,"id="+id,null); db.close(); }
Fetching user by userID
public UserModel getUser(int index) { String query = "SELECT "+ COLUMN_ID + "," + COLUMN_CATEGORY + "," + COLUMN_PHOTO + "," + COLUMN_NAME + "," + COLUMN_DEPARTMENT + "," + COLUMN_PHONE + "," + COLUMN_EMAIL + " FROM "+ TABLE_NAME +" WHERE "+ COLUMN_ID +" = "+ index; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(query, null); UserModel userModel = null; if (cursor.moveToFirst()) { userModel = new UserModel(); userModel.setId(Integer.parseInt(cursor.getString(0))); userModel.setCategory(cursor.getString(1)); userModel.setByteBuffer(cursor.getBlob(2)); userModel.setName(cursor.getString(3)); userModel.setDepartment(cursor.getString(4)); userModel.setPhone(cursor.getString(5)); userModel.setEmail(cursor.getString(6)); } return userModel; }
fetching all users in database
public List<UserModel> allUsers() { SQLiteDatabase db = this.getWritableDatabase(); List<UserModel> userModels = new ArrayList<>(); String query = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COLUMN_ID +" DESC"; Cursor cursor = db.rawQuery(query, null); if (cursor.moveToFirst()) { do { UserModel userModel = new UserModel(); userModel.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID))); userModel.setByteBuffer(cursor.getBlob(cursor.getColumnIndex(COLUMN_PHOTO))); userModels.add(userModel); } while (cursor.moveToNext()); } return userModels; }
Delete user by userID
public void deleteUser(int index) { SQLiteDatabase db = this.getWritableDatabase(); String query = "DELETE FROM " + TABLE_NAME + " WHERE "+ COLUMN_ID +" = " + index; db.execSQL(query); db.close(); }
import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import com.abhishek.sqlite.config.AppConfig; import com.abhishek.sqlite.model.UserModel; import java.util.ArrayList; import java.util.List; public class DatabaseHandler extends SQLiteOpenHelper { private static final int DATABASE_VERSION = 1; private static final String TABLE_NAME = "UsersDB"; private static final String COLUMN_ID = "id"; private static final String COLUMN_CATEGORY = "category"; private static final String COLUMN_PHOTO = "photo"; private static final String COLUMN_NAME = "name"; private static final String COLUMN_DEPARTMENT = "department"; private static final String COLUMN_PHONE = "phone"; private static final String COLUMN_EMAIL = "email"; public DatabaseHandler(Context context) { super(context, AppConfig.DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { String CREATE_USER = "CREATE TABLE " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_CATEGORY + " TEXT," + COLUMN_PHOTO + " BLOB, " + COLUMN_NAME + " TEXT," + COLUMN_DEPARTMENT + " TEXT," + COLUMN_PHONE + " TEXT," + COLUMN_EMAIL + " TEXT)"; db.execSQL(CREATE_USER); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NAME); onCreate(db); } public void adduser(UserModel userModel) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_CATEGORY,userModel.getCategory()); values.put(COLUMN_PHOTO, userModel.getByteBuffer()); values.put(COLUMN_NAME, userModel.getName()); values.put(COLUMN_DEPARTMENT, userModel.getDepartment()); values.put(COLUMN_PHONE, userModel.getPhone()); values.put(COLUMN_EMAIL, userModel.getEmail()); // Inserting Row db.insert(TABLE_NAME, null, values); db.close(); } public void updateUser(UserModel userModel, int id) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_CATEGORY,userModel.getCategory()); values.put(COLUMN_PHOTO, userModel.getByteBuffer()); values.put(COLUMN_NAME, userModel.getName()); values.put(COLUMN_DEPARTMENT, userModel.getDepartment()); values.put(COLUMN_PHONE, userModel.getPhone()); values.put(COLUMN_EMAIL, userModel.getEmail()); // Updating Row db.update(TABLE_NAME, values,"id="+id,null); db.close(); } public UserModel getUser(int index) { String query = "SELECT "+ COLUMN_ID + "," + COLUMN_CATEGORY + "," + COLUMN_PHOTO + "," + COLUMN_NAME + "," + COLUMN_DEPARTMENT + "," + COLUMN_PHONE + "," + COLUMN_EMAIL + " FROM "+ TABLE_NAME +" WHERE "+ COLUMN_ID +" = "+ index; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(query, null); UserModel userModel = null; if (cursor.moveToFirst()) { userModel = new UserModel(); userModel.setId(Integer.parseInt(cursor.getString(0))); userModel.setCategory(cursor.getString(1)); userModel.setByteBuffer(cursor.getBlob(2)); userModel.setName(cursor.getString(3)); userModel.setDepartment(cursor.getString(4)); userModel.setPhone(cursor.getString(5)); userModel.setEmail(cursor.getString(6)); } return userModel; } public List<UserModel> allUsers() { SQLiteDatabase db = this.getWritableDatabase(); List<UserModel> userModels = new ArrayList<>(); String query = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COLUMN_ID +" DESC"; Cursor cursor = db.rawQuery(query, null); if (cursor.moveToFirst()) { do { UserModel userModel = new UserModel(); userModel.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID))); userModel.setByteBuffer(cursor.getBlob(cursor.getColumnIndex(COLUMN_PHOTO))); userModels.add(userModel); } while (cursor.moveToNext()); } return userModels; } public void deleteUser(int index) { SQLiteDatabase db = this.getWritableDatabase(); String query = "DELETE FROM " + TABLE_NAME + " WHERE "+ COLUMN_ID +" = " + index; db.execSQL(query); db.close(); } }
MainActivity.class
Initialize the database handler for android sqlite database
db = new DatabaseHandler(MainActivity.this);
Bitmap to byteArray
public static byte[] getBytes(Bitmap bitmap) { ByteArrayOutputStream stream = new ByteArrayOutputStream(); bitmap.compress(Bitmap.CompressFormat.JPEG, 50, stream); return stream.toByteArray(); }
ByteArray to Bitmap
public static Bitmap getImage(byte[] image) { return BitmapFactory.decodeByteArray(image, 0, image.length); }
Adding user details
While adding user details we need to automatically add a user id which is automatically generated.
private void addData() { try { String uri = "@drawable/logo"; int imageResource1 = getResources().getIdentifier(uri, null, getPackageName()); Drawable drawable = getResources().getDrawable(imageResource1); Bitmap bitmap = ((BitmapDrawable) drawable).getBitmap(); UserModel userModel = new UserModel(); userModel.setCategory("student"); userModel.setByteBuffer(getBytes(bitmap)); userModel.setName("Abhishek"); userModel.setDepartment("Computers"); userModel.setPhone("9876543210"); userModel.setEmail("abhi@androidcoding.in"); db = new DatabaseHandler(this); db.adduser(userModel); Toast.makeText(this, "User Added", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } }
Update user details
Update details based on the user id and replacing the existing data.
private void updateUser(int id) { try { String uri = "@drawable/logo"; int imageResource1 = getResources().getIdentifier(uri, null, getPackageName()); Drawable drawable = getResources().getDrawable(imageResource1); Bitmap bitmap = ((BitmapDrawable) drawable).getBitmap(); UserModel userModel = new UserModel(); userModel.setCategory(txtUserCategory.getText().toString()); userModel.setByteBuffer(getBytes(bitmap)); userModel.setName(txtUserName.getText().toString()); userModel.setDepartment(txtUserDepartment.getText().toString()); userModel.setPhone(txtUserPhone.getText().toString()); userModel.setEmail(txtUserEmail.getText().toString()); db = new DatabaseHandler(this); db.updateUser(userModel, id); Toast.makeText(this, "User Updated", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } }
fetch user data by userID
With the help of userid we are fetching the user information.
private void fetchData() { try { userModel = db.getUser(1); txtUserId.setText("" + userModel.getId()); txtUserName.setText(userModel.getName()); imgUser.setImageBitmap(getImage(userModel.getByteBuffer())); txtUserCategory.setText(userModel.getCategory()); txtUserDepartment.setText(userModel.getDepartment()); txtUserPhone.setText(userModel.getPhone()); txtUserEmail.setText(userModel.getEmail()); Toast.makeText(this, "User Fetched", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } }
fetch all users in database
We are deleting all the users information in database
private void fetchUserList(){ allUsers = db.allUsers(); Log.d("Fetch all users",""+allUsers.size()); }
delete user details
Here we are making use of the userid to delete the exact user information row.
private void deleteUser() { try { db.deleteUser(1); txtUserId.setText(""); txtUserCategory.setText(""); txtUserName.setText(""); txtUserDepartment.setText(""); txtUserPhone.setText(""); txtUserEmail.setText(""); imgUser.setImageResource(0); Toast.makeText(this, "User Deleted", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } }
Providing the full source code for android sqlite database implementation.
import android.graphics.Bitmap; import android.graphics.BitmapFactory; import android.graphics.drawable.BitmapDrawable; import android.graphics.drawable.Drawable; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.ImageView; import android.widget.Toast; import androidx.appcompat.app.AppCompatActivity; import com.abhishek.sqlite.db.DatabaseHandler; import com.abhishek.sqlite.model.UserModel; import java.io.ByteArrayOutputStream; import java.util.List; public class MainActivity extends AppCompatActivity implements View.OnClickListener { DatabaseHandler db; ImageView imgUser; List<UserModel> allUsers; EditText txtUserId; EditText txtUserCategory; EditText txtUserName; EditText txtUserDepartment; EditText txtUserPhone; EditText txtUserEmail; Button btnAdd; Button btnDelete; Button btnUpdate; Button btnView; UserModel userModel; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); db = new DatabaseHandler(MainActivity.this); imgUser = findViewById(R.id.img_user); txtUserId = findViewById(R.id.txt_user_id); txtUserCategory = findViewById(R.id.txt_user_category); txtUserName = findViewById(R.id.txt_user_name); txtUserDepartment = findViewById(R.id.txt_user_department); txtUserPhone = findViewById(R.id.txt_user_phone); txtUserEmail = findViewById(R.id.txt_user_email); btnAdd = findViewById(R.id.btn_add); btnUpdate = findViewById(R.id.btn_update); btnDelete = findViewById(R.id.btn_delete); btnView = findViewById(R.id.btn_view); btnAdd.setOnClickListener(this); btnUpdate.setOnClickListener(this); btnDelete.setOnClickListener(this); btnView.setOnClickListener(this); } public static byte[] getBytes(Bitmap bitmap) { ByteArrayOutputStream stream = new ByteArrayOutputStream(); bitmap.compress(Bitmap.CompressFormat.JPEG, 50, stream); return stream.toByteArray(); } public static Bitmap getImage(byte[] image) { return BitmapFactory.decodeByteArray(image, 0, image.length); } @Override public void onClick(View v) { switch (v.getId()) { case R.id.btn_add: addData(); break; case R.id.btn_update: updateUser(Integer.parseInt(txtUserId.getText().toString())); break; case R.id.btn_delete: deleteUser(); break; case R.id.btn_view: fetchData(); fetchUserList(); break; } } private void updateUser(int id) { try { String uri = "@drawable/logo"; int imageResource1 = getResources().getIdentifier(uri, null, getPackageName()); Drawable drawable = getResources().getDrawable(imageResource1); Bitmap bitmap = ((BitmapDrawable) drawable).getBitmap(); UserModel userModel = new UserModel(); userModel.setCategory(txtUserCategory.getText().toString()); userModel.setByteBuffer(getBytes(bitmap)); userModel.setName(txtUserName.getText().toString()); userModel.setDepartment(txtUserDepartment.getText().toString()); userModel.setPhone(txtUserPhone.getText().toString()); userModel.setEmail(txtUserEmail.getText().toString()); db = new DatabaseHandler(this); db.updateUser(userModel, id); Toast.makeText(this, "User Updated", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } } private void deleteUser() { try { db.deleteUser(1); txtUserId.setText(""); txtUserCategory.setText(""); txtUserName.setText(""); txtUserDepartment.setText(""); txtUserPhone.setText(""); txtUserEmail.setText(""); imgUser.setImageResource(0); Toast.makeText(this, "User Deleted", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } } private void addData() { try { String uri = "@drawable/logo"; int imageResource1 = getResources().getIdentifier(uri, null, getPackageName()); Drawable drawable = getResources().getDrawable(imageResource1); Bitmap bitmap = ((BitmapDrawable) drawable).getBitmap(); UserModel userModel = new UserModel(); userModel.setCategory("student"); userModel.setByteBuffer(getBytes(bitmap)); userModel.setName("Abhishek"); userModel.setDepartment("Computers"); userModel.setPhone("9876543210"); userModel.setEmail("abhi@androidcoding.in"); db = new DatabaseHandler(this); db.adduser(userModel); Toast.makeText(this, "User Added", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } } private void fetchData() { try { userModel = db.getUser(1); txtUserId.setText("" + userModel.getId()); txtUserName.setText(userModel.getName()); imgUser.setImageBitmap(getImage(userModel.getByteBuffer())); txtUserCategory.setText(userModel.getCategory()); txtUserDepartment.setText(userModel.getDepartment()); txtUserPhone.setText(userModel.getPhone()); txtUserEmail.setText(userModel.getEmail()); Toast.makeText(this, "User Fetched", Toast.LENGTH_SHORT).show(); } catch (Exception e) { e.printStackTrace(); } } private void fetchUserList(){ allUsers = db.allUsers(); Log.d("Fetch all users",""+allUsers.size()); } }
Android sqlite database output :
So finally we can see the output for android sqlite database CRUD operations
If you have any query on this tutorial on android sqlite database do let us know in the comment section below.If you like this tutorial do like share for more interesting tutorial updates.