java - 如果数据存在于其他表中则插入到表中

Question

我使用 sqlite 在我的 Android 应用程序中记录数据。我有AP表和阅读表。我只想将表 AP 中存在的数据插入到表 Readings 中。

我现在的输出是这样的:

[{"building_id":"test",
"readings":[{"name":"B","routers":{"b4:e9:b0:58:24:80":"@wifi.id","5a:e3:47:cd:34:87":"yeygygegehe","8c:e1:17:9e:21:ec":"UKM CENTER","54:78:1a:f3:24:80":"@wifi.id","54:78:1a:f3:24:82":"seamless@wifi.id","50:c7:bf:6e:ed:35":"Teh Sosro","30:0c:23:c8:6d:ac":"","74:b5:7e:f8:23:64":"Intra Kentungan","50:06:04:7a:68:b0":"@wifi.id","40:ee:dd:7a:f1:68":"Mediapro Banteng","9c:ad:97:36:b4:57":"DESKTOP-R93MVG7 6315","10:c1:72:f3:1c:e4":"CHATUCHA","b4:e9:b0:58:24:82":"seamless@wifi.id","50:64:2b:8b:c7:4c":"Logitech","50:06:04:7a:68:b2":"seamless@wifi.id","b4:75:0e:dd:97:5e":"CLEON @AP LT2 UTARA"},
"values":{"b4:e9:b0:58:24:80":-47,"5a:e3:47:cd:34:87":-41,"8c:e1:17:9e:21:ec":-61,"54:78:1a:f3:24:80":-56,"54:78:1a:f3:24:82":-55,"50:c7:bf:6e:ed:35":-73,"30:0c:23:c8:6d:ac":-50,"74:b5:7e:f8:23:64":-69,"50:06:04:7a:68:b0":-35,"40:ee:dd:7a:f1:68":-79,"9c:ad:97:36:b4:57":-31,"10:c1:72:f3:1c:e4":-79,"b4:e9:b0:58:24:82":-47,"50:64:2b:8b:c7:4c":-54,"50:06:04:7a:68:b2":-35,"b4:75:0e:dd:97:5e":-78}},
"friendly_wifis":[{"BSSID":"9c:ad:97:36:b4:57","SSID":"DESKTOP-R93MVG7 6315"},{"BSSID":"50:06:04:7a:68:b2","SSID":"seamless@wifi.id"},{"BSSID":"5a:e3:47:cd:34:87","SSID":"yeygygegehe"}]}]

我希望它是这样的:

[{"building_id":"test",
"readings":[{"name":"B","routers":{"5a:e3:47:cd:34:87":"yeygygegehe","54:78:1a:f3:24:82":"seamless@wifi.id","9c:ad:97:36:b4:57":"DESKTOP-R93MVG7 6315"},
"values":{"5a:e3:47:cd:34:87":-41,"54:78:1a:f3:24:82":-55,"9c:ad:97:36:b4:57":-31}},
"friendly_wifis":[{"BSSID":"9c:ad:97:36:b4:57","SSID":"DESKTOP-R93MVG7 6315"},{"BSSID":"50:06:04:7a:68:b2","SSID":"seamless@wifi.id"},{"BSSID":"5a:e3:47:cd:34:87","SSID":"yeygygegehe"}]}]

readings 是读数表，Friendly_wifis 是 AP_TABLE。如您所见，我的应用程序扫描所有现有项目。我希望它使用 mac_id 作为参数仅保存与 AP_TABLE 中的数据匹配的数据。

我正在尝试添加 dataExist 进行检查:

public boolean dataExists(String id) {
        SQLiteDatabase db = this.getReadableDatabase();
        String Query = "SELECT * FROM " + AP_TABLE + " WHERE " + "ssid ='" + id+"'";
        Cursor cursor = db.rawQuery(Query, null);
        if(cursor.getCount() <= 0){
            cursor.close();
            return false;
        }
        cursor.close();
        return true;
    }

这是添加部分

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String READINGS_CREATE = "CREATE TABLE 'readings' ('building_id' TEXT NOT NULL , "
            + "'position_id' TEXT NOT NULL , 'x' FLOAT NOT NULL, 'y' FLOAT NOT NULL, "
            + " 'ssid' TEXT NOT NULL , 'mac_id' TEXT NOT NULL , 'rssi' INTEGER NOT NULL )";

    public ArrayList<String> getPositions(String building_id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select distinct position_id from "
                        + READINGS_TABLE + " where building_id=?",
                new String[] { building_id });
        ArrayList<String> result = new ArrayList<String>();
        cursor.moveToFirst();
        while (cursor.isAfterLast() == false) {
            result.add(cursor.getString(0));
            cursor.moveToNext();
        }
        return result;
    }

    public boolean addReadings(String building_id, PositionData positionData) {
        Log.v("Just Before db : ", positionData.toString());
        deleteReading(building_id, positionData.getName());

        SQLiteDatabase db = getWritableDatabase();
        for (Map.Entry<String, Integer> e : positionData.getValues().entrySet()) {
            ContentValues cv = new ContentValues();
            cv.put("building_id", building_id);
            cv.put("position_id", positionData.getName());
            cv.put("ssid",positionData.routers.get(e.getKey()));
            cv.put("mac_id",e.getKey());
            cv.put("rssi", e.getValue());
            cv.put("x", positionData.getX());
            cv.put("y", positionData.getY());
            Log.v(e.getKey(), e.getValue().toString());
            if (dataExists(e.gotKey())) {
                db.insert(READINGS_TABLE, null, cv);

        }
        System.out.println("Adding done");
        return true;

    }

我不会在这里显示 AP 表。该代码给我错误:

FATAL EXCEPTION: main
                  Process: com.example.indoorpositioning, PID: 32217
                  java.lang.RuntimeException: Failure delivering result ResultInfo{who=null, request=0, result=-1, data=Intent { cmp=com.example.indoorpositioning/.Positions (has extras) }} to activity {com.example.indoorpositioning/com.example.indoorpositioning.Reference}: android.database.sqlite.SQLiteException: unrecognized token: "0c" (code 1): , while compiling: SELECT * FROM access_points WHERE mac_id=0c:98:38:7c:19:f1
                      at android.app.ActivityThread.deliverResults(ActivityThread.java:4179)
                      at android.app.ActivityThread.handleSendResult(ActivityThread.java:4222)
                      at android.app.ActivityThread.-wrap20(ActivityThread.java)
                      at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1581)
                      at android.os.Handler.dispatchMessage(Handler.java:102)
                      at android.os.Looper.loop(Looper.java:163)
                      at android.app.ActivityThread.main(ActivityThread.java:6238)
                      at java.lang.reflect.Method.invoke(Native Method)
                      at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:933)
                      at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:823)
                   Caused by: android.database.sqlite.SQLiteException: unrecognized token: "0c" (code 1): , while compiling: SELECT * FROM access_points WHERE mac_id=0c:98:38:7c:19:f1
                      at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                      at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                      at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                      at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                      at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                      at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                      at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                      at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
                      at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
                      at com.example.indoorpositioning.DatabaseHelper.dataExists(DatabaseHelper.java:138)
                      at com.example.indoorpositioning.DatabaseHelper.addReadings(DatabaseHelper.java:162)
                      at com.example.indoorpositioning.Reference.onActivityResult(Reference.java:144)
                      at android.app.Activity.dispatchActivityResult(Activity.java:7132)
                      at android.app.ActivityThread.deliverResults(ActivityThread.java:4175)
                      at android.app.ActivityThread.handleSendResult(ActivityThread.java:4222) 
                      at android.app.ActivityThread.-wrap20(ActivityThread.java) 
                      at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1581) 
                      at android.os.Handler.dispatchMessage(Handler.java:102) 
                      at android.os.Looper.loop(Looper.java:163) 
                      at android.app.ActivityThread.main(ActivityThread.java:6238) 
                      at java.lang.reflect.Method.invoke(Native Method) 
                      at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:933) 
                      at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:823) 

Answer 1

试试这个

   "CREATE TABLE readings " + "(building_id TEXT NOT NULL , 
                + position_id TEXT NOT NULL , x FLOAT NOT NULL, y FLOAT NOT NULL, 
                +  ssid TEXT NOT NULL , mac_id TEXT NOT NULL , rssi INTEGER NOT NULL)";

编辑

  String Query = "SELECT * FROM " + AP_TABLE + " WHERE " + "mac_id ='" + id+"'";