Android Studio SQLite Manage

已于 2024-08-20 15:00:27 修改
阅读量627 收藏 13
点赞数 11
分类专栏： android studio 文章标签： android studio sqlite
于 2024-08-20 14:58:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wysdong/article/details/141358583
版权

一、类DBHelper ：

public class DBHelper extends SQLiteOpenHelper {

    private static final String CREATE_TABLE_START_SQL = "CREATE TABLE IF NOT EXISTS ";
    private static final String CREATE_TABLE_PRIMIRY_SQL = " integer primary key autoincrement,";

    public  DBHelper(Context context, String dbname, SQLiteDatabase.CursorFactory factory,int version)
    {
        super(context,dbname,null,1);
    }

    //数据库第一次创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        //CREATE TABLE person(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))
        StringBuffer mealSql = new StringBuffer();
        mealSql.append(CREATE_TABLE_START_SQL).append(" meals ").append(" ( ");
        mealSql.append(" id").append(CREATE_TABLE_PRIMIRY_SQL);
        mealSql.append(" MealID").append(" varchar(32) default \"\" ,");
        mealSql.append(" MealName").append(" varchar(150) default \"\" ,");
        mealSql.append(" MState").append(" varchar(10) default \"\" ,");
        mealSql.append(" TagID").append(" varchar(20) default \"\" ,");
        mealSql.append(" ImgUrl").append(" varchar(250) default \"\" ,");
        mealSql.append(" SellPrice").append(" double  default 0 ,");
        mealSql.append(" ForIndex").append(" double  default 0 ,");
        mealSql.append(" JLUnit").append(" double  default 1 ,");
        mealSql.append(" JLPrice").append(" double  default 0 ");
        mealSql.append(" )");

        db.execSQL(mealSql.toString());
    }
    //软件版本号发生改变时调用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
    }
}

二、类DBManager

public class DBManager {
    private static DBManager instance;
    private static SQLiteOpenHelper mDBHelper;
    private SQLiteDatabase mDatabase;
    private AtomicInteger mOpenCounter = new AtomicInteger();
    /**
     *单例模式，初始化DBManager
     * @return
     */
    public  static  synchronized  DBManager getInstance()
    {
        if(instance==null){
            instance=new DBManager()   ;
        }
        return  instance;
    }

    /**
     *数据库初始化
     * @param context
     */
    public  void init(Context context)
    {
        if(context==null)   {return;}
        if(mDBHelper==null)
        {
            mDBHelper=new DBHelper(context.getApplicationContext(),"meals.db",null,1);
        }
    }
    /**
     * 释放数据库
     */
    public void release() {
        if (mDBHelper != null) {
            mDBHelper.close();
            mDBHelper = null;
        }
        instance = null;
    }
    /**
     * 打开数据库
     */
    public synchronized SQLiteDatabase openDatabase() {
        if (mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            try {
                mDatabase = mDBHelper.getWritableDatabase();
            } catch (Exception e) {
                //Log.e(TAG, "openDatabase e = " + e.getMessage());
                Logger.d("openDatabase e "+e.getMessage());
                mDatabase = mDBHelper.getReadableDatabase();
            }
        }
        return mDatabase;
    }
    /**
     * 关闭数据库
     */
    public synchronized void closeDatabase() {
        if (mOpenCounter.decrementAndGet() == 0) {
            // Closing database
            mDatabase.close();
        }
    }

    private void closeCursor(Cursor cursor) {
        if (cursor != null) {
            try {
                cursor.close();
            } catch (Throwable e) {
                //Log.e(TAG, "closeCursor e = " + e.getMessage());
            }
        }
    }

    /**
     * 保持菜品，如果已经存在菜品编号则更新，否则新增
     * @param meal
     * @return
     */
    public ResponseBean addmeal(dishmeal meal) {

        Cursor cursor =null;
        ResponseBean res = new ResponseBean();
        res.setSucceed(false);
        try {

            if (mDBHelper == null) {
                res.setData("未初始化 mDBHelper");
                return res;
            }
            SQLiteDatabase db = mDBHelper.getReadableDatabase();
            cursor = db.rawQuery("SELECT * FROM meals WHERE MealID = ?",
                    new String[]{meal.getMealID()});

                //存在数据才返回true
                if (cursor == null) {
                    res.setData("cursor==null");
                    return res;
                }
                if (cursor.getCount() > 0) {
                    //存在该菜品,则修改
                    mDatabase = mDBHelper.getWritableDatabase();
                    ContentValues cv = new ContentValues();
                    cv.put("ForIndex", meal.getForIndex());
//                    cv.put("ImgUrl", meal.getImgUrl());
                    cv.put("JLPrice", meal.getJLPrice());
                    cv.put("JLUnit", meal.getJLUnit());
                    cv.put("MealID", meal.getMealID());
                    cv.put("MealName", meal.getMealName());
                    cv.put("MState", meal.getMState());
                    cv.put("SellPrice", meal.getSellPrice());
                    cv.put("TagID", meal.getTagID());
                    //参数依次是表名，修改后的值，where条件，以及约束，如果不指定三四两个参数，会更改所有行
                    int rowId = mDatabase.update("meals", cv, "MealID = ?", new String[]{meal.getMealID()});
                    Logger.d("update meal rowId:"+rowId);
                    if (rowId < 0) {
                        res.setData("更新失败，" + rowId);
                        closeCursor(cursor);
                        //return res;
                    } else {
                        res.setSucceed(true);
                        res.setData("更新数据成功");
                        closeCursor(cursor);
                        //return res;
                    }

                } else {
                    mDatabase = mDBHelper.getWritableDatabase();
                    ContentValues cv = new ContentValues();
                    cv.put("ForIndex", meal.getForIndex());
//                    cv.put("ImgUrl", meal.getImgUrl());
                    cv.put("JLPrice", meal.getJLPrice());
                    cv.put("JLUnit", meal.getJLUnit());
                    cv.put("MealID", meal.getMealID());
                    cv.put("MealName", meal.getMealName());
                    cv.put("MState", meal.getMState());
                    cv.put("SellPrice", meal.getSellPrice());
                    cv.put("TagID", meal.getTagID());
                    long rowId = mDatabase.insert("meals", null, cv);
                    Logger.d("insert meal rowId:"+rowId);
                    if (rowId < 0) {
                        res.setData("插入失败，" + rowId);
                        //closeCursor(cursor);
                        //return res;
                    } else {
                        res.setSucceed(true);
                        res.setData("插入数据成功");
                        //closeCursor(cursor);
                        //return res;
                    }


                }



            //return  res ;

        } catch (Exception ex) {
            res.setData("出错了，" + ex.getMessage());
            //closeCursor(cursor);
        }
        finally {
            closeCursor(cursor);
        }
        return res;
    }

