"near " ) ": syntax error"创建表期间的安卓SQLite问题

Question

我正在开发应用程序，其中将来自Web API的数据存储在数据库中。我的dbhelper课程：

public class DBHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "LocalVenues.db";
    public static final int DB_VERSION = 1;
    public static final String COLUMN_ID = "_id";
    public static final String TABLE_VENUES = "venues";
    public static final String COLUMN_VENUE_NAME = "name";
    public static final String COLUMN_RATING = "rating";
    public static final String COLUMN_RATING_COLOR = "rating_color";
    public static final String COLUMN_LOCATION_ID = "location_id";
    public static final String COLUMN_PHOTO_ID = "photo_id";
    public static final String COLUMN_PHONE = "phone";

    //tips
    public static final String TABLE_TIPS = "tips";
    public static final String COLUMN_VENUE_ID = "venue_id";
    public static final String COLUMN_TIP_TEXT = "text";
    public static final String COLUMN_AUTHOR_ID = "user_id";
    //locations
    public static final String TABLE_LOCATIONS = "locations";
    public static final String COLUMN_ADDRESS = "address";
    public static final String COLUMN_LAT = "lat";
    public static final String COLUMN_LNG = "lng";

    //authors
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_FIRST_NAME = "first_name";
    public static final String COLUMN_LAST_NAME = "last_name";
    //photos
    public static final String TABLE_PHOTOS = "photos";
    public static final String COLUMN_PREFIX = "prefix";
    public static final String COLUMN_SUFFIX = "suffix";

    private static DBHelper dbHelperInstance;
    private DBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    public static synchronized DBHelper getInstance(Context context) {
        if (dbHelperInstance == null) {
            return dbHelperInstance = new DBHelper(context.getApplicationContext());
        } else {
            return dbHelperInstance;
        }
    }
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String STM_CREATE_TABLE = "CREATE TABLE ";
        String STM_PRIMARY_KEY = "PRIMARY KEY ";
        String STM_FOREIGN_KEY = " FOREIGN KEY ";
        String STM_REFERENCES = " REFERENCES ";
        String STM_TEXT = " TEXT ";
        String STM_AUTOINCREMENT = " AUTOINCREMENT, ";
        String STM_TEXT_PRIMARY_KEY = STM_TEXT + STM_PRIMARY_KEY;
        String STM_NOT_NULL = " NOT NULL, ";
        String STM_REAL = " REAL ";
        String STM_INTEGER = " INTEGER ";

        String CREATE_TABLE_VENUES = STM_CREATE_TABLE + TABLE_VENUES + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_VENUE_NAME + STM_TEXT + STM_NOT_NULL +
                COLUMN_RATING + STM_REAL + STM_NOT_NULL +
                COLUMN_RATING_COLOR + STM_TEXT + STM_NOT_NULL +
                COLUMN_LOCATION_ID + STM_INTEGER + STM_NOT_NULL +
                COLUMN_PHOTO_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_PHONE + STM_TEXT + ", " +
                STM_FOREIGN_KEY + "(" + COLUMN_LOCATION_ID + ")" + STM_REFERENCES + TABLE_LOCATIONS + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_PHOTO_ID + ")" + STM_REFERENCES + TABLE_PHOTOS + "(" + COLUMN_ID + ") "
                + ");";
        String CREATE_TABLE_LOCATIONS = STM_CREATE_TABLE + TABLE_LOCATIONS + " ( " +
                COLUMN_ID + STM_INTEGER + STM_PRIMARY_KEY + STM_AUTOINCREMENT +
                COLUMN_ADDRESS + STM_TEXT + STM_NOT_NULL +
                COLUMN_LAT + STM_REAL + STM_NOT_NULL +
                COLUMN_LNG + STM_REAL + STM_NOT_NULL
                + ");";
        String CREATE_TABLE_TIPS = STM_CREATE_TABLE + TABLE_TIPS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_VENUE_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_AUTHOR_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_TIP_TEXT + STM_TEXT + STM_NOT_NULL +
                STM_FOREIGN_KEY + "(" + COLUMN_VENUE_ID + ")" + STM_REFERENCES + TABLE_VENUES + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_AUTHOR_ID + ")" + STM_REFERENCES + TABLE_USERS + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_PHOTO_ID + ")" + STM_REFERENCES + TABLE_PHOTOS + "(" + COLUMN_ID + ") "
                + ");";
        String CREATE_TABLE_PHOTOS = STM_CREATE_TABLE + TABLE_PHOTOS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_PREFIX + STM_TEXT + STM_NOT_NULL +
                COLUMN_SUFFIX + STM_TEXT + STM_NOT_NULL
                + ");";
        String CREATE_TABLE_USERS = STM_CREATE_TABLE + TABLE_USERS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_FIRST_NAME + STM_TEXT + STM_NOT_NULL +
                COLUMN_LAST_NAME + STM_TEXT + STM_NOT_NULL +
                STM_FOREIGN_KEY + " ( " + COLUMN_PHOTO_ID + " ) " + STM_REFERENCES + TABLE_PHOTOS + " (" + COLUMN_ID + " ) "
                + ");";
        db.execSQL(CREATE_TABLE_VENUES);
        db.execSQL(CREATE_TABLE_LOCATIONS);
        db.execSQL(CREATE_TABLE_TIPS);
        db.execSQL(CREATE_TABLE_PHOTOS);
        db.execSQL(CREATE_TABLE_USERS);
    }
    //The simplest implementation of onUpgrade() method
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String STM_DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";
        if (oldVersion != newVersion) {
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_VENUES);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_LOCATIONS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_PHOTOS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_TIPS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_USERS);
        }
    }
}

