使用SQLite Android开发：查询结果不应该是空的 [英] Android development with sqlite: query result shouldn't be empty

问题描述

我的时候，而当在机器人执行返回什么安卓之外执行的是返回数据的相当大的查询。

• 在我的几件拆分查询，确定该工会是好的。
• 在我试穿了一组具有相同的行为更小的数据。
• 在我与不同的硬件和API的版本进行测试。
• 在我使用的rawQuery法常数值。 <一href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery%28java.lang.String,%20java.lang.String%5B%5D%29" rel="nofollow">http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String中[]）

此查询是为了取代其目前不支持完全外连接。

  SELECT IFNULL（STYPE，GTYPE）类型，IFNULL（SDATE，gdate）日期，IFNULL（sroute，groute）AS路线FROM（

SELECT sp.type AS STYPE，sp.date AS SDATE，-1作为GTYPE，gp.date AS gdate，sp.route AS sroute，gp.route AS groute
从Sensor_Point为SP LEFT JOIN GPS_Point AS GP ON gp._id IS NULL和sp.sent = 0和sp.route = gp.route和sp.route = 1
UNION ALL
SELECT sp.type AS STYPE，sp.date AS SDATE，-1作为GTYPE，gp.date AS gdate，sp.route AS sroute，gp.route AS groute
从GPS_Point AS GP LEFT JOIN Sensor_Point为SP ON sp._id IS NULL和gp.sent = 0和sp.route = gp.route和gp.route = 1

），路由= 1 ORDER BY日期ASC LIMIT 255
 

任何提示将大大AP preciated。

更新：

看的一样，问题是最终的查询参数，如果我设置这种方式：

 字串[] args =新的String [3];
的args [0] =的args [1] =的args [2] =1;
光标数据dataBase.rawQuery（SELECT_POINTS，参数）;
 

它不工作，而直接在查询硬编码值时的作品。

 光标数据= dataBase.rawQuery（SELECT_POINTS，NULL）;
 

解决方案

在Android数据库API，所有的查询参数都是字符串。 （这是一个可怕的设计错误。）

您查询对应：

  ... AND sp.route ='1'
 

尝试将参数字符串转换回一个数字是这样的：

  ... AND sp.route = CAST（？AS INT）
 

或只是把数量直接进入查询字符串。

I have a rather big query that is returning data when executed outside android while returning nothing when executed within android.

• I split the query in several pieces and determined that the union was ok.
• I tried on a smaller set of data with the same behavior.
• I've tested with different hardware and API versions.
• I'm using the rawQuery method with constant values. http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[])

This query was meant to replace a FULL OUTER JOIN which is not currently supported.

SELECT IFNULL(stype, gtype) AS type, IFNULL(sdate, gdate) AS date, IFNULL(sroute, groute) AS route FROM (

SELECT sp.type AS stype, sp.date AS sdate, -1 AS gtype, gp.date AS gdate, sp.route AS sroute, gp.route AS groute
FROM Sensor_Point AS sp LEFT JOIN GPS_Point AS gp ON gp._id IS NULL AND sp.sent=0 AND sp.route=gp.route AND sp.route=1
UNION ALL
SELECT sp.type AS stype, sp.date AS sdate, -1 AS gtype, gp.date AS gdate, sp.route AS sroute, gp.route AS groute
FROM GPS_Point AS gp LEFT JOIN Sensor_Point AS sp ON sp._id IS NULL AND gp.sent=0 AND sp.route=gp.route AND gp.route=1

) WHERE route=1 ORDER BY date ASC LIMIT 255

Any hints would be greatly appreciated.

Update:

Look's like the problem is finally with the query parameters, if I set it this way:

String[] args = new String[3];
args[0] = args[1] = args[2] = "1"; 
Cursor data dataBase.rawQuery(SELECT_POINTS, args);

It doesn't work, while it works when hardcoding values directly in the query.

Cursor data = dataBase.rawQuery(SELECT_POINTS, null);

解决方案

In the Android database API, all query parameters are strings. (This is a horrible design mistake.)

Your query corresponds to:

... AND sp.route='1'

Try to convert the parameter strings back into a number like this:

... AND sp.route = CAST(? AS INT)

or just put the number directly into the query string.

这篇关于使用SQLite Android开发：查询结果不应该是空的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！