Displaying ALL data from sqlite database into listview in tabbed activity

Question

As a newcomer to android development I've been stuck on this issue for a few weeks now and it's getting pretty tiring.

After looking at every tutorial and reading every question and answer I could find, still I can't figure out how to get Android Studio to just take what's in my SQLite database and paste its contents into a listview. I would've thought there would be a android:displayallfrom("myDB") command of some kind in the XML files to just display everything there is in a database but it appears to be much more complicated.

Basically, what I want to do is display ALL data from my database (Dogs.db) into my listview (list_dogs) in the first tab of my tab view (Tab1).

Here is my code:

Tab1.java

package com.example.major.awoo;

import android.os.Bundle;
import android.support.v4.app.Fragment;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

public class Tab1 extends Fragment {

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        View rootView = inflater.inflate(R.layout.tab1, container, false);
        return rootView;
    }
}

tab1.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.major.awoo.MainActivity">

    <ListView
        android:id="@+id/list_dogs"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_alignParentBottom="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:listSelector=""/>

</RelativeLayout>

DatabaseHelper.java

package com.example.major.awoo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "Dogs.db";
    public static final String TABLE_NAME = "dogs_table";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "NAME";
    public static final String COL_3 = "AGE";
    public static final String COL_4 = "WEIGHT";
    public static final String COL_5 = "BREED";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
            db.execSQL("create table " + TABLE_NAME +" (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,SURNAME TEXT,MARKS INTEGER)");
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        onCreate(db);
    }

    public boolean insertData(String name,String age,String weight,String breed) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,age);
        contentValues.put(COL_4,weight);
        contentValues.put(COL_5,breed);
        long result = db.insert(TABLE_NAME, null, contentValues);
        if(result == -1)
            return false;
        else
            return true;
    }

    public Cursor getAllData() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("select * from "+TABLE_NAME,null);
        return res;
    }

    public boolean updateData(String id,String name,String age,String weight,String breed) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_1,id);
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,age);
        contentValues.put(COL_4,weight);
        contentValues.put(COL_5,breed);
        db.update(TABLE_NAME, contentValues, "ID = ?",new String[] { id });
        return true;
    }

    public Integer deleteData (String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ?",new String[] {id});

    }

    public Cursor getListContents(){
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME,null);
        return data;
}

//method to display data

    public Cursor displayData;
    {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery(" SELECT  * FROM " + TABLE_NAME, null);
        return res;    
    }   

}

I'm sure there is something really dumb I'm missing but any help would be appreciated.

Solution

If you want to display each and every information of each dog in a ListView, you need to make a Dog class. Although it is not necessary but it would make your job easier and it would make more sense to get data from database and store the information of each dog in a Dog class instance.

public class Dog {
    private String id;
    private String name;
    private String age;
    private String breed;
    private String weight;

    public Dog(String id, String name, String age, String breed, String weight) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.breed = breed;
        this.weight = weight;
    }

    public String getID() {
        return this.id;
    }

    public String getName() {
        return this.name;
    }

    public String getAge() {
        return this.age;
    }

    public String getWeight() {
        return this.weight;
    }

    public String getBreed() {
        return this.breed;
    }
}

Then you need to define a layout XML file that will represent the each row of your ListView. You are able to design it any way you want.

Here's an example row layout code

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout 
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    xmlns:app="http://schemas.android.com/apk/res-auto">

    <TextView
        android:id="@+id/text_dogID"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="demo"
        android:textColor="#000"
        android:textSize="25sp"/>

    <TextView
        android:id="@+id/text_dogName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="demo"
        android:textColor="#000"
        android:textSize="25sp"
        app:layout_constraintTop_toBottomOf="@id/text_dogID"/>

    <TextView
        android:id="@+id/text_dogAge"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="demo"
        android:textColor="#000"
        android:textSize="25sp"
        app:layout_constraintTop_toBottomOf="@id/text_dogName"/>

    <TextView
        android:id="@+id/text_dogWeight"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="demo"
        android:textColor="#000"
        android:textSize="25sp"
        app:layout_constraintTop_toBottomOf="@id/text_dogAge"/>

    <TextView
        android:id="@+id/text_dogBreed"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="demo"
        android:textColor="#000"
        android:textSize="25sp"
        app:layout_constraintTop_toBottomOf="@id/text_dogWeight"/>

</android.support.constraint.ConstraintLayout>

After this, you need your own custom adapter class that extends the BaseAdapter class and Override the getView method.

Here's an example custom adapter class

public class CustomAdapter extends BaseAdapter{

    private ArrayList<Dog> dogsList;
    private Context context;

    public CustomAdapter(ArrayList<Dog> list, Context cont){
        this.dogsList = list;
        this.context = cont;
    }

    @Override
    public int getCount() {
        return this.dogsList.size();
    }

    @Override
    public Object getItem(int position) {
        return this.dogsList.get(position);
    }

    @Override
    public long getItemId(int i) {
        return i;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        ViewHolder holder = null;

        if(convertView == null){
            LayoutInflater inf = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            convertView = inf.inflate(R.layout.listview_row_layout, null);

            holder = new ViewHolder();
            holder.id = (TextView)convertView.findViewById(R.id.text_dogID);
            holder.name = (TextView)convertView.findViewById(R.id.text_dogName);
            holder.age = (TextView)convertView.findViewById(R.id.text_dogAge);
            holder.weight = (TextView)convertView.findViewById(R.id.text_dogWeight);
            holder.breed = (TextView)convertView.findViewById(R.id.text_dogBreed);

            convertView.setTag(holder);
        }
        else {
            holder = (ViewHolder)convertView.getTag();
        }

        Dog stu = dogsList.get(position);
        holder.id.setText(stu.getID());
        holder.name.setText(stu.getName());
        holder.age.setText(stu.getAge());
        holder.weight.setText(stu.getWeight());
        holder.breed.setText(stu.getBreed());

        return convertView;
    }

    private static class ViewHolder{
        public TextView id;
        public TextView name;
        public TextView age;
        public TextView weight;
        public TextView breed;
    }
}

Now you need to get data from database and store the information of each dog in a separate Dog class instance that you created earlier.

public ArrayList<Dog> getAllData() {
        ArrayList<Dog> doglist = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("select * from "+TABLE_NAME,null);

        while(res.moveToNext()) {
            String id = res.getString(0);   //0 is the number of id column in your database table
            String name = res.getString(1);
            String age = res.getString(2);
            String breed = res.getString(3);
            String weight = res.getString(4);

            Dog newDog = new Dog(id, name, age, breed, weight);
            doglist.add(newDog);
        }
        return doglist;
 }

Now all your data from the database is stored in the instances of Dog class that are stored in doglist ArrayList.

Finally you need a method that fills your ListView

public void fillListview() {
     ListView myListview = findViewById(R.id.myListview);
     DatabaseHelper dbhelper = new DatabaseHelper(this);

     ArrayList<Dog> dogList = dbhelper.getAllData();

     Customadapter myAdapter = new Customadapter(dogList, this);
     myListview.setAdapter(myAdapter);
}

Comments

[Yousaf]

do you want to display all of the data for each dog in a listview or just the names of the dogs ?

[major]

All the data. I don't even care about presentation anymore I just want something to appear

[elmorabea]

where is code for adapter that you use to display dogs,

[Toris]

1. In DatabaseHelper columns doesn't match. onCreate() creates (ID, NAME, SURNAME, MARKS) but insertData() and updateData() tries to insert / update (ID, name, age, weight, breed). 2. Typo exists around public Cursor displayData; Even if it is public Cursor displayData(), getListContents() and displayData() look same.

[major]

oh right thank you toris, that's because I've been relying on tutorials in making this app so far and I guess I didn't edit that bit

[major]

Hi Yousaf, thanks for the help. Everything seemed to be going well up to the customadapter imgur.com/a/O5lsF What am i missing here?

[major]

Nevermind, just fixed most of it with importing the classes (that was obvious sorry) but now I'm left with just these: imgur.com/a/WUzLd

[Toris]

@major Need casts to TextView, I think.

[Yousaf]

@major as Toris said, you probably haven't updated your android studio to latest version, so you need to add (TextView) after the = sign and holder.id.setImageResource is a typo, change it to holder.id.setText. See the updated code of Customadapter class in my answer

[major]

@Yousaf thanks again for all your help, just one bit of red left, which is in Dog.java: imgur.com/a/fUVA0 I believe it's meant to be referring to a method?

[major]

@Yousaf 'cannot find symbol method'

[Yousaf]

@major which class contains the getAllData method ? It should be inside DatabaseHelper class and make sure that this in this.getWritableDatabase is referring to DatabaseHelper class. Try changing this.getWritableDatabase to DatabaseHelper.this.getWritableDatabase();

[major]

@Yousaf I never got anything to work because new errors kept appearing. nothing made sense and parts of code that used to work now wouldnt and it kept the app from running. this is worth 20% of my A level grade but I'm honestly done with code forever, it's clearly not my thing if i cant get a file to display text. cheers for your all help though.