问题描述
我有一个查询的不同变体。所有的值类型是字符串! (不要问为什么......)
第一种可能是这样的:
SELECT * FROM项,其中Arg1 = FALSE
为ARG(S)的字符串(sqlWhere)如下:
ARG1 =?
在索引0的ARG(S)-array(参数)的字符串看起来是这样的:
假
这工作正常。我有715的效果。
现在,如果我有这样的事情:
SELECT * FROM项WHERE(ARG1 = 40或ARG2 = 42)和参数3 = FALSE
我会想到110结果这一点。但目前还没有任何结果,不会抛出异常。
如果我会产生在Firefox的SQLite Manager中的原始查询:
SELECT COUNT(*)FROM项WHERE(ARG1 = 40或ARG2 = 42)和参数3 = FALSE
我会得到错误:没有这样的列:假
如果我生成一个查询是这样的:
SELECT COUNT(*)FROM项WHERE(ARG1 = 40或ARG2 = 42),ARG3 =假
这会不会失败。但。我使用selectionArgs两个。我读过,他们应该避免这种情况。但他们没有。甚至,如果我投入selectionArgs两个为ARG3 =\\+假+\\或参数3 =\\'+假+\\',甚至ARG3 ='+假+ '这是行不通的。也许一个Android的具体问题?
值假来源于值的列表。它被初始化用emty串。
查询的方法是这样的:
公开名单<地图<字符串,字符串>> getSearchResults(字符串sqlWhere,字串[] args)
{
清单<地图<字符串,字符串>>规格=新的ArrayList<地图<字符串,字符串>>();
的String [] = specnames this.getSpecNames(); 光标光标= mDb.query(dbSchema.SpecSchema.TABLE_NAME,specnames,sqlWhere,ARGS,NULL,NULL,dbSchema.SpecSchema._ID + dbSchema.SORT_ASC);
cursor.moveToFirst();
而(!cursor.isAfterLast())
{
地图<字符串,字符串>临时=新的HashMap<字符串,字符串>();
的for(int i = 0; I< specnames.length;我++)
temp.put(specnames [I],cursor.getString(cursor.getColumnIndex(specnames [I])));
specs.add(临时); cursor.moveToNext();
} 如果(指针!= NULL)
cursor.close(); 返回规范;
}
有关文档 SQLiteDatabase.query()
说:
When you execute this query:
SELECT * FROM item WHERE (arg1=40 OR arg2=42) AND arg3=false
and bind the arguments using the selectionArgs
-parameter, the integers and the boolean will be bound as strings. This is not the same as in your database. I know you said all your columns are of type TEXT
, but read the SQLite Documentation:
That means, when you insert your integer values in your string-table, SQLite will convert those and store them as integers. The next important point is comparison:
The docs give the following example:
CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeric affinity
c BLOB, -- no affinity
d -- no affinity
);
-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1
In your case that means the following:
- Your
TEXT
-table gets integers inserted, which are then converted (and stored) asINTEGER
s. - When performing your comparison, you give in
TEXT
-values (which are your integers). Those should be (but don't need to be) converted toTEXT
-values, because your table indicates it's values are of typeTEXT
. - You compare
INTEGER
toTEXT
, which is not equal and therefor doesn't give you the correct results.
To insert different data-types (other then string) into the SQLiteDatabase, use Prepared Statements.
这篇关于Android的SQLite的选择ARGS []不正确的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!