I'm wondering how to do 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 do I get 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 do I get the data from the category column only. I tried it that way but I could not ... the list was returning empty.