一、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框架不方便做複雜的條件查詢。

當然，框架還在不斷的完善中（索引和多表關聯暫時都還沒加），如果你覺得我下面的封裝有哪裡不合理，歡迎和我討論！@QQ:630709658

二、框架的測試類：

---

測試場景：

1. 執行自定義的Sql
2. 表操作：建表、刪表、備份、存在判斷
3. 插入
4. 刪除
5. 查詢
6. 更新
7. 事務

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 Person");
        dbHelper.execSQL("create table if not exists Person(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT,age INTEGER DEFAULT 100)");
        dbHelper.execSQL("insert into Person (age) values (21)");
        dbHelper.execSQL("insert into Person (name,age) values ('Fishyer',23)");
    }

    /**
     * 表操作
     */
    @Test
    public void testTable() throws DBException {
        //刪除表: drop table if exists Teacher
        dbHelper.drop(Teacher.class);
        //斷言表不存在:select count(*) from sqlite_master where type='table' and name='Teacher'
        assertThat(dbHelper.isExist(Teacher.class)).isEqualTo(false);
        //建立表:create table if not exists Teacher(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER,course TEXT)
        dbHelper.create(Teacher.class);
        //斷言表存在:
        assertThat(dbHelper.isExist("Teacher")).isEqualTo(true);
    }

    /**
     * 事務
     */
    @Test
    public void testTransaction() throws DBException {
        Person person = new Person("Fishyer", 23);
        dbHelper.beginTransaction();
        for (int i = 0; i < 100; i++) {
            //insert or replace into Person (name,height,age) values ('Fishyer',180.0,23)
            dbHelper.insert(person);
        }
        dbHelper.endTransaction();
    }

    /**
     * 刪除
     */
    @Test
    public void testDelete() throws DBException {
        //刪除指定資料(不推薦，建議使用條件刪除):delete from Person where name='Stay' and height=180.0 and age=-1;
        dbHelper.deleteObj(new Person("Stay"));

        //刪除所有資料:delete from Person
        dbHelper.deleteAll(Person.class);

        //條件刪除：delete from Person where name='Stay'
        dbHelper.delete(Person.class).where("name=Stay").execute();
    }

    @Test
    public void testQuery() throws DBException {
        //查詢所有資料: select * from Person
        dbHelper.queryAll(Person.class);

        //查詢指定資料: select * from Person where name='Stay'
        dbHelper.queryObj(new Person("Stay"));
    }

    @Test
    public void testSelect() throws DBException {
        //條件查詢1：select * from Person where age>='21' order by name
        dbHelper.select(Person.class).whereInt("age>=21").orderBy("name").query();

        //條件查詢2：select * from Person order by age desc
        dbHelper.select(Person.class).where("name=Stay").append("order by id").desc().query();

        //條件查詢3：select * from Person where age='23' order by name
        dbHelper.select(Person.class).whereInt("age=23").orderBy("id").query();

        //去重查詢：select distinct * from Person order by age desc
        dbHelper.distinct(Person.class).whereInt("age=23").orderBy("id").query();
    }

    @Test
    public void testBackup() throws DBException {
        //建立備份：create table Student_bak as select *from Person
        dbHelper.bak(Person.class);

        //查詢備份：select * from Student_bak
        dbHelper.queryBak(Person.class);
    }

    @Test
    public void testUpdate() throws DBException {
        //更新資料：update Person set age=99 where name='Fishyer'
        dbHelper.update(Person.class).setInt("age=99").where("name=Fishyer").execute();

        dbHelper.queryAll(Person.class);
    }

    @Test
    public void testIndex() throws DBException {
        // TODO: 16/9/17 新增索引
    }

    @Test
    public void testMap() throws DBException {
        // TODO: 16/9/17 新增多表關聯
    }

}

三、ORM框架的封裝之路：

---