我有错误：E/SQLiteLog: (1) near ")": syntax error结果，我还有另一个子问题：有没有办法使这样的sqlite错误更具信息性？

Answer 1

问题是这个

  String STM_NOT_NULL = " NOT NULL, ";

运行代码时，logcat输出为

  CREATE TABLE locations ( _id INTEGER PRIMARY KEY  AUTOINCREMENT, address TEXT  NOT NULL, lat REAL  NOT NULL, lng REAL  NOT NULL, );

lng real not null有一个额外的逗号，）

你应该摆脱那个

并进一步运行您的代码

Caused by: android.database.sqlite.SQLiteException: unknown column "photo_id" in foreign key definition (code 1): , while compiling: CREATE TABLE tips ( _id TEXT PRIMARY KEY , venue_id TEXT  NOT NULL, user_id TEXT  NOT NULL, text TEXT  NOT NULL,  FOREIGN KEY (venue_id) REFERENCES venues(_id),  FOREIGN KEY (user_id) REFERENCES users(_id),  FOREIGN KEY (photo_id) REFERENCES photos(_id) );

创建表格时，查询中缺少一些列。

我不确定您想要表结构。

所以我建议您通过查看logcat仔细查看查询。您没有发布足够的详细信息，当您的应用程序崩溃时，您应该在logcat中看到这些详细信息。

以下不会崩溃。没有完整的崩溃日志，很难发布答案。

