Local Databases with SQLiteOpenHelper

阅读原文时间：2023年07月10日阅读：1

For maximum control over local data, developers can use SQLite directly by leveraging SQLiteOpenHelper for executing SQL requests and managing a local database.

In this guide, we'll use the example of building a database to persist user created "Posts" to demonstrate SQLite and SQLiteOpenHelper.

If you want to use SQLite directly but reduce the verbosity of working with the database, check out our Easier SQL with Cupboardguide for a middle ground between SQLite and a full-fledged ORM.

We need to write our own class to handle database operations such as creation, upgrading, reading and writing. Database operations are defined using the SQLiteOpenHelper:

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// Database Info
private static final String DATABASE_NAME = "postsDatabase";
private static final int DATABASE_VERSION = 1;

// Table Names  
private static final String TABLE\_POSTS = "posts";  
private static final String TABLE\_USERS = "users";

// Post Table Columns  
private static final String KEY\_POST\_ID = "id";  
private static final String KEY\_POST\_USER\_ID\_FK = "userId";  
private static final String KEY\_POST\_TEXT = "text";

// User Table Columns  
private static final String KEY\_USER\_ID = "id";  
private static final String KEY\_USER\_NAME = "userName";  
private static final String KEY\_USER\_PROFILE\_PICTURE\_URL = "profilePictureUrl";

public PostsDatabaseHelper(Context context) {  
    super(context, DATABASE\_NAME, null, DATABASE\_VERSION);  
}

// Called when the database connection is being configured.  
// Configure database settings for things like foreign key support, write-ahead logging, etc.  
@Override  
public void onConfigure(SQLiteDatabase db) {  
    super.onConfigure(db);  
    db.setForeignKeyConstraintsEnabled(true);  
}

// Called when the database is created for the FIRST time.  
// If a database already exists on disk with the same DATABASE\_NAME, this method will NOT be called.  
@Override  
public void onCreate(SQLiteDatabase db) {  
    String CREATE\_POSTS\_TABLE = "CREATE TABLE " + TABLE\_POSTS +  
            "(" +  
                KEY\_POST\_ID + " INTEGER PRIMARY KEY," + // Define a primary key  
                KEY\_POST\_USER\_ID\_FK + " INTEGER REFERENCES " + TABLE\_USERS + "," + // Define a foreign key  
                KEY\_POST\_TEXT + " TEXT" +  
            ")";

    String CREATE\_USERS\_TABLE = "CREATE TABLE " + TABLE\_USERS +  
            "(" +  
                KEY\_USER\_ID + " INTEGER PRIMARY KEY," +  
                KEY\_USER\_NAME + " TEXT," +  
                KEY\_USER\_PROFILE\_PICTURE\_URL + " TEXT" +  
            ")";

    db.execSQL(CREATE\_POSTS\_TABLE);  
    db.execSQL(CREATE\_USERS\_TABLE);  
}

// Called when the database needs to be upgraded.  
// This method will only be called if a database already exists on disk with the same DATABASE\_NAME,  
// but the DATABASE\_VERSION is different than the version of the database that exists on disk.  
@Override  
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
    if (oldVersion != newVersion) {  
        // Simplest implementation is to drop all old tables and recreate them  
        db.execSQL("DROP TABLE IF EXISTS " + TABLE\_POSTS);  
        db.execSQL("DROP TABLE IF EXISTS " + TABLE\_USERS);  
        onCreate(db);  
    }  
}

}

Important Note: The SQLite database is lazily initialized. This means that it isn't actually created until it's first accessed through a call to getReadableDatabase() or getWriteableDatabase(). This also means that any methods that callgetReadableDatabase() or getWriteableDatabase() should be done on a background thread as there is a possibility that they might be kicking off the initial creation of the database.

Often a SQLite database will be used across your entire application; within services, applications, fragments, and more. For this reason, best practices often advise you to apply the singleton pattern to your SQLiteOpenHelper instances to avoid memory leaks and unnecessary reallocations. The best solution is to make your database instance a singleton instance across the entire application's lifecycle.

