android之SQLiteOpenHelper进阶版(增删改查)

public class SQLiteHelperActivity extends AppCompatActivity implements View.OnClickListener {

    private TextView et_name;
    private EditText et_age;
    private EditText et_height;
    private EditText et_weight;
    private CheckBox ck_married;
    private UserDBHelper mHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_helper);
        et_name = findViewById(R.id.et_name);
        et_age = findViewById(R.id.et_age);
        et_height = findViewById(R.id.et_height);
        et_weight = findViewById(R.id.et_weight);
        ck_married = findViewById(R.id.ck_married);


        findViewById(R.id.bth_save).setOnClickListener(this);
        findViewById(R.id.bth_delete).setOnClickListener(this);
        findViewById(R.id.bth_updata).setOnClickListener(this);
        findViewById(R.id.bth_query).setOnClickListener(this);
    }

    @Override
    protected void onStart() {
        super.onStart();
        //获得数据库帮助器的实例
        mHelper = UserDBHelper.getInstance(this);
        //打开数据库帮助器的写连接
        mHelper.openWriteLink();
        //打开数据库的读连接
        mHelper.openReadLink();
    }

    @Override
    protected void onStop() {
        super.onStop();
        //关闭数据库连接
       mHelper.closeLink();
    }

    @Override
    public void onClick(View v) {
        String name=et_name.getText().toString();
        String age=et_age.getText().toString();
        String height=et_height.getText().toString();
        String weight=et_weight.getText().toString();
        User user=null;

        switch (v.getId()){
            case R.id.bth_save:
                //以下声明一个用户信息对象,并填写它的各字段值
                user=new User(
                        name,
                        Integer.parseInt(age),
                        Long.parseLong(height),
                        Float.parseFloat(weight),
                        ck_married.isChecked());
              //我在这里创建了 User 对象并尝试插入数据库，但是实际上我执行了两次插入操作：
              //mHelper.insert(user);
              if(mHelper.insert(user)>0){
                  ToastUtil.show(this,"添加成功");
              }
                break;
            case R.id.bth_delete:
                if (mHelper.deleteByName(name)>0){
                    ToastUtil.show(this,"删除成功");
                }
                break;
            case R.id.bth_updata:
                user=new User(name,
                        Integer.parseInt(age),
                        Long.parseLong(height),
                        Float.parseFloat(weight),
                        ck_married.isChecked());
                if (mHelper.updata(user)>0){
                    ToastUtil.show(this,"修改成功");
                }
                break;
            case R.id.bth_query:
               // List<User> list = mHelper.queryALL();
                List<User> list = mHelper.queryByName(name);
                for (User u : list) {
                    Log.d("ning",u.toString());
                }
                break;
        }
    }
}

public class UserDBHelper extends SQLiteOpenHelper {

    private  static  final  String DB_NAME="user.db";
    private  static  final  String TABLE_NAME="user_info";
    private  static  final  int DB_VERISON=1;

    private SQLiteDatabase mRDB=null;
    private SQLiteDatabase mWDB=null;

    private  static  UserDBHelper mHelper=null;


    private  UserDBHelper(Context context){
        super(context,DB_NAME,null,DB_VERISON);
    }

    //利用单例模式获取数据库帮助器的唯一实例
    public static UserDBHelper getInstance(Context context){
    if(mHelper==null){
       mHelper=new UserDBHelper(context);
    }
    return mHelper;
    }
   //打开数据库的读连接
    public  SQLiteDatabase openReadLink(){
        if(mRDB==null || !mRDB.isOpen()){
            mRDB=mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    //打开数据库的写连接
    public  SQLiteDatabase openWriteLink(){
        if(mWDB==null || !mWDB.isOpen()){
            mWDB=mHelper.getWritableDatabase();
        }
        return mWDB;
    }


    //关闭数据的连接
    public void closeLink(){
    if(mRDB !=null && mRDB.isOpen()){
        mRDB.close();
        mRDB=null;
    }else if(mWDB !=null && mWDB.isOpen()){
        mWDB.close();
    }
    }

    //创建数据库,执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql="CREATE TABLE IF NOT EXISTS "+TABLE_NAME+"("+
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
                "name VARCHAR NOT NULL, "+
                "age  INTEGER NOT NULL, "+
                "height LONG NOT NULL, "+
                "weight Float NOT NULL, "+
                "married  INTEGER NOT NULL);";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insert(User user){
        ContentValues values=new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);

        return mWDB.insert(TABLE_NAME,null,values);
    }
    public long deleteByName(String name){
        //删除所有的
        //mWDB.delete(TABLE_NAME,"1=1",null);
      return mWDB.delete(TABLE_NAME,"name=? ",new String[]{name});
    }

    public  long updata(User user){
        ContentValues values=new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);
      return   mWDB.update(TABLE_NAME,values,"name=?",new String[]{user.name});
    }
    public List<User> queryALL(){
        //创建一个集合
      List<User>list=new ArrayList<>();
        //在创建一个游标,执行记录查询动作,该语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null);
        //循环取出游标指向的每条记录

        while(cursor.moveToNext()){
               User newUser = new User();
               newUser.id=cursor.getInt(0);
               newUser.name=cursor.getString(1);
               newUser.age=cursor.getInt(2);
               newUser.height=cursor.getLong(3);
               newUser.weight=cursor.getFloat(4);
               //SQLite没有布尔类型,用0表示false,用1表示true
               newUser.married=(cursor.getInt(5)==0)?false:true;
               list.add(newUser);
        }
        return list;
    }
    public List<User> queryByName(String name){
        //创建一个集合
        List<User>list=new ArrayList<>();
        //在创建一个游标,执行记录查询动作,该语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null);
        //循环取出游标指向的每条记录

        while(cursor.moveToNext()){
            User newUser = new User();
            newUser.id=cursor.getInt(0);
            newUser.name=cursor.getString(1);
            newUser.age=cursor.getInt(2);
            newUser.height=cursor.getLong(3);
            newUser.weight=cursor.getFloat(4);
            //SQLite没有布尔类型,用0表示false,用1表示true
            newUser.married=(cursor.getInt(5)==0)?false:true;
            list.add(newUser);

        }
        return list;

    }
}

现在是SQLite的数据:

当我们进行添加的时候:

当我们进行删除的时候:

当我们进行修改的时候:

当我们进行查询的时候: