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.

Answer 1

package com.example.app3

import android.content.ContentValues

import android.content.Context

import android.database.Cursor

import android.database.sqlite.SQLiteDatabase

import android.database.sqlite.SQLiteOpenHelper

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = ("CREATE TABLE $TABLE_NAME " +
                "($COL_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COL_NAME TEXT, " +
                "$COL_STUDENT_ID TEXT, " +
                "$COL_SEMESTER INTEGER, " +
                "$COL_BRANCH TEXT, " +
                "$COL_FACULTY TEXT)")
        db.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }

    fun addStudentDetails(name: String, studentId: String, semester: Int, branch: String, faculty: String): Boolean {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(COL_NAME, name)
        contentValues.put(COL_STUDENT_ID, studentId)
        contentValues.put(COL_SEMESTER, semester)
        contentValues.put(COL_BRANCH, branch)
        contentValues.put(COL_FACULTY, faculty)
        val result = db.insert(TABLE_NAME, null, contentValues)
        return result != -1L
    }

    fun getAllStudents(): ArrayList<String> {
        val studentList = ArrayList<String>()
        val db = this.readableDatabase
        val query = "SELECT * FROM $TABLE_NAME"
        val cursor = db.rawQuery(query, null)
        if (cursor.moveToFirst()) {
            do {
                val studentId = cursor.getString(cursor.getColumnIndex(COL_STUDENT_ID))
                val name = cursor.getString(cursor.getColumnIndex(COL_NAME))
                studentList.add("$studentId - $name ")
            } while (cursor.moveToNext())
        }
        cursor.close()
        return studentList
    }

    fun getStudentDetails(studentId: String): String {
        val db = this.readableDatabase
        val query = "SELECT * FROM $TABLE_NAME WHERE $COL_STUDENT_ID = ?"
        val cursor = db.rawQuery(query, arrayOf(studentId))
        var details = ""
        if (cursor.moveToFirst()) {
            val name = cursor.getString(cursor.getColumnIndex(COL_NAME))
            val semester = cursor.getInt(cursor.getColumnIndex(COL_SEMESTER))
            val branch = cursor.getString(cursor.getColumnIndex(COL_BRANCH))
            val faculty = cursor.getString(cursor.getColumnIndex(COL_FACULTY))
            details = "Name: $name\nStudent ID: $studentId\nSemester: $semester\nBranch: $branch\nFaculty: $faculty"
        }
        cursor.close()
        return details
    }

    fun deleteStudent(studentId: String) {
        val db = this.writableDatabase
        db.delete(TABLE_NAME, "$COL_STUDENT_ID = ?", arrayOf(studentId))
        db.close()
    }

    fun UpdateStudent(studentId: String, name: String, semester: Int, branch: String, faculty: String){
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put(COL_NAME,name)
        contentValues.put(COL_SEMESTER,semester)
        contentValues.put(COL_BRANCH,branch)
        contentValues.put(COL_FACULTY,faculty)
        db.update(TABLE_NAME,contentValues, "$COL_STUDENT_ID = ?", arrayOf(studentId))
    }

    companion object {
        private const val DATABASE_NAME = "StudentDetails.db"
        private const val DATABASE_VERSION = 1
        private const val TABLE_NAME = "students"
        private const val COL_ID = "id"
        private const val COL_NAME = "name"
        private const val COL_STUDENT_ID = "student_id"
        private const val COL_SEMESTER = "semester"
        private const val COL_BRANCH = "branch"
        private const val COL_FACULTY = "faculty"
    }
}

Answer 2

package com.example.app3

import android.os.Bundle
import android.widget.ArrayAdapter
import android.widget.ListView
import androidx.appcompat.app.AlertDialog
import androidx.appcompat.app.AppCompatActivity

