动态生成mybatis的xml文件，其中的sql语句生成



以findDTOByID为例。做一个说明例子，Forum为主表，Subject为Forum的字表，Post为Subject的字表。在Post里包含一个字段为Poster，指向Employee表。

类结果如下：AbstractTable定义Sql表的共同数据；表分为两种，一种是普通表class SQLTable extends AbstractTable，另一种是class JoinTable extends AbstractTable。还包括SQLField类，如下调用：

public String findByID(DTO dto) {aliasIndex = 0;SQLTable leaf = new SQLTable(dto, getFreeAlias());leaf.where(dto.getPrimaryKey(), "=", "#{id}");SQLTable cur = leaf;for (JavaField jField : dto.getFields()) {if (jField.getField().getType() instanceof ComponentType) {DTO ref = (DTO) jField.getType();SQLTable parent = new SQLTable(ref, getFreeAlias());if (jField.getField().isNotNull()) {JoinTable jtable = new JoinTable(JoinTable.Type.INNER, cur, leaf.findField(jField.getField().getJdbcName()), getFreeAlias());parent.join(jtable);}else {JoinTable jtable = new JoinTable(JoinTable.Type.LEFT, cur, leaf.findField(jField.getField().getJdbcName()), getFreeAlias());parent.join(jtable);}cur = parent;}}return cur.getSelectSQL();}

其中DTO为一个表数据结构类，包含字段和类型等数据信息。

public class SQLTable extends AbstractTable {public static final String SELECT = "SELECT ";public static final String FROM = " FROM ";public static final String WHERE = " WHERE";public static final String ORDERBY = " ORDER BY";public static final String LIMIT = " LIMIT ";protected JoinTable join = null;protected List<Where> wheres = new ArrayList<>();protected List<OrderBy> orders = new ArrayList<>();protected String limit;public SQLTable(DTO dto, String tableAlias) {super(dto.getName().toUpperCase(), tableAlias.toUpperCase());fields = getSQLField(dto.getFields());}public JoinTable getJoinTable() {return join;}public SQLTable join(JoinTable table) {this.join = table;return this;}public SQLTable where(JavaField field, String op, String val) {SQLField sField = findField(field);wheres.add(new Where(sField, op, val));return this;}public SQLTable orderby(JavaField field, String order) {SQLField sField = findField(field);orders.add(new OrderBy(sField, order));return this;}public SQLTable limit(String limit) {this.limit = limit;return this;}public SQLField getPrimaryKey() {for (SQLField f : fields) {if (f.isPrimaryKey()) {return f;}}return null;}public SQLField findField(String name) {name = name.toUpperCase();for (SQLField f : fields) {if (f.getName().equals(name)) {return f;}}return null;}public SQLField findField(JavaField jField) {for (SQLField f : fields) {if (f.getJavaField() == jField) {return f;}}return null;}public String getSelectSQL() {StringBuffer sb = new StringBuffer();sb.append(SELECT);sb.append(getSelectFieldsSQL());if (join != null) {sb.append(COMMA);sb.append(join.getSelectFieldsSQL());sb.append(FROM);sb.append(" ( ");sb.append(join.getChildTable().getSelectSQL());sb.append(" ) ");sb.append(join.getTableAlias());sb.append(SPACE);sb.append(join.getJoinSQL());sb.append(SPACE);sb.append(getTableName());sb.append(SPACE);sb.append(getTableAlias());sb.append(" ON ");sb.append(join.getTableAlias());sb.append(DOT);sb.append(join.findFieldByChildField().getName());sb.append(" = ");sb.append(getTableAlias());sb.append(DOT);sb.append(getPrimaryKey().getName());}else {sb.append(FROM);sb.append(getTableName());sb.append(SPACE);sb.append(getTableAlias());if (wheres.isEmpty() == false) {sb.append(WHERE);for (Where w : wheres) {sb.append(SPACE);sb.append(getTableAlias());sb.append(".");sb.append(w.getField().getName());sb.append(w.getOp());sb.append(w.getVal());}}if (orders.isEmpty() == false) {sb.append(ORDERBY);for (OrderBy orderby : orders) {sb.append(SPACE);sb.append(getTableAlias());sb.append(".");sb.append(orderby.getField().getName());sb.append(SPACE);sb.append(orderby.getOrder());}}if (limit != null && limit.length() > 0) {sb.append(LIMIT);sb.append(limit);}}return sb.toString();}private List<SQLField> getSQLField(List<JavaField> jFields) {ArrayList<SQLField> fields = new ArrayList<>(jFields.size());jFields.forEach(e-> {String name = e.getField().getJdbcName();SQLField f = new SQLField(e, this, name);fields.add(f);});return fields;}}

