Android SQLite 查询外键

Question

我有两个模型

categories(id, name, slug)
places(id, name, cat_id)

当我必须将地点详细信息插入数据库时​​，我有一个类别 slug 字符串。所以我需要查询类别 ID 并将其插入到 category_id

位置

DBAdapter.java

public class DBAdapter {
    // Database and version
    private static final String DATABASE_NAME = "nomad.db";
    private static final int DATABASE_VERSION = 2;

    // Tables
    public static final String TABLE_CATEGORIES = "categories";
    public static final String TABLE_PLACES = "places";
    public static final String TABLE_PHOTOS = "photos";

    // Table Columns
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_SLUG = "slug";
    public static final String COLUMN_ACTIVE = "active";

    public static final String COLUMN_DISTANCE = "distance";
    public static final String COLUMN_DAYS = "days";
    public static final String COLUMN_LAT = "latitude";
    public static final String COLUMN_LNG = "longitude";
    public static final String COLUMN_WEATHER = "weather";
    public static final String COLUMN_TODO = "todo";
    public static final String COLUMN_ABOUT = "about";

    public static final String COLUMN_URL = "url";
    public static final String COLUMN_COPY = "copyright";

    public static final String COLUMN_CAT_ID = "category_id";
    public static final String COLUMN_PLACE_ID = "place_id";

    // Table Queries
    private static final String CREATE_CATEGORIES = "CREATE TABLE categories (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, slug TEXT, active BOOLEAN);";
    private static final String CREATE_PLACES = "CREATE TABLE places (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, slug TEXT, category_id INTEGER, distance TEXT, days TEXT, latitude TEXT, longitude TEXT, weather TEXT, todo TEXT, about TEXT);";
    private static final String CREATE_PHOTOS = "CREATE TABLE photos (_id INTEGER PRIMARY KEY AUTOINCREMENT, place_id INTEGER, url TEXT, copyright TEXT);";

    private final Context context;
    private static DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase database) {
            database.execSQL(CREATE_CATEGORIES);
            database.execSQL(CREATE_PLACES);
            database.execSQL(CREATE_PHOTOS);
        }

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

    }

    public DBAdapter open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

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

    // Methods

    // Categories

    // Insert Category
    public long insertCategory(String name, String slug) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(COLUMN_NAME, name);
        initialValues.put(COLUMN_SLUG, name);
        initialValues.put(COLUMN_ACTIVE, true);
        return db.insert(TABLE_CATEGORIES, null, initialValues);
    }

    // Get Category ID by Slug
    public int find_city_id(String slug) {
        Cursor c = db.query(TABLE_CATEGORIES, new String[] { COLUMN_ID }, COLUMN_SLUG + "=" + "'" + slug + "'", null, null, null, null, null);
        if (c.moveToFirst()) {
            do{
                return Integer.parseInt(c.getString(0));
            } while (c.moveToNext());
        }
        else
            return -1;
    }


    // Places
    public long insertPlace(String name, String slug, String distance,
            String days, String latitude, String longitude, String weather,
            String todo, String about, int category_id) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(COLUMN_NAME, name);
        initialValues.put(COLUMN_SLUG, slug);
        initialValues.put(COLUMN_DISTANCE, distance);
        initialValues.put(COLUMN_DAYS, days);
        initialValues.put(COLUMN_LAT, latitude);
        initialValues.put(COLUMN_LNG, longitude);
        initialValues.put(COLUMN_WEATHER, weather);
        initialValues.put(COLUMN_TODO, todo);
        initialValues.put(COLUMN_ABOUT, about);
        return db.insert(TABLE_PLACES, null, initialValues);
    }

}

Answer 1

首先创建列级约束。

 final String CREATE_TABLE_STATE="CREATE TABLE booking_delivery ("
    + "id                INTEGER ,"   
    +"remark             TEXT, "
    +"barcode           INTEGER   REFERENCES customer_info(cust_barcode));";