這個框架，與其說是設計出來的，倒不如說是不斷重構出來的。一開始，我是想寫一個工具類，後來不斷的擴充和優化，結果，就變成框架了。

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);
    }

    public <T> void insertAll(List<T> list) throws DBException {
        getWritableDatabase().beginTransaction();
        for (T t : list) {
            insert(t);
        }
        getWritableDatabase().setTransactionSuccessful();
        getWritableDatabase().endTransaction();
    }

    /**
     * 刪除
     */
    public <T> void deleteObj(T t) throws DBException {
        String whereSql = SqlGenerater.deleteObj(t);
        execSQL(whereSql);
    }

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

    /**
     * 查詢
     */
    public <T> List<T> queryObj(T t) throws DBException {
        String whereSql = SqlGenerater.queryObj(t);
        Cursor cursor = rawQuery(whereSql);
        return (List<T>) ReflectHelper.parseCursor(cursor, t.getClass());
    }

    public <T> List<T> queryAll(Class<T> clazz) throws DBException {
        String queryAllSql = SqlGenerater.queryAll(clazz);
        Cursor cursor = rawQuery(queryAllSql);
        return ReflectHelper.parseCursor(cursor, clazz);
    }

    public <T> List<T> queryBak(Class<T> clazz) throws DBException {
        String selectAllSql = SqlGenerater.queryBak(clazz);
        Cursor cursor = rawQuery(selectAllSql);
        return ReflectHelper.parseCursor(cursor, clazz);
    }

    /**
     * 建立連線符編輯器
     */
    //查詢
    public <T> ConnectBuilder<T> select(Class<T> clazz) throws DBException {
        return new ConnectBuilder(this, clazz, "select * from " + ReflectHelper.getTableName(clazz));
    }

    //去重查詢
    public <T> ConnectBuilder<T> distinct(Class<T> clazz) throws DBException {
        return new ConnectBuilder(this, clazz, "select distinct * from " + ReflectHelper.getTableName(clazz));
    }

    //刪除
    public <T> ConnectBuilder<T> delete(Class<T> clazz) throws DBException {
        return new ConnectBuilder(this, clazz, "delete from " + ReflectHelper.getTableName(clazz));
    }

    //修改
    public <T> ConnectBuilder<T> update(Class<T> clazz) throws DBException {
        return new ConnectBuilder(this, clazz, "update " + ReflectHelper.getTableName(clazz));
    }

    /**
     * 連線符編輯器-執行，無返回值
     */
    public <T> void execute(ConnectBuilder<T> builder) throws DBException {
        execSQL(builder.sql);
    }

    /**
     * 編輯器-查詢，有返回值
     */
    public <T> List<T> query(ConnectBuilder<T> builder) throws DBException {
        Cursor cursor = rawQuery(builder.sql);
        return ReflectHelper.parseCursor(cursor, builder.clazz);
    }

    /**
     * 開啟事務
     */
    public void beginTransaction() {
        getReadableDatabase().beginTransaction();
    }

    /**
     * 關閉事務
     */
    public void endTransaction() {
        getReadableDatabase().setTransactionSuccessful();
        getReadableDatabase().endTransaction();
    }

}

2.SQL語句封裝

在上面的工具類中，大家可以看到我的封裝，主要就是將建立sql語句的過程進行了封裝，主要從2個方面入手：

1. SqlGenerater：Sql語句生成器
   這個類主要就是根據類資訊、物件資訊生成一個sql語句，交給DBHelper處理，適合一些模式化的sql,例如：create、insert等

---

2.ConnectBuilder：連線符編輯器
這個類主要就是為了方便寫查詢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);
        //拼接：select * from Student
        StringBuilder sb = new StringBuilder("select * from " + wrapper.name);
        //拼接： where name='Fishyer' and age=23
        sb.append(TypeConverter.zipConnNameValue(wrapper));
        return sb.toString();
    }

    /**
     * 生成deleteObj語句
     * <p>
     * 格式：delete from Student where name='Fishyer' and age=23
     */
    public static <T> String deleteObj(T t) {
        TableWrapper wrapper = ReflectHelper.parseObject(t);
        //拼接：select * from Student
        StringBuilder sb = new StringBuilder("delete from " + wrapper.name);
        //拼接： where name='Fishyer' and age=23
        sb.append(TypeConverter.zipConnNameValue(wrapper));
        return sb.toString();
    }

    /**
     * 生成bak語句
     * <p>
     * 格式：create table Student2 as select *from Student
     */
    public static <T> String bak(Class<T> clazz) {
        String table = ReflectHelper.getTableName(clazz);
        String tableBak = table + BAK_SUFFIX;
        StringBuilder sb = new StringBuilder("create table " + tableBak + " as select *from " + table);
        return sb.toString();
    }

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

}

