insert a cursor within a while in android

0

In android I have a cursor where it looks for the data in the database and perfectly returns the data but ..

How do I insert a second cursor into the first one?

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 obviously the data is missing parts since the other data is in a second table

    
asked by anonymous 20.12.2017 / 07:35

2 answers

3

In your case, it's best to use LEFT JOIN . Left Join, serves just to merge the contents of two or more tables. That 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 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;
    
20.12.2017 / 08:12
0

I thank Valdeir for his help, and his response is more practical and clean, I actually managed to put a cursor inside another one in 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;
    } 
    
20.12.2017 / 18:54