How to Select a Query

Question

I’m having doubts about how to make a SELECT in my database.

Here are the classes I use:

DBHELPER

    public class DBHelper extends SQLiteOpenHelper {

    private static String NAME = "sqlitejuh.db";
    private static int VERSION = 1;

    public DBHelper (Context context){
        super(context, NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(
            "CREATE TABLE [exercicio] (\n" +
            "[codigo] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
            "[nome] VARCHAR(60)  NOT NULL,\n" +
            "[categoria] VARCHAR(60)  NOT NULL,\n" +
            "[nivel] VARCHAR(60)  NOT NULL,\n" +
            "[descricao] VARCHAR(256)  NOT NULL\n" +
            ")"
        );

    }

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

    }
}

Adapter

    public class ExerciseAdapter extends ArrayAdapter {

    private ArrayList<Exercise> exercises;

    public ExerciseAdapter(@NonNull Context context, @NonNull ArrayList<Exercise> exercises) {
        super(context, 0, exercises);
        this.exercises = exercises;
    }

    @NonNull
    @Override
    public View getView(int position, @Nullable View convertView, @NonNullViewGroup parent) {
        Exercise exercise = exercises.get(position);

        convertView = LayoutInflater.from(getContext()).inflate(R.layout.item_edit_list_exercises, null);

        TextView tvName = convertView.findViewById(R.id.item_text_view_name);
        TextView tvCategory = convertView.findViewById(R.id.item_text_view_category);
        TextView tvNivel = convertView.findViewById(R.id.item_text_view_nivel);

        tvName.setText(exercise.getNome());
        tvCategory.setText(exercise.getCategoria());
        tvNivel.setText(exercise.getNivel());

        return convertView;

    }

Modal

public class Exercise {

private int codigo;
private String nome;
private String categoria;
private String nivel;
private String descricao;
private boolean excluir;
private Context context;

public Exercise(Context context){
    this.context = context;
    codigo = -1;
}

public int getCodigo() {
    return codigo;
}

public String getNome() {
    return nome;
}

public void setNome(String nome) {
    this.nome = nome;
}

public String getCategoria() {
    return categoria;
}

public void setCategoria(String categoria) {
    this.categoria = categoria;
}

public String getNivel() {
    return nivel;
}

public void setNivel(String nivel) {
    this.nivel = nivel;
}

public String getDescricao() {
    return descricao;
}

public void setDescricao(String descricao) {
    this.descricao = descricao;
}

public boolean isExcluir() {
    return excluir;
}

public void setExcluir(boolean excluir) {
    this.excluir = excluir;
}

public ArrayList<Exercise> getExercises() {
    DBHelper dbHelper = null;
    SQLiteDatabase sqLiteDatabase = null;
    Cursor cursor = null;
    ArrayList<Exercise> exercises = new ArrayList<>();
    try{
        dbHelper = new DBHelper(context);
        sqLiteDatabase = dbHelper.getReadableDatabase();
        cursor = sqLiteDatabase.query("exercicio", null, null,
                null,null,null,null);
        while(cursor.moveToNext()){
            Exercise exercise = new Exercise(context);
            exercise.codigo = cursor.getInt(cursor.getColumnIndex("codigo"));
            exercise.nome = cursor.getString(cursor.getColumnIndex("nome"));
            exercise.categoria = cursor.getString(cursor.getColumnIndex("categoria"));
            exercise.nivel = cursor.getString(cursor.getColumnIndex("nivel"));
            exercise.descricao = cursor.getString(cursor.getColumnIndex("descricao"));
            exercises.add(exercise);
        }

    }catch(Exception e){
        e.printStackTrace();
    }finally {
        if ((cursor != null) && (!cursor.isClosed()))
            cursor.close();
        if (sqLiteDatabase != null)
            sqLiteDatabase.close();
        if (dbHelper != null)
            dbHelper.close();
    }

    return exercises;
}

public ArrayList<Exercise> getExercisesByCategory(){
    DBHelper dbHelper = null;
    SQLiteDatabase sqLiteDatabase = null;
    Cursor cursor = null;
    ArrayList<Exercise> exercises = new ArrayList<>();
    try{
        dbHelper = new DBHelper(context);
        sqLiteDatabase = dbHelper.getReadableDatabase();
        cursor = sqLiteDatabase.query("exercicio",
                new String[]{"categoria"},
                "categoria = ?",
                new String[]{String.valueOf(categoria)},
                null,
                null,
                null);
        while(cursor.moveToNext()){
            Exercise exercise = new Exercise(context);
            exercise.codigo = cursor.getInt(cursor.getColumnIndex("codigo"));
            exercise.nome = cursor.getString(cursor.getColumnIndex("nome"));
            exercise.categoria = cursor.getString(cursor.getColumnIndex("categoria"));
            exercise.nivel = cursor.getString(cursor.getColumnIndex("nivel"));
            exercise.descricao = cursor.getString(cursor.getColumnIndex("descricao"));
            exercises.add(exercise);
        }

    }catch(Exception e){
        e.printStackTrace();
    }finally {
        if ((cursor != null) && (!cursor.isClosed()))
            cursor.close();
        if (sqLiteDatabase != null)
            sqLiteDatabase.close();
        if (dbHelper != null)
            dbHelper.close();
    }

    return exercises;
}

public boolean save(){
    DBHelper dbHelper = null;
    SQLiteDatabase sqLiteDatabase = null;

    try{
        dbHelper = new DBHelper(context);
        sqLiteDatabase = dbHelper.getWritableDatabase();
        String sql = "";
        if (codigo == -1){
            sql = "INSERT INTO exercicio (nome,categoria,nivel,descricao) VALUES (?,?,?,?)";
        }else{
            sql = "UPDATE exercicio SET nome = ?, categoria = ?, nivel = ?, descricao = ? WHERE codigo = ?";
        }
        sqLiteDatabase.beginTransaction();

        SQLiteStatement sqLiteStatement = sqLiteDatabase.compileStatement(sql);
        sqLiteStatement.clearBindings();

        sqLiteStatement.bindString(1, nome);
        sqLiteStatement.bindString(2, categoria);
        sqLiteStatement.bindString(3, nivel);
        sqLiteStatement.bindString(4, descricao);

        if (codigo != -1)  sqLiteStatement.bindString(5, String.valueOf(codigo));

        sqLiteStatement.executeInsert();

        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
        return true;

    }catch(Exception e){
        e.printStackTrace();
        assert sqLiteDatabase != null;
        sqLiteDatabase.endTransaction();
        return false;
    }finally {
        if (sqLiteDatabase != null)
            sqLiteDatabase.close();
        if (dbHelper != null)
            dbHelper.close();
    }
}

}

And where I list the items: public class Coneexercisesactivity extends Activity Implements Adapterview.Onitemclicklistener{

ListView listViewCones;
ExerciseAdapter exerciseAdapter;
private ArrayList<Exercise> exercises;
private AlertDialog alerta;
private Exercise exerciseEdicao;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_cone_exercises);

    listViewCones = findViewById(R.id.list_view_cone);

    listViewCones.setOnItemClickListener(this);

    exercises = new Exercise(this).getExercisesByCategory();
    exerciseAdapter = new ExerciseAdapter(this, exercises);
    listViewCones.setAdapter(exerciseAdapter);
}

