How to build a SELECT on Sqlite?

Question

I am new in Android development, I am using Sqlite as a bank. I need to make a SELECT, and return the values of three columns of my table. Then I need to set the attributes of my object with the return values of this SELECT .

1- I own an object called Imagecolor, as code below:

public class ColorImage {

    private int RedColor;
    private int GreenColor;
    private int BlueColor;
    private int Id;
    private String Nome;

   //Getters e Setters
}

2 - My table in the bank is like this:

I want to make a SELECT and return the values of RED, GREEN and BLUE.

my method that makes SELECT and returns an object of type Imagecolor and that:

public ColorImage SelectedColor(String nome){

    ColorImage color = new ColorImage();

    String selectQuery = "SELECT red, green, blue FROM COLOR WHERE nome =" + nome;

    SQLiteDatabase banco = this.getWritableDatabase();

    Cursor cursor = banco.rawQuery(selectQuery, null);

    color.setRedColor(cursor.getInt(0));
    color.setGreenColor(cursor.getInt(1));
    color.setBlueColor(cursor.getInt(2));

    return color;
}

But it’s not working, I don’t know what’s wrong. Someone knows what’s wrong?

Answer 1

Constructing dynamic SQL commands with concatenation does not always produce the expected/valid result.

In this case, the applications are missing. They are necessary when using a string as value. The correct form will be thus:

String selectQuery = "SELECT red, green, blue FROM COLOR WHERE nome = " + "'" + nome + "'";

To avoid this type of errors the indicated is to use placeholders to receive the dynamic parts and provide the values to be used separately. The method will ensure that the values are correctly assigned to the placeholders.

String selectQuery = "SELECT red, green, blue FROM COLOR WHERE nome = ?";
...
...
Cursor cursor = db.rawQuery(selectQuery, new String[] { nome });

Don’t forget to call cursor.moveToFirst() before using the cursor.

public ColorImage SelectedColor(String nome){

    ColorImage color = new ColorImage();

    String selectQuery = "SELECT red, green, blue FROM COLOR WHERE nome = ?";

    SQLiteDatabase banco = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(selectQuery, new String[] { nome });
    if(cursor.moveToFirst()){
        color.setRedColor(cursor.getInt(0));
        color.setGreenColor(cursor.getInt(1));
        color.setBlueColor(cursor.getInt(2));
    }
    cursor.close();

    return color;
}

Even better to use one of the methods query()

Cursor query(String table, 
             String[] columns, 
             String selection, 
             String[] selectionArgs, 
             String groupBy, 
             String having, 
             String orderBy)

In your case it would look like this:

Cursor query("COLOR", 
             new String[]{"red", "green", "blue"}, 
             String "nome = ?", 
             new String[]{nome}, 
             null, 
             null, 
             null);