这篇转载的文章有我对xUtils3操作数据库新学到的一些东西。
1.为两个字段设置索引
(name = "parent", onCreated = "CREATE UNIQUE INDEX index_name ON parent(name,email)")//自定义表名，并且设置两个字段为索引，如果不在此定义表名，则表的名字为包名_类名，并把.换成_
2.字段使用Date类型
(name = "time")
private Date time;//可以使用java.util.date
(name = "date")
private java.sql.Date date;//可以使用java.sql.date
3.可直接在类中写查询方法
public List<Child> getChildren(DbManager db) throws DbException {
return db.selector(Child.class).where("parentId", "=", this.id).findAll();//一对多的查询
}4.可对字段设置索引保持唯一
(name = "parentId" /*, property = "UNIQUE"//如果是一对一加上唯一约束*/)
private long parentId; // 外键表id
5.可忽略属性，只使用不存入数据库
// 这个属性被忽略，不存入数据库
private String willIgnore;
6.一对一，多对一查询
public Parent getParent(DbManager db) throws DbException {
//一对一，多对一查询
return db.findById(Parent.class, parentId);
}
以上具体参考文章 xUtils 里的DbUtils使用心得
使用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"));