Select with two columns returning empty

0

In a mobile application (Android), I have two tables. One login table (user and password) and one of (simple register). The APP has a registry screen, if the user does not have the account it creates at the moment and proceeds. If this user makes a registration and popular the table of (simple register) and another user that does not have account, create an account and access, it will access the data of the first user. I tried to run INNER JOIN but the result (select) returns empty. If I run without inner join (select * from tabela) it returns the data normally.

public class PessoaDao extends SQLiteOpenHelper {   
    private static final String NOME_BANCO = "TrainingBD";
    private static final int VERSION = 1;
    private static final String TABELA = "pessoa";

    private static final String ID = "_id";
    private static final String NOME = "nome";
    private static final String IDADE = "idade";
    private static final String CELULAR = "celular";
    private static final String DATACADASTRO = "dataCadastro";
    private static final String TIPOTREINO = "tipoTreino";
    private static final String PESO = "peso";
    private static final String ALTURA = "altura";
    private static final String OBSERVACAO = "observacao";
    private static final String USUARIOAUTENTICADO = "usuarioautenticado";

    private static final String TAG = PessoaDao.class.getSimpleName();
    private static final String TABELAUSUARIO = "users";
    private static final String USERS_ID = "_id";
    private static final String USERS_LOGIN = "login";
    private static final String USERS_SENHA = "senha";

    public PessoaDao(Context context) {
        super(context, NOME_BANCO, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        //tabela pessoas (formulario de cadastro)
        String sql = "CREATE TABLE IF NOT EXISTS "+TABELA+" (" +
                ""+ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
                ""+NOME+" TEXT, "+
                ""+IDADE+" INTEGER, "+
                ""+CELULAR+" TEXT, "+
                ""+DATACADASTRO+" TEXT, "+
                ""+TIPOTREINO+" TEXT, "+
                ""+PESO+" TEXT, "+
                ""+ALTURA+" TEXT, "+
                ""+OBSERVACAO+" TEXT, "+
                ""+USUARIOAUTENTICADO+", INTEGER," +
                " FOREIGN KEY ("+USUARIOAUTENTICADO+") REFERENCES "+TABELAUSUARIO+"("+USERS_LOGIN+"));";

        db.execSQL(sql);

        String CREATE_TABLE_USERS = "CREATE TABLE IF NOT EXISTS " + TABELAUSUARIO + "("
                + USERS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + USERS_LOGIN + " TEXT,"
                + USERS_SENHA + " TEXT);";

        db.execSQL(CREATE_TABLE_USERS);
    }

    //select para chamar os dados que estão no banco
    public ArrayList<Pessoa> selectAllPessoa(){
        String colunas = "SELECT * FROM " + TABELA + "," + TABELAUSUARIO + " WHERE " + TABELAUSUARIO + "." + USERS_LOGIN + " = " + TABELA + "." + USUARIOAUTENTICADO;

        //SE EU USAR ESSE MODO ELE RETORNO NORMALMENTE
        //OS DADOS JA CADASTRADOS
        //String colunas =  " Select * from " + TABELA  ;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(colunas, null);

        ArrayList<Pessoa> listPessoa = new ArrayList<Pessoa>();
        cursor.moveToFirst();

            while(cursor.moveToNext()){
                Pessoa pessoa = new Pessoa();

                pessoa.setId(cursor.getInt(0));
                pessoa.setNome(cursor.getString(1));
                pessoa.setIdade(cursor.getString(2));
                pessoa.setCelular(cursor.getString(3));
                pessoa.setDataCadastro(cursor.getString(4));
                pessoa.setTipoTreino(cursor.getString(5));
                pessoa.setPeso(cursor.getString(6));
                pessoa.setAltura(cursor.getString(7));
                pessoa.setObservacao(cursor.getString(8));
                pessoa.setUsuarioautenticado(cursor.getString(9));

                listPessoa.add(pessoa);
            }
        cursor.close();
        return listPessoa;
    }
}

Yes, when the user logs into the app it will only show what he added or what to edit in the ListView.

It was not to work if I put this line of code that you posted in my search method

// select to call the data that is in the database     public ArrayList selectAllPessoa () {

   String colunas = "SELECT * FROM " + TABELA + " WHERE " + USERS_ID + "=" + ID;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(colunas, null);

    ArrayList<Pessoa> listPessoa = new ArrayList<Pessoa>();
    cursor.moveToFirst();

        while(cursor.moveToNext()){

            Pessoa pessoa = new Pessoa();

            pessoa.setId(cursor.getInt(0));
            pessoa.setNome(cursor.getString(1));
            pessoa.setIdade(cursor.getString(2));
            pessoa.setCelular(cursor.getString(3));
            pessoa.setDataCadastro(cursor.getString(4));
            pessoa.setTipoTreino(cursor.getString(5));
            pessoa.setPeso(cursor.getString(6));
            pessoa.setAltura(cursor.getString(7));
            pessoa.setObservacao(cursor.getString(8));
            pessoa.setUsuarioautenticado(cursor.getInt(9));

            listPessoa.add(pessoa);

        }
    cursor.close();
    return listPessoa;
}

asked by anonymous 17.05.2017 / 23:50

2 answers

0

Hello, do you return the data only from the logged in user right? I do not see the need to make join ....

You already have the method that returns all people and theoretically you already have the data of the authenticated user, so create a method that returns only the person where the id hits the logged in user, something like

public Pessoa pegaPessoa(string id){

    String sql = "SELECT * FROM " + TABELA + " WHERE " + USERS_ID + "=" + id

//.....restante do codigo

}
    
18.05.2017 / 01:53
0

The relationship between tables appears to be 1: 1 (or will your application allow 2 users to have the same login user?). If it is the case of being 1: 1, why create 2 tables? I'm not seeing any gain in this implementation (separate personal data from access data). You could keep everything on one table and avoid unnecessary joins.

1: 1 relationships make more sense when you have an application that wants to consume data from the table of another application and you would like to add extra data (columns) that do not exist in the original table and your application has no management over it, then the solution is to create a new table with the necessary columns in your application by placing a foreign key referencing the source table.

Sorry to have fled the original topic, but it was just a suggestion. Regarding the question, the join probably does not work because you are comparing an INTEGER field with another being TEXT. Even the FK itself that you created should give you problems because of this if you enable the constraints in the DB (In SQLITE they are not enabled by default, you have to enable them manually at the bank's initialization).

    
18.05.2017 / 18:35