數(shù)據(jù)庫SQLite.jpg
這篇文章是數(shù)據(jù)庫系列篇文章的第一篇佃声，主要講Android Sqlite數(shù)據(jù)庫存儲菲嘴，后面陸續(xù)出GreenDao，LitePal, Realm,wcdb的文章蚌吸，一如既往锈拨，如果遇到任何關(guān)于Android中SQLite的問題，都可以直接在我的文章底部留言羹唠，或者直接在我的公眾號aserbao留言奕枢，文章會持續(xù)更新，希望這篇文章能為大家提供到幫助佩微！如果覺得文章對你有用缝彬，就幫忙點(diǎn)個贊，若覺得文章寫得不好之處望指出哺眯，必將加以修正跌造！
qrcode_for_gh_2886db5aaf83_258.jpg
這篇文章主要講SQlite數(shù)據(jù)庫存儲，從數(shù)據(jù)庫建表到數(shù)據(jù)庫的增刪改查操作族购，再到數(shù)據(jù)庫升級操作壳贪，最后是文章總結(jié)及參考鏈接
SQlite
1. 創(chuàng)建數(shù)據(jù)庫
Android中使用SQlite，需要自己創(chuàng)建庫寝杖，建表违施，添加數(shù)據(jù)！好在Android中提供了SQLiteOpenHelper類來幫助創(chuàng)建使用數(shù)據(jù)庫瑟幕，我們只需要繼承這個類就可以實(shí)現(xiàn)數(shù)據(jù)庫的創(chuàng)建和對數(shù)據(jù)的操作了磕蒲！Android 中創(chuàng)建數(shù)據(jù)庫需要通過如下幾部：
- 創(chuàng)建一個類繼承SQLiteOpenHelper留潦，需要實(shí)現(xiàn)其三個方法：
- 構(gòu)造方法：需要給SQLiteOpenHelper傳遞四個參數(shù):(上下文，數(shù)據(jù)庫名辣往，游標(biāo)工廠(通常為null)兔院，當(dāng)前數(shù)據(jù)庫版本號)；
- onCreate(): 表的創(chuàng)建站削，初始化操作
- onUpgrade(): 表升級
- 還有：onDowngrade(): 表降級,onOpen：每次打開數(shù)據(jù)庫坊萝，onBeforeDelete：
public class ThingManagerDBOpenHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "mysql.db";
private static final int VERSION = 1;
public ThingManagerDBOpenHelper(Context context) {
super(context, DB_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS "
+ ThingDBController.TABLE_NAME
+ String.format(
"("
+ "%s INTEGER PRIMARY KEY AUTOINCREMENT, "
+ "%s VARCHAR, "
+ "%s INTEGER"
+")"
, ThingManagerDBModel.ID
, ThingManagerDBModel.MESSAGE
, ThingManagerDBModel.TIME
)) ;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
……
}
}
說下SQlite的數(shù)據(jù)類型 ：
| 值 | 作用 |
|---|---|
| NULL: | 這個值為空值 |
| VARCHAR(n)： | 長度不固定且其最大長度為 n 的字串，n不能超過 4000许起。 |
| CHAR(n)： | 長度固定為n的字串十偶，n不能超過 254。 |
| INTEGER: | 值被標(biāo)識為整數(shù),依據(jù)值的大小可以依次被存儲為1,2,3,4,5,6,7,8. |
| REAL: | 所有值都是浮動的數(shù)值,被存儲為8字節(jié)的IEEE浮動標(biāo)記序號. |
| TEXT: | 值為文本字符串,使用數(shù)據(jù)庫編碼存儲(TUTF-8, UTF-16BE or UTF-16-LE). |
| BLOB: | 值是BLOB數(shù)據(jù)塊园细，以輸入的數(shù)據(jù)格式進(jìn)行存儲惦积。如何輸入就如何存儲,不改 變格式。 |
| DATA: | 包含了 年份猛频、月份狮崩、日期。 |
| TIME: | 包含了 小時鹿寻、分鐘厉亏、秒。 |
2. 創(chuàng)建一個數(shù)據(jù)庫存儲對象
public final class ThingManagerDBModel {
public static final String TABLE_NAME = "mysql_thing";
public static final String ID = "_id";
public static final String MESSAGE = "message";
public static final String TIME = "time";
int id;
String message;
long time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public long getTime() {
return time;
}
public void setTime(long time) {
this.time = time;
}
public ContentValues toContentValues(){
ContentValues cv = new ContentValues();
cv.put(MESSAGE, message);
cv.put(TIME, time);
return cv;
}
}
3. 創(chuàng)建一個數(shù)據(jù)庫控制器實(shí)現(xiàn)增刪改查
1. 增
- 使用Android APi來實(shí)現(xiàn)增加數(shù)據(jù)
public boolean insertApi(int id, String message, long time){
ThingManagerDBModel dbModel = new ThingManagerDBModel();
if(id >= 0) {
dbModel.id = id;
}
dbModel.message = message;
dbModel.time = time;
final boolean success = db.insert(TABLE_NAME, null, dbModel.toContentValues()) != -1;
dbClose();
return success;
}
- 使用Sql語句實(shí)現(xiàn)增加數(shù)據(jù)
public void insertRaw(String message, long time){
String sql = " insert into " + TABLE_NAME + "(message,time) values(?,?)";
Object[] args = {message,time};
db.execSQL(sql,args);
dbClose();
}
2. 刪
- 使用Api
public boolean deleteApi(String whereClause,String[] whereArgs){
boolean sucess = db.delete(TABLE_NAME, whereClause,whereArgs) != -1;
dbClose();
return sucess;
}
- 使用Sql語句
public void deleteRaw(String message){
String sql = "delete from "+TABLE_NAME + " where message = ?";
Object[] args = {message};
db.execSQL(sql,args);
dbClose();
}
3. 改
- 使用api
public boolean updateApi(int id,String message,long time){
ThingManagerDBModel dbModel = new ThingManagerDBModel();
if(id >= 0) {
dbModel.id = id;
}
dbModel.message = message;
dbModel.time = time;
boolean success = db.update(TABLE_NAME, dbModel.toContentValues(), "_id = ?", new String[]{String.valueOf(id)}) != -1;
dbClose();
return success;
}
- 使用Sql條件修改
public void updateRaw(int id,String message,long time){
String sql = "update "+TABLE_NAME + " set message = ?,time = ? where _id = ?";
Object[] args = {message,time,id};
db.execSQL(sql,args);
dbClose();
}
4. 查
- 使用Api
/**
* @param cloums 要查詢的字段
* @param selection 查詢條件
* @param selectionArgs 填充查詢條件的值
* @return
*/
public List<Thing> queryApi(String[] cloums,String selection,String[] selectionArgs){
try {
Cursor c = db.query(TABLE_NAME, cloums, selection, selectionArgs, null, null, null);
ArrayList<Thing> arrayList = new ArrayList<>();
if (!c.moveToLast()) {
return arrayList;
}
do {
Thing model = new Thing();
model.setId(c.getInt(c.getColumnIndexOrThrow(ThingManagerDBModel.ID)));
model.setTime(c.getLong(c.getColumnIndexOrThrow(ThingManagerDBModel.TIME)));
model.setMessage(c.getString(c.getColumnIndexOrThrow(ThingManagerDBModel.MESSAGE)));
arrayList.add(model);
} while (c.moveToPrevious());
c.close();
return arrayList;
}catch (Exception e){
e.printStackTrace();
}finally {
dbClose();
}
return new ArrayList<>();
}
- 使用sql條件
public List<Thing> queryRawById(String id){
//cursor獲取的一定是在這里申明的查詢條件值
String sql = "select _id,message,time from "+TABLE_NAME + " where _id = ?";
Cursor cursor = db.rawQuery(sql, new String[]{id});
ArrayList<Thing> arrayList = new ArrayList<>();
if (!cursor.moveToLast()) {
return arrayList;
}
do {
Thing model = new Thing();
model.setId(cursor.getInt(cursor.getColumnIndex(ThingManagerDBModel.ID)));
model.setTime(cursor.getLong(cursor.getColumnIndex(ThingManagerDBModel.TIME)));
model.setMessage(cursor.getString(cursor.getColumnIndexOrThrow(ThingManagerDBModel.MESSAGE)));
arrayList.add(model);
} while (cursor.moveToPrevious());
cursor.close();
dbClose();
return arrayList;
}
4. 數(shù)據(jù)庫的升級
在開發(fā)的過程中烈和，我們避免不了需要修改數(shù)據(jù)庫結(jié)構(gòu)爱只，添加字段或者刪除字段！我們通過增加傳遞給SQLiteOpenHelper的版本值招刹，得到onUpgrade方法的回調(diào)來實(shí)現(xiàn)對數(shù)據(jù)庫的操作來進(jìn)行升級恬试。這里需要注意的是，如果用戶安裝版本1之后直接跳裝版本3疯暑，這時候我們就需要在onUpgrade方法中通過oldVersion來進(jìn)行每一個版本的逐漸升級训柴，方法如下：
public class ThingManagerDBOpenHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "mysql.db";
private static final int VERSION = 3;
……
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion){
case 1:
upToDbVersion2(db);
case 2:
upToDbVersion3(db);
default:
break;
}
}
public void upToDbVersion2(SQLiteDatabase db){
db.execSQL("ALTER TABLE " + ThingDBController.TABLE_NAME + " ADD COLUMN add_user_name text");
}
public void upToDbVersion3(SQLiteDatabase db) {
ContentValues values = new ContentValues();
values.put("message", "版本升級后的數(shù)據(jù)");
db.update(ThingDBController.TABLE_NAME, values, null, null);
}
}
問題
查詢問題
Cursor只會擁有你查詢的數(shù)據(jù)，如果在查詢條件里面沒有申請妇拯，Cursor里面就不會包含這個值幻馁，就會出現(xiàn)如下異常：
錯誤寫法：
String sql = "select message,time from "+TABLE_NAME + " where _id = ?";//條件里面沒有申請查詢_id的值
……something……
model.setId(cursor.getInt(cursor.getColumnIndex(ThingManagerDBModel.ID)));//在這里提取會報異常
正確寫法：
String sql = "select _id,message,time from "+TABLE_NAME + " where _id = ?";//條件里面沒有申請查詢_id的值
……something……
model.setId(cursor.getInt(cursor.getColumnIndex(ThingManagerDBModel.ID)));//這里進(jìn)行id值的提取
異常：
java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
at android.database.CursorWindow.nativeGetLong(Native Method)
at android.database.CursorWindow.getLong(CursorWindow.java:513)
at android.database.CursorWindow.getInt(CursorWindow.java:580)
at android.database.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:69)
at com.aserbao.aserbaosandroid.functions.database.mySql.beans.ThingDBController.queryRawById(ThingDBController.java:109)
at com.aserbao.aserbaosandroid.functions.database.mySql.MySqlActivity.queryData(MySqlActivity.java:70)
at com.aserbao.aserbaosandroid.functions.database.base.DataBaseBaseActivity.onViewClicked(DataBaseBaseActivity.java:85)
at com.aserbao.aserbaosandroid.functions.database.base.DataBaseBaseActivity_ViewBinding$2.doClick(DataBaseBaseActivity_ViewBinding.java:48)
at butterknife.internal.DebouncingOnClickListener.onClick(DebouncingOnClickListener.java:22)
at android.view.View.performClick(View.java:6291)
at android.view.View$PerformClick.run(View.java:24931)
at android.os.Handler.handleCallback(Handler.java:808)
at android.os.Handler.dispatchMessage(Handler.java:101)
at android.os.Looper.loop(Looper.java:166)
at android.app.ActivityThread.main(ActivityThread.java:7425)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:245)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:921)
