SQLite语法错误代码1，尝试修复但修复无济于事-解网

问：

构建成功，但当我尝试通过以下方式打开数据库时，应用程序崩溃并附在下面附加错误myDataBaseManager.openDataBase()

有一个崩溃日志：

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

并且有一个类是数据库的构造：

   
    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;
    }
}

此外，还有一个类的用法为：myDataBaseManager.openDataBase()

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();
    }

我在搜索数据库语法错误或其他类错误时检查了几次代码，但没有找到任何东西。

附加信息：虚拟设备：Pixel 6 pro API 33

java sqlite 崩溃语法错误

0赞 forpas 1/17/2023

您没有发布引发错误的代码。

0赞 The NXP 1/17/2023

是的。我的错。现在附上了更多崩溃的解释。

答：

0赞 Bgod 1/17/2023 #1

具体错误是“myTableOfClothes”附近的语法不正确。您似乎正在尝试使用该名称创建表，但它不存在于您共享的代码中，表名似乎拼写错误，或者它不是您尝试创建的正确表名。

请检查您尝试创建的表的名称，并检查您是否使用了正确的 SQL 语句来创建表。在共享的代码中，该表在 MyDataBaseContract 类中称为“clothesMyTable”，创建该表的语句称为 CLOTHE_TABLE_STRUCTURE。

此外，请确保尚未创建该表，并且您正在尝试再次创建它，如果是这样，则应先删除它，然后再使用 DROP_CLOTHE_TABLE 语句再次创建它。

同样重要的是要注意，如果您在 SQL 语句中使用硬编码字符串文字，请确保使用正确的表名和列名。

public static final String CLOTHE_TABLE_NAME = “clothesMyTable”;

public static final String CLOTHE_TABLE_STRUCTURE = “CREATE TABLE IF NOT EXISTS ” + CLOTHE_TABLE_NAME + “ （” + CLOTHE_ID + “整数主键”，+ CLOTHE_COLOUR + “文本”，+ CLOTHE_WARMTH + “文本”，+ CLOTHE_TYPE + “文本”，+ CLOTHE_NAME + “文本”，+ CLOTHE_IMAGE_INDEX + “文本”，+ CLOTHE_OWNER_ID + “ 文本）”;

此外，还需要更新代码中所有使用的表名称，以匹配正确的表名称。

此外，请确保您使用的是正确的 SQL

0赞 The NXP 1/17/2023

数据库本身的结构是正确的。此外，在代码的每个位置，我都使用对具有特定表名称的常量的引用，以避免混淆。同时，使用名称和表专门通过MyDataBaseManager类，sqlite安装在当前版本中。所以我不明白问题出在哪里。即使重命名表中的列，问题也不会消失。此外，在我看来，如果数据库本身的结构存在问题，则会在构建时给出错误。

上一个：我无法使用 BotUser 类从我的数据库中获取数据 + 我不知道如何遍历数据库行

下一个：如何查找位置处的PostgreSQL语法错误（使用Eclipse）

SQLite语法错误代码1，尝试修复但修复无济于事

SQLite syntax error code 1, tried to fix but fixes doesn't helped

评论

评论