xUtils 里的DbUtils使用心得

不念不忘少年蓝@ 2022-08-19 13:12 147阅读 0赞

使用xUtils做Android数据库开发非常简便和得心应手，而且它本身还支持很多查询功能，比如一对多，select count和自定义sql查询等,并且支持事务（默认关闭）

下面是官方sample给的代码和我的一些使用心得

首先是两个实体类，对应两张表，这两张表中有一对多的关系

首先是多方：

import org.xutils.DbManager;
    import org.xutils.db.annotation.Column;
    import org.xutils.db.annotation.Table;
    import org.xutils.ex.DbException;
    
    import java.util.Date;
    import java.util.List;
    
    /**
     * Author: wyouflf
     * Date: 13-7-25
     * Time: 下午7:06
     */
    @Table(name = "parent", onCreated = "CREATE UNIQUE INDEX index_name ON parent(name,email)")//自定义表名，并且设置两个字段为索引，如果不在此定义表名，则表的名字为包名_类名，并把.换成_
    public class Parent {
    
        @Column(name = "id", isId = true)//设置为主键
        private int id;
    
        @Column(name = "name")//列名和成员名一致
        public String name;
    
        @Column(name = "email")
        private String email;
    
        @Column(name = "isAdmin")
        private boolean isAdmin;
    
        @Column(name = "time")
        private Date time;//可以使用java.util.date
    
        @Column(name = "date")
        private java.sql.Date date;//可以使用java.sql.date
    
        public List<Child> getChildren(DbManager db) throws DbException {
            return db.selector(Child.class).where("parentId", "=", this.id).findAll();//一对多的查询
        }
    
        // 一对一的查询
        //public Child getChild(DbManager db) throws DbException {
        //    return db.selector(Child.class).where("parentId", "=", this.id).findFirst();
        //}
    
        //get set方法必须写
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public boolean isAdmin() {
            return isAdmin;
        }
    
        public void setAdmin(boolean admin) {
            isAdmin = admin;
        }
    
        public Date getTime() {
            return time;
        }
    
        public void setTime(Date time) {
            this.time = time;
        }
    
        public java.sql.Date getDate() {
            return date;
        }
    
        public void setDate(java.sql.Date date) {
            this.date = date;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        @Override
        public String toString() {
            return "Parent{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", email='" + email + '\'' +
                    ", isAdmin=" + isAdmin +
                    ", time=" + time +
                    ", date=" + date +
                    '}';
        }
    }

下面是单方：

import org.xutils.DbManager;
    import org.xutils.db.annotation.Column;
    import org.xutils.db.annotation.Table;
    import org.xutils.ex.DbException;
    
    /**
     * Author: wyouflf
     * Date: 13-7-29
     * Time: 下午5:04
     */
    @Table(name = "child")
    public class Child {
    
        @Column(name = "id", isId = true)
        private int id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "email")
        private String email;
    
        @Column(name = "parentId" /*, property = "UNIQUE"//如果是一对一加上唯一约束*/)
        private long parentId; // 外键表id
    
        // 这个属性被忽略，不存入数据库
        private String willIgnore;
    
        @Column(name = "text")
        private String text;
    
        public Parent getParent(DbManager db) throws DbException {//一对一，多对一查询
            return db.findById(Parent.class, parentId);
        }
    
        public long getParentId() {
            return parentId;
        }
    
        public void setParentId(long parentId) {
            this.parentId = parentId;
        }
    
        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 String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public String getWillIgnore() {
            return willIgnore;
        }
    
        public void setWillIgnore(String willIgnore) {
            this.willIgnore = willIgnore;
        }
    
        public String getText() {
            return text;
        }
    
        public void setText(String text) {
            this.text = text;
        }
    
        @Override
        public String toString() {
            return "Child{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", email='" + email + '\'' +
                    ", parentId=" + parentId +
                    ", willIgnore='" + willIgnore + '\'' +
                    ", text='" + text + '\'' +
                    '}';
        }
    }

然后是官方给出的简单应用

