sqlite 如何将以前的数据添加到保存的ListView中的新数据中？

我正在用Android Studio中的SQL做一个预算计算器。用户输入购买日期、物品名称和物品价值。我使用SQL将每个“用户输入”保存到一个新的ListView中。
我希望我的代码访问前面的游标来获取值，将其添加到新键入的数据中，并计算累计总和：
|||
-|-|
[10.01.22]|3.75|咖啡
[10.02.22]|5.00|铅笔
[10.03.22]|10.00|Ring
因此，它显示了花费的总金额(但它只保存ListView，而不会在应用程序关闭时保存总价值)。

String queryString = "SELECT * FROM " + CATEGORY_VALUE;

    SQLiteDatabase db = this.getReadableDatabase();

    // cursor is the result from the SQlite
    Cursor cursor = db.rawQuery(queryString, null);
    Cursor cursor2 = db.rawQuery(queryString, null);
    Cursor cursor3 = db.rawQuery(queryString, null);

    String categoryValue;
    String categoryName;
    String categoryInfo;
    String categoryDate;
    String categoryTotal;

    if (cursor.moveToFirst()) {
        //if ther eare results loop through the results and creat the new customer results for each row
        // insert it into the return list and be able to make this function succeed
        do {

            categoryValue = cursor.getString(1);
            categoryName = cursor.getString(2);
            categoryInfo = cursor.getString(3);
            categoryDate = cursor.getString(4);
            //cursor3 = cursor;                        (I was trying to convert it to float,
            //cursor3.moveToPrevious();                 sum the previous numbers and put it
            //lastCategory = cursor3.getString(5);       back to string)
            //lastCate_f = Float.valueOf(lastCategory);   
            categoryTotal = cursor.getString(5);
            //thisCate_f = Float.valueOf(categoryTotal);  
            //totalCate_f = lastCate_f + thisCate_f;

            categoryTotal = Float.toString(totalCate_f);

            ValueModel newValue = new ValueModel(categoryDate, categoryValue, categoryInfo, categoryName, categoryTotal);
            returnList.add(newValue);
        } while(cursor.moveToNext());

    } else {
        //do not add anything to the list.
    }

    // close both things
    cursor.close();
    db.close();

    return returnList;
}

CATEGORY_VALUE是我的表名。
当我运行应用程序并在其中输入随机示例时，它实际上汇总了总数！然而，当我关闭并重新打开应用程序时，ListView仍然在那里，但全部从头开始。
[10.01.22]3.75咖啡3.75
[10.02.22]5.00铅笔8.75
[我关闭了应用程序并重新启动]
[10.03.22]10.00环10.00

