更改listView中特定项的日期

I have a button reschedule in my app, pressing it'll automatically change the past dates of the To-dos with the current date, but the problem is that with the following code, it also changes the future dates with the current one, i dont have any code under else-if but it still changes the future date with current.

我的应用程序中有一个按钮重新安排,按下它会自动更改待办事项的过去日期与当前日期,但问题是,使用以下代码,它也会更改当前日期的日期,我在else-if下没有任何代码,但它仍然用当前更改未来日期。

public void updateDates2() {
    SQLiteDatabase db = mHelper.getWritableDatabase();
    Cursor cursor = null;
    Calendar c = Calendar.getInstance();
    List<String> array = new ArrayList<String>();
    SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    cursor = db.query(TaskContract.TaskEntry.TABLE_NAME,
            new String[]{TaskContract.TaskEntry._ID,
                    TaskContract.TaskEntry.COLUMN_DATE,
            },
            null, null, null, null, null);

    String formattedDate = formatter.format(c.getTime());

    while (cursor.moveToNext()) {
        //Getting dates into the array using Cursor.
        String datesfromdb = cursor.getString(cursor.getColumnIndex("date"));
        array.add(datesfromdb);
    }
    ContentValues cv = new ContentValues();
    String TEMPPCOLNAME = "checkdate";
    Cursor csr = db.query(TaskContract.TaskEntry.TABLE_NAME, new String[]{
                    // All existing columns
                    "*",
                    "rowid as uid",
                    // generate reformatted date column named checkdate usable by date functions
                    // i.e. converts dd/mm/yyyy to yyyy-mm-yy
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",7,4)||'-'||" +
                            "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",4,2)||'-'||" +
                            "substr( " + TaskContract.TaskEntry.COLUMN_DATE + ",1,2) AS " + TEMPPCOLNAME},
            // where clause to only include properly formatted dates and those who date is less
            // than or equaly to today's date
            TEMPPCOLNAME + "<= date('now') AND " +
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",3,1) = '/' AND " +
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",6,1) = '/'",
            null, null, null, null
    );


    Toast.makeText(this, csr.toString() , Toast.LENGTH_SHORT).show();
    while (csr.moveToNext()) {
        for (int i = 0; i < array.size(); i++) {
            if (array.get(i).equals(formattedDate)) {
                //If comparision matches, then incrementing the current date by one.
                c.add(Calendar.DATE, 1);
                formattedDate = formatter.format(c.getTime());
            } else {
                //if the date doesn't matches then returning the date to the db.update to set this date to the current task.
                cv.put(TaskContract.TaskEntry.COLUMN_DATE, formattedDate);
            }
        }

        if (

                db.update(TaskContract.TaskEntry.TABLE_NAME, cv, "rowid=?", new String[]{
                        String.valueOf(csr.getLong(csr.getColumnIndex("uid")))}
                ) > 0) {
            Log.d("UPDT2", "Row Updated OK.");
        } else {
            Log.d("UPDT2", "Update failed.");
        }

    }

1 个解决方案

#1

I believe your issue, as you describe, is with this line :-

我相信你的问题正如你所描述的那样:

db.update(TaskContract.TaskEntry.TABLE_NAME, cv, String.valueOf(idx1), null);

and specifically the 3rd (WHERE clause) parameter i.e. String.valueOf(idx1)

特别是第3个(WHERE子句)参数,即String.valueOf(idx1)

You are effectively saying WHERE n (n being the offset/index of the column TaskContract.TaskEntry.COLUMN_DATE). If this column index is greater than 0 then the expression will effectively be true and thus every row will be updated.

你实际上是在说WHERE n(n是TaskContract.TaskEntry.COLUMN_DATE列的偏移量/索引)。如果此列索引大于0,则表达式将有效,因此每行都将更新。

So this update only has to be executed once and it, if the column index is greater then 0 (true), will update all rows.Which appears to be the behavior that you have described.

因此,此更新只需执行一次,如果列索引大于0(true),它将更新所有行。这似乎是您所描述的行为。

You need the parameter to be something like TaskContract.TaskEntry.COLUMN_DATE = 'CurrentDate'.

您需要参数类似于TaskContract.TaskEntry.COLUMN_DATE ='CurrentDate'。

Or preferably TaskContract.TaskEntry.COLUMN_DATE=? with the 4th parameter resolving the ? placeholder e.g. newString[]{dateDB}.

或者最好是TaskContract.TaskEntry.COLUMN_DATE =?用第4个参数解决?占位符,例如newString [] {dateDB}。

This would update all rows with that date, which may result in the needles update attempts. For example if you had two past dates that were the same, both would be updated when the cursor reaches that row and when the cursor reaches the other row.

这将更新具有该日期的所有行,这可能导致针更新尝试。例如,如果您有两个相同的过去日期,则当光标到达该行并且光标到达另一行时,两个日期都会更新。

