Select on SQLite returning null

Question

I have a method to search an specific item, but when i use this code and try to use the return object, i get NullPointerException

public Obra buscarInfoObra(String filtro) {
    SQLiteDatabase db = helper.getReadableDatabase();
    Obra obraSelecionada = null;


    Cursor cursor = db.rawQuery("SELECT * FROM " +BancoDadosHelper.TABELA_OBRA+ " WHERE "+BancoDadosHelper.COLUNA_NUM_OBRA + "=" +filtro, null);

    if (cursor.moveToFirst()){
        String numObra = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_NUM_OBRA));
        int anoObra = cursor.getInt(cursor.getColumnIndex(BancoDadosHelper.COLUNA_ANO_OBRA));
        String descObra = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_DESCRICAO_OBRA));
        String jurisdicionado = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_JURISDICIONADO));
        String tipoObra = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_TIPO_OBRA));
        String tipoObjeto = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_TIPO_OBJETO));
        String endereco = cursor.getString(cursor.getColumnIndex(BancoDadosHelper.COLUNA_ENDERECO_OBRA));

        obraSelecionada = new Obra(numObra,anoObra,descObra,jurisdicionado,tipoObra,tipoObjeto,endereco);
    }

    cursor.close();
    db.close();
    return obraSelecionada;

What i'm doing wrong?

[EDIT]This is not a duplicate,i know what is a NullPointerException, but i don't understand why im getting this exception. I already try to put an existing data directly on code, but still getting NullPointer..

*What i'm doing wrong?* nothing ... obviosuly this method returns null when there is no record which meets specification — Selvin, Apr 09 '18 at 12:03
Added answer below. no records should return an empty cursor, cursors return null when the sql statement is bad. I believe it is from the missing quotes in the where — mononz, Apr 09 '18 at 12:25
@mononz no, rawQuery will never return null... if the statment is wrong the exception is thrown ... also op written that he is not getting NPE in this method but if he use value returned from this method — Selvin, Apr 09 '18 at 12:50

mononz · Accepted Answer · 2018-04-09T13:07:58.250

0

You should check for null on the cursor, then use, then close.

public Obra buscarInfoObra(String filtro) {
    SQLiteDatabase db = helper.getReadableDatabase();
    Obra obraSelecionada = null;
    Cursor cursor = db.rawQuery(...);
    if (cursor != null) {
        if (cursor.moveToFirst()) {
            // use cursor
            obraSelecionada  = ...
        }
        cursor.close();
    }
    db.close();
    return obraSelecionada;
}

However null cursors can be formed from bad sql statements. You need to wrap the string of your equals with quotes like

"SELECT * FROM table_name WHERE column_name = 'some value'"

you have some thing that looks more like this

"SELECT * FROM table_name WHERE column_name = some value"

so like this should fix

Cursor cursor = db.rawQuery("SELECT * FROM " + BancoDadosHelper.TABELA_OBRA + " WHERE " + BancoDadosHelper.COLUNA_NUM_OBRA + "=" + "'" + filtro + "'", null);

Side tip, you should write your query like this instead to avoid sql injection

db.query(BancoDadosHelper.TABELA_OBRA, null,
     BancoDadosHelper.COLUNA_NUM_OBRA + "=?",
     new String[] { filtro  },
     null, null, null);

Hope that helps

edited Apr 09 '18 at 13:07

answered Apr 09 '18 at 12:09

mononz

1,665
1
12
17

This answer solve my problem, but i dint understand perfectly the use of "?". I should use "=?" over "="+"'"+filtro+"'" and the android will understand the "?" is my variable filtro? Thanks anyway, you already saved my day – José Henrique Firmino Apr 09 '18 at 12:38
Called a preparedStatement. this may explain it better than I can. https://stackoverflow.com/a/34126564/6407116 . Basically the ? is a placeholder for your values in the string array. The query is checked as like a tempate query, then the values are then put in as raw data and not sql. Means any extra sql injected wouldn't be executed. Doesn't fully solve sql injection, but its a huge start – mononz Apr 09 '18 at 12:52
*However null cursors are formed from bad sql statements* **this is not true** if you pass bad SQL the exception will thrown ... **rawQuery will never return null** – Selvin Apr 09 '18 at 12:52
Apologises, I did not realise rawQuery would never return null. Out of habit, I've just always checked for null. I generally use ContentResolver that can be null I believe. – mononz Apr 09 '18 at 13:06

score -1 · Answer 2 · answered Apr 09 '18 at 12:01

Try in this way

  public RentMasterModel getRent(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_RENTMASTER, new String[]{KEY_RENTMASTER_ITEMID,
                    KEY_RENTMASTER_RENTHOUR, KEY_RENTMASTER_RENTDAY, KEY_RENTMASTER_RENTWEEK, KEY_RENTMASTER_RENTMONTH}
            , KEY_RENTMASTER_ITEMID + "=?",
            new String[]{String.valueOf(id)}, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    RentMasterModel rm = new RentMasterModel(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
    // return contact
    return rm;
}

here add the cursor value to bean class

Please add an answer to your "answer" ... not only not related code — Selvin, Apr 09 '18 at 12:04

Select on SQLite returning null

2 Answers2