SELECT in SQLite - Android

Question

SELECT in SQLite - Android

Navigation

#1 by (4 votes)

1

I'm new to Android development, I'm using SQLite as a database. I need to do a SELECT, and return the values of three columns in my table . Then I need to set the attributes of my object with the return values of this SELECT .

I have an object called ImageColor , according to the 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 database looks like this:

IwanttodoaSELECTandreturnthevaluesofRED,GREENandBLUE.

MymethodthatdoestheSELECTandreturnsanobjectoftypeImageColorandthis:

publicColorImageSelectedColor(Stringnome){ColorImagecolor=newColorImage();StringselectQuery="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 do not know what's wrong. Does anyone know what's wrong?

java android sqlite

asked by anonymous 13.11.2017 / 22:13

1 answer

Why use a pointer in this algorithm? How to edit gigantic codes? [closed]

score 4 · Accepted Answer

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

In this case the plications are missing. They are required when using a string as the value. The correct form will look like this:

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

To avoid this type of error, use placeholders to receive the dynamic parts and provide the values to use separately. The method will ensure the correct assignment of the values to placeholders .

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

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

You'd still want to use one of the methods #

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