class ViewStudent : AppCompatActivity() {
    
private lateinit var listView: ListView
    
private lateinit var dbHelper: DatabaseHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.view_student)

        listView = findViewById(R.id.listView)
        dbHelper = DatabaseHelper(this)

        displayStudents()
    }

    private fun displayStudents(){
        val studentList = dbHelper.getAllStudents()
        val adapter = ArrayAdapter(this, android.R.layout.simple_list_item_1,studentList)
        listView.adapter = adapter
         listView.setOnItemClickListener{_,_, position ,_ ->
             val selectedStudent = adapter.getItem(position)
             val studentID = selectedStudent?.substringBefore("-")?.trim()
             val studentDetails = dbHelper.getStudentDetails(studentID?:"")
             showStudentDetailsDialog(studentDetails)
         }
    }

    private fun showStudentDetailsDialog(details:String){
        val builder = AlertDialog.Builder(this)
        builder.setTitle("Student Details")
        builder.setMessage(details)
        builder.setPositiveButton("OK") { dialog, _ ->
            dialog.dismiss()
        }
        val dialog = builder.create()
        dialog.show()
    }
}

Answer 3

package com.example.app3

import android.os.Bundle

import android.widget.ArrayAdapter

import android.widget.Button

import android.widget.EditText

import android.widget.ListView

import android.widget.Toast

import androidx.appcompat.app.AppCompatActivity

class UpdateStudent : AppCompatActivity() { private lateinit var dbHelper: DatabaseHelper private lateinit var listView: ListView private lateinit var adapter: ArrayAdapter private lateinit var studentList: ArrayList private lateinit var update :Button private lateinit var upnameStu : EditText private lateinit var upidStu : EditText private lateinit var upsemStu : EditText private lateinit var upbranchStu : EditText private lateinit var upfacultyStu : EditText

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.update_student)

    dbHelper = DatabaseHelper(this)
    update = findViewById(R.id.updateStu)
    upidStu = findViewById(R.id.update_id)
    upnameStu = findViewById(R.id.update_name)
    upsemStu = findViewById(R.id.update_sem)
    upbranchStu = findViewById(R.id.update_branch)
    upfacultyStu = findViewById(R.id.update_faculty)

    update.setOnClickListener(){
        updateStudent()
    }
}

private fun updateStudent() {
    val name = upnameStu.text.toString()
    val id = upidStu.text.toString()
    val sem = upsemStu.text.toString().toInt()
    val branch = upbranchStu.text.toString()
    val faculty = upfacultyStu.text.toString()
    if (id.isNotEmpty()) {
        dbHelper.UpdateStudent(id, name, sem, branch, faculty)
        Toast.makeText(this, "Student updated successfully", Toast.LENGTH_SHORT).show()
        // Clear EditText fields after successful addition
        upnameStu.text.clear()
        upidStu.text.clear()
        upsemStu.text.clear()
        upbranchStu.text.clear()
        upfacultyStu.text.clear()
    } else {
        Toast.makeText(this, "Please fill all fields correctly", Toast.LENGTH_SHORT).show()
    }
}

}

package com.example.app3

import android.os.Bundle

import android.widget.ArrayAdapter

import android.widget.Button

import android.widget.EditText

import android.widget.ListView

import android.widget.Toast

import androidx.appcompat.app.AppCompatActivity

class DeleteStudent : AppCompatActivity() { private lateinit var dbHelper: DatabaseHelper private lateinit var listView: ListView private lateinit var adapter: ArrayAdapter private lateinit var studentList: ArrayList private lateinit var delStu: EditText private lateinit var deleteBtn: Button

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.delete_student)

    dbHelper = DatabaseHelper(this)
    delStu = findViewById(R.id.del_id)
    deleteBtn = findViewById(R.id.deleteStu)

    deleteBtn.setOnClickListener() {
        deleteStudent()
    }
}
private fun deleteStudent() {
    val delid = delStu.text.toString()
    dbHelper.deleteStudent(delid)
    if (delid.isNotEmpty()) {
        Toast.makeText(this, "Student Deleted Successfully", Toast.LENGTH_SHORT).show()
        delStu.text.clear()
    } else {
        Toast.makeText(this, "Failed to delete student", Toast.LENGTH_SHORT).show()
    }
}

}

