Android 封装AsyncTask操作SQLite数据库

xiaoxiao2021-03-28 27

一.简单封装AsyncTask操作Sqlite数据库

SQLiteOpenHelper实现类

package com.wjn.androiddbdemo.utils.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Sqlite SQLiteOpenHelper实现类 * */ public class DBSQLiteOpenHelper extends SQLiteOpenHelper { /** * 构造方法 * */ public DBSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } /** * onCreate方法 * 首次使用软件时生成数据库表 * */ @Override public void onCreate(SQLiteDatabase db) { String sql="CREATE TABLE mytable( id INTEGER, name VARCHAR(10), describe TEXT)"; db.execSQL(sql); } /** * onUpgrade方法 * 在数据库的版本发生变化时会被调用，一般在软件升级时才需改变版本号 * 删除原来的表,重新执行onCreate方法 * */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql="DROP TABLE IF EXISTS mytable"; db.execSQL(sql); onCreate(db); } }

Activity代码

package com.wjn.androiddbdemo.activity; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.TextView; import com.wjn.androiddbdemo.R; import com.wjn.androiddbdemo.bean.Person; import com.wjn.androiddbdemo.utils.db.DBCheckAsyncTask; import com.wjn.androiddbdemo.utils.db.DBSQLiteOpenHelper; import com.wjn.androiddbdemo.utils.db.SQLiteHelp; import com.wjn.androiddbdemo.utils.ui.StatusBarUtil; public class AsyncTaskSQLiteActivity extends AppCompatActivity implements View.OnClickListener { private TextView textView1; private TextView textView2; private TextView textView3; private TextView textView4; private TextView textView; private DBSQLiteOpenHelper dbsqLiteOpenHelper; private SQLiteDatabase db; private int id=100; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_asynctasksqlite); initView(); } /** * 初始化各种View */ private void initView() { //根据状态栏颜色来决定状态栏背景用黑色还是白色 true:是否修改状态栏字体颜色 StatusBarUtil.setStatusBarMode(this, false, false, R.color.colorPrimary); textView1 = findViewById(R.id.activity_asynctasksqlite_textview1); textView2 = findViewById(R.id.activity_asynctasksqlite_textview2); textView3 = findViewById(R.id.activity_asynctasksqlite_textview3); textView4 = findViewById(R.id.activity_asynctasksqlite_textview4); textView = findViewById(R.id.activity_asynctasksqlite_textview); textView1.setOnClickListener(this); textView2.setOnClickListener(this); textView3.setOnClickListener(this); textView4.setOnClickListener(this); dbsqLiteOpenHelper=new DBSQLiteOpenHelper(this,"wjn.db",null,1); } /** * 各种点击事件的方法 */ @Override public void onClick(View v) { switch (v.getId()) { case R.id.activity_asynctasksqlite_textview1://增 insertbtnMethod(); break; case R.id.activity_asynctasksqlite_textview2://删 delbtnMethod("100"); break; case R.id.activity_asynctasksqlite_textview3://改 updatebtnMethod(); break; case R.id.activity_asynctasksqlite_textview4://查 findbtnMethod(); break; default: break; } } /** * API语句增 */ public void insertbtnMethod() { //打开数据库 db=dbsqLiteOpenHelper.getReadableDatabase(); //初始化对象 Person person=new Person(); person.setId(String.valueOf(id)); person.setName("张三"); person.setDescribe("本章节讲述getWritableDatabase()和getReadableDatabase()区别"); //插入语句 db.execSQL("INSERT INTO mytable(id,name,describe) values(?,?,?)", new String[]{person.getId(), person.getName(),person.getDescribe()}); //关闭数据库 db.close(); } /** * API语句删 */ public void delbtnMethod(String id) { //打开数据库 db=dbsqLiteOpenHelper.getReadableDatabase(); //删除语句 db.execSQL("DELETE FROM mytable WHERE id = ?", new String[]{id}); //关闭数据库 db.close(); } /** * API语句改 */ public void updatebtnMethod() { //打开数据库 db=dbsqLiteOpenHelper.getReadableDatabase(); //初始化对象 Person person = new Person(); person.setId("100"); person.setName("修改后的姓名"); person.setDescribe("修改后的描述"); //修改语句 db.execSQL("UPDATE mytable SET name = ?,describe = ? WHERE id = ?", new String[]{person.getName(), person.getDescribe(), person.getId()}); //关闭数据库 db.close(); } /** * API语句查 */ public void findbtnMethod() { //打开数据库 db=dbsqLiteOpenHelper.getReadableDatabase(); db=dbsqLiteOpenHelper.getReadableDatabase(); new DBCheckAsyncTask(textView,db).execute("select * from mytable"); } }

AsyncTask实现类

package com.wjn.androiddbdemo.utils.db; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.AsyncTask; import android.widget.TextView; public class DBCheckAsyncTask extends AsyncTask<String, Integer, String> { /** * 构造方法 * */ private TextView textView; private SQLiteDatabase db; public DBCheckAsyncTask(TextView textView,SQLiteDatabase db){ this.textView=textView; this.db=db; } /** *onPreExecute方法执行前准备工作 UI线程 * */ @Override protected void onPreExecute() { super.onPreExecute(); textView.setText("数据加载中..."); } /** * onPostExecute 返回结果 UI线程 * */ @Override protected void onPostExecute(String s) { super.onPostExecute(s); textView.setText(s); } /** * onProgressUpdate 更新进度 UI线程 * */ @Override protected void onProgressUpdate(Integer... values) { super.onProgressUpdate(values); textView.setText("数据加载中..."+values[0]); } /** * doInBackground 后台操作非UI线程 * */ @Override protected String doInBackground(String... strings) { String sql=strings[0]; Cursor cursor=db.rawQuery(sql, null); cursor.moveToFirst(); StringBuilder sbBuilder=new StringBuilder(); while(!cursor.isAfterLast()){ int id=cursor.getInt(cursor.getColumnIndex("id")); sbBuilder.append("ID:"+id+"\n"); String name=cursor.getString(cursor.getColumnIndex("name")); sbBuilder.append("姓名:"+name+"\n"); String describe=cursor.getString(cursor.getColumnIndex("describe")); sbBuilder.append("描述:"+describe+"\n\n\n"); cursor.moveToNext(); } cursor.close(); db.close(); return sbBuilder.toString(); } }

结果

增——>查

改——>查

删——>查

AsyncTask操作Sqlite数据库主要在于可以在子线程中操作数据库的查询，在UI线程中更新。

二.项目实战(AsyncTask+EventBus)

下面是项目实战中的代码

SQLiteOpenHelper实现类

/** * Created by wujna on 2017/12/21. * Android SQLite数据库帮助类 */ public class DBSQLiteOpenHelper extends SQLiteOpenHelper{ public static String dbname="CDSP_Clinicians.db";//数据库名称 public static int version=1;//数据库版本号 /** * 数据表1 * */ public static String index_data_dbtablename="index_data"; public static String index_create_sql="CREATE TABLE "+index_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String index_del_sql="DROP TABLE IF EXISTS "+index_data_dbtablename; /** * 数据表2 * */ public static String people_data_dbtablename="people_data"; public static String people_create_sql="CREATE TABLE "+people_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String people_del_sql="DROP TABLE IF EXISTS "+people_data_dbtablename; /** * 数据表3 * */ public static String attachment_data_dbtablename="attachment_data"; public static String attachment_create_sql="CREATE TABLE "+attachment_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String attachment_del_sql="DROP TABLE IF EXISTS "+attachment_data_dbtablename; /** * 数据表4 * */ public static String message_data_dbtablename="message_data"; public static String message_create_sql="CREATE TABLE "+message_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String message_del_sql="DROP TABLE IF EXISTS "+message_data_dbtablename; /** * 数据表5 * */ public static String followup_data_dbtablename="followup_data"; public static String followup_create_sql="CREATE TABLE "+followup_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String followup_del_sql="DROP TABLE IF EXISTS "+followup_data_dbtablename; /** * 数据表6 * */ public static String peoplewarning_data_dbtablename="peoplewarning_data"; public static String peoplewarning_create_sql="CREATE TABLE "+peoplewarning_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String peoplewarning_del_sql="DROP TABLE IF EXISTS "+peoplewarning_data_dbtablename; /** * 数据表7 * */ public static String peopleapply_data_dbtablename="peopleapply_data"; public static String peopleapply_create_sql="CREATE TABLE "+peopleapply_data_dbtablename +"( Id INTEGER PRIMARY KEY AUTOINCREMENT, userID TEXT,dataKey TEXT, dataValue TEXT)"; public static String peopleapply_del_sql="DROP TABLE IF EXISTS "+peopleapply_data_dbtablename; /** * 构造方法 * */ public DBSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } /** * onCreate方法 * 首次使用软件时生成数据库表 * */ @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(index_create_sql); sqLiteDatabase.execSQL(people_create_sql); sqLiteDatabase.execSQL(attachment_create_sql); sqLiteDatabase.execSQL(message_create_sql); sqLiteDatabase.execSQL(followup_create_sql); sqLiteDatabase.execSQL(peoplewarning_create_sql); sqLiteDatabase.execSQL(peopleapply_create_sql); } /** * onUpgrade方法 * 在数据库的版本发生变化时会被调用，一般在软件升级时才需改变版本号 * */ @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { sqLiteDatabase.execSQL(index_del_sql); sqLiteDatabase.execSQL(people_del_sql); sqLiteDatabase.execSQL(attachment_del_sql); sqLiteDatabase.execSQL(message_del_sql); sqLiteDatabase.execSQL(followup_del_sql); sqLiteDatabase.execSQL(peoplewarning_del_sql); sqLiteDatabase.execSQL(peopleapply_del_sql); onCreate(sqLiteDatabase); } }

初始化数据库帮助类

dbsqLiteOpenHelper=new DBSQLiteOpenHelper(this,DBSQLiteOpenHelper.dbname,null,DBSQLiteOpenHelper.version);

插入语句(OKHttp onNext方法)

@Override public void onNext(String s) { parseIndexNumber(s); DBUtils.insertMessageListData(dbsqLiteOpenHelper,s,userID); }

查询语句

String message_select_listdata_sql="SELECT dataValue FROM "+DBSQLiteOpenHelper.message_data_dbtablename +" WHERE dataKey="+ "'"+DataConstant.message_listdataKey+"'" +"and userID="+"'"+userID+"'"; DBUtils.getMessageListData(dbsqLiteOpenHelper,"MessageActivity_List",message_select_listdata_sql);

DBUtils工具类

/** * 本地数据库工具类 */ public class DBUtils { /** * 消息列表插入数据方法 */ public static void insertMessageListData(DBSQLiteOpenHelper dbsqLiteOpenHelper, String context, String userID) { if (null == dbsqLiteOpenHelper || null == context || null == userID) { return; } new InsertDataAsyncTask(dbsqLiteOpenHelper, context, "MessageListData", userID).execute(""); } /** * 消息列表获取数据方法 */ public static void getMessageListData(DBSQLiteOpenHelper dbsqLiteOpenHelper, String whichActivity, String sql) { if (null == dbsqLiteOpenHelper || null == whichActivity || null == sql) { return; } new GetStringDataAsyncTask(dbsqLiteOpenHelper, whichActivity).execute(sql); } }

AsyncTask实现类

插入

/** * 向本地数据库插入数据AsyncTask实现类 * Key-Value * */ public class InsertDataAsyncTask extends AsyncTask<String, Integer, String> { /** * 构造方法 * */ private DBSQLiteOpenHelper dbsqLiteOpenHelper; private String context; private String type; private String userID; public InsertDataAsyncTask(DBSQLiteOpenHelper dbsqLiteOpenHelper, String context,String type,String userID){ this.dbsqLiteOpenHelper=dbsqLiteOpenHelper; this.context=context; this.type=type; this.userID=userID; } /** *onPreExecute方法执行前准备工作 UI线程 * */ @Override protected void onPreExecute() { super.onPreExecute(); } /** * onPostExecute 返回结果 UI线程 * */ @Override protected void onPostExecute(String s) { super.onPostExecute(s); } /** * onProgressUpdate 更新进度 UI线程 * */ @Override protected void onProgressUpdate(Integer... values) { super.onProgressUpdate(values); } /** * doInBackground 后台操作非UI线程 * */ @Override protected String doInBackground(String... strings) { SQLiteDatabase db = dbsqLiteOpenHelper.getReadableDatabase(); if(null!=db){ db.execSQL("DELETE FROM "+DBSQLiteOpenHelper.message_data_dbtablename +" WHERE dataKey = ?", new String[]{DataConstant.message_listdataKey}); db.execSQL("INSERT INTO " + DBSQLiteOpenHelper.message_data_dbtablename + "(userID,dataKey,dataValue) values(?,?,?)", new String[]{userID,DataConstant.message_listdataKey, context}); db.close(); return ""; } }

获取

/** * 获取本地数据库字符串AsyncTask * */ public class GetStringDataAsyncTask extends AsyncTask<String, Integer, String> { /** * 构造方法 * */ private DBSQLiteOpenHelper dbsqLiteOpenHelper; private String whichActivity; public GetStringDataAsyncTask(DBSQLiteOpenHelper dbsqLiteOpenHelper,String whichActivity){ this.dbsqLiteOpenHelper=dbsqLiteOpenHelper; this.whichActivity=whichActivity; } /** *onPreExecute方法执行前准备工作 UI线程 * */ @Override protected void onPreExecute() { super.onPreExecute(); } /** * onPostExecute 返回结果 UI线程 * */ @Override protected void onPostExecute(String s) { super.onPostExecute(s); String result=""; if(!BooleanUtils.isEmpty(s)){ result=s; } //发送EventBus通知刷新页面 EventBusBean eventBusBean=new EventBusBean(); eventBusBean.setUpdatetype(whichActivity); eventBusBean.setContext(result); EventBus.getDefault().post(eventBusBean); } /** * onProgressUpdate 更新进度 UI线程 * */ @Override protected void onProgressUpdate(Integer... values) { super.onProgressUpdate(values); } /** * doInBackground 后台操作非UI线程 * */ @Override protected String doInBackground(String... strings) { String result=""; String sql=strings[0]; SQLiteDatabase db = dbsqLiteOpenHelper.getReadableDatabase(); if(null!=db&&!BooleanUtils.isEmpty(sql)){ Cursor cursor=db.rawQuery(sql, null); boolean b=cursor.moveToFirst(); if(b){ result= cursor.getString(0); } cursor.close(); db.close(); } return result; } }

Activity EventBus接收

/** * onEventMainThread EventBus 接收消息 * */ public void onEventMainThread(EventBusBean eventBusBean) { if(null!=eventBusBean){ String type=eventBusBean.getUpdatetype(); if("MessageActivity_List".equals(type)){//无网络本地数据库 String context=eventBusBean.getContext(); parseMessage(context,"0"); } } }

代码链接：https://github.com/wujianning/AndroidDBDemo

转载请注明原文地址: https://ju.6miu.com/read-664622.html

技术

最新回复(0)