How to Perform CRUD on SQLite with Android Room ORM

Introduction

Room is Google’s official ORM framework for SQLite. It adds an abstraction layer that lets developers use the full power of SQLite while accessing the database more fluently.

Main Components

Database : An abstract class that extends RoomDatabase, annotated with @Database. It lists the entity classes and provides abstract DAO getters. Instances are created at runtime with Room.databaseBuilder() or Room.inMemoryDatabaseBuilder().

Entity : A class annotated with @Entity that represents a table row. Each field becomes a column unless marked with @Ignore. Primary keys are defined with @PrimaryKey.

Dao : An interface or abstract class annotated with @Dao. It declares methods for queries, inserts, updates, and deletes. Room generates the implementation at compile time.

Project Setup

Create a new Android Studio project and enable legacy support libraries. Add the following dependencies to build.gradle:

def room_version = "2.2.5"
implementation "androidx.room:room-runtime:$room_version"
annotationProcessor "androidx.room:room-compiler:$room_version"

Database Singleton

Create a class AppDatabase that extends RoomDatabase. Use a double‑checked locking pattern to hold a static instance, call

Room.databaseBuilder(MyApplication.context, AppDatabase.class, "badao.db")

, enable .allowMainThreadQueries(), and build the instance. Declare an abstract method UserDao userDao() for the DAO.

package com.badao.roomstudy.room;

import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;
import com.badao.roomstudy.MyApplication;

@Database(entities = {User.class}, version = 1, exportSchema = false)
public abstract class AppDatabase extends RoomDatabase {
    private static AppDatabase INSTANCE;
    public static AppDatabase getInstance() {
        if (INSTANCE == null) {
            synchronized (AppDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(MyApplication.context, AppDatabase.class, "badao.db")
                        .allowMainThreadQueries()
                        .build();
                }
            }
        }
        return INSTANCE;
    }
    public abstract UserDao userDao();
}

Application Class

Define a class MyApplication that extends Application and stores a static Context so the database builder can obtain a context.

package com.badao.roomstudy;

import android.app.Application;
import android.content.Context;

public class MyApplication extends Application {
    public static Context context;
    @Override
    public void onCreate() {
        super.onCreate();
        context = this;
    }
}

Manifest Declaration

<application
    android:name=".MyApplication"
    ...>
    ...
</application>

Entity Definition

Create User.java annotated with @Entity. It contains a primary‑key field uid and two columns first_name and last_name, each with getters and setters.

package com.badao.roomstudy.room;

import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.PrimaryKey;

@Entity
public class User {
    @PrimaryKey
    public int uid;
    @ColumnInfo(name = "first_name")
    public String firstName;
    @ColumnInfo(name = "last_name")
    public String lastName;
    // getters and setters omitted for brevity
}

DAO Interface

Define UserDao with queries for retrieving all users, loading by IDs, finding by name patterns, inserting (with REPLACE strategy), and deleting.

package com.badao.roomstudy.room;

import androidx.room.Dao;
import androidx.room.Delete;
import androidx.room.Insert;
import androidx.room.OnConflictStrategy;
import androidx.room.Query;
import java.util.List;

@Dao
public interface UserDao {
    @Query("SELECT * FROM user")
    List<User> getAll();

    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    List<User> loadAllByIds(int[] userIds);

    @Query("SELECT * FROM user WHERE first_name LIKE :first AND last_name LIKE :last LIMIT 1")
    User findByName(String first, String last);

    @Query("SELECT * FROM user WHERE last_name LIKE :last LIMIT 1")
    User findByLastName(String last);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insertAll(User... users);

    @Delete
    void delete(User user);
}

UI Layout

Add four buttons to activity_main.xml for adding data, querying all records, fuzzy name search, and deletion.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="添加数据"
        android:onClick="insertData" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询所有数据"
        android:onClick="getAll" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="根据名字模糊查询"
        android:onClick="findByLastName" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除"
        android:onClick="delete" />

</LinearLayout>

Activity Logic

Implement CRUD methods in MainActivity:

insertData : creates a User with a timestamp‑based uid, a random first name, a fixed last name, inserts it via userDao().insertAll(), and shows a toast.

getAll : retrieves all users, concatenates their last names, and displays them.

findByLastName : queries with the pattern "霸道%", shows the found name or a not‑found message.

delete : creates a User with uid = 0 and calls userDao().delete(), then shows a toast.

package com.badao.roomstudy;

import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
import com.badao.roomstudy.room.AppDatabase;
import com.badao.roomstudy.room.User;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }

    public void insertData(View view) {
        User user = new User();
        user.setUid((int) System.currentTimeMillis());
        user.setFirstName("公众号" + Math.random());
        user.setLastName("霸道的程序猿");
        AppDatabase.getInstance().userDao().insertAll(user);
        Toast.makeText(this, "插入成功", Toast.LENGTH_SHORT).show();
    }

    public void getAll(View view) {
        StringBuilder sb = new StringBuilder();
        List<User> list = AppDatabase.getInstance().userDao().getAll();
        for (User u : list) {
            sb.append("," + u.getLastName());
        }
        Toast.makeText(this, "查询到所有的名字为:" + sb, Toast.LENGTH_SHORT).show();
    }

    public void findByLastName(View view) {
        User user = AppDatabase.getInstance().userDao().findByLastName("霸道%");
        if (user == null) {
            Toast.makeText(this, "没有查到", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(this, "查询到的名字为:" + user.getFirstName() + user.getLastName(), Toast.LENGTH_SHORT).show();
        }
    }

    public void delete(View view) {
        User user = new User();
        user.setUid(0);
        AppDatabase.getInstance().userDao().delete(user);
        Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
    }
}

Additional Information

The generated SQLite file resides in data/data/<your.package.name>/database. Tools such as Navicat can be used to visualize the database (link provided in the original article). Sample code can be downloaded from the given CSDN link.