Android中ListView无法显示SQLite数据的问题求助

我创建了一个ListView，计划从SQLite数据库中获取数据进行展示，为此编写了getrequestitems方法来映射数据并添加到ArrayList中，但打开对应Activity时，ListView没有显示任何记录，调试也未捕获到异常，怀疑SQL查询语句SELECT * from request WHERE time > date('now','-1 day')存在问题。

getrequestitems方法代码 #

public List<Map<String, String>> getrequestitems()   {
        List<Map<String, String>> data = null;


       data = new ArrayList<Map<String, String>>();
        try {
            SQLiteDatabase db = dbHandler.getReadableDatabase();
            String sql = "SELECT * from request WHERE time > date('now','-1 day')";
            Cursor rs = db.rawQuery(sql,null);
            if(rs.moveToFirst()) {
                do {
                    Map<String, String> dtname = new HashMap<String, String>();
                    dtname.put("bloodgrp", rs.getString(rs.getColumnIndex("bloodgrp")));
                    dtname.put("date", "Date:- " + rs.getString(rs.getColumnIndex("Datetime1")));
                    dtname.put("time", "Time:- " + rs.getString(rs.getColumnIndex("time")));
                    dtname.put("name", "Name:- " + rs.getString(rs.getColumnIndex("Name")));
                    dtname.put("Username", rs.getString(rs.getColumnIndex("Username")));
                    data.add(dtname);
                }
                while (rs.moveToNext());

            }
        } catch (Exception e) {
            Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
        }
        return data;
    }

完整Activity代码 #

public class request extends AppCompatActivity {

    SimpleAdapter ad;
    TextView list3;
      private SlidrInterface slidr;
      private DBHandler dbHandler;
      private static ArrayList<String> arrayList = new ArrayList<>();

     @RequiresApi(api = Build.VERSION_CODES.N)
     @Override
    protected void onCreate(Bundle savedInstanceState) {
         super.onCreate(savedInstanceState);
         setContentView(R.layout.activity_request);
         slidr = Slidr.attach(this);
         slidr.unlock();
         dbHandler = new DBHandler(this);
       
         list3 = findViewById(R.id.list3);
    
         TextView txt = findViewById(R.id.list4);

         ListView lst1 = (ListView) findViewById(R.id.list2);
         List<Map<String, String>> MyDataList = null;
          MyDataList = getrequestitems();
         String[] from = {"bloodgrp", "date", "time", "name", "Username"};
         int[] to = {R.id.listt, R.id.date, R.id.time1, R.id.name3, R.id.user};
         ad = new SimpleAdapter(request.this, MyDataList, R.layout.listrequest, from, to);
          lst1.setAdapter(ad);
       

         lst1.setOnItemClickListener(new AdapterView.OnItemClickListener() {
             @Override
             public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                  List<Map<String, String>> MyDataList1 = null;
                  MyDataList1 = getrequestitems();
                 String[] from = {"Username"};
                 int[] to = {R.id.user};
                 Toast.makeText(getApplicationContext(), MyDataList1.toString(), Toast.LENGTH_SHORT).show();
                 ad = new SimpleAdapter(request.this, MyDataList1, R.layout.listrequest, from, to);
                 if(ad.getCount()==0){
                     Toast.makeText(getApplicationContext(), "No requests are currently visible", Toast.LENGTH_SHORT).show();
                 }
                 HashMap<String, String> obj = (HashMap<String, String>) ad.getItem(i);
                 String name = (String) obj.get("Username");

                 try {
                     SQLiteDatabase db = dbHandler.getReadableDatabase();
                     String sql = "select * from request where Username = '" + name + "'";
                     Cursor rs = db.rawQuery(sql,null);
                     if (rs.moveToFirst()) {
                         do {
                             runOnUiThread(new Runnable() {
                                 @SuppressLint("Range")
                                 @Override
                                 public void run() {
                                     try {

                                         final AlertDialog.Builder alert = new AlertDialog.Builder(request.this);
                                         View mView = getLayoutInflater().inflate(R.layout.custom_dialog3, null);
                                         final TextView txt_inputText = (TextView) mView.findViewById(R.id.name1);
                                         txt_inputText.setText(rs.getString(rs.getColumnIndex("Name")));
                                         final TextView txt_inputText2 = (TextView) mView.findViewById(R.id.contactno1);
                                         txt_inputText2.setText(rs.getString(rs.getColumnIndex("contactNo")));
                                         final TextView txt_inputText3 = (TextView) mView.findViewById(R.id.message1);
                                         txt_inputText3.setText(rs.getString(rs.getColumnIndex("message")));


                                         Button btn_cancel = (Button) mView.findViewById(R.id.btn_cancel);
                                         Button btn_okay = (Button) mView.findViewById(R.id.btn_okay);
                                         alert.setView(mView);
                                         final AlertDialog alertDialog = alert.create();
                                         alertDialog.setCanceledOnTouchOutside(false);
                                         btn_cancel.setOnClickListener(new View.OnClickListener() {
                                             @Override
                                             public void onClick(View v) {
                                                 alertDialog.dismiss();
                                             }
                                         });
                                         btn_okay.setOnClickListener(new View.OnClickListener() {
                                             @Override
                                             public void onClick(View v) {
                                                 String number = txt_inputText2.getText().toString();
                                                 Intent intent = new Intent(Intent.ACTION_DIAL, Uri.parse("tel:" + number));// Initiates the Intent
                                                 startActivity(intent);
                                                 alertDialog.dismiss();
                                             }

                                         });
                                         alertDialog.show();
                                         alertDialog.getWindow().setLayout(730, 850); //Controlling width and height.

                                     } catch (Exception e) {
                                         runOnUiThread(new Runnable() {
                                             public void run() {
                                                 Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_LONG).show();
                                             }
                                         });
                                     }
                                 }
                             });

                         }while (rs.moveToNext());
                     }


                 } catch (Exception e) {
                     runOnUiThread(new Runnable() {
                         public void run() {
                           Toast.makeText(getApplicationContext(),e.getMessage(),Toast.LENGTH_LONG).show();
                         }
                     });
                 }

             }
         });

     }
    @SuppressLint("Range")
    public List<Map<String, String>> getrequestitems()   {
        List<Map<String, String>> data = null;


       data = new ArrayList<Map<String, String>>();
        try {
            SQLiteDatabase db = dbHandler.getReadableDatabase();
            String sql = "SELECT * from request WHERE time > date('now','-1 day')";
            Cursor rs = db.rawQuery(sql,null);
            if(rs.moveToFirst()) {
                do {
                    Map<String, String> dtname = new HashMap<String, String>();
                    dtname.put("bloodgrp", rs.getString(rs.getColumnIndex("bloodgrp")));
                    dtname.put("date", "Date:- " + rs.getString(rs.getColumnIndex("Datetime1")));
                    dtname.put("time", "Time:- " + rs.getString(rs.getColumnIndex("time")));
                    dtname.put("name", "Name:- " + rs.getString(rs.getColumnIndex("Name")));
                    dtname.put("Username", rs.getString(rs.getColumnIndex("Username")));
                    data.add(dtname);
                }
                while (rs.moveToNext());

            }
        } catch (Exception e) {
            Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
        }
        return data;
    }



}

