光标返回错误的值 - 源码 - Android电子 [英] Cursor returns wrong values - sqlite - Android
本文介绍了光标返回错误的值 - 源码 - Android电子的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的深化发展短信报名，
我试图从每个会话的最后一个短信。
这是我的SQL语句：
SELECT MAX（smsTIMESTAMP）AS smsTIMESTAMP，_id，SMSID，smsCONID，smsMSG，smsNUM，smsREAD，smsTYPE，smsSHORTMSG，COUNT（*）AS smsNUMMESSAGES FROM短信GROUP BY smsCONID ORDER BY smsTIMESTAMP递减
我跑在SQLite的专家查询，我得到正确的响应：
然而，当我在我的应用程序运行它，我得到：
下面是我的表
下面是我Datamanipulator类：
公共类DataManipulator {
私有静态最后弦乐DATABASE_NAME =smsapplication.db;
私有静态最终诠释DATABASE_VERSION = 3;
静态最后弦乐TABLE_NAME =短信;
私有静态上下文的背景下;
静态SQLiteDatabase分贝;
私有静态DataManipulator实例; 私人SQLiteStatement的insertstmt; 私有静态最后弦乐INSERT =插入或忽略为
+ TABLE_NAME +（？？？？？？？？，，，，，，，）（SMSID，smsCONID，smsMSG，smsNUM，smsREAD，smsTIMESTAMP，smsTYPE，smsSHORTMSG）值; 公共DataManipulator（上下文的背景下）{
DataManipulator.context =背景;
OpenHelper openHelper =新OpenHelper（DataManipulator.context）;
DataManipulator.db = openHelper.getWritableDatabase（）;
this.insertStmt = DataManipulator.db.compileStatement（INSERT）;
}
公共静态DataManipulator的getInstance（上下文mContext）
{
如果（例如== NULL）
{
例如=新DataManipulator（mContext）;
}
返回实例;
}
众长插入（INT SMSID，诠释smsCONID，弦乐smsMSG，
串smsNUM，INT smsREAD，长smsTIMESTAMP，字符串smsTYPE，字符串smsSHORTMSG）{
this.insertStmt.bindLong（1，SMSID）;
this.insertStmt.bindLong（2，smsCONID）;
this.insertStmt.bindString（3，smsMSG）;
this.insertStmt.bindString（4，smsNUM）;
this.insertStmt.bindLong（5，smsREAD）;
this.insertStmt.bindString（6，将String.valueOf（smsTIMESTAMP））;
this.insertStmt.bindString（7，smsTYPE）;
this.insertStmt.bindString（8，smsSHORTMSG）;
返回this.insertStmt.executeInsert（）;
} 公共无效deleteAll（）{
db.delete（TABLE_NAME，NULL，NULL）;
} 公共光标getLastSmsForAllConversations（）
{
光标光标= db.query（TABLE_NAME，新的String [] {_id，MAX（smsTIMESTAMP）AS smsTIMESTAMP，smsCONID，smsNUM，smsREAD，smsTYPE，smsSHORTMSG，COUNT（ *）AS smsNUMMESSAGES}，
NULL，NULL，smsCONID，NULL，smsTIMESTAMP DESC）;
返回游标;
}
公共光标getConversationMessages（INT conID）
{
光标光标= db.query（TABLE_NAME，新的String [] {_id，SMSID，smsCONID，smsMSG，smsNUM，smsREAD，smsTIMESTAMP，smsTYPE，smsSHORTMSG} ，
smsCONID =+ conID，NULL，NULL，NULL，smsTIMESTAMP ASC）;
返回游标;
}
公共无效printCursor（）
{
光标光标= db.rawQuery（SELECT MAX（smsTIMESTAMP）AS smsTIMESTAMP，_id，SMSID，smsCONID，smsMSG，smsNUM，smsREAD，smsTYPE，smsSHORTMSG，COUNT（*）AS smsNUMMESSAGES FROM短信GROUP BY smsCONID ORDER BY smsTIMESTAMP递减，空）; // db.query（TABLE_NAME，新的String [] {_id，smsTIMESTAMP AS smsTIMESTAMP，smsCONID，smsNUM，smsREAD，smsTYPE，smsSHORTMSG}，smsCONID = 40， NULL，NULL，NULL，smsTIMESTAMP DESC）;
如果（cursor.moveToFirst（））{
做{
字符串数据=;
数据= cursor.getString（cursor.getColumnIndex（smsTIMESTAMP））+ - + cursor.getString（cursor.getColumnIndex（smsSHORTMSG））;
Log.i（数据，数据）;
}而（cursor.moveToNext（））;
}
cursor.close（）;
} 私有静态类OpenHelper扩展SQLiteOpenHelper { OpenHelper（上下文的背景下）{
超（背景下，DATABASE_NAME，空，DATABASE_VERSION）;
} @覆盖
公共无效的onCreate（SQLiteDatabase DB）{
db.execSQL（CREATE TABLE+ TABLE_NAME +（_id INTEGER，SMSID INTEGER PRIMARY KEY，smsCONID INTEGER，smsMSG TEXT，smsNUM TEXT，INTEGER smsREAD，smsTIMESTAMP INTEGER，smsTYPE TEXT，smsSHORTMSG TEXT））;
} @覆盖
公共无效onUpgrade（SQLiteDatabase分贝，INT oldVersion，诠释静态网页）{
db.execSQL（DROP TABLE IF EXISTS+ TABLE_NAME）;
的onCreate（DB）;
}
@覆盖
公共同步无效的close（）{ 如果（DB！= NULL）
db.close（）; super.close（）; }
}
}
解决方案
当您使用 GROUP BY ，结果中的每一行对应于原始表的多个行。
有三种可能如何将这些结果被计算：
- 像
MAX或聚合函数列COUNT计算从该组中的所有行的值; - 出现在
GROUP BY子句只是取自列的任何的记录组（因为组中的所有记录具有相同该列的值）; - 其他列中的一个问题，因为该组中的行可能具有在其中不同的值。
在标准SQL中，这样的列是被禁止的; SQLite的允许他们，但只是给你从组，这是几乎从来没有你想要的任何随机行的值。
使用SQLite 3.7.11开始，你相匹配的行中的MIN或MAX获取值;这是你在SQLite的防爆preRT看，但不与旧的SQLite的机器人。
要解决你的问题，你必须首先使用 GROUP BY 来获得足够的信息来识别你想要的记录：
SELECT smsCONID， - OK：在GROUP BY使用
MAX（smsTIMESTAMP）AS smsTIMESTAMP， - OK：总MAX
COUNT（*）AS smsNUMMESSAGES - OK：总COUNT
从短信
GROUP BY smsCONID
ORDER BY smsTIMESTAMP DESC
然后，加入与原短信表结果表来获得这些记录的其他列：
SELECT *
FROM（SELECT smsCONID，
MAX（smsTIMESTAMP）AS smsTIMESTAMP，
COUNT（*）AS smsNUMMESSAGES
从短信
GROUP BY smsCONID）AS分组
JOIN短信
ON grouped.smsCONID = sms.smsCONID
和grouped.smsTIMESTAMP = sms.smsTIMESTAMP
ORDER BY smsTIMESTAMP DESC
I'm developping an Sms Application, I'm trying to get the last sms from each conversation.
here's my SQL Statement :
SELECT MAX(smsTIMESTAMP) AS smsTIMESTAMP,_id, smsID, smsCONID, smsMSG, smsNUM, smsREAD, smsTYPE, smsSHORTMSG, COUNT(*) AS smsNUMMESSAGES FROM sms GROUP BY smsCONID ORDER BY smsTIMESTAMP desc
I ran the query in SQLite Expert and I get the right response :
however when I run it in my app I get :
Here's my table
Here's My Datamanipulator Class :
public class DataManipulator {
private static final String DATABASE_NAME = "smsapplication.db";
private static final int DATABASE_VERSION = 3;
static final String TABLE_NAME = "sms";
private static Context context;
static SQLiteDatabase db;
private static DataManipulator instance;
private SQLiteStatement insertStmt;
private static final String INSERT = "insert or ignore into "
+ TABLE_NAME + " (smsID, smsCONID, smsMSG, smsNUM, smsREAD, smsTIMESTAMP, smsTYPE, smsSHORTMSG) values (?,?,?,?,?,?,?,?)";
public DataManipulator(Context context) {
DataManipulator.context = context;
OpenHelper openHelper = new OpenHelper(DataManipulator.context);
DataManipulator.db = openHelper.getWritableDatabase();
this.insertStmt = DataManipulator.db.compileStatement(INSERT);
}
public static DataManipulator getInstance(Context mContext)
{
if(instance == null)
{
instance = new DataManipulator(mContext);
}
return instance;
}
public long insert(int smsID, int smsCONID, String smsMSG,
String smsNUM, int smsREAD, long smsTIMESTAMP, String smsTYPE, String smsSHORTMSG) {
this.insertStmt.bindLong(1, smsID);
this.insertStmt.bindLong(2, smsCONID);
this.insertStmt.bindString(3, smsMSG);
this.insertStmt.bindString(4, smsNUM);
this.insertStmt.bindLong(5, smsREAD);
this.insertStmt.bindString(6, String.valueOf(smsTIMESTAMP));
this.insertStmt.bindString(7, smsTYPE);
this.insertStmt.bindString(8, smsSHORTMSG);
return this.insertStmt.executeInsert();
}
public void deleteAll() {
db.delete(TABLE_NAME, null, null);
}
public Cursor getLastSmsForAllConversations()
{
Cursor cursor = db.query(TABLE_NAME, new String[] {"_id"," MAX(smsTIMESTAMP) AS smsTIMESTAMP ", "smsCONID", "smsNUM", "smsREAD", "smsTYPE","smsSHORTMSG","COUNT(*) AS smsNUMMESSAGES" },
null, null, "smsCONID", null, "smsTIMESTAMP desc");
return cursor;
}
public Cursor getConversationMessages(int conID)
{
Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "smsID", "smsCONID", "smsMSG", "smsNUM", "smsREAD", "smsTIMESTAMP", "smsTYPE","smsSHORTMSG" },
"smsCONID="+conID, null, null, null, "smsTIMESTAMP asc");
return cursor;
}
public void printCursor()
{
Cursor cursor = db.rawQuery("SELECT MAX(smsTIMESTAMP) AS smsTIMESTAMP,_id, smsID, smsCONID, smsMSG, smsNUM, smsREAD, smsTYPE, smsSHORTMSG, COUNT(*) AS smsNUMMESSAGES FROM sms GROUP BY smsCONID ORDER BY smsTIMESTAMP desc", null);
// db.query(TABLE_NAME, new String[] {"_id","smsTIMESTAMP AS smsTIMESTAMP ", "smsCONID", "smsNUM", "smsREAD", "smsTYPE","smsSHORTMSG" },"smsCONID=40", null, null, null, "smsTIMESTAMP desc");
if (cursor.moveToFirst()){
do{
String data ="";
data= cursor.getString(cursor.getColumnIndex("smsTIMESTAMP"))+" - " +cursor.getString(cursor.getColumnIndex("smsSHORTMSG"));
Log.i("data", data);
}while(cursor.moveToNext());
}
cursor.close();
}
private static class OpenHelper extends SQLiteOpenHelper {
OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER , smsID INTEGER PRIMARY KEY, smsCONID INTEGER, smsMSG TEXT,smsNUM TEXT, smsREAD INTEGER, smsTIMESTAMP INTEGER, smsTYPE TEXT, smsSHORTMSG TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
@Override
public synchronized void close() {
if (db != null)
db.close();
super.close();
}
}
}
解决方案
When you are using GROUP BY, each row of the result corresponds to multiple rows of the original table.
There are three possibilities how these results are computed:
- Columns with an aggregate function like
MAXorCOUNTcompute the values from all rows in the group; - columns that appear in the
GROUP BYclause are just taken from any record in the group (because all records in the group have the same value for that column); - other columns are a problem, because the rows in the group might have different values in them.
In standard SQL, such columns are forbidden; SQLite allows them, but just gives you values from any random row in the group, which is almost never what you want.
Beginning with SQLite 3.7.11, you get values from the row that matches a
MINorMAX; this is what you see in SQLite Exprert, but not on Androids with an older SQLite.
To solve your problem, you have to first use a GROUP BY to get enough information to identify the records you want:
SELECT smsCONID, -- OK: used in GROUP BY
MAX(smsTIMESTAMP) AS smsTIMESTAMP, -- OK: aggregate MAX
COUNT(*) AS smsNUMMESSAGES -- OK: aggregate COUNT
FROM sms
GROUP BY smsCONID
ORDER BY smsTIMESTAMP DESC
Then, join that result table with the original sms table to get the other columns of these records:
SELECT *
FROM (SELECT smsCONID,
MAX(smsTIMESTAMP) AS smsTIMESTAMP,
COUNT(*) AS smsNUMMESSAGES
FROM sms
GROUP BY smsCONID) AS grouped
JOIN sms
ON grouped.smsCONID = sms.smsCONID
AND grouped.smsTIMESTAMP = sms.smsTIMESTAMP
ORDER BY smsTIMESTAMP DESC
这篇关于光标返回错误的值 - 源码 - Android电子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
