Navicat 根据表生成实体类字段和备注

2019独角兽企业重金招聘Python工程师标准>>>

SELECT
CONCAT('/** ', isc.column_comment, ' */') my_column_comment,
CONCAT('private ',
CASE
                                WHEN isc.data_type = 'varchar' or isc.data_type = 'char'  or isc.data_type = 'tinytext'  or isc.data_type = 'text'  or isc.data_type = 'longtext'   THEN
                                  'String'
                                WHEN isc.data_type = 'int' or isc.data_type = 'tinyint' or isc.data_type = 'integer' THEN
                                  'Integer'
                                WHEN isc.data_type = 'bigint' THEN
                                  'Long'
                                WHEN isc.data_type = 'datetime' THEN
                                  'Date'
                                WHEN isc.data_type = 'time' THEN
                                  'String'
                                WHEN isc.data_type = 'float' THEN
                                  'Double'
                                WHEN isc.data_type = 'decimal' THEN 
                                  'BigDecimal'
                                ELSE
                                  'String'
                              END,' ',
REPLACE(REPLACE(INITCAP(CONCAT('_',REPLACE(LOWER(isc.column_name),'_',' '))),'_',''),' ',''),
';',
CASE WHEN isc.data_type = 'datetime' THEN
CONCAT(' /** ', isc.column_comment,'字符串', ' */ ','private String ',
REPLACE(REPLACE(INITCAP(CONCAT('_',REPLACE(LOWER(isc.column_name),'_',' '))),'_',''),' ',''),'Str;')
ELSE '' END
)my_column
FROM
  information_schema.TABLES ist,
  information_schema.COLUMNS isc
WHERE ist.table_name = isc.table_name
  AND ist.table_schema = isc.table_schema
  AND ist.table_schema = 'yzsaas'
  -- AND ist.table_name LIKE 'ic_%'
  AND ist.table_name IN ('iqp_cno_account')
  ORDER BY ist.CREATE_TIME DESC,
  ist.table_name,ORDINAL_POSITION;

CREATE DEFINER=`yzsaas`@`%` FUNCTION `initcap`(ss VARCHAR (1000)) RETURNS varchar(1000) CHARSET utf8mb4
BEGIN
  DECLARE lena INT ;
  DECLARE pos INT ;
  DECLARE firsta CHAR(1) ;
  DECLARE seconda VARCHAR (999) ;
  DECLARE tmpstr VARCHAR (1000) ;
  DECLARE retstr VARCHAR (1000) ;
  IF (LENGTH(TRIM(ss)) = 0) 
  THEN RETURN '' ;
  END IF ;
  IF (RIGHT(ss, 1) != ' ') 
  THEN SET ss = CONCAT(ss, ' ') ;
  END IF ;
  SET pos = INSTR(ss, ' ') ;
  SET lena = LENGTH(ss) ;
  WHILE
    (pos > 0) DO SET tmpstr = TRIM(LEFT(ss, pos)) ;
    SET ss = RIGHT(ss, lena - pos) ;
    SET lena = LENGTH(tmpstr) ;
    SET firsta = UPPER(LEFT(tmpstr, 1)) ;
    SET seconda = LOWER(RIGHT(tmpstr, lena - 1)) ;
    IF (LENGTH(retstr) > 0) 
    THEN SET retstr = CONCAT(retstr, ' ', firsta, seconda) ;
    ELSE SET retstr = CONCAT(firsta, seconda) ;
    END IF ;
    SET pos = INSTR(ss, ' ') ;
    SET lena = LENGTH(ss) ;
  END WHILE ;
  RETURN retstr ;
END

转载于:https://my.oschina.net/u/2611678/blog/1817136