SQLiteOpenHelper数据库增删改查和数据库升级操作

public class DbHelper extends SQLiteOpenHelper {
private Context context;
public DbHelper(Context context){
super(context,DbManager.DB_NAME,null,DbManager.DB_VERSION);

}
public DbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    // 创建数据库
    String ct_stutent = "create table "+DbManager.StutentsTABLE.TABLE_NAME+"("+DbManager.StutentsTABLE.T_ID+" integer primary key autoincrement ,"+DbManager.StutentsTABLE.S_NAME+" varchar(255) ,"+DbManager.StutentsTABLE.S_WEIGHT+" varchar(255) ,"+DbManager.StutentsTABLE.S_HEIGHT+" varchar(255) ,"+DbManager.StutentsTABLE.S_GRADE+" varchar(255));";
    sqLiteDatabase.execSQL(ct_stutent);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    // 更新数据库
    if (i1 > i && i1 == DbManager.DB_VERSION) {
        String s2 = "alter table "+ DbManager.StutentsTABLE.TABLE_NAME+" add " + DbManager.StutentsTABLE.S_IDADD +" varchar(255)";
        sqLiteDatabase.execSQL(s2);
    }

}

}

public class DbManager {
public static String DB_NAME = "nade"; // 数据库名称
public static int DB_VERSION = 2; // 数据库版本
// stutent 表相关
public interface StutentsTABLE {
String TABLE_NAME = "stutents"; // 表名称

    String T_ID = "_id";
    String S_NAME = "name";
    String S_HEIGHT = "height";
    String S_WEIGHT = "weight";
    String S_GRADE = "grade";
    String S_IDADD = "idaddress";
}

}

public class TableStutentEvent {

private DbHelper dbHelper;


public TableStutentEvent(Context context) {
    createSutent(context);
}
// 创建数据库
public void createSutent(Context context){
    if (dbHelper == null) {
        dbHelper = new DbHelper(context);
    }
}
// 关闭数据库
public void closeDb(){
    if (dbHelper != null) {
        dbHelper.close();
    }
}
    // 批量插入
public void insertList(List<Stutent> list){
    if (list == null) {
        return;
    }
    for (int i = 0; i < list.size(); i++) {
        insert(list.get(i));
    }
}
// 单一插入
public void insert(Stutent stutent){
    if (stutent == null) {
        return;
    }
    if (dbHelper == null) {
        return;
    }
    SQLiteDatabase write = dbHelper.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(DbManager.StutentsTABLE.S_NAME, TextUtils.isEmpty(stutent.getName()) ? "" : stutent.getName());
    values.put(DbManager.StutentsTABLE.S_HEIGHT, TextUtils.isEmpty(stutent.getHeight()) ? "" : stutent.getHeight());
    values.put(DbManager.StutentsTABLE.S_WEIGHT, TextUtils.isEmpty(stutent.getWeight()) ? "" : stutent.getWeight());
    values.put(DbManager.StutentsTABLE.S_GRADE, TextUtils.isEmpty(stutent.getGrade()) ? "" : stutent.getGrade());
    values.put(DbManager.StutentsTABLE.S_IDADD, TextUtils.isEmpty(stutent.getIdAddress()) ? "" : stutent.getIdAddress());
    write.insert(DbManager.StutentsTABLE.TABLE_NAME,null,values);
    write.close();
}
    // 查询
public List<Stutent> query(){
    List<Stutent> list = new ArrayList<>();
    if (dbHelper != null) {
        SQLiteDatabase write = dbHelper.getWritableDatabase();
        Cursor cursor = write.query(DbManager.StutentsTABLE.TABLE_NAME, null, null, null, null, null,   "_id desc ");
        while (cursor.moveToNext()) {
            String name = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_NAME));
            String weight = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_WEIGHT));
            String height = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_HEIGHT));
            String grade = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_GRADE));
            String id = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.T_ID));
            Stutent stutent = new Stutent();
            if (!TextUtils.isEmpty(cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_IDADD)))) {
                String idadd = cursor.getString(cursor.getColumnIndex(DbManager.StutentsTABLE.S_IDADD));
                stutent.setIdAddress(idadd);
            }
            stutent.setName(name);
            stutent.setWeight(weight);
            stutent.setHeight(height);
            stutent.setGrade(grade);
            stutent.setId(id);
            list.add(stutent);
        }
        write.close();
    }
    return list;
}
    // 删除
