insert a cursor inside of a while on android

Question

on android i have a cursor where it searches the data in the database and returns the data perfectly but..

as I insert a second cursor inside the first?

to pull data from a second table

my code

 try {
        StringBuilder sbQuery = new StringBuilder();
        sbQuery.append("select * from contatos");
        //
        Cursor cursor = db.rawQuery(sbQuery.toString(), null);
        //



        while (cursor.moveToNext()) {
            HMAux_Contatos hmAux = new HMAux_Contatos();
            hmAux.put(
                    HMAux_Contatos.ID,
                    cursor.getString(cursor.getColumnIndex("id_contato")))
            ;

            hmAux.put(
                    HMAux_Contatos.NOME,
                    cursor.getString(cursor.getColumnIndex("nome")))
            ;

            StringBuilder sbQuery2 = new StringBuilder();
            sbQuery.append("select * from contatos_numeros where id_contato = "+hmAux.get(HMAux_Contatos.ID)+" and principal = '1' ");
            Cursor cursor2 = db.rawQuery(sbQuery2.toString(), null);

            hmAux.put(
                    HMAux_Contatos.DESCRICAO,
                    cursor2.getString(cursor2.getColumnIndex("descricao")))
            ;

            hmAux.put(
                    HMAux_Contatos.NUMERO,
                    cursor2.getString(cursor2.getColumnIndex("numero")))
            ;


            cursor2.close();
            cursor2 = null;

            //
            dados.add(hmAux);

        }



        cursor.close();
        cursor = null;
    } catch (Exception e) {
        Log.d("#Erro Do Servidor", String.valueOf(e));
    }

without the second cursor , it works perfect but it is obvious that the data is missing parts since the other data are in a second table

Answer 1

In your case, the ideal is to use LEFT JOIN. Left Join, serves precisely to merge the content of two or more tables. This way you will have a cursor with information from both tables.

With Left Join, your code would look like this.

SELECT *
FROM contatos c
LEFT JOIN contatos_numeros cn   /* Aqui eu informo que o SQLite deve mescar a tabela contatos com a tabela contatos_numeros */
    ON (c._id = cn.id_contato)  /* Aqui eu defino a regra, nesse caso ele pegará o ID da tabela contato e irá comprar com o campo id_contato da tabela contatos_numeros */
WHERE cn.principal = 1 /* Aqui eu filtro somente os que houver setado como principal na tabela contato_numeros */

If you want to test, just run the code below on the site Sqlitetutorial

DROP TABLE IF EXISTS `contatos`;
CREATE TABLE IF NOT EXISTS `contatos` (
  `_id` INTEGER PRIMARY KEY,
  `nome` TEXT NOT NULL);

DROP TABLE IF EXISTS `numeros`;
CREATE TABLE IF NOT EXISTS `numeros` (
  `_id` INTEGER PRIMARY KEY,
  `contato_id` INTEGER,
  `numero` TEXT NOT NULL,
  `principal` INTEGER NOT NULL CHECK (`principal` IN (1, 0)));

DELETE FROM `contatos`;
DELETE FROM `numeros`;

INSERT INTO `contatos` (nome) VALUES ("Fulano");
INSERT INTO `contatos` (nome) VALUES ("Beltrano");
INSERT INTO `contatos` (nome) VALUES ("Cicrano");

SELECT * FROM `contatos`;

INSERT INTO `numeros` (contato_id, numero, principal) VALUES (1, "123", 1);
INSERT INTO `numeros` (contato_id, numero, principal) VALUES (1, "456", 0);
INSERT INTO `numeros` (contato_id, numero, principal) VALUES (2, "789", 1);
INSERT INTO `numeros` (contato_id, numero, principal) VALUES (3, "159", 0);

SELECT * FROM `numeros`;

/* Aqui eu mesclo as tabelas contato e numeros, comparando o ID do contato. Depois filtro apenas os valores com o campo principal setado como 1  */
SELECT * FROM contatos c LEFT JOIN numeros n ON (c._id = n.contato_id) WHERE n.principal = 1;

Answer 2

I thank Valdeir for the help, and his response and more practical and clean, I really managed to put a cursor inside another one this way

try {
        StringBuilder sbQuery = new StringBuilder();
        sbQuery.append("select * from contatos");
        //
        Cursor cursor = db.rawQuery(sbQuery.toString(), null);
        //

        while (cursor.moveToNext()) {
            HMAux_Contatos hmAux = new HMAux_Contatos();
            hmAux.put(
                    HMAux_Contatos.ID,
                    cursor.getString(cursor.getColumnIndex("id_contato")))
            ;

            hmAux.put(
                    HMAux_Contatos.NOME,
                    cursor.getString(cursor.getColumnIndex("nome")))
            ;

            //dentro deste while eu crio o sbQuery2 e junto dele o cursor2 , isso dentro do primeiro cursor 
            StringBuilder sbQuery2 = new StringBuilder();
            sbQuery2.append("select * from contatos_numeros where id_contato = '"+hmAux.get(HMAux_Contatos.ID)+"' and principal = '1'");
            Cursor cursor2 = db.rawQuery(sbQuery2.toString(), null);

            //aqui o cursor2 inicia sua procra dentro do primeiro cursor, que no meu exemplo, sempre retornara 1 valor mas é possivel trazer varios elementos
            while (cursor2.moveToNext()) {
                hmAux.put(
                        HMAux_Contatos.DESCRICAO,
                        cursor2.getString(cursor2.getColumnIndex("descricao")))
                ;

                hmAux.put(
                        HMAux_Contatos.NUMERO,
                        cursor2.getString(cursor2.getColumnIndex("numero")))
                ;

            }

            //apos a procura eu limpo e fecho o cursor2 dentro do primeiro cursor
            //deixando pronto para a proxima pesquisa do while 
            cursor2.close();
            cursor2 = null;


            //bonus **  getCount retorna o numero de linhas dentro da pesquisa
            if(cursor.getCount()>0){
                String valor = "1";
                hmAux.put(
                        HMAux_Contatos.TAMANHO_DE_CONTATOS,
                        valor)
                ;
            }


            //
            dados.add(hmAux);

        }



        cursor.close();
        cursor = null;
    }