【转载】Xutils3-Dbutils

Github源码地址：https://github.com/wyouflf/xUtils3

下面是源码中sample关于Dbutils的使用代码：

import android.view.View;

import android.widget.TextView;

import org.xutils.DbManager;

import org.xutils.common.util.KeyValue;

import org.xutils.db.sqlite.WhereBuilder;

import org.xutils.db.table.DbModel;

import org.xutils.ex.DbException;

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.ArrayList;

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.db")

            // 不设置dbDir时, 默认存储在app的私有目录.

            .setDbDir(new File("/sdcard")) // "sdcard"的写法并非最佳实践, 这里为了简单, 先这样写了.

            .setDbVersion(2)

            .setDbOpenListener(new DbManager.DbOpenListener() {

                @Override

                public void onDbOpened(DbManager db) {

                    // 开启WAL, 对写入加速提升巨大

                    db.getDatabase().enableWriteAheadLogging();

                }

            })

            .setDbUpgradeListener(new DbManager.DbUpgradeListener() {

                @Override

                public void onUpgrade(DbManager db, int oldVersion, int newVersion) {

                    // TODO: ...

                    // db.addColumn(...);

                    // db.dropTable(...);

                    // ...

                    // or

                    // db.dropDb();

                }

            });

    @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("[email protected]");

            parent.setTime(new Date());

            parent.setDate(new java.sql.Date(new Date().getTime()));

            //db.save(parent);

            db.saveBindingId(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())

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

            }

            // test update

            parent.name = "hahaha123";

            parent.setEmail("[email protected]");

            db.update(parent);

            db.update(parent, "name", "email");

            db.update(Parent.class,

                    WhereBuilder.b("id", "=", 1).and("isAdmin", "=", true),

                    new KeyValue("name", "test_name"), new KeyValue("isAdmin", false));

            Parent entity = child.getParent(db);

            temp += "find by id:" + entity.toString() + "\n";

            tv_db_result.setText(temp);

            List<DbModel> dbModels = db.selector(Parent.class)

                    .groupBy("name")

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

        }

    }

    @Event(R.id.btn_test_db2)

    private void onTestDb2Click(View view) {

        tv_db_result.setText("wait...");

        x.task().run(new Runnable() { // 异步执行

            @Override

            public void run() {

                DbManager db = x.getDb(daoConfig);

                String result = "";

                List<Parent> parentList = new ArrayList<Parent>();

                for (int i = 0; i < 1000; i++) {

                    Parent parent = new Parent();

                    parent.setAdmin(true);

                    parent.setDate(new java.sql.Date(1234));

                    parent.setTime(new Date());

                    parent.setEmail(i + "[email protected]");

                    parentList.add(parent);

                }

                long start = System.currentTimeMillis();

                for (Parent parent : parentList) {

                    try {

                        db.save(parent);

                    } catch (DbException ex) {

                        ex.printStackTrace();

                    }

                }

                result += "插入1000条数据:" + (System.currentTimeMillis() - start) + "ms\n";

                start = System.currentTimeMillis();

                try {

                    parentList = db.selector(Parent.class).orderBy("id", true).limit(1000).findAll();

                } catch (DbException ex) {

                    ex.printStackTrace();

                }

                result += "查找1000条数据:" + (System.currentTimeMillis() - start) + "ms\n";

                start = System.currentTimeMillis();

                try {

                    db.delete(parentList);

                } catch (DbException ex) {

                    ex.printStackTrace();

                }

                result += "删除1000条数据:" + (System.currentTimeMillis() - start) + "ms\n";

                // 批量插入

                parentList = new ArrayList<Parent>();

                for (int i = 0; i < 1000; i++) {

                    Parent parent = new Parent();

                    parent.setAdmin(true);

                    parent.setDate(new java.sql.Date(1234));

                    parent.setTime(new Date());

                    parent.setEmail(i + "[email protected]");

                    parentList.add(parent);

                }

                start = System.currentTimeMillis();

                try {

                    db.save(parentList);

                } catch (DbException ex) {

                    ex.printStackTrace();

                }

                result += "批量插入1000条数据:" + (System.currentTimeMillis() - start) + "ms\n";

                try {

                    parentList = db.selector(Parent.class).orderBy("id", true).limit(1000).findAll();

                    db.delete(parentList);

                } catch (DbException ex) {

                    ex.printStackTrace();

                }

                final String finalResult = result;

                x.task().post(new Runnable() { // UI同步执行

                    @Override

                    public void run() {

                        tv_db_result.setText(finalResult);

                    }

                });

            }

        });

    }

}

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

    @Column(name = "date")

    private java.sql.Date 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();

    //}

    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 +

                '}';

    }

}