shz1998
Reputation: 53
How to get SQLite to work with my Checkboxes
I have an activity which consists a ExpandableListView with around 30+ checkboxes. Since I'm already using SQLite for storing other information in my android app. I want to be able to also store the checkbox status (check/unchecked) in my SQLite. The problem now is if I create a table dedicated to storing all 30+ checkbox, it means I'll need 30+ columns in a single row of that table? Is there any easier way to store them using SQLite?
Below is my code for my ExpandableListView for my checklist :-
public class Checklist extends AppCompatActivity {
// ExpandableListAdapter listAdapter;
ExpListViewAdapterWithCheckbox listAdapter;
ExpandableListView expListView;
ArrayList<String> listDataHeader;
HashMap<String, List<String>> listDataChild;
public TextView name_checklist;
DatabaseHelper myDb;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.checklist);
myDb = new DatabaseHelper(this);
final int position = getIntent().getExtras().getInt("Position");
TextView name_checklist = (TextView) findViewById(R.id.checklistname);
Cursor gettripname = myDb.getTripName(position+1);
if (gettripname.getCount() == 0) {
Toast.makeText(Checklist.this, "No amount found !", Toast.LENGTH_LONG).show();
} else {
gettripname.moveToFirst();
String temp = gettripname.getString(0);
name_checklist.setText(temp+ " Trip Checklist");
}
// get the listview
expListView = (ExpandableListView) findViewById(R.id.lvExp);
// preparing list data
prepareListData();
listAdapter = new ExpListViewAdapterWithCheckbox(this, listDataHeader, listDataChild);
// setting list adapter
expListView.setAdapter(listAdapter);
// Listview Group click listener
expListView.setOnGroupClickListener(new OnGroupClickListener() {
@Override
public boolean onGroupClick(ExpandableListView parent, View v,
int groupPosition, long id) {
// Toast.makeText(getApplicationContext(),
// "Group Clicked " + listDataHeader.get(groupPosition),
// Toast.LENGTH_SHORT).show();
return false;
}
});
// Listview Group expanded listener
expListView.setOnGroupExpandListener(new OnGroupExpandListener() {
@Override
public void onGroupExpand(int groupPosition) {
Toast.makeText(getApplicationContext(),
listDataHeader.get(groupPosition) + " Expanded",
Toast.LENGTH_SHORT).show();
}
});
// Listview Group collasped listener
expListView.setOnGroupCollapseListener(new OnGroupCollapseListener() {
@Override
public void onGroupCollapse(int groupPosition) {
Toast.makeText(getApplicationContext(),
listDataHeader.get(groupPosition) + " Collapsed",
Toast.LENGTH_SHORT).show();
}
});
// Listview on child click listener
expListView.setOnChildClickListener(new OnChildClickListener() {
@Override
public boolean onChildClick(ExpandableListView parent, View v,
int groupPosition, int childPosition, long id) {
Toast.makeText(
getApplicationContext(),
listDataHeader.get(groupPosition)
+ " : "
+ listDataChild.get(
listDataHeader.get(groupPosition)).get(
childPosition), Toast.LENGTH_SHORT)
.show();
return false;
}
});
}
private void prepareListData() {
listDataHeader = new ArrayList<String>();
listDataChild = new HashMap<String, List<String>>();
// Adding header data
listDataHeader.add("Toiletries");
listDataHeader.add("Clothes");
listDataHeader.add("Essentials");
listDataHeader.add("Travel Comfort");
//listDataHeader.add("")
// Adding child data
List<String> toiletries = new ArrayList<String>();
toiletries.add("Bandages");
toiletries.add("Contacts");
toiletries.add("Contacts Solution");
toiletries.add("Cologne");
toiletries.add("Conditioner");
toiletries.add("Cotton Buds");
toiletries.add("Deodorant");
toiletries.add("Hairbrush");
toiletries.add("Nail Clippers");
toiletries.add("Razor");
toiletries.add("Shampoo");
toiletries.add("Shaving Gel");
toiletries.add("Toothbrush");
toiletries.add("Toothpaste");
List<String> clothes = new ArrayList<String>();
clothes.add("Belt ");
clothes.add("Bras");
clothes.add("Casual Pants");
clothes.add("Casual Shirts");
clothes.add("Heavy Coat");
clothes.add("Jumper");
clothes.add("Light Jacket");
clothes.add("Pyjamas");
clothes.add("Scarf");
clothes.add("Shoes");
clothes.add("Shorts");
clothes.add("Socks");
clothes.add("Swimwear");
List<String> essentials = new ArrayList<String>();
essentials.add("Digital Camera");
essentials.add("Headache Pills");
essentials.add("Fever Pills");
essentials.add("Diarrhea Pills");
essentials.add("Flu Pills");
essentials.add("Cough Medicine");
essentials.add("Powerbank");
essentials.add("USB Power Socket");
essentials.add("Sunglasses");
essentials.add("Earphones");
List<String> travelcomfort = new ArrayList<String>();
travelcomfort.add("Travel Pillow");
travelcomfort.add("Travel Blanket");
travelcomfort.add("Eye Mask");
travelcomfort.add("Ear Plugs");
travelcomfort.add("Books");
travelcomfort.add("Magazines");
travelcomfort.add("Card games");
listDataChild.put(listDataHeader.get(0), toiletries); // Header, Child data
listDataChild.put(listDataHeader.get(1), clothes);
listDataChild.put(listDataHeader.get(2), essentials);
listDataChild.put(listDataHeader.get(3), travelcomfort);
}
}
Upvotes: 1
Views: 730
Answers (2)
MikeT
Reputation: 57043
You could use a single column type (INTEGER) i.e. store and retrieve as java long (your_cursor.getLong()).
Then you can have up to 64 checkboxes (1 per bit).
The following shows how you can manipulate the checkboxes, from the data stored in the column, based upon the first being 0, the last being 63 (i.e. to set a checkbox, or test a checkbox).
An activity has been used to test and produced some results.
:-
public class CheckList extends AppCompatActivity {
// The value stored in a column (rightmost bit represents first checkbox, ... leftmost bit represents 64th checkbox)
long db_value = (long) 0b11010000_00000000_00000000_00000000_01100000_00000000_00000000_00010101L;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_check_list);
db_value = setCheckBox(db_value,8); // set the 9th (offset 8)
db_value = setCheckBox(db_value,0); //already set
// Inspect and report on all 64 checkboxes
for (int i=0;i < 64; i++) {
Log.d(
"CHECKBOX CHECKED",
"Checked Offset " + String.valueOf(i) +
" checked value is " + String.valueOf(
isCheckBoxNChecked(db_value,i)
)
);
}
}
/**
* test to see if checkbox n (0-63) is set
* @param value_from_db_column the value extracted from the db that represents all checkboxes
* @param checkbox_to_check the checkbox to be tested
* @return true if checked else false
*/
public boolean isCheckBoxNChecked(long value_from_db_column, int checkbox_to_check) {
return BigInteger.valueOf(value_from_db_column).testBit(checkbox_to_check);
}
/**
* Set checkbox n (0-63)
* @param current_checkbox the current checkbox status
* @param checkbox_to_set the checkbox to be set
* @return the amended checkbox status
*/
public long setCheckBox(long current_checkbox, int checkbox_to_set) {
return BigInteger.valueOf(current_checkbox).setBit(checkbox_to_set).longValue();
}
}
Result
D/CHECKBOX CHECKED: Checked Offset 0 checked value is true D/CHECKBOX CHECKED: Checked Offset 1 checked value is false D/CHECKBOX CHECKED: Checked Offset 2 checked value is true D/CHECKBOX CHECKED: Checked Offset 3 checked value is false D/CHECKBOX CHECKED: Checked Offset 4 checked value is true D/CHECKBOX CHECKED: Checked Offset 5 checked value is false D/CHECKBOX CHECKED: Checked Offset 6 checked value is false D/CHECKBOX CHECKED: Checked Offset 7 checked value is false D/CHECKBOX CHECKED: Checked Offset 8 checked value is true D/CHECKBOX CHECKED: Checked Offset 9 checked value is false D/CHECKBOX CHECKED: Checked Offset 10 checked value is false D/CHECKBOX CHECKED: Checked Offset 11 checked value is false D/CHECKBOX CHECKED: Checked Offset 12 checked value is false D/CHECKBOX CHECKED: Checked Offset 13 checked value is false D/CHECKBOX CHECKED: Checked Offset 14 checked value is false D/CHECKBOX CHECKED: Checked Offset 15 checked value is false D/CHECKBOX CHECKED: Checked Offset 16 checked value is false D/CHECKBOX CHECKED: Checked Offset 17 checked value is false D/CHECKBOX CHECKED: Checked Offset 18 checked value is false D/CHECKBOX CHECKED: Checked Offset 19 checked value is false D/CHECKBOX CHECKED: Checked Offset 20 checked value is false D/CHECKBOX CHECKED: Checked Offset 21 checked value is false D/CHECKBOX CHECKED: Checked Offset 22 checked value is false D/CHECKBOX CHECKED: Checked Offset 23 checked value is false D/CHECKBOX CHECKED: Checked Offset 24 checked value is false D/CHECKBOX CHECKED: Checked Offset 25 checked value is false D/CHECKBOX CHECKED: Checked Offset 26 checked value is false D/CHECKBOX CHECKED: Checked Offset 27 checked value is false D/CHECKBOX CHECKED: Checked Offset 28 checked value is false D/CHECKBOX CHECKED: Checked Offset 29 checked value is true D/CHECKBOX CHECKED: Checked Offset 30 checked value is true D/CHECKBOX CHECKED: Checked Offset 31 checked value is false D/CHECKBOX CHECKED: Checked Offset 32 checked value is false D/CHECKBOX CHECKED: Checked Offset 33 checked value is false D/CHECKBOX CHECKED: Checked Offset 34 checked value is false D/CHECKBOX CHECKED: Checked Offset 35 checked value is false D/CHECKBOX CHECKED: Checked Offset 36 checked value is false D/CHECKBOX CHECKED: Checked Offset 37 checked value is false D/CHECKBOX CHECKED: Checked Offset 38 checked value is false D/CHECKBOX CHECKED: Checked Offset 39 checked value is false D/CHECKBOX CHECKED: Checked Offset 40 checked value is false D/CHECKBOX CHECKED: Checked Offset 41 checked value is false D/CHECKBOX CHECKED: Checked Offset 42 checked value is false D/CHECKBOX CHECKED: Checked Offset 43 checked value is false D/CHECKBOX CHECKED: Checked Offset 44 checked value is false D/CHECKBOX CHECKED: Checked Offset 45 checked value is false D/CHECKBOX CHECKED: Checked Offset 46 checked value is false D/CHECKBOX CHECKED: Checked Offset 47 checked value is false D/CHECKBOX CHECKED: Checked Offset 48 checked value is false D/CHECKBOX CHECKED: Checked Offset 49 checked value is false D/CHECKBOX CHECKED: Checked Offset 50 checked value is false D/CHECKBOX CHECKED: Checked Offset 51 checked value is false D/CHECKBOX CHECKED: Checked Offset 52 checked value is false D/CHECKBOX CHECKED: Checked Offset 53 checked value is false D/CHECKBOX CHECKED: Checked Offset 54 checked value is false D/CHECKBOX CHECKED: Checked Offset 55 checked value is false D/CHECKBOX CHECKED: Checked Offset 56 checked value is false D/CHECKBOX CHECKED: Checked Offset 57 checked value is false D/CHECKBOX CHECKED: Checked Offset 58 checked value is false D/CHECKBOX CHECKED: Checked Offset 59 checked value is false D/CHECKBOX CHECKED: Checked Offset 60 checked value is true D/CHECKBOX CHECKED: Checked Offset 61 checked value is false D/CHECKBOX CHECKED: Checked Offset 62 checked value is true D/CHECKBOX CHECKED: Checked Offset 63 checked value is true
SQL examples
The following is how you could use SQL to do some manipulation :-
DROP TABLE IF EXISTS mycheckboxes;
CREATE TABLE IF NOT EXISTS mycheckboxes (id INTEGER PRIMARY KEY, USER TEXT, checkbox INTEGER);
INSERT INTO mycheckboxes (user,checkbox) VALUES
('Fred', (1 << 0) + (1 << 5) + (1 << 7)),
('Fred', 0 + (1 << 3) + (1 << 5) + (1 << 8)),
('Fred', 0 + (1 << 2) + (1 << 4) + (1 << 63))
;
-- Show all
SELECT * FROM mycheckboxes;
-- Only select rows where 64th checkbox is checked
SELECT * FROM mycheckboxes WHERE checkbox & (1 << 63) <> 0;
-- Only select rows where 1st and 6th checkboxes are set
SELECT * FROM mycheckboxes WHERE checkbox & (1 << 0) <> 0 AND checkbox & (1 << 5) <> 0;
- Creates the table named mycheckboxes
- Adds 3 rows
- 1st rows has 1st, 6th and 8th checkboxes set
- 2nd row has 4th, 6th and 9th checkboxes set
- 3rd row has 3rd 5th and 64th checkboxes set
Results :-
- all rows
- Select rows where 64th checkbox is checked (only 3rd row)
- select rows where 1st and 6th checkboxes are set (only 1st row)
Android Demo of principle
The following code is a simple demonstration of how the above can be utilised in a simple Android App.
The App has
- two buttons (SAVE to save an entry, and LOG IT to report on the saved entries from a checkbox perspective).
- 6 checkboxes
It looks like (after some checkboxes have been set/checked) :-
With a couple of entries added (SAVE clicked) the output in the Log could be :-
1-29 02:44:52.803 3633-3633/so53521994numerouscheckboxes.so53521994numerouscheckboxes D/LOGCHECKBOX: For row 1 checkboxes value is 13
Checkbox 1 is CHECKED.
Checkbox 2 is NOT CHECKED.
Checkbox 3 is CHECKED.
Checkbox 4 is CHECKED.
Checkbox 5 is NOT CHECKED.
Checkbox 6 is NOT CHECKED.
11-29 02:44:52.803 3633-3633/so53521994numerouscheckboxes.so53521994numerouscheckboxes D/LOGCHECKBOX: For row 2 checkboxes value is 42
Checkbox 1 is NOT CHECKED.
Checkbox 2 is CHECKED.
Checkbox 3 is NOT CHECKED.
Checkbox 4 is CHECKED.
Checkbox 5 is NOT CHECKED.
Checkbox 6 is CHECKED.
- Row 2 being the entry added with the above.
The Code
The database helper DatabaseHelper.java :-
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mycheckboxes.db";
public static final int DBVERSION = 1;
public static final String CHECKBOX_TABLE = "mycheckboxes";
public static final String CHECKBOX_COL_ID = BaseColumns._ID;
public static final String CHECKBOX_COL_USER = "user";
public static final String CHECKBOX_COL_CHECKBOX = "checkbox";
SQLiteDatabase mDB;
public DatabaseHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_checkbox_table = "CREATE TABLE IF NOT EXISTS " + CHECKBOX_TABLE + "(" +
CHECKBOX_COL_ID + " INTEGER PRIMARY KEY, " +
CHECKBOX_COL_USER + " TEXT, " +
CHECKBOX_COL_CHECKBOX + " INTEGER" +
")";
db.execSQL(crt_checkbox_table);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {}
public long insertEntry(String user, long checkboxes) {
ContentValues cv = new ContentValues();
cv.put(CHECKBOX_COL_USER,user);
cv.put(CHECKBOX_COL_CHECKBOX,checkboxes);
return mDB.insert(CHECKBOX_TABLE,null,cv);
}
public void logCheckBoxStatusForUser(String user, int checkbox_count) {
String whereclause = CHECKBOX_COL_USER + "=?";
String[] whereargs = new String[]{user};
Cursor csr = mDB.query(
CHECKBOX_TABLE,
null,
whereclause,
whereargs,
null,
null,
null
);
while (csr.moveToNext()) {
long currrent_checkboxes = csr.getLong(csr.getColumnIndex(CHECKBOX_COL_CHECKBOX));
StringBuilder sb = new StringBuilder("For row ")
.append(String.valueOf(csr.getPosition() + 1))
.append(" checkboxes value is ")
.append(String.valueOf(currrent_checkboxes));
for (int i = 0; i < checkbox_count; i++) {
sb.append("\n\tCheckbox ")
.append(String.valueOf(i+1))
.append(" is ");
if (CheckList.isCheckBoxNChecked(currrent_checkboxes,i)) {
sb.append("CHECKED.");
} else {
sb.append(" NOT CHECKED.");
}
}
Log.d("LOGCHECKBOX",sb.toString());
}
}
}
The Activity CheckList.java :-
public class CheckList extends AppCompatActivity {
CheckBox[] checkboxes = new CheckBox[6];
Button save,logit;
DatabaseHelper mDBHlpr;
String current_user = "Fred";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_check_list);
mDBHlpr = new DatabaseHelper(this);
checkboxes[0] = this.findViewById(R.id.cb01);
checkboxes[1] = this.findViewById(R.id.cb02);
checkboxes[2] = this.findViewById(R.id.cb03);
checkboxes[3] = this.findViewById(R.id.cb04);
checkboxes[4] = this.findViewById(R.id.cb05);
checkboxes[5] = this.findViewById(R.id.cb06);
save = this.findViewById(R.id.savebutton);
save.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
save_to_db();
}
});
logit = this.findViewById(R.id.logitbutton);
logit.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
mDBHlpr.logCheckBoxStatusForUser(current_user,checkboxes.length);
}
});
}
/**
* test to see if checkbox n (0-63) is set
* @param value_from_db_column the value extracted from the db that represents all checkboxes
* @param checkbox_to_check the checkbox to be tested
* @return true if checked else false
*/
public static boolean isCheckBoxNChecked(long value_from_db_column, int checkbox_to_check) {
return BigInteger.valueOf(value_from_db_column).testBit(checkbox_to_check);
}
/**
* Set checkbox n (0-63)
* @param current_checkbox the current checkbox status
* @param checkbox_to_set the checkbox to be set
* @return the amended checkbox status
*/
public long setCheckBox(long current_checkbox, int checkbox_to_set) {
return BigInteger.valueOf(current_checkbox).setBit(checkbox_to_set).longValue();
}
private void save_to_db() {
long checkboxes_to_save = 0L;
for (int i =0; i < checkboxes.length; i++) {
if (checkboxes[i].isChecked()) {
checkboxes_to_save = setCheckBox(checkboxes_to_save,i);
}
}
mDBHlpr.insertEntry(current_user,checkboxes_to_save);
}
}
Upvotes: 0
Ivan
Reputation: 782
In the case you are storing the state of the checkboxes in your SQLite DB, I would recommend to use 30+ rows with just one column.
This is because, imagine tomorrow you need to add one more checkbox, you will need to change your table to add one more column for the new checkbox (change your DB schema), whereas if you use one row for each checkbox, you will just need to add one row.
I think it is a more efficient way.
EDIT
In response to your comment, if a trip has his own set of states of the checkboxes maybe an approach to save this without having a lot of rows/columns added could be to add to your (I will assume) trip table, one more column in which you could have the state of all the checkboxes in just one cell.
I would go with it saving a 30+ length binary string. (so each of the characters goes for one checkbox state).
The cons of this approach is that you should parse the states to save them and parse them back to read them.
This would save just one string for all the checkboxes, such as (with an example of 31 checkboxes):
0000000000000000000000000000001 (just the last checkbox is enabled)
1000000000000000000000000000001 (first and last checkbox are enabled)
Upvotes: 1
Related Questions
- Android : How to display a Sqlite Database With CheckBox in listview Automatically
- How to make working CheckBox inside ListView (which has items from sqlite database)
- Insert checkbox values into Sqlite Database
- How to make a checkbox from information found in DataBase?
- Getting sqlite data into ExpandableListView
- Populate CheckList from SQLite
- How to Keep the ExpandableListView Checkboxes Checked States?
- Android ListView With CheckBoxes
- SQLite to ListView with CheckBox guidance
- Android: ListView with CheckBox, populated from SQLite database not quite working