Issue
How can I use stringbuilder for the queries shown below? I don't have any idea how to use it, if anyone can provide an example of how to use stringbuilders in queries. please help me out
public ArrayList<User> getAllData(String Gender,String Status)
{
ArrayList<User> arrayList = new ArrayList<>();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor;
String query = " select * from " +TABLE_NAME;
if (!Gender.equals(""))
{
String query1 = query+ " WHERE " + COLUMN_GENDER + " =?";
String[] select = new String[]{Gender};
cursor = db.rawQuery(query1, select);
}
else if (!Status.equals(""))
{
String query2 = query+ " WHERE " + COLUMN_STATUS + " =?";
String[] select = new String[]{Status};
cursor = db.rawQuery(query2, select);
}
else
{
String query3 = query;
cursor = db.rawQuery(query3, null);
}
if (cursor.moveToFirst()) {
do {
String FirstName = cursor.getString(0);
String LastName = cursor.getString(1);
String Email = cursor.getString(2);
byte[] image = cursor.getBlob(5);
String gender = cursor.getString(6);
String status = cursor.getString(8);
// Log.e("Image Length here", "" + image.length);
// Log.e("Gender Length here", "" + gender);
User user = new User(FirstName, LastName, Email, image, gender, status);
arrayList.add(user);
}
while (cursor.moveToNext());
}
return arrayList;
}}
Solution
Perhaps consider the following, which will cater for empty or null gender/status and for all combinations i.e. gender only, status only or both (assumes AND when both):-
@SuppressLint("Range") /* may be required due to issue with getColumnIndex with SDK 31 */
public ArrayList<User> getAllData(String Gender, String Status) {
ArrayList<User> arrayList = new ArrayList<>();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor;
//String query = " select * from " + TABLE_NAME; not needed
StringBuilder whereclause = new StringBuilder(); // Added
boolean genderArgRequired = false; // Added
boolean statusArgRequired = false; // Added
String[] whereargs = new String[]{}; // Added
/* If gender is not null or it's length is > 0 then add the WHERE clause (less the WHERE keyword as query convenience method adds the WHERE clause)
/* and indicate that arg is required
*/
if (Gender != null && Gender.length() > 0) {
whereclause.append(COLUMN_GENDER).append("=? ");
genderArgRequired = true;
}
/* If status is not null and it's length is greater then
if the whereclause is not empty (i.e. gender has been given) then
add AND followed by the where clause
otherwise no gender so add the WHERE clause
and indicate that the arg is required
*/
if (Status != null && Status.length() > 0) {
statusArgRequired = true;
if (whereclause.length() > 0) {
whereclause.append(" AND ");
}
whereclause.append(COLUMN_STATUS).append("=? ");
}
/* Build the args (no args by default) */
/* if both status and gender then both args required*/
if (genderArgRequired && statusArgRequired) {
whereargs = new String[]{Gender, Status};
}
/* Other if one of either is required add just the one arg */
else {
if (genderArgRequired) {
whereargs = new String[]{Gender};
}
if (statusArgRequired) {
whereargs = new String[]{Status};
}
}
// Uses the query convenience method (i.e. builds the SQL)
cursor = db.query(TABLENAME, null, whereclause.toString(), whereargs, null, null, null);
// No need to moveTofirst while moveToNext has the same functionality
while (cursor.moveToNext()) {
// Can add a new user to the arraylist directly
arrayList.add(new User(
/* Note assumes column names */
/* more flexible/safer to use getColumnIndex
as column position is extracted from the cursor rather than being hardcoded.
*/
cursor.getString(cursor.getColumnIndex(COLUMN_FIRSTNAME)),
cursor.getString(cursor.getColumnIndex(COLUMN_LASTNAME)),
cursor.getString(cursor.getColumnIndex(COLUMN_EMAIL)),
cursor.getBlob(cursor.getColumnIndex(COLUMN_IMAGE)),
cursor.getString(cursor.getColumnIndex(COLUMN_GENDER)),
cursor.getString(cursor.getColumnIndex(COLUMN_STATUS))
)
);
}
cursor.close(); //<<<<< SHOULD ALWAYS CLOSE CURSOR WHEN DONE WITH IT
return arrayList;
}
Demo
The following demonstrates the above being used in a few scenarios :-
/* Add some testing data */
helper.insert(new User("User1","User1","[email protected]",new byte[]{0,0,0,0},"M","NEW"));
helper.insert(new User("User2","User2","[email protected]",new byte[]{0,0,0,0},"F","OLD"));
helper.insert(new User("User3","User3","[email protected]",new byte[]{0,0,0,0},"M","OLD"));
helper.insert(new User("User4","User4","[email protected]",new byte[]{0,0,0,0},"F","NEW"));
helper.insert(new User("User5","User5","[email protected]",new byte[]{0,0,0,0},"M","NOTHING"));
helper.insert(new User("User6","User6","[email protected]",new byte[]{0,0,0,0},"F","NOTHING"));
/* Call the logGetAllData for various scenarios */
/* Note that the actual getAllData method is called from the logGetAllData method */
logGetAllData("F","NEW","RUN1"); // User4 only
logGetAllData("F","OLD","RUN2"); // User2 only
logGetAllData(null,"","RUN3"); // ALL (1-6)
logGetAllData("F",null,"RUN4"); // User2,4,6
logGetAllData("","NEW","RUN5"); // User 1 and 4
logGetAllData("?????","?????","RUN6"); // None
logGetAllData(null,null,"RUN7"); // All
}
private void logGetAllData(String gender, String status, String tag_suffix) {
for(User u: helper.getAllData(gender,status)) {
Log.d("USERINFO_" + tag_suffix,
"FirstName is " + u.FirstName +
" LastName is " + u.LastName +
" Gender is " + u.gender +
" Status is " + u.status +
" GENDER ARG =>" + gender + "< STATUS ARG =>" + status + "<"
);
}
}
Resulting in the log containing :-
2021-12-29 22:19:30.665 D/USERINFO_RUN1: FirstName is User4 LastName is User4 Gender is F Status is NEW GENDER ARG =>F< STATUS ARG =>NEW<
2021-12-29 22:19:30.667 D/USERINFO_RUN2: FirstName is User2 LastName is User2 Gender is F Status is OLD GENDER ARG =>F< STATUS ARG =>OLD<
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User1 LastName is User1 Gender is M Status is NEW GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User2 LastName is User2 Gender is F Status is OLD GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User3 LastName is User3 Gender is M Status is OLD GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User4 LastName is User4 Gender is F Status is NEW GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User5 LastName is User5 Gender is M Status is NOTHING GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.669 D/USERINFO_RUN3: FirstName is User6 LastName is User6 Gender is F Status is NOTHING GENDER ARG =>null< STATUS ARG =><
2021-12-29 22:19:30.671 D/USERINFO_RUN4: FirstName is User2 LastName is User2 Gender is F Status is OLD GENDER ARG =>F< STATUS ARG =>null<
2021-12-29 22:19:30.671 D/USERINFO_RUN4: FirstName is User4 LastName is User4 Gender is F Status is NEW GENDER ARG =>F< STATUS ARG =>null<
2021-12-29 22:19:30.672 D/USERINFO_RUN4: FirstName is User6 LastName is User6 Gender is F Status is NOTHING GENDER ARG =>F< STATUS ARG =>null<
2021-12-29 22:19:30.675 D/USERINFO_RUN5: FirstName is User1 LastName is User1 Gender is M Status is NEW GENDER ARG =>< STATUS ARG =>NEW<
2021-12-29 22:19:30.675 D/USERINFO_RUN5: FirstName is User4 LastName is User4 Gender is F Status is NEW GENDER ARG =>< STATUS ARG =>NEW<
2021-12-29 22:19:30.678 D/USERINFO_RUN7: FirstName is User1 LastName is User1 Gender is M Status is NEW GENDER ARG =>null< STATUS ARG =>null<
2021-12-29 22:19:30.679 D/USERINFO_RUN7: FirstName is User2 LastName is User2 Gender is F Status is OLD GENDER ARG =>null< STATUS ARG =>null<
2021-12-29 22:19:30.679 D/USERINFO_RUN7: FirstName is User3 LastName is User3 Gender is M Status is OLD GENDER ARG =>null< STATUS ARG =>null<
2021-12-29 22:19:30.679 D/USERINFO_RUN7: FirstName is User4 LastName is User4 Gender is F Status is NEW GENDER ARG =>null< STATUS ARG =>null<
2021-12-29 22:19:30.679 D/USERINFO_RUN7: FirstName is User5 LastName is User5 Gender is M Status is NOTHING GENDER ARG =>null< STATUS ARG =>null<
2021-12-29 22:19:30.679 D/USERINFO_RUN7: FirstName is User6 LastName is User6 Gender is F Status is NOTHING GENDER ARG =>null< STATUS ARG =>null<
Answered By - MikeT
