如何编写关联查询的Prepared Statement？及Cursor为空问题排查

我一眼就瞅出问题出在哪啦！你的Prepared Statement返回空Cursor的核心原因——你手动给LIKE的参数加了多余的单引号！

在使用Prepared Statement时，数据库驱动会自动为字符串参数包裹单引号，根本不需要你手动添加。而你现在把参数写成了"'%" + query + "%'"，相当于实际传递的是带单引号的字符串（比如如果query是"漏水"，参数就变成了'%漏水%'），数据库会去匹配包含这个带单引号的完整字符串的字段值，自然找不到符合条件的结果。

修正后的两种规范写法 #

方案1：直接在参数中拼接%（推荐）

去掉参数里的单引号，直接传递包含%的匹配字符串：

String sql = "SELECT * FROM " + TableA.TABLE + " as d LEFT JOIN " + TableB.TABLE + " as c ON d."+ TableA.DAMAGECODE + "=c." + TableB.DAMAGECODE + " AND d." + TableA.INDEX + "=c." + TableB.DAMAGECODEINDEX + " AND d." + TableA.OBJECTTYPE + "=c." + TableB.OBJECTCLASS + " WHERE d."+ TableA.LEAF + " = '1' AND d." + TableA.OBJECTTYPE + " = ? AND " + "(d."+ TableA.DAMAGECODE + " LIKE ? OR d." + TableA.DAMAGETEXT + " LIKE ?) AND c." + TableB.CONSTRUCTIONSERIES + " = ? ORDER BY " + TableA.DAMAGETEXT;

cursor = db.rawQuery(sql, new String[]{
    String.valueOf(objectClass),
    "%" + query + "%",  // 去掉单引号，直接传带%的匹配串
    "%" + query + "%",
    constructionSeries
});

方案2：在SQL中使用CONCAT拼接%

如果不想在Java代码里拼接字符串，也可以在SQL语句里用CONCAT('%', ?, '%')生成匹配模式：

String sql = "SELECT * FROM " + TableA.TABLE + " as d LEFT JOIN " + TableB.TABLE + " as c ON d."+ TableA.DAMAGECODE + "=c." + TableB.DAMAGECODE + " AND d." + TableA.INDEX + "=c." + TableB.DAMAGECODEINDEX + " AND d." + TableA.OBJECTTYPE + "=c." + TableB.OBJECTCLASS + " WHERE d."+ TableA.LEAF + " = '1' AND d." + TableA.OBJECTTYPE + " = ? AND " + "(d."+ TableA.DAMAGECODE + " LIKE CONCAT('%', ?, '%') OR d." + TableA.DAMAGETEXT + " LIKE CONCAT('%', ?, '%')) AND c." + TableB.CONSTRUCTIONSERIES + " = ? ORDER BY " + TableA.DAMAGETEXT;

cursor = db.rawQuery(sql, new String[]{
    String.valueOf(objectClass),
    query,  // 直接传纯查询字符串
    query,
    constructionSeries
});

额外小提醒 #

你的LEFT JOIN写法还有个潜在问题：如果WHERE子句里直接过滤c." + TableB.CONSTRUCTIONSERIES + " = ?，会把LEFT JOIN变成INNER JOIN的效果（不匹配c表的d记录会被这个条件过滤掉）。如果确实需要保留LEFT JOIN的特性，应该把这个条件放到ON子句里，比如：

String sql = "SELECT * FROM " + TableA.TABLE + " as d LEFT JOIN " + TableB.TABLE + " as c ON d."+ TableA.DAMAGECODE + "=c." + TableB.DAMAGECODE + " AND d." + TableA.INDEX + "=c." + TableB.DAMAGECODEINDEX + " AND d." + TableA.OBJECTTYPE + "=c." + TableB.OBJECTCLASS + " AND c." + TableB.CONSTRUCTIONSERIES + " = ? WHERE d."+ TableA.LEAF + " = '1' AND d." + TableA.OBJECTTYPE + " = ? AND " + "(d."+ TableA.DAMAGECODE + " LIKE ? OR d." + TableA.DAMAGETEXT + " LIKE ?) ORDER BY " + TableA.DAMAGETEXT;

具体要不要调整，取决于你的业务需求~

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