【转载】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("wyouflf@qq.com"); 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("wyouflf@gmail.com"); 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 + "_@qq.com"); 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 + "_@qq.com"); 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 + '}'; } }