How can I populate a custom ListView from two tables in a Database?

Question

I need to populate a custom ListView with two tables in one Database.

Why two tables?

I have some data in both tables, and I can insert new items to them onStart aplication.first table has some animals names and second table has some plants name.

FirstTable

Animals:

cat

dog

pig

SecondTable

Plants:

rose

orchid

clover

I need to show items of both table in same custom ListView.

The living world ListView cat

dog

pig

rose

orchid

clover

and if i added one item to any of two tables,it should display on the custom ListView

cat

dog

pig

rabbit

rose

orchid

clover

I tried. but It is not working:

CODE:

MainActivity.java

public class MainActivity extends ListActivity  {

    public sql myDbHelper, myDbHelper2;
    public SQLiteDatabase db = null, db2=null;
    public List<String> show, show2, show3, show4;
    AdaptadorTitulares adapter;
    public ListView list;

    public void onCreate(Bundle icicle) {
        super.onCreate(icicle);

        // if extending Activity
        setContentView(R.layout.activity_main);

        try {
            myDbHelper = new sql(this);
            myDbHelper.createDataBase(this);

        } catch (Exception e) {
        }
        new threadbd().execute();

        list = (ListView) findViewById(android.R.id.list);
        list.setOnItemClickListener(new OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int posicion, long arg3) {

            //........
        }
        });
             }

           //..............
           //NOT IMPORTANT CODE
           //..............

private class threadbd extends AsyncTask<Void, Void, Void> {

        @Override
        protected Void doInBackground(Void... params) {

            myDbHelper.openDataBase();
            myDbHelper2.openDataBase();

            db = myDbHelper.getReadableDatabase();
            db2 = myDbHelper2.getReadableDatabase();

            Cursor c = db.rawQuery("SELECT id FROM animal order by id desc",
                    null);
            c.moveToFirst();
            int num = c.getInt(0);

            Cursor e = db2.rawQuery("SELECT id FROM plants order by id desc",
                    null);
            e.moveToFirst();
            int num2 = e.getInt(0);

            // creo array

            show = new ArrayList<String>(num);
            show2 = new ArrayList<String>(num);
            show3 = new ArrayList<String>(num2);
            show4 = new ArrayList<String>(num2);


            for (int i = 0; i <= num; i++) {

                Cursor d = db.rawQuery(
                        "SELECT name, group FROM animal WHERE id=" + i + "",
                        null);

                if (d.moveToFirst()) {

                    show.add(d.getString(0));
                    show2.add(String.valueOf(d.getDouble(1)));

                }

            for (int j = 0; j <= num2; j++) {

                    Cursor f = db2.rawQuery(
                            "SELECT name, group FROM comida WHERE id=" + j + "",
                            null);

                    if (f.moveToFirst()) {

                        show3.add(f.getString(0));
                        show4.add(String.valueOf(f.getDouble(1)));

                    }

            }// end for

            }
            return null;
        }// end InBackground

        protected void onPostExecute(Void result) {

            Titular titular, titular2;
            ArrayList<Titular> mList = new ArrayList<Titular>();

            for (int i = 0; i < show.size(); i++) {
                try {
                    titular = new Titular(show.get(i), show2.get(i));
                    titular2 = new Titular(show.get(i), show.get(i));
                    mList.add(titular);
                    mList.add(titular2);
                } catch (Exception e) {
                }

                AdaptadorTitulares actualizaAdapter=new AdaptadorTitulares(MainActivity.this, mList);
                list.setAdapter(actualizaAdapter);
            }

            }
        }
    }

Titular.java

public class Titular {

    private String group;
    private String title;
    public Titular(String group, String title) {
        this.group = group;
        this.title = title;
    }

    public String getGroup() {
        return group;
    }
    public void setGroup(String group) {
        this.group = group;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }

}

table 1

id name group

table 2

id name group

Thanks

UPDATE

06-08 16:47:35.802: E/AndroidRuntime(1242): FATAL EXCEPTION: AsyncTask #1
06-08 16:47:35.802: E/AndroidRuntime(1242): Process: com.example.listprueba, PID: 1242
06-08 16:47:35.802: E/AndroidRuntime(1242): java.lang.RuntimeException: An error occured while executing doInBackground()
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.os.AsyncTask$3.done(AsyncTask.java:300)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.FutureTask.run(FutureTask.java:242)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:231)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.lang.Thread.run(Thread.java:841)
06-08 16:47:35.802: E/AndroidRuntime(1242): Caused by: android.database.sqlite.SQLiteException: near "ALLSELECT": syntax error (code 1): , while compiling: SELECT _id + 1000000, nombre, precio FROM bebida UNION ALLSELECT _id + 2000000, nombre, precio FROM comida
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at com.example.listprueba.MainActivity$threadbd.doInBackground(MainActivity.java:216)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at com.example.listprueba.MainActivity$threadbd.doInBackground(MainActivity.java:1)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at android.os.AsyncTask$2.call(AsyncTask.java:288)
06-08 16:47:35.802: E/AndroidRuntime(1242):     at java.util.concurrent.FutureTask.run(FutureTask.java:237)
06-08 16:47:35.802: E/AndroidRuntime(1242):     ... 4 more
06-08 16:47:35.902: W/EGL_genymotion(1242): eglSurfaceAttrib not implemented
06-08 16:47:35.902: E/OpenGLRenderer(1242): Getting MAX_TEXTURE_SIZE from GradienCache
06-08 16:47:35.902: E/OpenGLRenderer(1242): MAX_TEXTURE_SIZE: 8192
06-08 16:47:35.922: E/OpenGLRenderer(1242): Getting MAX_TEXTURE_SIZE from Caches::initConstraints()
06-08 16:47:35.922: E/OpenGLRenderer(1242): MAX_TEXTURE_SIZE: 8192

Answer 1

Use a single compound query like this to get a single result set:

SELECT _id + 1000000, name, "group" FROM animal
UNION ALL
SELECT _id + 2000000, name, "group" FROM comida

(The added numbers make the IDs unique.)

This allows you to use a simple cursor adapter in the usual way to automatically show this data in the list view.