如何使用字符串生成器进行下面显示的查询？如果有人能提供一个如何在查询中使用字符串生成器的例子，我不知道如何使用它。请帮我解决
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;
}}
也许可以考虑以下内容，它将满足空或空的性别/状态以及所有组合，即仅性别、仅状态或两者都有(当两者都有时假设为and(：-
@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;
}
演示
以下演示了在几个场景中使用的上述内容：-
/* Add some testing data */
helper.insert(new User("User1","User1","user1@email.com",new byte[]{0,0,0,0},"M","NEW"));
helper.insert(new User("User2","User2","user1@email.com",new byte[]{0,0,0,0},"F","OLD"));
helper.insert(new User("User3","User3","user1@email.com",new byte[]{0,0,0,0},"M","OLD"));
helper.insert(new User("User4","User4","user1@email.com",new byte[]{0,0,0,0},"F","NEW"));
helper.insert(new User("User5","User5","user1@email.com",new byte[]{0,0,0,0},"M","NOTHING"));
helper.insert(new User("User6","User6","user1@email.com",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 + "<"
);
}
}
导致日志包含：-
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<
使用类似的东西
StringBuilder query = new StringBuilder().append(" select * from ").append(TABLE_NAME);
String query1 = query.append(" WHERE ").append(COLUMN_GENDER).append(" =?").toString();
String query2 = query.append(" WHERE ").append(COLUMN_STATUS).append(" =?").toString();