android - SQLite 数据库更新或插入不起作用

Question

我想像这样更新我的数据库:如果行存在更新它，如果行不存在添加一个新的。在 TABLE_MAP_MARKER 中，insert 有效，但 update 无效，在 TABLE_MAP_MARKER_INFO 中，更新和插入一般都无效。我的错误在哪里？请帮助我!

下面是我的代码:

 SQLiteDatabase db = this.getWritableDatabase();
    ContentValues marker_position_values= new ContentValues();
    marker_position_values.put(KEY_MARKER_LAT, map_marker_lat_value);
    marker_position_values.put(KEY_MARKER_LNG, map_marker_long_value);

    long marker_id_table = db.update(TABLE_MAP_MARKER, marker_position_values, HOME_ID + " = ?", new String[]String.valueOf(home_id)});
    if (marker_id_table == 0) {
        db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_REPLACE);
    }
    ContentValues unit_values= new ContentValues();
    unit_values.put(HOME_ID, marker_id_table);
    unit_values.put(HOME_TYPE, home_type);      
    unit_values.put(AMOUNT_RENT, amount_rent);

    long unit_id_table = db.update(TABLE_MAP_MARKER_INFO, unit_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
    if (unit_id_table == 0) {
        db.insert WithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_REPLACE);
    }

Answer 1

一些问题

• 您的代码中有许多无关的空格，例如 :-

  • String.value Of(home_id)
  • db.insert 与 On 冲突

• 要发生冲突，必须指定约束(隐式或显式)。根本不清楚您是否定义了合适的约束，甚至这些约束应该是什么。 (下面的示例假设了约束)

• 如果 TABLE_MAP_MARKER 中没有要更新的行，则 marker_id_table 将为 0。因此假设成功插入 TABLE_MAP_MARKER，则 HOME_ID 列将使用 0 以尝试更新 TABLE_MAP_MARKER_INFO(假设标准使用 ID 列并将其定义为 INTEGER PRIMARY KEY(带或不带 AUTOINCREMENT))然后 id 为 0 将不存在，因此无法更新任何行。但是，随后可能会插入 HOME_ID 为 0 的行，然后 HOME_ID 将在两个表之间不同步。示例中使用的修复方法是使用 TABLE_MAP_MARKER_INFO 更新/插入中的 _id(即 HOME_ID 列)列的 HOME_ID 值。

工作示例:-

以下基于 UNIQUE 约束:-

• 对于纬度和经度的组合应该是唯一的MAP MARKER表
• 对于 MAP MARKER INFO 表，房屋类型和租金的组合应该是唯一的(不太可能，但其他情况会是什么)。

然后可以使用等同于创建表:-

CREATE TABLE IF NOT EXISTS map_marker(_id INTEGER PRIMARY KEY,map_marker_lat INTEGER,map_marker_lng INTEGER, UNIQUE(map_marker_lat,map_marker_lng));
CREATE TABLE IF NOT EXISTS map_marker_info(_id INTEGER PRIMARY KEY,home_type TEXT,amount_rent REAL, UNIQUE(HOME_TYPE,AMOUNT_RENT));

因此，一个明确的 UNIQUE 约束被放置在 map_marker 的 map_marker_lat 和 map_marker_lng 列上。因此，组合的两个值必须是唯一的，否则会引发冲突。所以 100,110 和 100,120 都可以，但第二个 100,110 会导致冲突。

此外，由于 PRIMARY KEY，_id 列具有隐式唯一约束(因为它是 INTEGER PRIMARY KEY，所以它也必须是整数值)。

