android 数据存储sqlite,使用 SQLite 保存数据

对于重复数据或结构化数据(例如联系信息)，将数据保存到数据库是理想选择。本页假设您已基本熟悉 SQL 数据库，并可帮助您开始在 Android 上使用 SQLite 数据库。

注意：虽然这些 API 功能强大，但它们的级别较低，使用起来需要花费大量时间和精力：

没有针对原始 SQL 查询的编译时验证。随着数据图表不断发生变化，您需要手动更新受影响的 SQL 查询。此过程既耗时又容易出错。

您需要使用大量样板代码，在 SQL 查询和数据对象之间进行转换。

出于这些原因，我们强烈建议将 Room 持久性库用作抽象层以访问应用的 SQLite 数据库中的信息。

定义架构和协定

SQL 数据库的主要原则之一是架构，即数据库组织方式的正式声明。架构反映在您用于创建数据库的 SQL 语句中。您可能会发现创建伴随类(称为协定类)很有用，该类以系统化、自记录的方式明确指定了架构的布局。

协定类是定义 URI、表和列名称的常量的容器。通过协定类，您可以在同一软件包的所有其他类中使用相同的常量。这样一来，您就可以在一个位置更改列名称并将其传播到整个代码中。

组织协定类的一种良好方法是将对整个数据库而言具有全局性的定义放入类的根级别。然后，为每个表创建一个内部类。每个内部类都枚举相应表的列。

注意：通过实现 _ID 的主键字段，

例如，以下协定定义了表示 RSS Feed 的单个表的表名称和列名称：

Kotlin

object FeedReaderContract {

// Table contents are grouped together in an anonymous object.

object FeedEntry : BaseColumns {

const val TABLE_NAME = "entry"

const val COLUMN_NAME_TITLE = "title"

const val COLUMN_NAME_SUBTITLE = "subtitle"

}

}Java

public final class FeedReaderContract {

// To prevent someone from accidentally instantiating the contract class,

// make the constructor private.

private FeedReaderContract() {}

/* Inner class that defines the table contents */

public static class FeedEntry implements BaseColumns {

public static final String TABLE_NAME = "entry";

public static final String COLUMN_NAME_TITLE = "title";

public static final String COLUMN_NAME_SUBTITLE = "subtitle";

}

}

使用 SQL 帮助程序创建数据库

定义了数据库的外观后，您应实现用于创建和维护数据库和表的方法。以下是用于创建和删除表的一些典型语句：

Kotlin

private const val SQL_CREATE_ENTRIES =

"CREATE TABLE ${FeedEntry.TABLE_NAME} (" +

"${BaseColumns._ID} INTEGER PRIMARY KEY," +

"${FeedEntry.COLUMN_NAME_TITLE} TEXT," +

"${FeedEntry.COLUMN_NAME_SUBTITLE} TEXT)"

private const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${FeedEntry.TABLE_NAME}"Java

private static final String SQL_CREATE_ENTRIES =

"CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +

FeedEntry._ID + " INTEGER PRIMARY KEY," +

FeedEntry.COLUMN_NAME_TITLE + " TEXT," +

FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)";

private static final String SQL_DELETE_ENTRIES =

"DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;

就像您在设备的内部存储空间中保存文件一样，Android 会将您的数据库存储在您应用的私有文件夹中。您的数据安全无虞，因为在默认情况下，其他应用或用户无法访问此区域。

您仅需调用

例如，下面展示了使用上述一些命令的

Kotlin

class FeedReaderDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

override fun onCreate(db: SQLiteDatabase) {

db.execSQL(SQL_CREATE_ENTRIES)

}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {

// This database is only a cache for online data, so its upgrade policy is

// to simply to discard the data and start over

db.execSQL(SQL_DELETE_ENTRIES)

onCreate(db)

}

override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {

onUpgrade(db, oldVersion, newVersion)

}

companion object {

// If you change the database schema, you must increment the database version.

const val DATABASE_VERSION = 1

const val DATABASE_NAME = "FeedReader.db"

}

}Java

public class FeedReaderDbHelper extends SQLiteOpenHelper {

// If you change the database schema, you must increment the database version.

public static final int DATABASE_VERSION = 1;

public static final String DATABASE_NAME = "FeedReader.db";

public FeedReaderDbHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

public void onCreate(SQLiteDatabase db) {

db.execSQL(SQL_CREATE_ENTRIES);

}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// This database is only a cache for online data, so its upgrade policy is

// to simply to discard the data and start over

db.execSQL(SQL_DELETE_ENTRIES);

onCreate(db);

}

public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

onUpgrade(db, oldVersion, newVersion);

}

}

如需访问您的数据库，请实例化

Kotlin

val dbHelper = FeedReaderDbHelper(context)Java

FeedReaderDbHelper dbHelper = new FeedReaderDbHelper(getContext());

将信息添加到数据库

通过将

Kotlin

// Gets the data repository in write mode

val db = dbHelper.writableDatabase

// Create a new map of values, where column names are the keys

val values = ContentValues().apply {

put(FeedEntry.COLUMN_NAME_TITLE, title)

put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle)

}

// Insert the new row, returning the primary key value of the new row

