编译 : near "a": syntax error (code 1): , 时 android.database.sqlite.SQLiteException : INSERT INTO characters(a, b) VALUES (?,?)

Question

我创建了一个 SQLite 数据库，我试图在其中存储从一项 Activity 收集的表单数据。我不断在 logcat 中收到此错误:

04-10 00:49:00.341 8953-8953/com.miniproject32.writeup E/SQLiteLog: (1) near "Peeves": syntax error
04-10 00:49:00.351 8953-8953/com.miniproject32.writeup E/SQLiteDatabase: Error inserting Pet Peeves=Arshiya Eye Colour=Arshiya Profession=Arshiya Gender=null Purpose of the Story=Arshiya Dreams=Arshiya Age=22 Hair Colour=Arshiya Name=Arshiya 
android.database.sqlite.SQLiteException: near "Peeves": syntax error (code 1): , while compiling: INSERT INTO characters(Pet Peeves,Eye Colour,Profession,Gender,Purpose of the Story,Dreams,Age,Hair Colour,Name) VALUES (?,?,?,?,?,?,?,?,?)
 #################################################################
 Error Code : 1 (SQLITE_ERROR)
 Caused By : SQL(query) error or missing database.
    (near "Peeves": syntax error (code 1): , while compiling: INSERT INTO characters(Pet Peeves,Eye Colour,Profession,Gender,Purpose of the Story,Dreams,Age,Hair Colour,Name) VALUES (?,?,?,?,?,?,?,?,?))
 #################################################################
     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:996)
     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:561)
     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1624)
     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1496)
     at com.miniproject32.writeup.CharacterTable.createCharacter(CharacterTable.java:110)
     at com.miniproject32.writeup.S4CharacterForm.nextScreen(S4CharacterForm.java:82)
     at java.lang.reflect.Method.invoke(Native Method)
     at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288)
     at android.view.View.performClick(View.java:5716)
     at android.widget.TextView.performClick(TextView.java:10926)
     at android.view.View$PerformClick.run(View.java:22596)
     at android.os.Handler.handleCallback(Handler.java:739)
     at android.os.Handler.dispatchMessage(Handler.java:95)
     at android.os.Looper.loop(Looper.java:148)
     at android.app.ActivityThread.main(ActivityThread.java:7325)
     at java.lang.reflect.Method.invoke(Native Method)
     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)

我的数据库类是这样的:

public class CharacterTable {

public static final String KEY_ROWID = "_id";
public static final String COLUMN_NAME_NAME = "Name";
public static final String COLUMN_NAME_GENDER = "Gender";
public static final String COLUMN_NAME_AGE = "Age";
public static final String COLUMN_NAME_HAIRCOLOR = "Hair Colour";
public static final String COLUMN_NAME_EYECOLOR = "Eye Colour";
public static final String COLUMN_NAME_PROF = "Profession";
public static final String COLUMN_NAME_DREAMS = "Dreams";
public static final String COLUMN_NAME_PEEVES = "Pet Peeves";
public static final String COLUMN_NAME_PURPOSE = "Purpose of the Story";


public static final String TAG = "CharacterTable";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

private static final String DATABASE_NAME = "writeup_database";
private static final String DATABASE_TABLE = "characters";
private static final int DATABASE_VERSION = 3;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
                + COLUMN_NAME_NAME +" text not null, "
                + COLUMN_NAME_GENDER +" text not null, "
                + COLUMN_NAME_AGE +" text not null, "
                + COLUMN_NAME_HAIRCOLOR +" text not null, "
                + COLUMN_NAME_EYECOLOR +" text not null, "
                + COLUMN_NAME_PROF +" text not null, "
                + COLUMN_NAME_DREAMS +" text not null, "
                + COLUMN_NAME_PEEVES +" text not null, "
                + COLUMN_NAME_PURPOSE + " text not null);";

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {

    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "Creating DataBase: " + DATABASE_CREATE);
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }
}

/**
 * Constructor - takes the context to allow the database to be
 * opened/created
 *
 * @param ctx the Context within which to work
 */
