一個封裝類教你學會SQLite資料庫

標籤：

資料庫操作類的命名一般以Dao為結尾，什麼是Dao呢？

DAO(Data Access Object) Data Access Objects是第一個物件導向的資料庫介面

資料實體類

public class Tree {    private int id;    private String name;    private int age;    private float price;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    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 float getPrice() {        return price;    }    public void setPrice(float price) {        this.price = price;    }    public Tree() {    }    public Tree(String name, int age, float price) {        this.name = name;        this.age = age;        this.price = price;    }    @Override    public String toString() {        return "Tree{" +                "id=" + id +                ", name=‘" + name + ‘\‘‘ +                ", age=" + age +                ", price=" + price +                ‘}‘;    }}

資料庫操作封裝類

public class TreeDBDao {    private static final String DB_NAME = "tree.db";//資料庫名稱    private static final String TABLE_NAME = "treeinfo";//資料表名稱    private static final int DB_VERSION = 1;//資料庫版本    //表的欄位名    private static String KEY_ID = "id";    private static String KEY_NAME = "name";    private static String KEY_AGE = "age";    private static String KEY_PRICE = "price";    private SQLiteDatabase mDatabase;    private Context mContext;    private TreeDBOpenHelper mDbOpenHelper;//資料庫開啟協助類    public TreeDBDao(Context context) {        mContext = context;    }    //開啟資料庫    public void openDataBase() {        mDbOpenHelper = new TreeDBOpenHelper(mContext, DB_NAME, null, DB_VERSION);        try {            mDatabase = mDbOpenHelper.getWritableDatabase();//擷取可寫資料庫        } catch (SQLException e) {            mDatabase = mDbOpenHelper.getReadableDatabase();//擷取唯讀資料庫        }    }    //關閉資料庫    public void closeDataBase() {        if (mDatabase != null) {            mDatabase.close();        }    }    //插入一條資料    public long insertData(Tree tree) {        ContentValues values = new ContentValues();        values.put(KEY_NAME, tree.getName());        values.put(KEY_AGE, tree.getAge());        values.put(KEY_PRICE, tree.getPrice());        return mDatabase.insert(TABLE_NAME, null, values);    }    //刪除一條資料    public long deleteData(long id) {        return mDatabase.delete(TABLE_NAME, KEY_ID + "=" + id, null);    }    //刪除所有資料    public long deleteAllData() {        return mDatabase.delete(TABLE_NAME, null, null);    }    //更新一條資料    public long updateData(long id, Tree tree) {        ContentValues values = new ContentValues();        values.put(KEY_NAME, tree.getName());        values.put(KEY_AGE, tree.getAge());        values.put(KEY_PRICE, tree.getPrice());        return mDatabase.update(TABLE_NAME, values, KEY_ID + "=" + id, null);    }    //查詢一條資料    public List<Tree> queryData(long id) {        Cursor results = mDatabase.query(TABLE_NAME, new String[]{KEY_ID, KEY_NAME, KEY_AGE, KEY_PRICE},                KEY_ID + "=" + id, null, null, null, null);        return convertToTree(results);    }    //查詢所有資料    public List<Tree> queryDataList() {        Cursor results = mDatabase.query(TABLE_NAME, new String[]{KEY_ID, KEY_NAME, KEY_AGE, KEY_PRICE},                null, null, null, null, null);        return convertToTree(results);    }    private List<Tree> convertToTree(Cursor cursor) {        int resultCounts = cursor.getCount();        if (resultCounts == 0 || !cursor.moveToFirst()) {            return null;        }        List<Tree> mTreeList = new ArrayList<>();        for (int i = 0; i < resultCounts; i++) {            Tree tree = new Tree();            tree.setId(cursor.getInt(0));            tree.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));            tree.setAge(cursor.getInt(cursor.getColumnIndex(KEY_AGE)));            tree.setPrice(cursor.getFloat(cursor.getColumnIndex(KEY_PRICE)));            mTreeList.add(tree);            cursor.moveToNext();        }        return mTreeList;    }    /**     * 資料表開啟協助類     */    private static class TreeDBOpenHelper extends SQLiteOpenHelper {        public TreeDBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {            super(context, name, factory, version);        }        @Override        public void onCreate(SQLiteDatabase db) {            final String sqlStr = "create table if not exists " + TABLE_NAME + " (" + KEY_ID + " integer primary key autoincrement, " + KEY_NAME + " text not null, " + KEY_AGE + " integer," + KEY_PRICE + " float);";            db.execSQL(sqlStr);        }        @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            final String sqlStr = "DROP TABLE IF EXISTS " + TABLE_NAME;            db.execSQL(sqlStr);            onCreate(db);        }    }}

使用方法

TreeDBDao mDBDao = new TreeDBDao(MainActivity.this);//執行個體化對象mDBDao.openDataBase();//開啟資料庫//增刪改查操作mDBDao.insertData(new Tree("GreenTree", 12, 2321.5f));//增加資料mDBDao.deleteData(1);//刪除資料mDBDao.updateData(1, new Tree("RedTree", 20, 5200f));//更新資料mDBDao.deleteAllData();//刪除所有資料List<Tree> list = mDBDao.queryData(1);//查詢id為1的資料Log.v("-->", list.get(0).toString());List<Tree> lists = mDBDao.queryDataList();//查詢所有資料for (Tree tree : lists) {   Log.v("-->", tree.toString());}

儲存結果

一個封裝類教你學會SQLite資料庫