我正在将锻炼记录应用程序构建为一个项目。我正在尝试将锻炼名称插入数据库。在提示用户然后在其中添加练习之前。目前，它显示“出了点问题”错误，然后应用崩溃。
以下是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活动
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();
}
}
最佳答案
您的问题是您已经用COL2定义了public static final String COL2 = "Workout Name";
“锻炼”和“名称”之间的空格将引起诸如此类的问题，但有时不会引起诸如在创建表时的问题。
即列将是Workout，列类型将是名称TEXT，因为它包含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是Excerise Name。
关于java - database.sqlite.SQLiteException，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49729894/