public CharacterTable(Context ctx) {
    this.mCtx = ctx;
}

public CharacterTable open() throws SQLException {
    Log.i(TAG, "Opening DataBase Connection....");
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

public void close() {
    mDbHelper.close();
}

public long createCharacter(String name, String gender, String age, String hairColor, String eyeColor,
                            String prof, String dreams, String peeves, String purpose) throws NullPointerException {
    Log.i(TAG, "Inserting record...");
    ContentValues initialValues = new ContentValues();
    initialValues.put(COLUMN_NAME_NAME, name);
    initialValues.put(COLUMN_NAME_GENDER, gender);
    initialValues.put(COLUMN_NAME_AGE, age);
    initialValues.put(COLUMN_NAME_HAIRCOLOR, hairColor);
    initialValues.put(COLUMN_NAME_EYECOLOR, eyeColor);
    initialValues.put(COLUMN_NAME_PROF, prof);
    initialValues.put(COLUMN_NAME_DREAMS, dreams);
    initialValues.put(COLUMN_NAME_PEEVES, peeves);
    initialValues.put(COLUMN_NAME_PURPOSE, purpose);

    return mDb.insert(DATABASE_TABLE, null, initialValues);
}

public boolean deleteCharacter(long rowId) {

    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

public Cursor fetchAllCharacters() {

    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, COLUMN_NAME_NAME,
            COLUMN_NAME_GENDER, COLUMN_NAME_AGE, COLUMN_NAME_HAIRCOLOR, COLUMN_NAME_EYECOLOR,
            COLUMN_NAME_PROF, COLUMN_NAME_DREAMS, COLUMN_NAME_PEEVES, COLUMN_NAME_PURPOSE}, null, null, null, null, null);
}

public Cursor fetchCharacter(long charId) throws SQLException {

    Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, COLUMN_NAME_NAME,
                            COLUMN_NAME_GENDER, COLUMN_NAME_AGE, COLUMN_NAME_HAIRCOLOR, COLUMN_NAME_EYECOLOR,
                            COLUMN_NAME_PROF, COLUMN_NAME_DREAMS, COLUMN_NAME_PEEVES, COLUMN_NAME_PURPOSE},
                            KEY_ROWID + "=" + charId, null, null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

public boolean updateCharacter(int charId, String name, String gender, String age, String hairColor, String eyeColor,
                               String prof, String dreams, String peeves, String purpose) {
    ContentValues args = new ContentValues();
    args.put(COLUMN_NAME_NAME, name);
    args.put(COLUMN_NAME_GENDER, gender);
    args.put(COLUMN_NAME_AGE, age);
    args.put(COLUMN_NAME_HAIRCOLOR, hairColor);
    args.put(COLUMN_NAME_EYECOLOR, eyeColor);
    args.put(COLUMN_NAME_PROF, prof);
    args.put(COLUMN_NAME_DREAMS, dreams);
    args.put(COLUMN_NAME_PEEVES, peeves);
    args.put(COLUMN_NAME_PURPOSE, purpose);

    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + charId, null) > 0;
}
}

我的 Activity 是这样的:

public class S4CharacterForm extends AppCompatActivity {

TextView t1, t2, t3, t4, t5, t6, t7, t8, t9;
EditText e1, e3, e4, e5, e6, e7, e8, e9;
RadioGroup rg;
RadioButton r2;

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

    ImageView imageView = (ImageView) findViewById(R.id.img_s3);
    Glide.with(this).load(R.drawable.banner_s3).into(imageView);

    Typeface tf = Typeface.createFromAsset(getAssets(), "font.ttf");
    t1 = (TextView) findViewById(R.id.name);
    t1.setTypeface(tf);
    t2 = (TextView) findViewById(R.id.gender);
    t2.setTypeface(tf);
    t3 = (TextView) findViewById(R.id.age);
    t3.setTypeface(tf);
    t4 = (TextView) findViewById(R.id.hair_c);
    t4.setTypeface(tf);
    t5 = (TextView) findViewById(R.id.eye_c);
    t5.setTypeface(tf);
    t6 = (TextView) findViewById(R.id.prof);
    t6.setTypeface(tf);
    t7 = (TextView) findViewById(R.id.dreams);
    t7.setTypeface(tf);
    t8 = (TextView) findViewById(R.id.peeves);
    t8.setTypeface(tf);
    t9 = (TextView) findViewById(R.id.purpose);
    t9.setTypeface(tf);