連線符編輯器:

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.isAnnotationPresent(Column.class)) {
                column++;
                String fieldName = ReflectHelper.getColumnName(field); //獲取欄位名
                Class<?> fieldType = field.getType();//獲取欄位型別
                Object fieldValue = t == null ? null : getFieldValue(t, field);//獲取欄位值

                //非建立時，忽略id欄位
                if (t != null && fieldName.toLowerCase().equals("id".toLowerCase())) {
                    continue;
                }

                //建立表時，新增欄位約束
                if (t == null) {
                    addConstraint(clazz, field, constraintList);
                }

                //插入資料時，忽略空欄位
                if (t != null && fieldValue == null) {
                    continue;
                }

                //新增欄位名、欄位型別、欄位值到列表中
                filedList.add(fieldName);
                typeList.add(fieldType);
                valueList.add(fieldValue);
            }
        }
        if (column == 0) {
            throw new RuntimeException(clazz.getName() + "表中沒有新增任何列註解");
        }
        if (t != null && filedList.isEmpty()) {
            throw new RuntimeException(clazz.getName() + "表中物件所有列均為空");
        }
        return new TableWrapper(getTableName(clazz), filedList, typeList, constraintList, valueList);
    }

    /**
     * 獲取表名
     *
     * @param clazz
     * @return
     */
    public static String getTableName(Class<?> clazz) {
        Table annotation = clazz.getAnnotation(Table.class);
        String value = annotation.value();
        return TextUtils.isEmpty(value) ? clazz.getSimpleName() : value;

    }

    /**
     * 獲取列名
     *
     * @param field
     * @return
     */
    private static String getColumnName(Field field) {
        Column annotation = field.getAnnotation(Column.class);
        String value = annotation.value();
        return TextUtils.isEmpty(value) ? field.getName() : value;
    }


    /**
     * 新增欄位約束
     *
     * @param clazz
     * @param field
     * @param list
     */
    private static <T> void addConstraint(Class<T> clazz, Field field, List<String> list) {
        StringBuffer sb = new StringBuffer();
        //遍歷該欄位的所有註解
        for (Annotation item : field.getDeclaredAnnotations()) {
            if (item instanceof NotNull) {
                sb.append(Constraint.NOT_NULL);
            } else if (item instanceof Default) {
                String value = getDefaultValue(clazz, field);
                sb.append(Constraint.DEFAULT + " " + value);
            } else if (item instanceof Unique) {
                sb.append(Constraint.UNIQUE);
            } else if (item instanceof Check) {
                Check annotation = field.getAnnotation(Check.class);
                String value = annotation.value();
                sb.append(Constraint.CHECK + "(" + value + ")");
            } else {
                sb.append("");
            }
        }
        list.add(sb.toString());
    }

    /**
     * 獲取列的預設值
     *
     * @param clazz
     * @param field
     * @return
     */
    private static <T> String getDefaultValue(Class<T> clazz, Field field) {
        try {
            T t = clazz.newInstance();
            return getFieldValue(t, field).toString();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        throw new RuntimeException("獲取列的預設值異常");
    }

    /**
     * 解析物件的資訊
     */
    public static <T> TableWrapper parseObject(T t) {
        return parse(t.getClass(), t);
    }

    /**
     * 解析類的資訊
     */
    public static TableWrapper parseClass(Class<?> clazz) {
        return parse(clazz, null);
    }

    /**
     * 解析列表的資訊
     */
    public static <T> List<TableWrapper> parseList(List<T> list) {
        List<TableWrapper> wrappers = new ArrayList<>();
        for (T t : list) {
            wrappers.add(parse(t.getClass(), t));
        }
        return wrappers;
    }

    /**
     * 獲取資料庫Cursor的值
     * <p>
     * 例如:'Stay',23
     */
    private static Object getCursorValue(Cursor cursor, String filedName, Class<?> type) {
        while (cursor.moveToNext()) {
            int nameColumnIndex = cursor.getColumnIndex("filedName");
            String value = cursor.getString(nameColumnIndex);
        }

        //文字
        if (type == String.class) {
            return cursor.getString(cursor.getColumnIndex(filedName));
        }
        // TODO: 16/9/15 獲取整數時，如果資料庫存的是null，這裡會自動變成0，是個問題！
        //整數
        else if (type == int.class) {
            return cursor.getInt(cursor.getColumnIndex(filedName));
        } else if (type == Integer.class) {
            return cursor.getInt(cursor.getColumnIndex(filedName));
        } else if (type == long.class) {
            return cursor.getLong(cursor.getColumnIndex(filedName));
        } else if (type == Long.class) {
            return cursor.getLong(cursor.getColumnIndex(filedName));
        } else if (type == boolean.class) {
            int anInt = cursor.getInt(cursor.getColumnIndex(filedName));
            return anInt == 0 ? false : true;
        } else if (type == Boolean.class) {
            int anInt = cursor.getInt(cursor.getColumnIndex(filedName));
            return anInt == 0 ? false : true;
        }
        //實數
        else if (type == float.class) {
            return cursor.getFloat(cursor.getColumnIndex(filedName));
        } else if (type == Float.class) {
            return cursor.getFloat(cursor.getColumnIndex(filedName));
        } else if (type == double.class) {
            return cursor.getDouble(cursor.getColumnIndex(filedName));
        } else if (type == Double.class) {
            return cursor.getDouble(cursor.getColumnIndex(filedName));
        }
        //輸入形式
        else {
            return " BLOB";
        }
    }

}

