Android中SQLite的json_group_array/json_object函数无法使用问题

问题详情 #

在Android应用中执行SQLite查询时触发错误：no such function: json_object (code 1): , while compiling，但相同查询语句在DB Browser中可正常运行。

执行的SQL查询语句 #

SELECT json_group_array( 
    json_object(
        'name', name, 
        'ip', ip,
        'mac', mac,
        'token', token
    )
) 
FROM tvs 

DB Browser中的正常输出 #

[{"name":"lr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"mbr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"jr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"tr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"dsr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"lr2","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},
{"name":"lr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"}]

Android中的代码实现 #

Cursor cursor;

try {
   cursor = db.rawQuery(
      "SELECT " +
           "json_group_array(" +
                "json_object(" +
                     "'name', name," +
                     "'ip', ip," +
                     "'mac', mac," +
                     "'token', token" +
                ")" +
           ")" +
      "FROM " +
           "tvs", null);
  cursor.moveToFirst();
  String result = cursor.getString(0);
} catch (JsonProcessingException e) {
  throw new RuntimeException(e);
}

原因分析 #

Android系统内置的SQLite版本普遍滞后于官方最新版，json_object和json_group_array是SQLite 3.18.0（2017-03-28）才新增的JSON函数，若运行应用的Android系统内置SQLite版本低于该版本，就会出现函数不存在的报错。而DB Browser通常使用较新的SQLite版本，因此可正常执行查询。

解决方案 #

方案1：手动拼接JSON字符串（兼容低版本） #

通过SQL的字符串拼接替代JSON函数实现需求，注意需自行处理字段中的特殊字符（如双引号、反斜杠）：

SELECT '[' || GROUP_CONCAT(
    '{"name":"' || name || '",' ||
    '"ip":"' || ip || '",' ||
    '"mac":"' || mac || '",' ||
    '"token":"' || token || '"}'
) || ']' AS result
FROM tvs

方案2：替换为第三方SQLite库 #

集成第三方SQLite扩展库（如SQLite Android Bindings），替换系统内置的SQLite，从而获得最新版本的SQLite功能支持。

方案3：在Java代码中处理JSON转换 #

先查询出所有数据，再用JSON库（如Gson、Jackson）在代码层面转换为JSON数组：

List<Map<String, String>> tvList = new ArrayList<>();
Cursor cursor = db.rawQuery("SELECT name, ip, mac, token FROM tvs", null);
while (cursor.moveToNext()) {
    Map<String, String> tv = new HashMap<>();
    tv.put("name", cursor.getString(cursor.getColumnIndexOrThrow("name")));
    tv.put("ip", cursor.getString(cursor.getColumnIndexOrThrow("ip")));
    tv.put("mac", cursor.getString(cursor.getColumnIndexOrThrow("mac")));
    tv.put("token", cursor.getString(cursor.getColumnIndexOrThrow("token")));
    tvList.add(tv);
}
cursor.close();

// 使用Gson转换为JSON字符串
Gson gson = new Gson();
String result = gson.toJson(tvList);

内容的提问来源于stack exchange，提问作者StealthRT