Android 封装AsyncTask操作SQLite数据库

最新推荐文章于 2021-05-30 00:40:49 发布

jianning-wu

最新推荐文章于 2021-05-30 00:40:49 发布

阅读量778

点赞数

分类专栏： Android 数据库文章标签： Android 数据库 AsyncTask操作Sqlite数据库 Android 数据库增删改查 SQLite数据库增删改查 Android GreenDao 数据库

本文链接：https://blog.csdn.net/weixin_37730482/article/details/70154981

版权

Android 数据库专栏收录该内容

10 篇文章 1 订阅

订阅专栏

一.简单封装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

jianning-wu

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Android 封装AsyncTask操作SQLite数据库

一.简单封装AsyncTask操作Sqlite数据库SQLiteOpenHelper实现类package com.wjn.androiddbdemo.utils.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database....
复制链接

扫一扫

专栏目录