CREATE  PROCEDURE `proc_generate_api_bean`(IN in_table varchar(1000))
BEGIN
select CONCAT('@Entity\n','@Table(name=\"',in_table,'\")\n','public class ',func_humpString(in_table,'_'),'{','\n') from dual
union
select DISTINCT
                concat(
                '\t/** ',c.COLUMN_COMMENT,'*/ \n',
        if (c.ORDINAL_POSITION = 1,'\t@Id \n\t@GeneratedValue\n',''),
                if (c.DATA_TYPE = 'DATETIME','\t@Convert(converter = LocalDateTimeConverter.class) \n',''),
                '\t@Column(name =\"',c.COLUMN_NAME,'\") \n',
                '\tprivate ' ,(
                    case 
                            when c.DATA_TYPE = 'LONGTEXT' then 
                                    'String '
                            when c.DATA_TYPE = 'VARCHAR' then               
                                   'String '
              when c.DATA_TYPE = 'CHAR' then   
                                        'String '
              when c.DATA_TYPE = 'DATE' then    
                     'Date '
                            when c.DATA_TYPE = 'NUMBER' then
                     'Integer '
              when c.DATA_TYPE = 'BIGINT' then
                     'Long '       
                            when c.DATA_TYPE = 'mediumtext' then 
                                         'String '
              when c.DATA_TYPE = 'INT' then
                     'Long '       
                            when c.DATA_TYPE = 'DATETIME' then
                                         'LocalDateTime '
                            when c.DATA_TYPE = 'timestamp' then
                                         'LocalDateTime '
                            when c.DATA_TYPE = 'DECIMAL' then
                                         'BigDecimal '
                            when c.DATA_TYPE = 'ENUM' then
                                         'String '
                            when c.DATA_TYPE = 'TEXT' then
                                         'String '
                            when c.DATA_TYPE = 'MEDIUMINT' then 
                                         'Integer '
                            when c.DATA_TYPE = 'tinyint' then 
                                         'Integer '
                            when c.DATA_TYPE = 'FLOAT' then 
                                         'Float '
              end), func_humpString(c.COLUMN_NAME,'_'),'; \n') as '屬性信息'  from information_schema.columns c
where c.TABLE_NAME = in_table
union 
select '}' from dual;

CREATE  FUNCTION `func_split`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN 

        # 拆分傳入的字符串酌畜，返回拆分后的新字符串 
        declare result varchar(255) default ''; 
        set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); 

        return result; 

END

CREATE  FUNCTION `func_split_TotalLength`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
BEGIN 

    # 計算傳入字符串的總length 

    return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); 

END

CREATE  FUNCTION `func_humpString`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS varchar(255) CHARSET utf8
BEGIN
    declare cnt int default 0;
  declare i int default 1; 
  declare result varchar(255) default '';
  declare all_s varchar(255) default '';

    set result = func_split(f_string,f_delimiter,i);
  set cnt = func_split_TotalLength(f_string,f_delimiter);
  while i < cnt
    do 
    set i = i + 1;
    set all_s = func_split(f_string,f_delimiter,i);
        set result = concat(result,upper(left(all_s,1)),substring(all_s,2));
  end while;
    return result;
END