Android sqlite database data displaying abnormally

Question

I am relatively new to android programming and I am having a few issues with how my database data is fetched. I am trying to create a simple messaging application. The data in the database is saving as expected but when it comes to displaying is where the issue is. The message activity displays all messages while the view message activity displays the activity thread. The issue is that the data displayed by he messages activity is ordered in a descending mode but the ID of each message loads ASCENDING and thereby messages do not get their actual conversation thread. I would like to know why the messages id are loaded in an ascending manner. My code is as below:

MESSAGES ACTIVITY

package com.package.name;

import android.annotation.TargetApi;
import android.content.Context;
import android.content.Intent;
import android.os.Build;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.support.v7.widget.Toolbar;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.PopupMenu;

import java.util.ArrayList;


public class MessageActivity extends ActionBarActivity {
private ListView obj;
DBHelper mydb;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_message);

    mydb = new DBHelper(this);
    ArrayList array_list = mydb.getAllMessages();
    ArrayAdapter arrayAdapter=new ArrayAdapter(this,android.R.layout.simple_list_item_1, array_list);

    obj = (ListView)findViewById(R.id.list);
    obj.setAdapter(arrayAdapter);
    obj.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
            // TODO Auto-generated method stub
            int id_To_Search = arg2 + 1;

            Bundle dataBundle = new Bundle();
            dataBundle.putInt("id", id_To_Search);

            Intent intent = new Intent(getApplicationContext(), ViewMessage.class);

            intent.putExtras(dataBundle);
            startActivity(intent);
        }
    });
}

VIEW MESSAGES ACTIVITY

package com.package.name;

import android.content.Context;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.support.v7.widget.Toolbar;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import java.util.ArrayList;


public class ViewMessage extends ActionBarActivity {

private ListView obj;
DBHelper mydb;

private int msgId;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_view_message);
    Bundle extras = getIntent().getExtras();
    Integer msgId = extras.getInt("id");

    mydb = new DBHelper(this);
    ArrayList array_list = mydb.getAllConversations(msgId);
    ArrayAdapter arrayAdapter=new ArrayAdapter(this,android.R.layout.simple_list_item_1, array_list);

    obj = (ListView)findViewById(R.id.list);
    obj.setAdapter(arrayAdapter);
}
}

DBHELPER

public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "sdDemoDb.db";
public static final String CONTACTS_TABLE_NAME = "contacts";
public static final String CONTACTS_COLUMN_ID = "id";
public static final String CONTACTS_COLUMN_NAME = "name";
public static final String CONTACTS_COLUMN_EMAIL = "email";
public static final String CONTACTS_COLUMN_STREET = "street";
public static final String CONTACTS_COLUMN_CITY = "place";
public static final String CONTACTS_COLUMN_PHONE = "phone";

//MESSAGE DETAILS TABLE
public static final String MESSAGES_TABLE_NAME = "messages";
public static final String MESSAGES_COLUMN_ID = "id";
public static final String MESSAGES_USER_ID = "user_id";
public static final String MESSAGES_COLUMN_SUBJECT = "subject";
public static final String MESSAGES_COLUMN_CATEGORY = "category";
public static final String MESSAGES_STATUS = "status";
public static final String MESSAGES_CREATED_AT = "created_at";

//CONVERSATION DETAILS TABLE
public static final String CONVERSATION_TABLE_NAME = "conversation";
public static final String CONVERSATION_COLUMN_ID = "id";
public static final String CONVERSATION_COLUMN_MESSAGE_ID = "message_id";
public static final String CONVERSATION_COLUMN_TYPE = "type";
public static final String CONVERSATION_COLUMN_MESSAGE = "message";
public static final String CONVERSATION_STATUS = "status";
public static final String CONVERSATION_CREATED_AT = "created_at";
private HashMap hp;

public DBHelper(Context context)
{
    super(context, DATABASE_NAME , null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table " + MESSAGES_TABLE_NAME + "("
                    + MESSAGES_COLUMN_ID + " integer primary key autoincrement, "
                    + MESSAGES_USER_ID + " integer, "
                    + MESSAGES_COLUMN_SUBJECT + " text, "
                    + MESSAGES_COLUMN_CATEGORY + " text, "
                    + MESSAGES_STATUS + " text, "
                    + MESSAGES_CREATED_AT + " text)"
    );
    db.execSQL(
            "create table " + CONVERSATION_TABLE_NAME + "("
                    + CONVERSATION_COLUMN_ID + " integer primary key autoincrement, "
                    + CONVERSATION_COLUMN_MESSAGE_ID + " integer, "
                    + CONVERSATION_COLUMN_MESSAGE + " text, "
                    + CONVERSATION_COLUMN_TYPE + " text, "
                    + CONVERSATION_STATUS + " text, "
                    + CONVERSATION_CREATED_AT + " text)"
    );
    db.execSQL(
            "create table contacts " +
                    "(" + CONTACTS_COLUMN_ID + " integer primary key autoincrement, name text,phone text,email text, street text,place text)"
    );
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS " + MESSAGES_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + CONVERSATION_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS contacts");
    onCreate(db);
}

