shaydoe shaydoe - 24 days ago 12
Android Question

Why is my ListView not showing data from SQLite Database?

I am new to android development so this may seem like an obvious question but I can't figure it out. I am trying to populate a list view with data stored in a SQLite database. I have managed to populate the database.k However, I can't figure out why the data is not showing up in the list view. My code runs fine with no errors. Can someone please help me out. I will truly appreciate it!

MainActivity.java:

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.database.Cursor;
import java.util.List;

public class MainActivity extends AppCompatActivity {

SimpleCursorAdapter simpleCursorAdapter;
SqlHelper sqlHelper;
ListView list;

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

list = (ListView) findViewById(R.id.listView);
sqlHelper = new SqlHelper(this, null, null, 1);



/** CRUD Operations **/
// add Books
sqlHelper.addBook(new Book("Professional Android 4 Application Development", "Reto Meier", 4));
sqlHelper.addBook(new Book("Beginning Android 4 Application Development", "Wei-Meng Lee", 2));
sqlHelper.addBook(new Book("Programming Android", "Wallace Jackson", 3));
sqlHelper.addBook(new Book("Hello, Android", "Ben Wallace", 1));
// get all books
List<Book> list = sqlHelper.getAllBooks();
//sqlHelper.getAllBooks();
sqlHelper.getIds(list.get(0));

displayBookList();

}

private void displayBookList() {
try
{
Cursor cursor = sqlHelper.getBooks();
if (cursor == null)
{
return;
}
if (cursor.getCount() == 0)
{
return;
}
String[] columns = new String[] {
sqlHelper.KEY_ID,
sqlHelper.KEY_TITLE,
sqlHelper.KEY_AUTHOR,
sqlHelper.KEY_RATING
};
int[] boundTo = new int[] {
R.id.booknum,
R.id.booktitle,
R.id.bookauthor,
R.id.ratingBar
};
simpleCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),
R.layout.each_book,
cursor,
columns,
boundTo,
0);
list.setAdapter(simpleCursorAdapter);
}
catch (Exception ex)
{

}
}

}


Book.java:

public class Book {
private int id;
private String title;
private String author;
private int rating;

public Book() {
}

public Book(String title, String author, int rating) {
super();
this.title = title;
this.author = author;
this.rating = rating;
}

//getters & setters
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}

public String getAuthor() {
return author;
}

public void setAuthor(String author) {
this.author = author;
}

public int getRating() {
return rating;
}

public void setRating(int rating) {
this.rating = rating;
}

@Override
public String toString() {
return "Book [id=" + id + ", title=" + title + ", author=" + author + ", rating=" + rating + "]";
}
}


SQLHelper.java:

import java.util.LinkedList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log;


public class SqlHelper extends SQLiteOpenHelper { // Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "Books1DB"; // Books table name
private static final String TABLE_BOOKS = "books"; // Books Table Columns names
public static final String KEY_ID = "id";
public static final String KEY_TITLE = "title";
public static final String KEY_AUTHOR = "author";
public static final String KEY_RATING = "rating";

public SqlHelper(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
// SQL statement to create book table
String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title TEXT, " +
"author TEXT," + "rating INTEGER )";
// create books table
db.execSQL(CREATE_BOOK_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older books table if existed
db.execSQL("DROP TABLE IF EXISTS books"); // create fresh books table
this.onCreate(db);
}

public Cursor getBooks() {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_BOOKS, new String[] {KEY_ID, KEY_TITLE,
KEY_AUTHOR, KEY_RATING}, null, null, null, null, null);
if(cursor != null)
{
cursor.moveToFirst();
return cursor;
}
else {
return null;
}
}

/*CRUD operations (create "add", read "get", update, delete) */
public void addBook(Book book){
Log.d("addBook", book.toString());
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put(KEY_TITLE, book.getTitle()); // get title
values.put(KEY_AUTHOR, book.getAuthor()); // get author
values.put(KEY_RATING, book.getRating()); // get rating
// 3. insert
db.insert(TABLE_BOOKS, // table
null, //nullColumnHack
values); // key/value -> keys = column names/values
// 4. Close dbase
db.close();
}

// Get All Books
public List<Book> getAllBooks() {
List<Book> books = new LinkedList<Book>();
// 1. build the query
String query = "SELECT * FROM " + TABLE_BOOKS;
// 2. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// 3. go over each row, build book and add it to list
Book book = null;
if (cursor.moveToFirst()) {
do {
book = new Book();
book.setId(Integer.parseInt(cursor.getString(0)));
book.setTitle(cursor.getString(1));
book.setAuthor(cursor.getString(2));
book.setRating(Integer.parseInt(cursor.getString(3)));
// Add book to books
books.add(book);
} while (cursor.moveToNext());
}
Log.d("getAllBooks()", books.toString());
return books; // return books
}

// Updating single book
public int updateBook(Book book) {
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put("title", book.getTitle()); // get title
values.put("author", book.getAuthor()); // get author
// 3. updating row
int i = db.update(TABLE_BOOKS, //table
values, // column/value
KEY_ID + " = ?", // selections
new String[]{String.valueOf(book.getId())}); //selection args
// 4. close dbase
db.close();
Log.d("UpdateBook", book.toString());
return i;
}

// Deleting single book
public void deleteBook(Book book) {
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. delete
db.delete(TABLE_BOOKS, KEY_ID+" = ?",
new String[] { String.valueOf(book.getId()) });
//3. close
db.close();
Log.d("deleteBook", book.toString());
}

public int getIds(Book book) {
String selectQuery = "SELECT id FROM books";
SQLiteDatabase database = this.getReadableDatabase();
Cursor c = database.rawQuery(selectQuery, null);
c.moveToFirst();
int total = c.getCount();
String stringTotal = String.valueOf(total);
Log.d("Total Count:", stringTotal);
return total;
}

}


activity_main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content"
tools:context=".MainActivity">

<ListView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/listView"
android:layout_gravity="center_horizontal" />

</LinearLayout>


each_book.java:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent">


<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="Medium Text"
android:id="@+id/booknum"
android:layout_alignParentTop="true"
android:layout_alignParentStart="true" />

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceLarge"
android:text="Large Text"
android:id="@+id/booktitle"
android:layout_gravity="center_horizontal"
android:layout_below="@+id/booknum"
android:layout_centerHorizontal="true" />

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="Medium Text"
android:id="@+id/bookauthor"
android:layout_gravity="center_horizontal"
android:layout_below="@+id/booktitle"
android:layout_toEndOf="@+id/booktitle" />

<RatingBar
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/ratingBar"
android:layout_below="@+id/bookauthor"
android:layout_alignParentStart="true" />

</RelativeLayout>


AndroidManifest.xml:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.shaydoe.bookreviews">

<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />

<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>

Answer

A CursorAdpater requires a column named _id. As per The Cursor must include a column named "_id" or this class will not work. CursorAdapter.

Therefore you need to make it so that the cursor has a column named _id. You could do this in a few ways. e.g. name(rename it) in the query used to create the cursor using AS _id or rename the actual column. I'd suggest the latter in which case change:

public static final String KEY_ID = "id";

to

public static final String KEY_ID = "_id";