所以 DatabaseHelper 类是(您可能有的猜测，包括基于您的代码的 addMarkerAndMarkerInfo 方法(已应用更正并添加了登录 - 请参阅注释/注释掉的行)):-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "marker";
    public static final int DBVERSION = 1;

    public static final String TABLE_MAP_MARKER = "map_marker";
    public static final String TABLE_MAP_MARKER_INFO = "map_marker_info";
    public static final String HOME_ID = BaseColumns._ID;

    public static final String KEY_MARKER_LAT = "map_marker_lat";
    public static final String KEY_MARKER_LNG = "map_marker_lng";

    public static final String HOME_TYPE = "home_type";
    public static final String AMOUNT_RENT = "amount_rent";

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtmm = "CREATE TABLE IF NOT EXISTS " + TABLE_MAP_MARKER + "(" +
                HOME_ID + " INTEGER PRIMARY KEY, " +
                KEY_MARKER_LAT + " INTEGER, " +
                KEY_MARKER_LNG + " INTEGER, " +
                " UNIQUE(" +
                KEY_MARKER_LAT + "," +
                KEY_MARKER_LNG +
                        ")" +
                ")";
        String crtmmi = "CREATE TABLE IF NOT EXISTS " + TABLE_MAP_MARKER_INFO + "(" +
                HOME_ID + " INTEGER PRIMARY KEY, " +
                HOME_TYPE + " TEXT," +
                AMOUNT_RENT + " REAL," +
                " UNIQUE(" +
                HOME_TYPE + ", " +
                AMOUNT_RENT +
                ")" +
                ")";
        db.execSQL(crtmm);
        db.execSQL(crtmmi);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public void addMarkerAndMarkerInfo(long home_id,int map_marker_lat_value, int map_marker_long_value, String home_type, double amount_rent, String attempt) {

        String TAG = "AMAMI-" + attempt;
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues marker_position_values= new ContentValues();
        marker_position_values.put(KEY_MARKER_LAT, map_marker_lat_value);
        marker_position_values.put(KEY_MARKER_LNG, map_marker_long_value);

        Log.d(TAG,"Attempting Update of MAP MARKER TABLE.");
        long marker_id_table = db.update(TABLE_MAP_MARKER, marker_position_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
        if (marker_id_table == 0) {
            Log.d(TAG,String.valueOf(marker_id_table) + " rows Updated for MAP MARKER TABLE, attempting insert");
            //long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_REPLACE);
            long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_IGNORE);
            //long insertid = db.insert(TABLE_MAP_MARKER, null, marker_position_values); //<<<< used to check what conflicts occur
            if (insertid < 0) {
                Log.d(TAG,"No row inserted into MAP MARKER TABLE.");
            } else {
                Log.d(TAG,"Row inserted into MAP MARKER TABLE.");
            }
        } else {
            Log.d(TAG,"Update of MARKER TABLE Successful (" + String.valueOf(marker_id_table) + " rows updated.)");
        }

        ContentValues unit_values= new ContentValues();
        //unit_values.put(HOME_ID, marker_id_table); ????????
        unit_values.put(HOME_ID,home_id);
        unit_values.put(HOME_TYPE, home_type);
        unit_values.put(AMOUNT_RENT, amount_rent);

        Log.d(TAG,"Attempting Update of MAP MARKER INFO TABLE.");
        long unit_id_table = db.update(TABLE_MAP_MARKER_INFO, unit_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
        if (unit_id_table == 0) {
            Log.d(TAG,String.valueOf(unit_id_table) + " rows Updated for MAP MARKER INFO TABLE, attempting insert");
            long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_IGNORE);
            //long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_REPLACE);
            //long insertid = db.insert(TABLE_MAP_MARKER_INFO, null, unit_values); //<<<< used to check what conflicts occur
            if (insertid < 0) {
                Log.d(TAG,"No row inserted into MAP MARKER INFO TABLE");
            } else {
                Log.d(TAG,"Row inserted into MAP MARKER INFO TABLE.");
            }
        }  else {
            Log.d(TAG,"Update of MARKER INFO TABLE Successful (" + String.valueOf(unit_id_table) + " rows updated.)");
        }
    }
}

• 注意 CONFLICT_REPLACE 替换为 CONFLICT_IGNORE(真的替换重复项？)

结果