public boolean createMessage  (String user_id, String subject, String category, String status, String time, String msg) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(MESSAGES_USER_ID, user_id);
    contentValues.put(MESSAGES_COLUMN_SUBJECT, subject);
    contentValues.put(MESSAGES_COLUMN_CATEGORY, category);
    contentValues.put(MESSAGES_STATUS, status);
    contentValues.put(MESSAGES_CREATED_AT, time);
    long lastId = db.insert(MESSAGES_TABLE_NAME, null, contentValues);
    createConv(lastId, msg, "q", "1", time);
    return true;
}

public boolean createConv  (long msg_id,String msg, String type, String status, String time)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(CONVERSATION_COLUMN_MESSAGE_ID, msg_id);
    contentValues.put(CONVERSATION_COLUMN_MESSAGE, msg);
    contentValues.put(CONVERSATION_COLUMN_TYPE, type);
    contentValues.put(CONVERSATION_STATUS, status);
    contentValues.put(CONVERSATION_CREATED_AT, time);
    db.insert(CONVERSATION_TABLE_NAME, null, contentValues);
    return true;
}

public boolean insertContact  (String name, String phone, String email, String street,String place)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("name", name);
    contentValues.put("phone", phone);
    contentValues.put("email", email);
    contentValues.put("street", street);
    contentValues.put("place", place);
    db.insert("contacts", null, contentValues);
    return true;
}

public int numberOfRows(){
    SQLiteDatabase db = this.getReadableDatabase();
    int numRows = (int) DatabaseUtils.queryNumEntries(db, CONTACTS_TABLE_NAME);
    return numRows;
}

public boolean updateMessage (Integer id, String user_id, String subject, String category, String status, String time, String msg)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(MESSAGES_USER_ID, user_id);
    contentValues.put(MESSAGES_COLUMN_SUBJECT, subject);
    contentValues.put(MESSAGES_COLUMN_CATEGORY, category);
    contentValues.put(MESSAGES_STATUS, status);
    contentValues.put(MESSAGES_CREATED_AT, time);
    db.update(MESSAGES_TABLE_NAME, contentValues, MESSAGES_COLUMN_ID +" = ? ", new String[] { Integer.toString(id) } );
    return true;
}

public boolean updateContact (Integer id, String name, String phone, String email, String street,String place)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("name", name);
    contentValues.put("phone", phone);
    contentValues.put("email", email);
    contentValues.put("street", street);
    contentValues.put("place", place);
    db.update("contacts", contentValues, "id = ? ", new String[] { Integer.toString(id) } );
    return true;
}

public Integer deleteContact (Integer id)
{
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete("contacts",
            "id = ? ",
            new String[] { Integer.toString(id) });
}

public ArrayList<String> getAllMessages() {
    ArrayList<String> array_list = new ArrayList<String>();

    //hp = new HashMap();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "SELECT * FROM " + MESSAGES_TABLE_NAME + " ORDER BY " + MESSAGES_COLUMN_ID + " DESC", null);
    res.moveToFirst();

    while(res.isAfterLast() == false){
        array_list.add(res.getString(res.getColumnIndex(MESSAGES_COLUMN_SUBJECT)));
        res.moveToNext();
    }
    return array_list;
}

public ArrayList<String> getAllConversations(Integer msgId) {
    ArrayList<String> array_list = new ArrayList<String>();

    //hp = new HashMap();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "SELECT * FROM " + CONVERSATION_TABLE_NAME + " WHERE " + CONVERSATION_COLUMN_MESSAGE_ID  + " = " + msgId + " ORDER BY " + CONVERSATION_COLUMN_ID + " ASC", null);
    res.moveToFirst();

    while(res.isAfterLast() == false){
        array_list.add(res.getString(res.getColumnIndex(CONVERSATION_COLUMN_MESSAGE)));
        res.moveToNext();
    }
    return array_list;
}

public ArrayList<String> getAllCotacts() {
    ArrayList<String> array_list = new ArrayList<String>();

    //hp = new HashMap();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "SELECT * FROM " + CONTACTS_TABLE_NAME + " ORDER BY " + CONTACTS_COLUMN_ID + " DESC", null);
    res.moveToFirst();

    while(res.isAfterLast() == false){
        array_list.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_NAME)));
        res.moveToNext();
    }
    return array_list;
}
}

VCHe · Accepted Answer · 2015-08-17 10:11:11Z

1

Try to change

int id_To_Search = arg2 + 1;

into:

int id_To_Search = arg0.getCount() - arg2;

edited Aug 17, 2015 at 10:11

answered Aug 17, 2015 at 7:06

VCHe

324 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Biko Over a year ago

The gerAllConversations function displays the conversation after the message id has been declared. The problem is that the getAllMessages function in the messages activity displays the message subject descending but the id mapped to each subject is displayed ascending such that the last subject has the id value of the first subject

VCHe Over a year ago

arg2 in onItemClick is a position in your Listview and it isn't id of message in db. So... If you get allMessages in ASC, the position in ListView and id will be the same. But both in ASC

Biko Over a year ago

The ` getAllMessages` function gets the messages in desc order. If it is not the message id, what piece of code should I add or modify?

VCHe Over a year ago

Can you get messages in asc order? Or you need only desc? I think you will solve the issue if you change it into ASC in the "getAllMessages" method. But ASC order can be unconvenient for you.

Biko Over a year ago

Yes, it is inconvenient because I want to show new messages on top. Is there no way around it?

Collectives™ on Stack Overflow

Android sqlite database data displaying abnormally

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related