（笔记）关于SQLite简单数据存储

前言：总结关于SQLite的数据存储以及增删改查。

步骤：

首先： 创建一个DatebaseHelper继承SQLiteOpenHelper,其中至少需要实现三个方法： 构造函数、onCreate、onUpgrade。

1、定义数据库名、表名、版本号等全局静态变量。

   private static final String DATABASE_NAME = "crime.db";            
   public static final String TABLE_NAME = "crime_table";
   private static final int VERSION = 1;

2、构造函数，传入四个参数：Context对象，数据库名字name，操作数据库的Cursor对象，版本号version

public DatebaseHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }  

3、在onCreate创建数据库时，创建数据表table

 @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createDatebase = "create table " + TABLE_NAME
                + " (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + crime_title + " TEXT,"
                + crime_date + " TEXT,"
                + crime_solved + " TEXT,"
                + crime_user + " TEXT)";
        sqLiteDatabase.execSQL(createDatebase);
    }

4、onUpgrade用于数据库升级

@Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
 //用于升级数据库，只需要在创建本类对象时传入一个比之前创建传入的version大的数即可。
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(sqLiteDatabase);
    }

5、全局定义数据库表字段

 public static final String crime_id = "ID";
    public static final String crime_title = "NAME";
    public static final String crime_date = "DATE";
    public static final String crime_solved = "SOLVED";
    public static final String crime_user = "USER";

6、插入数据

 public void insertCrime(CrimeBean crimeBean) {
        SQLiteDatabase db = getWritableDatabase();
        //实例化一个ContentValues用来装载插入的数据
        ContentValues contentValues = new ContentValues();
       //添加数据
        contentValues.put(crime_title, crimeBean.getName());
        contentValues.put(crime_date, crimeBean.getDate().getTime());
        contentValues.put(crime_solved, crimeBean.isSolved());
        contentValues.put(crime_user, crimeBean.getUser());
        db.insert(TABLE_NAME, null, contentValues);//执行插入操作
        db.close();
    }   

7、更新数据

 public void updateCrime(CrimeBean crimeBean) {
        SQLiteDatabase db;
        db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(crime_id, crimeBean.getId());
        contentValues.put(crime_title, crimeBean.getName());
        contentValues.put(crime_date, crimeBean.getDate().getTime());
        contentValues.put(crime_solved, crimeBean.isSolved());
        contentValues.put(crime_user, crimeBean.getUser());
       String whereClause = "ID = ?";//修改条件
       String[] whereArgs = {crimeBean.getId() + ""};//修改条件的参数
        db.update(TABLE_NAME, contentValues, whereClause ,whereArgs );
        db.close();
    }   

8、删除数据

   private boolean deleteCrime(int id) {
        SQLiteDatabase db = getWritableDatabase();
        return db.delete(TABLE_NAME, "ID = ?", new String[]{id + ""}) > 0;
    }

9、查询所有数据

public List<CrimeBean> getAllCrimeBean() {
        List<CrimeBean> list = new ArrayList<>();
        String sql = "select * from " + TABLE_NAME;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
         // 始终让cursor指向数据库表的第1行记录
        cursor.moveToFirst();
        while (cursor.moveToNext()) {
            CrimeBean crimeBean = new CrimeBean();
            String name = cursor.getString(cursor.getColumnIndex(crime_title));
            long date = cursor.getLong(cursor.getColumnIndex(crime_date));
            int solved = cursor.getInt(cursor.getColumnIndex(DatebaseHelper.crime_solved));
            String user=cursor.getString(cursor.getColumnIndex(crime_user));
            crimeBean.setId(cursor.getInt(0));
            crimeBean.setName(name);
            crimeBean.setDate(new Date(date));
            crimeBean.setSolved(solved != 0);
            crimeBean.setUser(user);
            list.add(crimeBean);
        }
        cursor.close();
        db.close();
        return list;
    }

10、根据id查询数据

        CrimeBean crimeBean = new CrimeBean();
        if (param == -1) {
            return crimeBean;
        }
        String sql = "select * from " +TABLE_NAME;
        sql += " where ID=" + param;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        cursor.moveToFirst();
        String name=cursor.getString(cursor.getColumnIndex(crime_title));
        long date=cursor.getLong(cursor.getColumnIndex(crime_date));
        int solved=cursor.getInt(cursor.getColumnIndex(crime_solved));
        String user=cursor.getString(cursor.getColumnIndex(crime_user));
        crimeBean.setId(cursor.getInt(0));
        crimeBean.setName(name);
        crimeBean.setDate(new Date(date));
        crimeBean.setSolved(solved!=0);
        crimeBean.setUser(user);
        cursor.close();
        return crimeBean;
    }   

然后创建实体类

public class CrimeBean implements Serializable{
    private int id;
    private String name;
    private Date mDate;
    private boolean solved;
    private String user;
    public CrimeBean(){
        //this(UUID.randomUUID());
    }

    @Override
    public String toString() {
        return "CrimeBean{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", mDate=" + mDate +
                ", solved=" + solved +
                ", user='" + user + '\'' +
                '}';
    }

    public CrimeBean(int id){
       id=id;
        mDate=new Date();
    }
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getDate() {
        return mDate;
    }

    public void setDate(Date date) {
        mDate = date;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public boolean isSolved() {
        return solved;
    }

    public void setSolved(boolean solved) {
        this.solved = solved;
    }

最后在Activity中的调用

1、添加数据

DatebaseHelper mHelper=new DatebaseHelper(MainActivity.this);
String title=etMainInputTitle.getText().toString();
                Date date=new Date();
                mCrimeBean.setName(title);
                mCrimeBean.setDate(date);
                mCrimeBean.setSolved(isSovled);
                mHelper.insertCrime(mCrimeBean);

2、修改数据

   String Title = mTitle.getText().toString();
                Date date = new Date();
                mCrimeBean.setId(crimeId);
                mCrimeBean.setName(Title);
                mCrimeBean.setDate(date);
                mCrimeBean.setUser(user);
                DatebaseHelper m = new DatebaseHelper(getActivity());
                m.updateCrime(mCrimeBean);

3、删除数据

 DatebaseHelper datebaseHelper=new DatebaseHelper(mContext);
                        mBeanList.remove(crimeBean);
                        datebaseHelper.deleteCrimeById(crimeBean.getId());

4、查询数据库表中所有数据

  DatebaseHelper datebaseHelper=new DatebaseHelper(getActivity());
   List<CrimeBean> list=datebaseHelper.getAllCrimeBean();

5、根据id查询其中一个数据

 DatebaseHelper datebaseHelper = new DatebaseHelper(getActivity());
 mCrimeBean = datebaseHelper.getCrimeBeanById(crimeId);

总结：以上是SQLite数据存储的主要使用，SQLite知识点有：

1、getReadableDatabase()方法返回数据库是一个只读的
2、getWriteableDatabase()方法获得是一个可读写的数据库对象
3、ContentValues用来装载插入、更新时候数据
4、query和rawQuery两种查询语句
5、Cursor（游标）来进行数据库记录的操作，提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，Cursor实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制

image