How to show query result raws as a table in an activity?

Question

I am querying some raws from the database in Android studio. When I have the result table as a cursor, I want to show that cursor in an activity where columns are written at the top and rows are seen one under the other like a normal table. So, this activity's column names will change according to the result query's columns. Any idea how to implement this, or is there a template I can use? I am new to Android so it might be an easy question for some of you, sorry for that.

Answer 1

So I recently have as well the same question. Then I found a really good tutorial with custom list items.

First, make sure that you save your records from the database into an object.

So first you have to create a row view. So create a simple XML file insert for example the following code and save it as myobject_list_item in your res/layout folder.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/item"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:padding="5dp">

    <TextView
        android:id="@+id/column1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_toStartOf="@+id/column2"
        android:padding="10dp"
        android:paddingLeft="5dp"
        android:text="Column1"
        android:textAppearance="@android:style/TextAppearance.Material.Large"
        android:textSize="20sp" />

    <TextView
        android:id="@+id/column2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/gewichtung"
        android:layout_alignParentEnd="true"
        android:layout_alignParentTop="true"
        android:padding="10dp"
        android:text="Column2"
        android:textAppearance="@android:style/TextAppearance.Material.Large"
        android:textSize="20sp" />

</RelativeLayout>

After that you have to create a custom list adapter. So create a new Java file with the name MyObject_ListAdapter and insert the following code:

package net.example.app;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.RelativeLayout;
import android.widget.TextView;

import net.example.app.R;

import java.util.ArrayList;

public class MyObject_ListAdapter extends ArrayAdapter<MyObject> {

    // Source:
    // http://hmkcode.com/android-custom-listview-items-row/

    private ArrayList<MyObject> objects;
    private Context context;

    public MyObject_ListAdapter(Context context, ArrayList<MyObject> objects) {
        super(context, R.layout.myobject_list_item, objects);
        this.objects = objects;
        this.context = context;
    }

    public View getView(int position, View convertView, ViewGroup parent) {
        // 1. Create inflater
        LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

        // 2. Get rowView from inflater
        View rowView = inflater.inflate(R.layout.fach_list_item, parent, false);

        // 3. Get the two text view from the rowView
        TextView column1 = (TextView) rowView.findViewById(R.id.column1);
        TextView column2 = (TextView) rowView.findViewById(R.id.column2);
        RelativeLayout item = (RelativeLayout) rowView.findViewById(R.id.item);

        // 4. Set the text for textView
        column1.setText(objects.get(position).getName());
        column2.setText(objects.get(position).getSecondName());


        // 5. return rowView
        return rowView;
    }
}

Add in your activity now an simple ListView and add for this an id like lv. Then in your Java Activity you can insert the following code:

package net.example.app;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import net.example.app.MyObject;
import net.example.app.MyObject_ListAdapter;

import java.util.ArrayList;

public class MyActivity extends AppCompatActivity {

    private ArrayAdapter arrayAdapter;
    private ArrayList<MyObject> myObjects = new ArrayList<>();

    private SQLiteDatabase db;

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

        db = openOrCreateDatabase("database.db", MODE_PRIVATE, null);

        ListView lv = (ListView) findViewById(R.id.lv);
        arrayAdapter = new MyObject_ListAdapter(this, myObjects); //Define the custom list adapter with the activity and arraylist
        lv.setAdapter(arrayAdapter); //Connect your listview with the adapter

        displayData();
    }

    private void displayData() {
        Cursor c = db.rawQuery("SELECT * FROM my_table", null);
        while (c.moveToNext()) { //Loop through all the records
            //Now on the variable 'c' there is one record.

            int column_a_name = c.getColumnIndex("my_column1"); //Get the index of the column from your table.
            String column_a_value = c.getString(column_a_name); //Get the value from the column from the current record.

            int column_b_name = c.getColumnIndex("my_column2");
            String column_b_value = c.getString(column_b_name);

            //Now you can do with the value what you want.
            myObjects.add(new MyObject(column_a_value, column_b_value));

        }

        arrayAdapter.notifyDataSetChanged(); //Notify, that you have changed some data in the array list.
    }
}

I hope this tutorial may help you.

Answer 2

Android has different layouts that could be of use for what you state.

You can use TableLayout of GridLayout. At this post you can find some discussion about which one choose: Grid Layout Vs. Table Layout

From Android docs, you can check some examples like: https://developer.android.com/guide/topics/ui/layout/grid.html and https://developer.android.com/guide/topics/ui/layout/gridview.html

Some additional recommendation:

1. Use AsyncTask for interact with the database, don't do that from the UI thread.
2. I choose callback methods so, when the data access is completed, you inform the calling activity who is in charge of showing the data on the screen (using the layout of your choice).
3. Do not create big long classes doing everything. use one class for the activity, another for the adapter, another for the async task.

Hope this helps you.

Answer 3

When you get a Cursor from a SQL execution, then you can use the following script:

Cursor c = db.rawQuery("SELECT * FROM my_table", null);
while (c.moveToNext()) { //Loop through all the records
    //Now on the variable 'c' there is one record.

    int column_a_name = c.getColumnIndex("my_column"); //Get the index of the column from your table.
    String column_a_value = c.getString(column_a_name); //Get the value from the column from the current record.

    //Now you can do with the value what you want.
    System.out.println(column_a_value);

}

I hope this might be helpful for you.