移动架构30_面向对象式手写数据库架构设计二（修改、删除、查询、拓展）

本文链接：https://blog.csdn.net/baopengjian/article/details/78397406

Android移动架构汇总
Demo地址：
https://gitee.com/YuBaoZi/codes/nujo5r0xsvdyte9pq7lha77
建议：代码很简单，建议敲两边就能理解了，很实用；
##一、说明
前一篇讲到了框架的搭建和增加数据(移动架构29_向对象式手写数据库架构设计一（基本框架与插入数据）)，本篇文章将说明如何修改、删除和查询数据；通过这些操作，充分表现架构的简单性、易拓展；相比常见的框架ormLitem，由于创建的对象更少，效率更高，与其他框架相比，灵活性很强。

##二、简要回顾：

注解：由于有些数据与表名不能保持一致，这时通过注解实现转义，提高了框架的兼容性；
业务Bean：具体业务逻辑与数据库表的内容的封装（属性对应表的列名）；
IBaseDao接口：对数据库抽象操作（增、删、改、查）的抽象封装；
BaseDao抽象类：提供创建表语句的抽象方法；表的增、删、改、查具体操作；业务Bean的解析与封装；Sql语句的组拼执行；
工厂类：创建数据库，由泛型限制，提供Dao类；
具体实现的Dao类，提供差异的表创建的sql语句；定制自己的特殊需求

可以看出，在具体的实现中，只需要定义一个Dao和一个Bean即可；非常方便和简单。
这里写图片描述
##三、代码
###1、注解
/**

表名的解释
*/

	@Target(ElementType.TYPE)
	@Retention(RetentionPolicy.RUNTIME)
		public @interface DbTable {
	    String value();
	}

/**

列名的解释
*/

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DbField {
    String value();
}

###2、业务Bean：注意前面定义的注解在这里的使用

@DbTable("tb_user")
public class User {
    public Integer user_Id;

    public User(){}

    @DbField("name")
    public String name;

    @DbField("password")
    public String password;

    public User(int user_Id, String name, String password) {
        this.user_Id = user_Id;
        this.name = name;
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "user_Id=" + user_Id +
                ", name='" + name + '\'' +
                ", password='" + 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;
    }

    public Integer getUser_Id(){
        return user_Id;
    }

    public void setUser_Id(int user_Id) {
        this.user_Id = user_Id;
    }
}

###3、IBaseDao接口

public interface IBaseDao<T> {
    /**
     * 插入数据
     * @param entity
     * @return
     */
    Long insert(T entity);

    int update(T entity,T where);

    /**
     * 删除数据
     * @param where
     * @return
     */
    int delete(T where);

    /**
     * 查询数据
     * @param where
     * @return
     */
    List<T> query(T where);

    List<T> query(T where,String orderBy,Integer startIndex,Integer limit);

    List<T> query(String sql);
}

###4、BaseDao

public abstract class BaseDao<T> implements IBaseDao<T> {
    /**
     * 持有数据库操作类的引用
     */
    private SQLiteDatabase database;

    /**
     * 保证实例化一次
     */
    private boolean isInit = false;

    /**
     * 持有操作数据库表对应的java类型
     */
    private Class<T> entityClass;
    /**
     * 维护这表名与成员变量名的映射关系
     * key -->Field
     * value --> Field
     * class methodField
     * {
     * Method setMethod
     * Filed field
     * }
     */
    private HashMap<String, Field> chacheMap;

    private String tableName;

    protected synchronized boolean init(Class<T> entity, SQLiteDatabase sqLiteDatabase) {
        if (!isInit) {
            entityClass = entity;
            database = sqLiteDatabase;
            if (entity.getAnnotation(DbTable.class) == null) {
                tableName = entity.getClass().getSimpleName();
            } else {
                tableName = entity.getAnnotation(DbTable.class).value();
            }
            if (!database.isOpen()) {
                return false;
            }
            if (!TextUtils.isEmpty(creatTable())) {
                database.execSQL(creatTable());
            }
            chacheMap = new HashMap<>();
            initCacheMap();
            isInit = true;
        }
        return isInit;
    }

