How do I search the table with Sqlite?

Question

Good guys, how do I make a select in a table using Sqlite on Android? So far I have the table created and the Insert.

For example: search by User login and password, to be able to compare.

Follows the code:

Codless:

public class BancoDados extends SQLiteOpenHelper {
    public static final String NOME_BANCO = "banco.db";
    public static final String NOME_TABELA = "usuario";
    public static final int VERSAO_BANCO = 1;

    public BancoDados(Context context) {
        super(context, NOME_BANCO, null, VERSAO_BANCO);

    }

    @Override
    public void onCreate(SQLiteDatabase bd) {
        bd.execSQL("create table if not exists " +
                "usuario(_id integer primary key autoincrement, " +
                "nome text not null," +
                "login text not null," +
                " senha text not null," +
                " email text not null);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase bd, int oldVersion, int newVersion) {

    }
}

Control:

public class ControleBanco {
    private SQLiteDatabase bd;
    private BancoDados banco;

    public ControleBanco(Context context) {
        banco = new BancoDados(context);
    }

    public String inserirDados(String nome, String login, String senha, String email) 
{
        long resultado;
        ContentValues valores = new ContentValues();
        bd = banco.getWritableDatabase();
        valores.put("nome", nome);
        valores.put("login", login);
        valores.put("senha", senha);
        valores.put("email", email);

        resultado = bd.insert(BancoDados.NOME_TABELA, "", valores);
        bd.close();
        if (resultado == -1) {
            return "Falha ao cadastrar";
        } else {
            return "Cadastrado com sucesso";
        }
    }

Answer 1

Below is the code of the method responsible for taking a record of the bank and returning a user as a response. Any doubt, comments there!

public Usuario pegarUsuarioPorEmail(String email){

    Usuario u;  
    bd = banco.getReadableDatabase();

    Cursor cursor = bd.rawQuery("SELECT nome, login, senha, email FROM usuario WHERE email = ?", new String[] {email});
    cursor.moveToFirst();

    if(cursor.getCount() > 0){

        u = new Usuario();
        u.setNome(cursor.getString(0));
        u.setLogin(cursor.getString(1));
        u.setSenha(cursor.getString(2));
        u.setEmail(cursor.getString(3));
    } else {
        u = null;
    }

    cursor.close();
    bd.close();

    return u;
}

In your Mainactivity:

private Usuario recuperarUsuario(String email){

   ControleBanco banco = new ControleBanco(this);

   Usuario u = banco.pegarUsuarioPorEmail(email);

   return u;
}

EDIT: The problem you are facing is because from android studio 3.0, the syntax engine for sql query has become more rigorous. One of the solutions pointed out by some users and that can work is the following. Change this code snippet:

Cursor cursor = bd.rawQuery("SELECT nome, login, senha, email FROM usuario WHERE email = ?", new String[] {email});

for this:

String querySql = "SELECT nome, login, senha, email FROM usuario WHERE email = ?";
Cursor cursor = bd.rawQuery(querySql, new String[] {email});

It seems a silly change and it doesn’t make the slightest difference, but some users have stated that this way the query works. Test and tell me if it worked. NOTE: Remember to test without the single quotes and if it doesn’t work, test with the single quotes.

Answer 2

You can do it this way:

public boolean pesquisar(String texto){

    SQLiteDatabase db = getReadableDatabase();
    String sqlSelect = "SELECT * FROM TABELA WHERE texto= '"+texto+"'";
    Cursor c = db.rawQuery(sqlSelect,null);

    if(c.getCount()>0){
        return true;
    }

    c.close();
    db.close();

    return false;
}