public class DbHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "LocalVenues.db";
    public static final int DB_VERSION = 1;
    public static final String COLUMN_ID = "_id";
    public static final String TABLE_VENUES = "venues";
    public static final String COLUMN_VENUE_NAME = "name";
    public static final String COLUMN_RATING = "rating";
    public static final String COLUMN_RATING_COLOR = "rating_color";
    public static final String COLUMN_LOCATION_ID = "location_id";
    public static final String COLUMN_PHOTO_ID = "photo_id";
    public static final String COLUMN_PHONE = "phone";

    //tips
    public static final String TABLE_TIPS = "tips";
    public static final String COLUMN_VENUE_ID = "venue_id";
    public static final String COLUMN_TIP_TEXT = "text";
    public static final String COLUMN_AUTHOR_ID = "user_id";
    //locations
    public static final String TABLE_LOCATIONS = "locations";
    public static final String COLUMN_ADDRESS = "address";
    public static final String COLUMN_LAT = "lat";
    public static final String COLUMN_LNG = "lng";

    //authors
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_FIRST_NAME = "first_name";
    public static final String COLUMN_LAST_NAME = "last_name";
    //photos
    public static final String TABLE_PHOTOS = "photos";
    public static final String COLUMN_PREFIX = "prefix";
    public static final String COLUMN_SUFFIX = "suffix";

    private static DbHelper dbHelperInstance;
    public DbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    public static synchronized DbHelper getInstance(Context context) {
        if (dbHelperInstance == null) {
            return dbHelperInstance = new DbHelper(context.getApplicationContext());
        } else {
            return dbHelperInstance;
        }
    }
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String STM_CREATE_TABLE = "CREATE TABLE ";
        String STM_PRIMARY_KEY = "PRIMARY KEY ";
        String STM_FOREIGN_KEY = " FOREIGN KEY ";
        String STM_REFERENCES = " REFERENCES ";
        String STM_TEXT = " TEXT ";
        String STM_AUTOINCREMENT = " AUTOINCREMENT, ";
        String STM_TEXT_PRIMARY_KEY = STM_TEXT + STM_PRIMARY_KEY;
        String STM_NOT_NULL = " NOT NULL, ";
        String STM_REAL = " REAL ";
        String STM_INTEGER = " INTEGER ";

        String CREATE_TABLE_VENUES = STM_CREATE_TABLE + TABLE_VENUES + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_VENUE_NAME + STM_TEXT + STM_NOT_NULL +
                COLUMN_RATING + STM_REAL + STM_NOT_NULL +
                COLUMN_RATING_COLOR + STM_TEXT + STM_NOT_NULL +
                COLUMN_LOCATION_ID + STM_INTEGER + STM_NOT_NULL +
                COLUMN_PHOTO_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_PHONE + STM_TEXT + ", " +
                STM_FOREIGN_KEY + "(" + COLUMN_LOCATION_ID + ")" + STM_REFERENCES + TABLE_LOCATIONS + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_PHOTO_ID + ")" + STM_REFERENCES + TABLE_PHOTOS + "(" + COLUMN_ID + ") "
                + ");";
       String CREATE_TABLE_LOCATIONS = STM_CREATE_TABLE + TABLE_LOCATIONS + " ( " +
                COLUMN_ID + STM_INTEGER + STM_PRIMARY_KEY + STM_AUTOINCREMENT +
                COLUMN_ADDRESS + STM_TEXT + STM_NOT_NULL +
                COLUMN_LAT + STM_REAL + STM_NOT_NULL +
                COLUMN_LNG + STM_REAL + "NOT NULL"
                + ");";
        String CREATE_TABLE_PHOTOS = STM_CREATE_TABLE + TABLE_PHOTOS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_PREFIX + STM_TEXT + STM_NOT_NULL +
                COLUMN_SUFFIX + STM_TEXT + "NOT NUll"
                + ");";
        String CREATE_TABLE_USERS = STM_CREATE_TABLE + TABLE_USERS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_PHOTO_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_FIRST_NAME + STM_TEXT + STM_NOT_NULL +
                COLUMN_LAST_NAME + STM_TEXT + STM_NOT_NULL +
                STM_FOREIGN_KEY + " ( " + COLUMN_PHOTO_ID + " ) " + STM_REFERENCES + TABLE_PHOTOS + " (" + COLUMN_ID + " ) "
                + ");";
        String CREATE_TABLE_TIPS = STM_CREATE_TABLE + TABLE_TIPS + " ( " +
                COLUMN_ID + STM_TEXT_PRIMARY_KEY + ", " +
                COLUMN_PHOTO_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_VENUE_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_AUTHOR_ID + STM_TEXT + STM_NOT_NULL +
                COLUMN_TIP_TEXT + STM_TEXT + STM_NOT_NULL +
                STM_FOREIGN_KEY + "(" + COLUMN_VENUE_ID + ")" + STM_REFERENCES + TABLE_VENUES + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_AUTHOR_ID + ")" + STM_REFERENCES + TABLE_USERS + "(" + COLUMN_ID + "), " +
                STM_FOREIGN_KEY + "(" + COLUMN_PHOTO_ID + ")" + STM_REFERENCES + TABLE_PHOTOS + "(" + COLUMN_ID + ") "
                + ");";

        db.execSQL(CREATE_TABLE_VENUES);
        db.execSQL(CREATE_TABLE_LOCATIONS);
        db.execSQL(CREATE_TABLE_PHOTOS);
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_TIPS);

    }
    //The simplest implementation of onUpgrade() method
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String STM_DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";
        if (oldVersion != newVersion) {
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_VENUES);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_LOCATIONS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_PHOTOS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_TIPS);
            db.execSQL(STM_DROP_TABLE_IF_EXISTS + TABLE_USERS);
        }
    }
}

Answer 2

脱颖而出的一件事是您使用的CREATE_TABLE_LOCATIONS字符串中的STM_NOT_NULL在关闭括号之前具有逗号。这导致, lng REAL NOT NULL,); SQL语法 - 并非无效。这可能是您遇到的问题之一。您应该将所有最终字符串打印到LogCat（或在调试时查看它们），以确保格式正确。