    private void initCacheMap() {
        String sql = "select * from "+this.tableName+" limit 1 , 0";
        Cursor cursor = null;
        try {
            cursor = database.rawQuery(sql, null);
            /**
             * 表的列名数组
             */
            String[] columnNames = cursor.getColumnNames();
            /**
             * 拿到Field数组
             */
            Field[] columnFields = entityClass.getFields();
            for (Field field : columnFields) {
                field.setAccessible(true);
            }

            /**
             * 开始找到对应关系
             */
            for (String columnName : columnNames) {
                /**
                 * 如果找到对应的Field就赋值给他
                 */
                Field columnFiled = null;
                for (Field field : columnFields) {
                    String fieldName = null;
                    if (field.getAnnotation(DbField.class) != null) {
                        fieldName = field.getAnnotation(DbField.class).value();
                    } else {
                        fieldName = field.getName();
                    }
                    /**
                     * 如果表的列名 等于 成员变量的注解名字
                     */
                    if (columnName.equals(fieldName)) {
                        columnFiled = field;
                        break;
                    }
                }
                if (columnFiled != null) {
                    chacheMap.put(columnName, columnFiled);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            cursor.close();
        }
    }

    @Override
    public Long insert(T entity) {
        Map<String, String> map = getValues(entity);
        ContentValues values = getContentValues(map);
        Long result = database.insert(tableName, null, values);
        return result;
    }

    private Map<String, String> getValues(T entity) {
        HashMap<String, String> result = new HashMap<>();
        Iterator<Field> fieldIterator = chacheMap.values().iterator();
        while (fieldIterator.hasNext()) {
            Field columnToField = fieldIterator.next();
            String cacheKey = null;
            String cacheValue = null;
            if (columnToField.getAnnotation(DbField.class) != null) {
                cacheKey = columnToField.getAnnotation(DbField.class).value();
            } else {
                cacheKey = columnToField.getName();
            }

            try {
                if (null == columnToField.get(entity)) {
                    continue;
                }
                cacheValue = columnToField.get(entity).toString();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            result.put(cacheKey, cacheValue);
        }
        return result;
    }

    @Override
    public int delete(T where) {
        Map map = getValues(where);
        Condition condition = new Condition(map);
        int result = database.delete(tableName, condition.getWhereClause(), condition.getWhereArgs());
        return result;
    }

    @Override
    public int update(T entity, T where) {
        int result = -1;
        Map values = getValues(entity);
        /**
         * 将条件对象转换map
         */
        Map whereClause = getValues(where);

        Condition condition = new Condition(whereClause);
        ContentValues contentValues = getContentValues(values);
        result = database.update(tableName, contentValues, condition.getWhereClause(), condition.getWhereArgs());
        return result;
    }


    @Override
    public List<T> query(T where) {
        return query(where, null, null, null);
    }

    @Override
    public List<T> query(T where, String orderBy, Integer startIndex, Integer limit) {
        Map map = getValues(where);
        String limitString = null;
        if (startIndex != null && limit != null) {
            limitString = startIndex + " , " + limit;
        }

        Condition condition = new Condition(map);
        Cursor cursor = database.query(tableName, null, condition.getWhereClause()
                , condition.getWhereArgs(), null, null, orderBy, limitString);
        List<T> result = getResult(cursor, where);
        cursor.close();
        return result;
    }

    protected abstract String creatTable();

    private List<T> getResult(Cursor cursor, T where) {
        ArrayList list=new ArrayList();
        Object item;
        while (cursor.moveToNext())
        {
            try {
                item=where.getClass().newInstance();
                /**
                 * 列名  name
                 * 成员变量名  Filed;
                 */
                Iterator iterator= chacheMap.entrySet().iterator();
                while (iterator.hasNext())
                {
                    Map.Entry entry= (Map.Entry) iterator.next();
                    /**
                     * 得到列名
                     */
                    String colomunName= (String) entry.getKey();
                    /**
                     * 然后以列名拿到  列名在游标的位子
                     */
                    Integer colmunIndex=cursor.getColumnIndex(colomunName);

                    Field field= (Field) entry.getValue();

                    Class type=field.getType();
                    if(colmunIndex!=-1)
                    {
                        if(type==String.class)
                        {
                            //反射方式赋值
                            field.set(item,cursor.getString(colmunIndex));
                        }else if(type==Double.class)
                        {
                            field.set(item,cursor.getDouble(colmunIndex));
                        }else  if(type==Integer.class)
                        {
                            field.set(item,cursor.getInt(colmunIndex));
                        }else if(type==Long.class)
                        {
                            field.set(item,cursor.getLong(colmunIndex));
                        }else  if(type==byte[].class)
                        {
                            field.set(item,cursor.getBlob(colmunIndex));
                            /*
                            不支持的类型
                             */
                        }else {
                            continue;
                        }
                    }

                }
                list.add(item);
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        }
        return list;
    }

    private ContentValues getContentValues(Map<String, String> map) {
        ContentValues contentValues = new ContentValues();
        Set keys = map.keySet();
        Iterator<String> iterator = keys.iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            String value = map.get(key);
            if (value != null) {
                contentValues.put(key, value);
            }
        }
        return contentValues;
    }

    class Condition {
        private String whereClause;
        private String[] whereArgs;
        public Condition(Map<String ,String> whereClause) {
            ArrayList list=new ArrayList();
            StringBuilder stringBuilder=new StringBuilder();

            stringBuilder.append(" 1=1 ");
            Set keys=whereClause.keySet();
            Iterator iterator=keys.iterator();
            while (iterator.hasNext())
            {
                String key= (String) iterator.next();
                String value=whereClause.get(key);

                if (value!=null)
                {
                    /*
                    拼接条件查询语句
                    1=1 and name =? and password=?
                     */
                    stringBuilder.append(" and "+key+" =?");
                    /**
                     * ？----》value
                     */
                    list.add(value);
                }
            }
            this.whereClause=stringBuilder.toString();
            this.whereArgs= (String[]) list.toArray(new String[list.size()]);

        }

        public String[] getWhereArgs() {
            return whereArgs;
        }

        public String getWhereClause() {
            return whereClause;
        }
    }
}

###5、BaseDaoFactory

public class BaseDaoFactory {

    private String sqliteDatabasePath;

    private SQLiteDatabase sqLiteDatabase;

    private static BaseDaoFactory instance = new BaseDaoFactory();

    public BaseDaoFactory(){
        sqliteDatabasePath = Environment.getExternalStorageDirectory().getAbsolutePath()+"/teacher.db";
        openDatabase();
    }

    public synchronized <T extends BaseDao<M>,M> T getDataHelper(Class<T> clazz,Class<M> entityClass){
        T baseDao = null;
        try{
            baseDao = clazz.newInstance();
            baseDao.init(entityClass,sqLiteDatabase);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

        return baseDao;
    }

    private void openDatabase(){
        this.sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(sqliteDatabasePath,null);
    }

    public static BaseDaoFactory getInstance(){
        return instance;
    }
}

###6、具体实现Dao类：

public class UserDao extends BaseDao {
    @Override
    protected String creatTable() {
        return "create table if not exists tb_user(user_Id int,name varchar(20),password varchar(10))";
    }

    @Override
    public List query(String sql) {
        return null;
    }
}

###7、使用：

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "dongnao";

    IBaseDao<User> baseDao;
    IBaseDao<DownFile> fileDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        baseDao = BaseDaoFactory.getInstance().getDataHelper(UserDao.class, User.class);
        fileDao = BaseDaoFactory.getInstance().getDataHelper(DownDao.class, DownFile.class);
    }

    public void insert(View v) {
        for (int i = 0; i < 20; i++) {
            User user = new User(i, "teacher", "123456");
            baseDao.insert(user);
        }
    }

    public void queryList(View v) {
        User where = new User();
        where.setName("teacher");
        where.setUser_Id(5);
        List<User> list = baseDao.query(where);
        Log.i(TAG, "查询到 " + list.size() + " 条数据");
    }

    public void update(View v) {
        User where = new User();
        where.setName("teacher");

        User user = new User(1, "David", "123456789");
        baseDao.update(user, where);
    }

    public void delete(View v) {
        User user = new User();
        user.setName("David");
        baseDao.delete(user);
    }
}

###8、拓展：增加新的需要，添加一个文件下载操作的表tb_down：
####1）新建bean:

@DbTable("tb_down")
public class DownFile {

    public DownFile(){}

    public DownFile(String time, String path) {
        this.time = time;
        this.path = path;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    @DbField("tb_time")
    public String time;

    @DbField("tb_path")
    public String path;
}

####2)添加Dao类：

public class DownDao extends BaseDao {
    @Override
    protected String creatTable() {
        return "create table if not exists tb_down(tb_time varchar(20),tb_path varchar(20))";
    }

    @Override
    public List query(String sql) {
        return null;
    }
}

####3）调用：

IBaseDao<DownFile> fileDao = BaseDaoFactory.getInstance().getDataHelper(DownDao.class, DownFile.class);

…增、删、改、查（和User相同）

##四、补充说明

tb_down的逻辑增加很简单，类似的业务逻辑都是这样实现的，调用方便；Dao中有一个空实现的方法：query(String sql)是为了兼容不同业务的具体实现（本质上就是sql语句的不同）
2.核心类在于BaseDao；
1)数据的操作集中抽取到了这个类；
2)简单的讲就是将业务的List、Bean翻译成sql语句执行，再将执行的cursor结构翻译成Bean、List、Obeject返回；
3)Condition 内部类的作用是将map翻译成sql语句要执行的条件：
private String whereClause;
private String[] whereArgs;

##五、注意
由于反射调用方法，默认使用的是空参的构造，所以Bean要给出空参的构造；为了调用方便，Bean的属性也声明为public的。