Bitter Mybatis and SpringData for a long time, I began to develop Java Orm framework

Architecture design

Defining base fields

First, you need to define an abstract class for the underlying query that implements some of the underlying methods, while providing the abstract methods that the subclasses themselves implement. So, what methods need to be defined? First, you need to be compatible with several basic SQL, which is CRUD related.

I’ve defined the following base fields here

public abstract class BaseQuery<T> {

    protected String primaryKey = "id";
    protected String table = "";
    protected List<String> with = new ArrayList<>();
    protected List<String> withCount = new ArrayList<>();
    protected Integer limit = -1;
    protected Integer offset = -1;
    protected List<String> traitInitializers = new ArrayList<>();
    protected List<String> globalScopes = new ArrayList<>();
    protected List<String> ignoreOnTouch = new ArrayList<>();
    protected List<String> columns = new ArrayList<>();
    protected WhereSyntaxTree wheres = new WhereSyntaxTree();
    protected Map<String, Object> params = new HashMap<>();

    protected Map<String, Object> updateSetMaps = new HashMap<>();

    protected List<String> orders = new ArrayList<>();
}
Copy the code

Among them, generic programming is used here, which needs to be added when inheriting the class. The T here refers to the business table itself, so when it comes to the final underlying class, it can be directly written.

Here, I define a recursive structure for where, because the WHERE statement is likely to be recursive, that is, when the WHERE statement is generated, there will probably be parentheses inside the parentheses. Its structure is as follows:

public class WhereSyntaxTree {

    public Boolean isFinal = false;
    List<WhereSyntaxTree> childTree = new ArrayList<>();
    public WhereSyntaxNode whereSyntaxNode;
}
Copy the code

As you can see, this where tree is a multi-fork tree. The content of the node is:

public class WhereSyntaxNode {
    private String name;
    private String operate = "=";
    private Object value;
    private String setName;
    private Boolean valueContainBracket;
    private Boolean listValueIsObject; // Whether the list data is a complex object, complex objects need special processing
}
Copy the code

Where a = :a and b = :b The setName in the node object is the variable name after the colon. By default, setName is equal to name, but when setName already appears in the previous node, it needs to regenerate the setName.

The BaseQuery here is essentially a basic query for a relational database implementation. Then consider implementing a database query, using PostgreSql as an example, implementing PostgreSQLBaseQuery, which inherits from BaseQuery. Its fields are defined as follows:

public class PostgreSQLBaseQuery<T> extends BaseQuery<T> implements LogExtenseInterface.TreeExtenseInterface.LRUCacheExtensionInterface {

    private Log logger = LogFactory.getLog(PostgreSQLBaseQuery.class);

    private Converter<String, String> camelToUnderscoreConverter = CaseFormat.LOWER_CAMEL.converterTo(CaseFormat.LOWER_UNDERSCORE);

    protected Class<T> clazz;

    public T model;
}
Copy the code

Some plugins are implemented here, in order to avoid writing too much code in BaseQuery, so that functions can be decouple, friends can follow their own understanding to implement log extension, cache extension and tree query extension.

Implement basic query related functions

First, each class of the underlying query needs a constructor, so let’s look at the implementation. BaseQuery is an abstract class and does not need to provide a constructor. PostgreSQLBaseQuery has introduced a generic class T that needs to be initialized, so its constructor is as follows:

public PostgreSQLBaseQuery(a) {
    Class clazz = getClass();
    while(clazz ! = Object.class) { Type t = clazz.getGenericSuperclass();if (t instanceof ParameterizedType) {
            Type[] args = ((ParameterizedType) t).getActualTypeArguments();
            if (args[0] instanceof Class) {
                this.clazz = (Class<T>) args[0];
                break; }}}try {
        Constructor constructor = this.clazz.getDeclaredConstructor();
        model = (T) constructor.newInstance();
    } catch (NoSuchMethodException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    }
    this.table = camelToUnderscoreConverter.convert(this.clazz.getSimpleName());
    logger.info(this.table);
}
Copy the code

The class of the currently instantiated object is retrieved for subsequent deserialization. When a HashMap transforms an object, you need to provide the object’s original class.

Then, to get the dot-dot effect, we need to implement a set of basic methods that must return the value of Query itself, because that’s the only way to keep dot-dot. The following methods are implemented in BaseQuery:

public BaseQuery<T> finds(List<String> names) {
    this.columns.addAll(names);
    return this;
}

public BaseQuery<T> size(Integer size) {
    this.limit = size;
    return this;
}

public BaseQuery<T> page(Integer page) {
    this.offset = page;
    return this;
}

public BaseQuery<T> orderBy(String key, String order) {
    this.orders.add(key + "" + order);
    return this;
}

public BaseQuery<T> find(String name) {
    this.columns.add(name);
    return this;
}

public AndWhereSyntaxTree defaultAndWheres(Map<String, Object> andWheres) {
    return wheres.createAndTree(andWheres);
}


public AndWhereSyntaxTree defaultAndWheresWithOperate(List<Triplet<String, String, Object>> andWheres) {
    return wheres.createAndTreeByOperate(andWheres);
}

public OrWhereSyntaxTree defaultOrWheresWithOperate(List<Triplet<String, String, Object>> orWheres) {
    return wheres.createOrTreeByOperate(orWheres);
}


public OrWhereSyntaxTree defaultOrWheres(Map<String, Object> orWheres) {
    return wheres.createOrTree(orWheres);
}

public BaseQuery<T> set(String name, Object value) {
    this.updateSetMaps.put(name, value);
    return this;
}

public BaseQuery<T> sets(Map<String, Object> sets) {
    this.updateSetMaps.putAll(sets);
    return this;
}
Copy the code

