Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
i have a database in which some cases are NULL, if i want to use a column data as header(in expendable listview) and if the column has null values, i got some empty headers. how to chose specific rows to be displayed as header . thanks

this my Database.java
package ma.ac.iav.learn;

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

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class Database {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "URTc.db";
    private static final String DATABASE_TABLE = "OrganAnatomy";
    public static final String DATABASE_ID = "_id";
    public static final String DATABASE_GROUP_1 = "Larynx_features";
    public static final String DATABASE_CHILD_1 = "Larynx";
    private final Context mContext;
    private DatabaseHelper mDatabaseHelper;
    private SQLiteDatabase mDB;

    public Database(Context context) {
        mContext = context;
    }

    public void open() {
        mDatabaseHelper = new DatabaseHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
        mDB = mDatabaseHelper.getWritableDatabase();
    }


    public void close() {
        if (mDatabaseHelper != null) mDatabaseHelper.close();
    }

    public Cursor getDatabase() {
            return mDB.query(DATABASE_TABLE, null, null, null, null, null, DATABASE_GROUP_1);
    }


    public Cursor getID(long rowID) {
        return mDB.query(DATABASE_TABLE, null, "_id" + " = "
                + rowID , null, null, null, null);

    }

    public class DatabaseHelper extends SQLiteAssetHelper {

        public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);

        }
    }


}


and this is my Mainactivity:
package ma.ac.iav.learn;

import android.content.Context;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ExpandableListView;
import android.widget.SimpleCursorTreeAdapter;

public class MainActivity extends AppCompatActivity {
    ExpandableListView expandableListView;
    Database mDatabase;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
             /*
        "Called when the database has been opened."
        https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onOpen(android.database.sqlite.SQLiteDatabase)
         */
        mDatabase = new Database(this);
        mDatabase.open();

        /*
        "This interface provides random read-write access to the result set returned by a database query."
        https://developer.android.com/reference/android/database/Cursor.html
         */

        Cursor cursor = mDatabase.getDatabase();
        startManagingCursor(cursor);

        /*
        "A list of column names that will be used to display the data for a group."
        "The group views (from the group layouts) that should display column in the "from" parameter."
        "A list of column names that will be used to display the data for a child."
        "The resource identifier of a layout file that defines the views for a child."
        https://developer.android.com/reference/android/widget/SimpleCursorTreeAdapter.html#SimpleCursorTreeAdapter(android.content.Context, android.database.Cursor, int, java.lang.String[], int[], int, java.lang.String[], int[])
         */

        String[] childFrom = new String[]{Database.DATABASE_CHILD_1};
        String[] groupFrom = new String[]{Database.DATABASE_GROUP_1};

        int[] groupTo = {R.id.group1};
        int[] childTo = {R.id.child1};

        /*
        "An easy adapter to map columns from a cursor to TextViews or ImageViews defined in an XML file."
        https://developer.android.com/reference/android/widget/SimpleCursorTreeAdapter.html
        */
        SimpleCursorTreeAdapter simplecursortreeAdapter = new ExpandableListViewAdapter(
                this,
                cursor,
                R.layout.list_group,
                groupFrom,
                groupTo,
                R.layout.list_child,
                childFrom,
                childTo
        );

        /*
        "Finds a view that was identified by the android:id XML attribute that was processed in onCreate(Bundle)."
        "Sets the adapter that provides data to this view."
        https://developer.android.com/reference/android/app/Activity.html#findViewById(int)
        https://developer.android.com/reference/android/widget/ExpandableListView.html#setAdapter(android.widget.ExpandableListAdapter)
         */
        expandableListView = findViewById(R.id.expandableListview);
        expandableListView.setAdapter(simplecursortreeAdapter);

    }

    /*
    "Closes the Cursor, releasing all of its resources and making it completely invalid."
    https://developer.android.com/reference/android/database/Cursor.html#close()
     */
    protected void onDestroy() {
        super.onDestroy();
        mDatabase.close();
    }

    private class ExpandableListViewAdapter extends SimpleCursorTreeAdapter {
        private ExpandableListViewAdapter(
                Context context,
                Cursor cursor,
                int groupLayout,
                String[] groupFrom,
                int[] groupTo,
                int childLayout,
                String[] childFrom,
                int[] childTo) {
            super(context, cursor, groupLayout, groupFrom, groupTo, childLayout, childFrom, childTo);
        }

        /*
        "Gets the Cursor for the children at the given group."
        https://developer.android.com/reference/android/widget/CursorTreeAdapter.html#getChildrenCursor(android.database.Cursor)
         */
        protected Cursor getChildrenCursor(Cursor groupCursor) {
            return mDatabase.getID(groupCursor.getInt(groupCursor.getColumnIndex(Database.DATABASE_ID)));
        }

    }
}


What I have tried:

i have searched on internet but couldn't find a clear answer
Posted
Updated 21-Aug-19 10:37am
Comments
Richard MacCutchan 30-Jul-18 7:45am    
You need to read rows until you find a non-null column. Although this does not seem like a very good design.
Member 13930815 30-Jul-18 8:45am    
what do you mean by read rows, explain more with code please
Richard MacCutchan 30-Jul-18 9:11am    
Well since I do not really understand what you are trying to do I can't give any more detail. Why are you not just using the column names as headers?
Member 13930815 30-Jul-18 10:15am    
This is my database structure: https://imgur.com/6eyDx5c . as you can see some columns have empty cases , and that generate either some empty childs( if i used the columns as headers) or some empty header(if i used the content of column as header) as you can see here https://imgur.com/VNlFtoP (venous drainage have empty child , and i don't want it to appear at all)
Member 13930815 30-Jul-18 10:18am    
i guess the problem is i should use a statement if (X != NULL) or something but can't figure it how and if it's the right thing to do! please help

1 solution

You can use the SQLite coalesce function to convert a null into a value.

For example using a query such as :-

SQL
SELECT
  Organ_features,
	coalesce(Pharynx,'no Pharynx header as null') AS Pharynx, /* replaces null with specified value */
	coalesce(larynx,'no Larynx header as null') AS Larynx /* replaces null with specified value */
FROM OrganAnatomy;


The above would result in :-

Organ_features      Pharynx                   Larynx
 Overview            BLAAAA                      BLALLAL
 Blood Supply        NOOOOO                      SSSSSS
 Venous drain        no Pharynx header as null   SADADJ
 Nerves              sssAAAA                     no Larynx header as null


An alternative approach along the lines of how to use IF THEN ELSE clauses is to use CASE WHEN THEN ELSE END clauses. A similar result to the above could be achieved using CASEs could be :-

SQL
SELECT Organ_features,
	CASE WHEN Pharynx IS NULL THEN 'No HDR for Pharynx' ELSE Pharynx END AS Pharynx,
	CASE WHEN Larynx IS NOT NULL THEN Larynx ELSE 'No HDR for Larynx' END AS Larynx
FROM OrganAnatomy;
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900