How to make a query in Sqlite?

Question

In my project the user must register to enter, when placing the data name, email and password and when clicking the registration I would like to check if the email already exists, how to do ?

I have a search method in a dao class, but I don’t know which logica to use to implement user search, which best practice?

    public List<Usuario> buscaUsuario(){
        String sql = "SELECT * FROM USUARIO;";
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.rawQuery(sql, null);

        List<Usuario> usuario = new ArrayList<Usuario>();
        while (c.moveToNext()){
            Usuario user = new Usuario();
            user.setId(c.getLong(c.getColumnIndex("_ID")));
            user.setNome(c.getString(c.getColumnIndex("CAMPO_NOME")));
            user.setEmail(c.getString(c.getColumnIndex("CAMPO_EMAIL")));
            user.setSenha(c.getString(c.getColumnIndex("CAMPO_SENHA")));

            usuario.add(user);
        }
        c.close();
        return usuario;
    }

Answer 1

To read from a database use the method query() passing the selection criteria and the desired columns. The results of the query are returned in an object Cursor.

To see a line on the cursor, use one of the motion methods Cursor, which should always be called before starting to read values. Generally, one should start by calling moveToFirst(), that puts the "read position" in the first entry in the results. For each row, you can read the value of a column by calling one of the methods of the Cursor, as getString() or getLong(). For each of the methods, you must pass the index position of the desired column.

In this, see how you could do:

/**
 * Este metodo busca um usuario pelo ID
 *
 * @param userID
 * @return
 */
public Usuario buscaUsuario(int userID){
    // instancia do objeto Usuario
    Usuario usuario = new Ususario();
    // Cariação do SQLiteDatabase a leitura do banco de dados
    SQLiteDatabase db = getReadableDatabase();
    // criação do cursor no qual recebera a query d do usuario
    Cursor cursor = db.query("USUARIO", new String[]{"_ID", "CAMPO_NOME",
            "CAMPO_EMAIL", "CAMPO_SENHA"}, "_ID" + "= ? ", new String[]{String.valueOf(userID)}, null, null, null, null);

    // verifica se o cursos retornou alguma resultado
    if(cursor!=null){
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            usuario.setId(cursor.getInt(0));            // definição do ID retornado do cursor
            usuario.setNome(cursor.getString(1));       // definição do NOME retornado do cursor
            usuario.setEmail(cursor.getString(2));      // definição do EMAIL retornado do cursor
            usuario.setSenha(cursor.getString(3));      // definição da SENHA retornado do cursor
        } else {
            // caso não retornar nenhum usuario do cursor, o retorno da função será nula
            return null;
        }
    }
    // finaliza o SQLiteDatabase
    db.close();
    return usuario;
}

To use the method, simply create an object of the type Usuario in which it received the value of the search.

See how your code would look:

// aqui você instancia seu Handler extendendo o `SQLiteOpenHelper`
MyDBHandler db = new MyDBHandler(this);

// userID é o id do usuário no qual precisa buscar
Usuario user = db.buscaUsuario(userID); 

All this written here, this in the documentation, How to Save Data to SQL Databases, for more details.

I would like to check if the email already exists, how to do ?

Just create a method like this done above, but passing the email as a parameter. If you return any results, is that there is already an email registered in your database. This way you can check so:

if(user!=null){
   // se entrou aqui é porque existe um usuário baseado na busca
} else {
   // se entrou aqui é porque NÃO existe um usuário baseado na busca
}