public class PostsDatabaseHelper extends SQLiteOpenHelper {
private static PostsDatabaseHelper sInstance;

// …

public static synchronized PostsDatabaseHelper getInstance(Context context) {
// Use the application context, which will ensure that you
// don't accidentally leak an Activity's context.
// See this article for more information: http://bit.ly/6LRzfx
if (sInstance == null) {
sInstance = new PostsDatabaseHelper(context.getApplicationContext());
}
return sInstance;
}

/**
* Constructor should be private to prevent direct instantiation.
* Make a call to the static method "getInstance()" instead.
*/
private PostsDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}

The static getInstance() method ensures that only one PostsDatabaseHelper will ever exist at any given time. If thesInstance object has not been initialized, one will be created. If one has already been created then it will simply be returned. Then we can access our database connection with:

// In any activity just pass the context and use the singleton method
PostsDatabaseHelper helper = PostsDatabaseHelper.getInstance(this);

See this android design patterns article for more information.

In order to access our records from the database more easily, we should create a model class for each of our resources. In this case, let's define a Post and a User model:

public class Post {
public User user;
public String text;
}

public class User {
public String userName;
public String profilePictureUrl;
}

Now we can interact with our data using the models.

We'll walk through examples of creating, reading, updating, and deleting posts / users in our database.

Inserting New Records

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// …existing methods…

// Insert a post into the database  
public void addPost(Post post) {  
    // Create and/or open the database for writing  
    SQLiteDatabase db = getWritableDatabase();

    // It's a good idea to wrap our insert in a transaction. This helps with performance and ensures  
    // consistency of the database.  
    db.beginTransaction();  
    try {  
        // The user might already exist in the database (i.e. the same user created multiple posts).  
        long userId = addOrUpdateUser(post.user);

        ContentValues values = new ContentValues();  
        values.put(KEY\_POST\_USER\_ID\_FK, userId);  
        values.put(KEY\_POST\_TEXT, post.text);

        // Notice how we haven't specified the primary key. SQLite auto increments the primary key column.  
        db.insertOrThrow(TABLE\_POSTS, null, values);  
        db.setTransactionSuccessful();  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to add post to database");  
    } finally {  
        db.endTransaction();  
    }  
}

// Insert or update a user in the database  
// Since SQLite doesn't support "upsert" we need to fall back on an attempt to UPDATE (in case the  
// user already exists) optionally followed by an INSERT (in case the user does not already exist).  
// Unfortunately, there is a bug with the insertOnConflict method  
// (https://code.google.com/p/android/issues/detail?id=13045) so we need to fall back to the more  
// verbose option of querying for the user's primary key if we did an update.  
public long addOrUpdateUser(User user) {  
    // The database connection is cached so it's not expensive to call getWriteableDatabase() multiple times.  
    SQLiteDatabase db = getWritableDatabase();  
    long userId = -1;

    db.beginTransaction();  
    try {  
        ContentValues values = new ContentValues();  
        values.put(KEY\_USER\_NAME, user.userName);  
        values.put(KEY\_USER\_PROFILE\_PICTURE\_URL, user.profilePictureUrl);

        // First try to update the user in case the user already exists in the database  
        // This assumes userNames are unique  
        int rows = db.update(TABLE\_USERS, values, KEY\_USER\_NAME + "= ?", new String\[\]{user.userName});

        // Check if update succeeded  
        if (rows == 1) {  
            // Get the primary key of the user we just updated  
            String usersSelectQuery = String.format("SELECT %s FROM %s WHERE %s = ?",  
                    KEY\_USER\_ID, TABLE\_USERS, KEY\_USER\_NAME);  
            Cursor cursor = db.rawQuery(usersSelectQuery, new String\[\]{String.valueOf(user.userName)});  
            try {  
                if (cursor.moveToFirst()) {  
                    userId = cursor.getInt(0);  
                    db.setTransactionSuccessful();  
                }  
            } finally {  
                if (cursor != null && !cursor.isClosed()) {  
                    cursor.close();  
                }  
            }  
        } else {  
            // user with this userName did not already exist, so insert new user  
            userId = db.insertOrThrow(TABLE\_USERS, null, values);  
            db.setTransactionSuccessful();  
        }  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to add or update user");  
    } finally {  
        db.endTransaction();  
    }  
    return userId;  
}

}

Note: If you are inserting a large number of records, you might want to use a compiled SQLiteStatement. You can read more about the performance benefits on this blog.

Querying Records

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// …existing methods…

