xUtils3.3.x数据库操作实例

xUtils是国人开发的一款功能丰富的Android开发框架，主要包括网络请求、图片加载、数据库操作等模块，本章主要介绍xUtils3.3.x在数据库方面的使用。

要使用xUtils，需要首先在Application或主Activity中进行xUtils初始化，我的xUtils3.3.x的初始化和数据库的配置都是在Application中进行的，具体代码如下：

import android.app.Application;
import android.content.SharedPreferences;
import android.os.Environment;
import android.os.storage.StorageManager;
import android.util.Log;

import com.lnint.mediaplayer.utils.Constants;
import org.xutils.DbManager;
import org.xutils.x;
import java.io.File;
import java.lang.reflect.Method;

/**
 * xUtils3.3应用demo
 * Created by wangpf on 2017/7/1.
 */
public class MyApplication extends Application {
    private DbManager.DaoConfig daoConfig;
    private static MyApplication mInstance = null;

    public DbManager.DaoConfig getDaoConfig() {
        return daoConfig;
    }

    @Override
    public void onCreate() {
        super.onCreate();
        mInstance = this;
        x.Ext.init(this);
        x.Ext.setDebug(BuildConfig.DEBUG);

        String rootPath = MyApplication.getExternalStoragePath();
        File dbFile = new File(rootPath + "/media/db/");
        daoConfig = new DbManager.DaoConfig()
                .setDbDir(dbFile)
                .setDbName("media_dbs")//创建数据库的名称
                .setDbVersion(1)//数据库版本号
                .setDbUpgradeListener(new DbManager.DbUpgradeListener() {
                    @Override
                    public void onUpgrade(DbManager db, int oldVersion, int newVersion) {
                        // db.addColumn(...);
                        // db.dropTable(...);
                        // ...
                    }
                });//数据库更新操作
    }......

我们以保存并查询视频文件的播放历史为例，重点讲解对表sys_play_his的增删改查操作。

由于xUtils是一个ORM框架，所以第一步工作就是基于表sys_play_his创建实体，具体代码如下：

实体BaseEntity

import org.xutils.db.annotation.Column;

/**
 * 实体BaseEntity
 * Created by wangpf on 2016/11/8.
 */

public class BaseEntity {
    //id
    @Column(name = "id", isId = true)
    private int id;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
}

数据库操作实体

import org.xutils.db.annotation.Column;
import org.xutils.db.annotation.Table;
import java.util.Date;

/**
 * 保存媒体文件播放历史
 * Created by wangpf on 2017/7/21.
 */
@Table(name = "sys_play_his")
public class PlayHisEntity extends BaseEntity {
    //文件名
    @Column(name = "fileName")
    private String fileName;
    //播放开始时间
    private Date startTime;
    //播放结束时间
    @Column(name = "endTime")
    private Date endTime;
    //广告机编号
    @Column(name = "equipNo")
    private String equipNo;
    //固件版本号
    @Column(name = "firmware")
    private String firmware;

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    ......
}

准备工作做好之后，就可以对数据库进行操作，具体操作如下：

（1）数据插入

DbManager db = x.getDb(((MyApplication)getApplicationContext()).getDaoConfig());
                try {
                    PlayHisEntity entity = new PlayHisEntity();
                    entity.setFileName("20170803120203.mp4");
                    entity.setEndTime(new Date());
                    entity.setFirmware("4.4.4");
                    db.saveBindingId(entity);
                }catch (DbException e) {
                    e.printStackTrace();
                }

（2）数据删除

DbManager db = x.getDb(MyApplication.getInstance().getDaoConfig());
        try {
            Date date = DateUtils.getLastDay(30);
            db.delete(PlayHisEntity.class, WhereBuilder.b("endTime","<=",date));
        }catch (DbException e) {
            e.printStackTrace();
        }

（3）数据查询

DbManager db = x.getDb(MyApplication.getInstance().getDaoConfig());
        try {
            if(StringUtils.isNotEmpty(playHisEntity.getFileName())) {//根据文件名查询
                List<DbModel> dbModels = new ArrayList<DbModel>();
                dbModels = db.selector(PlayHisEntity.class)
                        .where("fileName","like","%"+playHisEntity.getFileName()+"%")
                        .groupBy("fileName")
                        .select("fileName", "count(fileName) as count").findAll();

                List<VideoPlayEntity> playList = new ArrayList<VideoPlayEntity>();
                for(DbModel dbModel : dbModels) {
                    VideoPlayEntity entity = new VideoPlayEntity();
                    entity.setName(dbModel.getString("fileName"));
                    entity.setNum(dbModel.getInt("count"));
                    playList.add(entity);
                }
            }else if(playHisEntity.getStartTime() != null && playHisEntity.getEndTime() != null) {//根据时间查询
                List<DbModel> dbModels = db.selector(PlayHisEntity.class)
                        .where("endTime","between",new Date[]{playHisEntity.getStartTime(), playHisEntity.getEndTime()})
                        .groupBy("fileName")
                        .select("fileName", "count(fileName) as count").findAll();

                List<VideoPlayEntity> playList = new ArrayList<VideoPlayEntity>();
                for(DbModel dbModel : dbModels) {
                    VideoPlayEntity entity = new VideoPlayEntity();
                    entity.setName(dbModel.getString("fileName"));
                    entity.setNum(dbModel.getInt("count"));
                    playList.add(entity);
                }
            }
        }catch (DbException e) {
            e.printStackTrace();
        }