By

文章目录

下面这段SQL用于生成数据库对应的实体对象的代码。 执行效果

1
2
3
Integer deleteFlag;
String note;
String parentId;

SQL语句，执行的时候 注意修改 数据库名 和表名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
set @db_name = 'test';
set @table_name = 'table';

CREATE TEMPORARY TABLE if not exists tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

truncate table tally;

INSERT INTO tally (n)
SELECT NULL
 FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;
with cols as (
select COLUMN_NAME,DATA_TYPE,ordinal_position from information_schema.`COLUMNS`
where TABLE_SCHEMA=@db_name and TABLE_NAME=@table_name
)
, c as (
select SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, '_', n.n), '_', -1) word,  COLUMN_NAME,n from cols
CROSS JOIN tally n
where n.n <= 1 + (CHAR_LENGTH(COLUMN_NAME) - CHAR_LENGTH(REPLACE(COLUMN_NAME, '_', ''))) 
),
cd as (
   select if(n>1, concat(UPPER(left(word,1)),substring(word,2,(length(word)-1))), word ) word,COLUMN_NAME,n from c
),
cn as (
    select group_concat(word order by n separator '') varName,COLUMN_NAME from cd group by COLUMN_NAME
),
vct as (
   select varName,cn.COLUMN_NAME,DATA_TYPE from cn left join cols on cn.COLUMN_NAME=cols.COLUMN_NAME
),
map as (
select 'varchar' as dbType,'String' as javaType union
select 'timestamp' as dbType,'Date' as javaType union
select 'datetime' as dbType,'Date' as javaType union
select 'date' as dbType,'Date' as javaType union
select 'tinyint' as dbType,'Integer' as javaType union
select 'int' as dbType,'Integer' as javaType union
select 'smallint' as dbType,'Integer' as javaType union
select 'year' as dbType,'Integer' as javaType union
select 'bigint' as dbType,'Long' as javaType union
select 'mediumint' as dbType,'Long' as javaType union
select 'float' as dbType,'Float' as javaType union
select 'double' as dbType,'Double' as javaType union
select 'bit' as dbType,'Boolean' as javaType union
select 'decimal' as dbType,'BigDecimal' as javaType union
select 'char' as dbType,'String' as javaType union
select 'tinytext' as dbType,'String' as javaType union
select 'mediumtext' as dbType,'String' as javaType union
select 'longtext' as dbType,'String' as javaType union
select 'json' as dbType,'String' as javaType union
select 'enum' as dbType,'String' as javaType union
select 'text' as dbType,'String' as javaType
),
vctm as (
    select varName,COLUMN_NAME,DATA_TYPE,javaType from vct left join map on vct.DATA_TYPE=map.dbType
),
cl as (
    select concat(javaType,' ', varName, ';') from vctm inner join cols on vctm.COLUMN_NAME = cols.COLUMN_NAME ORDER BY cols.ordinal_position
)
select * from cl;
文章目录