我想这样update我的database:如果行存在update,并且行不存在,则添加一个新行。在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);
    }

最佳答案

一些问题


您的代码中有许多多余的空格,例如:


String.value Of(home_id)
db.insert With On Conflict

为了发生冲突,必须指定一个约束(隐式或显式)。尚不清楚您是否定义了合适的约束,甚至应该定义什么。 (以下示例假定了约束)
如果TABLE_MAP_MARKER中没有要更新的行,则marker_id_table将为0。因此,假设成功插入TABLE_MAP_MARKER,则将0用于HOME_ID列,以尝试更新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表,经度和纬度的组合应为UNIQUE
对于MAP MARKER INFO表,房屋类型和房租的组合应为UNIQUE(不太可能,但其他情况应该如此)。


然后可以使用以下等效项创建表:

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_lng列一起放置在map_marker的map_marker_lat上。因此,两个值的组合必须唯一,否则会引起冲突。因此100,110和100,120会很好,但是第二个100,110会导致冲突。

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

因此,有了DatabaseHelper类(您可​​能会猜到,包括基于代码的addMarkerAndMarkerInfo方法(已应用的更正和添加的loggin-参见注释/注释行)):-

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已更新行,因为它们存在。


增刊

似乎也不需要两个表,因为两个表之间似乎存在1-1关系。因此,一个表可能具有所有列(纬度,经度,类型和租金)。

额外

添加方法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

关于android - SQLite数据库UPDATE或INSERT不起作用,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52171719/

10-11 22:31
查看更多