利用sql 生成带swagger注解的实体

吉魔灵

于 2023-02-08 15:29:51 发布

阅读量315

点赞数

文章标签： sql Powered by 金山文档

本文链接：https://blog.csdn.net/gu1079358530/article/details/128936395

版权

这段SQL脚本用于将MySQL数据库中的表字段信息转换为Java实体类的属性定义，包括数据类型转换、API注解生成以及驼峰命名规则的应用。转换过程考虑了如varchar、decimal、datetime等不同数据类型的默认值，并针对特定字段添加了@TableLogic、@Version等注解。

摘要由CSDN通过智能技术生成

-- 设置参数：schema数据库

SET @table_schema='ibp_enrolment';

-- 设置参数：表名称

SET @table_name='bs_warehouse_file_config';

SELECT

CONCAT('@ApiModelProperty(value="',main.COLUMN_COMMENT,'")\n','private ',

CASE

WHEN LOWER(main.DATA_TYPE) LIKE 'varchar%' OR LOWER(main.DATA_TYPE) LIKE 'char%' OR LOWER(main.DATA_TYPE) LIKE 'LONGTEXT%' OR LOWER(main.DATA_TYPE) LIKE 'tinytext%' THEN 'String'

WHEN LOWER(main.DATA_TYPE) LIKE 'decimal%' OR LOWER(main.DATA_TYPE) = 'float'THEN 'Double'

WHEN LOWER(main.DATA_TYPE)='int' OR LOWER(main.DATA_TYPE)='tinyint' THEN 'Integer'

WHEN LOWER(main.DATA_TYPE)='bigint' THEN 'Long'

WHEN LOWER(main.DATA_TYPE)='datetime' OR LOWER(main.DATA_TYPE)='timestamp' THEN 'Timestamp'

WHEN LOWER(main.DATA_TYPE)='date' THEN 'Date'

ELSE ''

END,' ',

underlineToCamel(main.COLUMN_NAME),

'=',

CASE

WHEN LOWER(main.DATA_TYPE) LIKE 'varchar%' OR LOWER(main.DATA_TYPE) LIKE 'char%' THEN '\"\"'

WHEN LOWER(main.DATA_TYPE) LIKE 'decimal%' THEN IF(main.NUMERIC_SCALE=1,'0.0D',IF(main.NUMERIC_SCALE=2,'0.00D',IF(main.NUMERIC_SCALE=3,'0.000D',IF(main.NUMERIC_SCALE=4,'0.0000D','0.00D'))))

WHEN LOWER(main.DATA_TYPE) = 'float' THEN '0.0D'

WHEN LOWER(main.DATA_TYPE)='int' OR LOWER(main.DATA_TYPE)='tinyint' THEN '0'

WHEN LOWER(main.DATA_TYPE)='bigint' THEN '0L'

WHEN LOWER(main.DATA_TYPE)='datetime' OR LOWER(main.DATA_TYPE)='timestamp' THEN 'Timestamp.valueOf(LocalDateTime.now())'

WHEN LOWER(main.DATA_TYPE)='date' THEN 'Date.valueOf(LocalDate.now())'

ELSE ''

END,

';') AS VO,