    /**
     * 查询出识别的菜品详细信息
     * @param mlst
     * @return
     */
    public List<dishmeal> getmeal(List<String> mlst) {
        Cursor cursor = null;
        List<dishmeal> meallist = new ArrayList<>();
        try {

            if(mDBHelper==null){
                return  null;
            }
            SQLiteDatabase db=mDBHelper.getReadableDatabase();

            String where =null;//"MealID in (?,?,?)  ";
            String[] whereValue =null;//{"11","10","12"};
            if(mlst!=null && mlst.size()>0) {
                where ="";
                whereValue = mlst.toArray(new String[0]);//. " MealID"
                for(int i=0;i<mlst.size();i++)
                {
                    where+=where==""?"":",";
                    where+="?";
                }
                where=" MealID in ("+where+") ";
            }
//            Logger.d("getmeal where：" +where);
//            Logger.d("getmeal whereValue ：" + JSON.toJSONString(whereValue));

            cursor = db.query("meals", null, where, whereValue, null, null, null);
//            cursor =  db.rawQuery("SELECT * FROM meals ", new String[]{});
//            Logger.d("cursor getCount：" +cursor.getCount());
            while (cursor != null && cursor.getCount() > 0 && cursor.moveToNext()) {
                //int dbId = cursor.getInt(cursor.getColumnIndex("_id"));
                double ForIndex = cursor.getDouble(cursor.getColumnIndex("ForIndex"));
                String ImgUrl = cursor.getString(cursor.getColumnIndex("ImgUrl"));
                double JLPrice = cursor.getDouble(cursor.getColumnIndex("JLPrice"));
                double JLUnit = cursor.getDouble(cursor.getColumnIndex("JLUnit"));

                String MealID = cursor.getString(cursor.getColumnIndex("MealID"));
                String MealName = cursor.getString(cursor.getColumnIndex("MealName"));
                String MState = cursor.getString(cursor.getColumnIndex("MState"));
                double SellPrice = cursor.getDouble(cursor.getColumnIndex("SellPrice"));
                String TagID = cursor.getString(cursor.getColumnIndex("TagID"));


                dishmeal meal=new dishmeal();
                meal.setForIndex(ForIndex);
                meal.setImgUrl(ImgUrl);
                meal.setJLPrice(JLPrice);
                meal.setJLUnit(JLUnit);
                meal.setMealID(MealID);
                meal.setMealName(MealName);
                meal.setMState(MState);
                meal.setSellPrice(SellPrice);
                meal.setTagID(TagID);
                meallist.add(meal);

            }


        } catch (Exception ex) {
            Logger.d("getmeal Error：" +ex.getMessage());

        } finally {
            closeCursor(cursor);
        }
        return meallist;
    }

    public ResponseBean updatemealimg(String mealid,String imgpath) {

        Cursor cursor = null;
        ResponseBean res = new ResponseBean();
        res.setSucceed(false);
        try {

            if (mDBHelper == null) {
                res.setData("未初始化 mDBHelper");
                return res;
            }
            //存在该菜品,则修改
            mDatabase = mDBHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();

            cv.put("ImgUrl", imgpath);
            cv.put("MealID", mealid);

            //参数依次是表名，修改后的值，where条件，以及约束，如果不指定三四两个参数，会更改所有行
            int rowId = mDatabase.update("meals", cv, "MealID = ?", new String[]{mealid});
            Logger.d("update meal rowId:" + rowId);
            if (rowId < 0) {
                res.setData("更新失败，" + rowId);
                closeCursor(cursor);
                //return res;
            } else {
                res.setSucceed(true);
                res.setData("更新数据成功");
                closeCursor(cursor);
                //return res;
            }

        } catch (Exception ex) {
            res.setData("出错了，" + ex.getMessage());
            //closeCursor(cursor);
        } finally {
            closeCursor(cursor);
        }
        return res;
    }



}

三、简单调用

  DBManager.getInstance().init(context);
                List<String> qmlst=new ArrayList<>();
                qmlst.add("2146");
  List<dishmeal> mlst = DBManager.getInstance().getmeal(qmlst);

努力，别失业
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值