java - 数据库.sqlite.SQLiteException

Question

我正在构建一个锻炼日志应用程序作为一个项目。我正在尝试将锻炼名称插入数据库。在提示用户添加练习之前。目前它显示“出现问题错误”，然后应用程序崩溃。

下面是Logcat中的错误

android.database.sqlite.SQLiteException: near "Name": syntax error (code 1): , while compiling: INSERT INTO workout_table(Workout Name) VALUES (?)
 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                               at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                                                                               at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                                                                               at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                               at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                                               at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                               at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
                                                                               at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
                                                                               at com.example.cormac.gymbud.DatabaseHelper.addWorkout(DatabaseHelper.java:112)
                                                                               at com.example.cormac.gymbud.AddWorkout.AddData(AddWorkout.java:65)
                                                                               at com.example.cormac.gymbud.AddWorkout.access$100(AddWorkout.java:15)
                                                                               at com.example.cormac.gymbud.AddWorkout$1.onClick(AddWorkout.java:49)
                                                                               at android.view.View.performClick(View.java:5205)
                                                                               at android.view.View$PerformClick.run(View.java:21164)
                                                                               at android.os.Handler.handleCallback(Handler.java:739)
                                                                               at android.os.Handler.dispatchMessage(Handler.java:95)

下面是我的DatabaseHelper.class

public class DatabaseHelper extends SQLiteOpenHelper {
    private static String TAG = "DatabaseHelper";

    //workout table
    public static final String TABLE_WORKOUT = "workout_table";
    public static final String COL1 = "ID";
    public static final String COL2 = "Workout Name";    
    //exercise table
    public static final String TABLE_EXERCISE ="exercise_table";
    public static final String COL3 = "ID";
    public static final String COL4 = ("Exercise Name");
    public static final String COL5 = ("Set");
    public static final String COL6 = ("Weight");
    public static final String COL7 = ("Reps");

    private static final String DATABASE_NAME = "workouts.db";
    private static final int DATABASE_VERSION = 1;

    private static final String createWorkoutTable = "CREATE TABLE " + TABLE_WORKOUT + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL2 +" TEXT)";

    private static final String createExerciseTable = "CREATE TABLE " + TABLE_EXERCISE + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL4 +" TEXT," + COL5 +" INTEGER,"+ COL6 + " INTEGER," + COL7 + "INTEGER)";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        //db.execSQL("Create table user(email text primary key, password text)");    
                db.execSQL(createWorkoutTable);    
                db.execSQL(createExerciseTable);    
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        //db.execSQL("drop table if exists user");
        //onCreate(db);

