Android : SQLite 的使用

本文内容

1.定义模式和契约

2.使用 SQL Helper 创建数据库

3.插入数据

4.读取数据

5.删除数据

6.更新数据

7.保持连接与断开

 

！如下讲述，以RSS 订阅表为例，表名entry,含标题，子标题两列

 

1.定义模式和契约

定义一个Contract  ,

在其内部实现一个静态内部类，内部类实现BaseColumns接口，

内部类中 定义表的模式。！注意： 系统创建表时会自动添加一个_id列。

​
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";
    }
}

​

2. 使用 SQL Helper 创建数据库

先写好entry表 创建和删除的SQL语句

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;

创建一个FeedReaderDbHelper 类， 继承自SQLiteOpenHelper 

其中 当entry表的模式修改了, DATABASE_VERSION则要进行修改，否则再调试时，还是原来的旧表。除非app删除再安装。

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

3.插入数据

使用SQLite Helper 的getWritableDatabase()获取一个可写数据库对象db

插入数据需要用到一个ContentValues类, 使用其put方法以键值的方式存入数据

再调用db的insert方法 ，其中第二个参数为null表示当values值为空,则不执行插入

FeedReaderDbHelper dbHelper = new FeedReaderDbHelper(getContext());

// 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);

4. 读取数据

使用SQLite Helper 的getReadableDatabase()获取一个读数据库对象db

然后 一个SQL 语句被拆成以下四个部分

定义一个projection ,为要查询的列名串

定义一个selection,为要查询的where 条件

定义一个selectionArgs, 其值与selection 中的 ? 一 一对应

定义一个sortOrder，其为排序方式

最后创建一个游标对象查询

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

使用游标的getLong或getString方法查询，

但是要传入所要查询的列名的索引作为参数，调用getColumnIndex或getColumnIndexOrThrow 获取 

List itemIds = new ArrayList<>();
while(cursor.moveToNext()) {
  long itemId = cursor.getLong(
      cursor.getColumnIndexOrThrow(FeedEntry._ID));
  itemIds.add(itemId);
}
cursor.close();

5.删除数据

定义selection 和 selectionArgs 

再调用delete方法

// 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);

6.更新数据

使用ContentValues类，以键值方式存储，其中键为要更新的列名，值为更新后的新值

selection 为where条件，selectionArgs中的值与selection中的？一 一 对应，系统执行时替换其中的问号

最后调用SQLiteDatabase 的update方法

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

7.保持连接与断开

数据库的开启极其占用内存，如在调用getWritableDatabase和getReadDatabase时

所以仅在activity 的 onDestroy时关闭

@Override
protected void onDestroy() {
    dbHelper.close();
    super.onDestroy();
}

本文参考安卓官方文档 Save data using SQLite

本文作者：林东豪  原文链接