I'd suggest only extracting the rows to be updated, this isn't made easy as you appear to have the date stored as dd/mm/yyyy (which is not easily sorted according to date sequence). However, you can get around that by creating a column in the extracted cursor that re-formats the date and allows it to be checked.

我建议只提取要更新的行,这并不容易,因为您似乎将日期存储为dd / mm / yyyy(根据日期顺序不容易排序)。但是,您可以通过在提取的游标中创建一个列来重新格式化日期并允许对其进行检查。

This does look a little complex here's an example :-

这看起来有点复杂,这是一个例子: -

String TEMPPCOLNAME = "checkdate";
        Cursor csr = db.query(TBNAME,new String[]{
                // All existing columns
                "*",
                "rowid as uid",
                // generate reformatted date column named checkdate usable by date functions
                // i.e. converts dd/mm/yyyy to yyyy-mm-yy
                "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",7,4)||'-'||" +
                        "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",4,2)||'-'||" +
                        "substr( " + TaskContract.TaskEntry.COLUMN_DATE + ",1,2) AS " + TEMPPCOLNAME },
                // where clause to only include properly formatted dates and those who date is less
                // than or equaly to today's date
                TEMPPCOLNAME + "<= date('now') AND " +
                        "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",3,1) = '/' AND " +
                        "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",6,1) = '/'",
                null,null,null,null
                );

This is saying get a Cursor with all columns in the table (*), make the Cursor have a column called uid which is an alias of the rowid and also make the Cursor have a column called checkdate (name as per TEMPCOLNAME). checkdate being the reformatted date (now yyyy-mm-dd as SQLite likes)

这就是说获取一个包含表(*)中所有列的Cursor,使Cursor有一个名为uid的列,它是rowid的别名,并且还使Cursor有一个名为checkdate的列(名称根据TEMPCOLNAME)。 checkdate是重新格式化的日期(现在像SQLite喜欢的yyyy-mm-dd)

The WHERE clause (third paramter) is what is used to only select dates that are less than or equal to whatever is defined in the 4th parameter that will replace the ? placeholder.

WHERE子句(第三个参数)是用于仅选择小于或等于第4个参数中定义的日期的日期,该参数将替换?占位符。

The WHERE parameter also restricts the selected rows to those whose date has / at positions 3 and 6 (just a precaution).

WHERE参数还将所选行限制为日期具有/位置3和6的那些行(仅作为预防措施)。

The update is then quite straightforward and importantly specific to a single row (hence why the column uid was extracted) :-

然后,更新非常简单,重要的是特定于单行(因此提取列uid的原因): -

    while (csr.moveToNext()) {
        ContentValues cv = new ContentValues();
        cv.put(TaskContract.TaskEntry.COLUMN_DATE,
                new SimpleDateFormat("dd/MM/yyyy").format(Calendar.getInstance().getTime()));
        db.update(TBNAME,cv,"rowid=?",new String[]{
                String.valueOf(csr.getLong(csr.getColumnIndex("uid")))}
                ); 
    }
    csr.close();

Combining both, you could have:-

结合两者,你可以: -

public void updateDates2() {
    String TEMPPCOLNAME = "checkdate";
    Cursor csr = db.query(TaskContract.TaskEntry.TABLE_NAME,new String[]{
            // All existing columns
            "*",
            "rowid as uid",
            // generate reformatted date column named checkdate usable by date functions
            // i.e. converts dd/mm/yyyy to yyyy-mm-yy
            "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",7,4)||'-'||" +
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",4,2)||'-'||" +
                    "substr( " + TaskContract.TaskEntry.COLUMN_DATE + ",1,2) AS " + TEMPPCOLNAME },
            // where clause to only include properly formatted dates and those who date is less
            // than or equaly to today's date
            TEMPPCOLNAME + "<= date('now') AND " +
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",3,1) = '/' AND " +
                    "substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",6,1) = '/'",
            null,null,null,null
            );
    while (csr.moveToNext()) {
        ContentValues cv = new ContentValues();
        cv.put(TaskContract.TaskEntry.COLUMN_DATE,
                new SimpleDateFormat("dd/MM/yyyy").format(Calendar.getInstance().getTime()));
        if (
        db.update(TBNAME,cv,"rowid=?",new String[]{
                String.valueOf(csr.getLong(csr.getColumnIndex("uid")))}
                ) > 0) {
            Log.d("UPDT2","Row Updated OK.");
        } else {
            Log.d("UPDT2", "Update failed.");
        }
    }
    csr.close();
}

using the above testing provided the following results :-

