Android中使用ON CONFLICT REPLACE同步数据到SQLITE

最近我开发的一个功能需要从服务端获取json同步到本地的sqlite数据库，然后通知UI更新（Sqlbrite ftw）。这块的数据有一个字段叫isRead，当它为true的时候表示用户在UI上删除了它，app不再显示那条数据。

数据模型是这样的：

[java]  view plain  copy

 

1. public class Alert {    
2.     private final String alertId;  
3.     private final String alertTitle;  
4.     private final String alertText;  
5.     private final String alertActionText;  
6.     private final String alertActionUrl;  
7.     private final Boolean isRead;  
8.    
9.     ...  
10.    
11. }  

而数据库是这样的：

[java]  view plain  copy

 

1. private static final String TABLE_CREATE_ALERTS = "CREATE TABLE "    
2.         + ALERTS + "("   
3.         + ALERT_ID + " text UNIQUEE, "   
4.         + ALERT_TITLE + " text, "   
5.         + ALERT_TEXT + " text, "   
6.         + ALERT_ACTION_TEXT + " text, "   
7.         + ALERT_ACTION_URL + " text, "   
8.         + ALERT_IS_READ + " integer default 0);";  

当我们相服务器发起请求之后，同步返回的Alert集合应该是这样的：

[java]  view plain  copy

 

1. @Override  
2. public Observable<Alert> syncAlerts(final List<Alert> alerts) {  
3.    
4.     Observable<Alert> observable = Observable.from(alerts)  
5.         .doOnNext(new Action1<Alert>() {  
6.             @Override  
7.             public void call(Alert alert) {  
8.    
9.                 Cursor doesExistCursor = null;  
10.                 BriteDatabase.Transaction transaction = db.newTransaction();  
11.                 try {  
12.                     doesExistCursor = db.query(AlertQueries.byAlertIdQuery(alert.getAlertId()));  
13.    
14.                     //then the row exists and we shouldn't insert  
15.                     if (doesExistCursor != null && doesExistCursor.moveToNext()) {  
16.    
17.                         return;  
18.                     }  
19.    
20.                     ContentValues values = new AlertsContentValuesBuilder()  
21.                             .withAlertId(alert.getAlertId())  
22.                             .withAlertTitle(alert.getAlertTitle())  
23.                             .withAlertText(alert.getAlertText())  
24.                             .withActionText(alert.getActionText())  
25.                             .withActionUrl(alert.getActionUrl())  
26.                             .build();  
27.    
28.                     db.insert(MyOpenHelper.TABLE_ALERTS, values, SQLiteDatabase.CONFLICT_IGNORE);  
29.                     transaction.markSuccessful();  
30.                 } finally {  
31.                     transaction.end();  
32.                     if (doesExistCursor != null) {  
33.                         doesExistCursor.close();  
34.                     }  
35.                 }  
36.    
37.             }  
38.         });  
39.     return observable;  
40. }  

大体来说，这段代码查看了该Alert是否已经存在于本地数据库中，如果不是，插入之。

但是这段代码有两个缺点。

1. 我们跑了两次数据库（查询和插入）

2. 如果Alert已经存在，我们不会更新各个字段，但是很可能上次查询的数据已经和之前不一样了。

ON CONFLICT REPLACE

解决这两个问题的方法就是在alertId上添加ON CONFLICT REPLACE

[java]  view plain  copy

 

1. private static final String TABLE_CREATE_ALERTS = "CREATE TABLE "    
2.         + ALERTS + "("   
3.         + ALERT_ID + " text UNIQUE ON CONFLICT REPLACE, "   
4.         + ALERT_TITLE + " text, "   
5.         + ALERT_TEXT + " text, "   
6.         + ALERT_ACTION_TEXT + " text, "   
7.         + ALERT_ACTION_URL + " text, "   
8.         + ALERT_IS_READ + " integer default 0);";  

然后创建一个query来使用它

[java]  view plain  copy

 

1. private static final String ALERT_INSERT_OR_REPLACE = "INSERT OR REPLACE INTO " +    
2.         MyOpenHelper.TABLE_ALERTS +" ( "  +  
3.         MyOpenHelper.ALERT_ID + " , " +  
4.         MyOpenHelper.ALERT_TITLE + " , " +  
5.         MyOpenHelper.ALERT_TEXT + " , " +  
6.         MyOpenHelper.ALERT_ACTION_TEXT + " , " +  
7.         MyOpenHelper.ALERT_ACTION_URL + " , " +  
8.         MyOpenHelper.ALERT_IS_READ +  ") VALUES (?, ?, ?, ?, ?, COALESCE((SELECT " +  
9.         MyOpenHelper.ALERT_IS_READ + " FROM " +  
10.         MyOpenHelper.TABLE_ALERTS + " WHERE " +  
11.         MyOpenHelper.ALERT_ID + " = ?), 0));";  

有点难懂，其实做了如下事情

1. 我们使用INSERT OR REPLACE告诉数据库，如果插入有冲突则用新的值替换。

2. 我们还确保了在同步的时候isRead字段不会被覆盖。我们是这样做的：查询字段当前值，如果不存在则设置一个默认的值0.

现在syncAlerts() 是这样的

[java]  view plain  copy

 

1. @Override  
2. public void syncAlerts(final List<Alert> alerts) {  
3.    
4.     for (Alert alert: alerts) {  
5.         BriteDatabase.Transaction transaction = db.newTransaction();  
6.         try {  
7.             db.executeAndTrigger(MyOpenHelper.TABLE_ALERTS,   
8.                 ALERT_INSERT_OR_REPLACE,   
9.                 alert.getAlertId(),   
10.                 alert.getAlertTitle(),   
11.                 alert.getAlertText(),   
12.                 alert.getActionText(),   
13.                 alert.getActionUrl(),   
14.                 alert.getAlertId());  
15.             transaction.markSuccessful();  
16.         } catch (Exception e) {  
17.             Logger.errorNotify(e);  
18.         } finally {  
19.             transaction.end();  
20.         }  
21.     }  
22. }  

最后要注意的就是你该调用 BriteDatabase.executeAndTrigger(...)而不是BriteDatabase.execute(...)。

 就这样稍微写了个复杂点的插入语句，我们就能利用Sqlite的INSERT OR REPLACE INTO 极大的简化我们的代码。

转自：http://www.jcodecraeer.com/a/anzhuokaifa/androidkaifa/2016/0503/4202.html