一个封装类教你学会SQLite数据库

数据库操作类的命名一般以Dao为结尾，什么是Dao呢？

DAO(Data Access Object) 数据访问对象是第一个面向对象的数据库接口

数据实体类

            
                public
                class Tree {
    privateint id;
    private String name;
    privateint age;
    privatefloat price;


    publicintgetId() {
        return id;
    }

    publicvoidsetId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    publicvoidsetName(String name) {
        this.name = name;
    }

    publicintgetAge() {
        return age;
    }

    publicvoidsetAge(int age) {
        this.age = age;
    }

    publicfloatgetPrice() {
        return price;
    }

    publicvoidsetPrice(float price) {
        this.price = price;
    }

    publicTree() {
    }

    publicTree(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";//数据库名称privatestaticfinal String TABLE_NAME = "treeinfo";//数据表名称privatestaticfinalint DB_VERSION = 1;//数据库版本//表的字段名privatestatic String KEY_ID = "id";
    privatestatic String KEY_NAME = "name";
    privatestatic String KEY_AGE = "age";
    privatestatic String KEY_PRICE = "price";

    private SQLiteDatabase mDatabase;
    private Context mContext;
    private TreeDBOpenHelper mDbOpenHelper;//数据库打开帮助类publicTreeDBDao(Context context) {
        mContext = context;
    }

    //打开数据库publicvoidopenDataBase() {
        mDbOpenHelper = new TreeDBOpenHelper(mContext, DB_NAME, null, DB_VERSION);
        try {
            mDatabase = mDbOpenHelper.getWritableDatabase();//获取可写数据库
        } catch (SQLException e) {
            mDatabase = mDbOpenHelper.getReadableDatabase();//获取只读数据库
        }
    }

    //关闭数据库publicvoidcloseDataBase() {
        if (mDatabase != null) {
            mDatabase.close();
        }
    }

    //插入一条数据publiclonginsertData(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);
    }

    //删除一条数据publiclongdeleteData(long id) {
        return mDatabase.delete(TABLE_NAME, KEY_ID + "=" + id, null);
    }

    //删除所有数据publiclongdeleteAllData() {
        return mDatabase.delete(TABLE_NAME, null, null);
    }

    //更新一条数据publiclongupdateData(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()) {
            returnnull;
        }
        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;
    }

    /**
     * 数据表打开帮助类
     */privatestaticclassTreeDBOpenHelperextendsSQLiteOpenHelper {publicTreeDBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @OverridepublicvoidonCreate(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);
        }

        @OverridepublicvoidonUpgrade(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());
}


        

存储结果