Android基礎(chǔ)的SQLite使用
1. SQLiteOpenHelper
- 借助該類實(shí)現(xiàn)數(shù)據(jù)庫創(chuàng)建與升級
- 通過繼承實(shí)現(xiàn)該類獲取數(shù)據(jù)庫進(jìn)行數(shù)據(jù)操作
方法 描述 備注 SQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable CursorFactory factory, int version) 構(gòu)造方法 上下文赂毯、數(shù)據(jù)庫名芬首、游標(biāo)工廠類(默認(rèn)null)佑刷、數(shù)據(jù)庫版本號 onCreate(SQLiteDatabase db) 創(chuàng)建數(shù)據(jù)庫 使用db執(zhí)行設(shè)計配置好的數(shù)據(jù)庫建表語句進(jìn)行建表 onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 升級數(shù)據(jù)庫 根據(jù)版本號不同進(jìn)行升級，發(fā)生在App低版本升級數(shù)據(jù)庫結(jié)構(gòu)有改變的情況下
2. 使用 CRUD Create Retrieve Update Delete
1.getWritableDatabase()與getReadableDatabase()
創(chuàng)建或打開一個現(xiàn)有的數(shù)據(jù)庫（如果已存在直接打開严望，否則創(chuàng)建一個），并返回一個可對數(shù)據(jù)庫進(jìn)行讀寫操作的對象；當(dāng)數(shù)據(jù)庫不可寫入時（磁盤空間已滿）竖伯，getWritableDatabase()會出現(xiàn)異常章母，getReadableDatabase()返回的對象將已只讀的方式打開
2.long insert(String table, String nullColumnHack, ContentValues values)
插入表名母蛛，自動賦值null(用于未指定添加數(shù)據(jù)的情況下給可空列自動賦值)，數(shù)據(jù)集合
ContentValues contentValues = new ContentValues(); contentValues.put("name","日常消費(fèi)"); contentValues.put("details","飲食#水果#衣物"); DBUtils.insert(DBTables.TABLE_ACCOUNT_SORT,null, contentValues);3.int delete(String table, String whereClause, String[] whereArgs)
表名乳怎，條件彩郊，條件字符串組
DBUtils.delete(DBTables.TABLE_ACCOUNT_SORT,"name = ?",new String[]{"日常消費(fèi)"});4.int update(String table, ContentValues values, String whereClause, String[] whereArgs)
表名，更新的值集合蚪缀，條件秫逝，條件字符串組
ContentValues contentValues = new ContentValues(); contentValues.put("details","飲食#水果#衣物#交通"); DBUtils.update(DBTables.TABLE_ACCOUNT_SORT, contentValues, "name = ?", new String[]{"日常消費(fèi)"});5.Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
查詢表名，指定的列名（不指定默認(rèn)查詢所有列椿胯，約束條件（約束查詢某一行或某幾行的數(shù)據(jù)筷登，默認(rèn)查詢所有行），條件字符串集哩盲，指定分組的列（用于獲取分組匯總）前方，分組匯總的進(jìn)一步篩選狈醉，查詢結(jié)果指定排序
Cursor cursor = DBUtils.query(DBTables.TABLE_ACCOUNT_SORT, null, null, null, null, null, null); if (cursor.moveToFirst()) { do { String name = cursor.getString(cursor.getColumnIndex("name")); String details = cursor.getString(cursor.getColumnIndex("details")); Log.e(TAG, "name: " + name); Log.e(TAG, "details: " + details); }while (cursor.moveToNext()); cursor.close(); }
3. 封裝
DBHelper
public class DBHelper extends SQLiteOpenHelper { public DBHelper(Context context) { super(context, DBTables.DB_NAME, null, DBTables.DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DBTables.ACCOUNT_SQL); db.execSQL(DBTables.ACCOUNT_SORT_SQL); db.execSQL(DBTables.ACCOUNT_DETAIL_SQL); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }DBTables
public interface DBTables { String DB_NAME = "db.sqlite"; int DB_VERSION = 1; String TABLE_ACCOUNT = "account"; String ACCOUNT_SQL = "create table "+TABLE_ACCOUNT+"(" + "id integer primary key autoincrement," + "date varchar(20)," + "sort varchar(10)," + "detail varchar(10)," + "sum varchar(10)," + "source varchar(10)," + "income integer," + "type varchar(10)," + "reason varchar(10)," + "remark varchar(60)" + ")"; String TABLE_ACCOUNT_SORT = "sort"; String ACCOUNT_SORT_SQL = "create table "+TABLE_ACCOUNT_SORT+"(" + "name varchar(10) primary key," + "details varchar(100)" + ")"; String TABLE_ACCOUNT_DETAIL = "detail"; String ACCOUNT_DETAIL_SQL = "create table "+TABLE_ACCOUNT_DETAIL+"(" + "name varchar(10) primary key," + "budget varchar(100)" + ")"; }DBUtils
/** * Created by SJ on 2019/1/22. * 使用時需要在Application中初始化 * 查詢操作時 需要及時關(guān)閉游標(biāo)與數(shù)據(jù)庫 */ public class DBUtils { private static DBHelper dbHelper; public static void init(Context context) { if (dbHelper == null) { dbHelper = new DBHelper(context); } } private static SQLiteDatabase getReadableDatabase() { if (dbHelper != null) { return dbHelper.getReadableDatabase(); }else { throw new SQLException("DBUtils未初始化！"); } } /** * 執(zhí)行增刪改SQL語句 * @param sql */ public static void execSQL(String sql) { SQLiteDatabase db = getReadableDatabase(); db.execSQL(sql); db.close(); } /** * 執(zhí)行增刪改SQL語句 * @param sql 帶占位符的語句 * @param bindArgs 實(shí)際value */ public static void execSQL(String sql, Object[] bindArgs) { SQLiteDatabase db = getReadableDatabase(); db.execSQL(sql, bindArgs); db.close(); } /** * @param table 表名 * @param nullColumnHack 指定為null的列 * @param values 添加的值集合 */ public static void insert(String table, String nullColumnHack, ContentValues values) { SQLiteDatabase db = getReadableDatabase(); db.insert(table,nullColumnHack,values); db.close(); } /** * 刪除 * @param table 表名 * @param whereClause 刪除條件 * @param whereArgs 條件對應(yīng)值 */ public static void delete(String table, String whereClause, String[] whereArgs) { SQLiteDatabase db = getReadableDatabase(); db.delete(table,whereClause,whereArgs); db.close(); } /** * 修改 * @param table 表名 * @param values 需要修改的值 * @param whereClause 條件 * @param whereArgs 條件對應(yīng)值 */ public static void update(String table, ContentValues values, String whereClause, String[] whereArgs) { SQLiteDatabase db = getReadableDatabase(); db.update(table, values, whereClause, whereArgs); db.close(); } /** * * @param table String：表名 * @param columns String[]:要查詢的列名 * @param selection String：查詢條件 * @param selectionArgs String[]：查詢條件的參數(shù) * @param groupBy String:對查詢的結(jié)果進(jìn)行分組 * @param having String：對分組的結(jié)果進(jìn)行限制 * @param orderBy String：對查詢的結(jié)果進(jìn)行排序 * @return 返回游標(biāo)惠险，使用后需要及時關(guān)閉游標(biāo) */ public static Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){ SQLiteDatabase db = getReadableDatabase(); return db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy); } /** * @param sql 帶占位符的查詢語句 * @param selectionArgs 查詢條件實(shí)際值 * @return 返回游標(biāo)苗傅，使用后需要及時關(guān)閉游標(biāo) */ public static Cursor rawQuery(String sql, String[] selectionArgs){ SQLiteDatabase db = getReadableDatabase(); return db.rawQuery(sql,selectionArgs); } public static void colseDB(){ dbHelper.close(); } }使用代碼及結(jié)果
ContentValues contentValues = new ContentValues(); contentValues.put("name", "日常消費(fèi)"); contentValues.put("details", "飲食#水果#衣物"); DBUtils.insert(DBTables.TABLE_ACCOUNT_SORT, null, contentValues); contentValues.clear(); contentValues.put("details", "飲食#水果#衣物#交通"); DBUtils.update(DBTables.TABLE_ACCOUNT_SORT, contentValues, "name = ?", new String[]{"日常消費(fèi)"}); Cursor cursor = DBUtils.query(DBTables.TABLE_ACCOUNT_SORT, null, null, null, null, null, null); if (cursor.moveToFirst()) { do { String name = cursor.getString(cursor.getColumnIndex("name")); String details = cursor.getString(cursor.getColumnIndex("details")); Log.e(TAG, "name: " + name); Log.e(TAG, "details: " + details); } while (cursor.moveToNext()); cursor.close(); } DBUtils.delete(DBTables.TABLE_ACCOUNT_SORT, "name = ?", new String[]{"日常消費(fèi)"});image.png
