iDZs0pwU

1-- --------------------------------------------------------------------------------
2-- Routine DDL
3-- Note: comments before and after the routine body will not be stored by the server
4-- --------------------------------------------------------------------------------
5DELIMITER $$
6 
7CREATE DEFINER=`root`@`localhost` PROCEDURE `GenJavaModel`(in pTableName VARCHAR(255) )
8BEGIN
9DECLARE vClassName varchar(255);
10declare vClassGetSet mediumtext;
11declare vClassPrivate mediumtext;
12declare v_codeChunk_pri_var varchar(1024);
13declare v_codeChunk_pub_get varchar(1024);
14declare v_codeChunk_pub_set varchar(1024);
15 
16DECLARE v_finished INTEGER DEFAULT 0;
17DEClARE code_cursor CURSOR FOR
18    SELECT pri_var,pub_get, pub_set FROM temp1; 
19 
20DECLARE CONTINUE HANDLER 
21        FOR NOT FOUND SET v_finished = 1;
22 
23set vClassGetSet ='';
24/* Make class name*/
25    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) into vClassName
26    FROM(
27    SELECT CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
28    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2
29    FROM
30    (SELECT SUBSTRING_INDEX(pTableName, '_', -1) as ColumnName2,
31        SUBSTRING_INDEX(pTableName, '_', 1) as ColumnName1) A) B;
32 
33    /*store all properties into temp table*/
34    CREATE TEMPORARY TABLE IF NOT EXISTS  temp1 ENGINE=MyISAM  
35    as (
36    select
37    concat('\tprivate ', ColumnType,' _', FieldName,';') pri_var,
38    concat( 'public ', ColumnType , ' get' , FieldName,'(){\r\n\t\t return _', FieldName,';\r\n\t}') pub_get,
39    concat( 'public void ', ' set' , FieldName,'( ',ColumnType,' value){\r\n\t\t _', FieldName,' = value;\r\n\t}') pub_set
40    FROM(
41    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) AS FieldName, 
42    case DATA_TYPE 
43            when 'bigint' then 'long'
44            when 'binary' then 'byte[]'
45            when 'bit' then 'bool'
46            when 'char' then 'String'
47            when 'date' then 'Date'
48            when 'datetime' then 'DateTime'
49            when 'datetime2' then 'DateTime'
50            when 'decimal' then 'decimal'
51            when 'float' then 'float'
52            when 'image' then 'byte[]'
53            when 'int' then 'int'
54            when 'money' then 'decimal'
55            when 'nchar' then 'String'
56            when 'ntext' then 'String'
57            when 'numeric' then 'decimal'
58            when 'nvarchar' then 'String'
59            when 'real' then 'double'
60            when 'smalldatetime' then 'Date'
61            when 'smallint' then 'short'
62            when 'mediumint' then 'int'
63            when 'smallmoney' then 'decimal'
64            when 'text' then 'String'
65            when 'time' then 'Date'
66            when 'timestamp' then 'Date'
67            when 'tinyint' then 'byte'
68            when 'uniqueidentifier' then 'String'
69            when 'varbinary' then 'byte[]'
70            when 'varchar' then 'String'
71            when 'year' THEN 'int'
72            else 'UNKNOWN_' + DATA_TYPE
73        end ColumnType
74    FROM(
75    select CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
76    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2, DATA_TYPE
77    from
78    (SELECT SUBSTRING_INDEX(COLUMN_NAME, '_', -1) as ColumnName2,
79    SUBSTRING_INDEX(COLUMN_NAME, '_', 1) as ColumnName1,
80    DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_name = pTableName) A) B)C);
81 
82    set vClassGetSet = '';
83    set vClassPrivate = '';
84    /* concat all properties*/
85    OPEN code_cursor;
86  
87            get_code: LOOP
88          
89                FETCH code_cursor INTO v_codeChunk_pri_var, v_codeChunk_pub_get,  v_codeChunk_pub_set;
90          
91                IF v_finished = 1 THEN
92                    LEAVE get_code;
93                END IF;
94                 
95                -- build code
96                select  CONCAT('\t',vClassPrivate,'\r\n', v_codeChunk_pri_var) into vClassPrivate;
97                select  CONCAT('\t',vClassGetSet,'\r\n\t', v_codeChunk_pub_get,'\r\n\t', v_codeChunk_pub_set) into  vClassGetSet ;
98          
99            END LOOP get_code;
100      
101        CLOSE code_cursor;
102 
103drop table temp1;
104/*make class*/
105select concat('public class ',vClassName,'\r\n{',vClassPrivate,'\r\n', vClassGetSet,'\r\n}');
106END