安卓：SQLite的查询不具约束力的整数参数？ [英] Android: SQLite Query not binding integer parameters?

问题描述

我和参数DB Android平台（2.2）进行查询时遇到的问题。我创建的表是这样的：

I have problems when making queries with parameters to DB on Android platform (2.2). I have created table like this:

db.execSQL("CREATE VIRTUAL TABLE " + Msg._TABLE_NAME + " USING FTS3 ("
            + Msg._ID + " INTEGER, "
            (...)
            + Msg.READ + " SHORT DEFAULT 0,"
            + Msg.URGENT + " SHORT DEFAULT 0"
            + ");");

然后我试图查询该使用参数化查询：

Then I am trying to query this using parametrized query:

String[] columns = new String[] {Msg.ROWID, Msg.TITLE, Msg.READ, Msg.URGENT};
(...)
getContentResolver().query(Msg.CONTENT_URI, columns, 
    Msg.URGENT + "=? AND " + Msg.READ + "=?" + , whereArgs, null);

其中， whereArgs 变化对每个查询：

String[] urgentUnread = new String[]{"1", "0"};
String[] regularUnread = new String[]{"0", "0"};

但不管它返回0结果/行虽然数据存在。内容提供商不会改变PARAMS和使用的QueryBuilder以及调用查询时，查询将返回没有直接：

but no matter what it returns 0 results/rows even though data exist. The content provider does not change the params and the query returns nothing using QueryBuilder as well as when calling query "directly":

Cursor c = db.query(tables, columns, where, whereArgs, groupBy, having, orderBy, limit);

查询工作，如果我这样做只是使用concat：

The query works if I do just String concat:

getContentResolver().query(Msg.CONTENT_URI, columns, 
    Msg.READ + "=0 AND " + Msg.URGENT + "=1", null, null);

但似乎杀参数查询的目的，是讨厌的缓存。 Dalvik的抱怨（做大量的查询之后），有在高速缓存中的查询中没有空间，具有讽刺意味的​​，告诉我，使用与参数化查询的？。我希望，相信我：）

but that seems to kill the purpose of param queries and is nasty to cache. Dalvik complains (after making lot of queries) that there is no space in cache for query and, ironically, tells me to use parametrized queries with '?'. I would love to, trust me :)

我知道，参数被绑定为字符串，但我根本无法相信......因为那将是主要的......啊哈JavaDoc的状态，... WTF 的

I know JavaDoc states that parameters are bound as StringS but I just simply can't believe that... because that would be major ...ahem, ... WTF

在哪里我做错什么了吗？

Where did I go wrong here?

在此先感谢。

推荐答案

这是OP，我正在研究进一步的实验，并来到了结论，即有 FTS3 惹的祸。因为我需要的数据是搜索由我创建虚拟表全文使用FTS3 ，然后将参数绑定失败。

This is OP, I was researching and experimenting further and came to conclusion that there is FTS3 to blame. Since I need the data to be searchable by fulltext I was creating VIRTUAL TABLE USING FTS3 and then the parameters binding failed.

由于我没有想查询影子表（ Msg_content ）直接，我的解决办法是将数据分割成2相关的表：

As I do not want to query shadow table (Msg_content) directly, my solution is to split data into 2 related tables:

db.execSQL("CREATE TABLE " + Msg._TABLE_NAME + " (" +
    Msg._ID + PRIMARY_KEY_AUTOINC + 
    Msg.PRIORITY + " TEXT," +
    Msg.RECEIVED + " INTEGER," +
    Msg.MOBILE_STATUS + " INTEGER DEFAULT 0," +
    Msg.READ + " SHORT DEFAULT 0," +
    Msg.FLASH + " SHORT DEFAULT 0" +
");");

db.execSQL("CREATE VIRTUAL TABLE " + MsgText._TABLE_NAME + " USING FTS3 (" + 
    MsgText._ID + PRIMARY_KEY +
    MsgText.TITLE + " TEXT," +
    MsgText.CONTENT + " TEXT," +
    MsgText.KEYWORDS + " TEXT," +
    "FOREIGN KEY(" + MsgText._ID + ") " +
    "REFERENCES " + Msg._TABLE_NAME + "(" + Msg._ID + ") " +
");");

然后，我创建的视图通过查询使用：

Then I created View to use by queries:

db.execSQL("CREATE VIEW IF NOT EXISTS " + View.MSG_CONTENT +
    " AS SELECT " +
    Msg._TABLE_NAME + "." + Msg._ID + ", " +
    Msg._TABLE_NAME + "." + Msg.READ + ", " +
    Msg._TABLE_NAME + "." + Msg.FLASH + ", " +
(...)
    MsgText._TABLE_NAME + "." + MsgText.TITLE + ", " +
    MsgText._TABLE_NAME + "." + MsgText.CONTENT +
    " FROM " + Msg._TABLE_NAME + ", " + MsgText._TABLE_NAME +
    " WHERE " + Msg._TABLE_NAME + "." + Msg._ID + "=" +
    MsgText._TABLE_NAME + "." + MsgText._ID);

这都非常好，我为我能查询使用的参数数据，并在需要的时候做的全文检索。查询性能是一样的只使用一个表时。

This works very well for me as I can query data using parameters and do fulltext search when needed. Query performance is the same as when using just one table.

我希望这可以帮助别人谁可能碰到同样的问题。

I hope this helps someone else who might bump into the same issue.

干杯，
PES

Cheers,
PeS

P.S。经过元和它是确定回复自我，显然

P.S. Checked Meta and it is OK to reply to self, apparently.

这篇关于安卓：SQLite的查询不具约束力的整数参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！