MST

星途 面试题库

面试题：SQLite与Android UI交互的复杂数据处理及优化

假设在一个Android应用中，用户界面需要展示从SQLite数据库获取的复杂数据，这些数据需要经过多表关联、聚合计算等操作，并且要求实时更新。描述从数据库获取数据、处理数据到展示在UI上的完整流程及技术方案，如何优化数据获取和处理的性能以避免UI卡顿，以及在数据更新时如何高效通知UI进行刷新，给出涉及到的关键类和方法以及大致代码框架。
50.2万 热度难度
数据库SQLite

知识考点

AI 面试

面试题答案

一键面试

1. 从数据库获取数据

  1. 关键类：SQLiteOpenHelper、SQLiteDatabase
  2. 关键方法：
    • SQLiteOpenHelper的getReadableDatabase()或getWritableDatabase()获取数据库实例。
    • SQLiteDatabase的query()方法进行单表查询，execSQL()执行复杂SQL语句（多表关联、聚合计算）。
  3. 代码框架：
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "your_database.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建表的SQL语句
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库的逻辑
    }
}

// 获取数据
DatabaseHelper helper = new DatabaseHelper(context);
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT column1, column2, COUNT(*) FROM table1 " +
        "JOIN table2 ON table1.id = table2.table1_id " +
        "GROUP BY column1, column2", null);

2. 处理数据

  1. 关键类：ArrayList、自定义的数据模型类
  2. 关键方法：遍历Cursor，将数据封装到自定义数据模型类的实例，添加到ArrayList。
  3. 代码框架：
ArrayList<YourDataModel> dataList = new ArrayList<>();
if (cursor.moveToFirst()) {
    do {
        YourDataModel model = new YourDataModel();
        model.setColumn1(cursor.getString(cursor.getColumnIndex("column1")));
        model.setColumn2(cursor.getString(cursor.getColumnIndex("column2")));
        model.setCount(cursor.getInt(cursor.getColumnIndex("COUNT(*)")));
        dataList.add(model);
    } while (cursor.moveToNext());
}
cursor.close();

3. 展示在UI上

  1. 关键类：RecyclerView、Adapter（如RecyclerView.Adapter）
  2. 关键方法：
    • RecyclerView的setAdapter()和layoutManager设置。
    • Adapter的onCreateViewHolder()、onBindViewHolder()等方法实现。
  3. 代码框架：
RecyclerView recyclerView = findViewById(R.id.recyclerView);
recyclerView.setLayoutManager(new LinearLayoutManager(context));
YourAdapter adapter = new YourAdapter(dataList);
recyclerView.setAdapter(adapter);

class YourAdapter extends RecyclerView.Adapter<YourAdapter.ViewHolder> {
    private ArrayList<YourDataModel> dataList;

    public YourAdapter(ArrayList<YourDataModel> dataList) {
        this.dataList = dataList;
    }

    @NonNull
    @Override
    public ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.item_layout, parent, false);
        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(@NonNull ViewHolder holder, int position) {
        YourDataModel model = dataList.get(position);
        holder.textView1.setText(model.getColumn1());
        holder.textView2.setText(model.getColumn2());
        holder.textViewCount.setText(String.valueOf(model.getCount()));
    }

    @Override
    public int getItemCount() {
        return dataList.size();
    }

    class ViewHolder extends RecyclerView.ViewHolder {
        TextView textView1, textView2, textViewCount;

        public ViewHolder(@NonNull View view) {
            super(view);
            textView1 = view.findViewById(R.id.textView1);
            textView2 = view.findViewById(R.id.textView2);
            textViewCount = view.findViewById(R.id.textViewCount);
        }
    }
}

4. 优化数据获取和处理的性能以避免UI卡顿

  1. 使用异步任务：在AsyncTask或Executor的execute()方法中执行数据库操作和数据处理，避免在主线程操作。
