通过查询显示列表视图android中的一些意外结果 [英] Group by query showing some unexpected results in List view android

问题描述

我有一个列表视图，其中显示来自联系人号码的收到消息。现在的问题是，如果我从1234567收到5条消息，从56789收到3条消息，那么我的主列表视图显示8条消息，尽管我只是想通过这两个数字在我的主lisvtiew中显示11条消息，类似于短消息应用作为线程和项目点击剩余的消息应显示该特定号码。
有人通过电话号码查询使用Group来通过电话号码查询结果。
当收到任何短信时，我将它保存在名为smss的表格中。
这是从数据库获取数据时的代码：

  public ArrayList< Sms> fetchScreenedSms（）{
 Sms a = new Sms（）; 
 ArrayList< Sms> smsInbox = new ArrayList< Sms>（）; 
 String query_fetchSMS =select * from+smss+group by+contactnumber+\; 
 DBtableforNotSpam smsD = new DBtableforNotSpam（this）; 
 SQLiteDatabase dbw = smsD.getWritableDatabase（）; 
 Cursor cursor = dbw.rawQuery（query_fetchSMS，null）; 
 if（cursor！= null）{
 cursor.moveToLast（）; 
 if（cursor.getCount（）> 0）{
 
 do {
 
 Sms message = new Sms（）; 
 message.id = cursor.getInt（游标
 .getColumnIndex（id））; 
 message.messageNumber = cursor.getString（cursor 
 .getColumnIndex（contactnumber））; 
 message.messageSender = cursor。 getString（cursor 
 .getColumnIndex（contactname））; 
 message.messageContent = cursor.getString（cursor 
 .getColumnIndex（message））; 
 message.setDate cursor.getString（cursor 
 .getColumnIndex（date）））; 
 smsInbox.add（message）; 
} while（cursor.moveToPrevious（ ））; 
} else {
 empty.setVisibility（View.VISIBLE）; 
} 
  

这是我在数据库中保存短信的代码：

pre $ public $ screen $ message $ {
DBtableforNotSpam smsdb = new DBtableforNotSpam（上下文）;
SQLiteDatabase dbw = smsdb.getWritableDatabase（）;

字符串query_insertSMS =insert into+smss+（+contactnumber+，+contactname+，+message
+， +date+）values（\+ msg_from.toString（）+\，\+ msgSender +\，\+ msgBody
+ \，\+ msgDate +\）;

dbw.execSQL（query_insertSMS）;
smsdb.close（）;
dbw.close（）;
abortBroadcast（）;






现在我通过查询在由某人建议的fetchScreenSms方法中应用组但如果应用这个，然后listview显示什么，即它是空的列表视图数据：

  String query_fetchSMS =select * from+smss +group by+contactnumber+= \+\; 
  

这是由某人推荐的，但它在列表视图
中什么都没显示，当我应用这个然后listview显示与该号码有关的数据。

  String query_fetchSMS =select * from+smss+group by +contactnumber+= \+1234567+\; 
  

在查询之后，listview在主列表视图中显示一条来自此编号的消息，然后显示5条消息在这里面。这正是我想要的。但为什么上面的查询不起作用？我想显示两个号码信息

问题发生在哪里？
当通过查询使用简单组时，不显示结果，但当我在组中指定数字时显示结果。
任何人都请帮助Iam卡住

解决方案

如果您使用准备好的语句。它们通常更快（如果您需要多次执行相同的查询），更强大，并且通常不会导致语法错误，难以解决。

  PreparedStatement pstmt = con.prepareStatement（insert into smss+ 
（contactnumbe，contactname，message，date）+ 
values（？，？，？，？ ）; 
  

看看它有多简单吗？然后你可以逐个绑定参数。需要担心正确转义它们，准备好的语句会自动为你处理。

  stmt.setInt（msg_from.toString（ ））; 
  

等等每个参数。尝试类似这样：
$ b $ pre $ 通过contactnumber从smss group中选择contactnumber，contactname，count（*）;


请注意，您可以执行SELECT * FROM smss，但日期和消息列是不确定的，因为哟你正在按联系号码分组。如果来自同一号码的多条消息不能保证将显示哪个日期和消息。然而，这也可以通过巧妙使用子查询和命令来控制。

I have a List view in which I am showing received messages from contact numbers. Now the problem was that if i received 5 messages from "1234567" and 3 messages from "56789" then my main listview was showing 8 messages although i just want to show 1 1 message by both these numbers in my main lisvtiew similar to sms apps as threads and on item click remaining messages should show for that particular number. Somebody answer me to use Group by phonenumber query to query the results by phone number. When any sms received i saved it in table named as "smss". This is my code when fetching data from database:

public ArrayList<Sms> fetchScreenedSms() {
  Sms a = new Sms();
ArrayList<Sms> smsInbox = new ArrayList<Sms>();
String query_fetchSMS = "select * from " + "smss" +  " group by " + "contactnumber" + "\"" ;
DBtableforNotSpam smsD = new DBtableforNotSpam(this);
SQLiteDatabase dbw = smsD.getWritableDatabase();
Cursor cursor = dbw.rawQuery(query_fetchSMS, null);
if (cursor != null) {
  cursor.moveToLast();
  if (cursor.getCount() > 0) {

    do {

      Sms message = new Sms();
      message.id = cursor.getInt(cursor
          .getColumnIndex("id"));
      message.messageNumber = cursor.getString(cursor
          .getColumnIndex("contactnumber"));
      message.messageSender = cursor.getString(cursor
          .getColumnIndex("contactname"));
      message.messageContent = cursor.getString(cursor
          .getColumnIndex("message"));
      message.setDate(cursor.getString(cursor
          .getColumnIndex("date")));
      smsInbox.add(message);
    } while (cursor.moveToPrevious());
  } else {
     empty.setVisibility(View.VISIBLE);
    }

this is my code for saving sms in database:

public void screenMessagee(Context context, String msg_from, String msgSender,
    String msgBody, String msgDate) {
    DBtableforNotSpam smsdb = new DBtableforNotSpam(context);
    SQLiteDatabase dbw = smsdb.getWritableDatabase();

    String query_insertSMS = "insert into " + "smss" + "(" + "contactnumber" + "," + "contactname" + "," + "message"
        + "," + "date" + ") values (\"" + msg_from.toString() + "\", \"" + msgSender + "\",\"" + msgBody
        + "\",\"" + msgDate + "\")";

    dbw.execSQL(query_insertSMS);
    smsdb.close();
    dbw.close();
    abortBroadcast();

  }

Now i applied group by query in fetchScreenSms method suggested by someone but if applied this then listview is showing nothing i.e. it empty listview data:

String query_fetchSMS = "select * from " + "smss" +  " group by " + "contactnumber" + " = \"" + "\"" ;

This is suggested by someone but it shows nothing in list view and when i apply this then listview show data related to that number.

String query_fetchSMS = "select * from " + "smss" +  " group by " + "contactnumber" + " = \"" + "1234567" + "\"" ;

After this query, the listview show me one message from this number in my main listview and then 5 messages inside this. This is exactly what i wants. But why the upper query not working? I want to show both numbers messages

What and where is the problem occuring? Not showing results when using simple group by query but showing result when i specify number in group by. Anyone please help Iam stuck

解决方案

You will not have these troubles if you use prepared statements. They are usually faster (if you need to do the same query multiple times), more robust and generally dont' lead to syntax errors that are hard to solve.

PreparedStatement pstmt = con.prepareStatement( "insert into smss " +  
   "(contactnumbe,contactname,message,date)" +
   "values (?,?,?,?)";

See how much simpler it is? Then you can bind the parameters one by one. NO need to worry about escaping them properly. Prepared statements take care of that for you automatically.

  stmt.setInt(msg_from.toString());

and so on for each of the parameters. Getting to your GROUP by problem, I would try something like this:

"select contactnumber, contactname, count(*) from smss group by contactnumber" ;

Note that you can do SELECT * FROM smss, but the date and the messages columns are indeterminate because you are grouping by contact number. If there are multiple messages from that same number there is no assurance of which date and message will be shown. However that too can be controlled by clever use of sub queries and order by.

这篇关于通过查询显示列表视图android中的一些意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！