通用查询解决方案

数据库表之间关系：

一对一（可以看做一对多的特例）
一对多
多对多

下图将涵盖了所有关系。

根据restful接口风格，我们最终会落到一个实体上，示例按照b表。其他表同理。

GET https://ip:port/xx/xx/1/0/b

查询的场景：

只需要b表的某些元素
需要b某些元素，及相关联的a表某些元素。
需要b某些元素，及a,c,d某些元素。

思路：通过数据库的外键，自动生成代码。在新建实体类时，自动查询关联的实体。关联实体查询一层一层传导下去。

通过在返回对象实体上加注解，标识需要返回的字段，及需要关联查询的对象，防止洪泛式关联对象查询。

class B{

自身属性；

关联的外键对象a；

被关联的对象c集合；

构造方法（）{

　　查询自身属性；

　　查询关联的外键对象a；

　　查询被关联的对象c集合；

}
}

生成代码示例如下：

public class BInfo implements Serializable {

    private static final long serialVersionUID = 1L;
    
    public BInfo(){}

    // 主键
    @JsonProperty("id")
    private Integer id;
    
    // 名称
    @JsonProperty("name")
    private String name;
    
    // 详情
    @JsonProperty("detail")
    private String detail;
    
    // 状态:0-无效，1-有效，2-编辑
    @JsonProperty("status")
    private Integer status;
    
    // 外键关联实体类(字段：[ a_id ] 注释：[  ])
    // 业务实体(关联表名：tbl_a )
    @JsonProperty("tblAInfo")
    private AInfo aInfo;

    // 业务实体 集合 [tbl_c][b_id]
    @JsonProperty("tblCInfoList")
    private List cInfoList;


    // 构造方法
    public BInfo(SapoDao dao ,B b) throws Exception {
        super();
        //System.err.println("-----new BInfo--------------");
        
        Class<? extends BInfo> thisClass = this.getClass();
        JsonIgnoreProperties annotation = thisClass.getAnnotation(JsonIgnoreProperties.class);
       
        // 外键关联对象：业务实体(tbl_a)
        // 如果注解标注不需要该字段，则不用查询该字段
        if(annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("tblAInfo"))) {
            // 不管状态，只用外键查询 
            // A a = dao.getAById(b.getAId());
            
           A aResult = null;
        
            // 外键字段不为空才有去查的意义
            if(b.getAId() != null){
            // 组建查询条件
                A aForQuery = new  A();
           
                 aForQuery.setId(b.getAId());
                // aForQuery.setStatus(A.STATUS_INVALID);
            
                // 查询，不可为空
                aResult = dao.getA(aForQuery) ;
            }
                        
            // 如果能查该外键对应的值，则进行赋值操作
            if(aResult !=null){
            
                // 判断该字段是否被注解标识，使用哪个子类
                FkPojo fkano = thisClass.getAnnotation(FkPojo.class);
                
                // 如果没有被注解标注，需要报错
                if (fkano == null || fkano.value().length == 0) {
                    throw new Exception(thisClass.getName() + " ->  must have @PkPojo annotation and lenght !=0 ");
                }
                
                // pojo不符合规范也要报错，注解用冒号分开[类名:全类名]
                Map map = new HashMap();
                for (String s : fkano.value()) {
                    s = s.trim();
                    String[] split = s.split(":");
                    if (split == null || split.length != 2) {
                        throw new Exception(
                                thisClass.getName() + " ->  @PkPojo annotation format error [pojoName:pojoAllPath] ");
                    }
                    map.put(split[0], split[1]);
                }
                //如果没有标识子类使用哪一个，则报错
                if(map.get("AInfo")==null){
                    throw new Exception(thisClass.getName() + " ->   @PkPojo annotation has no class: AInfo");
                }
                // 反射出子类，将子类赋值给该对象。
                Class<?> c1 = Class.forName(map.get("AInfo"));
                Constructor<?> declaredConstructor = c1.getDeclaredConstructor(SapoDao.class, A.class);
                this.aInfo = (AInfo)declaredConstructor.newInstance(dao, aResult);
                    
                }
        }

   
        this.detail=b.getDetail();

   
        this.id=b.getId();

   
        this.name=b.getName();

   
        this.status=b.getStatus();


        // 注解如果将该字段忽略了，就不需要查了。
        if (annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("tblCInfoList"))) {

            C cForQuery = new C();
            cForQuery.setBId(b.getId());

            List cList = dao.getCListWithNull(cForQuery);

            // 如果查询的集合是空，也不用继续往下查了。
            if (cList != null && cList.size() != 0) {
                cInfoList = new ArrayList(cList.size());
                // 判断该字段是否被注解标识，使用哪个子类
                FkPojo fkano = thisClass.getAnnotation(FkPojo.class);

                // 如果没有被注解标注，需要报错
                if (fkano == null || fkano.value().length == 0) {
                    throw new Exception(thisClass.getName() + " ->  must have @PkPojo annotation and lenght !=0 ");
                }

                // pojo不符合规范也要报错，注解用冒号分开[类名:全类名]
                Map map = new HashMap();
                for (String s : fkano.value()) {
                    s = s.trim();
                    String[] split = s.split(":");
                    if (split == null || split.length != 2) {
                        throw new Exception(
                                thisClass.getName() + " ->  @PkPojo annotation format error [pojoName:pojoAllPath] ");
                    }
                    map.put(split[0], split[1]);
                }
                // 如果没有标识子类使用哪一个，则报错
                if (map.get("CInfo") == null) {
                    throw new Exception(
                            thisClass.getName() + " ->   @PkPojo annotation has no class: CInfo");
                }
                // 反射出子类，将子类赋值给该对象。
                Class<?> c1 = Class.forName(map.get("CInfo"));
                Constructor<?> declaredConstructor = c1.getDeclaredConstructor(SapoDao.class, C.class);
                
                // 循环新建对象，将对象加入到集合中。
                for (C item : cList) {
                    cInfoList.add((CInfo) declaredConstructor.newInstance(dao, item));
                }
            }
        }

    
    }


    /**
     * 设置
     * 主键
     * 的方法
     *
     * @param id 主键
     */
    public void setId(Integer id){
        this.id = id;
    }
    
    /**
     * 获取
     * 主键
     * 的方法
     *
     * @return   主键
     */
    public Integer getId(){
        return id;
    }

    /**
     * 设置
     * 名称
     * 的方法
     *
     * @param name 名称
     */
    public void setName(String name){
        this.name = name;
    }
    
    /**
     * 获取
     * 名称
     * 的方法
     *
     * @return   名称
     */
    public String getName(){
        return name;
    }

    /**
     * 设置
     * 详情
     * 的方法
     *
     * @param detail 详情
     */
    public void setDetail(String detail){
        this.detail = detail;
    }
    
    /**
     * 获取
     * 详情
     * 的方法
     *
     * @return   详情
     */
    public String getDetail(){
        return detail;
    }

    /**
     * 设置
     * 状态:0-无效，1-有效，2-编辑
     * 的方法
     *
     * @param status 状态:0-无效，1-有效，2-编辑
     */
    public void setStatus(Integer status){
        this.status = status;
    }
    
    /**
     * 获取
     * 状态:0-无效，1-有效，2-编辑
     * 的方法
     *
     * @return   状态:0-无效，1-有效，2-编辑
     */
    public Integer getStatus(){
        return status;
    }

    /**
     * 设置
     * 业务实体(tbl_a )
     * 的方法
     *
     * @param 业务实体(tbl_a )
     */
    public AInfo setAInfo(AInfo aInfo){
        this.aInfo = aInfo;
        return this;
    }
    
    /**
     * 获取
     * 业务实体(tbl_a )
     * 的方法
     *
     * @return 
     */
    public AInfo getAInfo(){
        return aInfo;
    }


    
    public void setCInfoList(List cInfoList){
        this.cInfoList = cInfoList;
    }
    
    
    public List getCInfoList(){
        return cInfoList;
    }


}