    e1 = (EditText) findViewById(R.id.name_ip);
    e3 = (EditText) findViewById(R.id.age_ip);
    e4 = (EditText) findViewById(R.id.hair_c_ip);
    e5 = (EditText) findViewById(R.id.eye_c_ip);
    e6 = (EditText) findViewById(R.id.prof_ip);
    e7 = (EditText) findViewById(R.id.dreams_ip);
    e8 = (EditText) findViewById(R.id.peeves_ip);
    e9 = (EditText) findViewById(R.id.purpose_ip);

    rg = (RadioGroup) findViewById(R.id.gender_ip);
    int selected_ID = rg.getCheckedRadioButtonId();
    r2 = (RadioButton) findViewById(selected_ID);

}


public void nextScreen(View view) {
    String radioText = null;
    CharacterTable charTable = new CharacterTable(this);
    charTable.open();
    try {
        radioText = r2.getText().toString();
    } catch (Exception e) {
        e.printStackTrace();
        Log.i(TAG, "Radio Button Null Exception");
    }
    long rowID = charTable.createCharacter(e1.getText().toString(), radioText, e3.getText().toString(),
            e4.getText().toString(), e5.getText().toString(), e6.getText().toString(), e7.getText().toString(),
            e8.getText().toString(), e9.getText().toString());

    if (rowID != -1)
        Toast.makeText(S4CharacterForm.this, "Character Created", Toast.LENGTH_SHORT).show();
    else
        Toast.makeText(S4CharacterForm.this, "Something went wrong", Toast.LENGTH_SHORT).show();
    charTable.close();
    Intent intent = new Intent(S4CharacterForm.this, S5.class);
    startActivity(intent);
}
}

我在这里查看了一堆类似的问题，但找不到有效的解决方案。有人知道我应该更正什么吗？

编辑

像这样更改列的名称:

public static final String KEY_ROWID = "_id";
public static final String COLUMN_NAME_NAME = "name";
public static final String COLUMN_NAME_GENDER = "gender";
public static final String COLUMN_NAME_AGE = "age";
public static final String COLUMN_NAME_HAIRCOLOR = "hair_colour";
public static final String COLUMN_NAME_EYECOLOR = "eye_colour";
public static final String COLUMN_NAME_PROF = "profession";
public static final String COLUMN_NAME_DREAMS = "dreams";
public static final String COLUMN_NAME_PEEVES = "pet_peeves";
public static final String COLUMN_NAME_PURPOSE = "purpose";

logcat 显示一个新错误:

04-12 01:08:25.644 32644-32644/com.miniproject32.writeup E/SQLiteLog: (1) table characters has no column named hair_colour
04-12 01:08:25.644 32644-32644/com.miniproject32.writeup E/SQLiteDatabase: Error inserting hair_colour=arshiya profession=arshiya eye_colour=arshiya age=22 dreams=arshiya name=arshiya  gender=null pet_peeves=arshiya purpose=arshiya
android.database.sqlite.SQLiteException: table characters has no column named hair_colour (code 1): , while compiling: INSERT INTO characters(hair_colour,profession,eye_colour,age,dreams,name,gender,pet_peeves,purpose) VALUES (?,?,?,?,?,?,?,?,?)

#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(table characters has no column named hair_colour (code 1): , while compiling: INSERT INTO characters(hair_colour,profession,eye_colour,age,dreams,name,gender,pet_peeves,purpose) VALUES (?,?,?,?,?,?,?,?,?))

#################################################################

Answer 1

这可能与您的列名中有空格有关。确保您的列名称不包含空格，使用下划线分隔单词。