import android.view.View;
    import android.widget.TextView;
    
    import org.xutils.DbManager;
    import org.xutils.db.table.DbModel;
    import org.xutils.sample.db.Child;
    import org.xutils.sample.db.Parent;
    import org.xutils.view.annotation.ContentView;
    import org.xutils.view.annotation.Event;
    import org.xutils.view.annotation.ViewInject;
    import org.xutils.x;
    
    import java.io.File;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    
    /**
     * Created by wyouflf on 15/11/4.
     */
    @ContentView(R.layout.fragment_db)
    public class DbFragment extends BaseFragment {
    
        //配置数据库基本信息
        DbManager.DaoConfig daoConfig = new DbManager.DaoConfig()
                .setDbName("test")//设置数据库的名字
                .setDbDir(new File("/sdcard"))//设置数据库的存储路径
                .setDbVersion(2)//设置当前数据库的版本
                .setDbUpgradeListener(new DbManager.DbUpgradeListener() {//设置数据库更新监听器
                    @Override
                    public void onUpgrade(DbManager db, int oldVersion, int newVersion) {
                        // TODO: ...
                        // db.addColumn(...);//添加字段的操作
                        // db.dropTable(...);//删除表的操作
                        // ...
                    }
                });
    
        @ViewInject(R.id.tv_db_result)
        private TextView tv_db_result;
    
        @Event(R.id.btn_test_db)
        private void onTestDbClick(View view) {
    
            // 一对多: (本示例的代码)
            // 自己在多的一方(child)保存另一方的(parentId), 查找的时候用parentId查parent或child.
            // 一对一:
            // 在任何一边保存另一边的Id并加上唯一属性: @Column(name = "parentId", property = "UNIQUE")
            // 多对多:
            // 再建一个关联表, 保存两边的id. 查询分两步: 先查关联表得到id, 再查对应表的属性.
    
            String temp = "";
    
            try {
    
                DbManager db = x.getDb(daoConfig);
    
                Child child = new Child();//多方
                child.setName("child's name");
    
                Parent test = db.selector(Parent.class).where("id", "in", new int[]{1, 3, 6}).findFirst();
                // long count = db.selector(Parent.class).where("id", "in", new int[]{1, 3, 6}).count();//查找符合条件的数据条数
                // Parent test = db.selector(Parent.class).where("id", "between", new String[]{"1", "5"}).findFirst();//查找符合条件的第一条数据
                if (test != null) {
                    child.setParentId(test.getId());//给多方设置查询的限制条件
    
                    temp += "first parent:" + test + "\n";//显示多方的查询结果
                    tv_db_result.setText(temp);
                }
    
                Parent parent = new Parent();
                parent.name = "测试" + System.currentTimeMillis();
                parent.setAdmin(true);
                parent.setEmail("wyouflf@qq.com");
                parent.setTime(new Date());
                parent.setDate(new java.sql.Date(new Date().getTime()));
                db.save(parent);
    
                db.saveBindingId(child);//保存对象关联数据库生成的id
    
                List<Child> children = db.selector(Child.class).findAll();
                temp += "children size:" + children.size() + "\n";
                tv_db_result.setText(temp);
                if (children.size() > 0) {
                    temp += "last children:" + children.get(children.size() - 1) + "\n";
                    tv_db_result.setText(temp);
                }
    
                Calendar calendar = Calendar.getInstance();
                calendar.add(Calendar.DATE, -1);
                calendar.add(Calendar.HOUR, 3);
    
                List<Parent> list = db.selector(Parent.class)
                        .where("id", "<", 54)
                        .and("time", ">", calendar.getTime())//以java.util.date作为查询条件
                        .orderBy("id")
                        .limit(10).findAll();
                temp += "find parent size:" + list.size() + "\n";
                tv_db_result.setText(temp);
                if (list.size() > 0) {
                    temp += "last parent:" + list.get(list.size() - 1) + "\n";
                    tv_db_result.setText(temp);
                }
    
                //parent.name = "hahaha123";
                //db.update(parent);
    
                Parent entity = child.getParent(db);
                temp += "find by id:" + entity.toString() + "\n";
                tv_db_result.setText(temp);
    
                List<DbModel> dbModels = db.selector(Parent.class)//DbModel是个很有用的类，用于不查询实体的情况
                        .groupBy("name")//可以使用groupBy
                        .select("name", "count(name) as count").findAll();//在这里查询结果是数据的条数
                temp += "group by result:" + dbModels.get(0).getDataMap() + "\n";
                tv_db_result.setText(temp);
    
            } catch (Throwable e) {
                temp += "error :" + e.getMessage() + "\n";
                tv_db_result.setText(temp);
            }
        }
    
    }

xUtils里面还有很多非常有用的自定义查询sample里面没有，尤其是以原生sql语句进行查询的方法，下面是个例子