The above method provides the method required for basic query and update. By analyzing CRUD, it can be known that the current system requires logical deletion, so the deletion operation only needs to realize CRU, and RU has been provided at present. You just need to combine these methods in an inherited class to implement the requirements.

In addition to the provided methods, we need to define some abstract methods that subclasses must implement, as follows:

public abstract T simpleGet(a);

public abstract Long count(a);

public abstract List<Map<String, Object>> listMapGet();

public abstract Long insert(Map<String, Object> values);

public abstract Integer batchInsert(List<Map<String, Object>> listValues);

public abstract Integer update(Map<String, Object> conditions, Map<String, Object> values);

public abstract Integer update(List<Triplet<String, String, Object>> condition, Map<String, Object> values);

public abstract Integer updateById(Object primaryKey, Map<String, Object> value);
Copy the code

These abstract methods can be multiplied according to the associated default method requirements, which is an iterative process and won’t be delved into for now.

Finally, you can define default SQL generation methods in BaseQuery that subclasses can override or override.

• The default generates functions that update the SQL

protected String defaultGenerateUpdateSql(a) {
    String sql = "";
    if (updateSetMaps.size() == 0) {
        return "";
    }
    sql = "UPDATE " + this.table + " SET ";
    String setSql = "";
    for (Map.Entry<String, Object> set : this.updateSetMaps.entrySet()) {
        if (setSql.equals("")) {
            setSql = set.getKey() + "=:set" + set.getKey();
            this.params.put("set" + set.getKey(), set.getValue());
        } else {
            setSql = setSql + "," + set.getKey() + "=:set" + set.getKey();
            this.params.put("set"+ set.getKey(), set.getValue()); }}if (StringUtils.hasText(setSql)) {
        sql = sql + "" + setSql;
    }
    String whereSql = this.wheres.getSql(this.params);
    if (StringUtils.hasText(whereSql)) {
        if (whereSql.startsWith("(") && whereSql.endsWith(")")) {
            whereSql = whereSql.substring(1, whereSql.length() - 1);
        }
        sql = sql + " WHERE " + whereSql + "";
    }
    sql = sql + ";";
    return sql;
}
Copy the code

Generate update statements directly from the current variable. This is why I mentioned earlier that each Query is a separate Query, and if it wasn’t, there would be conflicts and multithreading problems with variables. It’s a lot of trouble to solve.

• Functions that generate queries for SQL by default

protected String defaultGenerateSql(a) {
    String sql = "";
    for (String column : this.columns) {
        if (sql.equals("")) {
            sql = "SELECT " + column;
        } else {
            sql = sql + ","+ column; }}if(! StringUtils.hasText(sql)) {return null;
    } else {
        sql = sql + "";
    }
    sql = sql + "FROM " + table + "";
    String whereSql = this.wheres.getSql(this.params);
    if (StringUtils.hasText(whereSql)) {
        if (whereSql.startsWith("(") && whereSql.endsWith(")")) {
            whereSql = whereSql.substring(1, whereSql.length() - 1);
        }
        sql = sql + "WHERE " + whereSql + "";
    }
    // TODO: 2021/8/17 order by
    if (this.orders.size() > 0) {
        String orderSql = "";
        for (String order : this.orders) {
            if (orderSql.equals("")) {
                orderSql = "order by " + order;
            } else {
                orderSql = orderSql + "," + order;
            }
        }
        sql = sql + orderSql + "";
    }
    // TODO: 2021/8/17 offset
    if (this.offset ! = -1) {
        if (this.limit ! = -1) {
            sql = sql + " offset " + (this.offset - 1) * this.limit + "";
        } else {
            sql = sql + " offset " + this.offset + ""; }}if (this.limit ! = -1) {
        sql = sql + " limit " + this.limit + "";
    }
    sql = sql + ";";
    return sql;
}
Copy the code

Well, that’s about it for our basic query.

How does a WHERE multi-fork tree generate SQL

If you remember, when we defined the WHERE of the base query class, it was a multi-fork tree type. So why is it a multi-fork tree? Let me illustrate it with a picture:



Then, the way to generate a full WHERE statement is obvious, simply printing out each node as a depth-first traversal of a multi-fork tree. The code is as follows:

public String getSql(Map<String, Object> params) {
    if (isFinal) {
        if (params.containsKey(whereSyntaxNode.getSetName())) {
            Random random = new Random();
            whereSyntaxNode.setSetName(MD5Utils.compMd5(whereSyntaxNode.getSetName() + LocalDateTime.now().toString() + random.ints().toString()));
        }
        params.put(whereSyntaxNode.getSetName(), whereSyntaxNode.getValue());
        if (whereSyntaxNode.getValueContainBracket()) {
            return whereSyntaxNode.getName() + "" + whereSyntaxNode.getOperate() + "(" + whereSyntaxNode.getSetName() + ")";
        } else {
            return whereSyntaxNode.getName() + "" + whereSyntaxNode.getOperate() + ":"+ whereSyntaxNode.getSetName(); }}else {
        String sunSql = "";
        for (WhereSyntaxTree whereSyntaxTree : childTree) {
            if (whereSyntaxTree instanceof AndWhereSyntaxTree) {
                if (sunSql.equals("")) {
                    sunSql = whereSyntaxTree.getSql(params);
                } else {
                    sunSql = sunSql + " AND "+ whereSyntaxTree.getSql(params); }}else if (whereSyntaxTree instanceof OrWhereSyntaxTree) {
                if (sunSql.equals("")) {
                    sunSql = whereSyntaxTree.getSql(params);
                } else {
                    sunSql = sunSql + " OR "+ whereSyntaxTree.getSql(params); }}}if (StringUtils.hasText(sunSql)) {
            return "(" + sunSql + ")";
        } else {
            return ""; }}}Copy the code