android - E/SQLiteLog﹕ (1) near "TABLEaccount": syntax error

Question

当我尝试写入数据库时​​，我的应用程序出现此错误E/SQLiteLog:(1) near "TABLEaccount": 语法错误

我不明白为什么？这是我的 SQLite 数据库代码。我在这里附上数据库类的完整代码

包 com.astrolabe.tcpremote1;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.sql.SQLDataException;
import java.sql.SQLException;

public class accountsDB {

    //private variables
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CTYPE = "c_type";
    public static final String KEY_SNAME = "s_name";
    public static final String KEY_SNUMB = "s_numb";
    public static final String KEY_USRN = "user_name";
    public static final String KEY_PASS = "password";
    public static final String KEY_EMPTY = "empty";

    private static final String DATABASE_NAME ="account.db";
    private static final String DATABASE_TABLE ="accounts";
    private static final int DATABASE_VERSION =1;

    private DbHelper  ourHelper;
    private final Context  ourContext;
    private SQLiteDatabase  ourDatabase;

    public long createEntry(String ctype, String siteName, String siteNum, String username, String pass) {

        ContentValues cv= new ContentValues();
        cv.put(KEY_CTYPE,ctype);
        cv.put(KEY_SNAME,siteName);
        cv.put(KEY_SNUMB,siteNum);
        cv.put(KEY_USRN,username);
        cv.put(KEY_PASS,pass);
        //cv.put(KEY_EMPTY,0);
        return ourDatabase.insert(DATABASE_TABLE,null,cv);
    }

    private static class DbHelper extends SQLiteOpenHelper {
         public DbHelper(Context context){
             super(context,DATABASE_NAME,null,DATABASE_VERSION);

         }

         @Override
         public void onCreate(SQLiteDatabase db) {
             db.execSQL("CREATE TABLE"+ DATABASE_NAME + "(" +
                KEY_ROWID + "INTEGER PRIMARY KEY AUTOINCREMENT"
                     + KEY_CTYPE + "TEXT NOT NULL,"
                     +  KEY_SNAME + "TEXT NOT NULL,"
                     +  KEY_SNUMB + "TEXT NOT NULL,"
                     +  KEY_USRN + "TEXT NOT NULL,"
                     +  KEY_PASS + "TEXT NOT NULL,"
                     +  KEY_EMPTY + "INTEGER);"
                );

         }

         @Override
         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
             db.execSQL("DROP TABLE IF EXISTS" +DATABASE_TABLE);
             onCreate(db);

         }
     }

    public accountsDB (Context c)
    {
        ourContext= c;
    }
    public accountsDB open() throws SQLException
    {
        ourHelper = new DbHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return  this;
    }
    public  void close()
    {
        ourHelper.close();
    }

  }

Answer 1

您需要在 SQL 标记之间添加空格，例如而不是

db.execSQL("CREATE TABLE"+ DATABASE_NAME + "(" +

改为

db.execSQL("CREATE TABLE "+ DATABASE_TABLE + "(" +

注意 TABLE 之后的空格。我还将表名从 account.db 更改为 accounts。

这就是导致异常的原因。但还有其他问题，例如列规范

KEY_ROWID + "INTEGER PRIMARY KEY AUTOINCREMENT"
    + KEY_CTYPE + "TEXT NOT NULL,"
    +  KEY_SNAME + "TEXT NOT NULL,"
    +  KEY_SNUMB + "TEXT NOT NULL,"
    +  KEY_USRN + "TEXT NOT NULL,"
    +  KEY_PASS + "TEXT NOT NULL,"
    +  KEY_EMPTY + "INTEGER);"

您需要在列名和数据类型之间使用空格，并且在每个列规范之后使用逗号 ,:

KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
    + KEY_CTYPE + " TEXT NOT NULL,"
    +  KEY_SNAME + " TEXT NOT NULL,"
    +  KEY_SNUMB + " TEXT NOT NULL,"
    +  KEY_USRN + " TEXT NOT NULL,"
    +  KEY_PASS + " TEXT NOT NULL,"
    +  KEY_EMPTY + " INTEGER);"

此外，onUpgrade() 中也存在类似的空格问题:

db.execSQL("DROP TABLE IF EXISTS" +DATABASE_TABLE);

应该是

db.execSQL("DROP TABLE IF EXISTS " +DATABASE_TABLE);

为了更轻松地自行调试，请查看异常堆栈跟踪以找到发生异常的确切代码行。