CONCAT('/**\n','*',main.COLUMN_COMMENT,'\n*/\n',

CASE WHEN main.COLUMN_NAME='valid' THEN '@TableLogic(value = "0", delval = "1")\n@TableField(fill = FieldFill.INSERT)\n'

WHEN main.COLUMN_NAME='version' THEN '@Version\n@TableField(fill = FieldFill.INSERT_UPDATE)\n'

WHEN main.COLUMN_NAME='created_user_code' THEN '@TableField(fill = FieldFill.INSERT)\n'

WHEN main.COLUMN_NAME='created_time' THEN '@TableField(fill = FieldFill.INSERT)\n'

WHEN main.COLUMN_NAME='updated_user_code' THEN '@TableField(fill = FieldFill.INSERT_UPDATE)\n'

WHEN main.COLUMN_NAME='updated_time' THEN '@TableField(fill = FieldFill.INSERT_UPDATE)\n'

WHEN main.COLUMN_NAME='id' THEN '@TableId(type = IdType.AUTO)\n'

ELSE ''

END

,'private ',

CASE

WHEN LOWER(main.DATA_TYPE) LIKE 'varchar%' OR LOWER(main.DATA_TYPE) LIKE 'char%' OR LOWER(main.DATA_TYPE) LIKE 'LONGTEXT%' OR LOWER(main.DATA_TYPE) LIKE 'tinytext%' THEN 'String'

WHEN LOWER(main.DATA_TYPE) LIKE 'decimal%' OR LOWER(main.DATA_TYPE) = 'float'THEN 'Double'

WHEN LOWER(main.DATA_TYPE)='int' OR LOWER(main.DATA_TYPE)='tinyint' THEN 'Integer'

WHEN LOWER(main.DATA_TYPE)='bigint' THEN 'Long'

WHEN LOWER(main.DATA_TYPE)='datetime' OR LOWER(main.DATA_TYPE)='timestamp' THEN 'Timestamp'

WHEN LOWER(main.DATA_TYPE)='date' THEN 'Date'

ELSE ''

END,' ',

underlineToCamel(main.COLUMN_NAME),

'=',

CASE

WHEN LOWER(main.DATA_TYPE) LIKE 'varchar%' OR LOWER(main.DATA_TYPE) LIKE 'char%' THEN '\"\"'

WHEN LOWER(main.DATA_TYPE) LIKE 'decimal%' THEN IF(main.NUMERIC_SCALE=1,'0.0D',IF(main.NUMERIC_SCALE=2,'0.00D',IF(main.NUMERIC_SCALE=3,'0.000D',IF(main.NUMERIC_SCALE=4,'0.0000D','0.00D'))))

WHEN LOWER(main.DATA_TYPE) = 'float' THEN '0.0D'

WHEN LOWER(main.DATA_TYPE)='int' OR LOWER(main.DATA_TYPE)='tinyint' THEN '0'

WHEN LOWER(main.DATA_TYPE)='bigint' THEN '0L'

WHEN LOWER(main.DATA_TYPE)='datetime' OR LOWER(main.DATA_TYPE)='timestamp' THEN 'Timestamp.valueOf(LocalDateTime.now())'

WHEN LOWER(main.DATA_TYPE)='date' THEN 'Date.valueOf(LocalDate.now())'

ELSE ''

END,

';') AS 'Entity'

FROM

information_schema.`COLUMNS` main

WHERE

main.TABLE_SCHEMA = @table_schema

AND main.TABLE_NAME = @table_name;

其中转驼峰的函数underlineToCamel 如下：

CREATE DEFINER=`vx`@`%` FUNCTION `underlineToCamel`(paramString VARCHAR(200)) RETURNS varchar(200) CHARSET utf8

DETERMINISTIC

begin

set paramString = replace(paramString, '_a', 'A');

set paramString = replace(paramString, '_b', 'B');

set paramString = replace(paramString, '_c', 'C');

set paramString = replace(paramString, '_d', 'D');

set paramString = replace(paramString, '_e', 'E');

set paramString = replace(paramString, '_f', 'F');

set paramString = replace(paramString, '_g', 'G');

set paramString = replace(paramString, '_h', 'H');

set paramString = replace(paramString, '_i', 'I');

set paramString = replace(paramString, '_j', 'J');

set paramString = replace(paramString, '_k', 'K');

set paramString = replace(paramString, '_l', 'L');

set paramString = replace(paramString, '_m', 'M');

set paramString = replace(paramString, '_n', 'N');

set paramString = replace(paramString, '_o', 'O');

set paramString = replace(paramString, '_p', 'P');

set paramString = replace(paramString, '_q', 'Q');

set paramString = replace(paramString, '_r', 'R');

set paramString = replace(paramString, '_s', 'S');

set paramString = replace(paramString, '_t', 'T');

set paramString = replace(paramString, '_u', 'U');

set paramString = replace(paramString, '_v', 'V');

set paramString = replace(paramString, '_w', 'W');

set paramString = replace(paramString, '_x', 'X');

set paramString = replace(paramString, '_y', 'Y');

set paramString = replace(paramString, '_z', 'Z');

set paramString = replace(paramString, '_', '');

RETURN paramString;

end

利用sql 生成 带swagger注解的实体

利用sql 生成带swagger注解的实体