最近我开发的一个功能需要从服务端获取json同步到本地的sqlite数据库，然后通知UI更新（Sqlbrite ftw）。这块的数据有一个字段叫isRead，当它为true的时候表示用户在UI上删除了它，app不再显示那条数据。
数据模型是这样的：
| 1 2 3 4 5 6 7 8 9 10 11
|
public class Alert { private final String alertId;
private final String alertTitle;
private final String alertText;
private final String alertActionText;
private final String alertActionUrl;
private final Boolean isRead;
... } |
而数据库是这样的：
| 1 2 3 4 5 6 7 8
|
private static final String TABLE_CREATE_ALERTS =
"CREATE TABLE "+ ALERTS +
"("+ ALERT_ID +
" text UNIQUEE, "+ ALERT_TITLE +
" text, "+ ALERT_TEXT +
" text, "+ ALERT_ACTION_TEXT +
" text, "+ ALERT_ACTION_URL +
" text, "+ ALERT_IS_READ +
" integer default 0);
"
;
|
当我们相服务器发起请求之后，同步返回的Alert集合应该是这样的：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
|
@Override public Observable syncAlerts(final List alerts) { Observable observable = Observable.from(alerts) .doOnNext(
new
Action1() { @Override public void call(Alert alert) { Cursor doesExistCursor =
null
;
BriteDatabase.Transaction transaction = db.newTransaction();
try
{ doesExistCursor = db.query(AlertQueries.byAlertIdQuery(alert.getAlertId()));
//then the row exists and we shouldn't insert if
(doesExistCursor !=
null
&& doesExistCursor.moveToNext()) { return
;
} ContentValues values =
new
AlertsContentValuesBuilder() .withAlertId(alert.getAlertId()) .withAlertTitle(alert.getAlertTitle()) .withAlertText(alert.getAlertText()) .withActionText(alert.getActionText()) .withActionUrl(alert.getActionUrl()) .build();
db.insert(MyOpenHelper.TABLE_ALERTS, values, SQLiteDatabase.CONFLICT_IGNORE);
transaction.markSuccessful();
} finally { transaction.end();
if
(doesExistCursor !=
null
) { doesExistCursor.close();
} } } });
return
observable;
} |
大体来说，这段代码查看了该Alert是否已经存在于本地数据库中，如果不是，插入之。
【在安卓中使用ON CONFLICT REPLACE同步数据到SQLITE】 但是这段代码有两个缺点。
- 我们跑了两次数据库（查询和插入）
- 如果Alert已经存在，我们不会更新各个字段，但是很可能上次查询的数据已经和之前不一样了。
ON CONFLICT REPLACE 解决这两个问题的方法就是在alertId上添加ON CONFLICT REPLACE
| 1 2 3 4 5 6 7 8
|
private static final String TABLE_CREATE_ALERTS =
"CREATE TABLE "+ ALERTS +
"("+ ALERT_ID +
" text UNIQUE ON CONFLICT REPLACE, "+ ALERT_TITLE +
" text, "+ ALERT_TEXT +
" text, "+ ALERT_ACTION_TEXT +
" text, "+ ALERT_ACTION_URL +
" text, "+ ALERT_IS_READ +
" integer default 0);
"
;
|
然后创建一个query来使用它
| 1 2 3 4 5 6 7 8 9 10 11
|
private static final String ALERT_INSERT_OR_REPLACE =
"INSERT OR REPLACE INTO "
+ MyOpenHelper.TABLE_ALERTS +
" ( "
+ MyOpenHelper.ALERT_ID +
" , "
+ MyOpenHelper.ALERT_TITLE +
" , "
+ MyOpenHelper.ALERT_TEXT +
" , "
+ MyOpenHelper.ALERT_ACTION_TEXT +
" , "
+ MyOpenHelper.ALERT_ACTION_URL +
" , "
+ MyOpenHelper.ALERT_IS_READ +
") VALUES (?, ?, ?, ?, ?, COALESCE((SELECT "
+ MyOpenHelper.ALERT_IS_READ +
" FROM "
+ MyOpenHelper.TABLE_ALERTS +
" WHERE "
+ MyOpenHelper.ALERT_ID +
" = ?), 0));
"
;
|
有点难懂，其实做了如下事情
- 我们使用INSERT OR REPLACE告诉数据库，如果插入有冲突则用新的值替换。
- 我们还确保了在同步的时候isRead字段不会被覆盖。我们是这样做的：查询字段当前值，如果不存在则设置一个默认的值0.
现在syncAlerts() 是这样的
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
@Override public void syncAlerts(final List alerts) { for
(Alert alert: alerts) { BriteDatabase.Transaction transaction = db.newTransaction();
try
{ db.executeAndTrigger(MyOpenHelper.TABLE_ALERTS, ALERT_INSERT_OR_REPLACE, alert.getAlertId(), alert.getAlertTitle(), alert.getAlertText(), alert.getActionText(), alert.getActionUrl(), alert.getAlertId());
transaction.markSuccessful();
}
catch
(Exception e) { Logger.errorNotify(e);
} finally { transaction.end();
} } } |
最后要注意的就是你该调用 BriteDatabase.executeAndTrigger(...)而不是BriteDatabase.execute(...)。
就这样稍微写了个复杂点的插入语句，我们就能利用Sqlite的INSERT OR REPLACE INTO 极大的简化我们的代码。
推荐阅读