B实体对象

@JsonIgnoreProperties({
"id",
"name",
"detail",
"status",
"tblAInfo",
"tblCInfoList"
})

@FkPojo({
"CInfo:classQualifiedName",
"AInfo:classQualifiedName"
})
public class BInfoxx extends BInfo{

    private static final long serialVersionUID = 1L;
    
    public BInfoxx(){}
    
    public BInfoxx(SapoDao dao,B b) throws Exception{
        super(dao,b);
    }
}

B实体返回对象

生成代码工具如下：

   1 DROP PROCEDURE IF EXISTS `print_pojo`;
   2 DELIMITER $
   3 CREATE  PROCEDURE `print_pojo`()
   4 BEGIN
   5 
   6 SET  group_concat_max_len = 4294967295;
   7 
   8 -- 表名去除那些前缀
   9 -- SET @noStrInTbl='tbl_ams';
  10 SET @noStrInTbl='tbl';
  11 -- 通用dao层类名，
  12 SET @common_dao_name='SapoDao';
  13 -- domain类名后缀
  14 SET @domain_suffix='Info';
  15 
  16 SET @domain_prefix='tbl_sapo';
  17 
  18 
  19 
  20 -- ######################begin:基础信息表维护###################
  21 
  22 -- 保存所有表及表的所有字段
  23 DROP TABLE if EXISTS all_col_table;
  24 CREATE table if not exists all_col_table(
  25 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  26 tbl_name VARCHAR(256)    COMMENT '表名:tbl_sapo_admin_account',
  27 tbl_name_comment  VARCHAR(256)   COMMENT '表注释',
  28 
  29 tbl_name_upper_camel VARCHAR(1024) COMMENT '表名大写驼峰:SapoAdminAccount',
  30 tbl_name_lower_camel VARCHAR(1024) COMMENT '表名小写驼峰:sapoAdminAccount',
  31 
  32 domain_tbl_name_upper_camel VARCHAR(1024) COMMENT 'doamin层表名驼峰:SapoAdminAccountInfo',
  33 domain_tbl_name_lower_camel VARCHAR(1024) COMMENT 'domain层表名引用驼峰:sapoAdminAccountInfo',
  34 
  35 col VARCHAR(256)   COMMENT '字段名:create_time',
  36 if_fk VARCHAR(512) NOT NULL DEFAULT 'no' COMMENT '外键标志，yes=外键',
  37 col_comment VARCHAR(512) COMMENT '字段注释',
  38 col_lower_camel VARCHAR(256) COMMENT '字段驼峰形式:createTime',
  39 col_upper_camel VARCHAR(256) COMMENT '字段驼峰首字母大写:CreateTime',
  40 
  41 col_type VARCHAR(256) COMMENT '字段类型，datetime,int',
  42 java_type VARCHAR(256) COMMENT 'java类型，LocalDateTime,Integer',
  43 
  44 col_setter VARCHAR(256)   COMMENT 'setter模式：setCreateTime',
  45 col_getter VARCHAR(256)   COMMENT 'getter模式：getCreateTime',
  46 
  47 json_property VARCHAR(256) COMMENT 'json_property字段',
  48 
  49 PRIMARY KEY (`id`) ,
  50 index  (`tbl_name`,col) ,
  51 INDEX idx_1(col)
  52 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  53 
  54 
  55 -- 外键临时表
  56 DROP TABLE if exists fk_def;
  57 CREATE TABLE if not exists fk_def as
  58     SELECT
  59         t.TABLE_NAME AS tbl_name,
  60         k.column_name AS col,
  61         k.REFERENCED_TABLE_NAME AS rf_tbl_name,
  62         k.REFERENCED_COLUMN_NAME AS rf_col 
  63     FROM
  64         information_schema.TABLE_CONSTRAINTS t
  65         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
  66         ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
  67         AND t.TABLE_NAME = k.TABLE_NAME 
  68         AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 
  69     WHERE
  70         t.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  71         AND t.table_schema = DATABASE();
  72         
  73 ALTER TABLE `fk_def`
  74     ADD INDEX `idx1` (tbl_name,col);
  75     
  76 ALTER TABLE `fk_def`
  77     ADD INDEX `idx2` (rf_tbl_name,rf_col);
  78 
  79 
  80 -- select * from fk_def ;
  81 -- ######################end:基础信息表维护###################
  82 
  83 
  84 -- 将本库中所有表及所有字段插入表中: tbl_name,tbl_name_comment,col,col_comment,col_type
  85 INSERT INTO all_col_table(tbl_name,tbl_name_comment,col,col_comment,col_type) 
  86 SELECT 
  87     t1.table_name, t2.TABLE_COMMENT,t1.column_name ,t1.COLUMN_COMMENT,t1.DATA_TYPE
  88 FROM
  89     information_schema.COLUMNS t1 JOIN information_schema.tables t2 ON t1.TABLE_NAME=t2.TABLE_NAME     
  90 WHERE
  91       t1.table_schema= DATABASE() AND t1.TABLE_NAME LIKE 'tbl_%' ORDER BY t1.TABLE_NAME,t1.ORDINAL_POSITION;
  92 
  93 -- java类型转换
  94 UPDATE all_col_table SET java_type=
  95 case  col_type 
  96     when 'datetime' then 'LocalDateTime' 
  97     when 'tinyint'  then 'Byte' 
  98     when 'bigint'   then 'Long' 
  99     when 'int'      then 'Integer' 
 100     when 'varchar'  then 'String' 
 101 END;
 102 
 103 -- 字段转驼峰
 104 UPDATE all_col_table SET col_lower_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') 
 105 ,'');
 106 UPDATE all_col_table SET col_upper_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS('','_',col), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') 
 107 ,'');
 108 -- getter,setter
 109 UPDATE all_col_table SET col_setter=CONCAT_WS('','set',col_upper_camel);
 110 UPDATE all_col_table SET col_getter=CONCAT_WS('','get',col_upper_camel);
 111 
 112 
 113 -- 表名转驼峰
 114 UPDATE all_col_table SET tbl_name_upper_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, @noStrInTbl, ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ;
 115 UPDATE all_col_table SET tbl_name_lower_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, CONCAT(@noStrInTbl,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ;
 116 UPDATE all_col_table SET domain_tbl_name_upper_camel= CONCAT_WS('',tbl_name_upper_camel,@domain_suffix);
 117 UPDATE all_col_table SET domain_tbl_name_lower_camel= CONCAT_WS('',tbl_name_lower_camel,@domain_suffix);
 118 
 119 -- 更新if_fk是否外键标志
 120 UPDATE all_col_table a SET a.if_fk=
 121 ifnull((SELECT 'yes' FROM fk_def f WHERE f.tbl_name=a.tbl_name AND a.col = f.col),'no');
 122 
 123 
 124 
 125 -- ######################end:基础信息表维护###################
 126 
 127 
 128 -- ######################begin:基础字段代码表###################
 129 -- 拼接代码使用
 130 DROP TABLE if EXISTS all_col_code;
 131 CREATE table if not exists all_col_code(
 132 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
 133 tbl_name VARCHAR(256)    COMMENT '表名:tbl_sapo_admin_account',
 134 col VARCHAR(256)   COMMENT '字段名:create_time',
 135 col_type VARCHAR(256) COMMENT '字段类型，datetime,int',
 136 if_fk VARCHAR(512) NOT NULL DEFAULT 'no' COMMENT   '外键标志，yes=外键',
 137 
 138 
 139 col_code LONGTEXT COMMENT 'private Integer id;',
 140 domain_col_code LONGTEXT COMMENT '@注解 private Integer id;',
 141 
 142 domain_to_model LONGTEXT COMMENT '',
 143 
 144 getter_setter_code LONGTEXT COMMENT 'getter ,setter',
 145 domain_getter_setter_code LONGTEXT COMMENT 'domain层getter,setter',
 146 
 147 to_string_code LONGTEXT COMMENT 'toString代码',
 148 domain_to_string_code LONGTEXT COMMENT 'domain_toString代码',
 149 
 150 domain_construct_code LONGTEXT COMMENT 'domain构造方法',
 151 PRIMARY KEY (`id`) ,
 152 index (`tbl_name`,col) 
 153 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 154 
 155 
 156 
 157 -- 初始化表名和字段
 158 INSERT INTO all_col_code (tbl_name,col,col_type,if_fk) SELECT tbl_name,col,col_type,if_fk FROM all_col_table;
 159 
 160 -- domain_to_model      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 161 SET @temp='        item.%col_setter%(this.%col_getter%());\r\n';
 162 
 163 UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk='no';
 164 
 165 SET @temp='        item.%col_setter%(this.get%fk_domain_tbl_name_upper_camel%()==null?null:this.get%fk_domain_tbl_name_upper_camel%().%fk_col_getter%());\r\n';
 166 
 167 UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk='yes';
 168 
 169 -- col_code      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 170 SET @temp = 
 171 '
 172     // %col_comment% [%col%]
 173     private %java_type% %col_lower_camel%;
 174     
 175 ';
 176 
 177 UPDATE all_col_code SET col_code = @temp;
 178 
 179 -- domain_col_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 180 
 181 -- 外键
 182 SET @temp = 
 183 '
 184     // 外键关联实体类(字段：[ %col% ] 注释：[ %col_comment% ])
 185     // %fk_tbl_name_comment%(关联表名：%fk_tbl_name% )
 186     @JsonProperty("%json_property%")
 187     private %fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%;
 188     
 189 ';
 190 
 191 
 192 
 193 UPDATE all_col_code SET domain_col_code = @temp WHERE if_fk='yes';
 194 
 195 -- datetime字段
 196 SET @temp =
 197 '
 198     // %col_comment%
 199     @JsonProperty("%col_lower_camel%")
 200     @JsonFormat(pattern = "yyyyMMddHHmmss")
 201     @JsonDeserialize(using = LocalDateTimeDeserializer.class)
 202     @JsonSerialize(using = LocalDateTimeSerializer.class)
 203     private %java_type% %col_lower_camel%;
 204 ';
 205 
 206 UPDATE all_col_code SET domain_col_code = @temp WHERE col_type='datetime';
 207 
 208 -- 普通字段
 209 SET @temp=
 210 '
 211     // %col_comment%
 212     @JsonProperty("%col_lower_camel%")
 213     private %java_type% %col_lower_camel%;
 214     
 215 ';
 216 UPDATE all_col_code SET domain_col_code = @temp WHERE domain_col_code IS NULL;
 217 
 218 
 219 
 220 -- getter_setter_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 221 SET @temp=
 222 '
 223     /**
 224      * 设置
 225      * %col_comment%
 226      * 的方法
 227      *
 228      * @param %col_lower_camel% %col_comment%
 229      */
 230     public %tbl_name_upper_camel% %col_setter%(%java_type% %col_lower_camel%){
 231         this.%col_lower_camel% = %col_lower_camel%;
 232         return this;
 233     }
 234     
 235     /**
 236      * 获取
 237      * %col_comment%
 238      * 的方法
 239      *
 240      * @return   %col_comment%
 241      */
 242     public %java_type% %col_getter%(){
 243         return %col_lower_camel%;
 244     }
 245 '
 246 ;
 247 
 248 update all_col_code SET getter_setter_code=@temp ;
 249 
 250 -- domain_getter_setter_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 251 
 252 -- 不带外键
 253 SET @temp=
 254 '
 255     /**
 256      * 设置
 257      * %col_comment%
 258      * 的方法
 259      *
 260      * @param %col_lower_camel% %col_comment%
 261      */
 262     public void %col_setter%(%java_type% %col_lower_camel%){
 263         this.%col_lower_camel% = %col_lower_camel%;
 264     }
 265     
 266     /**
 267      * 获取
 268      * %col_comment%
 269      * 的方法
 270      *
 271      * @return   %col_comment%
 272      */
 273     public %java_type% %col_getter%(){
 274         return %col_lower_camel%;
 275     }
 276 '
 277 ;
 278 update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk='no';
 279 
 280 -- 外键字段
 281 SET @temp=
 282 '
 283     /**
 284      * 设置
 285      * %fk_tbl_name_comment%(%fk_tbl_name% )
 286      * 的方法
 287      *
 288      * @param %fk_tbl_name_comment%(%fk_tbl_name% )
 289      */
 290     public %fk_domain_tbl_name_upper_camel% set%fk_domain_tbl_name_upper_camel%(%fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%){
 291         this.%fk_domain_tbl_name_lower_camel% = %fk_domain_tbl_name_lower_camel%;
 292         return this;
 293     }
 294     
 295     /**
 296      * 获取
 297      * %fk_tbl_name_comment%(%fk_tbl_name% )
 298      * 的方法
 299      *
 300      * @return %col_comment%
 301      */
 302     public %fk_domain_tbl_name_upper_camel% get%fk_domain_tbl_name_upper_camel%(){
 303         return %fk_domain_tbl_name_lower_camel%;
 304     }
 305 ';
 306 
 307 update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk='yes' ;
 308 
 309 -- to_string_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 310 SET @temp='"%col_lower_camel%=" + %col_lower_camel%';
 311 
 312 UPDATE all_col_code SET to_string_code =@temp;
 313 
 314 -- domain_to_string_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 315 -- 不是外键，延续之前的to_string_code
 316 UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk='no';
 317 
 318 -- 如果是外键
 319 SET @temp='"%fk_domain_tbl_name_lower_camel%=" + %fk_domain_tbl_name_lower_camel%';
 320 UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk='yes';
 321 
 322 -- domain_construct_code  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 323 -- 外键字段
 324 SET @temp=
 325 '   
 326         // 外键关联对象：%fk_tbl_name_comment%(%fk_tbl_name%)
 327         // 如果注解标注不需要该字段，则不用查询该字段
 328         if(annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("%json_property%"))) {
 329             // 不管状态，只用外键查询 
 330             // %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel% = dao.get%fk_tbl_name_upper_camel%By%fk_col_upper_camel%(%tbl_name_lower_camel%.%col_getter%());
 331             
 332            %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel%Result = null;
 333         
 334             // 外键字段不为空才有去查的意义
 335             if(%tbl_name_lower_camel%.%col_getter%() != null){
 336             // 组建查询条件
 337                 %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel%ForQuery = new  %fk_tbl_name_upper_camel%();
 338            
 339                  %fk_tbl_name_lower_camel%ForQuery.set%fk_col_upper_camel%(%tbl_name_lower_camel%.%col_getter%());
 340                 // %fk_tbl_name_lower_camel%ForQuery.setStatus(%fk_tbl_name_upper_camel%.STATUS_INVALID);
 341             
 342                 // 查询，不可为空
 343                 %fk_tbl_name_lower_camel%Result = dao.get%fk_tbl_name_upper_camel%(%fk_tbl_name_lower_camel%ForQuery) ;
 344             }
 345                         
 346             // 如果能查该外键对应的值，则进行赋值操作
 347             if(%fk_tbl_name_lower_camel%Result !=null){
 348             
 349                 // 判断该字段是否被注解标识，使用哪个子类
 350                 FkPojo fkano = thisClass.getAnnotation(FkPojo.class);
 351                 
 352                 // 如果没有被注解标注，需要报错
 353                 if (fkano == null || fkano.value().length == 0) {
 354                     throw new Exception(thisClass.getName() + " ->  must have @PkPojo annotation and lenght !=0 ");
 355                 }
 356                 
 357                 // pojo不符合规范也要报错，注解用冒号分开[类名:全类名]
 358                 Map map = new HashMap();
 359                 for (String s : fkano.value()) {
 360                     s = s.trim();
 361                     String[] split = s.split(":");
 362                     if (split == null || split.length != 2) {
 363                         throw new Exception(
 364                                 thisClass.getName() + " ->  @PkPojo annotation format error [pojoName:pojoAllPath] ");
 365                     }
 366                     map.put(split[0], split[1]);
 367                 }
 368                 //如果没有标识子类使用哪一个，则报错
 369                 if(map.get("%fk_domain_tbl_name_upper_camel%")==null){
 370                     throw new Exception(thisClass.getName() + " ->   @PkPojo annotation has no class: %fk_domain_tbl_name_upper_camel%");
 371                 }
 372                 // 反射出子类，将子类赋值给该对象。
 373                 Class<?> c1 = Class.forName(map.get("%fk_domain_tbl_name_upper_camel%"));
 374                 Constructor<?> declaredConstructor = c1.getDeclaredConstructor(%common_dao_name%.class, %fk_tbl_name_upper_camel%.class);
 375                 this.%fk_domain_tbl_name_lower_camel% = (%fk_domain_tbl_name_upper_camel%)declaredConstructor.newInstance(dao, %fk_tbl_name_lower_camel%Result);
 376                     
 377                 }
 378         }
 379 ';
 380 
 381 SET @temp:=REPLACE(@temp,'%common_dao_name%',@common_dao_name);
 382 
 383 UPDATE all_col_code  SET domain_construct_code=@temp WHERE if_fk='yes';
 384 
 385 
 386 
 387 -- 不是外键字段
 388 SET @temp=
 389 '   
 390         this.%col_lower_camel%=%tbl_name_lower_camel%.%col_getter%();
 391 '
 392 ;
 393 UPDATE all_col_code SET domain_construct_code =@temp WHERE if_fk='no';
 394 
 395 -- ######################begin:基础字段代码表###################
 396 
 397 /*
 398 -- 建立外键详情表需要打印出字段列表
 399 SELECT 
 400     GROUP_CONCAT(
 401     CONCAT_WS('','a.', t1.column_name ,' as fk_',t1.column_name )
 402     )
 403 FROM
 404     information_schema.COLUMNS t1
 405 WHERE
 406       t1.table_schema= DATABASE() AND t1.TABLE_NAME='all_col_table';
 407       
 408 */
 409 
 410 -- 外键明细表
 411 DROP TABLE if exists fk_all_col_table;
 412 CREATE TABLE if NOT exists fk_all_col_table
 413 SELECT 
 414 f.tbl_name,f.col,a.tbl_name AS json_property,a.tbl_name as fk_tbl_name,a.tbl_name_comment as fk_tbl_name_comment
 415 ,a.tbl_name_upper_camel as fk_tbl_name_upper_camel,a.tbl_name_lower_camel as fk_tbl_name_lower_camel
 416 ,a.domain_tbl_name_upper_camel as fk_domain_tbl_name_upper_camel,a.domain_tbl_name_lower_camel as fk_domain_tbl_name_lower_camel
 417 ,a.col as fk_col,a.if_fk as fk_if_fk,a.col_comment as fk_col_comment,a.col_lower_camel as fk_col_lower_camel
 418 ,a.col_upper_camel as fk_col_upper_camel,a.col_type as fk_col_type
 419 ,a.java_type as fk_java_type,a.col_setter as fk_col_setter,a.col_getter as fk_col_getter
 420 FROM all_col_table a 
 421 JOIN fk_def f 
 422 ON a.tbl_name=f.rf_tbl_name 
 423 WHERE f.rf_col=a.col
 424 ; 
 425 
 426 ALTER TABLE `fk_all_col_table`
 427     ADD INDEX `idx1` (tbl_name,col);
 428     
 429 
 430 
 431 -- 更新 json_property  ，及在json注解中显示的名字
 432 UPDATE fk_all_col_table SET json_property=
 433 CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(json_property, CONCAT(@domain_prefix,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') 
 434 ,'Info');
 435 
 436 -- json_property 
 437 
 438 UPDATE all_col_table SET json_property = col_lower_camel WHERE if_fk='no';
 439 UPDATE all_col_table a SET a.json_property = (SELECT f.json_property FROM fk_all_col_table f WHERE a.tbl_name =f.tbl_name AND a.col=f.col) 
 440 WHERE a.if_fk='yes';
 441 
 442 
 443 SET @temp='   
 444 UPDATE all_col_code c  SET c.@col_code@= REPLACE(c.@col_code@,\'%@tbl_name@%\',
 445 ifnull((select a.@tbl_name@ FROM @all_col_table@ a WHERE c.tbl_name=a.tbl_name AND c.col=a.col),\'%@tbl_name@%\')
 446 );
 447 ';
 448 
 449 
 450 
 451 -- 所有执行替换的sql都在该表中
 452 DROP TABLE if exists exe_sql_tbl;
 453 CREATE TABLE if NOT EXISTS exe_sql_tbl as
 454 SELECT (@id:=@id+1) as id,t1.TABLE_NAME,t.col,
 455     replace(
 456     replace(
 457     REPLACE(@temp,'@tbl_name@',t1.COLUMN_NAME)
 458     ,'@all_col_table@',t1.TABLE_NAME) 
 459     ,'@col_code@',t.col) 
 460     AS code
 461 FROM
 462     information_schema.COLUMNS t1
 463     JOIN (
 464     SELECT 'col_code' AS col
 465     UNION ALL 
 466     SELECT 'domain_col_code'
 467     UNION ALL 
 468     SELECT 'getter_setter_code'
 469     UNION ALL 
 470     SELECT 'domain_getter_setter_code'
 471     UNION ALL 
 472     SELECT 'to_string_code'
 473     UNION ALL 
 474     SELECT 'domain_to_string_code'
 475      UNION ALL 
 476     SELECT 'domain_construct_code'
 477     UNION all
 478     SELECT 'domain_to_model'
 479     ) t
 480     JOIN (SELECT @id:=0) tt
 481 WHERE
 482       t1.table_schema= DATABASE() AND t1.TABLE_NAME IN ('all_col_table','fk_all_col_table')
 483 ;
 484  
 485  ALTER TABLE `exe_sql_tbl`
 486     ADD INDEX `idx1` (id);
 487  
 488 -- select * from exe_sql_tbl;
 489 -- 确定循环次数
 490 SELECT MAX(id) INTO @var_count FROM exe_sql_tbl;
 491   
 492 -- 循环  
 493 loop_lab:LOOP
 494     if @var_count = 0 then
 495         leave loop_lab;
 496     END if;
 497     
 498     -- 从表中取出一个sql
 499     SELECT CODE INTO @exe_sql FROM exe_sql_tbl WHERE id =@var_count;
 500     
 501     -- 动态执行
 502     PREPARE stmt FROM @exe_sql ;
 503     EXECUTE stmt ;
 504 
 505     SET @var_count = @var_count -1;
 506 END LOOP;
 507 
 508 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 509 
 510 -- 表相关数据
 511 DROP TABLE if EXISTS all_table;
 512 CREATE table if not exists all_table(
 513 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
 514 tbl_name VARCHAR(256)  NOT NULL  COMMENT '表名:tbl_sapo_admin_account',
 515 tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount',
 516 tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount',
 517 domain_tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount',
 518 domain_tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount',
 519 pojo LONGTEXT COMMENT '',
 520 domain_pojo LONGTEXT  COMMENT '',
 521 controller_res_pojo LONGTEXT COMMENT '',
 522 controller_req_pojo LONGTEXT COMMENT '',
 523 controller_pojo_annotation LONGTEXT COMMENT'',
 524 controller_pojo_fk_pojo_annotation LONGTEXT COMMENT '',
 525 domain_to_model LONGTEXT COMMENT '',
 526 constant_code LONGTEXT COMMENT '',
 527 domain_construct_code LONGTEXT COMMENT '',
 528 list_domain_construct_code LONGTEXT COMMENT '',
 529 to_string_code LONGTEXT COMMENT 'toString',
 530 domain_to_string_code LONGTEXT COMMENT 'toString',
 531 col_code LONGTEXT COMMENT 'getter_setter_code',
 532 list_col_code LONGTEXT COMMENT 'getter_setter_code',
 533 getter_setter_code LONGTEXT COMMENT '',
 534 list_getter_setter_code LONGTEXT COMMENT '',
 535 domain_col_code LONGTEXT COMMENT '',
 536 domain_getter_setter_code LONGTEXT COMMENT '',
 537 PRIMARY KEY (`id`) ,
 538 index (`tbl_name`) 
 539 ) ENGINE=InnoDB ;
 540 
 541 -- 初始化表名等字段
 542 INSERT INTO all_table (tbl_name,tbl_name_upper_camel,tbl_name_lower_camel,domain_tbl_name_upper_camel,domain_tbl_name_lower_camel)
 543 SELECT DISTINCT tbl_name,tbl_name_upper_camel,tbl_name_lower_camel,domain_tbl_name_upper_camel,domain_tbl_name_lower_camel FROM all_col_table;
 544 
 545 
 546 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 547 -- 设置 被外键关联的表。 例如：业务订单下面挂了好几个支付订单
 548 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 549 
 550 
 551 DROP TABLE if EXISTS fk_list;
 552 CREATE TABLE if NOT exists fk_list 
 553 SELECT f.rf_tbl_name ,f.rf_col
 554     ,CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.tbl_name, CONCAT(@domain_prefix,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,'InfoList') AS json_property_list
 555     ,a.* 
 556     from fk_def f
 557     JOIN all_col_table a
 558     on f.tbl_name = a.tbl_name  and f.col=a.col;
 559 
 560 ALTER TABLE `fk_list`
 561     ADD COLUMN `rf_tbl_name_lower_camel` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_tbl_name`;
 562 
 563 ALTER TABLE `fk_list`
 564     ADD COLUMN `rf_col_getter` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_col`;
 565 
 566 ALTER TABLE `fk_list`
 567     ADD INDEX `idx1` (rf_tbl_name);
 568     
 569 -- SELECT * FROM fk_list;
 570 -- list_col_code
 571 
 572 UPDATE fk_list l SET l.rf_tbl_name_lower_camel=(SELECT a.tbl_name_lower_camel FROM all_col_table a WHERE a.tbl_name=l.rf_tbl_name AND a.col = l.rf_col);
 573 UPDATE fk_list l SET l.rf_col_getter=(SELECT a.col_getter FROM all_col_table a WHERE a.tbl_name=l.rf_tbl_name AND a.col = l.rf_col);
 574 
 575 -- domain_to_model
 576 
 577 SET @temp=
 578 '
 579     // 将domain对象转成model对象
 580     public %tbl_name_upper_camel% domain2%tbl_name_upper_camel%(){
 581         %tbl_name_upper_camel% item = new %tbl_name_upper_camel%();
 582 %domain_to_model%
 583         return item;        
 584     }
 585 ';
 586 
 587 UPDATE all_table a SET a.domain_to_model=
 588 REPLACE(
 589 REPLACE(@temp,'%domain_to_model%',
 590     (
 591         SELECT 
 592             GROUP_CONCAT( c.domain_to_model ORDER BY c.id SEPARATOR '')
 593         FROM all_col_code c WHERE c.tbl_name=a.tbl_name
 594     )
 595 )
 596 ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel)
 597 ;
 598 
 599 -- list_col_code
 600 
 601 SET @temp=
 602 '
 603     // %tbl_name_comment% 集合 [%tbl_name%][%col%]
 604     @JsonProperty("%json_property_list%")
 605     private List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List;
 606 '
 607 ;
 608 UPDATE all_table atb SET atb.list_col_code=
 609 (
 610     SELECT GROUP_CONCAT( 
 611             REPLACE(
 612             REPLACE(
 613             REPLACE(
 614             REPLACE(
 615             REPLACE(
 616             REPLACE(@temp,'%tbl_name_comment%',tbl_name_comment)
 617             ,'%tbl_name%',tbl_name)
 618             ,'%col%',col)
 619             ,'%json_property_list%',json_property_list)
 620             ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel)
 621             ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel)
 622         SEPARATOR '')
 623 FROM fk_list t
 624 WHERE atb.tbl_name=t.rf_tbl_name
 625  ) ;
 626 
 627 -- SELECT * FROM fk_list;
 628 
 629 -- 外键list，构造对象语句
 630 SET @temp=
 631 '
 632         // 注解如果将该字段忽略了，就不需要查了。
 633         if (annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("%json_property_list%"))) {
 634 
 635             %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery = new %tbl_name_upper_camel%();
 636             %tbl_name_lower_camel%ForQuery.%col_setter%(%rf_tbl_name_lower_camel%.%rf_col_getter%());
 637 
 638             List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = dao.get%tbl_name_upper_camel%ListWithNull(%tbl_name_lower_camel%ForQuery);
 639 
 640             // 如果查询的集合是空，也不用继续往下查了。
 641             if (%tbl_name_lower_camel%List != null && %tbl_name_lower_camel%List.size() != 0) {
 642                 %domain_tbl_name_lower_camel%List = new ArrayList<%domain_tbl_name_upper_camel%>(%tbl_name_lower_camel%List.size());
 643                 // 判断该字段是否被注解标识，使用哪个子类
 644                 FkPojo fkano = thisClass.getAnnotation(FkPojo.class);
 645 
 646                 // 如果没有被注解标注，需要报错
 647                 if (fkano == null || fkano.value().length == 0) {
 648                     throw new Exception(thisClass.getName() + " ->  must have @PkPojo annotation and lenght !=0 ");
 649                 }
 650 
 651                 // pojo不符合规范也要报错，注解用冒号分开[类名:全类名]
 652                 Map map = new HashMap();
 653                 for (String s : fkano.value()) {
 654                     s = s.trim();
 655                     String[] split = s.split(":");
 656                     if (split == null || split.length != 2) {
 657                         throw new Exception(
 658                                 thisClass.getName() + " ->  @PkPojo annotation format error [pojoName:pojoAllPath] ");
 659                     }
 660                     map.put(split[0], split[1]);
 661                 }
 662                 // 如果没有标识子类使用哪一个，则报错
 663                 if (map.get("%domain_tbl_name_upper_camel%") == null) {
 664                     throw new Exception(
 665                             thisClass.getName() + " ->   @PkPojo annotation has no class: %domain_tbl_name_upper_camel%");
 666                 }
 667                 // 反射出子类，将子类赋值给该对象。
 668                 Class<?> c1 = Class.forName(map.get("%domain_tbl_name_upper_camel%"));
 669                 Constructor<?> declaredConstructor = c1.getDeclaredConstructor(%common_dao_name%.class, %tbl_name_upper_camel%.class);
 670                 
 671                 // 循环新建对象，将对象加入到集合中。
 672                 for (%tbl_name_upper_camel% item : %tbl_name_lower_camel%List) {
 673                     %domain_tbl_name_lower_camel%List.add((%domain_tbl_name_upper_camel%) declaredConstructor.newInstance(dao, item));
 674                 }
 675             }
 676         }
 677 ';
 678 
 679 SET @temp:=REPLACE(@temp,'%common_dao_name%',@common_dao_name);
 680 
 681 
 682 update all_table a SET a.list_domain_construct_code=
 683 (
 684 SELECT GROUP_CONCAT(
 685         REPLACE(
 686         REPLACE(
 687         REPLACE(
 688         REPLACE(
 689         REPLACE(
 690         REPLACE(
 691         REPLACE(
 692         REPLACE(@temp,'%json_property_list%',json_property_list)
 693         ,'%tbl_name_upper_camel%',tbl_name_upper_camel)
 694         ,'%tbl_name_lower_camel%',tbl_name_lower_camel)
 695         ,'%col_setter%',col_setter)
 696         ,'%rf_tbl_name_lower_camel%',rf_tbl_name_lower_camel)
 697         ,'%rf_col_getter%',rf_col_getter)
 698         ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel)
 699         ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel)
 700     order BY f.tbl_name separator '')
 701 FROM fk_list f
 702 WHERE f.rf_tbl_name=a.tbl_name
 703 );
 704 
 705 
 706 
 707 -- list_getter_setter_code
 708 
 709 SET @temp=
 710 '
 711     
 712     public void set%domain_tbl_name_upper_camel%List(List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List){
 713         this.%domain_tbl_name_lower_camel%List = %domain_tbl_name_lower_camel%List;
 714     }
 715     
 716     
 717     public List<%domain_tbl_name_upper_camel%> get%domain_tbl_name_upper_camel%List(){
 718         return %domain_tbl_name_lower_camel%List;
 719     }
 720 ';
 721 
 722 UPDATE all_table atb SET atb.list_getter_setter_code=
 723 (
 724     SELECT GROUP_CONCAT( 
 725             REPLACE(
 726             REPLACE(
 727             REPLACE(
 728             REPLACE(
 729             REPLACE(
 730             REPLACE(@temp,'%tbl_name_comment%',tbl_name_comment)
 731             ,'%tbl_name%',tbl_name)
 732             ,'%col%',col)
 733             ,'%json_property_list%',json_property_list)
 734             ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel)
 735             ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel)
 736         SEPARATOR '')
 737 FROM fk_list t
 738 WHERE atb.tbl_name=t.rf_tbl_name
 739  ) ;
 740 
 741 -- list_controller_pojo_annotation
 742 
 743 -- list_controller_pojo_fk_pojo_annotation
 744 
 745 
 746 
 747 -- col_code
 748 UPDATE all_table a SET  a.col_code=
 749 (
 750     SELECT GROUP_CONCAT(col_code ORDER BY id SEPARATOR '') 
 751     FROM all_col_code 
 752     WHERE a.tbl_name =tbl_name
 753 );
 754 
 755 
 756 
 757 
 758 -- getter_setter_code
 759 UPDATE all_table a SET  a.getter_setter_code=
 760 (
 761     SELECT GROUP_CONCAT(getter_setter_code ORDER BY id SEPARATOR '') 
 762     FROM all_col_code 
 763     WHERE a.tbl_name =tbl_name
 764 );
 765 
 766 
 767 -- domain_col_code
 768 UPDATE all_table a SET  a.domain_col_code=
 769 (
 770     SELECT GROUP_CONCAT(domain_col_code ORDER BY id SEPARATOR '')
 771     FROM all_col_code 
 772     WHERE a.tbl_name =tbl_name
 773 );
 774 
 775 -- 将外键list加上
 776 UPDATE all_table a SET a.domain_col_code=CONCAT_WS('',domain_col_code,'\r\n',list_col_code);
 777 
 778 -- domain_getter_setter_code
 779 UPDATE all_table a SET  a.domain_getter_setter_code=
 780 (
 781     SELECT GROUP_CONCAT(domain_getter_setter_code ORDER BY id SEPARATOR '')
 782     FROM all_col_code 
 783     WHERE a.tbl_name =tbl_name
 784 );
 785 
 786 -- 将外键list加上
 787 UPDATE all_table a SET a.domain_getter_setter_code=CONCAT_WS('',domain_getter_setter_code,'\r\n',list_getter_setter_code);
 788 
 789 
 790 -- toString()
 791 
 792 SET @template=
 793 '
 794     @Override
 795     public String toString(){
 796         return "%tbl_name_upper_camel% ["+%to_string_code%+" ]";
 797     }
 798 ';
 799 
 800 UPDATE all_table a SET a.to_string_code=
 801 (
 802 SELECT 
 803     REPLACE(
 804         REPLACE(
 805         @template,'%to_string_code%',GROUP_CONCAT(to_string_code SEPARATOR '+ " , " +' )
 806         )
 807         ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel
 808     )
 809 FROM all_col_code
 810 WHERE a.tbl_name = tbl_name
 811 );
 812 
 813 -- domain_to_string_code
 814 
 815 UPDATE all_table a SET a.domain_to_string_code=
 816 (
 817 SELECT 
 818     REPLACE(
 819         REPLACE(@template,'%to_string_code%',
 820         GROUP_CONCAT(t.domain_to_string_code SEPARATOR '+ " , " +')
 821         ),'%tbl_name_upper_camel%',a.domain_tbl_name_upper_camel
 822     )
 823 FROM 
 824 (
 825     SELECT tbl_name,domain_to_string_code FROM all_col_code
 826     UNION ALL /*以下为了展示 外键list*/
 827     SELECT rf_tbl_name AS tbl_name,CONCAT_WS('','"',domain_tbl_name_lower_camel,'List = "+',domain_tbl_name_lower_camel,'List') AS domain_to_string_code FROM fk_list   
 828 ) t
 829 WHERE a.tbl_name = t.tbl_name
 830 );
 831 
 832 
 833 -- domain_construct 构造器
 834 
 835 SET @template='
 836     // 构造方法
 837     public %domain_tbl_name_upper_camel%(%common_dao_name% dao ,%tbl_name_upper_camel% %tbl_name_lower_camel%) throws Exception {
 838         super();
 839         //System.err.println("-----new %domain_tbl_name_upper_camel%--------------");
 840         
 841         Class<? extends %domain_tbl_name_upper_camel%> thisClass = this.getClass();
 842         JsonIgnoreProperties annotation = thisClass.getAnnotation(JsonIgnoreProperties.class);
 843        
 844         
 845 %domain_construct_code%
 846 %list_domain_construct_code%
 847     
 848     }
 849 ';
 850 
 851 SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name);
 852 
 853 UPDATE all_table a SET a.domain_construct_code=
 854 (
 855 SELECT 
 856     REPLACE(
 857     REPLACE(
 858     REPLACE(
 859     REPLACE(
 860     REPLACE(@template,'%domain_construct_code%',GROUP_CONCAT(domain_construct_code SEPARATOR '\r\n'))
 861     ,'%domain_tbl_name_lower_camel%',a.domain_tbl_name_lower_camel)
 862     ,'%domain_tbl_name_upper_camel%',a.domain_tbl_name_upper_camel)
 863     ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel)
 864     ,'%tbl_name_lower_camel%',a.tbl_name_lower_camel)
 865     
 866 FROM all_col_code
 867 WHERE a.tbl_name = tbl_name
 868 );
 869 -- 将外键list更新进去
 870 UPDATE all_table SET domain_construct_code=REPLACE(domain_construct_code,'%list_domain_construct_code%',ifnull(list_domain_construct_code,' '));
 871 
 872 -- constant 静态常量
 873 
 874 SET @template='
 875     //%biz_desc%(%tbl_name% [%col%]) : %col_value_desc%
 876     public static final %java_type% %upper% = %col_value%;
 877 ';
 878 
 879 -- 如果不存在就创建表。防止报错
 880 CREATE TABLE if NOT EXISTS `sys_code_mapper` (
 881 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
 882 `create_time` datetime(3) NULL COMMENT '创建时间',
 883 `biz_desc` varchar(255) NULL COMMENT '业务含义',
 884 `tbl_name` varchar(255) NULL COMMENT '表名',
 885 `col` varchar(255) NULL COMMENT '字段',
 886 `col_value` varchar(255) NULL COMMENT '字段值',
 887 `col_value_desc` varchar(255) NULL COMMENT '字段值含义',
 888 `remark` varchar(255) NULL COMMENT '备注',
 889 PRIMARY KEY (`id`) ,
 890 INDEX idx_1(tbl_name,col)
 891 )COMMENT = '系统代码映射表';
 892 
 893 UPDATE all_table s SET s.constant_code =
 894 (
 895 SELECT 
 896 GROUP_CONCAT(
 897 REPLACE(
 898 REPLACE(
 899 REPLACE(
 900 REPLACE(
 901 REPLACE(
 902 REPLACE(
 903 REPLACE(
 904 @template,'%upper%', CONCAT_WS('',UPPER(d.col),'_',UPPER(d.remark)) )
 905 ,'%biz_desc%',d.biz_desc)
 906 ,'%tbl_name%',d.tbl_name)
 907 ,'%col%',d.col)
 908 ,'%col_value_desc%',d.col_value_desc)
 909 ,'%java_type%',a.java_type)
 910 ,'%col_value%',if(a.java_type='String',CONCAT_WS('','"',d.col_value,'"'),d.col_value))
 911  SEPARATOR '')
 912 FROM sys_code_mapper d
 913 JOIN all_col_table a
 914 ON a.tbl_name=d.tbl_name AND a.col=d.col 
 915 where  d.tbl_name=s.tbl_name
 916 );
 917 
 918 -- controller_pojo_annotation
 919 
 920 SET @template=
 921 '
 922 @JsonIgnoreProperties({
 923 %controller_pojo_annotation%
 924 })
 925 ';
 926 
 927 UPDATE all_table a SET a.controller_pojo_annotation=
 928 REPLACE(@template,'%controller_pojo_annotation%',
 929     (
 930     SELECT 
 931         GROUP_CONCAT(
 932                 CONCAT_WS('','"',t.json_property,'"') 
 933             order BY t.tbl_name,id SEPARATOR ',\r\n' )
 934     FROM 
 935     (
 936         SELECT id,tbl_name,json_property FROM all_col_table
 937         UNION ALL
 938         SELECT id,rf_tbl_name,json_property_list FROM fk_list /* 该行：添加外键list语句*/
 939     ) t
 940     WHERE t.tbl_name = a.tbl_name
 941     )
 942 );
 943 
 944 
 945 
 946 SET @template='@FkPojo({
 947 %controller_pojo_fk_pojo_annotation%
 948 })';
 949 
 950 UPDATE all_table a SET a.controller_pojo_fk_pojo_annotation=
 951 REPLACE(@template,'%controller_pojo_fk_pojo_annotation%',
 952 (
 953     SELECT 
 954     GROUP_CONCAT(
 955         CONCAT_WS('','"',t.fk_domain_tbl_name_upper_camel,':','classQualifiedName','"')
 956     order BY t.tbl_name SEPARATOR ',\r\n')
 957     FROM 
 958     (
 959         SELECT tbl_name,fk_domain_tbl_name_upper_camel FROM fk_all_col_table
 960         UNION ALL
 961         SELECT rf_tbl_name,domain_tbl_name_upper_camel FROM fk_list  /* 该行：添加外键list语句*/
 962     ) t WHERE t.tbl_name = a.tbl_name
 963 )
 964 );
 965 
 966 
 967 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 968 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 969 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 970 
 971 -- pojo
 972 
 973 SET @template=
 974 '
 975 
 976 
 977 public class %tbl_name_upper_camel% implements Serializable {
 978 
 979     private static final long serialVersionUID = 1L;
 980 
 981     public %tbl_name_upper_camel%(){}
 982     
 983     public static %tbl_name_upper_camel%  getInstance(){
 984         return new %tbl_name_upper_camel%();
 985     }
 986     
 987     // 赋值一个一模一样的对象
 988     public static %tbl_name_upper_camel%  getInstance(%tbl_name_upper_camel% source){
 989         %tbl_name_upper_camel% target = new %tbl_name_upper_camel%();
 990         org.springframework.beans.BeanUtils.copyProperties(source,target);
 991         return target;
 992     }
 993 
 994 %constant_code%
 995 %col_code%
 996 %getter_setter_code%
 997 %to_string_code%
 998 }
 999 ';
