Fill object list with Sqlite data

Question

I have a state object containing the following attributes.

idState (String), siglaState (String)

And I have a table already filled in the database with the same attributes above. But I would like to return all this data to a collection of this object so that I can load a spinner. But the way I searched for this data is returning null.

Class creating the bank:

 public static final String NOME_BANCO = "carona";
private static final int VERSAO_BANCO = 1;

public BancoDados(Context context) {
    super(context, NOME_BANCO, null, VERSAO_BANCO);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_ESTADO);
    db.execSQL(SQL_CIDADE);
    db.execSQL(SQL_INSERE_UF);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}


String SQL_ESTADO = "CREATE TABLE [Estado](\n" +
        "[EstadoId] [INT] NOT NULL,\n" +
        "[Sigla] [char](2) NOT NULL,\n" +
        "CONSTRAINT [PK_Estado] PRIMARY KEY ([EstadoId])\n" +
        ");";

String SQL_CIDADE = "CREATE TABLE [Cidade](\n" +
        "[CidadeId] [int] NOT NULL,\n" +
        "[Nome] [varchar](50) NOT NULL,\n" +
        "[EstadoId] [INT] NULL,\n" +
        "CONSTRAINT [PK_Cidade] PRIMARY KEY ([CidadeId]\n" +
        "), CONSTRAINT[FK_ESTADO] FOREIGN KEY ([EstadoID]) REFERENCES [Estado] ([EstadoID])\n" +
        ");";


String SQL_INSERE_UF =  "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES " +
        "(11,'MG')," +
        "(19,'RJ')," +
        "(26,'SP');" ;

Method class that searches the database and creates a list of states

public class EstadoDAO {


public SQLiteDatabase sqLiteDatbase;
public BancoDados bancoDados;

public EstadoDAO (Context context) { bancoDados = new BancoDados(context);}


public List<Estado> carregarEstado (){

    List<Estado> estadoList = new ArrayList<>();

    this.sqLiteDatbase = this.bancoDados.getWritableDatabase();

    Cursor cursor = sqLiteDatbase.rawQuery("SELECT * FROM Estado",null);

    if(cursor.moveToFirst()){
        do{
        Estado estado = new Estado();
        estadoList.add(estado);

            estado.setIdEstado(cursor.getString(0));
            estado.setSiglaEstado(cursor.getString(1));

        }while(cursor.moveToNext());

    }
        return estadoList;

    }



}

1st Doubt: Why is returning null from the bank?

2nd doubt: Another doubt I have is that in fact there will be 2 spinner one for state other to city, how do I so when selecting the state I perform a select in the city table to load only with the cities of the state indicated.

Answer 1

1 - You are adding the object emptiness in its Arraylist. Try so:

Cursor cursor = sqLiteDatbase.rawQuery("SELECT * FROM Estado",null);   
if(cursor.moveToFirst()){
        do{
       Estado estado = new Estado();

       estado.setIdEstado(cursor.getString(0));
       estado.setSiglaEstado(cursor.getString(1));

       estadoList.add(estado);

    }while(cursor.moveToNext());

}
    return estadoList

Some suggestions:

• Create a class to store strings with SQL, thus making reuse easier in other classes.
• A class with all the queries you will need.

• Code hint to read data:

  DBAdapter meuBanco= new DBAdapter(this);
  meuBanco.openDB();
  Cursor cursor = meuBanco.getEstados();
  
  while(cursor.moveToNext()){
      String estadoId = cursor.getInt(0);
      String estadoSigla= cursor.getString(1);
  
      EstadoObj estado= new EstadoObj(estadoId, estadoSigla);
      estadoList.add(estado);
  }
  
  if(!(estado.size()<1)){
      // faça algo, se a lista não for vazia
  }
  meuBanco.closeDB();
  

  }

2 - About Spinner, implements Itemselectedlistener:

    spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
    @Override
    public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
        //seu código
    }

    @Override
    public void onNothingSelected(AdapterView<?> parentView) {
        // seu código
    }

});