Android Studio中不使用自增实现squad_id递增的问题

我是Android Studio新手，正在开发一款运动统计APP，使用SQLite数据库存储球员、球队（squad）和数据。squad表中每支球队可添加15名球员，添加完成后希望将squad_id递增1。该表结构如下：

squad_table_id INTEGER PRIMARY KEY AUTOINCREMENT
squad_id INTEGER
squad_name TEXT
player_name TEXT
player_no INTEGER

squad_id并非主键，且不能使用自增。我已通过DISTINCT查询展示球队，点击可查看队内所有球员。现有按钮可输入15个球员姓名，对应的数据库添加方法如下：

public class AddNewSquad extends AppCompatActivity {

    private EditText player1, player2, player3, player4, player5, player6, player7, player8, player9,
    player10, player11, player12,player13, player14, player15;
    private Button addPlayerButton;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_squad_setup);

        player1 = findViewById(R.id.player1input);
        player2 = findViewById(R.id.player2input);
        player3 = findViewById(R.id.player3input);
        player4 = findViewById(R.id.player4input);
        player5 = findViewById(R.id.player5input);
        player6 = findViewById(R.id.player6input);
        player7 = findViewById(R.id.player7input);
        player8 = findViewById(R.id.player8input);
        player9 = findViewById(R.id.player9input);
        player10 = findViewById(R.id.player10input);
        player11 = findViewById(R.id.player11input);
        player12 = findViewById(R.id.player12input);
        player13 = findViewById(R.id.player13input);
        player14 = findViewById(R.id.player14input);
        player15 = findViewById(R.id.player15input);


        addPlayers();
    }

    public void addPlayers(){
        addPlayerButton = findViewById(R.id.addPlayersButton);
        addPlayerButton.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View view) {
                int squad_increment = 0;
                MyDatabaseHelper myDB = new MyDatabaseHelper(AddNewSquad.this);
              
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player1.getText().toString().trim(), String.valueOf(1));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player2.getText().toString().trim(), String.valueOf(2));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player3.getText().toString().trim(), String.valueOf(3));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player4.getText().toString().trim(), String.valueOf(4));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player5.getText().toString().trim(), String.valueOf(5));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player6.getText().toString().trim(), String.valueOf(6));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player7.getText().toString().trim(), String.valueOf(7));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player8.getText().toString().trim(), String.valueOf(8));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player9.getText().toString().trim(), String.valueOf(9));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player10.getText().toString().trim(), String.valueOf(10));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player11.getText().toString().trim(), String.valueOf(11));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player12.getText().toString().trim(), String.valueOf(12));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player13.getText().toString().trim(), String.valueOf(13));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player14.getText().toString().trim(), String.valueOf(14));
                myDB.addtoSquad(String.valueOf(squad_increment), "My First Squad", player15.getText().toString().trim(), String.valueOf(15));
                Intent intent = new Intent(AddNewSquad.this, SavedSquads.class);
                startActivity(intent);

            }
        });
    }
}

当前问题是每次打开该Activity时，squad_increment变量都会重置为0，导致所有球员的squad_id都相同。我尝试过调整变量位置，但问题依旧，数据库更新功能正常，问题出在squad_id的递增逻辑上，请问如何确保每添加15名球员后squad_id自动递增？

核心思路是从数据库中读取当前最大的squad_id，以此为基础生成新的球队ID，而不是依赖内存变量（Activity销毁重建后内存变量会丢失）。

步骤1：在数据库工具类中添加获取最大squad_id的方法 #

在MyDatabaseHelper类中新增方法，用于查询当前已存在的最大squad_id：

public int getMaxSquadId() {
    SQLiteDatabase db = this.getReadableDatabase();
    // 使用IFNULL处理空表情况，空表时返回0
    Cursor cursor = db.rawQuery("SELECT IFNULL(MAX(squad_id), 0) FROM squad_table", null);
    int maxId = 0;
    if (cursor.moveToFirst()) {
        maxId = cursor.getInt(0);
    }
    cursor.close();
    db.close();
    return maxId;
}

步骤2：修改添加球队的逻辑 #

在AddNewSquad的点击事件中，替换原来的固定squad_increment，改为从数据库获取最大ID后加1：

@Override
public void onClick(View view) {
    MyDatabaseHelper myDB = new MyDatabaseHelper(AddNewSquad.this);
    // 获取新的球队ID：当前最大ID + 1
    int newSquadId = myDB.getMaxSquadId() + 1;

    // 批量添加15名球员，统一使用新的squadId
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player1.getText().toString().trim(), String.valueOf(1));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player2.getText().toString().trim(), String.valueOf(2));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player3.getText().toString().trim(), String.valueOf(3));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player4.getText().toString().trim(), String.valueOf(4));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player5.getText().toString().trim(), String.valueOf(5));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player6.getText().toString().trim(), String.valueOf(6));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player7.getText().toString().trim(), String.valueOf(7));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player8.getText().toString().trim(), String.valueOf(8));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player9.getText().toString().trim(), String.valueOf(9));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player10.getText().toString().trim(), String.valueOf(10));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player11.getText().toString().trim(), String.valueOf(11));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player12.getText().toString().trim(), String.valueOf(12));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player13.getText().toString().trim(), String.valueOf(13));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player14.getText().toString().trim(), String.valueOf(14));
    myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", player15.getText().toString().trim(), String.valueOf(15));

    Intent intent = new Intent(AddNewSquad.this, SavedSquads.class);
    startActivity(intent);
}

优化建议 #

• 简化重复代码：把15个EditText放入数组，用循环批量添加球员，避免冗余：

// 初始化EditText数组
EditText[] playerInputs = {player1, player2, player3, player4, player5,
                           player6, player7, player8, player9, player10,
                           player11, player12, player13, player14, player15};

// 循环添加球员
for (int i = 0; i < playerInputs.length; i++) {
    String playerName = playerInputs[i].getText().toString().trim();
    // 可添加空值判断，跳过未填写的球员
    if (!playerName.isEmpty()) {
        myDB.addtoSquad(String.valueOf(newSquadId), "My First Squad", playerName, String.valueOf(i + 1));
    }
}

• 处理空输入：添加判断逻辑，跳过未填写的球员，避免插入空数据到数据库。

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