自定义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框架不方便做复杂的条件查询。
当然,框架还在不断的完善中(索引和多表关联暂时都还没加),如果你觉得我下面的封装有哪里不合理,欢迎和我讨论!
二、框架的测试类:
测试场景:
- 执行自定义的Sql
- 表操作:建表、删表、备份、存在判断
- 插入
- 删除
- 查询
- 更新
- 事务
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";
}