Android开发进阶系列（七） 使用数据库

数据库的概念不用多说了吧，增删改查这些基本语句大概的样子也应该略有所闻了。Android的数据库略有不同，不过也没有那么神秘，对于新手来说，一个字，开练！

在我的项目里，我是新建了一个类负责数据库的操作的，下面上代码：

1. 版本号

// 数据库名称
private static String DBName="easyretail.db";
LocalDatabase(Context paramContext) {
        super(paramContext, DBName, null, 2); // 2为version
}

1. 建表

@Override
    public void onCreate(SQLiteDatabase db) {
        try {
          db.execSQL("CREATE TABLE IF NOT EXISTS stock (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, " +
                "cost DECIMAL(4,1), price DECIMAL(4,1) NOT NULL, amount INTEGER NOT NULL, total_sold INTEGER NOT NULL, stock_date Text NOT NULL, pic BLOB NULL);");

        db.execSQL("CREATE TABLE IF NOT EXISTS record (id INTEGER PRIMARY KEY AUTOINCREMENT, payment DECIMAL(6,1) NOT NULL, profit DECIMAL(6,1) NOT NULL, " +
                "add_time Text NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, day INTEGER NOT NULL);");

        db.execSQL("CREATE TABLE IF NOT EXISTS recorddetail (id INTEGER PRIMARY KEY AUTOINCREMENT, record_id INTEGER, goods_id INTEGER," +
                "goods_name TEXT NOT NULL, goods_price DECIMAL(4,1) NOT NULL, goods_amount INTEGER NOT NULL, goods_payment DECIMAL(4,1) NOT NULL, goods_pic BLOB NULL);");

        } catch(Exception e) {
            e.printStackTrace();
        }       
    }

1. 更新表

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(" ALTER TABLE record ADD profit DECIMAL(6,1) NULL");
        /* version=2时的更新
        //db.execSQL("CREATE TABLE IF NOT EXISTS record (id INTEGER PRIMARY KEY AUTOINCREMENT, payment DECIMAL(6,1) NOT NULL, " +
            //  "add_time Text NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, day INTEGER NOT NULL);");

        //db.execSQL("CREATE TABLE IF NOT EXISTS recorddetail (id INTEGER PRIMARY KEY AUTOINCREMENT, record_id INTEGER, goods_id INTEGER," +
            //  "goods_name TEXT NOT NULL, goods_price DECIMAL(4,1) NOT NULL, goods_amount INTEGER NOT NULL, goods_payment DECIMAL(4,1) NOT NULL);");
         */ 
        // version = 3时
        //db.execSQL(" ALTER TABLE stock ADD pic BLOB NULL "); //往表中增加一列图片类型
        // version =4 时
        //db.execSQL(" ALTER TABLE recorddetail ADD goods_pic BLOB NULL "); //往表中增加一列图片类型
    }

注意，如果你以前发布的版本中数据库版本也不同，那你可能需要判断当前数据库的版本来决定执行哪些更新语句。

1. 数据的增删改查

    /*
     * 查询数据库，给goodsList赋值
     */
    public void InitGoodsList() {
        Log.v(FusionField.TAG, "InitGoodsList() called");

        try {
            SQLiteDatabase localSQLiteDatabase = getReadableDatabase();
            //Cursor localCursor = localSQLiteDatabase.query(GoodsTableName, null,null,null, null, null, null);
            Cursor localCursor = localSQLiteDatabase.rawQuery("select * from stock",null);

            if (localCursor != null) {
                if(FusionField.goodsList==null)
                    FusionField.goodsList = new ArrayList<GoodsInfo>();  

                GoodsInfo goods;

                while(localCursor.moveToNext()){  
                    int id = localCursor.getInt(localCursor.getColumnIndex(GoodsTable_Column_ID));  
                    goods = FusionField.FindGoodsByID(id);
                    if(goods==null){        
                        goods = new GoodsInfo();  
                        goods.setID(id);  
                        FusionField.goodsList.add(goods);
                    }
                    String name = localCursor.getString(localCursor.getColumnIndex(GoodsTable_Column_Name)); 
                    float cost = localCursor.getFloat(localCursor.getColumnIndex(GoodsTable_Column_Cost)); 
                    float price = localCursor.getFloat(localCursor.getColumnIndex(GoodsTable_Column_Price)); 
                    int amount = localCursor.getInt(localCursor.getColumnIndex(GoodsTable_Column_Amount)); 
                    String stock_date = localCursor.getString(localCursor.getColumnIndex(GoodsTable_Column_StockDate)); 
                    int total_sold = localCursor.getInt(localCursor.getColumnIndex(GoodsTable_Column_TotalSold));
                    byte[] pic = localCursor.getBlob(localCursor.getColumnIndex(GoodsTable_Column_Pic));
                    goods.setName(name);  
                    goods.setCost(cost);
                    goods.setPrice(price);
                    goods.setAmount(amount);       
                    goods.setStockDate(FusionField.ConverStringToDate(stock_date));
                    goods.setTotalSold(total_sold);
                    if(pic!=null)
                        goods.setPicture(FusionField.ConvertByteToBitmap(pic));
                }
                localCursor.close();
            }

            localSQLiteDatabase.close();
        } catch (SQLiteException localSQLiteException) {
            Log.v(FusionField.TAG, "SQLite exception in LocalDatabase.InitGoodsList(): " + localSQLiteException.getMessage());
        }
    }

    /*
     * 修改数据
     * return 0: succeeded
     *       -1: failed
     */
    public Boolean ModifyGoods(int id, String name, int amount, float cost, float price, byte[] pic) {
        Log.v(FusionField.TAG, "ModifyGoods(" + id + ", " + name + ", " + amount + ", " + cost + ", " + price + ") called");
        Boolean ret = true;
        try {
            SQLiteDatabase localSQLiteDatabase = getWritableDatabase();
            Cursor localCursor = localSQLiteDatabase.query(GoodsTableName, new String[]{"amount"}, "id = ?", new String[]{String.valueOf(id)}, null, null, null);

            if (localCursor != null && localCursor.moveToFirst()) {
                ContentValues localContentValues = new ContentValues();
                localContentValues.put("name", name); // 名称
                localContentValues.put("amount", amount); // 数量
                localContentValues.put("cost", cost); // 成本
                localContentValues.put("price", price); // 价格
                localContentValues.put("pic", pic); // 图片
                localSQLiteDatabase.update(GoodsTableName, localContentValues, "id = ?", new String[]{String.valueOf(id)});
            } else
                ret = false; // Error, not exists

            localCursor.close();
            localSQLiteDatabase.close();
        } catch (SQLiteException localSQLiteException) {
            Log.v(FusionField.TAG, "SQLite exception in LocalDatabase.UpdateStock(): " + localSQLiteException.getMessage());
            ret = false;
        }

        return ret;
    }

    /*
     *  删除某商品
     */
    public Boolean DeleteGoodsFromStock(int id) {
        Log.v(FusionField.TAG, "DeleteGoodsFromStock(" + id + ") called");
        Boolean ret = true;

        try {
            SQLiteDatabase localSQLiteDatabase = getWritableDatabase();         
            localSQLiteDatabase.delete(GoodsTableName, "id = ?", new String[]{String.valueOf(id)});

            localSQLiteDatabase.close();

        } catch (SQLiteException localSQLiteException) {
            Log.v(FusionField.TAG, "SQLite exception in LocalDatabase.DeleteGoodsFromStock(): " + localSQLiteException.getMessage());
            ret = false;
        }

        return ret;
    }   

• * Android开发进阶系列（一） 序言 *
• * Android开发进阶系列（二） Tab页界面布局 *
• * Android开发进阶系列（三） 系统参数的获取和Broadcast *
• * Android开发进阶系列（四） 左移拉出Menu菜单界面布局 *
• * Android开发进阶系列（五） 连接服务器更新APK或下载资源文件 *
• * Android开发进阶系列（六） ListView的基本用法 *
• * Android开发进阶系列（七） 使用数据库 *
• * Android开发进阶系列（八） 界面美化之自定义弹出框 *
• * Android开发进阶系列（九） AChartEngine专题 *