1.场景描述:
使用mybatis生成mapper.xml文件的时候经常遇到数据库表字段很多,写mapper.xml文件时转化成java实体很麻烦,写增删改查的sql时也要做
很多重复的工作。程序员就想一劳永逸,我拼接了几个sql可以简化,方便生成数据库映射和java实体。
2.解决:
先来一个表结构(如果字段很多,要是一个一个写,早烦啦烦啦,死啦死啦,怎么破?)
CREATE TABLE `t_ltl_order` (
`id` varchar(100) NOT NULL COMMENT 'id',
`shipper_id` varchar(30) DEFAULT NULL COMMENT '发货客户id',
`shipper_number` varchar(50) DEFAULT NULL COMMENT '发货客户编码',
`shipper_name` varchar(100) DEFAULT NULL COMMENT '发货客户名称'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='快递订单表';
2.1 mysql版本
先建立一个拼sql的函数(做了两件事,把下划线后面的字母大写,去除下划线)
CREATE
FUNCTION fun_yxl_rep(str varchar(128))
RETURNS varchar(128) CHARSET utf8
BEGIN
DECLARE len,
i int;
DECLARE ch char;
DECLARE s1,s2 varchar(128);
SET i = 1;
SELECT
LENGTH(str) INTO len;
WHILE (i <= len) DO
SET ch = SUBSTRING(str, i, 1);
IF (ch = '_' and (i+1) <= len) THEN
set s1 = SUBSTRING(str, 1, i);
set s2 = SUBSTRING(str, i+2, len - i);
SET str = CONCAT(s1,UPPER(SUBSTRING(str, i+1, 1)),s2);
END IF;
SET i = i + 1;
END WHILE;
RETURN REPLACE(str,'_','');
END
2.2使用
2.2.1.生成表的列升序排列(注意修改表名和schema就可以用了)
select CONCAT('t.',COLUMN_NAME,',') from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
t.shipper_id,
t.shipper_name,
t.shipper_number,
2.2.2生成java实体 及其注释
select CONCAT('// ',COLUMN_COMMENT,CHAR(13),' private String ',
fun_yxl_rep(COLUMN_NAME),';') from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
// 发货客户id
private String shipperId;
// 发货客户名称
private String shipperName;
// 发货客户编码
private String shipperNumber;
2.2.3生成resultMap
select CONCAT('<result property="',fun_yxl_rep(COLUMN_NAME),'" column="',
COLUMN_NAME,'"/>') from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
<result property="shipperId" column="shipper_id"/>
<result property="shipperName" column="shipper_name"/>
<result property="shipperNumber" column="shipper_number"/>
2.2.4生成--where if 判空
select CONCAT('<if test ="',fun_yxl_rep(COLUMN_NAME),' != null and ',fun_yxl_rep(COLUMN_NAME),
' != \'\' ">',CHAR(13),CHAR(09),'and t.',COLUMN_NAME,' = #{',fun_yxl_rep(COLUMN_NAME),'}',CHAR(13),'</if>')
from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms' order by COLUMN_NAME;
结果:
<if test ="shipperId != null and shipperId != '' ">
and t.shipper_id = #{shipperId}
</if>
<if test ="shipperName != null and shipperName != '' ">
and t.shipper_name = #{shipperName}
</if>
<if test ="shipperNumber != null and shipperNumber != '' ">
and t.shipper_number = #{shipperNumber}
</if>
2.2.5生成extjs model
select CONCAT('{name : \'',fun_yxl_rep(COLUMN_NAME),'\' // ',
COLUMN_COMMENT,CHAR(13),'},') from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms' order by COLUMN_NAME;
结果:
{name : 'shipperId' // 发货客户id
},
{name : 'shipperName' // 发货客户名称
},
{name : 'shipperNumber' // 发货客户编码
}
2.2.6 update 修改
select CONCAT('<if test ="',fun_yxl_rep(COLUMN_NAME),' != null">','t.',COLUMN_NAME,' = #{',fun_yxl_rep(COLUMN_NAME),'}',',</if>')
from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
<if test ="shipperId != null">t.shipper_id = #{shipperId},</if>
<if test ="shipperName != null">t.shipper_name = #{shipperName},</if>
<if test ="shipperNumber != null">t.shipper_number = #{shipperNumber},</if>
2.2.7insert插入 判空
插入语句模板<insert id="insertLocalWorkOrder">
insert into t_ltl_order
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="workOrderNo != null">WORK_ORDER_NO,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="workOrderNo != null">#{workOrderNo},</if>
</trim>
</insert>
select CONCAT('<if test ="',fun_yxl_rep(COLUMN_NAME),' != null">',
COLUMN_NAME,',','</if>')
from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
<if test ="shipperId != null">shipper_id,</if>
<if test ="shipperName != null">shipper_name,</if>
<if test ="shipperNumber != null">shipper_number,</if>
insert插入 判空values
select CONCAT('<if test ="',fun_yxl_rep(COLUMN_NAME),' != null">',
'#{',fun_yxl_rep(COLUMN_NAME),'},','</if>')
from information_schema.COLUMNS where
table_name = 't_ltl_order' and table_schema = 'oms'order by COLUMN_NAME;
结果:
<if test ="shipperId != null">#{shipperId},</if>
<if test ="shipperName != null">#{shipperName},</if>
<if test ="shipperNumber != null">#{shipperNumber},</if>
2.3oracle版本
2.3.1函数:
create or replace function fun_yxl_rep(str varchar2) return varchar2 is
lng int;
i int;
ch char;
s1 varchar2(128);
s2 varchar2(128);
rst varchar2(128);
begin
i := 1;
rst := str;
select length(rst) into lng from dual;
while (i <= lng) loop
ch := substr(rst, i, 1);
if (ch = '_' and (i + 1) <= lng) then
s1 := substr(rst, 1, i);
s2 := substr(rst, i + 2, lng - i);
rst := s1 || upper(substr(rst, i + 1, 1)) || s2;
end if;
i := i + 1;
end loop;
return replace(rst,'_','');
end fun_yxl_rep;
2.3.2用到的基本表
select c.table_name as 表名,
c.column_name as 列名,
c.data_type as 数据类型,
c.data_length as 长度,
c.nullable as 是否为空,
c.column_id as 列序号,
m.comments as 备注
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
2.3.3列columns
select 'T.' || column_name ||','
from user_tab_cols c
where c.table_name = 'T_AUTH_FUNCTION'
order by column_name;
2.3.4java实体 属性
select '// ' || m.comments || chr(13) || ' private String ' ||
fun_yxl_rep(lower(c.column_name)) || ';'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
2.3.5mapper resultMap
select '<result property="' || fun_yxl_rep(lower(c.column_name)) ||
'" column="' || c.column_name || '"/>'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
2.3.6if 判空
select '<if test = "' || fun_yxl_rep(lower(c.column_name)) ||
' != null and ' || fun_yxl_rep(lower(c.column_name)) ||
' != '''' "> ' || chr(13) || chr(09) || 'and t.' ||
lower(c.column_name) || ' = #{' || fun_yxl_rep(lower(c.column_name)) || '}' ||
chr(13) || '</if>'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
2.3.7extjsjs model
select '{name : ''' || fun_yxl_rep(lower(c.column_name)) || ''' // ' ||
m.comments || chr(13) || '},'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
2.3.8update 修改
select '<if test = "' || fun_yxl_rep(lower(c.column_name)) || ' != null ">' || 't.' ||
lower(c.column_name) || ' = #{' || fun_yxl_rep(lower(c.column_name)) || '}' ||
',</if>'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
2.3.9insert插入 判空
select '<if test = "' || fun_yxl_rep(lower(c.column_name)) || ' != null">' ||
lower(c.column_name) ||','|| '</if>'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;
-- insert插入 判空values
select '<if test ="' || fun_yxl_rep(lower(c.column_name)) || ' != null">' || '#{' ||
fun_yxl_rep(lower(c.column_name)) || '},' || '</if>'
from user_tab_cols c
inner join user_col_comments m
on m.table_name = c.table_name
and m.column_name = c.column_name
where c.table_name = 'T_AUTH_FUNCTION'
order by c.column_name;