Display data from SQLite database into ListView in Android

Question

Although this question has been asked many times on here, I just can't find the proper answer to fit my code. I realise it may be something small, but I just can't seem to find the problem as I'm only really new to this.

Here's my code getClientNames in DatabaseHelper class:

public Cursor getSitesByClientname(String id) {
    String[] args={id};

Cursor myCursor = db.rawQuery("SELECT client_sitename FROM " + CLIENT_SITES_TABLE + " WHERE client_id=?", args);
        String results = "";
        /*int count = myCursor.getCount();
        String[] results = new String[count + 1];
        int i = 0;*/

         if (myCursor != null) {           
                if(myCursor.getCount() > 0)
                {   
                    for (myCursor.moveToFirst(); !myCursor.isAfterLast(); myCursor.moveToNext())
                    {
                        results = results + myCursor.getString(myCursor.getColumnIndex("client_sitename")); 
                    }
                }
            }
            return results;
}

One problem is that I'm returning a 'String' to the 'Cursor', but I'm not sure what is the best way around this, as I think I should be returning a Cursor.

Here's the ClientSites class where I want to display the data:

public class ClientSites extends Activity {

//public final static String ID_EXTRA="com.example.loginfromlocal._ID";

        private DBUserAdapter dbHelper = null;
    private Cursor ourCursor = null;
    private Adapter adapter=null;


    @SuppressWarnings("deprecation")
    @SuppressLint("NewApi")
    public void onCreate(Bundle savedInstanceState) {
        try
        {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.client_sites);

            Intent i = getIntent();
            String uID = String.valueOf(i.getIntExtra("userID", 0));

            ListView myListView = (ListView)findViewById(R.id.myListView);

            dbHelper = new DBUserAdapter(this);

            //dbHelper.createDatabase();

            dbHelper.openDataBase();

            ourCursor = dbHelper.getSitesByClientname(uID);
            Log.e("ALERT", uID.toString());

            startManagingCursor(ourCursor);
            Log.e("ERROR", "After start manage cursor: ");

            //@SuppressWarnings("deprecation") 
            //SimpleCursorAdapter adapter = new SimpleCursorAdapter(getBaseContext(), R.id.myListView, null, null, null);
            CursorAdapter adapter = new SimpleCursorAdapter(this, R.id.myListView, null, null, null, 0);
            adapter = new Adapter(ourCursor);

            //Toast.makeText(ClientSites.this, "Booo!!!", Toast.LENGTH_LONG).show();

            myListView.setAdapter(adapter);

            myListView.setOnItemClickListener(onListClick);


        }
        catch (Exception e)
        {
            Log.e("ERROR", "XXERROR IN CODE: " + e.toString());
            e.printStackTrace();
        } 

    }

    private AdapterView.OnItemClickListener onListClick=new AdapterView.OnItemClickListener() {
        public void onItemClick(AdapterView<?> parent,
                                View view, int position,
                                long id) 
        {
            Intent i=new Intent(ClientSites.this, InspectionPoints.class);

            i.putExtra(ID_EXTRA, String.valueOf(id));
            startActivity(i);

        }
    };


    class Adapter extends CursorAdapter {
        @SuppressWarnings("deprecation")
        Adapter(Cursor c) {
          super(ClientSites.this, c);
        }

        //@Override
        public void bindView(View row, Context ctxt,
                           Cursor c) {
            Holder holder=(Holder)row.getTag();
            holder.populateFrom(c, dbHelper);
        }
        @Override
        public View newView(Context ctxt, Cursor c,
                           ViewGroup parent) {
          LayoutInflater inflater=getLayoutInflater();
          View row = inflater.inflate(R.layout.row, parent, false);
          Holder holder=new Holder(row);
          row.setTag(holder);
          return(row);
        }
      }

    static class Holder {
        private TextView name=null;

        Holder(View row) {
            name=(TextView)row.findViewById(R.id.ingredientText);
        }

        void populateFrom(Cursor c, DBUserAdapter r) {
            name.setText(r.getName(c));
        }
    }
}

Here is the code I'm now using to try and display the data in the Listview. I have altered it somewhat from my original attempt, but still not sure what I'm doing wrong.

