为什么要写一篇，Android-SQLiteOpenHelper里增删改查，的文章呢；
因为之前的方式是：MySQLiteOpenHelper（只负责 生成打开据库/生成打开表/升级表），在其他端：完成此数据库表的增删改查逻辑处理，这样代码有些分散
现在 MySQLiteOpenHelper（负责 生成打开据库 / 生成打开表 / 升级表 / 完成此数据库表的增删改查逻辑处理 / 还有其他表处理功能增加等等），这一个类全包了
MySQLiteOpenHelperStudent
注意事项：继承SQLiteOpenHelper抽象类 重写的创表方法，此SQLiteDatabase db 不能关闭
package liudeli.datastorage.db; import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log; import java.util.List; import liudeli.datastorage.entity.Student; /**
* 数据库表管理
* MySQLiteOpenHelperStudent
* （负责 生成打开据库 / 生成打开表 / 升级表 /
* 完成此数据库表的增删改查逻辑处理
* / 还有其他表处理功能增加等等）
*/
public class MySQLiteOpenHelperStudent extends SQLiteOpenHelper { private final static String TAG = MySQLiteOpenHelperStudent.class.getSimpleName(); /**
* 数据库名称
*/
private final static String DB_NAME = "student_info_manager.db"; /**
* 表名
*/
private static final String TABLE_NAME = "studentTable"; /**
* 定义单例模式，可以被多次地方多次调用
*/
private static MySQLiteOpenHelperStudent mySQLiteOpenHelperStudent; public static MySQLiteOpenHelperStudent getInstance(Context context) {
if (null == mySQLiteOpenHelperStudent) {
synchronized (MySQLiteOpenHelperStudent.class) {
if (null == mySQLiteOpenHelperStudent) {
mySQLiteOpenHelperStudent = new MySQLiteOpenHelperStudent(context, DB_NAME, null, 1);
}
}
}
return mySQLiteOpenHelperStudent;
} private MySQLiteOpenHelperStudent(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
} /**
* 创表方法，注意：⚠️ 传递进来的 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, "
+ "hobby VARCHAR);";
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();
}
}*/
} /**
* 删除表，会把表给删除，慎用
* drop 表
*/
public void dropTable() {
SQLiteDatabase database = getWritableDatabase();
String dropSQL = "drop table if exists " + TABLE_NAME;
try {
database.execSQL(dropSQL);
} catch (Exception e) {
e.printStackTrace();
Log.d(TAG, "drop异常：" + e.toString());
} finally {
if (null != database) {
database.close();
}
}
} /**
* 清除表的数据
*/
public void cleanUpData() { // delete from TableName; //清空数据 SQLiteDatabase database = getWritableDatabase();
String cleanUpDataSQL = "delete from " + TABLE_NAME;
try {
database.execSQL(cleanUpDataSQL);
} catch (Exception e) {
e.printStackTrace();
Log.d(TAG, "清除表的数据异常：" + e.toString());
} finally {
if (null != database) {
database.close();
}
}
} /**
* 插入多条数据
* @param students 传递Student集合
*/
public void insertData(List<Student> students) {
SQLiteDatabase database = getWritableDatabase();
try {
for (Student student : students) {
ContentValues contentValues = new ContentValues();
contentValues.clear();
contentValues.put("name", student.getName());
contentValues.put("age", student.getName());
contentValues.put("hobby", student.getHobby());
database.insert(TABLE_NAME, "_id", contentValues);
}
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "insert多条异常：" + e.toString());
} finally {
if (null != database) {
database.close();
}
}
} /**
* 插入单条
* @param contentValues 传递ContentValues
*/
public void insertData(ContentValues contentValues) {
SQLiteDatabase database = getWritableDatabase();
try {
database.insert(TABLE_NAME, "_id", contentValues);
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "insert单条异常：" + e.toString());
} finally {
if (null != database) {
database.close();
}
}
} /**
* 查询需要的列名
*/
private String[] columns = new String[]{"name", "age", "hobby"}; /**
* 查询第一条数据
* @return 返回Student实体
*/
public Student selectData() {
SQLiteDatabase database = getReadableDatabase();
Cursor cursor = null;
Student student = null;
try {
cursor = database.query(TABLE_NAME, columns, null, null, null, null, null);
if (cursor.moveToFirst()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
String hobby = cursor.getString(cursor.getColumnIndex("hobby")); student = new Student(name, age, hobby);
}
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "select异常：" + e.toString());
} finally {
if (null != database) {
database.close();
} if (null != cursor) cursor.close();
}
return student;
} /**
* 判断第一条数据是否存在
* @return 存在返回true，否则返回false
*/
public boolean isMoveToFirst() {
boolean result;
SQLiteDatabase database = getReadableDatabase();
Cursor cursor = database.query(TABLE_NAME,
columns,
null,
null,
null,
null,
null);
result = cursor.moveToFirst();
database.close();
cursor.close();
return result;
} /**
* ......... 还可以增加很多操作表相关的行为
*/ /**
* 继承SQLiteOpenHelper抽象类 重写的创表方法，此SQLiteDatabase db 不能关闭
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
createTable(db);
} /**
* 继承SQLiteOpenHelper抽象类 重写的升级方法
* @param db
* @param oldVersion
* @param newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
继承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, "
+ "hobby VARCHAR);";
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 void dropTable(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>