Android學習筆記-資料庫開發-2-使用SQLite，androidsqlite

Android學習筆記-資料庫開發-2-使用SQLite，androidsqlite

上一篇：介紹了SQLite概念和android中兩個基本類：

SQLiteDataBase和SQLiteOpenHelper

本篇主要用代碼實現對這兩個方法的使用：

1、資料模型建立：UserModel

public class UserModel implements Serializable {    public ContentValues toContentValues() {//返回一個ContentValues 、更新、插入時候用到        ContentValues cv = new ContentValues();        cv.put(Columns.NAME, name);        cv.put(Columns.AGE, age);        return cv;    }    public static class Columns {//對應資料庫的列名        public static final String _ID = "_id";//主鍵        public static final String NAME = "NAME";//姓名        public static final String AGE = "AGE";//年齡    }    public long _id;    public String name;    public int age;    @Override    public String toString() {        return "UserModel{" +                "_id='" + _id + '\'' +                ", name='" + name + '\'' +                ", age=" + age +                '}';    }}

2、建立MyDataBaseHelper，繼承SQLiteOpenHelper，用於管理資料庫表的建立和更新。

public class MyDataBaseHelper extends SQLiteOpenHelper {    public String TAG = "MyDataBaseHelper";    public MyDataBaseHelper(Context mContext, String databaseName, SQLiteDatabase.CursorFactory factory, int version) {        super(mContext, databaseName, factory, version);    }    public static final String USER_TABLE_NAME = "USER_TABLE";//表名    private static final String CREATE_USER_TABLE = new StringBuffer(//建立表的SQL            "CREATE TABLE IF NOT EXISTS "                    + USER_TABLE_NAME                    + String.format(                    "("                            + "%s INTEGER PRIMARY KEY AUTOINCREMENT, " // id                            + "%s VARCHAR, " // name                            + "%s INTEGER " //  age                            + ")"                    , UserModel.Columns._ID                    , UserModel.Columns.NAME                    , UserModel.Columns.AGE            )    )            .toString();    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL(CREATE_USER_TABLE);//建立表    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        //根據版本號碼更新表        switch (oldVersion) {            case 1://todo 增加表、刪除表、增加欄位、刪除欄位、等操作            case 2:        }    }    /**     * 給一張表增加一個欄位     *     * @param db     * @param dbTable     * @param columnName     * @param columnDefinition     */    private void addColumn(SQLiteDatabase db, String dbTable,                           String columnName, String columnDefinition) {        if (checkColumnExists(db, dbTable, columnName)) {            Log.e("checkColumnExists", "true");        } else {            Log.e("checkColumnExists", "false");            db.execSQL("ALTER TABLE " + dbTable + " ADD COLUMN " + columnName + " "                    + columnDefinition);        }    }    /**     * 檢查表中某列是否存在     *     * @param db     * @param tableName  表名     * @param columnName 列名     * @return     */    private boolean checkColumnExists(SQLiteDatabase db, String tableName, String columnName) {        boolean result = false;        Cursor cursor = null;        try {            cursor = db.rawQuery("select * from sqlite_master where name =? and sql like ?"                    , new String[]{tableName, "%" + columnName + "%"});            result = null != cursor && cursor.moveToFirst();        } catch (Exception e) {            Log.e(TAG, "checkColumnExists2..." + e.getMessage());        } finally {            if (null != cursor && !cursor.isClosed()) {                cursor.close();            }        }        return result;    }}

3、寫一個DBManager類，主要負責資料庫資源SQLiteDatabase的初始化,開啟,關閉,以及獲得DatabaseHelper協助類操作。

import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import java.io.File;/** * SQLite資料庫管理類 * <p> * 主要負責資料庫資源SQLiteDatabase 的初始化,開啟,關閉,以及獲得DatabaseHelper協助類操作 * * @author shimiso */public class DBManager {    public final static int version = 1;    private String databaseName;    public static final String DB_KEY = "";//包資料庫標識    // 本地Context對象    private Context mContext = null;    private static DBManager dBManager = null;    /**     * 建構函式     *     * @param mContext     */    private DBManager(Context mContext) {        super();        this.mContext = mContext;    }    /***     * 單態對象值為null     **/    public static void clearDbmanager() {        dBManager = null;    }    public static DBManager getInstance(Context mContext, String databaseName) {        dBManager = new DBManager(mContext);        dBManager.databaseName = DB_KEY + databaseName;        return dBManager;    }    /**     * 關閉資料庫 注意:當事務成功或者一次性操作完畢時候再關閉     */    public void closeDatabase(SQLiteDatabase dataBase, Cursor cursor) {        if (null != dataBase) {            dataBase.close();        }        if (null != cursor) {            cursor.close();        }    }    /**     * 開啟資料庫       */    public SQLiteDatabase openDatabase() {        return getDatabaseHelper().getWritableDatabase();    }    /**     * 擷取DataBaseHelper     *     * @return     */    public MyDataBaseHelper getDatabaseHelper() {        return new MyDataBaseHelper(mContext, this.databaseName, null,                this.version);    }    public int getVersion() {        String path = getDatabasePath(databaseName).getPath();        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(path, null);        return db.getVersion();    }    private File getDatabasePath(String name) {        String EXTERN_PATH = "/data/data/" + mContext.getPackageName() + "/databases/";        File f = new File(EXTERN_PATH);        if (!f.exists()) {            f.mkdirs();        }        return new File(EXTERN_PATH + name);    }}

4、UserDBController，通過DBManager擷取SQLiteDatabase，用於對UserModel的資料持久化操作。可以包括一些增刪改查等。

這裡唯寫了了增加一條UserModel。

import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import databasetest.zx.com.databasetest.AppApplication;import databasetest.zx.com.databasetest.model.UserModel;import static databasetest.zx.com.databasetest.db.MyDataBaseHelper.USER_TABLE_NAME;/** * Created by zhoux on 2017/12/11. * 注釋： */public class UserDBController {    private final SQLiteDatabase db;    public UserDBController() {//        MyDataBaseHelper openHelper = new MyDataBaseHelper(AppApplication.CONTEXT, "zxdbtest.db");        db = DBManager.getInstance(AppApplication.CONTEXT, "zxdbtest.db").openDatabase();    }    public UserModel addOne(UserModel model) {        try {            long _id = db.insert(USER_TABLE_NAME, null, model.toContentValues());//見UserModel的注釋            final boolean succeed = _id != -1;            model._id = _id;            return succeed ? model : null;        } catch (Exception e) {        } finally {            closeDatabase(null);        }        return null;    }    public void closeDatabase(Cursor cursor) {        if (null != cursor) {            cursor.close();        }    }}

5、布局檔案activity_main.xml

<?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:id="@+id/add_tv"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:text="addOne" />    <TextView        android:id="@+id/result_tv"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="result:" /></LinearLayout>

6、MainActivity

import android.support.v7.app.AppCompatActivity;import android.os.Bundle;import android.view.View;import android.widget.TextView;import databasetest.zx.com.databasetest.db.UserDBController;import databasetest.zx.com.databasetest.model.UserModel;public class MainActivity extends AppCompatActivity {    TextView add_tv;    TextView result_tv;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        add_tv = findViewById(R.id.add_tv);        result_tv = findViewById(R.id.result_tv);        addListener();    }    private void addListener() {        add_tv.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                UserModel user = new UserModel();                user.name = "周小小";                user.age = 18;                UserDBController mUserDBController = new UserDBController();                UserModel userDB = mUserDBController.addOne(user);                if (userDB != null) {                    result_tv.setText("插入結果：成功：" + userDB.toString());                } else {                    result_tv.setText("插入結果：失敗");                }            }        });    }}

源碼：https://gitee.com/zhou.xiang/databasetest.git

下一篇：Android學習筆記-資料庫開發-3：使用SQLite Expert Pro

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。