多Activity场景下数据库查询实现问询及Android SQLite球员入库代码解析

我来帮你梳理下在不同Android Activity里实现SQLite查询的靠谱方案，结合你已经写好的入库代码，咱们一步步来：

核心原则是复用数据库实例，避免重复创建连接——如果每个Activity都new一个数据库实例，容易导致连接泄漏、数据不一致，甚至ANR问题。下面是具体实现步骤：

1. 先把你的数据库帮助类改成单例模式 #

把FootySortItDatabase改成单例，让所有Activity共享同一个数据库实例，避免资源浪费：

public class FootySortItDatabase extends SQLiteOpenHelper {
    // 单例实例，volatile保证多线程可见性
    private static volatile FootySortItDatabase INSTANCE;
    private static final String DATABASE_NAME = "FootySortIt.db";
    private static final int DATABASE_VERSION = 1;

    // 私有构造，禁止外部直接new
    private FootySortItDatabase(Context context) {
        // 用Application Context，避免持有Activity导致内存泄漏
        super(context.getApplicationContext(), DATABASE_NAME, null, DATABASE_VERSION);
    }

    // 获取单例的全局方法
    public static FootySortItDatabase getInstance(Context context) {
        if (INSTANCE == null) {
            synchronized (FootySortItDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = new FootySortItDatabase(context);
                }
            }
        }
        return INSTANCE;
    }

    // 你的表创建、升级逻辑保留原实现即可
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PLAYER_TABLE = "CREATE TABLE " + PlayerDetails.PlayerTableEntry.TABLE_NAME + " ("
                + PlayerDetails.PlayerTableEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME + " TEXT NOT NULL)";
        db.execSQL(CREATE_PLAYER_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 这里写你的数据库升级逻辑，比如删表重建
        db.execSQL("DROP TABLE IF EXISTS " + PlayerDetails.PlayerTableEntry.TABLE_NAME);
        onCreate(db);
    }
}

2. 在数据库类中封装查询方法 #

把查询逻辑统一放在数据库类里，所有Activity直接调用就行，不用重复写SQL：

// 在FootySortItDatabase中添加以下方法
// 查询所有球员
public List<PlayerDetails> getAllPlayers() {
    List<PlayerDetails> playerList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    String[] projection = {
            PlayerDetails.PlayerTableEntry._ID,
            PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME
            // 这里加你需要的其他字段
    };

    Cursor cursor = db.query(
            PlayerDetails.PlayerTableEntry.TABLE_NAME,
            projection,
            null, // 查询条件，null表示查所有
            null, // 条件参数
            null,
            null,
            null
    );

    if (cursor.moveToFirst()) {
        do {
            PlayerDetails player = new PlayerDetails();
            player.id = cursor.getInt(cursor.getColumnIndexOrThrow(PlayerDetails.PlayerTableEntry._ID));
            player.name = cursor.getString(cursor.getColumnIndexOrThrow(PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME));
            // 其他字段赋值
            playerList.add(player);
        } while (cursor.moveToNext());
    }
    // 必须关闭Cursor，避免资源泄漏
    cursor.close();
    return playerList;
}

// 按名字查询单个球员（示例）
public PlayerDetails getPlayerByName(String playerName) {
    SQLiteDatabase db = this.getReadableDatabase();
    String[] projection = {
            PlayerDetails.PlayerTableEntry._ID,
            PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME
    };
    String selection = PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME + " = ?";
    String[] selectionArgs = { playerName };

    Cursor cursor = db.query(
            PlayerDetails.PlayerTableEntry.TABLE_NAME,
            projection,
            selection,
            selectionArgs,
            null,
            null,
            null
    );

    PlayerDetails player = null;
    if (cursor.moveToFirst()) {
        player = new PlayerDetails();
        player.id = cursor.getInt(cursor.getColumnIndexOrThrow(PlayerDetails.PlayerTableEntry._ID));
        player.name = cursor.getString(cursor.getColumnIndexOrThrow(PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME));
    }
    cursor.close();
    return player;
}

3. 在任意Activity中调用查询 #

比如在你的第二个Activity里，这样获取并展示球员数据：

public class PlayerListActivity extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_player_list);

        // 获取数据库单例实例
        FootySortItDatabase db = FootySortItDatabase.getInstance(this);
        
        // 注意：数据库操作不能在主线程！这里用AsyncTask举例（Android 11+可以用Executor或Coroutine）
        new AsyncTask<Void, Void, List<PlayerDetails>>() {
            @Override
            protected List<PlayerDetails> doInBackground(Void... voids) {
                // 后台线程执行查询
                return db.getAllPlayers();
            }

            @Override
            protected void onPostExecute(List<PlayerDetails> players) {
                // 主线程更新UI，比如把数据显示到RecyclerView
                updatePlayerListUI(players);
            }
        }.execute();
    }

    private void updatePlayerListUI(List<PlayerDetails> players) {
        // 这里写你的UI更新逻辑，比如设置RecyclerView的Adapter
    }
}

4. 调整你已有的入库代码 #

把原来的入库逻辑改成用单例实例，还可以加事务提升批量插入的效率：

public void addPlayerToTheDatabase(PlayerList players){
    if(players == null){
        return;
    }
    // 获取单例数据库实例
    FootySortItDatabase gameDatabase = FootySortItDatabase.getInstance(this);
    SQLiteDatabase playerDatabase = gameDatabase.getWritableDatabase();
    
    // 开启事务，批量插入更高效且保证数据一致性
    playerDatabase.beginTransaction();
    try {
        for(PlayerDetails i : players.myPlayers){
            addNewPlayer(playerDatabase, i);
        }
        playerDatabase.setTransactionSuccessful();
    } finally {
        playerDatabase.endTransaction();
    }
}

// 修改addNewPlayer方法，传入数据库实例
private long addNewPlayer(SQLiteDatabase db, PlayerDetails player) {
    ContentValues cv = new ContentValues();
    cv.put(PlayerDetails.PlayerTableEntry.COLUMN_PLAYER_NAME, player.name);
    // 其他字段赋值
    return db.insert(PlayerDetails.PlayerTableEntry.TABLE_NAME, null, cv);
}

关键注意事项 #

• 绝对不要在主线程执行数据库操作：Android 8.0+会触发ANR，必须用后台线程处理
• 记得关闭Cursor：每次查询后一定要调用cursor.close()，避免资源泄漏
• 用Application Context：单例中不要持有Activity的Context，防止内存泄漏
• 批量操作开事务：像批量插入这种场景，开启事务能大幅提升效率，还能避免部分插入失败的情况

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