自定义ORM框架

Tags
Star
#Android

一、ORM简单科普

所谓ORM,即对象-关系映射(Object/Relation Mapping),方便我们以操作对象的方式去操作关系型数据库。
在平时的开发过程中,大家一定会或多或少地接触到SQLite。然而在使用它时,我们往往需要做许多额外的工作,像编写 SQL 语句与解析查询结果等。
假如我们有这样一个对象需要存在数据库:
@Table
public class Person {
@Column
private int id;

@Check("name!='Fucker'")
@Column
private String name;

@Default
@Column
private double height = 180;

@Column
private int age;

@Default
@NotNull
@Column
private String job = "IT";
}
那么我们在建表时,需要写这样的sql语句:
create table if not exists Person(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT CHECK(name!='Fucker'),
height REAL DEFAULT 180.0,
age INTEGER,
job TEXT DEFAULT IT NOT NULL);
然后在查询后,我们又需要对Curcor进行遍历取值,然后set到对象中去,很麻烦有木有?
while (cursor.moveToNext()) {
int nameColumnIndex = cursor.getColumnIndex("filedName");
String value = cursor.getString(nameColumnIndex);
}
一不小心sql拼错了,或者cursor取字段时字段名写错了,就GG了啊!
于是,各种ORM框架就出来了,通过注解和反射将生成建表sql、解析cursor成对象,都自动化了,这大大方便了我们这些懒人了。
但是,现在的ORM框架大多在写查询语句时,感觉有点过度封装了,有时候,使用ORM框架去做条件查询,甚至还不如自己去写查询的sql!
为了解决这个问题呢,本人封装了一套自己的ORM框架,借鉴了Guava的字符串操作库的Fluent链式接口的思想,将写查询语句方便了一点点,既尽量减少我们写原生sql语句容易拼错的问题,也不像有的ORM框架不方便做复杂的条件查询。
当然,框架还在不断的完善中(索引和多表关联暂时都还没加),如果你觉得我下面的封装有哪里不合理,欢迎和我讨论!

二、框架的测试类:

测试场景:
  1. 执行自定义的Sql
  1. 表操作:建表、删表、备份、存在判断
  1. 插入
  1. 删除
  1. 查询
  1. 更新
  1. 事务
package com.che.baseutil.sqlite;

import android.app.Application;

import com.che.base_util.LogUtil;
import com.che.baseutil.BuildConfig;
import com.che.baseutil.table.Person;
import com.che.baseutil.table.Teacher;
import com.che.fast_orm.DBHelper;
import com.che.fast_orm.helper.DBException;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.robolectric.RobolectricTestRunner;
import org.robolectric.RuntimeEnvironment;
import org.robolectric.annotation.Config;
import org.robolectric.shadows.ShadowLog;

import java.util.ArrayList;
import java.util.List;

import static com.google.common.truth.Truth.assertThat;

/**
 * 作者:余天然 on 16/9/16 下午10:17
 */
@RunWith(RobolectricTestRunner.class)
@Config(constants = BuildConfig.class,
  sdk = 21,
  manifest = "src/main/AndroidManifest.xml",
  packageName = "com.che.baseutil",
  resourceDir = "res")
