如何解决sqlite中使用游标查询时的语法异常

Question

在我的项目中，我使用这个游标查询:

Cursor c = db.query(VivzHelper.TX_TABLE, columns, helper.TX_ID + "='" + name + "' AND " + helper.TX_DATE + " BETWEEN '"+ datefrom.from_date + "' AND '"  + dateto.to_date + "'  AND '"+ helper.TX_TYPE +" = '"+"DM", null, helper.TX_NAME, null, null);

对于上面的查询，我得到了以下异常:

android.database.sqlite.SQLiteException: near "DM": syntax error (code 1): , while compiling: SELECT _id, txname, SUM(amount) AS amount, date, txtype FROM transactions WHERE txid='E' AND date BETWEEN '2015-09-06' AND '2015-09-06'  AND 'txtype = 'DM GROUP BY txname

Answer 1

查看这部分:

AND 'txtype = 'DM GROUP BY txname

应:

AND txtype = 'DM' GROUP BY txname

---

错误在这里:

 AND '"+ helper.TX_TYPE +" = '"+"DM", null,

应:

 AND "+ helper.TX_TYPE +" = 'DM'", null,

---

那么，修复你的rawQuery:

Cursor c = db.query(VivzHelper.TX_TABLE, columns, helper.TX_ID + "='" + name + "' AND " + helper.TX_DATE + " BETWEEN '"+ datefrom.from_date + "' AND '" + dateto.to_date + "' AND '"+ helper.TX_TYPE +" = '"+"DM", null, helper.TX_NAME, null, null);

:

Cursor c = db.query(VivzHelper.TX_TABLE, columns, helper.TX_ID + "='" + name + "' AND " + helper.TX_DATE + " BETWEEN '"+ datefrom.from_date + "' AND '" + dateto.to_date + "' AND "+ helper.TX_TYPE +" = 'DM'", null, helper.TX_NAME, null, null);

[编辑]

使用绑定参数(又名selectionArgs)将帮助您摆脱字符串分隔符:

Cursor c = db.query(VivzHelper.TX_TABLE, columns, helper.TX_ID + " = ? AND " + helper.TX_DATE + " BETWEEN ? AND ? AND "+ helper.TX_TYPE + " = ?", new String[]{name, datefrom.from_date, dateto.to_date, "DM"}, helper.TX_NAME, null, null);