Android Criminal实例--（7）SQLite数据库

向右看齐 2022-05-18 08:49 132阅读 0赞

**建立数据库需要的属性名称和数据库名**  
使用内部类存储属性名称及数据库名

public class CrimeDbSchema { 
    
        public static final class CrimeTable{ 
            public static final String NAME="crimes";
    
            public static final class cols{ 
    
                public static final String UUID="uuid";
                public static final String TITLE="title";
                public static final String DATE="date";
                public static final String SOLVED="solved";
    
            }
        }
    
    }

**建立数据库**

public class CrimeBaseHelper extends SQLiteOpenHelper { 
    
        private static final int VERSION=1;
        private static final String DATABASE_NAME="crimeBase.db";
    
        public CrimeBaseHelper(Context context) {
            super(context, DATABASE_NAME, null, VERSION);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("create table "+ CrimeTable.NAME+"("+
            "_id integer primary key autoincrement,"+
            CrimeTable.cols.UUID+","+
            CrimeTable.cols.TITLE+","+
            CrimeTable.cols.DATE+","+
            CrimeTable.cols.SOLVED+")");
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
        }
    }

**获取数据库中元素值，将cursor封装，方便直接调用**

public class CrimeCursorWrapper extends CursorWrapper { 
        /** * Creates a cursor wrapper. * * @param cursor The underlying cursor to wrap. */
        public CrimeCursorWrapper(Cursor cursor) {
            super(cursor);
        }
    
        public Crime getCrime(){
            String uuidString=getString(getColumnIndex(CrimeTable.cols.UUID));
            String title=getString(getColumnIndex(CrimeTable.cols.TITLE));
            long date=getLong(getColumnIndex(CrimeTable.cols.DATE));
            int isSolved=getInt(getColumnIndex(CrimeTable.cols.SOLVED));
    
            Crime crime=new Crime(UUID.fromString(uuidString));
            crime.setTitle(title);
            crime.setDate(new Date(date));
            crime.setChecked(isSolved!=0);
    
            return crime;
        }
    }

**修改CrimeLab，用数据库存储数据**

public class CrimeLab {
    
        private Context mContext;
        private SQLiteDatabase mDatabase;
    
        //创建静态单例
        private static CrimeLab sCrimeLab;
    
        public static CrimeLab getCrimeLab(Context context){
            if(sCrimeLab==null){
                sCrimeLab=new CrimeLab(context);
            }
            return sCrimeLab;
        }
    
        //向数组列表中添加对象
        public CrimeLab(Context context){
            mContext=context.getApplicationContext();
            mDatabase=new CrimeBaseHelper(mContext).getWritableDatabase();
         // mCrimes=new ArrayList<>();
    // for(int i=0;i<100;i++){ 
    // Crime crime=new Crime();
    // crime.setTitle("Crime "+i);
    // crime.setChecked(i%2==0);
    // mCrimes.add(crime);
    // }
    
    
        }
    
       // private List<Crime> mCrimes;
    
        //获取列表数组
        public List<Crime> getCrimes() {
            //return mCrimes;
            List<Crime> crimes=new ArrayList<>();
    
            CrimeCursorWrapper cursor=queryCrime(null,null);
    
            try{
                cursor.moveToFirst();
                while(!cursor.isAfterLast()){
                    crimes.add(cursor.getCrime());
                    cursor.moveToNext();
                }
            }finally {
                cursor.close();
            }
    
            return crimes;
        }
    
        //返回列表数组中指定对象
        public Crime getCrime(UUID id){
    // for(Crime crime:mCrimes){ 
    // if(crime.getId().equals(id)){ 
    // return crime;
    // }
    // }
           // return null;
    
            CrimeCursorWrapper cursor=queryCrime(CrimeTable.cols.UUID+"=?",new String[]{id.toString()});
    
            try{
                if(cursor.getCount()==0){
                    return null;
                }
                cursor.moveToFirst();
                return  cursor.getCrime();
            }finally {
                cursor.close();
            }
    
    
    
        }
    
        public void addCrime(Crime c){
           // mCrimes.add(c);
    
            ContentValues values=getContentValues(c);
            mDatabase.insert(CrimeTable.NAME,null,values);
        }
    
    
        private static ContentValues getContentValues(Crime crime){
            ContentValues values=new ContentValues();
            values.put(CrimeTable.cols.UUID,crime.getId().toString());
            values.put(CrimeTable.cols.TITLE,crime.getTitle());
            values.put(CrimeTable.cols.DATE,crime.getDate().getTime());
            values.put(CrimeTable.cols.SOLVED,crime.isChecked() ? 1:0);
    
            return values;
    
        }
    
        public void updateCrime(Crime crime){
            String uuidString=crime.getId().toString();
            ContentValues values=getContentValues(crime);
            mDatabase.update(CrimeTable.NAME,values,CrimeTable.cols.UUID+ "=?",new String[]{uuidString});
        }
    
        private CrimeCursorWrapper queryCrime(String whereClause, String[] whereArgs){
            Cursor cursor=mDatabase.query(CrimeTable.NAME,null,whereClause,whereArgs,null,null,null);
            return new CrimeCursorWrapper(cursor);
        }
    }