SQLiteDatabase工具类(增删改查)

梦里梦外; 2022-05-12 05:36 260阅读 0赞

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

Android开发中，sdk提供了SQLiteDatabase类，有响应调用数据操作的api。

1、面对象，先创建表对应的实体类

public class DbEntity {
        private int      id;
        private int      p_id;
        private int      c_id;
        private int      d_id;
        private String   province;
        private String   city;
        private String   district;
        private String  address;
        private String   attr;
    
        protected DbEntity(){
    
        }
    
        public DbEntity(int p_id, int c_id, int d_id, String province, String city, String district, String address, String attr) {
            this.p_id = p_id;
            this.c_id = c_id;
            this.d_id = d_id;
            this.province = province;
            this.city = city;
            this.district = district;
            this.address = address;
            this.attr = attr;
        }
    }

2、封装SQLiteDatabase工具类实现curd

public class DBUtils extends SQLiteOpenHelper {
        private static final String DB_NAME = "data.db";
        private static final int VERSION = 1;
        private static final String TABLE_NAME = "t_table";
        private SQLiteDatabase mDatabase;
    
        public DBUtils(Context context) {
            super(context, DB_NAME, null, VERSION);
        }
    
        public void setDb(SQLiteDatabase db) {
            this.mDatabase = db;
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            String sql = "create table if not exists "
                    + TABLE_NAME +
                    " (id integer primary key autoincrement,p_id integer,c_id integer,d_id integer,province varchar(20),city varchar(50),district  varchar(100),address varchar(200),attr varchar(200))";
            db.execSQL(sql);
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
            db.execSQL(sql);
            onCreate(db);
        }
    
    
        public void closeDb() {
            if (mDatabase != null)
                mDatabase.close();
        }
    
        public void insertBySql(DbEntity dbEntity) {
            mDatabase.beginTransaction();
            //sql中字符串字段一定要使用  ''
            StringBuilder sb = new StringBuilder();
            sb.append("'").append(dbEntity.getProvince()).append("'").append(",");
            sb.append("'").append(dbEntity.getCity()).append("'").append(",");
            sb.append("'").append(dbEntity.getDistrict()).append("'").append(",");
            sb.append("'").append(dbEntity.getAddress()).append("'").append(",");
            sb.append("'").append(dbEntity.getAttr()).append("'");
    
            String sql = "insert into " + TABLE_NAME +
                    "(p_id,c_id,d_id,province,city,district,address,attr)  values("
                    + dbEntity.getP_id() + "," + dbEntity.getC_id() + "," + dbEntity.getD_id() + ","
                    + sb.toString() + ")";
    
            mDatabase.execSQL(sql);
            mDatabase.setTransactionSuccessful();
        }
    
        public void insertByContentValues(DbEntity dbEntity) {
            String[] columns = {"id", "p_id", "province", "c_id", "city", "d_id", "district", "address", "attr"};
            ContentValues values = new ContentValues();
            values.put(columns[0], dbEntity.getP_id());
            values.put(columns[1], dbEntity.getProvince());
            values.put(columns[2], dbEntity.getC_id());
            values.put(columns[3], dbEntity.getCity());
            values.put(columns[4], dbEntity.getD_id());
            values.put(columns[5], dbEntity.getDistrict());
            values.put(columns[6], dbEntity.getAddress());
            values.put(columns[7], dbEntity.getAttr());
            mDatabase.insert(TABLE_NAME, "", values);
        }
    
        public void update(DbEntity dbEntity) {
            mDatabase.beginTransaction();
            String sql = "update " + TABLE_NAME + " set " +
                    "p_id=" + "'" + dbEntity.getP_id() + "'" +
                    ",province=" + "'" + dbEntity.getProvince() + "'" +
                    ",c_id=" + "'" + dbEntity.getC_id() + "'" +
                    ",city=" + "'" + dbEntity.getCity() + "'" +
                    ",d_id=" + "'" + dbEntity.getD_id() + "'" +
                    ",district=" + "'" + dbEntity.getDistrict() + "'" +
                    ",attr=" + "'" + dbEntity.getAttr() + "'" +
                    "where  id=" + dbEntity.getId();
            mDatabase.execSQL(sql);
            mDatabase.setTransactionSuccessful();
        }
    
        public void delete(DbEntity dbEntity) {
            String sql = "delete from " + TABLE_NAME + "where _id" + dbEntity.getId();
            mDatabase.execSQL(sql);
        }
    
        public List<DbEntity> findAll() {
            List<DbEntity> list = new ArrayList<>();
            String[] columns = {"id", "p_id", "province", "c_id", "city", "d_id", "district", "address", "attr"};
            String orderBy = "id desc";
            Cursor cursor = mDatabase.query(TABLE_NAME, columns, "", null, null, null, orderBy);
            DbEntity dbEntity;
            while (cursor.moveToNext()) {
                dbEntity = new DbEntity();
                dbEntity.setId(cursor.getInt(cursor.getColumnIndex(columns[0])));
                dbEntity.setP_id(cursor.getInt(cursor.getColumnIndex(columns[1])));
                dbEntity.setProvince(cursor.getString(cursor.getColumnIndex(columns[2])));
                dbEntity.setC_id(cursor.getInt(cursor.getColumnIndex(columns[3])));
                dbEntity.setCity(cursor.getString(cursor.getColumnIndex(columns[4])));
                dbEntity.setD_id(cursor.getInt(cursor.getColumnIndex(columns[5])));
                dbEntity.setDistrict(cursor.getString(cursor.getColumnIndex(columns[6])));
                dbEntity.setAddress(cursor.getString(cursor.getColumnIndex(columns[7])));
                dbEntity.setAttr(cursor.getString(cursor.getColumnIndex(columns[8])));
                list.add(dbEntity);
            }
    
            cursor.close();
            return list;
        }
    
        public DbEntity findEntityById(int id) {
            String[] columns = {"id", "p_id", "province", "c_id", "city", "d_id", "district", "address", "attr"};
            String sql = "select  * from " + TABLE_NAME + " where  id=" + id;
            Cursor cursor = mDatabase.rawQuery(sql, new String[]{});
            DbEntity dbEntity = null;
            while (cursor.moveToNext()) {
                dbEntity = new DbEntity();
                dbEntity.setId(cursor.getInt(cursor.getColumnIndex(columns[0])));
                dbEntity.setP_id(cursor.getInt(cursor.getColumnIndex(columns[1])));
                dbEntity.setProvince(cursor.getString(cursor.getColumnIndex(columns[2])));
                dbEntity.setC_id(cursor.getInt(cursor.getColumnIndex(columns[3])));
                dbEntity.setCity(cursor.getString(cursor.getColumnIndex(columns[4])));
                dbEntity.setD_id(cursor.getInt(cursor.getColumnIndex(columns[5])));
                dbEntity.setDistrict(cursor.getString(cursor.getColumnIndex(columns[6])));
                dbEntity.setAddress(cursor.getString(cursor.getColumnIndex(columns[7])));
                dbEntity.setAttr(cursor.getString(cursor.getColumnIndex(columns[8])));
            }
            cursor.close();
            return dbEntity;
        }
    
    }

三、使用初始化

DBUtils dbUtils=new DBUtils(this);
     SQLiteDatabase db=dbUtils.getReadableDatabase();
     dbUtils.setDb(db);