然后调用上面的使用(删除任何现有行并尝试添加 4 行):-

    DatabaseHelper mDBHlp = new DatabaseHelper(this);
    mDBHlp.getWritableDatabase().delete(DatabaseHelper.TABLE_MAP_MARKER,null,null);
    mDBHlp.getWritableDatabase().delete(DatabaseHelper.TABLE_MAP_MARKER_INFO,null,null);

    mDBHlp.addMarkerAndMarkerInfo(1,100,100,"House",45.64,"Attempt 1");
    mDBHlp.addMarkerAndMarkerInfo(2,150,150,"House",65.64,"Attempt 2");
    // Duplicate
    mDBHlp.addMarkerAndMarkerInfo(3,100,100,"House",45.64,"Attempt 3");
    // Another duplicate
    mDBHlp.addMarkerAndMarkerInfo(1,100,100,"House",45.64,"Attempt 4");

结果:-

09-04 22:47:16.459 1917-1917/? D/AMAMI-Attempt 1: Attempting Update of MAP MARKER TABLE.
    0 rows Updated for MAP MARKER TABLE, attempting insert
09-04 22:47:16.463 1917-1917/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER TABLE.
    Attempting Update of MAP MARKER INFO TABLE.
    0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-04 22:47:16.467 1917-1917/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER INFO TABLE.


09-04 22:47:16.467 1917-1917/? D/AMAMI-Attempt 2: Attempting Update of MAP MARKER TABLE.
    0 rows Updated for MAP MARKER TABLE, attempting insert
09-04 22:47:16.471 1917-1917/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER TABLE.
    Attempting Update of MAP MARKER INFO TABLE.
    0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER INFO TABLE.


09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 3: Attempting Update of MAP MARKER TABLE.
    0 rows Updated for MAP MARKER TABLE, attempting insert
    No row inserted into MAP MARKER TABLE.
    Attempting Update of MAP MARKER INFO TABLE.
    0 rows Updated for MAP MARKER INFO TABLE, attempting insert
    No row inserted into MAP MARKER INFO TABLE


09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 4: Attempting Update of MAP MARKER TABLE.
09-04 22:47:16.479 1917-1917/? D/AMAMI-Attempt 4: Update of MARKER TABLE Successful (1 rows updated.)
    Attempting Update of MAP MARKER INFO TABLE.
09-04 22:47:16.483 1917-1917/? D/AMAMI-Attempt 4: Update of MARKER INFO TABLE Successful (1 rows updated.)

即

• 1 和 2 已添加行，因为没有更新。
• 3 没有做任何事情，因为没有 id 3 但重复数据。
• 4 更新了存在的行。

补充问题

看起来也可能不需要这两个表，因为这两个表之间似乎是一对一的关系。因此，单个表可能包含所有列(纬度、经度、类型和租金)。

附加

添加方法 logAllRows(如下所示)并在每次更新/插入结束时调用它。允许您查看实际数据。

private void logAllRows(String tablename, String TAG) {
    SQLiteDatabase db = this.getWritableDatabase();
    StringBuilder sb = new StringBuilder();
    Cursor csr = db.query(tablename,null,null,null,null,null,null);
    while (csr.moveToNext()) {
        sb.append("\nRow # = ").append(String.valueOf(csr.getPosition() + 1));
        for (int i=0; i < csr.getColumnCount(); i++) {
            sb.append("\n\tColumn is ").append(csr.getColumnName(i)).append(" Value is ");
            int columntype = csr.getType(i);
            switch (columntype) {
                case Cursor.FIELD_TYPE_NULL:
                    sb.append("NULL");
                    break;
                case Cursor.FIELD_TYPE_FLOAT:
                    sb.append(String.valueOf(csr.getDouble(i)));
                    break;
                case Cursor.FIELD_TYPE_INTEGER:
                    sb.append(String.valueOf(csr.getInt(i)));
                    break;
                case Cursor.FIELD_TYPE_STRING:
                    sb.append(csr.getString(i));
                    break;
                case Cursor.FIELD_TYPE_BLOB:
                    sb.append("BLOB");
                    break;
            }
        }
    }
    csr.close();
    Log.d(TAG,sb.toString());
}

使用 logAllRows 时的结果