可能不需要按照您正在尝试的方式来完成此操作，而是您可以在单个插入中完成所有这些操作，例如：

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.01.22', /* The date */
        '3.75', /* the value */
        'Coffee', /* The name*/
        'hot drink', /* The info */
        CAST('3.75' /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +
        COALESCE( /* just in case null (e.g. when inserting the very first row */
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                /* RE ORDER BY */
                /* NOTE assumes 1 row per date */
                    /* could use ORDER BY rowid DESC as an alternative IF ALWAYS INSERTING a later date*/
                    /* Really dates should be in a recognised format see https://www.sqlite.org/lang_datefunc.html */ 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

作为演示，请考虑以下用于按顺序插入所有4行的代码(即运行相同的查询，但值不同)：

/* Preapre the demo environment (tables) */
DROP TABLE IF EXISTS category_value;
CREATE TABLE IF NOT EXISTS category_value (id INTEGER PRIMARY KEY, categoryvalue TEXT, categoryname TEXT, categoryinfo TEXT, categorydate TEXT, categorytotal TEXT DEFAULT 0);

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.01.22', /* The date */
        '3.75', /* the value */
        'Coffee', /* The name*/
        'hot drink', /* The info */
        CAST('3.75' /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +
        COALESCE( /* just in case null (e.g. when inserting the very first row */
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                /* RE ORDER BY */
                /* NOTE assumes 1 row per date */
                    /* could use ORDER BY rowid DESC as an alternative IF ALWAYS INSERTING a later date*/
                    /* Really dates should be in a recognised format see https://www.sqlite.org/lang_datefunc.html */ 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.02.22',
        '5.00',
        'Pencil',
        'Writing and Drawing implement',
        CAST('5.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.03.22',
        '10.00',
        'Ring',
        'flat round thing',
        CAST('10.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;

INSERT INTO category_value (categorydate,categoryvalue,categoryname,categoryinfo,categorytotal)
    VALUES(
        '10.04.22',
        '20.00',
        'new thing',
        'that would be telling',
        CAST('20.00' AS REAL) +
        COALESCE(
            (
                SELECT CAST(categorytotal AS REAL) 
                FROM category_value 
                ORDER BY substr(categorydate,7,2)||substr(categorydate,1,2)||substr(categorydate,4,4) DESC
                LIMIT 1
            )
        ,0)
    )
;
SELECT * FROM category_value;
/* Cleanup demo environment */
DROP TABLE IF EXISTS category_value;

结果输出(来自SELECT)是：-

也就是说，几乎不需要您的所有代码，只需要插入(基于上面的插入SQL，但满足传递值而不是演示中的硬编码值)，然后在插入之后构建新的列表。
Android上的应用

将上述内容合并到一个INSERT方法中(在数据库帮助器中，并假设db是一个已示例化的SQLiteDatabase成员变量)：-

public void insert(float value, String name, String info, String date) {
    String sql="INSERT OR IGNORE INTO " + CATEGORY_VALUE +
            "(" +
            COLUMN_VALUE +
            "," +COLUMN_NAME +
            "," + COLUMN_INFO +
            "," + COLUMN_DATE +
            "," + COLUMN_TOTAL +
            ") " +
            "VALUES(" +
            "? /* The Value */," +
            "? /* the name */," +
            "? /* The info */," +
            "? /* The date */," +
            "CAST(? /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +" +
            "COALESCE( /* just in case null (e.g. when inserting the very first row */" +
            "(" +
            "SELECT CAST(" + COLUMN_TOTAL + " AS REAL) " +
            "FROM " + CATEGORY_VALUE + " " +
            "ORDER BY substr(" + COLUMN_DATE + ",7,2)||substr(" + COLUMN_DATE + ",1,2)||substr(" + COLUMN_DATE + ",4,4) DESC " +
            "LIMIT 1" +
            ")" +
            ",0)" +
            ")" +
            ";";
    db.execSQL(sql,new Object[]{value,name,info,date,value});
}

然后，为了演示Insert方法，您可以使用：

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = DBHelper.getInstance(this);
        dbHelper.insert(3.5F,"Coffee","Hot Drink","10.01.22");
        dbHelper.insert(5.00F,"Pencil","Writing and Drawing Implement","10.02.22");
        dbHelper.insert(10.00f,"Ring","Flat, circular object","10.03.22");
        dbHelper.insert(20.00F,"New Thing","That would be telling","10.04.22");
    }
}

结果(通过Appcheck)是一个具有以下内容的数据库：

使用的完整DBHelper类为：-

class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "the_database.db";
    private static final int DATABASE_VERSION = 1;
    public static final String CATEGORY_VALUE = "cv";
    public static final String COLUMN_ID = BaseColumns._ID;
    public static final String COLUMN_VALUE = "categoryValue";
    public static final String COLUMN_NAME = "categoryName";
    public static final String COLUMN_INFO = "categoryInfo";
    public static final String COLUMN_DATE = "categoryDate";
    public static final String COLUMN_TOTAL = "categoryTotal";
    private static final String CV_CRT_SQL = "CREATE TABLE IF NOT EXISTS " + CATEGORY_VALUE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY" +
            "," + COLUMN_VALUE + " REAL" +
            "," + COLUMN_NAME + " TEXT" +
            "," + COLUMN_INFO + " TEXT" +
            "," + COLUMN_DATE + " TEXT" +
            "," + COLUMN_TOTAL + " REAL" +
            ");";

    private SQLiteDatabase db;

    private DBHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
        db = this.getWritableDatabase(); /* readable database is 90%+ writeable anyway */
    }
    private volatile static DBHelper instance;
    public static DBHelper getInstance(Context context) {
        if (instance==null) {
            instance = new DBHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CV_CRT_SQL);
    }

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

    }

    public void insert(float value, String name, String info, String date) {
        String sql="INSERT OR IGNORE INTO " + CATEGORY_VALUE +
                "(" +
                COLUMN_VALUE +
                "," +COLUMN_NAME +
                "," + COLUMN_INFO +
                "," + COLUMN_DATE +
                "," + COLUMN_TOTAL +
                ") " +
                "VALUES(" +
                "? /* The Value */," +
                "? /* the name */," +
                "? /* The info */," +
                "? /* The date */," +
                "CAST(? /* SAME VALUE AS the value !!!IMPORTANT!!!*/ AS REAL) +" +
                "COALESCE( /* just in case null (e.g. when inserting the very first row */" +
                "(" +
                "SELECT CAST(" + COLUMN_TOTAL + " AS REAL) " +
                "FROM " + CATEGORY_VALUE + " " +
                "ORDER BY substr(" + COLUMN_DATE + ",7,2)||substr(" + COLUMN_DATE + ",1,2)||substr(" + COLUMN_DATE + ",4,4) DESC " +
                "LIMIT 1" +
                ")" +
                ",0)" +
                ")" +
                ";";
        db.execSQL(sql,new Object[]{value,name,info,date,value});
    }
}

sqlite 如何将以前的数据添加到保存的ListView中的新数据中？

1条答案

相关问题

热门标签

最新问答