val newRowId = db?.insert(FeedEntry.TABLE_NAME, null, values)Java

// Gets the data repository in write mode

SQLiteDatabase db = dbHelper.getWritableDatabase();

// Create a new map of values, where column names are the keys

ContentValues values = new ContentValues();

values.put(FeedEntry.COLUMN_NAME_TITLE, title);

values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle);

// Insert the new row, returning the primary key value of the new row

long newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);

第二个参数将指示框架在 null(如此代码示例所示)，框架在没有值时不会插入行。

从数据库中读取信息

如需从数据库中读取信息，请使用

Kotlin

val db = dbHelper.readableDatabase

// Define a projection that specifies which columns from the database

// you will actually use after this query.

val projection = arrayOf(BaseColumns._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE)

// Filter results WHERE "title" = 'My Title'

val selection = "${FeedEntry.COLUMN_NAME_TITLE} = ?"

val selectionArgs = arrayOf("My Title")

// How you want the results sorted in the resulting Cursor

val sortOrder = "${FeedEntry.COLUMN_NAME_SUBTITLE} DESC"

val cursor = db.query(

FeedEntry.TABLE_NAME, // The table to query

projection, // The array of columns to return (pass null to get all)

selection, // The columns for the WHERE clause

selectionArgs, // The values for the WHERE clause

null, // don't group the rows

null, // don't filter by row groups

sortOrder // The sort order

)Java

SQLiteDatabase db = dbHelper.getReadableDatabase();

// Define a projection that specifies which columns from the database

// you will actually use after this query.

String[] projection = {

BaseColumns._ID,

FeedEntry.COLUMN_NAME_TITLE,

FeedEntry.COLUMN_NAME_SUBTITLE

};

// Filter results WHERE "title" = 'My Title'

String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";

String[] selectionArgs = { "My Title" };

// How you want the results sorted in the resulting Cursor

String sortOrder =

FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";

Cursor cursor = db.query(

FeedEntry.TABLE_NAME, // The table to query

projection, // The array of columns to return (pass null to get all)

selection, // The columns for the WHERE clause

selectionArgs, // The values for the WHERE clause

null, // don't group the rows

null, // don't filter by row groups

sortOrder // The sort order

);

第三个参数和第四个参数(selection 和 selectionArgs)会合并起来创建一个 WHERE 子句。由于这两个参数是与选择查询分开提供的，因此它们在合并之前会进行转义。这样一来，您的选择语句就不受 SQL 注入的影响。如需详细了解所有参数，请参阅

如需查看光标中的某一行，请使用

Kotlin

val itemIds = mutableListOf()

with(cursor) {

while (moveToNext()) {

val itemId = getLong(getColumnIndexOrThrow(BaseColumns._ID))

itemIds.add(itemId)

}

}Java

List itemIds = new ArrayList<>();

while(cursor.moveToNext()) {

long itemId = cursor.getLong(

cursor.getColumnIndexOrThrow(FeedEntry._ID));

itemIds.add(itemId);

}

cursor.close();

从数据库中删除信息

如需从表中删除行，您需要提供选择条件，以标识

Kotlin

// Define 'where' part of query.

val selection = "${FeedEntry.COLUMN_NAME_TITLE} LIKE ?"

// Specify arguments in placeholder order.

val selectionArgs = arrayOf("MyTitle")

// Issue SQL statement.

val deletedRows = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs)Java

// Define 'where' part of query.

String selection = FeedEntry.COLUMN_NAME_TITLE + " LIKE ?";

// Specify arguments in placeholder order.

String[] selectionArgs = { "MyTitle" };

// Issue SQL statement.

int deletedRows = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs);

更新数据库

如果您需要修改数据库值的子集，请使用

Kotlin

val db = dbHelper.writableDatabase

// New value for one column

val title = "MyNewTitle"

val values = ContentValues().apply {

put(FeedEntry.COLUMN_NAME_TITLE, title)

}

// Which row to update, based on the title

val selection = "${FeedEntry.COLUMN_NAME_TITLE} LIKE ?"

val selectionArgs = arrayOf("MyOldTitle")

val count = db.update(

FeedEntry.TABLE_NAME,

values,

selection,

selectionArgs)Java

SQLiteDatabase db = dbHelper.getWritableDatabase();

// New value for one column

String title = "MyNewTitle";

ContentValues values = new ContentValues();

values.put(FeedEntry.COLUMN_NAME_TITLE, title);

// Which row to update, based on the title

String selection = FeedEntry.COLUMN_NAME_TITLE + " LIKE ?";

String[] selectionArgs = { "MyOldTitle" };

int count = db.update(

FeedReaderDbHelper.FeedEntry.TABLE_NAME,

values,

selection,

selectionArgs);

保留数据库连接

由于在数据库关闭时，调用

Kotlin

override fun onDestroy() {

dbHelper.close()

super.onDestroy()

}Java

@Override

protected void onDestroy() {

dbHelper.close();

super.onDestroy();

}

调试数据库

Android SDK 提供一个 sqlite3 shell 工具，您可以使用该工具浏览表内容、运行 SQL 命令以及在 SQLite 数据库中执行其他实用操作。如需了解详情，请参阅如何发出 shell 命令。