分享一波超实用的Android DataBaseUtils数据对象插入-查询-删除

简单介绍

Android端的数据库sqlite，小巧基于文件。用于本地存储数据很是方便。但是还是要去主动设计表结构，查询结果等也是游标形式。

对于懒癌晚期的我来说，太麻烦了。难道不能再简单点？能即插即用？所以我决定写个工具。虽然不是很复杂高端，单我觉得客户端存储的数据本来就不用太复杂。下面慢慢介绍。

定义对象(对应表)

   /**
     * 示例
     */
    public static class Example {
        @DBField("VARCHAR(100)")
        public String name;
        @DBField("VARCHAR(100)")
        public String password;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }
    }

和常规对象的定义类似，这里我为了方便定义为一个内部类，如果业务较复杂可以单独放一个package。
字段的注解@DBField("VARCHAR(100)")，@DBField表示该属性为建表字段，值为SQL语句中字段，这里我默认存储字符长100。

以下为注解定义:

    /**
     * 数据库字段注解
     */
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    protected @interface DBField {
        String value() default "";
    }

对象注册

对象定义还不行，还需要能识别到对象，其中提供了方法。用于注册并返回所有需要建表的对象。

/**
     * 获取所有数据库对象
     *
     * @return
     */
    private List<Class> getObjects() {
        List<Class> list = new ArrayList<>();
        list.add(Example.class);
        return list;
    }

实现原理

用到SQLiteOpenHelper辅助创建数据库文件，及表

以下:

        @Override
        public void onCreate(SQLiteDatabase sqLiteDatabase) {
            List<Class> objects = getObjects();

            for (Class object : objects) {
                StringBuffer sql = new StringBuffer();
                String name = object.getSimpleName();

                sql.append("CREATE TABLE ");
                sql.append(name);
                sql.append(" (");
                fillSql(object, sql);

                sql.replace(sql.length() - 1, sql.length(), "");
                sql.append(")");
                sqLiteDatabase.execSQL(sql.toString());
            }
        }

     /**
     * 利用字段注解填充sql
     *
     * @param mClass
     * @param sql
     */
    private void fillSql(Class mClass, StringBuffer sql) {
        Field[] fields = mClass.getDeclaredFields();
        for (Field f : fields) {
            if (f.isAnnotationPresent(DBField.class)) {
                DBField bind = f.getAnnotation(DBField.class);
                String type = bind.value();
                sql.append(f.getName().toUpperCase());
                sql.append(" ");
                sql.append(type);
                sql.append(",");
            }
        }
    }

对表对象的操作

insert

public void insert(Object object)
object即我们上面创建的表对象实例

query

public <T> List<T> query(T t)
t即查询对象，对象中属性如不为null则作为查询条件。

delete

public void insert(Object object)
object对象中属性如不为null则作为删除条件。慎用，如果属性全为空则删除所有。

此处提供了3个方法，增、删、查。我觉的改在客户端意义不大，组合操作（删除，新增）即可实现

DataBaseUtils.java

