java 的 sqlHelper，改改之后也适用于不使用 EF 的 C# 项目，包含查询和建表。

这个类用来拼接 sql。

package com.ly.orm;

public class Query {
    protected Query(String v) {
        sql = v;
    }

    public String toString() {
        return sql;
    }

    protected String sql;

    public String select(String... cols) {
        if (cols.length == 0) {
            return String.format(sql, "*");
        }
        StringBuilder sb = new StringBuilder();
        for (String c : cols) {
            sb.append(c + ',');
        }
        sb.setLength(sb.length() - 1);
        return String.format(sql, sb);
    }

    public static class P extends Query {

        protected P(String v) {
            super(v);
        }

        /**
         * @param limit_offset
         *            [0] is limit, [1] is offset.
         */
        public Query page(int[] limit_offset) {
            return new Query(sql + " limit " + limit_offset[0] + " offset " + limit_offset[1]);
        }
    }

    public static F from(String body) {
        return new F(body);
    }

    public static class F extends P {

        F(String v) {
            super("select %s from " + v);
        }

        public F join(String v) {
            return new F(sql + " " + v);
        }

        public W where(String v) {
            if (noe(v)) {
                return new W(sql);
            }
            return new W(sql + " where " + v);
        }

        public G groupby(String v) {
            if (noe(v)) {
                return new G(sql);
            }
            return new G(sql + " group by " + v);
        }

        public P orderby(String v) {
            if (noe(v)) {
                return new P(sql);
            }
            return new P(sql + " order by " + v);
        }

    }

    public static class W extends P {

        W(String v) {
            super(v);
        }

        public G groupby(String v) {
            if (noe(v)) {
                return new G(sql);
            }
            return new G(sql + " group by " + v);
        }

        public P orderby(String v) {
            if (noe(v)) {
                return new P(sql);
            }
            return new P(sql + " ordery by " + v);
        }
    }

    public static class G extends P {

        G(String v) {
            super(v);
        }

        public H having(String v) {
            if (noe(v)) {
                return new H(sql);
            }
            return new H(sql + " having " + v);
        }

        public P orderby(String v) {
            if (noe(v)) {
                return new P(sql);
            }
            return new P(sql + " order by " + v);
        }

        public static class H extends P {

            H(String h) {
                super(h);
            }

            public P orderby(String v) {
                if (noe(v)) {
                    return new P(sql);
                }
                return new P(sql + " order by " + v);
            }
        }
    }

    private static boolean noe(String v) {
        return v == null || v.trim().length() == 0;
    }
}

这个类用来生成 sql 的条件项，包括 where having orderby limit offset，而 groupby 会改变 sql 的结构，所以不是条件项。

package com.ly.orm;

public class Condtions {
    protected Condtions(Condtions s) {
        if (s == null) {
            return;
        } else {
            page = s.page;
            where = s.where;
            orderby = s.orderby;
            having = s.having;
        }
    }

    public static class Loader {

        public static HOW page(int limit, int offset) {
            HOW r = new HOW(null);
            r.page = new int[] { limit, offset };
            return r;
        }

        public static HOP where(String v) {
            HOP r = new HOP(null);
            r.where = v;
            return r;
        }

        public static OPW having(String v) {
            OPW r = new OPW(null);
            r.having = v;
            return r;
        }

        public static HPW orderby(String v) {
            HPW r = new HPW(null);
            r.orderby = v;
            return r;
        }
    }

    protected String where;
    protected String having;
    protected String orderby;
    protected int[] page;

    public static class HOP extends Condtions {

        protected HOP(Condtions s) {
            super(s);
        }

        public HO page(int l, int o) {
            HO r = new HO(this);
            r.page = new int[] { l, o };
            return r;
        }

        public HP orderby(String v) {
            HP r = new HP(this);
            r.orderby = v;
            return r;
        }

        public OP having(String v) {
            OP r = new OP(this);
            r.having = v;
            return r;
        }
    }

    public static class OPW extends Condtions {

        protected OPW(Condtions s) {
            super(s);
        }

        public OW page(int l, int o) {
            OW r = new OW(this);
            r.page = new int[] { l, o };
            return r;
        }

        public PW orderby(String v) {
            PW r = new PW(this);
            r.orderby = v;
            return r;
        }

        public OP where(String v) {
            OP r = new OP(this);
            r.where = v;
            return r;
        }
    }

    public static class HPW extends Condtions {

        protected HPW(Condtions s) {
            super(s);
        }