09-05 02:49:56.875 3016-3016/? D/AMAMI-Attempt 1: Attempting Update of MAP MARKER TABLE.
09-05 02:49:56.879 3016-3016/? D/AMAMI-Attempt 1: 0 rows Updated for MAP MARKER TABLE, attempting insert
    Row inserted into MAP MARKER TABLE. ID = 1
     Table is map_marker Row # = 1
        Column is _id Value is 1
        Column is map_marker_lat Value is 100
        Column is map_marker_lng Value is 100
    Attempting Update of MAP MARKER INFO TABLE.
    0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-05 02:49:56.887 3016-3016/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER INFO TABLE. ID = 1
     Table is map_marker_info Row # = 1
        Column is _id Value is 1
        Column is home_type Value is House
        Column is amount_rent Value is 45.64


09-05 02:49:56.887 3016-3016/? D/AMAMI-Attempt 2: Attempting Update of MAP MARKER TABLE.
    0 rows Updated for MAP MARKER TABLE, attempting insert
09-05 02:49:56.903 3016-3016/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER TABLE. ID = 2
     Table is map_marker Row # = 1
        Column is _id Value is 1
        Column is map_marker_lat Value is 100
        Column is map_marker_lng Value is 100
     Table is map_marker Row # = 2
        Column is _id Value is 2
        Column is map_marker_lat Value is 150
        Column is map_marker_lng Value is 150
    Attempting Update of MAP MARKER INFO TABLE.
    0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-05 02:49:56.907 3016-3016/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER INFO TABLE. ID = 2
     Table is map_marker_info Row # = 1
        Column is _id Value is 1
        Column is home_type Value is House
        Column is amount_rent Value is 45.64
     Table is map_marker_info Row # = 2
        Column is _id Value is 2
        Column is home_type Value is House
        Column is amount_rent Value is 65.64


09-05 02:49:56.907 3016-3016/? D/AMAMI-Attempt 3: Attempting Update of MAP MARKER TABLE.
    0 rows Updated for MAP MARKER TABLE, attempting insert
    No row inserted into MAP MARKER TABLE.
     Table is map_marker Row # = 1
        Column is _id Value is 1
        Column is map_marker_lat Value is 100
        Column is map_marker_lng Value is 100
     Table is map_marker Row # = 2
        Column is _id Value is 2
        Column is map_marker_lat Value is 150
        Column is map_marker_lng Value is 150
    Attempting Update of MAP MARKER INFO TABLE.
09-05 02:49:56.911 3016-3016/? D/AMAMI-Attempt 3: 0 rows Updated for MAP MARKER INFO TABLE, attempting insert
    No row inserted into MAP MARKER INFO TABLE
     Table is map_marker_info Row # = 1
        Column is _id Value is 1
        Column is home_type Value is House
        Column is amount_rent Value is 45.64
     Table is map_marker_info Row # = 2
        Column is _id Value is 2
        Column is home_type Value is House
        Column is amount_rent Value is 65.64


09-05 02:49:56.911 3016-3016/? D/AMAMI-Attempt 4: Attempting Update of MAP MARKER TABLE.
09-05 02:49:56.915 3016-3016/? D/AMAMI-Attempt 4: Update of MARKER TABLE Successful (1 rows updated.)
     Table is map_marker Row # = 1
        Column is _id Value is 1
        Column is map_marker_lat Value is 100
        Column is map_marker_lng Value is 100
     Table is map_marker Row # = 2
        Column is _id Value is 2
        Column is map_marker_lat Value is 150
        Column is map_marker_lng Value is 150
    Attempting Update of MAP MARKER INFO TABLE.
09-05 02:49:56.919 3016-3016/? D/AMAMI-Attempt 4: Update of MARKER INFO TABLE Successful (1 rows updated.)
     Table is map_marker_info Row # = 1
        Column is _id Value is 1
        Column is home_type Value is House
        Column is amount_rent Value is 45.64
     Table is map_marker_info Row # = 2
        Column is _id Value is 2
        Column is home_type Value is House
        Column is amount_rent Value is 65.64