syntax-error - Android SQLiteDatabase插入语法错误

Question

我四处搜寻，发现有类似问题的人，但问题并不完全相同。我收到一个SQLite语法错误，但是找不到错误的语法。请帮忙!

我在DatabaseAdapter.java中创建了一个SQLite数据库，如下所示:

static class DatabaseHelper extends SQLiteOpenHelper{
        private static final String DATABASE_NAME = "mealdatabase";
        private static final String TABLE_INGREDIENT = "INGREDIENTS";
        private static final int DATABASE_VERSION = 1;
        private static final String UID = "_id";
        private static final String INGREDIENT_NAME = "Ingredient Name";
        private static final String SERVING_UNITS = "Serving Units";
        private static final String SPC = "Servings Per Container";
        private static final String CREATE_TABLE_INGREDIENT = "CREATE TABLE "+TABLE_INGREDIENT+
                " ("+UID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+INGREDIENT_NAME+" VARCHAR(255), "
                +SERVING_UNITS+" VARCHAR(255), "+SPC+" VARCHAR(255));";

        private static final String DROP_TABLE_INGREDIENT = "DROP TABLE IF EXISTS "+TABLE_INGREDIENT;
        private Context context;

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.context=context;
            Message.message(context, "Constructor Called");
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            try {
                db.execSQL(CREATE_TABLE_INGREDIENT);
                Message.message(context, "onCreate Called");
            } catch (SQLException e) {
                Message.message(context, ""+e);
            }

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            try {
                Message.message(context, "onUpgrade Called");
                db.execSQL(DROP_TABLE_INGREDIENT);
                //context.deleteDatabase(DATABASE_NAME);
                onCreate(db);
            } catch (SQLException e) {
                Message.message(context, ""+e);
            }

        }
    } 

我正在尝试将新数据插入到现有的SQLite数据库中。该数据库已创建，没有错误。但是，当我的插入函数被调用时，出现以下语法错误:

03-06 20:44:27.559: E/SQLiteLog(968): (1) near "Per": syntax error
03-06 20:44:27.619: E/SQLiteDatabase(968): Error inserting Servings Per Container=8.0 Serving Units=oz Ingredient Name=Spaghetti Noodle

在这里，我调用NewIngredient.java中的insert函数:

public void saveIngredientData() {

        try {
            String ingredient = ingredient_editText.getText().toString();
            String servingUnits = spinner.getSelectedItem().toString();
            double servPerContainer = Double.parseDouble(SPC_editText.getText().toString());

            long id = databaseHelper.insertIngredientData(ingredient, servingUnits, servPerContainer);
            if (id < 0)
            {
                Message.message(this, "Insert was Unsuccessful");
            }
            else
                Message.message(this, "Successfully entered a row");
        } catch (NumberFormatException e) {
            Message.message(getApplicationContext(), ""+e);
        }

这是包含语法错误的insertIngredientData函数，位于DatabaseAdapter.java中:

public long insertIngredientData(String ingredientName, String units, double spc){
    SQLiteDatabase db = helper.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(DatabaseHelper.INGREDIENT_NAME, ingredientName);
    contentValues.put(DatabaseHelper.SERVING_UNITS, units);
    contentValues.put(DatabaseHelper.SPC, spc);
    long id = db.insert(DatabaseHelper.TABLE_INGREDIENT, null, contentValues);
    db.close();
    return id;
}

Answer 1

您在字段名称中使用空格。

您最好改用下划线( _ )。即:private static final String SPC = "Servings_Per_Container";。
或(如果您真的想坚持使用空格)，则必须在方括号( [和] )中包括字段名称。即:private static final String SPC = "[Servings Per Container]";。

更正所有您的字段名称，不仅是示例中的一个。