mybatis mapper.xml数据库映射生成java实体，增删改查sql语句

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;