package xyz.wbsite.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class DataBaseUtils {
    private String defaultDBName = "db";
    private SQLiteOpenHelper sqLiteOpenHelper;
    private SQLiteDatabase writableDatabase;

    private class SQLiteOpenHelperImpl extends SQLiteOpenHelper {

        public SQLiteOpenHelperImpl(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        public SQLiteOpenHelperImpl(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
            super(context, name, factory, version, errorHandler);
        }

        @Override
        public void onCreate(SQLiteDatabase sqLiteDatabase) {
            List<Class> objects = getObjects();

            for (Class object : objects) {
                StringBuffer sql = new StringBuffer();
                String name = object.getSimpleName();

                sql.append("CREATE TABLE ");
                sql.append(name);
                sql.append(" (");
                fillSql(object, sql);

                sql.replace(sql.length() - 1, sql.length(), "");
                sql.append(")");
                sqLiteDatabase.execSQL(sql.toString());
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

        }
    }

    private DataBaseUtils() {
    }

    public DataBaseUtils(Context context) {
        sqLiteOpenHelper = new SQLiteOpenHelperImpl(context, defaultDBName, null, 1);
        writableDatabase = sqLiteOpenHelper.getWritableDatabase();
    }


    public <T> List<T> query(T t) {
        ArrayList<T> list = new ArrayList<>();
        List<Class> objects = getObjects();
        for (Class a : objects) {
            if (t.getClass() == a) {
                //1、获取表名
                String table = a.getSimpleName();
                //2、获取字段列表
                List<Field> fs = new ArrayList<>();
                for (Field f : a.getDeclaredFields()) {
                    if (f.isAnnotationPresent(DBField.class)) {
                        fs.add(f);
                    }
                }
                String[] columns = new String[fs.size()];
                int where = 0;
                for (int i = 0; i < columns.length; i++) {
                    columns[i] = fs.get(i).getName().toUpperCase();
                    try {
                        Object o = fs.get(i).get(t);
                        if (o != null) {
                            where++;
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }

                }
                //3、获取where条件
                StringBuilder selectionSB = new StringBuilder("");
                String[] selectionArgs = null;
                if (where > 0) {
                    selectionArgs = new String[where];
                    where = 0;
                    for (int i = 0; i < columns.length; i++) {
                        columns[i] = fs.get(i).getName().toUpperCase();
                        try {
                            Object o = fs.get(i).get(t);
                            if (o != null) {
                                if ("".equals(selectionSB.toString())) {
                                    selectionSB.append(" ");
                                } else {
                                    selectionSB.append(" and");
                                }
                                selectionSB.append(columns[i]).append(" = ?");
                                selectionArgs[where] = o.toString();
                                where++;
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
                //4、其他暂未支持

                Cursor query = writableDatabase.query(table, columns, selectionSB.toString(), selectionArgs, null, null, "");

                while (query.moveToNext()) {
                    try {
                        T o = (T) a.newInstance();
                        for (int i = 0; i < fs.size(); i++) {
                            Field field = fs.get(i);
                            String string = query.getString(i);
                            field.set(o, string);
                        }
                        list.add(o);
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return list;
    }

    public void delete(Object object) {
        List<Class> objects = getObjects();
        for (Class aClass : objects) {
            if (object.getClass() == aClass) {
                //1、获取表名
                String table = aClass.getSimpleName();
                //2、获取字段列表
                List<Field> fs = new ArrayList<>();
                for (Field f : aClass.getDeclaredFields()) {
                    if (f.isAnnotationPresent(DBField.class)) {
                        fs.add(f);
                    }
                }
                String[] columns = new String[fs.size()];
                int where = 0;
                for (int i = 0; i < columns.length; i++) {
                    columns[i] = fs.get(i).getName().toUpperCase();
                    try {
                        Object o = fs.get(i).get(object);
                        if (o != null) {
                            where++;
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }

                }
                //3、获取where条件
                StringBuilder selectionSB = new StringBuilder("");
                String[] selectionArgs = null;
                if (where > 0) {
                    selectionArgs = new String[where];
                    where = 0;
                    for (int i = 0; i < columns.length; i++) {
                        columns[i] = fs.get(i).getName().toUpperCase();
                        try {
                            Object o = fs.get(i).get(object);
                            if (o != null) {
                                if ("".equals(selectionSB.toString())) {
                                    selectionSB.append(" ");
                                } else {
                                    selectionSB.append(" and ");
                                }
                                selectionSB.append(columns[i]).append(" = ?");
                                selectionArgs[where] = o.toString();
                                where++;
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
                writableDatabase.delete(table, selectionSB.toString(), selectionArgs);
            }
        }
    }

    public void insert(Object object) {

        List<Class> objects = getObjects();
        for (Class aClass : objects) {
            if (object.getClass() == aClass) {
                StringBuffer sql = new StringBuffer();
                String tableName = aClass.getSimpleName();
                sql.append("INSERT INTO ");
                sql.append(tableName.toUpperCase());
                sql.append("(");

                ContentValues contentValues = new ContentValues();
                StringBuffer fieldsSql = new StringBuffer();
                StringBuffer valueSql = new StringBuffer();
                for (Field f : aClass.getDeclaredFields()) {
                    if (f.isAnnotationPresent(DBField.class)) {

                        f.setAccessible(true);
                        try {
                            String value = "";
                            Object o = f.get(object);
                            if (o instanceof String) {
                                value = (String) o;
                                valueSql.append(value);
                            } else {
                                valueSql.append("null");
                            }
                            valueSql.append(",");
                            contentValues.put(f.getName().toUpperCase(), value);
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                            valueSql.append("null,");
                        }

                        fieldsSql.append(f.getName().toUpperCase());
                        fieldsSql.append(",");
                    }
                }

                if (",".equals(fieldsSql.substring(fieldsSql.length() - 1, fieldsSql.length()))) {
                    fieldsSql.replace(fieldsSql.length() - 1, fieldsSql.length(), "");
                }
                if (",".equals(valueSql.substring(valueSql.length() - 1, valueSql.length()))) {
                    valueSql.replace(valueSql.length() - 1, valueSql.length(), "");
                }
                sql.append(fieldsSql);
                sql.append(") VALUES (");
                sql.append(valueSql);
                sql.append(")");

                writableDatabase.insert(tableName, null, contentValues);
            }
        }
    }

    /**
     * 获取所有数据库对象
     *
     * @return
     */
    private List<Class> getObjects() {
        List<Class> list = new ArrayList<>();
        list.add(Example.class);
        return list;
    }


    /**
     * 数据库字段注解
     */
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    protected @interface DBField {
        String value() default "";
    }

    /**
     * 利用字段注解填充sql
     *
     * @param mClass
     * @param sql
     */
    private void fillSql(Class mClass, StringBuffer sql) {
        Field[] fields = mClass.getDeclaredFields();
        for (Field f : fields) {
            if (f.isAnnotationPresent(DBField.class)) {
                DBField bind = f.getAnnotation(DBField.class);
                String type = bind.value();
                sql.append(f.getName().toUpperCase());
                sql.append(" ");
                sql.append(type);
                sql.append(",");
            }
        }
    }

    /**
     * 示例
     */
    public static class Example {
        @DBField("VARCHAR(100)")
        public String name;
        @DBField("VARCHAR(100)")
        public String password;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }
    }

}