public List<Post> getAllPosts() {  
    List<Post> posts = new ArrayList<>();

    // SELECT \* FROM POSTS  
    // LEFT OUTER JOIN USERS  
    // ON POSTS.KEY\_POST\_USER\_ID\_FK = USERS.KEY\_USER\_ID  
    String POSTS\_SELECT\_QUERY =  
            String.format("SELECT \* FROM %s LEFT OUTER JOIN %s ON %s.%s = %s.%s",  
                    TABLE\_POSTS,  
                    TABLE\_USERS,  
                    TABLE\_POSTS, KEY\_POST\_USER\_ID\_FK,  
                    TABLE\_USERS, KEY\_USER\_ID);

    // "getReadableDatabase()" and "getWriteableDatabase()" return the same object (except under low  
    // disk space scenarios)  
    SQLiteDatabase db = getReadableDatabase();  
    Cursor cursor = db.rawQuery(POSTS\_SELECT\_QUERY, null);  
    try {  
        if (cursor.moveToFirst()) {  
            do {  
                User newUser = new User();  
                newUser.userName = cursor.getString(cursor.getColumnIndex(KEY\_USER\_NAME));  
                newUser.profilePictureUrl = cursor.getString(cursor.getColumnIndex(KEY\_USER\_PROFILE\_PICTURE\_URL));

                Post newPost = new Post();  
                newPost.text = cursor.getString(cursor.getColumnIndex(KEY\_POST\_TEXT));  
                newPost.user = newUser;  
                posts.add(newPost);  
            } while(cursor.moveToNext());  
        }  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to get posts from database");  
    } finally {  
        if (cursor != null && !cursor.isClosed()) {  
            cursor.close();  
        }  
    }  
    return posts;  
}

}

Updating Records

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// …existing methods…

// Update the user's profile picture url  
public int updateUserProfilePicture(User user) {  
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();  
    values.put(KEY\_USER\_PROFILE\_PICTURE\_URL, user.profilePictureUrl);

    // Updating profile picture url for user with that userName  
    return db.update(TABLE\_USERS, values, KEY\_USER\_NAME + " = ?",  
            new String\[\] { String.valueOf(user.userName) });  
}

}

Deleting Records

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// …existing methods…

public void deleteAllPostsAndUsers() {  
    SQLiteDatabase db = getWritableDatabase();  
    db.beginTransaction();  
    try {  
        // Order of deletions is important when foreign key relationships exist.  
        db.delete(TABLE\_POSTS, null, null);  
        db.delete(TABLE\_USERS, null, null);  
        db.setTransactionSuccessful();  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to delete all posts and users");  
    } finally {  
        db.endTransaction();  
    }  
}

}

We can now leverage our database handler and models to persist data to our SQLite store:

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

    // Create sample data  
    User sampleUser = new User();  
    sampleUser.userName = "Steph";  
    sampleUser.profilePictureUrl = "https://i.imgur.com/tGbaZCY.jpg";

    Post samplePost = new Post();  
    samplePost.user = sampleUser;  
    samplePost.text = "Won won!";

    // Get singleton instance of database  
    PostsDatabaseHelper databaseHelper = PostsDatabaseHelper.getInstance(this);

    // Add sample post to the database  
    databaseHelper.addPost(samplePost);

    // Get all posts from database  
    List<Post> posts = databaseHelper.getAllPosts();  
    for (Post post : posts) {  
        // do something  
    }  
}

}

Note: In many cases, rather than interacting with SQL directly, Android apps can leverage one of the many available higher-level ORMs (object relational mappers) to persist Java models to a database table instead.

The full source code for the database handler above can be found here for reference:

public class PostsDatabaseHelper extends SQLiteOpenHelper {
// Database Info
private static final String DATABASE_NAME = "postsDatabase";
private static final int DATABASE_VERSION = 1;

// Table Names  
private static final String TABLE\_POSTS = "posts";  
private static final String TABLE\_USERS = "users";

// Post Table Columns  
private static final String KEY\_POST\_ID = "id";  
private static final String KEY\_POST\_USER\_ID\_FK = "userId";  
private static final String KEY\_POST\_TEXT = "text";

// User Table Columns  
private static final String KEY\_USER\_ID = "id";  
private static final String KEY\_USER\_NAME = "userName";  
private static final String KEY\_USER\_PROFILE\_PICTURE\_URL = "profilePictureUrl";

private static PostsDatabaseHelper sInstance;

public static synchronized PostsDatabaseHelper getInstance(Context context) {  
    // Use the application context, which will ensure that you  
    // don't accidentally leak an Activity's context.  
    // See this article for more information: http://bit.ly/6LRzfx  
    if (sInstance == null) {  
        sInstance = new PostsDatabaseHelper(context.getApplicationContext());  
    }  
    return sInstance;  
}

/\*\*  
 \* Constructor should be private to prevent direct instantiation.  
 \* Make a call to the static method "getInstance()" instead.  
 \*/  
private PostsDatabaseHelper(Context context) {  
    super(context, DATABASE\_NAME, null, DATABASE\_VERSION);  
}

// Called when the database connection is being configured.  
// Configure database settings for things like foreign key support, write-ahead logging, etc.  
@Override  
public void onConfigure(SQLiteDatabase db) {  
    super.onConfigure(db);  
    db.setForeignKeyConstraintsEnabled(true);  
}

// Called when the database is created for the FIRST time.  
// If a database already exists on disk with the same DATABASE\_NAME, this method will NOT be called.  
@Override  
public void onCreate(SQLiteDatabase db) {  
    String CREATE\_POSTS\_TABLE = "CREATE TABLE " + TABLE\_POSTS +  
            "(" +  
                KEY\_POST\_ID + " INTEGER PRIMARY KEY," + // Define a primary key  
                KEY\_POST\_USER\_ID\_FK + " INTEGER REFERENCES " + TABLE\_USERS + "," + // Define a foreign key  
                KEY\_POST\_TEXT + " TEXT" +  
            ")";

    String CREATE\_USERS\_TABLE = "CREATE TABLE " + TABLE\_USERS +  
            "(" +  
                KEY\_USER\_ID + " INTEGER PRIMARY KEY," +  
                KEY\_USER\_NAME + " TEXT," +  
                KEY\_USER\_PROFILE\_PICTURE\_URL + " TEXT" +  
            ")";

    db.execSQL(CREATE\_POSTS\_TABLE);  
    db.execSQL(CREATE\_USERS\_TABLE);  
}

// Called when the database needs to be upgraded.  
// This method will only be called if a database already exists on disk with the same DATABASE\_NAME,  
// but the DATABASE\_VERSION is different than the version of the database that exists on disk.  
@Override  
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
    if (oldVersion != newVersion) {  
        // Simplest implementation is to drop all old tables and recreate them  
        db.execSQL("DROP TABLE IF EXISTS " + TABLE\_POSTS);  
        db.execSQL("DROP TABLE IF EXISTS " + TABLE\_USERS);  
        onCreate(db);  
    }  
}

// Insert a post into the database  
public void addPost(Post post) {  
    // Create and/or open the database for writing  
    SQLiteDatabase db = getWritableDatabase();

    // It's a good idea to wrap our insert in a transaction. This helps with performance and ensures  
    // consistency of the database.  
    db.beginTransaction();  
    try {  
        // The user might already exist in the database (i.e. the same user created multiple posts).  
        long userId = addOrUpdateUser(post.user);

        ContentValues values = new ContentValues();  
        values.put(KEY\_POST\_USER\_ID\_FK, userId);  
        values.put(KEY\_POST\_TEXT, post.text);

        // Notice how we haven't specified the primary key. SQLite auto increments the primary key column.  
        db.insertOrThrow(TABLE\_POSTS, null, values);  
        db.setTransactionSuccessful();  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to add post to database");  
    } finally {  
        db.endTransaction();  
    }  
}

