Android-数据库操作

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import cn.crane.application.shoppingguider.model.BrandInfo;
import cn.crane.application.shoppingguider.model.FoodInfo;
import cn.crane.application.shoppingguider.model.JoyInfo;
import cn.crane.application.shoppingguider.model.MallInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseHelper {
    public DBOpenHelper myDbHelper;
    public static Context context;
    public static final String DATABASE_CHAT_NAME = "shopguider.db";

    public static final String TABLE_BRAND = "brands";
    public static final String TABLE_JOY = "joy";
    public static final String TABLE_FOODS = "foods";
    public static final String TABLE_SHOPINFO = "shopInfo";

    // private SQLiteDatabase db;

    public MyDatabaseHelper(Context context) {
        MyDatabaseHelper.context = context;
        copyDataBase(context, DATABASE_CHAT_NAME);
        myDbHelper = new DBOpenHelper(context, DATABASE_CHAT_NAME, null, 2);

    }

    /**
     * 获取商铺信息
     * 
     * @return
     */
    public List<MallInfo> getMalls() {
        SQLiteDatabase db = myDbHelper.getReadableDatabase();
        Cursor cursor = null;
        cursor = db.query(TABLE_SHOPINFO, null, null, null, null, null, null);
        List<MallInfo> arrMallInfos = new ArrayList<MallInfo>();
        while (cursor.moveToNext()) {
            MallInfo mallInfo = new MallInfo();
            mallInfo.setId(cursor.getString(cursor.getColumnIndex(MallInfo.ID)));
            mallInfo.setName(cursor.getString(cursor
                    .getColumnIndex(MallInfo.NAME)));
            mallInfo.setMsg(cursor.getString(cursor
                    .getColumnIndex(MallInfo.MSG)));
            mallInfo.setImages(cursor.getString(cursor
                    .getColumnIndex(MallInfo.IMAGES)));
            mallInfo.setIsCollect(cursor.getString(cursor
                    .getColumnIndex(MallInfo.ISCOLLECT)));
            mallInfo.setCategory(cursor.getString(cursor
                    .getColumnIndex(MallInfo.CATEGORY)));
            arrMallInfos.add(mallInfo);
        }
        if (!(cursor.isClosed() || cursor == null)) {
            cursor.close();
        }
        db.close();
        return arrMallInfos;
    }

    /**
     * 获取品牌信息
     * 
     * @return
     */
    public List<BrandInfo> getBrands(boolean isAll) {
        SQLiteDatabase db = myDbHelper.getReadableDatabase();
        Cursor cursor = null;
        if (isAll) {
            cursor = db.query(TABLE_BRAND, null, null, null, null, null, null);
        } else {
            cursor = db.query(TABLE_BRAND, null, "isCollect=?",
                    new String[] { BrandInfo.COLLECT_YES }, null, null, null);
        }

        List<BrandInfo> arrBrandInfos = new ArrayList<BrandInfo>();
        while (cursor.moveToNext()) {
            BrandInfo brandInfo = new BrandInfo();
            brandInfo.setId(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.ID)));
            brandInfo.setBrand_name(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.NAME)));
            brandInfo.setMsg(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.MSG)));
            brandInfo.setImages(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.IMAGES)));
            brandInfo.setIsCollect(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.ISCOLLECT)));
            brandInfo.setAction(cursor.getString(cursor
                    .getColumnIndex(BrandInfo.ACTION)));
            arrBrandInfos.add(brandInfo);
        }
        if (!(cursor.isClosed() || cursor == null)) {
            cursor.close();
        }
        db.close();
        return arrBrandInfos;
    }

    /**
     * 获取餐饮信息
     * 
     * @return
     */
    public List<FoodInfo> getFoods() {
        SQLiteDatabase db = myDbHelper.getReadableDatabase();
        Cursor cursor = null;
        cursor = db.query(TABLE_FOODS, null, null, null, null, null, null);

        List<FoodInfo> arrFoodInfos = new ArrayList<FoodInfo>();
        while (cursor.moveToNext()) {
            FoodInfo foodInfo = new FoodInfo();
            foodInfo.setId(cursor.getString(cursor.getColumnIndex(FoodInfo.ID)));
            foodInfo.setName(cursor.getString(cursor
                    .getColumnIndex(FoodInfo.NAME)));
            foodInfo.setImages(cursor.getString(cursor
                    .getColumnIndex(FoodInfo.IMAGES)));
            foodInfo.setDetail(cursor.getString(cursor
                    .getColumnIndex(FoodInfo.DETAIL)));
            arrFoodInfos.add(foodInfo);
        }
        if (!(cursor.isClosed() || cursor == null)) {
            cursor.close();
        }
        db.close();
        return arrFoodInfos;
    }

    /**
     * 获取娱乐信息
     * 
     * @return
     */
    public List<JoyInfo> getJoys() {
        SQLiteDatabase db = myDbHelper.getReadableDatabase();
        Cursor cursor = null;
        cursor = db.query(TABLE_JOY, null, null, null, null, null, null);

        List<JoyInfo> arrJoyInfos = new ArrayList<JoyInfo>();
        while (cursor.moveToNext()) {
            JoyInfo joyInfo = new JoyInfo();
            joyInfo.setId(cursor.getString(cursor.getColumnIndex(JoyInfo.ID)));
            joyInfo.setName(cursor.getString(cursor
                    .getColumnIndex(JoyInfo.NAME)));
            joyInfo.setImages(cursor.getString(cursor
                    .getColumnIndex(JoyInfo.IMAGES)));
            joyInfo.setDetail(cursor.getString(cursor
                    .getColumnIndex(JoyInfo.DETAIL)));
            arrJoyInfos.add(joyInfo);
        }
        if (!(cursor.isClosed() || cursor == null)) {
            cursor.close();
        }
        db.close();
        return arrJoyInfos;
    }

    public long updateBrandInfo(BrandInfo brandInfo) {
        SQLiteDatabase db = myDbHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(BrandInfo.ISCOLLECT, brandInfo.getIsCollect());
        // 插入ContentValues中的数据
        long l = db.update(TABLE_BRAND, cv, "id=?",
                new String[] { brandInfo.getId() + "" });
        return l;
    }

    /**
     * copy DB
     */
    public static void copyDataBase(Context context, String dbName) {
        OutputStream os = null;
        File dbFile = context.getDatabasePath(dbName);
        if (dbFile.exists()) {
            return;
        }
        File dirDatabase = new File(dbFile.getParent());
        dirDatabase.mkdirs();
        try {
            dbFile.createNewFile();
            os = new FileOutputStream(dbFile.getAbsolutePath());
            InputStream open = context.getAssets().open(dbName);
            byte[] b = new byte[1024 * 512];
            int len;
            while ((len = open.read(b)) > 0) {
                os.write(b, 0, len);
            }
            os.flush();
            open.close();

        } catch (FileNotFoundException e) {

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

            e.printStackTrace();
        } finally {
            if (null != os) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * SQLiteOpenHelper
     * 
     * @author yurf
     * 
     */
    private static class DBOpenHelper extends SQLiteOpenHelper {

        public DBOpenHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);

        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            File file = context.getDatabasePath(DATABASE_CHAT_NAME);
            file.delete();
            copyDataBase(context, DATABASE_CHAT_NAME);
            onCreate(db);
        }

    }

}