CODEWITHSUNDEEP
javaandroidsqlitelistview
Tony
Tony

Reputation: 163

Android: Sqlite on ListView

I need help in creating a listview that shows the elements of a database sql.. for first created a small xml layout with 3 fields TextView with id: Text1,Text2 and Text3 and one listview in my activity. After I have grouped the columns in an interface

public interface TableRegistry extends BaseColumns{

     String TABLE_NAME = "Registry";

     String TYPE = "Type";
     String DATE = "Date";
     String STATUS = "Status";
     String NUMBER = "Number";
     String MESSAGE = "Message";
     String OTHER = "Other";

     String[] COLUMNS = new String[]
                { _ID, TYPE, DATE, STATUS, NUMBER, MESSAGE, OTHER };
            }

After I created the database class..

public class APdatabaseClass extends  SQLiteOpenHelper {

    private static final String DATABASE_NAME = "APdatabase";
    private static final int DATABASE_VERSION = 1;

    public APdatabaseClass(Context context) {
        super(context, DATABASE_NAME , null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "CREATE TABLE {0} ({1} INTEGER PRIMARY KEY AUTOINCREMENT," + 
                " {2} TEXT NOT NULL,{3} TEXT NOT NULL,{4} TEXT NOT NULL,{5} TEXT NOT NULL,{6} TEXT NOT NULL, {7} TEXT NOT NULL);";
            db.execSQL(MessageFormat.format(sql, TableRegistry.TABLE_NAME, TableRegistry._ID,
                TableRegistry.TYPE, TableRegistry.DATE, TableRegistry.STATUS, TableRegistry.NUMBER, TableRegistry.MESSAGE, TableRegistry.OTHER));

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TableRegistry.TABLE_NAME);

        // Create tables again
        onCreate(db);       
    }

Now how do I connect my database to listview in my activity with SimpleCursorAdapter or something? I need to populate the 3 TextView fields with:

TableRegistry.TYPE, TableRegistry.DATE, TableRegistry.STATUS.

thanks in advance!

Upvotes: 1

Views: 523

Answers (2)

Barak
Barak

Reputation: 16393

It seems like you are going to a bit of extra work. Here's how I do it normally:

First create method in your database helper to fetch the information you want:

public Cursor fetchListData() {
    return mDb.query(TableRegistry.TABLE_NAME, new String[] { TableRegistry._ID,
        TableRegistry.TYPE, TableRegistry.DATE, TableRegistry.STATUS }, null,null, 
        null, null, null);
}

Then in whatever class you want to create the list:

APdatabaseClass mDbHelper = new APdatabaseClass(getActivity());
mDbHelper.open();

Cursor c = fetchListData();
getActivity().startManagingCursor(c);

String[] from = new String[] { TableRegistry.TYPE, TableRegistry.DATE, TableRegistry.STATUS };
int[] to = new int[] {  R.id.Text1, R.id.Text2, R.id.Text3  };
SimpleCursorAdapter data = new SimpleCursorAdapter(getActivity(), R.layout.yourlistlayout, c, from, to);
setListAdapter(data);

Note that this assumes you are using a ListFragment or ListActivity. If you aren't, it would be slightly different in how you set the adapter.

Hope this helps!

Upvotes: 2

Paresh Mayani
Paresh Mayani

Reputation: 128428

Yes you can now fire queries by using raqQuery() method, you will get Cursor in result.

Now, you have to iterate through the cursor and fetch the data.

For example,

Cursor cur = db.raqQuery("Select * from myTable",null);
if (cur != null) {
    if (cur.moveToFirst()) {
        do {
            cur.getString(cur.getColumnIndex("Name"))); // "Name" is the field name in table
        } while (cur.moveToNext());
    }
}

Upvotes: 0

Related Questions