10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 1 - Event Name is Event1 Starts on 01/03/2011 Ends on 2021-03-21
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 2 - Event Name is Event2 Starts on 14/08/2014 Ends on 2020-03-21
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 3 - Event Name is Event3 Starts on 26/10/2017 Ends on 2017-12-21
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 4 - Event Name is Event4 Starts on 28/10/2017 Ends on 2018-21-31
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 5 - Event Name is Event5 Starts on 01/01/2018 Ends on 2018-12-31
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 6 - Event Name is Event6 Starts on 27/10/2018 Ends on 2018-12-31
10-27 09:32:51.482 4259-4259/? D/EVENTDATA: Row 7 - Event Name is Event7 Starts on garbage Ends on 2018-12-31
10-27 09:32:51.483 4259-4259/? D/UPDT2: Extracted Row for Event Event1 StartDate Stored = 01/03/2011 Converted to = 2011-03-01
10-27 09:32:51.487 4259-4259/? D/UPDT2: Row Updated OK.
10-27 09:32:51.487 4259-4259/? D/UPDT2: Extracted Row for Event Event2 StartDate Stored = 14/08/2014 Converted to = 2014-08-14
10-27 09:32:51.491 4259-4259/? D/UPDT2: Row Updated OK.
10-27 09:32:51.491 4259-4259/? D/UPDT2: Extracted Row for Event Event3 StartDate Stored = 26/10/2017 Converted to = 2017-10-26
10-27 09:32:51.495 4259-4259/? D/UPDT2: Row Updated OK.
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 1 - Event Name is Event1 Starts on 27/10/2017 Ends on 2021-03-21
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 2 - Event Name is Event2 Starts on 27/10/2017 Ends on 2020-03-21
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 3 - Event Name is Event3 Starts on 27/10/2017 Ends on 2017-12-21
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 4 - Event Name is Event4 Starts on 28/10/2017 Ends on 2018-21-31
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 5 - Event Name is Event5 Starts on 01/01/2018 Ends on 2018-12-31
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 6 - Event Name is Event6 Starts on 27/10/2018 Ends on 2018-12-31
10-27 09:32:51.495 4259-4259/? D/EVENTDATA: Row 7 - Event Name is Event7 Starts on garbage Ends on 2018-12-31

The first 7 lines are the data prior to the above being run, the last 7 after. The 6 lines between are form the update itself (I removed the code to produce the Extracted Row ....., the Converted to is the value of checkdate i.e. the reformatted date)

前7行是上述运行之前的数据,后7是之后的数据。之间的6行形成更新本身(我删除了代码以生成Extracted Row .....,Converted to是checkdate的值,即重新格式化的日期)

However, perhaps the best method would be to have a single update that can do away with the need for a cursor to extract the rows:-

public void updateDates() {
    Calendar c = Calendar.getInstance();
    SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    String formattedDate = formatter.format(c.getTime());

    ContentValues cv = new ContentValues();
    cv.put(TaskContract.TaskEntry.COLUMN_DATE,formattedDate);

    String whereclause =
            " substr(" + TaskContract.TaskEntry.COLUMN_DATE +  ",7,4)||" +
                    " substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",4,2)||" +
                    " substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",1,2) " +
                    " <= date('now') " +
                    " AND substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",3,1) = '/'" +
                    " AND substr(" + TaskContract.TaskEntry.COLUMN_DATE + ",6,1) = '/'";
    db.update(TaskContract.TaskEntry.TABLE_NAME,cv,whereclause,null);
}

Testing Data :-

测试数据: -

10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 1 - Event Name is Event1 Starts on 01/03/2011 Ends on 2021-03-21
10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 2 - Event Name is Event2 Starts on 14/08/2014 Ends on 2020-03-21
10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 3 - Event Name is Event3 Starts on 26/10/2017 Ends on 2017-12-21
10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 4 - Event Name is Event4 Starts on 28/10/2017 Ends on 2018-21-31
10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 5 - Event Name is Event5 Starts on 01/01/2018 Ends on 2018-12-31
10-27 11:25:52.331 5250-5250/? D/EVENTDATA: Row 6 - Event Name is Event6 Starts on 27/10/2018 Ends on 2018-12-31
10-27 11:25:52.332 5250-5250/? D/EVENTDATA: Row 7 - Event Name is Event7 Starts on garbage Ends on 2018-12-31
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 1 - Event Name is Event1 Starts on 27/10/2017 Ends on 2021-03-21
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 2 - Event Name is Event2 Starts on 27/10/2017 Ends on 2020-03-21
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 3 - Event Name is Event3 Starts on 26/10/2017 Ends on 2017-12-21
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 4 - Event Name is Event4 Starts on 28/10/2017 Ends on 2018-21-31
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 5 - Event Name is Event5 Starts on 01/01/2018 Ends on 2018-12-31
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 6 - Event Name is Event6 Starts on 27/10/2018 Ends on 2018-12-31
10-27 11:25:52.335 5250-5250/? D/EVENTDATA: Row 7 - Event Name is Event7 Starts on garbage Ends on 2018-12-31