Android应用ListView读取SQLite数据库数据滚动卡顿问题求助

Hey there! Let's tackle that ListView scrolling lag issue you're facing. Looking through your code, I've spotted several key areas causing the performance hit, and I'll walk you through fixing each one step by step.

1. Fix the Incomplete getView() Method in Your Adapter #

Your searchad class's getView() method was missing critical code: it didn't set text to the TextView, didn't return the convertView, and created a redundant searchadapter object. All these issues break display efficiency and contribute to lag.

Here's the corrected adapter code:

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;
import java.util.ArrayList;

public class searchad extends ArrayAdapter<searchadapter> {
    private Context mcontext;
    private int mresource;
    private int lastpos = -1;

    static class ViewHolder{
        TextView state;
    }

    public searchad(Context context, int resource, ArrayList<searchadapter> objects){
        super(context, resource, objects);
        mcontext = context;
        mresource = resource;
    }

    @Override
    public View getView(int pos, View convertView, ViewGroup parent){
        // Get the data item for this position
        searchadapter item = getItem(pos);
        ViewHolder holder;

        if (convertView == null){
            LayoutInflater inflater = LayoutInflater.from(mcontext);
            convertView = inflater.inflate(mresource, parent, false);
            holder = new ViewHolder();
            holder.state = convertView.findViewById(R.id.txtst);
            convertView.setTag(holder);
        } else {
            holder = (ViewHolder) convertView.getTag();
        }

        // Bind data to the TextView
        holder.state.setText(item.getSt());

        // Return the fully populated view
        return convertView;
    }
}

2. Avoid Loading All Data into Memory at Once (Use CursorAdapter) #

Right now, you're loading all query results into an ArrayList upfront. For large datasets, this eats up memory and causes lag. Instead, use SimpleCursorAdapter to bind data directly from the Cursor to the ListView, loading only what's needed for visible items.

Step 1: Update DatabaseAccess to Return a Cursor #

Modify your database helper to return a Cursor directly (and fix the SQL syntax error + add parameterized queries for security):

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseAccess {
    private SQLiteOpenHelper openHelper;
    private SQLiteDatabase db;
    private static DatabaseAccess instance;

    private DatabaseAccess(Context context){
        this.openHelper = new DatabaseOpenHelper(context.getApplicationContext());
    }

    public static DatabaseAccess getInstance(Context context){
        if (instance == null)
            instance = new DatabaseAccess(context.getApplicationContext());
        return instance;
    }

    public void open(){
        this.db = openHelper.getReadableDatabase(); // Use readable for queries only
    }

    public void close(){
        if (db != null && db.isOpen())
            this.db.close();
    }

    public Cursor getSearchResults(String name){
        // Fixed SQL syntax (added missing AND) + parameterized query
        String query = "SELECT land.State, Avg_Land_Rate, Annual_Avg_Temp_inC, No_of_Companies, annual_avg_rain_inCm, No_of_Schools, No_of_Malls, No_of_Colleges, No_of_Pubs, Literacy_rate_inPercent, Rank_in_Crime_Rate " +
                "FROM land, company, education, people, recreation, weather " +
                "WHERE UPPER(land.state) = ? " +
                "AND land.State = company.State " +
                "AND land.State_code = education.State_code " +
                "AND land.State_code = people.State_code " +
                "AND land.State_short_code = recreation.State_short_code " +
                "AND land.State_short_code = weather.State_short_code";

        return db.rawQuery(query, new String[]{name.toUpperCase()});
    }
}

Step 2: Use SimpleCursorAdapter in Your Activity #

Run database operations on a background thread to avoid blocking the main thread (the #1 cause of scrolling lag):

import android.content.Intent;
import android.database.Cursor;
import android.os.AsyncTask;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class searchint extends AppCompatActivity {
    private ListView listView;
    private DatabaseAccess dbAccess;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_searchint);
        listView = findViewById(R.id.lsearch);
        listView.setClickable(true);
        dbAccess = DatabaseAccess.getInstance(getApplicationContext());
        
        // Load data on background thread
        new LoadSearchResultsTask().execute();
    }

    private class LoadSearchResultsTask extends AsyncTask<Void, Void, Cursor> {
        @Override
        protected Cursor doInBackground(Void... voids) {
            dbAccess.open();
            // Replace with your actual search term (e.g., from intent extra)
            String searchTerm = "DEFAULT_SEARCH_TERM";
            return dbAccess.getSearchResults(searchTerm);
        }

        @Override
        protected void onPostExecute(Cursor cursor) {
            super.onPostExecute(cursor);
            // Map Cursor columns to ListView items
            String[] fromColumns = {"State"};
            int[] toViews = {R.id.txtst};

            SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                    searchint.this,
                    R.layout.search,
                    cursor,
                    fromColumns,
                    toViews,
                    0
            );

            listView.setAdapter(adapter);

            // Handle item clicks
            listView.setOnItemClickListener((parent, view, position, id) -> {
                Cursor selectedCursor = (Cursor) parent.getItemAtPosition(position);
                String state = selectedCursor.getString(selectedCursor.getColumnIndex("State"));
                
                Intent intent = new Intent(getApplicationContext(), searchres.class);
                intent.putExtra("SELECTED_STATE", state);
                startActivity(intent);
            });
        }
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (dbAccess != null) dbAccess.close();
    }
}

3. Additional Optimization Tips #

• Add indexes to your SQLite tables: Index columns used in WHERE and JOIN clauses (like State, State_code) to speed up query execution.
• Remove unused animations: Avoid adding animations in getView() as they can cause jank.
• Avoid memory leaks: Always use getApplicationContext() when initializing database helpers to prevent activity context leaks.

After implementing these changes, your ListView should scroll smoothly even with large datasets—we're now loading data on demand, avoiding main thread blocking, and using efficient data binding.

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