@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

    Toast.makeText(listViewCones.getContext(),
            "Posição Selecionada:" + position, Toast.LENGTH_LONG)
            .show();
}
}

Finally my question is how I can get only the category column data. I tried that way but I couldn’t... the list was returning empty.

Answer 1

The Sqlitedatabase class has a method called rawQuery, where you can place a query in the ANSI SQL standard. In the first parameter, we pass the SQL query of type String, and in the second parameter an array with values to be compared in a Where clause. If you have no condition and no Where clause, we pass null in the second parameter. Follows a possible code:

SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select categoria from exercicio where categoria = ?", new String[] {String.valueOf(categoria)});
// Percorre o cursor para pegar os registros.

Hugs!

Answer 2

I got it another way:

public ArrayList<Exercise> getExercisesByCategory(String categoria){
    DBHelper dbHelper = null;
    SQLiteDatabase sqLiteDatabase = null;
    Cursor cursor = null;
    ArrayList<Exercise> exercises = new ArrayList<>();
    try{
        dbHelper = new DBHelper(context);
        sqLiteDatabase = dbHelper.getReadableDatabase();

        String table = "exercicio";
        String[] tableColumns = new String[] {"codigo","nome","categoria","nivel","descricao"};
        String whereClause = "categoria = ?";
        String[] selectionArgs = {categoria};

        cursor = sqLiteDatabase.query(table, tableColumns, whereClause,
                selectionArgs,null,null,null);
        while(cursor.moveToNext()){
            Exercise exercise = new Exercise(context);
            exercise.codigo = cursor.getInt(cursor.getColumnIndex("codigo"));
            exercise.nome = cursor.getString(cursor.getColumnIndex("nome"));
            exercise.categoria = cursor.getString(cursor.getColumnIndex("categoria"));
            exercise.nivel = cursor.getString(cursor.getColumnIndex("nivel"));
            exercise.descricao = cursor.getString(cursor.getColumnIndex("descricao"));
            exercises.add(exercise);
        }

    }catch(Exception e){
        e.printStackTrace();
    }finally {
        if ((cursor != null) && (!cursor.isClosed()))
            cursor.close();
        if (sqLiteDatabase != null)
            sqLiteDatabase.close();
        if (dbHelper != null)
            dbHelper.close();
    }

    return exercises;
}

In this way I was able to return the data values of all columns, but only take the lines in which the category was "Cone"