How to build a SELECT in SQLite?



  • I'm new to Android development, I'm 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 seal the attributes of my object with the return values of that SELECT .

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

    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:

    inserir a descrição da imagem aqui

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

    my method that makes SELECT and returns a type object ImageColor and this:

    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.
    Does anyone know what's wrong?



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

    In this case the plications are missing. They are needed when using a string as value. The correct way will be:

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

    To avoid this type of errors the one indicated is to use placeholders to receive the dynamic parts and separately provide the values to use. The method will ensure the correct allocation of values to 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;
    

    }

    Better yet to use one of the methods https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String,%20java.lang.String)

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

    In your case it would be like this:

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2