java - 如何为此查询编写准备好的语句？

Question

您好，我已经为我的应用程序及其工作编写了一个连接查询。我的下一个要求是将该查询转换为我陷入困境的准备好的语句。

"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});

当我运行原始查询时，我得到结果，但是当运行上述准备好的语句时，我得到的游标计数始终为零

Answer 1

您不需要添加 '当您经过 String 时，您自己参数，PreparedStatement将自行管理这些。

"'%" + query + "%'"

目前您的情况是这样的

where columnA = "'%somethingToFind%'"

所以除非你有 columnA 的值喜欢 'somethingToFindInColumnA' (请注意 String 开头和结尾的引用)。你永远不会得到结果。

删除这些以获得类似的内容:

"%" + query + "%"

完整答案:

db.rawQuery(sql,new String[]{String.valueOf(objectClass),"'%" + query + "%'","'%" + query + "%'",constructionSeries});

成为:

db.rawQuery(sql,new String[]{String.valueOf(objectClass),"%" + query + "%","%" + query + "%",constructionSeries});