public void delete(String id){
    if (dbHelper == null) {
        return;
    }
    if (TextUtils.isEmpty(id)) {
        return;
    }
    SQLiteDatabase write = dbHelper.getWritableDatabase();
    write.delete(DbManager.StutentsTABLE.TABLE_NAME,DbManager.StutentsTABLE.T_ID+"=?",new String[]{id});
    write.close();
}
// 批量删除
public void deleteList(List<String> list){
    if (dbHelper == null) {
        return;
    }
    if (list == null) {
        return;
    }
    SQLiteDatabase write = dbHelper.getWritableDatabase();
    write.delete(DbManager.StutentsTABLE.TABLE_NAME,DbManager.StutentsTABLE.T_ID+"=?", (String[]) list.toArray());
    write.close();
}
    // 修改数据
public void updata(String key,String value,String id){
    if (dbHelper == null) {
        return;
    }
    if (TextUtils.isEmpty(key)) {
        return;
    }
    if (TextUtils.isEmpty(id)) {
        return;
    }
    SQLiteDatabase write = dbHelper.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(key,value);
    write.update(DbManager.StutentsTABLE.TABLE_NAME,values,DbManager.StutentsTABLE.T_ID+"=?",new String[]{id});
    write.close();
}

    // 数据库升级后 清除数据
public void dbVersionUpDataClearData(){
    for (int i = 0; i < query().size(); i++) {
        if (TextUtils.isEmpty(query().get(i).getIdAddress())) {
            Log.d("nade", "onCreate: 删除了不符合要求的数据="+query().get(i).toString()+"\n");
            delete(query().get(i).getId());
        }
    }
}

}

    TableStutentEvent event = new TableStutentEvent(this);
    List<Stutent> list = new ArrayList<>();
    Stutent s1 = new Stutent();
    s1.setGrade("1");
    s1.setName("张三");
    s1.setHeight("181");
    s1.setWeight("51");
    s1.setIdAddress("北京市.紫禁城1号");
    list.add(s1);

    Stutent s2 = new Stutent();
    s2.setGrade("2");
    s2.setHeight("182");
    s2.setName("李四");
    s2.setWeight("52");
    s2.setIdAddress("北京市.紫禁城2号");
    event.insert(s2);
    Stutent s3 = new Stutent();
    s3.setGrade("3");
    s3.setName("王二");
    s3.setHeight("183");
    s3.setWeight("53");
    list.add(s3);
    Stutent s4 = new Stutent();
    s4.setGrade("4");
    s4.setName("麻子");
    s4.setHeight("184");
    s4.setWeight("54");
    list.add(s4);
    Stutent s5 = new Stutent();
    s5.setGrade("5");
    s5.setName("nade");
    s5.setHeight("185");
    s5.setWeight("55");
    list.add(s5);

    // event.insert(s1);


   // event.insertList(list);


    List<Stutent> query = event.query();
    for (int i = 0; i < query.size(); i++) {
        Log.d("nade", "onCreate: 添加后查询所有数据="+query.get(i).toString()+"\n");
    }
    event.updata(DbManager.StutentsTABLE.S_NAME,"我是爸爸","1");
    List<Stutent> query1 = event.query();
    for (int i = 0; i < query1.size(); i++) {
        Log.d("nade", "onCreate: 更改后查询搜友数据="+query1.get(i).toString()+"\n");
    }

    event.delete("2");
    List<Stutent> query2 = event.query();
    for (int i = 0; i < query2.size(); i++) {
        Log.d("nade", "onCreate: 删除后查询所有数据="+query2.get(i).toString()+"\n");
    }

    event.dbVersionUpDataClearData();

    List<Stutent> query3 = event.query();
    for (int i = 0; i < query3.size(); i++) {
        Log.d("nade", "onCreate: 数据库升级后查询所有数据="+query3.get(i).toString()+"\n");
    }
    event.insertList(list);
    List<Stutent> query4 = event.query();
    for (int i = 0; i < query4.size(); i++) {
        Log.d("nade", "onCreate: 数据库升级后添加有效查询所有数据="+query4.get(i).toString()+"\n");
    }
}

public class Stutent {
String id;
String name;
String weight;
String height;
String grade;
String idAddress;
@Override
public String toString() {
return "Stutent{" +
"id='" + id + ''' +
", name='" + name + ''' +
", weight='" + weight + ''' +
", height='" + height + ''' +
", grade='" + grade + ''' +
", idAddress='" + idAddress + ''' +
'}';
}
}