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专题 *