// Insert or update a user in the database  
// Since SQLite doesn't support "upsert" we need to fall back on an attempt to UPDATE (in case the  
// user already exists) optionally followed by an INSERT (in case the user does not already exist).  
// Unfortunately, there is a bug with the insertOnConflict method  
// (https://code.google.com/p/android/issues/detail?id=13045) so we need to fall back to the more  
// verbose option of querying for the user's primary key if we did an update.  
public long addOrUpdateUser(User user) {  
    // The database connection is cached so it's not expensive to call getWriteableDatabase() multiple times.  
    SQLiteDatabase db = getWritableDatabase();  
    long userId = -1;

    db.beginTransaction();  
    try {  
        ContentValues values = new ContentValues();  
        values.put(KEY\_USER\_NAME, user.userName);  
        values.put(KEY\_USER\_PROFILE\_PICTURE\_URL, user.profilePictureUrl);

        // First try to update the user in case the user already exists in the database  
        // This assumes userNames are unique  
        int rows = db.update(TABLE\_USERS, values, KEY\_USER\_NAME + "= ?", new String\[\]{user.userName});

        // Check if update succeeded  
        if (rows == 1) {  
            // Get the primary key of the user we just updated  
            String usersSelectQuery = String.format("SELECT %s FROM %s WHERE %s = ?",  
                    KEY\_USER\_ID, TABLE\_USERS, KEY\_USER\_NAME);  
            Cursor cursor = db.rawQuery(usersSelectQuery, new String\[\]{String.valueOf(user.userName)});  
            try {  
                if (cursor.moveToFirst()) {  
                    userId = cursor.getInt(0);  
                    db.setTransactionSuccessful();  
                }  
            } finally {  
                if (cursor != null && !cursor.isClosed()) {  
                    cursor.close();  
                }  
            }  
        } else {  
            // user with this userName did not already exist, so insert new user  
            userId = db.insertOrThrow(TABLE\_USERS, null, values);  
            db.setTransactionSuccessful();  
        }  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to add or update user");  
    } finally {  
        db.endTransaction();  
    }  
    return userId;  
}

// Get all posts in the database  
public List<Post> getAllPosts() {  
    List<Post> posts = new ArrayList<>();

    // SELECT \* FROM POSTS  
    // LEFT OUTER JOIN USERS  
    // ON POSTS.KEY\_POST\_USER\_ID\_FK = USERS.KEY\_USER\_ID  
    String POSTS\_SELECT\_QUERY =  
            String.format("SELECT \* FROM %s LEFT OUTER JOIN %s ON %s.%s = %s.%s",  
                    TABLE\_POSTS,  
                    TABLE\_USERS,  
                    TABLE\_POSTS, KEY\_POST\_USER\_ID\_FK,  
                    TABLE\_USERS, KEY\_USER\_ID);

    // "getReadableDatabase()" and "getWriteableDatabase()" return the same object (except under low  
    // disk space scenarios)  
    SQLiteDatabase db = getReadableDatabase();  
    Cursor cursor = db.rawQuery(POSTS\_SELECT\_QUERY, null);  
    try {  
        if (cursor.moveToFirst()) {  
            do {  
                User newUser = new User();  
                newUser.userName = cursor.getString(cursor.getColumnIndex(KEY\_USER\_NAME));  
                newUser.profilePictureUrl = cursor.getString(cursor.getColumnIndex(KEY\_USER\_PROFILE\_PICTURE\_URL));

                Post newPost = new Post();  
                newPost.text = cursor.getString(cursor.getColumnIndex(KEY\_POST\_TEXT));  
                newPost.user = newUser;  
                posts.add(newPost);  
            } while(cursor.moveToNext());  
        }  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to get posts from database");  
    } finally {  
        if (cursor != null && !cursor.isClosed()) {  
            cursor.close();  
        }  
    }  
    return posts;  
}

// Update the user's profile picture url  
public int updateUserProfilePicture(User user) {  
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();  
    values.put(KEY\_USER\_PROFILE\_PICTURE\_URL, user.profilePictureUrl);

    // Updating profile picture url for user with that userName  
    return db.update(TABLE\_USERS, values, KEY\_USER\_NAME + " = ?",  
            new String\[\] { String.valueOf(user.userName) });  
}

// Delete all posts and users in the database  
public void deleteAllPostsAndUsers() {  
    SQLiteDatabase db = getWritableDatabase();  
    db.beginTransaction();  
    try {  
        // Order of deletions is important when foreign key relationships exist.  
        db.delete(TABLE\_POSTS, null, null);  
        db.delete(TABLE\_USERS, null, null);  
        db.setTransactionSuccessful();  
    } catch (Exception e) {  
        Log.d(TAG, "Error while trying to delete all posts and users");  
    } finally {  
        db.endTransaction();  
    }  
}

}

When working with SQLite, opening and inspecting the SQLite database can be helpful while debugging issues. You can leverage the Stetho library to view your data directly, or you can use the following command-line tools to retrieve the data.

The commands below will show how to get at the data (whether running on an emulator or an actual device). The commands should be performed within the terminal or command-line. Once you have the data, there are desktop SQLite viewers to help inspect the SQLite data graphically.