public class DBTestClient {

private DBHelper dbHelper;//数据库辅助类

@Before
public void setUp() throws DBException {
  ShadowLog.stream = System.out;
  Application application = RuntimeEnvironment.application;
  dbHelper = new DBHelper(application, "mydb", 1);
  //删除表
  dbHelper.drop(Person.class);
  //创建表
  dbHelper.create(Person.class);
  //初始化数据,方便之后操作
  initData();
}

/**
 * 插入
 */
public void initData() {
  try {
      //插入多条数据
      List<Person> persons = new ArrayList<>();
      persons.add(new Person("Fishyer", 23));
      persons.add(new Person("Stay", 23));
      persons.add(new Person("Ricky"));
      persons.add(new Person("Stay", 23));
      persons.add(new Person("Fuck", 24));
      persons.add(new Person("Albert"));
      dbHelper.insertAll(persons);

      //插入单条数据
      Person untitled = new Person();
      untitled.setAge(21);
      untitled.setHeight(200);
      dbHelper.insert(untitled);
  } catch (DBException e) {
      LogUtil.print("数据库异常:" + e.getMessage());
  }
}

/**
 * 自定义Sql
 */
@Test
public void testSql() throws DBException {
  dbHelper.execSQL("drop table if exists Per

三、ORM框架的封装之路:

这个框架,与其说是设计出来的,倒不如说是不断重构出来的。一开始,我是想写一个工具类,后来不断的拓展和优化,结果,就变成框架了。
fast-orm库的项目结构.png

1.ORM工具类

public class DBHelper extends SQLiteOpenHelper {

/**
 * 构造函数,必须实现
 *
 * @param context 上下文
 * @param name    数据库名称
 * @param version 当前数据库版本号
 */
public DBHelper(Context context, String name, int version) {
  super(context, name, null, version);
}

//数据库第一次创建时会调用,一般在其中创建数据库表
@Override
public void onCreate(SQLiteDatabase db) {
}

//当数据库需要修改的时候,Android系统会主动的调用这个方法。
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

//基本修改命令
public void execSQL(String sql) throws DBException {
  try {
      sql += ";";
      LogUtil.print(sql);
      getWritableDatabase().execSQL(sql);
  } catch (Exception e) {
      e.printStackTrace();
      throw new DBException(e.getMessage());
  }
}

//基本查询命令
public Cursor rawQuery(String sql) throws DBException {
  Cursor cursor = null;
  try {
      sql += ";";
      LogUtil.print(sql);
      cursor = getReadableDatabase().rawQuery(sql, null);
  } catch (Exception e) {
      e.printStackTrace();
      throw new DBException(e.getMessage());
  }
  return cursor;
}

/**
 * 表操作命令
 */
public void create(Class<?> clazz) throws DBException {
  String createSql = SqlGenerater.create(clazz);
  execSQL(createSql);
}

public void drop(Class<?> clazz) throws DBException {
  String dropSql = SqlGenerater.drop(clazz);
  execSQL(dropSql);
}

public <T> void bak(Class<T> clazz) throws DBException {
  String bakSql = SqlGenerater.bak(clazz);
  execSQL(bakSql);
}

public <T> boolean isExist(Class<T> clazz) throws DBException {
  return isExist(ReflectHelper.getTableName(clazz));
}

public boolean isExist(String tableName) throws DBException {
  Cursor cursor = rawQuery("select count(*) from sqlite_master where type='table' and name='" + tableName + "'");
  if (cursor.moveToNext()) {
      int count = cursor.getInt(0);
      if (count > 0) {
          return true;
      }
  }
  return false;
}

/**
 * 新增
 */
public <T> void insert(T t) throws DBException {
  String insertSql = SqlGenerater.insert(t);
  execSQL(insertSql);
}

2.SQL语句封装

在上面的工具类中,大家可以看到我的封装,主要就是将创建sql语句的过程进行了封装,主要从2个方面入手:

这个类主要就是根据类信息、对象信息生成一个sql语句,交给DBHelper处理,适合一些模式化的sql,例如:create、insert等

这个类主要就是为了方便写查询sql,将where、and、set等,通过链式调用拼接起来,合成一条sql,和写原生的sql差不多,不过又尽可能避免了写原生sql时一不小心哪里少打了空格等问题
Sql语句生成器:
public class SqlGenerater {

public final static String BAK_SUFFIX = "_bak";//备份的后缀

/**
 * 生成create语句
 * <p>
 * 格式:create table Student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age TEXT)
 */
public static String create(Class<?> clazz) {
  TableWrapper wrapper = ReflectHelper.parseClass(clazz);
  //拼接:create table Student(id INTEGER PRIMARY KEY AUTOINCREMENT,
  StringBuilder sb = new StringBuilder("create table if not exists " + wrapper.name);
  //拼接:(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age TEXT)
  sb.append(TypeConverter.zipNameType(wrapper));
  return sb.toString();
}

/**
 * 生成drop语句
 * <p>
 * 格式:drop table if exists Student;
 */
public static String drop(Class<?> clazz) {
  StringBuilder sb = new StringBuilder("drop table if exists " + ReflectHelper.getTableName(clazz));
  return sb.toString();
}

/**
 * 生成insert语句
 * <p>
 * 格式:insert or replace into Student (name,age) values ('Fishyer',23)
 */
public static <T> String insert(T t) {
  TableWrapper wrapper = ReflectHelper.parseObject(t);
  //拼接:insert into Student
  StringBuilder sb = new StringBuilder("insert or replace into " + wrapper.name + " ");
  //拼接:(name,age)
  sb.append(TypeConverter.zipName(wrapper));
  //拼接: values
  sb.append(" values ");
  //拼接:('Fishyer',23)
  sb.append(TypeConverter.zipValue(wrapper));
  return sb.toString();
}

/**
 * 生成queryAll语句
 * <p>
 * 格式:select * from Student
 */
public static String queryAll(Class<?> clazz) {
  StringBuilder sb = new StringBuilder("select * from " + ReflectHelper.getTableName(clazz));
  return sb.toString();
}

/**
 * 生成deleteAll语句
 * <p>
 * 格式:delete from Student
 */
public static String deleteAll(Class<?> clazz) {
  StringBuilder sb = new StringBuilder("delete from " + ReflectHelper.getTableName(clazz));
  return sb.toString();
}


/**
 * 生成queryObj语句
 * <p>
 * 格式:select * from Student where name='Fishyer' and age=23
 */
public static <T> String queryObj(T t) {
  TableWrapper wrapper = ReflectHelper.parseObject(t);
连接符编辑器:
public class ConnectBuilder<T> {
public DBHelper dbHelper;//用于调用终止连接符:query和execute
public Class<T> clazz;//用于解析Cursor
public String sql;

public ConnectBuilder(DBHelper dbHelper, Class<T> clazz, String sql) {
  this.dbHelper = dbHelper;
  this.clazz = clazz;
  this.sql = sql;
}

/**
 * where 连接符
 * <p>
 * 1、where的默认比较符是=,如果是其它符号,需在第二个参数说明
 * 2、where与whereInt的区别在于:是否给后面的值加单引号
 */
public ConnectBuilder<T> where(String s) {
  return where(s, "=");
}

public ConnectBuilder<T> where(String s, String operation) {
  this.sql = sql + (" where " + TypeConverter.addQuote(s, operation));
  return this;
}

public ConnectBuilder<T> whereInt(String s) {
  this.sql = sql + (" where " + s);
  return this;
}


/**
 * and 连接符
 */
public ConnectBuilder<T> and(String s) {
  return and(s, "=");
}

public ConnectBuilder<T> and(String s, String operation) {
  this.sql = sql + (" and " + TypeConverter.addQuote(s, operation));
  return this;
}

public ConnectBuilder<T> andInt(String s) {
  this.sql = sql + (" and " + s);
  return this;
}

/**
 * set 连接符
 */
public ConnectBuilder<T> set(String s) {
  return where(s, "=");
}

public ConnectBuilder<T> set(String s, String operation) {
  this.sql = sql + (" set " + TypeConverter.addQuote(s, operation));
  return this;
}

public ConnectBuilder<T> setInt(String s) {
  this.sql = sql + (" set " + s);
  return this;
}

/**
 * order by 连接符
 */
public ConnectBuilder<T> orderBy(String field) {
  this.sql = sql + (" order by " + field);
  return this;
}

/**
 * desc 连接符
 */
public ConnectBuilder<T> desc() {
  this.sql = sql + (" desc");
  return this;
}

/**
 * append 连接符
 * <p>
 * 代表一个空格
 */
public ConnectBuilder<T> append(String s) {
  this.sql = sql + (" " + s);
  return this;
}

/**
 * 执行Sql语句,查询,有返回值
 *
 * @return
 */
public List<T> query() throws DBException {
  return dbHelper.query(this);
}

/**
 * 执行Sql语句,非查询,无返回值
 *
 * @return
 */
public void execute() throws DBException {
  dbHelper.execute(this);
}

}

3.反射辅助类

为了上面的SqlGenerater能生成正确的sql,我们需要用到注解和反射。
通过注解,我们在一个类中(例如上面的Person),标明了我们根据这个类去创建表时所需要的参数。
通过反射,我们可以在运行时获取到这些参数,交给SqlGenerater。
public class ReflectHelper {


/**
 * 直接反射,获取字段值
 */
private static <T> Object getFieldValue(T t, Field field) {
  // TODO: 16/9/15 这里怎么将返回值自动强转成fieldType呢?求解!!!
  Object value = null;
  try {
      field.setAccessible(true);
      value = field.get(t);
      field.setAccessible(false);
  } catch (IllegalAccessException e) {
      e.printStackTrace();
  }
  return value;
}

/**
 * 解析数据库游标
 *
 * @param cursor
 * @param clazz
 * @return
 */
public static <T> List<T> parseCursor(Cursor cursor, Class<T> clazz) {
  List<T> list = new ArrayList<>();
  try {
      TableWrapper wrapper = ReflectHelper.parseClass(clazz);
      while (cursor.moveToNext()) {
          T t = clazz.newInstance();
          int pos = 0;
          for (String filedName : wrapper.filedList) {
              Class<?> type = wrapper.typeList.get(pos);
              Object value = getCursorValue(cursor, filedName, type);
              Field field = clazz.getDeclaredField(filedName);
              field.setAccessible(true);
              field.set(t, value);
              field.setAccessible(false);
              pos++;
          }
          LogUtil.print("-->:" + t.toString());
          list.add(t);
      }
      cursor.close();
  } catch (InstantiationException e) {
      e.printStackTrace();
  } catch (IllegalAccessException e) {
      e.printStackTrace();
  } catch (NoSuchFieldException e) {
      e.printStackTrace();
  }
  return list;
}

/**
 * 解析类或对象的信息
 */
private static <T> TableWrapper parse(Class<?> clazz, T t) {
  List<String> filedList = new ArrayList<>();//字段名
  List<Class<?>> typeList = new ArrayList<>();//字段类型
  List<String> constraintList = new ArrayList<>();//字段约束(一个列的约束可能不止一个)
  List<Object> valueList = new ArrayList<>();//字段值
  //判断是否存在表注解
  if (!clazz.isAnnotationPresent(Table.class)) {
      throw new RuntimeException(clazz.getName() + "没有添加表注解");
  }

  int column = 0;
  //遍历所有的字段
  for (Field field : clazz.getDeclaredFields()) {
      //判断是否存在列注解
      if (field.isAnnotationPres

4.其余辅助类

表信息包装类: 这个其实就是通过ReflectHelper将一个Class 解析成这个TableWrapper,它是那些写SQL的参数的载体
public class TableWrapper {
public String name;//类名
public List<String> filedList;//字段名
public List<Class<?>> typeList;//字段类型
public List<String> constraintList;//字段约束
public List<Object> valueList;//字段值

public TableWrapper(String name, List<String> filedList, List<Class<?>> typeList, List<String> constraintList, List<Object> valueList) {
  this.name = name;
  this.filedList = filedList;
  this.typeList = typeList;
  this.constraintList = constraintList;
  this.valueList = valueList;
}
}
类型转换器: 因为不同sql命令的参数的格式不一样,这里就是为了方便处理从class的field到table的column之间的转换
public class TypeConverter {

//wrapper --> (name,age)
public static String zipName(TableWrapper wrapper) {
  StringBuilder sb = new StringBuilder();
  sb.append("(");
  for (int i = 0; i < wrapper.filedList.size(); i++) {
      String filed = wrapper.filedList.get(i);
      sb.append(filed);
      if (i != wrapper.filedList.size() - 1) {
          sb.append(",");
      }
  }
  sb.append(")");
  return sb.toString();
}

//wrapper --> ('Fishyer',23)
public static String zipValue(TableWrapper wrapper) {
  StringBuilder sb = new StringBuilder();
  sb.append("(");
  for (int j = 0; j < wrapper.filedList.size(); j++) {
      Class<?> type = wrapper.typeList.get(j);
      Object value = wrapper.valueList.get(j);
      sb.append(TypeConverter.getInsertValue(type, value));
      if (j != wrapper.typeList.size() - 1) {
          sb.append(",");
      }
  }
  sb.append(")");
  return sb.toString();
}

//wrapper --> (name TEXT NOT NULL, age TEXT)
public static String zipNameType(TableWrapper wrapper) {
  StringBuilder sb = new StringBuilder();
  sb.append("(");
  for (int i = 0; i < wrapper.filedList.size(); i++) {
      String filed = wrapper.filedList.get(i);
      String type = TypeConverter.getCreateType(filed, wrapper.typeList.get(i));
      String constraint = wrapper.constraintList.get(i);
      sb.append(filed + type + constraint);
      if (i != wrapper.filedList.size() - 1) {
          sb.append(",");
      }
  }
  sb.append(")");
  return sb.toString();
}

//wrapper --> where name='Fishyer' and age=23
public static String zipConnNameValue(TableWrapper wrapper) {
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < wrapper.filedList.size(); i++) {
      if (i == 0) {
          sb.append(" where ");
      } else {
          sb.append(" and ");
      }
      String filed = wrapper.filedList.get(i);
      Class<?> type = wrapper.typeList.get(i);
      Object value = wrapper.valueList.get(i);
      sb.append(filed + "=" + TypeConverter.getInsertValue(type, val
各种注解标识:
@Column:列注解,默认是字段名,也可自定义列名 @Unique:唯一性约束 @NotNull:非空约束 @Default:默认值约束 @Check:条件约束
约束符号常量
public class Constraint {
public static final String NOT_NULL = " NOT NULL";
public static final String DEFAULT = " DEFAULT";
public static final String UNIQUE = " UNIQUE";
public static final String CHECK = " CHECK";
}

© fishyer 2022