package com.example.app3

import android.content.Intent

import androidx.appcompat.app.AppCompatActivity

import android.os.Bundle

import android.view.Menu

import android.view.MenuItem

import android.widget.Toast

import kotlin.system.exitProcess

class MainActivity : AppCompatActivity() { private lateinit var dbHelper: DatabaseHelper override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_main) dbHelper = DatabaseHelper(this) }

override fun onCreateOptionsMenu(menu: Menu?): Boolean {
    menuInflater.inflate(R.menu.menu,menu)
    return super.onCreateOptionsMenu(menu)
}

override fun onOptionsItemSelected(item: MenuItem): Boolean {
    when (item.itemId){
        R.id.add_stu -> {
            Toast.makeText(this,"Add Student Selected",Toast.LENGTH_LONG).show()
            val intent  = Intent(this, AddStudent::class.java)
            startActivity(intent)
        }
        R.id.update_stu -> {
            Toast.makeText(this,"Update Student Selected",Toast.LENGTH_LONG).show()
            val intent  = Intent(this, UpdateStudent::class.java)
            startActivity(intent)
        }
        R.id.del_stu -> {
            Toast.makeText(this,"Delete Student Selected",Toast.LENGTH_LONG).show()
            val intent  = Intent(this, DeleteStudent::class.java)
            startActivity(intent)
        }
        R.id.view_stu -> {
            Toast.makeText(this,"View Students Selected",Toast.LENGTH_LONG).show()
            val intent  = Intent(this, ViewStudent::class.java)
            startActivity(intent)
        }
        R.id.exit_stu -> {
            Toast.makeText(this,"Exit Selected",Toast.LENGTH_LONG).show()
            //val intent  = Intent(this, ExitStudent::class.java)
            //startActivity(intent)
            this@MainActivity.finish()
            exitProcess(0)
        }
    }
    return super.onOptionsItemSelected(item)
}

}

package com.example.app3

import android.os.Bundle

import android.widget.ArrayAdapter

import android.widget.Button

import android.widget.EditText

import android.widget.ListView

import android.widget.Toast

import androidx.appcompat.app.AppCompatActivity

class AddStudent : AppCompatActivity() { private lateinit var dbHelper: DatabaseHelper private lateinit var listView: ListView private lateinit var adapter: ArrayAdapter private lateinit var studentList: ArrayList private lateinit var submit :Button private lateinit var nameStu : EditText private lateinit var idStu : EditText private lateinit var semStu : EditText private lateinit var branchStu : EditText private lateinit var facultyStu : EditText

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.add_student)

    dbHelper = DatabaseHelper(this)
    submit = findViewById(R.id.submitStu)
    nameStu = findViewById(R.id.add_name)
    idStu = findViewById(R.id.add_id)
    semStu = findViewById(R.id.add_sem)
    branchStu = findViewById(R.id.add_branch)
    facultyStu = findViewById(R.id.add_faculty)

    submit.setOnClickListener(){
        addStudent()
    }
}

private fun addStudent(){
    val name = nameStu.text.toString()
    val id = idStu.text.toString()
    val sem = semStu.text.toString().toInt()
    val branch = branchStu.text.toString()
    val faculty = facultyStu.text.toString()
    if(name.isNotEmpty()){
        val success = dbHelper.addStudentDetails(name,id,sem,branch,faculty)
        if (success) {
            Toast.makeText(this, "Student added successfully", Toast.LENGTH_SHORT).show()
            // Clear EditText fields after successful addition
            nameStu.text.clear()
            idStu.text.clear()
            semStu.text.clear()
            branchStu.text.clear()
            facultyStu.text.clear()
        } else {
            Toast.makeText(this, "Failed to add student", Toast.LENGTH_SHORT).show()
        }
    } else {
        Toast.makeText(this, "Please fill all fields correctly", Toast.LENGTH_SHORT).show()
    }
    }
}

Answer 4

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);
}