mysql读取表字段生成对应的java变量

#读取表字段生成对应的java变量,无初始值

例:String column ;// 注释

SELECT 

CONCAT('private ',(CASE 

WHEN (DATA_TYPE='CHAR'||DATA_TYPE='VARCHAR'||DATA_TYPE='LONGVARCHAR') THEN 'String' 

WHEN (DATA_TYPE='NUMERIC'||DATA_TYPE='DECIMAL') THEN 'BigDecimal'

WHEN DATA_TYPE='BIT' THEN 'boolean'

WHEN (DATA_TYPE='TINYINT'||DATA_TYPE='SMALLINT'||DATA_TYPE='INTEGER'||DATA_TYPE='INT') THEN 'int'

WHEN DATA_TYPE='BIGINT' THEN 'long'

WHEN DATA_TYPE='REAL' THEN 'float'

WHEN DATA_TYPE='DOUBLE' THEN 'double'

WHEN DATA_TYPE='DATE' THEN 'Date'

WHEN (DATA_TYPE='DATE'||DATA_TYPE='TIME'||DATA_TYPE='datetime'||DATA_TYPE='TIMESTAMP') THEN 'Date'

ELSE '-------------'

END),' ',column_name,';// ',column_comment)

FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND TABLE_NAME='yy_dayreport' 

 

#读取表字段生成对应的java变量,有初始值

例:String column = "";// 注释

SELECT 

CONCAT('private ',(CASE 

WHEN (DATA_TYPE='CHAR'||DATA_TYPE='VARCHAR'||DATA_TYPE='LONGVARCHAR') THEN 'String' 

WHEN (DATA_TYPE='NUMERIC'||DATA_TYPE='DECIMAL') THEN 'BigDecimal'

WHEN DATA_TYPE='BIT' THEN 'boolean'

WHEN (DATA_TYPE='TINYINT'||DATA_TYPE='SMALLINT'||DATA_TYPE='INTEGER'||DATA_TYPE='INT') THEN 'int'

WHEN DATA_TYPE='BIGINT' THEN 'long'

WHEN DATA_TYPE='REAL' THEN 'float'

WHEN DATA_TYPE='DOUBLE' THEN 'double'

WHEN DATA_TYPE='DATE' THEN 'Date'

WHEN (DATA_TYPE='DATE'||DATA_TYPE='TIME'||DATA_TYPE='datetime'||DATA_TYPE='TIMESTAMP') THEN 'Date'

ELSE '-------------'

END),' ',column_name,' = ',(CASE 

WHEN (DATA_TYPE='CHAR'||DATA_TYPE='VARCHAR'||DATA_TYPE='LONGVARCHAR') THEN '""' 

WHEN (DATA_TYPE='NUMERIC'||DATA_TYPE='DECIMAL') THEN '0'

WHEN DATA_TYPE='BIT' THEN 'false'

WHEN (DATA_TYPE='TINYINT'||DATA_TYPE='SMALLINT'||DATA_TYPE='INTEGER'||DATA_TYPE='INT') THEN '0'

WHEN DATA_TYPE='BIGINT' THEN '0L'

WHEN DATA_TYPE='REAL' THEN '0f'

WHEN DATA_TYPE='DOUBLE' THEN '0.0'

WHEN DATA_TYPE='DATE' THEN 'null'

WHEN (DATA_TYPE='DATE'||DATA_TYPE='TIME'||DATA_TYPE='datetime'||DATA_TYPE='TIMESTAMP') THEN 'null'

ELSE '-------------'

END),';// ',column_comment)

FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND TABLE_NAME='yy_dayreport' 

 

#生成添加sql语句

#例:insert into (id,channelCode)values(#{id},#{channelCode})

SELECT 

CONCAT('insert into (',group_concat(column_name),') values (',group_concat(CONCAT('#{',column_name,'}')),')')

FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND TABLE_NAME='yy_dayreport' 

 

#生成对应的set语句:

#例:dayReportVo.setId(id);

SELECT 

CONCAT('dayReportVo.set',UPPER(LEFT(column_name,1)),right(column_name,LENGTH(column_name)-1),'(',column_name,');// ',column_comment)

FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND TABLE_NAME='yy_dayreport' 

 

 

#输出编辑窗格需要的列:

#例:

/*<div class="fitem">

<label>主键ID:</label>

<input name="id" class="easyui-textbox">

</div>*/

SELECT CONCAT('<div class="fitem">\n\t<label>',column_comment,':</label>\n\t<input name="',column_name,'" class="easyui-textbox">\n</div>') FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND table_name='yy_dayreport'

 

#输出easyui的列:

#例:{ field:'xzyh',sortable:true,title:'新增用户',width:fixWidth(0.2),resizable:true},

SELECT CONCAT('{ field:\'',column_name,'\',sortable:true,title:\'',column_comment,'\',width:fixWidth(0.2),resizable:true},') FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND table_name='yy_dayreport'

 

#输出mysql的集合列:

#例:<result column="id" jdbcType="BIGINT" property="id" />

SELECT CONCAT('<result column="',column_name,'" jdbcType="',upper(CASE 

WHEN (DATA_TYPE='CHAR'||DATA_TYPE='VARCHAR'||DATA_TYPE='LONGVARCHAR'||DATA_TYPE='BIT'||DATA_TYPE='INTEGER'||DATA_TYPE='BIGINT'||DATA_TYPE='DOUBLE') THEN DATA_TYPE

WHEN (DATA_TYPE='NUMERIC'||DATA_TYPE='DECIMAL'||DATA_TYPE='INT'||DATA_TYPE='TINYINT'||DATA_TYPE='SMALLINT') THEN 'INTEGER'

WHEN DATA_TYPE='REAL' THEN 'FLOAT'

WHEN (DATA_TYPE='DATE'||DATA_TYPE='TIME'||DATA_TYPE='datetime'||DATA_TYPE='TIMESTAMP') THEN 'DATE'

ELSE '-------------'

END),'" property="',column_name,'" />') FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND table_name='yy_monthreport';

 

 

#输出表中所有字段:

#例:id,sendUserId,receiveUserId,itemId,itemName,itemNum,crateTime

SELECT GROUP_CONCAT(column_name) FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND table_name='t_send_log';

#输出表中所有#{字段}:

#例:#{id},#{sendUserId},#{receiveUserId},#{itemId},#{itemName},#{itemNum},#{crateTime}

SELECT GROUP_CONCAT(CONCAT('#{',column_name,'}')) FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='fish' AND table_name='t_send_log';