Android sqlite

偏执的太偏执、 2022-05-10 05:16 267阅读 0赞

public class DBConst {
        public static final String T_BP_INFOTYPE = "T_INFOTYPE";
        public static final String T_BP_PUSH_INFO = "T_PUSH_INFO";
    
        //创建类型表语句
        public static final String create_infoType = "create table if not exists " + T_INFOTYPE+ "("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "typeId NVARCHAR,"
                + "typeName NVARCHAR,"
                + "parentId NVARCHAR,"
                + "times NVARCHAR)";
    
        //创建信息表语句
        public static final String create_push_info = "create table if not exists " + T_PUSH_INFO+ "("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "infoId NVARCHAR,"
                + "infoTitle NVARCHAR,"
                + "createTime NVARCHAR,"
                + "createUser NVARCHAR,"
                + "createUserName NVARCHAR,"
                + "tenantId NVARCHAR,"
                + "type NVARCHAR)";
    }

public class DBHelper extends SQLiteOpenHelper {
        //数据库实际上是没有被创建或者打开的，
        直到getWritableDatabase() 或者 getReadableDatabase() 方法中的一个被调用时才会进行创建或者打开
       public DBHelper(Context context, String name) {
        //context上下文
        //name  数据库名称
       //factory 创建游标。默认为null
        //version 数据库版本号，默认从1开始
          super(context, name, null, 1);
       }
       
      // 创建表
       @Override
       public void onCreate(SQLiteDatabase db) {
          db.execSQL(DBConst.create_infoType);
          db.execSQL(DBConst.create_push_info);
       }
    
       @Override
       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
       }
    }

public class DBManager {
       private static DBManager dbManager;    //
       private Context context;
       static SQLiteDatabase sqliteDatabase = null;  //
    
       private DBManager(Context context) {
          this.context = context;
       }
    
       /**
        * 得到数据库连接
        * @return
        */
       public synchronized SQLiteDatabase getConnection() {
          try {
             if (sqliteDatabase != null && sqliteDatabase.isOpen()) {
                sqliteDatabase.close();
             }
            //创建或打开可以读/写的数据库（getReadableDatabase（）创建或打开可读的数据库）
             sqliteDatabase = new DBHelper(context, "*****_db")
                   .getReadableDatabase();
          } catch (Exception e) {
             e.printStackTrace();
          }
          return sqliteDatabase;
       }
    
       public synchronized static DBManager getDbInstance(Context context) {
          if (dbManager == null) {
             dbManager = new DBManager(context);
          }
          return dbManager;
       }
    
       /**
        * 插入
        * 
        * @param contentValues
        * @param tableName
        */
       public synchronized long insert(ContentValues contentValues,
             String tableName) {
         long count=-3;
          SQLiteDatabase db = getConnection();
          try {
          count = db.insert(tableName, null, contentValues);
          } catch (Exception e) {
             e.printStackTrace();
          } finally {
             if (db != null) {
                db.close();
             }
          }
          return count;
       }
    
       /**
        * 更新
        * 
        * @param values
        * @param whereClause
        * @param whereArgs
        * @param tableName
        */
       public synchronized long update(ContentValues values, String whereClause,
             String[] whereArgs, String tableName) {
          long count=-3;
          SQLiteDatabase db = getConnection();
          try {
              count=db.update(tableName, values, whereClause, whereArgs);
          } catch (Exception e) {
             e.printStackTrace();
          } finally {
             if (null != db) {
                db.close();
             }
          }
          return count;
       }
    
    
       /**
        * 增删改用sql
        *  @param sql
        */
       public synchronized  long insert(String sql) {
          long count=-3;
          SQLiteDatabase db = getConnection();
          try {
              count=db.execSQL(sql);
          } catch (Exception e) {
             e.printStackTrace();
          } finally {
             if (db != null) {
                db.close();
             }
          }
          return  count;
       }

/**
     *  查询信息
     * @return  list
     */
    public synchronized List<XgMessage> queryPushInfoByType(String type){
        List<XgMessage> pushList = new ArrayList<XgMessage>();
        Cursor cursor = null;
        SQLiteDatabase connection = DBManager.getDbInstance(mContext).getConnection();
    
        try{
           
            //查询两天之内的数据
            cursor = connection.rawQuery("select DISTINCT  * from " + DBConst.T_PUSH_INFO + " where type = " + type+" and date(createTime)>=date('"+time+"')" ,null);
            //便利游标
            while(cursor.moveToNext()){
                XgMessage msg = new XgMessage();
               //获取要取的那一列的数据
                msg.setId(cursor.getString(cursor.getColumnIndex("infoId")));
                msg.setInfoTitle(cursor.getString(cursor.getColumnIndex("infoTitle")));
                msg.setCreateTime(cursor.getString(cursor.getColumnIndex("createTime")));
                String ss=cursor.getString(cursor.getColumnIndex("createTime"));
                msg.setCreateUserName(cursor.getString(cursor.getColumnIndex("createUserName")));
                msg.setTenantId(cursor.getString(cursor.getColumnIndex("tenantId")));
                msg.setType(cursor.getString(cursor.getColumnIndex("type")));
                pushList.add(msg);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(cursor != null)cursor.close();
            if(connection != null)connection.close();
        }
    
        return pushList;
    }

void insertMsginfo( XgMessage xgMsg){
        /**
         * 将信息保存到数据库
         */
        ContentValues cv = new ContentValues();
        cv.put("infoId",xgMsg.getId());
        cv.put("infoTitle",xgMsg.getInfoTitle());
        cv.put("createTime",xgMsg.getCreateTime());
        cv.put("createUser",xgMsg.getCreateUser());
        cv.put("type",xgMsg.getType());
       //这里可以提取出方法
        DBManager dbManager = DBManager.getDbInstance(mContext);
        dbManager.insert(cv, DBConst.T_PUSH_INFO);
    }

本地查看数据库：  
       1.用adb命令导出数据到本地  
         ![20181129181520544.png][]

2.下载安装SQLiteStudio

3.在SQLiteStudio中打开本地数据库，用sql语句进行操作

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNoaW5lXzA3MDc_size_16_color_FFFFFF_t_70][]

[20181129181520544.png]: /images/20220510/6f8f2eafa28d4ffa8c1ce8350d2bc64d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNoaW5lXzA3MDc_size_16_color_FFFFFF_t_70]: /images/20220510/ed0a31e354d84d47a40107410e35804e.png