new AsyncTask<Void, Void, ArrayList<YourDataModel>>() {
    @Override
    protected ArrayList<YourDataModel> doInBackground(Void... voids) {
        // 数据库获取和数据处理代码
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT column1, column2, COUNT(*) FROM table1 " +
                "JOIN table2 ON table1.id = table2.table1_id " +
                "GROUP BY column1, column2", null);
        ArrayList<YourDataModel> dataList = new ArrayList<>();
        if (cursor.moveToFirst()) {
            do {
                YourDataModel model = new YourDataModel();
                model.setColumn1(cursor.getString(cursor.getColumnIndex("column1")));
                model.setColumn2(cursor.getString(cursor.getColumnIndex("column2")));
                model.setCount(cursor.getInt(cursor.getColumnIndex("COUNT(*)")));
                dataList.add(model);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return dataList;
    }

    @Override
    protected void onPostExecute(ArrayList<YourDataModel> dataList) {
        super.onPostExecute(dataList);
        // 更新UI
        YourAdapter adapter = new YourAdapter(dataList);
        recyclerView.setAdapter(adapter);
    }
}.execute();
  1. 索引优化：对SQLite数据库的查询字段添加索引，提高查询性能。
CREATE INDEX index_column1 ON table1(column1);
  1. 批量操作：如果有插入或更新操作，使用事务进行批量处理，减少数据库操作次数。
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();
try {
    for (YourDataModel model : dataList) {
        // 插入或更新操作
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

5. 在数据更新时高效通知UI进行刷新

  1. 使用观察者模式：
    • 关键类：Observable、Observer（Java内置的观察者模式相关类，或使用RxJava等库实现响应式编程）。
    • 关键方法：Observable的addObserver()添加观察者，setChanged()和notifyObservers()通知观察者数据变化。
    • 代码框架：
// 被观察的数据源
class DataSource extends Observable {
    private ArrayList<YourDataModel> dataList;

    public DataSource() {
        dataList = new ArrayList<>();
    }

    public ArrayList<YourDataModel> getDataList() {
        return dataList;
    }

    public void updateData() {
        // 数据库获取和数据处理代码
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT column1, column2, COUNT(*) FROM table1 " +
                "JOIN table2 ON table1.id = table2.table1_id " +
                "GROUP BY column1, column2", null);
        dataList.clear();
        if (cursor.moveToFirst()) {
            do {
                YourDataModel model = new YourDataModel();
                model.setColumn1(cursor.getString(cursor.getColumnIndex("column1")));
                model.setColumn2(cursor.getString(cursor.getColumnIndex("column2")));
                model.setCount(cursor.getInt(cursor.getColumnIndex("COUNT(*)")));
                dataList.add(model);
            } while (cursor.moveToNext());
        }
        cursor.close();
        setChanged();
        notifyObservers();
    }
}

// 观察者（Activity或Fragment）
class YourActivity extends AppCompatActivity implements Observer {
    private DataSource dataSource;
    private RecyclerView recyclerView;
    private YourAdapter adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        recyclerView = findViewById(R.id.recyclerView);
        recyclerView.setLayoutManager(new LinearLayoutManager(this));
        dataSource = new DataSource();
        dataSource.addObserver(this);
        dataSource.updateData();
    }

    @Override
    public void update(Observable o, Object arg) {
        adapter = new YourAdapter(dataSource.getDataList());
        recyclerView.setAdapter(adapter);
    }
}
  1. 使用LiveData（推荐在Android架构组件中使用）：
    • 关键类：LiveData、ViewModel。
    • 关键方法：LiveData的observe()方法注册观察者，ViewModel中通过MutableLiveData来持有数据并通知变化。
    • 代码框架：
// ViewModel
import androidx.lifecycle.MutableLiveData;
import androidx.lifecycle.ViewModel;

public class YourViewModel extends ViewModel {
    private MutableLiveData<ArrayList<YourDataModel>> dataLiveData;

    public MutableLiveData<ArrayList<YourDataModel>> getDataLiveData() {
        if (dataLiveData == null) {
            dataLiveData = new MutableLiveData<>();
            loadData();
        }
        return dataLiveData;
    }

    private void loadData() {
        // 数据库获取和数据处理代码
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT column1, column2, COUNT(*) FROM table1 " +
                "JOIN table2 ON table1.id = table2.table1_id " +
                "GROUP BY column1, column2", null);
        ArrayList<YourDataModel> dataList = new ArrayList<>();
        if (cursor.moveToFirst()) {
            do {
                YourDataModel model = new YourDataModel();
                model.setColumn1(cursor.getString(cursor.getColumnIndex("column1")));
                model.setColumn2(cursor.getString(cursor.getColumnIndex("column2")));
                model.setCount(cursor.getInt(cursor.getColumnIndex("COUNT(*)")));
                dataList.add(model);
            } while (cursor.moveToNext());
        }
        cursor.close();
        dataLiveData.setValue(dataList);
    }
}

// Activity或Fragment
import androidx.lifecycle.Observer;
import androidx.lifecycle.ViewModelProvider;

public class YourActivity extends AppCompatActivity {
    private RecyclerView recyclerView;
    private YourAdapter adapter;
    private YourViewModel viewModel;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        recyclerView = findViewById(R.id.recyclerView);
        recyclerView.setLayoutManager(new LinearLayoutManager(this));
        viewModel = new ViewModelProvider(this).get(YourViewModel.class);
        viewModel.getDataLiveData().observe(this, new Observer<ArrayList<YourDataModel>>() {
            @Override
            public void onChanged(ArrayList<YourDataModel> dataList) {
                adapter = new YourAdapter(dataList);
                recyclerView.setAdapter(adapter);
            }
        });
    }
}