        public HW page(int l, int o) {
            HW r = new HW(this);
            r.page = new int[] { l, o };
            return r;
        }

        public HP where(String v) {
            HP r = new HP(this);
            r.where = v;
            return r;
        }

        public PW having(String v) {
            PW r = new PW(this);
            r.having = v;
            return r;
        }
    }

    public static class HOW extends Condtions {

        protected HOW(Condtions s) {
            super(s);
        }

        public HO where(String v) {
            HO r = new HO(this);
            r.where = v;
            return r;
        }

        public OW having(String v) {
            OW r = new OW(this);
            r.having = v;
            return r;
        }

        public HW orderby(String v) {
            HW r = new HW(this);
            r.orderby = v;
            return r;
        }
    }

    public class P extends Condtions {

        protected P(Condtions s) {
            super(s);
        }

        public Condtions page(int l, int o) {
            Condtions r = new Condtions(this);
            r.page = new int[] { l, o };
            return r;
        }
    }

    public class O extends Condtions {

        protected O(Condtions s) {
            super(s);
        }

        public Condtions orderby(String v) {
            Condtions r = new Condtions(this);
            r.orderby = v;
            return r;
        }
    }

    public class H extends Condtions {

        protected H(Condtions s) {
            super(s);
        }

        public Condtions having(String v) {
            Condtions r = new Condtions(this);
            r.having = v;
            return r;
        }
    }

    public class W extends Condtions {

        protected W(Condtions s) {
            super(s);
        }

        public Condtions where(String v) {
            Condtions r = new Condtions(this);
            r.where = v;
            return r;
        }
    }

    public class HO extends Condtions {

        protected HO(Condtions s) {
            super(s);
        }

        public O having(String v) {
            O r = new O(this);
            r.having = v;
            return r;
        }

        public H orderby(String v) {
            H r = new H(this);
            r.orderby = v;
            return r;
        }
    }

    public class HP extends Condtions {

        protected HP(Condtions s) {
            super(s);
        }

        public H page(int l, int o) {
            H r = new H(this);
            r.page = new int[] { l, o };
            return r;
        }

        public P having(String v) {
            P r = new P(this);
            r.having = v;
            return r;
        }
    }

    public class HW extends Condtions {

        protected HW(Condtions s) {
            super(s);
        }

        public H where(String v) {
            H r = new H(this);
            r.where = v;
            return r;
        }

        public W having(String v) {
            W r = new W(this);
            r.having = v;
            return r;
        }
    }

    public class OP extends Condtions {

        protected OP(Condtions s) {
            super(s);
        }

        public O page(int l, int o) {
            O r = new O(this);
            r.page = new int[] { l, o };
            return r;
        }

        public P orderby(String v) {
            P r = new P(this);
            r.orderby = v;
            return r;
        }
    }

    public class OW extends Condtions {

        protected OW(Condtions s) {
            super(s);
        }

        public O where(String v) {
            O r = new O(this);
            r.where = v;
            return r;
        }

        public W orderby(String v) {
            W r = new W(this);
            r.orderby = v;
            return r;
        }
    }

    public class PW extends Condtions {

        protected PW(Condtions s) {
            super(s);
        }

        public P where(String v) {
            P r = new P(this);
            r.where = v;
            return r;
        }

        public W page(int l, int o) {
            W r = new W(this);
            r.page = new int[] { l, o };
            return r;
        }
    }
}

两个 annotation。一个在创建表时会用到，一个在查询时会用到，留意其中的 group 。

package com.ly.orm;

public @interface Creating {

    /**
     * set first charactor ' ' if want to rename the column
     */
    String desc();

    int index() default 0;

}
package com.ly.orm;

public @interface Querying {
    String name();

    String groupby() default "";
}

这个类用来转换类型到 sql 语句，这里用到了 group 关键词。自定义列名的话，Creating 中用一个空格开头。

至于其中的 Enm，只是因为不想写 foreach。java 有人实现过伪 linq，我也写了一个伪 linq，只有一小部分，用他的吧。

package com.ly.orm;

import java.lang.reflect.Field;
import java.util.ArrayList;

import com.ly.linq.Enm;
import com.ly.linq.FuncT;
import com.ly.linq.FuncTT;
import com.ly.linq.Pre;

public class SqlMapper {
    public interface Mapper<T> extends FuncT<ItemLoader, T> {
    }

    public interface ItemLoader extends FuncTT<String, Class<?>, Object> {
    }

