CODEWITHSUNDEEP
androidsqlite
Yoo
Yoo

Reputation: 1441

Get all rows from SQLite

I have been trying to get all rows from the SQLite database. But I got only last row from the following codes.

FileChooser class:

public ArrayList<String> readFileFromSQLite() {

  fileName = new ArrayList<String>();

  fileSQLiteAdapter = new FileSQLiteAdapter(FileChooser.this);
  fileSQLiteAdapter.openToRead();
  cursor = fileSQLiteAdapter.queueAll();

  if (cursor != null) {
    if (cursor.moveToFirst()) {
      do {
        fileName.add(cursor.getString(cursor.getColumnIndex(FileSQLiteAdapter.KEY_CONTENT1)));
      } while (cursor.moveToNext());

    }
    cursor.close();
  }

  fileSQLiteAdapter.close();
  return fileName;
}

FileSQLiteAdapter class:

public Cursor queueAll() {
  String[] columns = new String[] { KEY_ID, KEY_CONTENT1 };

  Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, null,
                null, null, null, null);
  return cursor;
}

Please tell me where is my incorrect. Appreciate.

Upvotes: 47

Views: 133196

Answers (8)

Ali Hamza Ansari
Ali Hamza Ansari

Reputation: 21

a concise solution can be used for accessing the cursor rows.

while(cursor.isAfterLast)
{
    cursor.getString(0)
    cursor.getString(1)
}

These records can be manipulated with a loop

Upvotes: -1

abc
abc

Reputation: 9

public List<String> getAllData(String email)
{

    db = this.getReadableDatabase();
    String[] projection={email};

    List<String> list=new ArrayList<>();

    Cursor cursor = db.query(TABLE_USER, //Table to query
            null,    //columns to return
            "user_email=?",        //columns for the WHERE clause
            projection,        //The values for the WHERE clause
            null,       //group the rows
            null,       //filter by row groups
            null);
    //  cursor.moveToFirst();

    if (cursor.moveToFirst()) {
        do {

            list.add(cursor.getString(cursor.getColumnIndex("user_id")));
            list.add(cursor.getString(cursor.getColumnIndex("user_name")));
            list.add(cursor.getString(cursor.getColumnIndex("user_email")));
            list.add(cursor.getString(cursor.getColumnIndex("user_password")));
            // cursor.moveToNext();

        } while (cursor.moveToNext());
    }
    return list;
}

Upvotes: 0

Suragch
Suragch

Reputation: 511626

Using Android's built in method

If you want every column and every row, then just pass in null for the SQLiteDatabase column and selection parameters.

Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null, null);

More details

The other answers use rawQuery, but you can use Android's built in SQLiteDatabase. The documentation for query says that you can just pass in null to the selection parameter to get all the rows.

selection Passing null will return all rows for the given table.

And while you can also pass in null for the column parameter to get all of the columns (as in the one-liner above), it is better to only return the columns that you need. The documentation says

columns Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.

Example

SQLiteDatabase db = mHelper.getReadableDatabase();
String[] columns = {
        MyDatabaseHelper.COLUMN_1,
        MyDatabaseHelper.COLUMN_2,
        MyDatabaseHelper.COLUMN_3};
String selection = null; // this will select all rows
Cursor cursor = db.query(MyDatabaseHelper.MY_TABLE, columns, selection,
        null, null, null, null, null);

Upvotes: 6

roshan posakya
roshan posakya

Reputation: 1030

Cursor cursor = myDb.viewData();

        if (cursor.moveToFirst()){
              do {
                 String itemname=cursor.getString(cursor.getColumnIndex(myDb.col_2));
                 String price=cursor.getString(cursor.getColumnIndex(myDb.col_3));
                 String quantity=cursor.getString(cursor.getColumnIndex(myDb.col_4));
                 String table_no=cursor.getString(cursor.getColumnIndex(myDb.col_5));

                 }while (cursor.moveToNext());

                }

                cursor.requery();

Upvotes: 1

ArtiomLK
ArtiomLK

Reputation: 2260

This is almost the same solution as the others, but I thought it might be good to look at different ways of achieving the same result and explain a little bit:

Probably you have the table name String variable initialized at the time you called the DBHandler so it would be something like;

private static final String MYDATABASE_TABLE = "anyTableName";

Then, wherever you are trying to retrieve all table rows;

SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from " + MYDATABASE_TABLE, null);

List<String> fileName = new ArrayList<>();
if (cursor.moveToFirst()){
   fileName.add(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
   while(cursor.moveToNext()){
      fileName.add(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
   }
}
cursor.close();
db.close();

Honestly, there are many ways about doing this,

Upvotes: 4

Ferdous Ahamed
Ferdous Ahamed

Reputation: 21736

Update queueAll() method as below:

public Cursor queueAll() {

     String selectQuery = "SELECT * FROM " + MYDATABASE_TABLE;
     Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);

     return cursor;
}

Update readFileFromSQLite() method as below:

public ArrayList<String> readFileFromSQLite() {

    fileName = new ArrayList<String>();

    fileSQLiteAdapter = new FileSQLiteAdapter(FileChooser.this);
    fileSQLiteAdapter.openToRead();

    cursor = fileSQLiteAdapter.queueAll();

    if (cursor != null) {
        if (cursor.moveToFirst()) {
            do 
            {
                String name = cursor.getString(cursor.getColumnIndex(FileSQLiteAdapter.KEY_CONTENT1));
                fileName.add(name);
            } while (cursor.moveToNext());
        }
        cursor.close();
    }

    fileSQLiteAdapter.close();

    return fileName;
}

Upvotes: 1

Samir Mangroliya
Samir Mangroliya

Reputation: 40406

try:

Cursor  cursor = db.rawQuery("select * from table",null);

AND for List<String>:

if (cursor.moveToFirst()) {
  while (!cursor.isAfterLast()) {
    String name = cursor.getString(cursor.getColumnIndex(countyname));

    list.add(name);
    cursor.moveToNext();
  }
}

Upvotes: 92

Sam Khalil
Sam Khalil

Reputation: 27

I have been looking into the same problem! I think your problem is related to where you identify the variable that you use to populate the ArrayList that you return. If you define it inside the loop, then it will always reference the last row in the table in the database. In order to avoid this, you have to identify it outside the loop:

String name;
if (cursor.moveToFirst()) {

        while (cursor.isAfterLast() == false) {
            name = cursor.getString(cursor
                    .getColumnIndex(countyname));

            list.add(name);
            cursor.moveToNext();
        }
}

Upvotes: 1

Related Questions