        db.execSQL("DROP IF TABLE EXISTS " + TABLE_WORKOUT);
        onCreate(db);    
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EXERCISE);
        onCreate(db);
    }

      //inserting in database
       // public boolean insert(String email, String password){
         //   SQLiteDatabase db = this.getWritableDatabase();
       // ContentValues contentValues = new ContentValues();
        //contentValues.put("email",email);
        //contentValues.put("password",password);
        //long ins = db.insert("user", null,contentValues);
        //if (ins==1) return false;
        //else return true;
    //}

    //checking if the email already exists
    //public Boolean checkEmail(String email){
      //  SQLiteDatabase db = this.getReadableDatabase();
       // Cursor cursor = db.rawQuery("Select * from user where email=?",new String[]{email});
        //if (cursor.getCount()>0) return false;
        //else return true;
    //}

    //checking the email and password
    //public Boolean emailpassword(String email, String password){
      //  SQLiteDatabase db = this.getReadableDatabase();
       // Cursor cursor = db.rawQuery("select * from user where email=? and password=?",new String[]{email,password});
       // if (cursor.getCount()>0) return true;
        //else return false;
    //}

    public boolean addWorkout(String workout){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2,workout);

        Log.d(TAG, "addData: Adding " + workout + " to" + TABLE_WORKOUT);    
        long result = db.insert(TABLE_WORKOUT, null, contentValues);    
        if (result == -1){    
            return false;
        }else{
            return true;
        }    
    }

    public boolean addExercise(String item){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(item, COL4);
        contentValues.put(item, COL5);
        contentValues.put(item, COL6);
        contentValues.put(item, COL7);

        Log.d(TAG, "addData: Adding " + item + " to" + TABLE_EXERCISE);    
        long result = db.insert(TABLE_EXERCISE, null, contentValues);    
        if (result == -1){
            return false;
        }else{
            return true;
        }
    }

    //return data from the database 
    public Cursor getDataWorkout(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_WORKOUT;
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getDataExercise(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_EXERCISE;
        Cursor data = db.rawQuery(query, null);
        return data; 
    }

    public Cursor getItemIDWorkout(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT " + COL1 + "FROM" + TABLE_WORKOUT +
                " WHERE " + COL2 + " = " + name + " ";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getItemIDExercise(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL4 + " = " + name + " ";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    public Cursor getExerciseSet(Integer set){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL5 + " = " + set + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data; 
    }

    public Cursor getExerciseWeight(Integer weight){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL6 + " = " + weight + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data;    
    }

    public Cursor getExerciseRep(Integer rep){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("SELECT " + COL3 + "FROM" + TABLE_EXERCISE +
                " WHERE " + COL7 + " = " + rep + " ");
        Cursor data = db.rawQuery(String.valueOf(query), null);
        return data;    
    }

    public void updateWorkoutName(String newName, int id, String oldName){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE " + TABLE_WORKOUT + " SET" + COL2 +
                " = " + newName + "WHERE" + COL1 + " = " + id + " " +
                " AND " + COL2 + " = " + oldName + " ";
        Log.d(TAG, "updateName: query " + query);
        Log.d(TAG, "updateName: Setting name to " + newName);
        db.execSQL(query);    
    }

    public void updateExerciseName(String newName, int id, String oldName){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE" + TABLE_EXERCISE + "SET" + COL4 +
                " = " + newName + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL4 + " = " + oldName + " ";
        Log.d(TAG, "updateName: query " + query);
        Log.d(TAG, "updateName: Setting name to" + newName);
        db.execSQL(query);
     }

    public void updateExerciseSet(int newSet, int id, String oldSet){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL5 +
                " = " + newSet + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL5 + " = " + oldSet + " ");
        Log.d(TAG, "updateSet: query " + query);
        Log.d(TAG, "updateSet: Setting name to" + newSet);
        db.execSQL(String.valueOf(query));
    }

    public void updateExerciseWeight(int newWeight, int id, String oldWeight){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL6 +
                " = " + newWeight + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL6 + " = " + oldWeight + " ");
        Log.d(TAG, "updateWeight: query " + query);
        Log.d(TAG, "updateWeight: Setting name to" + newWeight);
        db.execSQL(String.valueOf(query));
    }

    public void updateExerciseRep(int newRep, int id, String oldRep){
        SQLiteDatabase db = this.getWritableDatabase();
        Integer query = Integer.valueOf("UPDATE" + TABLE_EXERCISE + "SET" + COL7 +
                " = " + newRep + "WHERE" + COL3 + " = " + id + " " +
                " AND " + COL7 + " = " + oldRep + " ");
        Log.d(TAG, "updateRep: query " + query);
        Log.d(TAG, "updateRep: Setting name to" + newRep);
        db.execSQL(String.valueOf(query));
    }

    public void deleteWorkoutName(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_WORKOUT + " WHERE "
                + COL1 + " = '" + id + "'" +
                " AND " + COL2 + " = '" + name + "'";
        Log.d(TAG, "deleteName: query: " + query);
        Log.d(TAG, "deleteName: Deleting " + name + " from database.");
        db.execSQL(query);
    }

    public void deleteExerciseName(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL4 + " = '" + name + "'";
        Log.d(TAG, "deleteName: query: " + query);
        Log.d(TAG, "deleteName: Deleting " + name + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseSet(int id, int set){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL5 + " = '" + set + "'";
        Log.d(TAG, "deleteSet: query: " + query);
        Log.d(TAG, "deleteSet: Deleting " + set + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseWeight(int id, int Weight){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL6 + " = '" + Weight + "'";
        Log.d(TAG, "deleteWeight: query: " + query);
        Log.d(TAG, "deleteWeight: Deleting " + Weight + " from database.");
        db.execSQL(query);    
    }

    public void deleteExerciseRep(int id, int rep){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_EXERCISE + " WHERE "
                + COL3 + " = '" + id + "'" +
                " AND " + COL7 + " = '" + rep + "'";
        Log.d(TAG, "deleteRep: query: " + query);
        Log.d(TAG, "deleteRep: Deleting " + rep + " from database.");
        db.execSQL(query);    
    }    
}

这是 AddWorout Activity

public class AddWorkout extends AppCompatActivity implements View.OnClickListener {

    public static final String TAG = "AddWorkout";
    private EditText edtWorkout;
    private Button btnAddW;
    DatabaseHelper mDatabseHelper;
    private WorkoutDAO mWorkoutDOA;

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

        initViews();
        edtWorkout = (EditText) findViewById(R.id.edtWorkout);
        btnAddW = (Button) findViewById(R.id.btnAddW);
        mDatabseHelper = new DatabaseHelper(this);

        btnAddW.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                String workout = edtWorkout.getText().toString();
                if (edtWorkout.length() != 0) {
                    AddData(workout);
                    edtWorkout.setText("");
                    Intent intent = new Intent(AddWorkout.this, AddExercise.class);
                    startActivity(intent);
                } else {
                    toastMessage("You must put something in the text field!");
                }
                ;
            }
        });
    }

    private void AddData(String workout) {
        boolean insertData = mDatabseHelper.addWorkout(workout);

        if (insertData){
            toastMessage("Data Successfully Inserted");
        } else {
            toastMessage("Something went wrong");
        }
    }

    private void toastMessage(String s) {
        Toast.makeText(this,s,Toast.LENGTH_SHORT).show();
    }

    private void initViews(){

        this.edtWorkout = (EditText) findViewById(R.id.edtWorkout);
        this.btnAddW = (Button) findViewById(R.id.btnAddW);
        this.btnAddW.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {

        switch (view.getId()) {
            case R.id.btnAddW:
                Editable WorkoutName = edtWorkout.getText();

                if (!TextUtils.isEmpty(WorkoutName) ) {
                    // add the exercise to database
                    Workout createdWorkout = mWorkoutDOA.createWorkout(
                            WorkoutName.toString());

                    Log.d(TAG, "added workout : "+ createdWorkout.getName());
                    Intent intent = new Intent();
                    intent.putExtra(ListWorkoutActivity.EXTRA_ADDED_WORKOUT, (Parcelable) createdWorkout);
                    setResult(RESULT_OK, intent);
                    finish();
                }
                else {
                    Toast.makeText(this, R.string.empty_fields_message, Toast.LENGTH_LONG).show();
                }
                break;

            default:
                break;
        }
    }
    @Override
    protected void onDestroy() {
        super.onDestroy();
        mWorkoutDOA.close();
    }
}

Answer 1

您的问题是您使用 public static final String COL2 = "Workout Name"; 定义了 COL2

锻炼和名称之间的空格会导致此类问题，但有时不会导致问题，例如创建表格时。

• 即列将为Workout，列类型将为name TEXT，其类型亲和性为TEXT 因为它包含文本。 more about this at - Datatypes In SQLite Version 3

您可以将列名称更改为不包含空格，例如

public static final String COL2 = "Workout_Name"; //<<<< recommended way

或者您可以将其附上，例如使用以下其中一个:-

public static final String COL2 = "[Workout Name]"; 
public static final String COL2 = "`Workout Name`";
public static final String COL2 = "'Workout Name'";
public static final String COL2 = "\"Workout Name\"";

这些修复需要删除并重新创建表，因为当前列名称为 Workout

如果您确实想要，您可以使用public static final String COL2 = "Workout";，因为这就是当前的列名称。在这个阶段，这不需要删除并重新创建表。

注意

您可能会遇到类似的问题，因为COL4是练习名称。