问题排查与解决方案 #

1. SQL查询语句的核心问题

你的查询语句大概率是失效原因，分三种场景处理：

• 字段仅存时分秒：如果time字段存储的是HH:mm:ss格式字符串，和date('now','-1 day')返回的日期字符串比较时，字符串逻辑对比完全不符合时间预期，需改为存储完整日期时间（如YYYY-MM-DD HH:mm:ss）后再查询。
• 完整日期时间存储：若time字段是完整日期时间格式，将date('now','-1 day')替换为datetime('now','-1 day')，因为date()仅返回日期部分，datetime()返回包含时间的完整值，才能正确筛选过去24小时内的数据。
• 毫秒时间戳存储：如果time字段是long型毫秒时间戳，查询语句改为：

  SELECT * from request WHERE time > strftime('%s','now','-1 day') * 1000
  

2. 代码优化点

• 关闭资源避免泄漏：添加finally块关闭Cursor和数据库连接：

  Cursor rs = null;
  SQLiteDatabase db = null;
  try {
      db = dbHandler.getReadableDatabase();
      String sql = "SELECT * from request WHERE time > datetime('now','-1 day')";
      rs = db.rawQuery(sql,null);
      // 数据处理逻辑
  } catch (Exception e) {
      Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
  } finally {
      if (rs != null) rs.close();
      if (db != null) db.close();
  }
  

• 防止SQL注入：Item点击事件中的查询改为参数化查询：

  String sql = "select * from request where Username = ?";
  Cursor rs = db.rawQuery(sql, new String[]{name});
  

• 异步处理数据库操作：getrequestitems在UI线程执行查询会导致卡顿，建议用AsyncTask或Coroutines异步执行。
• 空数据提示：初始化Adapter后直接判断数据是否为空并显示提示：

  if(MyDataList.isEmpty()){
      txt.setText("暂无请求记录");
  }
  

3. 调试步骤

• 在getrequestitems中添加日志打印查询记录数：

  Log.d("RequestDebug", "查询到记录数：" + rs.getCount());
  

• 用Android Studio的Database Inspector直接执行查询语句，验证是否返回预期数据。

内容的提问来源于stack exchange，提问作者Rishi Ff