SQLiteQueryBuilder中的selectionArgs不适用于列中的整数值 [英] selectionArgs in SQLiteQueryBuilder doesn't work with integer values in columns

问题描述

我正在尝试从数据库中选择一些数据，我有两段代码可以做到这一点:

I'm trying to select some data from database and I have two slices of code to do it:

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = ?", new String[]{getSID(db)}, null, null, null);

和

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = " + getSID(db), null, null, null, null);

它们之间的区别是，根据文档，第一个似乎更正确，但它也不起作用-光标为空.而不是第二个-我正在获取所需的所有数据.

The difference between them is that first one seems to be more correct according to documentation, but it also doesn't work - cursor is empty. Instead of the second one - I'm getting all data I need.

因此，我尝试在PC上使用数据库副本执行不同的SQL查询，而这就是我得到的:

So I tried to execute different SQL queries on my PC with a copy of database and that's what I've got:

SELECT col1, col2, col3 FROM SomeTables WHERE (id = '42')

此查询无效(此查询显然等于由第一个代码示例生成的查询)

This one doesn't work (and this query obviously equals to query, generated by first code sample)

SELECT col1, col2, col3 FROM SomeTables WHERE (id = 42)

而且这很好用(等于从第二个代码示例中查询).

And this one works fine (equals to query from second code sample).

据我所知，SQLite应该自动执行类型转换，但是出了点问题，我也不知道为什么.您对如何解决第一个代码示例有什么想法? (或者，也许是数据库?)

As I know, SQLite should perform type cast automatically, but something went wrong and I don't know why. Do you have any ideas about how first code sample can be fixed? (Or, perhaps, database?)

如果重要的话，这是带有id字段的表的简化CREATE脚本:

If it matters, here's simplified CREATE script of the table with id field:

CREATE TABLE SomeTable ( ID PRIMARY KEY, col1, col2, [...] )

UPD:而且，getSID(db)返回字符串对象.

UPD: And, by the way, getSID(db) returns String Object.

推荐答案

根据SQLite 文档 ，

According to SQLite documentation,

SQLite版本3数据库中的任何列(除了INTEGER PRIMARY KEY列之外)都可以用于存储任何存储类的值.

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

在我的情况下，这意味着我们不确定哪种数据类型将存储在列中.如果可以在将数据类型放入数据库时​​进行控制和转换-将数据添加到数据库时可以将id值转换为TEXT，并轻松使用selectionArgs.但这不是我的问题的答案，因为我必须按原样处理数据库内容.

In context of my case, that means that we can't be sure what data type will be stored in columns. If you can control and convert data types when they're putting into database - you can convert id values to TEXT when adding data to database and use selectionArgs easily. But it's not an answer for my question, because I have to deal with database content as is.

因此，可能的解决方案:

So, possible solutions:

a)将整数值嵌入到selection字符串中，而不将其包装到'中:

a) embed integer values in selection string without wrapping them into ':

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = " + getSID(db), null, null, null, null);

b)从selectionArgs:CAST(? as INTEGER)或CAST(id AS TEXT)强制转换值.我认为将列转换为TEXT是更好的解决方案，因为右操作数始终为TEXT，但是左操作数可以是任何值.所以:

b) cast values from selectionArgs: CAST(? as INTEGER) or CAST(id AS TEXT). I think, converting column to TEXT is better solution, because right operand is always TEXT, but the left one can be anything. So:

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "CAST(id AS TEXT) = ?", 
                    new String[]{getSID(db)}, null, null, null);

这篇关于SQLiteQueryBuilder中的selectionArgs不适用于列中的整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！