安卓SQLite插入用户输入时应用崩溃，请求排查解决

问题描述 #

开发安卓应用时，点击SAVE按钮将输入的名称和数字存入SQLite数据库（infinitecounter.db），但点击后应用崩溃。以下是相关代码及排查分析：

数据库操作类代码 #

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Toast;

import androidx.annotation.Nullable;

class database1 extends SQLiteOpenHelper {
    private Context context;
    private static final String DATABASE_NAME= "infinitecounter.db";
    private static final int DATABASE_VERSION= 1;

    private static final String TABLE_NAME = "infinitecounter list";
    private static final String COLUMN_ID = "id";
    private static final String COUNTER_NAME = "title";
    private static final String CURRENT_CLICKS = ("Current Clicks"); // fix later

    public database1(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query =
                "CREATE TABLE " + TABLE_NAME +
                        "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        COUNTER_NAME + " TEXT, " +
                        CURRENT_CLICKS + " INTEGER);";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    void addinfincounter(String title, int current_clicks){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv =  new ContentValues();

        cv.put(COUNTER_NAME, title);
        cv.put(CURRENT_CLICKS, current_clicks);
        long results = db.insert(TABLE_NAME, null, cv);
        if(results == -1){
            Toast.makeText(context, "FAILED!", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(context, "SUCCESS!", Toast.LENGTH_SHORT).show();
        }

    }
}

Activity调用代码 #

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;


public class infincounter_setup extends AppCompatActivity {

    EditText name, clicks;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_infincounter_setup);
    }
    public void cancel(View V){
        Intent i = new Intent(this, MainActivity.class);
        startActivity(i);
    }


    public void save(View B) {
        name = findViewById(R.id.infincounter_name);
        clicks = findViewById(R.id.clicks);
        if (name.length() == 0 || name == null) {
            Toast.makeText(this, "PLEASE ASSIGN A NAME", Toast.LENGTH_LONG).show();
        } else{
            database1 myDB = new database1(infincounter_setup.this);
            myDB.addinfincounter(name.getText().toString(), Integer.parseInt(clicks.getText().toString()));
        }
    }
}

崩溃原因分析 #

1. SQL标识符带空格导致语法错误
   表名infinitecounter list和列名Current Clicks包含空格，SQLite中未用反引号/双引号包裹的带空格标识符会被解析为多个关键字，导致创建表的SQL语句执行失败，后续插入操作时找不到表/列直接崩溃。

2. 未处理数字输入异常
   Activity中直接调用Integer.parseInt(clicks.getText().toString())，若用户输入非数字内容，会抛出NumberFormatException引发崩溃。

3. 空指针判断逻辑错误
   if (name.length() == 0 || name == null)的判断顺序错误，若name为null，调用name.length()会先触发NullPointerException。

4. 数据库资源未关闭
   addinfincounter方法中获取了可写数据库但未关闭，长期运行会导致资源泄漏，也可能引发异常。

修复方案 #

1. 修正SQL标识符命名 #

修改表名和列名，去掉空格或用反引号包裹（推荐前者，符合数据库命名规范）：

private static final String TABLE_NAME = "infinitecounter_list"; // 替换原带空格的表名
private static final String CURRENT_CLICKS = "current_clicks"; // 替换原带空格的列名

同时更新onCreate中的建表语句：

String query =
        "CREATE TABLE " + TABLE_NAME +
                "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COUNTER_NAME + " TEXT, " +
                CURRENT_CLICKS + " INTEGER);";

2. 优化Activity输入验证逻辑 #

调整空指针判断顺序，增加数字输入异常捕获：

public void save(View B) {
    name = findViewById(R.id.infincounter_name);
    clicks = findViewById(R.id.clicks);
    
    // 先判断null再判断内容长度
    if (name == null || name.getText().toString().trim().isEmpty()) {
        Toast.makeText(this, "请输入名称", Toast.LENGTH_LONG).show();
        return;
    }
    
    if (clicks == null) {
        Toast.makeText(this, "请输入点击数", Toast.LENGTH_LONG).show();
        return;
    }
    
    String clicksStr = clicks.getText().toString().trim();
    if (clicksStr.isEmpty()) {
        Toast.makeText(this, "点击数不能为空", Toast.LENGTH_LONG).show();
        return;
    }
    
    int clickCount;
    try {
        clickCount = Integer.parseInt(clicksStr);
    } catch (NumberFormatException e) {
        Toast.makeText(this, "请输入有效的数字", Toast.LENGTH_LONG).show();
        return;
    }
    
    database1 myDB = new database1(infincounter_setup.this);
    myDB.addinfincounter(name.getText().toString().trim(), clickCount);
}

3. 关闭数据库资源 #

在addinfincounter方法中操作完成后关闭数据库：

void addinfincounter(String title, int current_clicks){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv =  new ContentValues();

    cv.put(COUNTER_NAME, title);
    cv.put(CURRENT_CLICKS, current_clicks);
    long results = db.insert(TABLE_NAME, null, cv);
    if(results == -1){
        Toast.makeText(context, "保存失败!", Toast.LENGTH_SHORT).show();
    } else {
        Toast.makeText(context, "保存成功!", Toast.LENGTH_SHORT).show();
    }
    db.close(); // 关闭数据库
}

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