Android-SQLiteOpenHelper里增删改查 _增删改

大鹏一日同风起，扶摇直上九万里。这篇文章主要讲述Android-SQLiteOpenHelper里增删改查相关的知识，希望能为你提供帮助。
为什么要写一篇，android-SQLiteOpenHelper里增删改查，的文章呢；
因为之前的方式是：mysqliteOpenHelper（只负责生成打开据库/生成打开表/升级表），在其他端：完成此数据库表的增删改查逻辑处理，这样代码有些分散
现在 MySQLiteOpenHelper（负责生成打开据库 / 生成打开表 / 升级表 / 完成此数据库表的增删改查逻辑处理 / 还有其他表处理功能增加等等），这一个类全包了

MySQLiteOpenHelperStudent
注意事项：继承SQLiteOpenHelper抽象类重写的创表方法，此SQLiteDatabase db 不能关闭

1 package liudeli.datastorage.db; 2 3 import android.content.ContentValues; 4 import android.content.Context; 5 import android.database.Cursor; 6 import android.database.sqlite.SQLiteDatabase; 7 import android.database.sqlite.SQLiteOpenHelper; 8 import android.util.Log; 9 10 import java.util.List; 11 12 import liudeli.datastorage.entity.Student; 13 14 /** 15*数据库表管理 16*MySQLiteOpenHelperStudent 17*（负责生成打开据库 / 生成打开表 / 升级表 / 18*完成此数据库表的增删改查逻辑处理 19*/ 还有其他表处理功能增加等等） 20*/ 21 public class MySQLiteOpenHelperStudent extends SQLiteOpenHelper { 22 23private final static String TAG = MySQLiteOpenHelperStudent.class.getSimpleName(); 24 25/** 26* 数据库名称 27*/ 28private final static String DB_NAME = "student_info_manager.db"; 29 30/** 31* 表名 32*/ 33private static final String TABLE_NAME = "studentTable"; 34 35/** 36* 定义单例模式，可以被多次地方多次调用 37*/ 38private static MySQLiteOpenHelperStudent mySQLiteOpenHelperStudent; 39 40public static MySQLiteOpenHelperStudent getInstance(Context context) { 41if (null == mySQLiteOpenHelperStudent) { 42synchronized (MySQLiteOpenHelperStudent.class) { 43if (null == mySQLiteOpenHelperStudent) { 44mySQLiteOpenHelperStudent = new MySQLiteOpenHelperStudent(context, DB_NAME, null, 1); 45} 46} 47} 48return mySQLiteOpenHelperStudent; 49} 50 51private MySQLiteOpenHelperStudent(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { 52super(context, name, factory, version); 53} 54 55 56/** 57* 创表方法，注意：?? 传递进来的 SQLiteDatabase database 不能关闭 58* @param database 59*/ 60private void createTable(SQLiteDatabase database) { 61String createTableSQL ="CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (ID INTEGER PRIMARY KEY, " 62+ "name VARCHAR, " 63+ "age INTEGER, " 64+ "hobbyVARCHAR); "; 65try { 66database.execSQL(createTableSQL); 67} catch (Exception e) { 68e.printStackTrace(); 69Log.e(TAG, TAG + "创表异常：" + e.toString()); 70} 71 72/** 73* 注意：?? 74* 这里不能关闭database 75* 一旦关闭就会 Caused by: java.lang.IllegalStateException: 76*attempt to re-open an already-closed object: SQLiteDatabase: 77* 为什么不能在这里关闭呢？ 78* 答：因为这个database，是在 public void onCreate -> 传递过来的 79*/ 80/*finally { 81if (null != database) { 82database.close(); 83} 84}*/ 85} 86 87/** 88* 删除表，会把表给删除，慎用 89* drop 表 90*/ 91public void dropTable() { 92SQLiteDatabase database = getWritableDatabase(); 93String dropSQL = "drop table if exists " + TABLE_NAME; 94try { 95database.execSQL(dropSQL); 96} catch (Exception e) { 97e.printStackTrace(); 98Log.d(TAG, "drop异常：" + e.toString()); 99} finally { 100if (null != database) { 101database.close(); 102} 103} 104} 105 106/** 107* 清除表的数据 108*/ 109public void cleanUpData() { 110 111// delete from TableName; //清空数据 112 113SQLiteDatabase database = getWritableDatabase(); 114String cleanUpDataSQL = "delete from " + TABLE_NAME; 115try { 116database.execSQL(cleanUpDataSQL); 117} catch (Exception e) { 118e.printStackTrace(); 119Log.d(TAG, "清除表的数据异常：" + e.toString()); 120} finally { 121if (null != database) { 122database.close(); 123} 124} 125} 126 127/** 128* 插入多条数据 129* @param students 传递Student集合 130*/ 131public void insertData(List< Student> students) { 132SQLiteDatabase database = getWritableDatabase(); 133try { 134for (Student student : students) { 135ContentValues contentValues = new ContentValues(); 136contentValues.clear(); 137contentValues.put("name", student.getName()); 138contentValues.put("age", student.getName()); 139contentValues.put("hobby", student.getHobby()); 140database.insert(TABLE_NAME, "_id", contentValues); 141} 142} catch (Exception e) { 143e.printStackTrace(); 144Log.e(TAG, "insert多条异常：" + e.toString()); 145} finally { 146if (null != database) { 147database.close(); 148} 149} 150} 151 152/** 153* 插入单条 154* @param contentValues 传递ContentValues 155*/ 156public void insertData(ContentValues contentValues) { 157SQLiteDatabase database = getWritableDatabase(); 158try { 159database.insert(TABLE_NAME, "_id", contentValues); 160} catch (Exception e) { 161e.printStackTrace(); 162Log.e(TAG, "insert单条异常：" + e.toString()); 163} finally { 164if (null != database) { 165database.close(); 166} 167} 168} 169 170/** 171* 查询需要的列名 172*/ 173private String[] columns = new String[]{"name", "age", "hobby"}; 174 175/** 176* 查询第一条数据 177* @return 返回Student实体 178*/ 179public Student selectData() { 180SQLiteDatabase database = getReadableDatabase(); 181Cursor cursor = null; 182Student student = null; 183try { 184cursor = database.query(TABLE_NAME, columns, null, null, null, null, null); 185if (cursor.moveToFirst()) { 186String name = cursor.getString(cursor.getColumnIndex("name")); 187int age = cursor.getInt(cursor.getColumnIndex("age")); 188String hobby = cursor.getString(cursor.getColumnIndex("hobby")); 189 190student = new Student(name, age, hobby); 191} 192} catch (Exception e) { 193e.printStackTrace(); 194Log.e(TAG, "select异常：" + e.toString()); 195} finally { 196if (null != database) { 197database.close(); 198} 199 200if (null != cursor) cursor.close(); 201} 202return student; 203} 204 205/** 206* 判断第一条数据是否存在 207* @return 存在返回true，否则返回false 208*/ 209public boolean isMoveToFirst() { 210boolean result; 211SQLiteDatabase database = getReadableDatabase(); 212Cursor cursor = database.query(TABLE_NAME, 213columns, 214null, 215null, 216null, 217null, 218null); 219result = cursor.moveToFirst(); 220database.close(); 221cursor.close(); 222return result; 223} 224 225/** 226*.........还可以增加很多操作表相关的行为 227*/ 228 229/** 230* 继承SQLiteOpenHelper抽象类重写的创表方法，此SQLiteDatabase db 不能关闭 231* @param db 232*/ 233@Override 234public void onCreate(SQLiteDatabase db) { 235createTable(db); 236} 237 238/** 239* 继承SQLiteOpenHelper抽象类重写的升级方法 240* @param db 241* @param oldVersion 242* @param newVersion 243*/ 244@Override 245public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 246} 247 }

继承SQLiteOpenHelper抽象类重写的创表方法，此SQLiteDatabase db 不能关闭

/** * 创表方法，注意：?? 传递进来的 SQLiteDatabase database 不能关闭 * @param database */ private void createTable(SQLiteDatabase database) { String createTableSQL ="CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (ID INTEGER PRIMARY KEY, " + "name VARCHAR, " + "age INTEGER, " + "hobbyVARCHAR); "; try { database.execSQL(createTableSQL); } catch (Exception e) { e.printStackTrace(); Log.e(TAG, TAG + "创表异常：" + e.toString()); }/** * 注意：?? * 这里不能关闭database * 一旦关闭就会 Caused by: java.lang.IllegalStateException: *attempt to re-open an already-closed object: SQLiteDatabase: * 为什么不能在这里关闭呢？ * 答：因为这个database，是在 public void onCreate -> 传递过来的 */ /*finally { if (null != database) { database.close(); } }*/ }

一旦关闭，就会报以下错误：
Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase:

文章图片

文章图片

Student实体：

package liudeli.datastorage.entity; public class Student {private String name; private int age; private String hobby; public Student() { }public Student(String name, int age, String hobby) { this.name = name; this.age = age; this.hobby = hobby; }public String getName() { return name; }public void setName(String name) { this.name = name; }public int getAge() { return age; }public void setAge(int age) { this.age = age; }public String getHobby() { return hobby; }public void setHobby(String hobby) { this.hobby = hobby; }@Override public String toString() { return "Student{" + "name=\'" + name + \'\\\'\' + ", age=" + age + ", hobby=\'" + hobby + \'\\\'\' + \'}\'; } }

测试代码：

package liudeli.datastorage; import android.app.Activity; import android.content.ContentValues; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Toast; import java.util.ArrayList; import java.util.List; import liudeli.datastorage.db.MySQLiteOpenHelperStudent; import liudeli.datastorage.entity.Student; public class MySQLiteActivity extends Activity {private MySQLiteOpenHelperStudent mySQLiteOpenHelperStudent; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_my_sqlite); mySQLiteOpenHelperStudent = MySQLiteOpenHelperStudent.getInstance(this); /** * 初始化数据 */ ContentValues contentValues = new ContentValues(); contentValues.put("name", "刘刘" + System.currentTimeMillis()); contentValues.put("age", 26); contentValues.put("hobby", "爱写博客" + System.currentTimeMillis()); mySQLiteOpenHelperStudent.insertData(contentValues); }/** * 查询 * @param view */ public void query(View view) { Student student = mySQLiteOpenHelperStudent.selectData(); if (student != null) { Log.d("sql", "student.toString:" + student.toString()); } }/** * 是否有第一条数据 * @param view */ public void query1(View view) { Toast.makeText(this, mySQLiteOpenHelperStudent.isMoveToFirst() + "", Toast.LENGTH_LONG).show(); }/** * drop表删除表 * @param view */ public voiddropTable(View view) { mySQLiteOpenHelperStudent.dropTable(); }/** * 插入很多数据 * @param view */ public void installMany(View view) { List< Student> students = new ArrayList< > (); students.add(new Student("李李", 11, "AAAAAAA")); students.add(new Student("李李22", 222, "BBBB")); students.add(new Student("李李33", 333, "CCC")); students.add(new Student("李李44", 444, "DDD")); mySQLiteOpenHelperStudent.insertData(students); }/** * 清除表数据 * @param view */ public void cleanUpData(View view) { mySQLiteOpenHelperStudent.cleanUpData(); } }

测试代码的布局：

< ?xml version="1.0" encoding="utf-8"?> < LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> < Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:onClick="query" android:text="查询" android:layout_weight="0" /> < Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="是否有第一条数据" android:onClick="query1" android:layout_weight="0" /> < Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="drop表删除表" android:onClick="dropTable" android:layout_weight="0" /> < Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="插入很多数据" android:onClick="installMany" android:layout_weight="0" /> < Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:onClick="cleanUpData" android:text="清除表数据" /> < /LinearLayout>

【Android-SQLiteOpenHelper里增删改查】