How to make a query in SQLite?

Question

How to make a query in SQLite?

Navigation

#1 by (2 votes)

1

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

I have a search method in a dao class, but I do not know which logic to use to implement the user search, which one is best?

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

java android sqlite

asked by anonymous 06.04.2017 / 20:41

1 answer

TypeError: obj is not defined, but the variable is defined Apache server stopped recording accesses in the access.log file

score 2 · Accepted Answer

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

To see a line in the cursor, use one of the Cursor motion methods, which should always be called before you start reading values. Generally, you should start by calling moveToFirst() , which places 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 Cursor methods, such as getString() or getLong() . For each of the methods, you must pass the index position of the desired column.

In this, here's how you could do it:

/**
 * 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 type Usuario in which you receive the search value.

Here's 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 is written here, in the documentation, How to save data in SQL databases , for more details.

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

You just need to create a method like this done above, but passing email as a parameter. If you return any results, an email is already registered in your database. This way you can check this:

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
}