**4.其餘輔助類： **

表資訊包裝類：
這個其實就是通過ReflectHelper將一個Class<T> 解析成這個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, value));
        }
        return sb.toString();
    }

    /**
     * 獲取create時的儲存型別
     * <p>
     * 例如:TEXT、INTEGER
     */
    private static String getCreateType(String field, Class<?> type) {
        //主鍵
        if (field.toLowerCase().equals("id".toLowerCase())) {
            return " INTEGER PRIMARY KEY AUTOINCREMENT";
        }
        //文字
        if (type == String.class) {
            return " TEXT";
        }
        //整數
        else if (type == int.class) {
            return " INTEGER";
        } else if (type == Integer.class) {
            return " INTEGER";
        } else if (type == long.class) {
            return " INTEGER";
        } else if (type == Long.class) {
            return " INTEGER";
        } else if (type == boolean.class) {
            return " INTEGER";
        } else if (type == Boolean.class) {
            return " INTEGER";
        }
        //實數
        else if (type == float.class) {
            return " REAL";
        } else if (type == Float.class) {
            return " REAL";
        } else if (type == double.class) {
            return " REAL";
        } else if (type == Double.class) {
            return " REAL";
        }
        //輸入形式
        else {
            return " BLOB";
        }
    }

    /**
     * 獲取Insert時的儲存值
     * <p>
     * 例如:'Stay',23 (主要就是為了給String加單引號)
     */
    private static String getInsertValue(Class<?> type, Object value) {
        if (type == String.class) {
            return "'" + value + "'";
        }
        else if (type == int.class) {
            return value.toString();
        }else {
            return value.toString();
        }
    }

    /**
     * 給欄位加單引號
     * <p>
     * 例：Fishyer --> 'Fishyer'
     */
    public static String addQuote(String s, String operation) {
        String[] strings = s.split(operation);
        return strings[0] + operation + "'" + strings[1] + "'";
    }

}

各種註解標識:

@Table:表註解，預設是類名，也可以自定義表名
@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";
}