    public static <T> Mapper<T> load(final Class<T> cls) {
        return new Mapper<T>() {
            @Override
            public T get(ItemLoader querier) {
                T r;
                try {
                    r = cls.newInstance();
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
                Field[] fs = cls.getDeclaredFields();
                for (Field f : fs) {
                    if (f.getAnnotation(Ignored.class) != null) {
                        continue;
                    }
                    Querying anno = f.getAnnotation(Querying.class);
                    String name = anno == null ? f.getName() : anno.name();
                    Object val = querier.get(name, f.getType());
                    f.setAccessible(true);
                    try {
                        f.set(r, val);
                    } catch (Exception e) {
                        e.printStackTrace();
                        continue;
                    }
                }
                return r;
            }
        };
    }

    public static String getCreateSQL(Class<?> cls) {
        String cols = Enm.toString(Enm.select(Enm.sort(Enm.where(cls.getFields(),
                new Pre<Field>() {// where
                    @Override
                    public boolean check(Field ti) {
                        return ti.getAnnotation(Creating.class) != null;
                    }
                }), new FuncT<Field, Integer>() {// sort
            @Override
            public Integer get(Field ti) {
                return ti.getAnnotation(Creating.class).index();
            }
        }), new FuncT<Field, String>() {// select
            @Override
            public String get(Field ti) {
                String desc = ti.getAnnotation(Creating.class).desc();
                if (desc.charAt(0) == ' ') {
                    return desc.substring(1);
                }
                return ti.getName() + ' ' + desc;
            }
        }), ',');
        if (cols.length() == 0) {
            return cols;
        }
        Creating c = cls.getAnnotation(Creating.class);
        String table = c != null ? c.desc() : cls.getSimpleName();
        return String.format("create table %s(%s);", table, cols);
    }

    public static String getQuerySQL(Class<?> cls, Condtions p) {
        ArrayList<String> fuck = Enm.notNull(Enm.select(cls.getDeclaredFields(), getQueryingColName));
        String[] c = new String[fuck.size()];
        fuck.toArray(c);
        Querying q = cls.getAnnotation(Querying.class);
        String b = q == null ? cls.getSimpleName() : q.name();
        String g = q == null ? null : q.groupby();
        String h = g == null || g.trim().length() == 0 ? null : p.having;
        return Query.from(b).where(p.where).groupby(g).having(h).orderby(p.orderby).page(p.page).select(c);
    }

    private static FuncT<Field, String> getQueryingColName = new FuncT<Field, String>() {
        @Override
        public String get(Field ti) {
            if (ti.getAnnotation(Ignored.class) != null) {
                return null;
            }
            Querying q = ti.getAnnotation(Querying.class);
            return q == null ? ti.getName() : q.name();
        }
    };
}

这个是 android 中 SQLiteOpenHelper 子类的局部代码，query(类型，可选条件）就可以返回这个类型的集合，方便的很。

    public <T> ArrayList<T> query(Class<T> cls, Condtions sql, String... selectionArgs) {
        return query(cls, SqlMapper.getQuerySQL(cls, sql), selectionArgs);
    }

    public <T> ArrayList<T> query(Class<T> cls, String sql, String... selectionArgs) {
        Cursor c = getReadableDatabase().rawQuery(sql, selectionArgs);
        ArrayList<T> r = new ArrayList<T>();
        Mapper<T> mapper = SqlMapper.load(cls);
        while (c.moveToNext()) {
            r.add(mapper.get(getItem(c)));
        }
        c.close();
        return r;
    }

    private static ItemLoader getItem(final Cursor c) {
        return new ItemLoader() {
            @Override
            public Object get(String col, Class<?> t1) {
                int i = c.getColumnIndex(col);
                if (i < 0) {
                    return null;
                }
                if (t1.equals(int.class)) {
                    return c.getInt(i);
                }
                if (t1.equals(double.class)) {
                    return c.getDouble(i);
                }
                if (t1.equals(long.class)) {
                    return c.getLong(i);
                }
                if (t1.equals(short.class)) {
                    return c.getShort(i);
                }
                if (t1.equals(float.class)) {
                    return c.getFloat(i);
                }
                if (t1.equals(byte[].class)) {
                    return c.getBlob(i);
                }
                return c.getString(i);
            }
        };
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        for (Class<?> t : tables) {
            db.execSQL(SqlMapper.getCreateSQL(t));
        }
    }

 不想写任何字符串！但 java 没办法。

linq + DynamicLinq，想怎么写就怎么写，大家快转 C# 吧！