下面是JoinTable

public class JoinTable extends AbstractTable {protected Type type;protected SQLTable childTable;protected SQLField childKey;public  JoinTable(Type type, SQLTable childTable, SQLField childKey, String tableAlias) {super(null, tableAlias.toUpperCase());this.type = type;this.childTable = childTable;this.childKey = childKey;for (SQLField field : getAllSQLFields()) {SQLField f = new SQLField(this, field);fields.add(f);}}public SQLTable getChildTable() {return childTable;}public SQLField getChildKey() {return childKey;}public SQLField findFieldByChildField() {SQLField p = childKey;while (p!=null && p.getTable() != this) {p = p.getUp();}return p;}public List<SQLField> getAllSQLFields() {List<SQLField> fs = new ArrayList<>();fs.addAll(childTable.getFields());if (childTable.getJoinTable() != null) {fs.addAll(childTable.getJoinTable().getFields());}return fs;}public String getJoinSQL() {return type.name() + " JOIN";}public enum Type {INNER, LEFT, RIGHT;}}

最后是SQLFIeld

public class SQLField {private final JavaField jField; private final String name;private final AbstractTable table;private SQLField up = null;private SQLField down = null;public SQLField(AbstractTable table, SQLField field) {this(field.jField, table, field.getAlias());this.down = field;field.up = this;}public SQLField(JavaField jField, AbstractTable table, String name) {this.jField = jField;this.table = table;this.name = name.toUpperCase();}public String getName() {return name;}public String getAlias() {return table.getTableAlias() +"_"+name;}public boolean isPrimaryKey() {return jField.getField().getType() instanceof PrimaryKeyType;}public SQLField getUp() {return up;}public SQLField getDown() {return down;}public AbstractTable getTable() {return table;}public JavaField getJavaField() {return jField;}@Overridepublic String toString() {return name;}

运行后，生成的针对Post表的SQL查询ByID语句如下，其中换行是我手动添加的，原始是1行

    SELECT D.EMPLOYEEID D_EMPLOYEEID,D.NAME D_NAME,    E.B_SUBJECTID E_B_SUBJECTID,E.B_FORUMID E_B_FORUMID,E.B_TITLE E_B_TITLE,E.B_CONTENT E_B_CONTENT,    E.C_A_POSTID E_C_A_POSTID,E.C_A_SUBJECTID E_C_A_SUBJECTID,E.C_A_POSTERID E_C_A_POSTERID FROM  (       SELECT B.SUBJECTID B_SUBJECTID,B.FORUMID B_FORUMID,B.TITLE B_TITLE,B.CONTENT B_CONTENT,      C.A_POSTID C_A_POSTID,C.A_SUBJECTID C_A_SUBJECTID,C.A_POSTERID C_A_POSTERID FROM  (         SELECT A.POSTID A_POSTID,A.SUBJECTID A_SUBJECTID,A.POSTERID A_POSTERID FROM POST A WHERE A.POSTID=#{id}       ) C INNER JOIN SUBJECT B ON C.A_SUBJECTID = B.SUBJECTID     ) E INNER JOIN EMPLOYEE D ON E.C_A_POSTERID = D.EMPLOYEEID

三个表做内连接子查询，并自动为表和字段命名别名，再配合自动生成的mybatis的resultmap，可完成findbyid, findbypage, findbycount, insert, update, delete的生成。完整的生成mybatis xml文件
生成的类：

public class Post {private Integer postID;private Subject subject;private Employee poster;

resultMap是

<resultMap type="net.xway.bbs.forum.dto.Post" id="POST"><id property="PostID" column="C_A_POSTID"/><result property="subject.subjectID" column="B_SUBJECTID"/><result property="subject.forum" column="B_FORUMID"/><result property="subject.title" column="B_TITLE"/><result property="subject.content" column="B_CONTENT"/><result property="poster.employeeID" column="D_EMPLOYEEID"/><result property="poster.name" column="D_NAME"/></resultMap>