1000 
1001 UPDATE all_table  SET pojo=
1002 REPLACE(
1003 REPLACE(
1004 REPLACE(
1005 REPLACE(
1006 REPLACE(
1007 @template,'%tbl_name_upper_camel%',tbl_name_upper_camel)
1008 ,'%col_code%',col_code)
1009 ,'%getter_setter_code%',getter_setter_code)
1010 ,'%to_string_code%',to_string_code)
1011 ,'%constant_code%',ifnull(constant_code,''))
1012 ;
1013 
1014 -- domain_pojo
1015 SET @template=
1016 '
1017 public class %tbl_name_upper_camel%%doamin_suffix% implements Serializable {
1018 
1019     /*
1020     
1021     
1022         com.fasterxml.jackson.datatype
1023         jackson-datatype-jsr310
1024     
1025      @JsonProperty(value = "xx",access = JsonProperty.Access.WRITE_ONLY)
1026      @JsonProperty(value = "xx",access = JsonProperty.Access.READ_ONLY)
1027     */
1028     private static final long serialVersionUID = 1L;
1029     
1030     public %tbl_name_upper_camel%%doamin_suffix%(){}
1031     
1032 %domain_to_model%
1033 %constant_code%    
1034 %col_code%
1035 %domain_construct_code%
1036 %getter_setter_code%
1037 %to_string_code%
1038 }
1039 ';
1040 
1041 -- select * from all_table;
1042 -- select * from all_col_table;
1043 
1044 SET @template:=REPLACE(@template,'%doamin_suffix%',@domain_suffix);
1045 
1046 UPDATE all_table SET domain_pojo=
1047 REPLACE(
1048 REPLACE(
1049 REPLACE(
1050 REPLACE(
1051 REPLACE(
1052 REPLACE(
1053 REPLACE(
1054 @template,'%tbl_name_upper_camel%',tbl_name_upper_camel)
1055 ,'%col_code%',domain_col_code)
1056 ,'%getter_setter_code%',domain_getter_setter_code)
1057 ,'%to_string_code%',domain_to_string_code)
1058 ,'%domain_construct_code%',domain_construct_code)
1059 ,'%domain_to_model%',domain_to_model)
1060 ,'%constant_code%',ifnull(constant_code,''))
1061 ;
1062 
1063 -- SELECT * FROM all_table;
1064 -- controller_res_pojo
1065 
1066 SET @template=
1067 '
1068 %controller_pojo_annotation%
1069 public class %domain_tbl_name_upper_camel%xx extends %domain_tbl_name_upper_camel%{
1070 
1071     private static final long serialVersionUID = 1L;
1072     
1073     public %domain_tbl_name_upper_camel%xx(){}
1074     /*
1075         @NotNull(message=" xx can not be null")    验证对象是否不为null, 无法查检长度为0的字符串
1076         @Range(min = 1, max = 100000, message = " xx not between [xx,xx] range")
1077      
1078         @NotBlank(message=" xx can not be blank") 检查约束 (字符串) 是不是Null还有被Trim的长度是否大于0,只对字符串,且会去掉前后空格.
1079         @Pattern(regexp = "^1([38][0-9]|4[579]|5[0-3,5-9]|6[6]|7[0135678]|9[89])\\d{8}$", message = "xx not match regular expression ")    
1080         
1081         @NotEmpty(message=" xx can not be null or empty") 检查(集合)约束元素是否为NULL或者是EMPTY.
1082         @Size(min = 1, max = 1000, message = " xx  size not between [xx,xx]")
1083         
1084         @NotNull(message=" xx can not be null")  对象级联判断
1085         @Valid
1086         注：@JsonIgnoreProperties 和 @NotBlank 混用注意。可能忽略了字段，但是该字段上有非空注解。因为忽略肯定空，但是非空又在校验，故报错。
1087         所以忽略的字段不加非空判断。只能手动挨个加。
1088     */
1089 %col_code%
1090 %getter_setter_code%
1091 %to_string_code%
1092 }
1093 ';
1094 SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name);
1095 
1096 UPDATE all_table SET controller_req_pojo=
1097 REPLACE(
1098 REPLACE(
1099 REPLACE(
1100 REPLACE(
1101 REPLACE(
1102 REPLACE(
1103 REPLACE(
1104 REPLACE(
1105 REPLACE(@template,'%controller_pojo_annotation%',controller_pojo_annotation)
1106 ,'%controller_pojo_fk_pojo_annotation%',ifnull(controller_pojo_fk_pojo_annotation,''))
1107 ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel)
1108 ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel)
1109 ,'%tbl_name_lower_camel%',tbl_name_lower_camel)
1110 ,'%col_code%',domain_col_code)
1111 ,'%to_string_code%',domain_to_string_code)
1112 ,'%getter_setter_code%',domain_getter_setter_code)
1113 ,'%tbl_name_upper_camel%',tbl_name_upper_camel);
1114 
1115 
1116 -- controller_res_pojo
1117 
1118 SET @template=
1119 '
1120 %controller_pojo_annotation%
1121 %controller_pojo_fk_pojo_annotation%
1122 public class %domain_tbl_name_upper_camel%xx extends %domain_tbl_name_upper_camel%{
1123 
1124     private static final long serialVersionUID = 1L;
1125     
1126     public %domain_tbl_name_upper_camel%xx(){}
1127     
1128     public %domain_tbl_name_upper_camel%xx(%common_dao_name% dao,%tbl_name_upper_camel% %tbl_name_lower_camel%) throws Exception{
1129         super(dao,%tbl_name_lower_camel%);
1130     }
1131 }
1132 ';
1133 SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name);
1134 
1135 UPDATE all_table SET controller_res_pojo=
1136 REPLACE(
1137 REPLACE(
1138 REPLACE(
1139 REPLACE(
1140 REPLACE(
1141 REPLACE(
1142 REPLACE(@template,'%controller_pojo_annotation%',controller_pojo_annotation)
1143 ,'%controller_pojo_fk_pojo_annotation%',ifnull(controller_pojo_fk_pojo_annotation,''))
1144 ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel)
1145 ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel)
1146 ,'%tbl_name_lower_camel%',tbl_name_lower_camel)
1147 ,'%col_code%',domain_col_code)
1148 ,'%tbl_name_upper_camel%',tbl_name_upper_camel);
1149 
1150 -- 查看执行替换的sql列表
1151 -- SELECT * FROM exe_sql_tbl;
1152 
1153 -- 查看外键明细表
1154 -- SELECT * FROM fk_all_col_table;
1155 
1156 -- 查看基础明细
1157 -- select * from all_col_table; 
1158 
1159 -- select * from all_col_code;
1160 
1161 -- select * from all_table;
1162 
1163 
1164 
1165 -- DROP TABLE sys_code_mapper;
1166 -- DROP TABLE fk_def;
1167 -- DROP TABLE exe_sql_tbl;
1168 -- DROP TABLE fk_all_col_table;
1169 -- DROP TABLE all_col_table;
1170 -- DROP TABLE all_col_code;
1171 
1172 END$
1173 DELIMITER ;
1174 
1175 
1176 CALL print_pojo();
1177 
1178  -- select * from all_col_table; 
1179 
1180  -- select * from all_col_code;
1181 
1182 -- select * from all_table;
1183 
1184 -- SELECT tbl_name,pojo,domain_pojo FROM all_table;
1185 
1186 SELECT pojo FROM all_table;
1187 
1188 SELECT 
1189 '
1190 @Target(ElementType.TYPE)
1191 @Retention(RetentionPolicy.RUNTIME)
1192 public @interface FkPojo {
1193 
1194     public String[] value() default { };
1195 }
1196 ' AS 'domain_pojo'
1197 UNION all
1198 SELECT domain_pojo FROM all_table;
1199 
1200 SELECT tbl_name,controller_req_pojo AS 'controller_req_pojo' FROM all_table;
1201 
1202 SELECT tbl_name,controller_res_pojo AS 'controller_res_pojo' FROM all_table;
1203 
1204 
1205 
1206 DROP TABLE if exists all_col_code;
1207 DROP TABLE  if exists all_col_table;
1208 DROP TABLE  if exists all_table;
1209 DROP TABLE  if exists exe_sql_tbl;
1210 DROP TABLE  if exists fk_all_col_table;
1211 DROP TABLE  if exists fk_def;
1212 DROP TABLE  if exists sys_code_mapper;
1213 DROP TABLE  if exists fk_list;
1214 drop table  if exists  java_code;
1215 drop table  if exists java_code_file;
1216 drop PROCEDURE  if exists print_pojo;
1217 drop PROCEDURE  if exists print_code;
1218 
1219 /*
1220 DROP TABLE if exists all_col_code;
1221 DROP TABLE  if exists all_col_table;
1222 DROP TABLE  if exists all_table;
1223 DROP TABLE  if exists exe_sql_tbl;
1224 DROP TABLE  if exists fk_all_col_table;
1225 DROP TABLE  if exists fk_def;
1226 DROP TABLE  if exists sys_code_mapper;
1227 DROP TABLE  if exists fk_list;
1228 drop table  if exists  java_code;
1229 drop table  if exists java_code_file;
1230 drop PROCEDURE  if exists print_pojo;
1231 drop PROCEDURE  if exists print_code;
1232 
1233 
1234 
1235 
1236 SELECT * FROM all_col_table;
1237 
1238 SELECT * FROM all_col_code;
1239 
1240 SELECT * FROM fk_all_col_table;
1241 
1242 SELECT * FROM all_table;
1243 */

生成实体类代码

代码工具

通用查询解决方案

相关

VS2015Git 源代码工具使用

标签