Android SQL数据库操作（使用xUtils3）

Android SQL数据库操作（使用xUtils3）

目录

---

基础配置

xUtils3地址：
https://github.com/wyouflf/xUtils3/tree/master

使用Gradle构建时添加一下依赖即可:

compile 'org.xutils:xutils:3.5.0'

需要的权限

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

初始化

// 在application的onCreate中初始化
@Override
public void onCreate() {
    super.onCreate();
    x.Ext.init(this);
    x.Ext.setDebug(BuildConfig.DEBUG); // 是否输出debug日志, 开启debug会影响性能.
        setDaoConfig();//配置数据库参数 创建数据库
}

//获取数据库操作对象
    public DbManager getDbManager() {
        return db;
    }

    public static void setDaoConfig() {
        DbManager.DaoConfig daoConfig = new DbManager.DaoConfig()
                .setDbName("sql.db")
                // 不设置dbDir时, 默认存储在app的私有目录.
                //.setDbDir(new File(Constant.PATH_DB))
                //数据库版本可使用app版本号
                .setDbVersion(1)
                //开启事务
                .setAllowTransaction(true)
                .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) {
                        // 版本升级监听 可添加字段 删除表 删除数据库等 

                        // db.addColumn(...);
                        // db.dropTable(...);
                        // db.dropDb();
                    }
                });
        db = x.getDb(daoConfig);
    }

基础类

@Table(name = "student")
public class StudentBean {
    @Column(name = "id", isId = true)
    private int id;
    @Column(name = "name")
    private String name;
    @Column(name = "age")
    private int age;
    @Column(name = "sex")
    private String sex;

  //重写setget方法

---

基本操作

增

保存一条数据

public class StudentSql {

    DbManager db;

    public StudentSql(DbManager db) {
        this.db = db;
    }

    //保存一条数据
    public void save(final StudentBean bean) {
        try {
            db.save(bean);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

        StudentSql    sql = new StudentSql(MyApp.getInstance().getDbManager());

        StudentBean bean=new StudentBean();
        bean.setName("张三");
        bean.setAge(18);
        bean.setSex("男");
        sql.save(bean);

保存一组数据

    //保存一组数据
    public void saveList(ArrayList<StudentBean> list) {
        try {
            db.save(list);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void addData() {
        StudentBean bean = new StudentBean();
        bean.setName("张三");
        bean.setAge(18);
        bean.setSex("男");
        StudentBean bean1 = new StudentBean();
        bean1.setName("李四");
        bean1.setAge(19);
        bean1.setSex("男");
        StudentBean bean2 = new StudentBean();
        bean2.setName("王五");
        bean2.setAge(20);
        bean2.setSex("男");
        ArrayList<StudentBean> list=sql.selectorAll();
        list.add(bean);
        list.add(bean1);
        list.add(bean2);
        sql.saveList(list);
    }

删

删除一条数据

    //删除某个
    public void delete(final StudentBean StudentBean) {
        try {
            db.delete(StudentBean);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

删除某条数据需要确定其ID

    private void del_data() {
        StudentBean bean1 = new StudentBean();
        bean1.setId(2);
//        bean1.setName("李四");
//        bean1.setAge(19);
//        bean1.setSex("男");
        sql.delete(bean1);
    }

删除所有

    //删除所有
    public void deleteAll() {
        try {
            db.dropTable(StudentBean.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

删除所有 就是删除表

删除一组数据

    public void deleteList(ArrayList<StudentBean> list) {
        try {
            db.delete(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

条件删除

    //删除某个
    public void deleteOpt() {
        try {
            db.delete(StudentBean.class, WhereBuilder.b("name", "in", new String[]{"张三"}));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

删除名字叫张三的数据

    //删除某个
    public void deleteOpt() {
        try {
            db.delete(StudentBean.class, WhereBuilder.b("age", ">", 18));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

删除年龄大于18的

改

修改一条数据

    //修改数据
    public void up(StudentBean newBean) {
        try {
            db.update(newBean);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

修改数据有id判断数据是否存在

    private void upData() {
        StudentBean bean1 = new StudentBean();
        bean1.setId(2);
        bean1.setName("赵六");
        bean1.setAge(19);
        bean1.setSex("女");
        sql.up(bean1);
    }

批量修改数据

 //修改数据
    public void upOpt() {
        try {
            db.update(StudentBean.class,WhereBuilder.b("age", ">", 18),new KeyValue("age", 16));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

查

查询所有数据

    //查找所有
    public ArrayList<StudentBean> selectorAll() {
        ArrayList<StudentBean> children = new ArrayList<StudentBean>();
        ;
        try {
            children = (ArrayList<StudentBean>) db.selector(StudentBean.class).findAll();
            if (children == null) {
                return new ArrayList<>();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return children;
    }

查询某条

    //查找某个

    public StudentBean selectorFirst() {

        StudentBean bean = new StudentBean();
        try {
            bean = db.selector(StudentBean.class).where("name", "in", new String[]{"张三"}).findFirst();
            return bean;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

模糊查询

 public ArrayList<StudentBean> selectorOpt() {

        ArrayList<StudentBean> children = new ArrayList<StudentBean>();
        try {
            children = (ArrayList<StudentBean>) db.selector(StudentBean.class)
                    .where("name", "LIKE", "张%")
                    .findAll();
            if (children == null) {
                return new ArrayList<>();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return children;
    }

条件查询

 public ArrayList<StudentBean> selectorOpt1() {

        ArrayList<StudentBean> children = new ArrayList<StudentBean>();
        try {
            children = (ArrayList<StudentBean>) db.selector(StudentBean.class)
                    .where("age", "between", new int[]{19, 20})
                    .findAll();
            if (children == null) {
                return new ArrayList<>();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return children;
    }

分组查询

public void selectotGroupBy() {

        try {
            List<DbModel> dbModels = db.selector(StudentBean.class)
                    .groupBy("age")
                    .select("age").findAll();

            for (int i = 0; i < dbModels.size(); i++) {
//                System.out.println(dbModels.get(i).getDataMap().toString());
                System.out.println(dbModels.get(i).getString("age"));
            }
        } catch (DbException e) {
            e.printStackTrace();
        }
    }

查询数量

        long sum=db.selector(StudentBean.class).count();

其他

排序

orderBy("id")

指定数量

limit(10)

指定位置

offset(5)