构建成功但调用openDataBase()时SQLite语法错误（code1）致App崩溃

DevOps 小满

2026-8-4

数据库打开时崩溃问题排查与解决

构建成功，但调用myDataBaseManager.openDataBase()打开数据库时App崩溃，崩溃日志如下：

FATAL EXCEPTION:
main Process: com.example.smartwardrobe, PID: 9535
java.lang.RuntimeException: Unable to resume activity
{com.example.smartwardrobe/com.example.smartwardrobe.LoginActivity}:
android.database.sqlite.SQLiteException: near "myTableOfClothes": syntax
error (code 1 SQLITE_ERROR): , while compiling: myTableOfClothes

相关代码片段

数据库构造类（MyDataBaseContract）

public final class MyDataBaseContract {
    public static final String USER_TABLE_NAME = "usersMyTable";
    public static final String USER_ID = "userID";
    public static final String USER_LOGIN = "login";
    public static final String USER_PASSWORD = "password";

    public static final String CLOTHE_TABLE_NAME = "clothesMyTable";
    public static final String CLOTHE_COLOUR = "clothe_Colour";
    public static final String CLOTHE_WARMTH = "clotheWarmth";
    public static final String CLOTHE_TYPE = "clotheType";
    public static final String CLOTHE_NAME = "clotheName";
    public static final String CLOTHE_IMAGE_INDEX = "clotheImageIndex";
    public static final String CLOTHE_OWNER_ID = "ownerId";
    public static final String CLOTHE_ID = "clotheID";

    public static final String DATABASE_NAME = "wardrobeDatabase.db";
    public static final int DB_VERSION = 1;

    public static final String USER_TABLE_STRUCTURE = "CREATE TABLE IF NOT EXISTS " +
            USER_TABLE_NAME + " (" + USER_ID + " INTEGER PRIMARY KEY," + USER_LOGIN + " TEXT," +
            USER_PASSWORD + " TEXT)";
    public static final String CLOTHE_TABLE_STRUCTURE = "CREATE TABLE IF NOT EXISTS " +
            CLOTHE_TABLE_NAME + " (" + CLOTHE_ID + " INTEGER PRIMARY KEY," + CLOTHE_COLOUR + " TEXT," +
            CLOTHE_WARMTH + " TEXT," + CLOTHE_TYPE + " TEXT," + CLOTHE_NAME + " TEXT," +  CLOTHE_IMAGE_INDEX + " TEXT," +
            CLOTHE_OWNER_ID + " TEXT)";

    public static final String DROP_CLOTHE_TABLE = "DROP TABLE IF EXIST " + CLOTHE_TABLE_NAME;
    public static final String DROP_USER_TABLE = "DROP TABLE IF EXIST " + USER_TABLE_NAME;
}

MyDataBaseManager类

@SuppressLint("Range")
public class MyDataBaseManager {
    private Context context;
    private MyDataBaseHelper myDataBaseHelper;
    private SQLiteDatabase database;

    public MyDataBaseManager(Context context) {
        this.context = context;
        myDataBaseHelper = new MyDataBaseHelper(context);
    }

    public void openDataBase(){
        database = myDataBaseHelper.getWritableDatabase();
    }

    public void closeDataBase(){
        myDataBaseHelper.close();
    }

    public void insertUserToUserDataBase(String login, String password){
        ContentValues cv = new ContentValues();
        cv.put(MyDataBaseContract.USER_LOGIN, login);
        cv.put(MyDataBaseContract.USER_PASSWORD, password);
        database.insert(MyDataBaseContract.USER_TABLE_NAME, null, cv);
    }

    public void insertClotheToClotheDatabase(String type, String warmth, String colour,
                                             String name, int ownerId){
        ContentValues cv = new ContentValues();
        cv.put(MyDataBaseContract.CLOTHE_TYPE, type);
        cv.put(MyDataBaseContract.CLOTHE_WARMTH, warmth);
        cv.put(MyDataBaseContract.CLOTHE_COLOUR, colour);
        cv.put(MyDataBaseContract.CLOTHE_NAME, name);
        cv.put(MyDataBaseContract.CLOTHE_OWNER_ID, ownerId);
        database.insert(MyDataBaseContract.CLOTHE_TABLE_NAME, null, cv);
    }


