How to do a table search with SQLite?

Question

How to do a table search with SQLite?

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

Well, 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 for the user's login and password, to be able to compare.

Follow the code:

BankData:

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) {

    }
}

Bank 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";
        }
    }

android sqlite

asked by anonymous 05.11.2017 / 00:21

2 answers

1

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;
}

05.11.2017 / 02:09

Alternative to JPA Implementation Golang - Doubt over pointers

score 2 · Accepted Answer

Below is the code of the method responsible for picking up a bank record and returning a user in response. Any questions, please comment!

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 got tighter. One of the solutions pointed out by some users and which may 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 like a silly change and it does not 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 does not work, test with the single quotes.