SQLiteDatabase工具类(增删改查)

本文介绍SQLite数据库在Android开发中的应用,包括实体类设计、SQLiteDatabase工具类封装及CURD操作,展示如何在Android应用中有效管理和操作本地数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

     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);

   

大家好,个人觉得用Sqlite数据库时,经常需要进行机械性的CRUD操作,故对其进行了一下封装,希望能起到抛砖引玉的作用。 目的:封装共有的CRUD 下面简单的说一下使用步骤,如果觉得多余,可以无视。 1. 实现自己的DBHelper: /** * * @author Kee.Li * * 1. 继承了SmartDBHelper,不需要重写SQLiteOpenHelper的那两个方法 * 2. 父类构造方法参数modelClasses是实体类的数组,也就是需要生产表的类的Class数组 * */ public class DBHelper extends SmartDBHelper { //数据库名称 private final static String DATABASE_NAME = "books.db"; //数据库版本 private final static int DATABASE_VERSION = 2; //需要生成数据库表的类的数组 private final static Class<?>[] modelClasses = {Book.class,User.class}; public DBHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION,modelClasses); } } 2.创建app需要的实体,也就是对应的数据库表(这里的实体添加到DBHelper的modelClasses数组中) /** * 数据库的实体 * @author Kee.Li * 关于注解: * Table: 此类对应的数据库表名 * Id:标识此属性为数据库自增长的id,应为int型 * Column:标识此属性对应的数据库字段名 */ @Table(name="t_books") public class Book{ @Id @Column(name="book_id") private int bookId; @Column(name="book_name") private String bookName; @Column(name="book_author") private String bookAuthor; //set get 方法省略.... } 3. 实现DAO,也就是对实体的CRUD类 /** * @author Kee.Li * * 此类只需要继承TemplateDAO,在构造方法里面给父类的属性dbHelper赋值,即可实现CRUD操作 * 若有复杂的操作,可以自定义方法 */ public class BookDAO extends TemplateDAO { /** * 创建DAO时初始化连接数据库对象helper * @param context */ public BookDAO(Context context) { super(new DBHelper(context)); } } 4. activity的调用 bookDAO = new BookDAO(this); List books = bookDAO.find(); 好了,到此结束,如果有什么好的建议或者意见,希望可以共同学习!谢谢大家!
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值