List<DbModel> offersmodelList = getDbUtils().findDbModelAll(
                        DbModelSelector.from(IMGRestaurantOffer.class)
                                .select("id", "offerId", "offerType",
                                        "offerDescription", "offerImage",
                                        "offerTerms", "offerStartDate",
                                        "offerEndDate", "offerStartTime",
                                        "offerEndTime", "offerTitle",
                                        "offerOff", "offerSlotId",
                                        "offerSlotMinDisc",
                                        "offerSlotMaxDisc", "offerMenu",
                                        "offerSlotList", "offerUrl",
                                        "tagLine", "restaurantId")
                                .where(WhereBuilder.b()
                                        .and("restaurantId", "=", restaurantId)
                                        .and("offerStartDate", "<=", maxDate)
                                        .and("offerEndDate", ">=", minDate))
                                .orderBy("offerType", false).orderBy("offerSlotMaxDisc", true));
                if (offersmodelList != null && offersmodelList.size() > 0) {
                    final int offermodellistsize = offersmodelList.size();
    
                    int specialofferlistindex = offermodellistsize;
                    for (int modelindex = (offermodellistsize - 1); modelindex >= 0; --modelindex) {
                        DbModel model = offersmodelList.get(modelindex);
                        if (model != null) {
                           
                                RestaurantOffer oneofferitem = new RestaurantOffer();
                                oneofferitem.setId(model.getLong("id"));
                                oneofferitem.setOfferId(model.getString("offerId"));
                                oneofferitem.setOfferType(model.getInt("offerType"));
                                oneofferitem.setOfferDescription(model.getString("offerDescription"));
                                oneofferitem.setOfferImage(model.getString("offerImage"));
                                oneofferitem.setOfferTerms(model.getString("offerTerms"));
                                oneofferitem.setOfferStartDate(model.getString("offerStartDate"));
                                oneofferitem.setOfferEndDate(model.getString("offerEndDate"));
                                oneofferitem.setOfferStartTime(model.getLong("offerStartTime"));
                                oneofferitem.setOfferEndTime(model.getLong("offerEndTime"));
                                oneofferitem.setOfferTitle(model.getString("offerTitle"));
                                oneofferitem.setOfferOff(model.getString("offerOff"));
                                oneofferitem.setOfferSlotId(model.getString("offerSlotId"));
                                oneofferitem.setOfferSlotMinDisc(model.getString("offerSlotMinDisc"));
                                oneofferitem.setOfferSlotMaxDisc(model.getString("offerSlotMaxDisc"));
                                oneofferitem.setOfferMenu(model.getString("offerMenu"));
                                oneofferitem.setOfferSlotList(model.getString("offerSlotList"));
                                oneofferitem.setOfferUrl(model.getString("offerUrl"));
                                oneofferitem.setRestaurantId(model.getString("restaurantId"));
                                oneofferitem.setTagLine(model.getString("tagLine"));
                                result.add(0, oneofferitem);

还有手写sql的多表联查

final String sqlStr = "SELECT DISTINCT A.offerId,"
                        + " A.id, A.offerType, A.offerDescription, A.offerImage, A.offerTerms,"
                        + " A.offerStartDate, A.offerEndDate, A.offerStartTime, A.offerEndTime,"
                        + " A.offerTitle, A.offerOff, A.tagLine,"
                        + " A.offerSlotId, A.offerSlotMinDisc, A.offerSlotMaxDisc, A.offerSlotList,"
                        + " A.offerMenu, A.offerUrl, A.restaurantId, A.hasNewSign"
                        + " FROM com_xxx_yyy_model_RestaurantOffer AS A"
                        + " LEFT OUTER JOIN com_xxx_yyy_model_OfferSlot AS B"
                        + " ON A.offerId = B.offerId"
                        + " WHERE A.restaurantId = ?"
                        + " ORDER BY A.offerType ASC, A.offerSlotMaxDisc DESC";
    
    
                SqlInfo sqlinfo = new SqlInfo();
                sqlinfo.setSql(sqlStr);
    //			sqlinfo.addBindArgs(restaurantId, currentdatestring, currentdatestring);
                sqlinfo.addBindArgs(restaurantId);
                List<DbModel> offersmodelList = db.findDbModelAll(sqlinfo);
                if (offersmodelList != null && offersmodelList.size() > 0) {
                    final int offermodellistsize = offersmodelList.size();
    
                    int specialofferlistindex = offermodellistsize;
                    for (int modelindex = (offermodellistsize - 1); modelindex >= 0; --modelindex) {
                        DbModel model = offersmodelList.get(modelindex);
                        if (model != null) {
                            int offerType = model.getInt("offerType");
                            if (offerType == 4) {
                                RestaurantOffer oneofferitem = new RestaurantOffer();
                                oneofferitem.setId(model.getLong("id"));
                                oneofferitem.setOfferId(model.getString("offerId"));
                                oneofferitem.setOfferType(model.getInt("offerType"));
                                oneofferitem.setOfferDescription(model.getString("offerDescription"));
                                oneofferitem.setOfferImage(model.getString("offerImage"));
                                oneofferitem.setOfferTerms(model.getString("offerTerms"));
                                oneofferitem.setOfferStartDate(model.getString("offerStartDate"));
                                oneofferitem.setOfferEndDate(model.getString("offerEndDate"));
                                oneofferitem.setOfferStartTime(model.getLong("offerStartTime"));
                                oneofferitem.setOfferEndTime(model.getLong("offerEndTime"));
                                oneofferitem.setOfferTitle(model.getString("offerTitle"));
                                oneofferitem.setOfferOff(model.getString("offerOff"));
                                oneofferitem.setOfferSlotId(model.getString("offerSlotId"));
                                oneofferitem.setOfferSlotMinDisc(model.getString("offerSlotMinDisc"));
                                oneofferitem.setOfferSlotMaxDisc(model.getString("offerSlotMaxDisc"));
                                oneofferitem.setOfferMenu(model.getString("offerMenu"));
                                oneofferitem.setOfferSlotList(model.getString("offerSlotList"));
                                oneofferitem.setOfferUrl(model.getString("offerUrl"));
                                oneofferitem.setRestaurantId(model.getString("restaurantId"));
                                oneofferitem.setTagLine(model.getString("tagLine"));
                                oneofferitem.setHasNewSign(model.getInt("hasNewSign"));