Android 封裝AsyncTask操作Sqlite資料庫

1.DBCheckAsyncTask類

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();
        }
        return sbBuilder.toString();
    }
}

 

 

 

 

2.DBSQLiteOpenHelper類

/**
 * 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方法
     * 在資料庫的版本發生變化時會被呼叫， 一般在軟體升級時才需改變版本號
     * */

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql="DROP TABLE IF EXISTS mytable";
        db.execSQL(sql);
        onCreate(db);
    }

}

 

 

 

3.MainActivity類

package com.wjn.sqlitedemo.activity;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

import com.wjn.sqlitedemo.R;
import com.wjn.sqlitedemo.bean.Person;
import com.wjn.sqlitedemo.db.DBCheckAsyncTask;
import com.wjn.sqlitedemo.db.DBSQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private TextView textView1;
    private TextView textView2;
    private TextView textView3;
    private TextView textView4;
    private TextView textView5;
    private TextView textView6;
    private DBSQLiteOpenHelper dbsqLiteOpenHelper;
    private SQLiteDatabase db;
    private int id=111;
    private int page=1;
    private int pageNum=3;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        textView1=findViewById(R.id.activity_main_textview1);
        textView2=findViewById(R.id.activity_main_textview2);
        textView3=findViewById(R.id.activity_main_textview3);
        textView4=findViewById(R.id.activity_main_textview4);
        textView5=findViewById(R.id.activity_main_textview5);
        textView6=findViewById(R.id.activity_main_textview6);

        dbsqLiteOpenHelper=new DBSQLiteOpenHelper(MainActivity.this,"cdsp.db",null,1);

        //增
        textView1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db=dbsqLiteOpenHelper.getReadableDatabase();
                Person person=new Person();
                person.setId(String.valueOf(id));
                person.setName("張三"+id);
                person.setDescribe("本章節講述getWritableDatabase()和getReadableDatabase()區別");
                insert(person);
                id=id+100;
            }
        });

        //刪
        textView2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                delete("111");
            }
        });

        //改
        textView3.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
//                Person person=new Person();
//                person.setId("211");
//                person.setName("修改");
//                person.setDescribe("修改描述");
//                update(person);

                long count=getCount();
                textView6.setText(count+"條");
            }
        });

        //查
        textView4.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db=dbsqLiteOpenHelper.getReadableDatabase();
                new DBCheckAsyncTask(textView6,db).execute("select * from mytable");

//                check();
            }
        });

        //清空資料
        textView5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
//                db=dbsqLiteOpenHelper.getReadableDatabase();
//                db.execSQL("DELETE FROM mytable");

                List<Person> list=getScrollData(page,pageNum);
                StringBuilder sbBuilder=new StringBuilder();
                for(int i=0;i<list.size();i++){
                    Person person=list.get(i);
                    sbBuilder.append("ID:"+person.getId()+"\n");
                    sbBuilder.append("姓名:"+person.getName()+"\n");
                    sbBuilder.append("描述:"+person.getDescribe()+"\n\n\n");
                }
                textView6.setText(sbBuilder.toString());
                page++;

            }
        });
    }

    /**
     * 插入資料
     * */

    public void insert(Person p) {
        db=dbsqLiteOpenHelper.getReadableDatabase();
        db.execSQL("INSERT INTO mytable(id,name,describe) values(?,?,?)",
                new String[]{p.getId(), p.getName(),p.getDescribe()});
    }

    /**
     * 刪除資料
     * */

    public void delete(String id) {
        db=dbsqLiteOpenHelper.getReadableDatabase();
        db.execSQL("DELETE FROM mytable WHERE id = ?",
                new String[]{id});
    }

    /**
     * 修改資料
     * */

    public void update(Person p) {
        db=dbsqLiteOpenHelper.getReadableDatabase();
        db.execSQL("UPDATE mytable SET name = ?,describe = ? WHERE id = ?",
                new String[]{p.getName(), p.getDescribe(), p.getId()});
    }

    /**
     * 查詢資料
     * */

    public void check() {
        db=dbsqLiteOpenHelper.getReadableDatabase();
        String sql="select * from mytable";
        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();
        }
        textView6.setText(sbBuilder.toString());
        db.close();
    }

    /**
     * 查詢條目
     * */

    public long getCount() {
        db=dbsqLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT (*) FROM mytable", null);
        cursor.moveToFirst();
        long result = cursor.getLong(0);
        cursor.close();
        return result;
    }

    /**
     * 資料分頁顯示
     * limit 2,3; 從第2+1條開始顯示 顯示3條記錄;
     * */

    public List<Person> getScrollData(int offset, int maxResult) {
        List<Person> person = new ArrayList<Person>();
        db=dbsqLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM mytable ORDER BY id ASC LIMIT"+" ?,?",
                new String[]{String.valueOf(offset), String.valueOf(maxResult)});
        while (cursor.moveToNext()) {
            Person persons=new Person();
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String describe = cursor.getString(cursor.getColumnIndex("describe"));
            persons.setId(String.valueOf(id));
            persons.setName(name);
            persons.setDescribe(describe);
            person.add(persons);
        }
        cursor.close();
        return person;
    }

}