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 + ''' +
'}';
}
}
网友评论