Android数据存储-DB（SQLite）

适用类型：重复、大量、结构化的数据

在APP中，DB被保存到了程序的private空间，这些空间默认是私有的，不能被其它的应用所访问，从而保证了DB中数据的安全性

在程序中，我们借助SQLiteOpenHelper对数据库进行读写操作，系统对于一些耗时的操作会在程序需要时才执行，而不是程序启动就开始执行，对于耗时的操作，我们应该AsyncTask or IntentService中进行，避免对程序主线程的影响

package net.oschina.git.zhaikun.androiddeveloped.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by zhaikun68 on 2017/8/2.
 * <p>
 * SQLite数据操作类
 */
public class SQLiteHelper extends SQLiteOpenHelper {

    private static final String TABLE_NAME = "students";//表名
    private static final String CREATE_TABLE_SQL =//创建表students
            "create table " + TABLE_NAME +
                    "(" +
                    "_id integer primary key," +//Android中SQLite默认的主键
                    "id integer," +
                    "name text" +
                    //添加更多的属性
                    ")";
    private static final String DELETE_TABLE_SQL =
            "DROP TABLE IF EXISTS " + TABLE_NAME;

    /**
     * Create a helper object to create, open, and/or manage a database.
     * This method always returns very quickly.  The database is not actually
     * created or opened until one of {@link #getWritableDatabase} or
     * {@link #getReadableDatabase} is called.
     *
     * @param context to use to open or create the database
     * @param name    of the database file, or null for an in-memory database
     * @param factory to use for creating cursor objects, or null for the default
     * @param version number of the database (starting at 1); if the database is older,
     *                {@link #onUpgrade} will be used to upgrade the database; if the database is
     *                newer, {@link #onDowngrade} will be used to downgrade the database
     */
    public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    /**
     * Called when the database is created for the first time. This is where the
     * creation of tables and the initial population of the tables should happen.
     *
     * @param db The database.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_SQL);
    }

    /**
     * Called when the database needs to be upgraded. The implementation
     * should use this method to drop tables, add tables, or do anything else it
     * needs to upgrade to the new schema version.
     * <p>
     * <p>
     * The SQLite ALTER TABLE documentation can be found
     * <a href="http://sqlite.org/lang_altertable.html">here</a>. If you add new columns
     * you can use ALTER TABLE to insert them into a live table. If you rename or remove columns
     * you can use ALTER TABLE to rename the old table, then create the new table and then
     * populate the new table with the contents of the old table.
     * </p><p>
     * This method executes within a transaction.  If an exception is thrown, all changes
     * will automatically be rolled back.
     * </p>
     *
     * @param db         The database.
     * @param oldVersion The old database version.
     * @param newVersion The new database version.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DELETE_TABLE_SQL);
        onCreate(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        super.onDowngrade(db, oldVersion, newVersion);
    }
}

package net.oschina.git.zhaikun.androiddeveloped.activitys;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;

import net.oschina.git.zhaikun.androiddeveloped.db.SQLiteHelper;

/**
 * Created by zhaikun68 on 2017/8/2.
 * <p>
 * DB数据操作
 */
public class DBActivity extends AppCompatActivity {

    private static final String DATABASE_NAME = "dbTest.db";//数据库名臣
    private static final int DATABASE_VERSION = 1;//数据库版本号

    private SQLiteHelper sqLiteHelper = new SQLiteHelper(this, DATABASE_NAME, null, DATABASE_VERSION);

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        //1、向数据库写入数据
        SQLiteDatabase dbWrite = sqLiteHelper.getWritableDatabase();
        //向数据库中插入的列对应的值
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", "123456");
        /**
         * insert()方法说明
         * 参数一：操作的表名
         * 参数二：参数会使得系统自动对那些ContentValues 没有提供数据的列填充数据为null，如果第二个参数传递的是null，那么系统则不会对那些没有提供数据的列进行填充
         */
        dbWrite.insert("students", null, contentValues);

        //2、读取数据库中的数据
        SQLiteDatabase dbRead = sqLiteHelper.getReadableDatabase();
        String[] columns = {"name"};
        String selection = "id=? and name=?";
        String[] selectionArgs = {"1", "小明"};//与selection中问号的先后顺序相对应，一个问号对应一个值
        /**
         * query()方法说明
         * 参数一：操作的表名
         * 参数二：查询的列的名称，返回所查询列对应的结果
         * 参数三：查询条件中的列
         * 参数四：查询条件中的列对应的值
         * 参数五：分组
         * 参数六：分组过滤
         * 参数七：排序
         * 返回Cursor游标对象
         */
        Cursor cursor = dbRead.query("students", columns, selection, selectionArgs, null, null, null);
        cursor.moveToFirst();//取游标中的数据，将读取位置移动到数据集中最开始的位置，使用cursor的其中一个get方法如getString()或getLong()获取列的值
        String name = cursor.getString(cursor.getColumnIndex("name"));//对于每一个get方法必须传递想要获取的列的索引位置，索引位置可以通过调用getColumnIndex()或getColumnIndexOrThrow()获得
        while (cursor.moveToNext()) {
            name = cursor.getString(cursor.getColumnIndex("name"));
        }

        //3、删除数据
        String whereClause = "id like ?";
        String[] whereClauseArgs = {"12"};
        /**
         * delete()方法说明
         * 参数一：操作的表名
         * 参数二：删除条件
         * 参数三：删除条件中对应的值
         */
        dbRead.delete("students", whereClause, whereClauseArgs);

        //4、跟新数据
        ContentValues updateContentValues = new ContentValues();
        contentValues.put("name", "小花");
        String updateSelection = "id = ?";
        String[] updateSelectionArgs = {"123456"};//更新条件对应的值，与updateSelection中？顺序一致
        /**
         * update()方法说明
         * 参数一：操作的表名
         * 参数二：更新的列对应的值
         * 参数三：更新条件
         * 参数四：更新条件对应的值
         */
        dbRead.update("students", updateContentValues, updateSelection, updateSelectionArgs);

        dbWrite.close();
        dbRead.close();
    }
}