public void onCreate(Bundle savedInstanceState) {
        try
        {
            super.onCreate(savedInstanceState);
            //setContentView(R.layout.client_sites);

            Intent i = getIntent();
            String uID = String.valueOf(i.getIntExtra("userID", 0));
            //int uID = i.getIntExtra("userID", 0);

            //ListView myListView = (ListView)findViewById(R.id.myListView);

            dbHelper = new DBUserAdapter(this);

            dbHelper.createDatabase();

            dbHelper.openDataBase();

            String[] results = dbHelper.getSitesByClientname(uID);

            //setListAdapter(new ArrayAdapter<String>(ClientSites.this, R.id.myListView, results));
            //adapter = new ArrayAdapter<String>(ClientSites.this, R.id.myListView, results);
            setListAdapter(new ArrayAdapter<String>(ClientSites.this, R.layout.client_sites, results));

            //ListView myListView = (ListView)findViewById(R.id.myListView);
            ListView listView = getListView();
            listView.setTextFilterEnabled(true);

            //ourCursor = dbHelper.getSitesByClientname(uID);
            //Log.e("ALERT", uID.toString());

            //startManagingCursor(ourCursor);
            //Log.e("ERROR", "After start manage cursor: ");

            //@SuppressWarnings("deprecation") 
            //SimpleCursorAdapter adapter = new SimpleCursorAdapter(getBaseContext(), R.id.myListView, null, null, null); // LOOK AT THIS IN THE MORNING!!!!!!!!!!! 
            //CursorAdapter adapter = new SimpleCursorAdapter(this, R.id.myListView, null, null, null, 0);
            //adapter = new Adapter(ourCursor);

            //Toast.makeText(ClientSites.this, "Booo!!!", Toast.LENGTH_LONG).show();

            //myListView.setAdapter(adapter);

            //myListView.setOnItemClickListener(onListClick);

Answer 1

I Created a Listview from a database. Here is the code I used for my app

Here is part of the database handler. It will return a List of Categories for my listview. It can return a list of Strings if that is what you need.

public List<Category> getAllCategorys() {
        ArrayList<Category> categoryList = new ArrayList<Category>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CATEGORY;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        try{
            if (cursor.moveToFirst()) {
                do {
                    Category category = new Category();
                    category.setID(Integer.parseInt(cursor.getString(0)));
                    category.setCategory(cursor.getString(1));
                    // Adding category to list
                    categoryList.add(category);
                } while (cursor.moveToNext());
            }   
        }finally{
            cursor.close();
        }   
        db.close();
        // return category list
        return categoryList;

Here is how I fill the ListView by calling the database.

int size = db.getCategoryCount();
List<Category> categoryList = db.getAllCategorys();

category_data = new String[size-1];
int i=0;
for(Category cn : categoryList)
{   
    category_data[i] = cn.getCategory(); // get the name of the category and add it to array
    i++;   
} 

listAdapter = new ArrayAdapter<String>(this, R.layout.categoryrow, category_data);
listViw.setAdapter(listAdapter);

EDIT: Here is something that should work for you

public List<String> getSitesByClientname(String id) {
    String[] args={id};
    ArrayList<String> result = new ArrayList<String>();
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor myCursor = db.rawQuery("SELECT client_sitename FROM " + CLIENT_SITES_TABLE +      " WHERE client_id=?", args);

    try{
       if (myCursor.moveToFirst()){
           do{
              result.add(myCursor.getString(myCusor.getString(myCursor.getColumnIndex("client_sitename"));
           }while(myCursor.moveToNext());
        }
    }finally{
         myCursor.close();
    }
    db.close();
    return result;
}

Use it like this

List<String> sites_data = dbHelper.getSitesByClientname(uID);

result_data = new String[sites_data.size()];
int i=0;
for(String s : sites_data)
{   
    result_data[i] = s; // get the name of the category and add it to array
    i++;   
} 

listAdapter = new ArrayAdapter<String>(this, R.layout.client_sites, result_data);
listViw.setAdapter(listAdapter);

Answer 2

If you want to return the Cursor from dbHelper you can do something like this...

public Cursor getSitesByClientname(String id) {
    String[] args={id};

return db.rawQuery("SELECT client_sitename FROM " + CLIENT_SITES_TABLE + " WHERE client_id=?", args);
}

I'd also take some time to read the listview tutorial