当前位置：首页 > news >正文

Android Studio SQLite Manage

news 来源：原创 2024/9/18 8:50:27

一、类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 databasetry {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 databasemDatabase.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()});//存在数据才返回trueif (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);