    public ArrayList<Clothe> getAllClothesByOwnerId(int ownerId){
        Cursor cursor = database.query(MyDataBaseContract.CLOTHE_TABLE_NAME,
                null, null, null,
                null, null, null);
        ArrayList<Clothe> clothes = new ArrayList<>();

        while (cursor.moveToNext()){
            if(ownerId == cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_OWNER_ID))){
                Clothe clothe = new Clothe(
                        cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_ID)),//Clothe ID
                        cursor.getString(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_WARMTH)), //Clothe warmth
                        cursor.getString(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_TYPE)), // Clothe type
                        cursor.getString(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_COLOUR)), //Clothe colour
                        cursor.getString(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_NAME)), //Clothe name
                        cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.CLOTHE_OWNER_ID))); //Owner ID
                clothes.add(clothe);
            }

        }
        cursor.close();
        return clothes;
    }

    public boolean doesLoginExist(String login){
        Cursor cursor = database.query(MyDataBaseContract.USER_TABLE_NAME,
                null, null, null,
                null, null, null);
        while (cursor.moveToNext()){
            if(login.equals(cursor.getString(cursor.getColumnIndex(MyDataBaseContract.USER_LOGIN))))
            {
                cursor.close();
                return true;
            };
        }
        cursor.close();
        return false;
    }

    public User getUserByLogin(String login){
        if (!doesLoginExist(login)) return null;
        Cursor cursor = database.query(MyDataBaseContract.USER_TABLE_NAME,
                null, null, null,
                null, null, null);
        while (cursor.moveToNext()){
            if(login.equals(cursor.getString(cursor.getColumnIndex(MyDataBaseContract.USER_LOGIN)))){
                User user = new User(login, cursor.getString(cursor.getColumnIndex(MyDataBaseContract.USER_PASSWORD)),
                        cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.USER_ID)));
                cursor.close();
                return user;

            }
        }
        cursor.close();
        return null;
    }

    public User getUserByID(int userID){
        Cursor cursor = database.query(MyDataBaseContract.USER_TABLE_NAME,
                null, null, null,
                null, null, null);
        while (cursor.moveToNext()){
            if (userID == cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.USER_ID))){
                User user = new User(cursor.getString(cursor.getColumnIndex(MyDataBaseContract.USER_LOGIN)),
                        cursor.getString(cursor.getColumnIndex(MyDataBaseContract.USER_PASSWORD)),
                        cursor.getInt(cursor.getColumnIndex(MyDataBaseContract.USER_ID)));
                cursor.close();
                return user;
            }
        }
        return null;
    }
}

LoginActivity类（调用部分）

public class LoginActivity extends AppCompatActivity {
    private MyDataBaseManager myDataBaseManager;
    @SuppressLint("MissingInflatedId")
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_login);

        //Buttons
        Button btn_login = findViewById(R.id.btn_LoginPage_SignIn);
        Button btn_register = findViewById(R.id.btn_LoginPage_OpenRegisterDialog);
        //Edit Texts
        EditText et_login = findViewById(R.id.et_LoginPage_Login);
        EditText et_password = findViewById(R.id.et_LoginPage_Password);
        //Other
        myDataBaseManager = new MyDataBaseManager(this);
        Activity activity = this;

        btn_register.setOnClickListener(v -> startRegistration(activity, myDataBaseManager));

        btn_login.setOnClickListener(v -> {
            String login = et_login.getText().toString();
            if (myDataBaseManager.doesLoginExist(login)){
                User tempUser = myDataBaseManager.getUserByLogin(login);
                String password = et_password.getText().toString();
                if(tempUser.getPassword().equals(password)){
                    Intent intent = new Intent(activity, MainActivity.class);
                    intent.putExtra("UserID", tempUser.getUserID());
                    startActivity(intent);
                    myDataBaseManager.closeDataBase();
                    finishActivity(0);
                }
                else
                    Toast.makeText(activity, "Password is incorrect. Check it and try again", Toast.LENGTH_SHORT).show();
            }
            else Toast.makeText(activity, "User with this login does not exist. Sign up!(^_^)", Toast.LENGTH_SHORT).show();
        });
    }

    @Override
    protected void onResume() {
       super.onResume();
       myDataBaseManager.openDataBase();
    }
}

问题分析

旧数据库缓存残留：崩溃日志提到的myTableOfClothes在当前代码中不存在，说明之前的版本使用过该表名，旧数据库文件未被清除，导致SQLite尝试执行旧的建表逻辑。
DROP语句语法错误：MyDataBaseContract中的删除表语句使用了IF EXIST，正确SQL语法应为IF EXISTS，会导致数据库升级或删除表时出错。

解决方案

修复DROP语句语法：
将MyDataBaseContract中的删除语句修改为：

public static final String DROP_CLOTHE_TABLE = "DROP TABLE IF EXISTS " + CLOTHE_TABLE_NAME;
public static final String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + USER_TABLE_NAME;

清除旧数据库缓存：
- 在虚拟设备上卸载App，重新安装；
- 或通过Android Studio的Device File Explorer找到/data/data/com.example.smartwardrobe/databases/wardrobeDatabase.db文件并删除，重启App。
可选：更新数据库版本：
若后续需要修改数据库结构，将DB_VERSION的值加1（如从1改为2），触发SQLiteOpenHelper的onUpgrade方法，避免旧结构残留。

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

构建成功但调用openDataBase()时SQLite语法错误（code1）致App崩溃

相关代码片段 #

数据库构造类（MyDataBaseContract） #

MyDataBaseManager类 #

LoginActivity类（调用部分） #

问题分析 #

解决方案 #

相关代码片段

数据库构造类（MyDataBaseContract）

MyDataBaseManager类

LoginActivity类（调用部分）

问题分析

解决方案