android SQLite的使用

左手的ㄟ右手 2022-07-16 12:46 283阅读 0赞

public class NewsDBApi extends SQLiteOpenHelper{
    
        private static final String DB_NAME = "newsData.db"; //数据库名称
        private static final int version = 1; //数据库版本
        private SQLiteDatabase database;
        private ArrayList<Data_Map_News> list = new ArrayList<Data_Map_News>();
        public NewsDBApi(Context context){
            super(context,DB_NAME,null,version);
        }
    
        /*
       **返回数据表所有的数据
       * @params tablename 数据库表名
       * @return list
       */
        public ArrayList<Data_Map_News> queryData(String tablename){
            database = this.getReadableDatabase();
            //Log.i("查询数据库路径：",database.getPath());
            Cursor c = database.query(tablename,null,null,null,null,null,"mTime desc ");//查询并获得游标
    
            while (c.moveToNext()){
                    int id = c.getInt(c.getColumnIndex("_id"));
                    String mTitle = c.getString(c.getColumnIndex("mTitle"));
                    String mFrom = c.getString(c.getColumnIndex("mFrom"));
                    String mImage = c.getString(c.getColumnIndex("mImage"));
                    String mUrl = c.getString(c.getColumnIndex("mUrl"));
                    long mTime = c.getLong(c.getColumnIndex("mTime"));
                    list.add(new Data_Map_News(mTitle,new Date(mTime),mFrom,mImage,mUrl));
                }
    
            c.close();
            return  list;
        }
        /*
        **返回数据表最近更新的数据
        * @params tablename 数据库表名
        * @params lasttime 上次最后插入的一条数据
        * @return list
        */
        public ArrayList<Data_Map_News> queryUpdateData(String tablename,long lasttime){
            database = this.getReadableDatabase();
            //Log.i("查询数据库路径：",database.getPath());
            Cursor c = database.rawQuery("select * from "+tablename+" where "+lasttime+"> mTime order by mTime desc",null);
    
            while (c.moveToNext()){
                int id = c.getInt(c.getColumnIndex("_id"));
                String mTitle = c.getString(c.getColumnIndex("mTitle"));
                String mFrom = c.getString(c.getColumnIndex("mFrom"));
                String mImage = c.getString(c.getColumnIndex("mImage"));
                String mUrl = c.getString(c.getColumnIndex("mUrl"));
                long mTime = c.getLong(c.getColumnIndex("mTime"));
                list.add(new Data_Map_News(mTitle,new Date(mTime),mFrom,mImage,mUrl));
            }
    
            c.close();
            return  list;
        }
        /*
         **返回数据表的数量
         * @params tablename 数据库表名
         * @return int
         */
        public int querysum(String tablename){
            database = this.getReadableDatabase();
            Cursor c = database.rawQuery("select * from "+tablename,null);
            Log.i("查询数据数量：",c.getCount()+"");
            return  c.getCount();
        }
        /*
         **查询最新插入数据库的一条数据
         * @params tablename 数据库表名
         * @return long
         */
        public long querylast(String tablename){
            ArrayList<Data_Map_News> list1 = queryData(tablename);
           // Log.i("查询数据库：",list1.get(0).getmTime().getTime()+"");
            return list1.get(0).getmTime().getTime();
        }
        /*
       **插入数据
       * @params tablename 数据库表名
       * @params mTime和strings 需要插入的数据
       * @return int
       */
        public void insertData(String tablename,long mTime,String... strings){
            database = this.getWritableDatabase();
            //Log.i("插入数据库路径：",database.getPath());
            //实例化一个ContentValues用来装载待插入的数据cv.put("username","Jack Johnson");//添加用户名
            ContentValues cv = new ContentValues();
                cv.put("mTitle",strings[0]);
                cv.put("mFrom",strings[1]);
                cv.put("mImage",strings[2]);
                cv.put("mUrl",strings[3]);
                cv.put("mTime",mTime);
                database.insert(tablename,null,cv);//执行插入操作
    
        }
        /*
          ** 根据mTime字段删除数据
         */
        public void deleteData(long mTime, String tablename){
            database = this.getWritableDatabase();
            String sql = "delete from "+tablename+" where mTime <= "+mTime;//删除操作的SQL语句
            database.execSQL(sql);//执行删除操作
        }
        /*
         **创建多个数据表
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("create table t_sectors( _id integer primary key autoincrement" +
                    ",mTitle not null,mFrom not null,mTime not null,mImage not null,mUrl not null)");
            db.execSQL("create table t_domestic( _id integer primary key autoincrement" +
                    ",mTitle not null,mFrom not null,mTime not null,mImage not null,mUrl not null)");
            
        }
        /*
          **更新数据库
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
    //        db.execSQL(sql);
    //        onCreate(db);
        }
        /*
         * 删除数据库
         */
        public static void  deleteDB(String dbName,Context context){
            File file = new File("/data/data/"+context.getPackageName()+"/databases/"+dbName);
            file.delete();
        }
    }