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

来源：互联网发布：plsql导出单个表数据编辑：程序博客网时间：2024/05/21 00:17

1.问题描述：使用mybatis生成mapper.xml文件的时候经常遇到数据库表字段很多，写mapper.xml文件时转化成java实体很麻烦，写增删改查的sql时也要做

很多重复的工作。程序员就想一劳永逸，我拼接了几个sql可以简化，方便生成数据库映射和java实体。（待解决问题，没有JDBCTYPE，需要再建立一个映射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 '发货客户名称',
`contact_name` varchar(500) DEFAULT NULL COMMENT '发货联系人名称',
`contact_mobile` varchar(200) DEFAULT NULL COMMENT '手机',
`contact_phone` varchar(500) DEFAULT NULL COMMENT '电话',
`contact_province` varchar(200) DEFAULT NULL COMMENT '省份',
`contact_city` varchar(500) DEFAULT NULL COMMENT '城市',
`contact_area` varchar(100) DEFAULT NULL COMMENT '区县',
`contact_address` varchar(255) DEFAULT NULL COMMENT '详细地址',
`isreceivegood` varchar(1) DEFAULT NULL COMMENT '是否接货',
`beginaccep_time` datetime DEFAULT NULL COMMENT '接货起始时间',
`endaccp_time` datetime DEFAULT NULL COMMENT '接货结束时间',
`receive_id` varchar(50) DEFAULT NULL COMMENT '收货客户id',
`receive_number` varchar(50) DEFAULT NULL COMMENT '收货客户编码',
`receive_custname` varchar(100) DEFAULT NULL,
`issendms` varchar(1) DEFAULT NULL COMMENT '是否短信通知',
`receive_name` varchar(255) DEFAULT NULL COMMENT '收货联系人姓名',
`receive_mobile` varchar(200) DEFAULT NULL COMMENT '收货联系人手机',
`receive_phone` varchar(500) DEFAULT NULL COMMENT '收货联系人电话',
`receive_province` varchar(200) DEFAULT NULL COMMENT '收货联系人省份',
`receive_city` varchar(500) DEFAULT NULL COMMENT '收货联系人城市',
`receive_area` varchar(50) DEFAULT NULL COMMENT '收货联系人区县',
`receive_address` varchar(255) DEFAULT NULL COMMENT '收货联系人详细地址',
`transport_mode` varchar(30) DEFAULT NULL COMMENT '运输方式',
`goods_name` varchar(255) DEFAULT NULL COMMENT '货物名称',
`packing` varchar(500) DEFAULT NULL COMMENT '包装材料',
`goods_type` varchar(50) DEFAULT NULL COMMENT '货物类型',
`total_piece` int(11) DEFAULT NULL COMMENT '托运货物总件数',
`total_weight` double(28,10) DEFAULT NULL COMMENT '托运货物总重量',
`total_volume` double(28,10) DEFAULT NULL COMMENT '托运货物总体积',
`payment_type` varchar(30) DEFAULT NULL COMMENT '付款方式',
`delivery_mode` varchar(50) DEFAULT NULL COMMENT '提货方式',
`dshk_type` varchar(30) DEFAULT NULL COMMENT '代收货款类型',
`dshk` double(28,10) DEFAULT NULL COMMENT '代收货款',
`bjsmjz` double(28,10) DEFAULT NULL COMMENT '保价声明价值',
`order_time` datetime DEFAULT NULL COMMENT '下单时间',
`departure_id` varchar(30) DEFAULT NULL COMMENT '始发网点id',
`accept_deptid` varchar(30) DEFAULT NULL COMMENT '受理部门id',
`order_status` varchar(50) DEFAULT NULL COMMENT '订单状态',
`accepter` varchar(500) DEFAULT NULL COMMENT '受理人',
`ordercreate_time` datetime DEFAULT NULL COMMENT '订单创建时间',
`accept_time` datetime DEFAULT NULL COMMENT '订单受理时间',
`waybill_number` varchar(500) DEFAULT NULL COMMENT '运单号',
`resource` varchar(50) DEFAULT NULL COMMENT '订单来源',
`hasten_count` int(11) DEFAULT '0' COMMENT '催单次数',
`order_number` varchar(50) NOT NULL COMMENT '订单号',
`channel_number` varchar(500) DEFAULT NULL COMMENT '渠道的单号',
`returnbill_type` varchar(30) DEFAULT NULL COMMENT '签收单方式',
`trans_note` varchar(1000) DEFAULT NULL COMMENT '储运事项',
`receivingto_pointid` varchar(30) DEFAULT NULL COMMENT '到达网点id',
`order_person` varchar(100) DEFAULT NULL COMMENT '下单人',
`depart_linkmannum` varchar(50) DEFAULT NULL COMMENT '发货人联系人编码',
`order_type` varchar(50) DEFAULT NULL COMMENT '订单类型：数据字典数据',
`arrive_linkmannum` varchar(50) DEFAULT NULL COMMENT '收货人联系人编码',
`remark` varchar(1000) DEFAULT NULL COMMENT '备注（网点名称、电话，快递员手机、电话）',
`refund_mode` varchar(30) DEFAULT NULL COMMENT '退款方式',
`create_usernum` varchar(65) DEFAULT NULL COMMENT '制单员工工号',
`accepter_name` varchar(100) DEFAULT NULL COMMENT '接货员姓名',
`accepter_mobile` varchar(30) DEFAULT NULL COMMENT '接货员联系方式',
`feedback_info` varchar(2000) DEFAULT NULL COMMENT '反馈信息',
`member_type` varchar(255) DEFAULT NULL COMMENT '阿里巴巴会员类型',
`towait_accept_time` datetime DEFAULT NULL COMMENT '最后一次待受理时间',
`delayorder_time` datetime DEFAULT NULL COMMENT '延迟时间',
`contact_comments` varchar(1000) DEFAULT NULL COMMENT '发货方地址备注',
`receive_comments` varchar(1000) DEFAULT NULL COMMENT '收货方地址备注',
`procurement_number` varchar(50) DEFAULT NULL COMMENT '采购单号',
`cust_group` varchar(100) DEFAULT NULL COMMENT '客户分群',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='快递订单表';

2.1 mysql版本(后面有oracle版本）

先建立一个拼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.accepter,
t.accepter_mobile,
t.accepter_name,
t.accept_deptid,
t.accept_time,
t.arrive_linkmannum,
t.beginaccep_time,
t.bjsmjz,
t.channel_number,
t.contact_address,
t.contact_area,
t.contact_city,
t.contact_comments,
t.contact_mobile,
t.contact_name,
t.contact_phone,
t.contact_province,
t.create_usernum,
t.cust_group,
t.delayorder_time,
t.delivery_mode,
t.departure_id,
t.depart_linkmannum,
t.dshk,
t.dshk_type,
t.endaccp_time,
t.feedback_info,
t.goods_name,
t.goods_type,
t.hasten_count,
t.id,
t.isreceivegood,
t.issendms,
t.member_type,
t.ordercreate_time,
t.order_number,
t.order_person,
t.order_status,
t.order_time,
t.order_type,
t.packing,
t.payment_type,
t.procurement_number,
t.receive_address,
t.receive_area,
t.receive_city,
t.receive_comments,
t.receive_custname,
t.receive_id,
t.receive_mobile,
t.receive_name,
t.receive_number,
t.receive_phone,
t.receive_province,
t.receivingto_pointid,
t.refund_mode,
t.remark,
t.resource,
t.returnbill_type,
t.shipper_id,
t.shipper_name,
t.shipper_number,
t.total_piece,
t.total_volume,
t.total_weight,
t.towait_accept_time,
t.transport_mode,
t.trans_note,
t.waybill_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;

结果：

// 受理人
private String accepter;
// 接货员联系方式
private String accepterMobile;
// 接货员姓名
private String accepterName;
// 受理部门id
private String acceptDeptid;
// 订单受理时间
private String acceptTime;
// 收货人联系人编码
private String arriveLinkmannum;
// 接货起始时间
private String beginaccepTime;
// 保价声明价值
private String bjsmjz;
// 渠道的单号
private String channelNumber;
// 详细地址
private String contactAddress;
// 区县
private String contactArea;
// 城市
private String contactCity;
// 发货方地址备注
private String contactComments;
// 手机
private String contactMobile;
// 发货联系人名称
private String contactName;
// 电话
private String contactPhone;
// 省份
private String contactProvince;
// 制单员工工号
private String createUsernum;
// 客户分群
private String custGroup;
// 延迟时间
private String delayorderTime;
// 提货方式
private String deliveryMode;
// 始发网点id
private String departureId;
// 发货人联系人编码
private String departLinkmannum;
// 代收货款
private String dshk;
// 代收货款类型
private String dshkType;
// 接货结束时间
private String endaccpTime;
// 反馈信息
private String feedbackInfo;
// 货物名称
private String goodsName;
// 货物类型
private String goodsType;
// 催单次数
private String hastenCount;
// id
private String id;
// 是否接货
private String isreceivegood;
// 是否短信通知
private String issendms;
// 阿里巴巴会员类型
private String memberType;
// 订单创建时间
private String ordercreateTime;
// 订单号
private String orderNumber;
// 下单人
private String orderPerson;
// 订单状态
private String orderStatus;
// 下单时间
private String orderTime;
// 订单类型：数据字典数据
private String orderType;
// 包装材料
private String packing;
// 付款方式
private String paymentType;
// 采购单号
private String procurementNumber;
// 收货联系人详细地址
private String receiveAddress;
// 收货联系人区县
private String receiveArea;
// 收货联系人城市
private String receiveCity;
// 收货方地址备注
private String receiveComments;
//
private String receiveCustname;
// 收货客户id
private String receiveId;
// 收货联系人手机
private String receiveMobile;
// 收货联系人姓名
private String receiveName;
// 收货客户编码
private String receiveNumber;
// 收货联系人电话
private String receivePhone;
// 收货联系人省份
private String receiveProvince;
// 到达网点id
private String receivingtoPointid;
// 退款方式
private String refundMode;
// 备注（网点名称、电话，快递员手机、电话）
private String remark;
// 订单来源
private String resource;
// 签收单方式
private String returnbillType;
// 发货客户id
private String shipperId;
// 发货客户名称
private String shipperName;
// 发货客户编码
private String shipperNumber;
// 托运货物总件数
private String totalPiece;
// 托运货物总体积
private String totalVolume;
// 托运货物总重量
private String totalWeight;
// 最后一次待受理时间
private String towaitAcceptTime;
// 运输方式
private String transportMode;
// 储运事项
private String transNote;
// 运单号
private String waybillNumber;

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;

结果：

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 ="accepter != null and accepter != '' ">
and t.accepter = #{accepter}
</if>
<if test ="accepterMobile != null and accepterMobile != '' ">
and t.accepter_mobile = #{accepterMobile}
</if>
<if test ="accepterName != null and accepterName != '' ">
and t.accepter_name = #{accepterName}
</if>
<if test ="acceptDeptid != null and acceptDeptid != '' ">
and t.accept_deptid = #{acceptDeptid}
</if>
<if test ="acceptTime != null and acceptTime != '' ">
and t.accept_time = #{acceptTime}
</if>
<if test ="arriveLinkmannum != null and arriveLinkmannum != '' ">
and t.arrive_linkmannum = #{arriveLinkmannum}
</if>
<if test ="beginaccepTime != null and beginaccepTime != '' ">
and t.beginaccep_time = #{beginaccepTime}
</if>
<if test ="bjsmjz != null and bjsmjz != '' ">
and t.bjsmjz = #{bjsmjz}
</if>
<if test ="channelNumber != null and channelNumber != '' ">
and t.channel_number = #{channelNumber}
</if>
<if test ="contactAddress != null and contactAddress != '' ">
and t.contact_address = #{contactAddress}
</if>
<if test ="contactArea != null and contactArea != '' ">
and t.contact_area = #{contactArea}
</if>
<if test ="contactCity != null and contactCity != '' ">
and t.contact_city = #{contactCity}
</if>
<if test ="contactComments != null and contactComments != '' ">
and t.contact_comments = #{contactComments}
</if>
<if test ="contactMobile != null and contactMobile != '' ">
and t.contact_mobile = #{contactMobile}
</if>
<if test ="contactName != null and contactName != '' ">
and t.contact_name = #{contactName}
</if>
<if test ="contactPhone != null and contactPhone != '' ">
and t.contact_phone = #{contactPhone}
</if>
<if test ="contactProvince != null and contactProvince != '' ">
and t.contact_province = #{contactProvince}
</if>
<if test ="createUsernum != null and createUsernum != '' ">
and t.create_usernum = #{createUsernum}
</if>
<if test ="custGroup != null and custGroup != '' ">
and t.cust_group = #{custGroup}
</if>
<if test ="delayorderTime != null and delayorderTime != '' ">
and t.delayorder_time = #{delayorderTime}
</if>
<if test ="deliveryMode != null and deliveryMode != '' ">
and t.delivery_mode = #{deliveryMode}
</if>
<if test ="departureId != null and departureId != '' ">
and t.departure_id = #{departureId}
</if>
<if test ="departLinkmannum != null and departLinkmannum != '' ">
and t.depart_linkmannum = #{departLinkmannum}
</if>
<if test ="dshk != null and dshk != '' ">
and t.dshk = #{dshk}
</if>
<if test ="dshkType != null and dshkType != '' ">
and t.dshk_type = #{dshkType}
</if>
<if test ="endaccpTime != null and endaccpTime != '' ">
and t.endaccp_time = #{endaccpTime}
</if>
<if test ="feedbackInfo != null and feedbackInfo != '' ">
and t.feedback_info = #{feedbackInfo}
</if>
<if test ="goodsName != null and goodsName != '' ">
and t.goods_name = #{goodsName}
</if>
<if test ="goodsType != null and goodsType != '' ">
and t.goods_type = #{goodsType}
</if>
<if test ="hastenCount != null and hastenCount != '' ">
and t.hasten_count = #{hastenCount}
</if>
<if test ="id != null and id != '' ">
and t.id = #{id}
</if>
<if test ="isreceivegood != null and isreceivegood != '' ">
and t.isreceivegood = #{isreceivegood}
</if>
<if test ="issendms != null and issendms != '' ">
and t.issendms = #{issendms}
</if>
<if test ="memberType != null and memberType != '' ">
and t.member_type = #{memberType}
</if>
<if test ="ordercreateTime != null and ordercreateTime != '' ">
and t.ordercreate_time = #{ordercreateTime}
</if>
<if test ="orderNumber != null and orderNumber != '' ">
and t.order_number = #{orderNumber}
</if>
<if test ="orderPerson != null and orderPerson != '' ">
and t.order_person = #{orderPerson}
</if>
<if test ="orderStatus != null and orderStatus != '' ">
and t.order_status = #{orderStatus}
</if>
<if test ="orderTime != null and orderTime != '' ">
and t.order_time = #{orderTime}
</if>
<if test ="orderType != null and orderType != '' ">
and t.order_type = #{orderType}
</if>
<if test ="packing != null and packing != '' ">
and t.packing = #{packing}
</if>
<if test ="paymentType != null and paymentType != '' ">
and t.payment_type = #{paymentType}
</if>
<if test ="procurementNumber != null and procurementNumber != '' ">
and t.procurement_number = #{procurementNumber}
</if>
<if test ="receiveAddress != null and receiveAddress != '' ">
and t.receive_address = #{receiveAddress}
</if>
<if test ="receiveArea != null and receiveArea != '' ">
and t.receive_area = #{receiveArea}
</if>
<if test ="receiveCity != null and receiveCity != '' ">
and t.receive_city = #{receiveCity}
</if>
<if test ="receiveComments != null and receiveComments != '' ">
and t.receive_comments = #{receiveComments}
</if>
<if test ="receiveCustname != null and receiveCustname != '' ">
and t.receive_custname = #{receiveCustname}
</if>
<if test ="receiveId != null and receiveId != '' ">
and t.receive_id = #{receiveId}
</if>
<if test ="receiveMobile != null and receiveMobile != '' ">
and t.receive_mobile = #{receiveMobile}
</if>
<if test ="receiveName != null and receiveName != '' ">
and t.receive_name = #{receiveName}
</if>
<if test ="receiveNumber != null and receiveNumber != '' ">
and t.receive_number = #{receiveNumber}
</if>
<if test ="receivePhone != null and receivePhone != '' ">
and t.receive_phone = #{receivePhone}
</if>
<if test ="receiveProvince != null and receiveProvince != '' ">
and t.receive_province = #{receiveProvince}
</if>
<if test ="receivingtoPointid != null and receivingtoPointid != '' ">
and t.receivingto_pointid = #{receivingtoPointid}
</if>
<if test ="refundMode != null and refundMode != '' ">
and t.refund_mode = #{refundMode}
</if>
<if test ="remark != null and remark != '' ">
and t.remark = #{remark}
</if>
<if test ="resource != null and resource != '' ">
and t.resource = #{resource}
</if>
<if test ="returnbillType != null and returnbillType != '' ">
and t.returnbill_type = #{returnbillType}
</if>
<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>
<if test ="totalPiece != null and totalPiece != '' ">
and t.total_piece = #{totalPiece}
</if>
<if test ="totalVolume != null and totalVolume != '' ">
and t.total_volume = #{totalVolume}
</if>
<if test ="totalWeight != null and totalWeight != '' ">
and t.total_weight = #{totalWeight}
</if>
<if test ="towaitAcceptTime != null and towaitAcceptTime != '' ">
and t.towait_accept_time = #{towaitAcceptTime}
</if>
<if test ="transportMode != null and transportMode != '' ">
and t.transport_mode = #{transportMode}
</if>
<if test ="transNote != null and transNote != '' ">
and t.trans_note = #{transNote}
</if>
<if test ="waybillNumber != null and waybillNumber != '' ">
and t.waybill_number = #{waybillNumber}
</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 : 'accepter' // 受理人
},
{name : 'accepterMobile' // 接货员联系方式
},
{name : 'accepterName' // 接货员姓名
},
{name : 'acceptDeptid' // 受理部门id
},
{name : 'acceptTime' // 订单受理时间
},
{name : 'arriveLinkmannum' // 收货人联系人编码
},
{name : 'beginaccepTime' // 接货起始时间
},
{name : 'bjsmjz' // 保价声明价值
},
{name : 'channelNumber' // 渠道的单号
},
{name : 'contactAddress' // 详细地址
},
{name : 'contactArea' // 区县
},
{name : 'contactCity' // 城市
},
{name : 'contactComments' // 发货方地址备注
},
{name : 'contactMobile' // 手机
},
{name : 'contactName' // 发货联系人名称
},
{name : 'contactPhone' // 电话
},
{name : 'contactProvince' // 省份
},
{name : 'createUsernum' // 制单员工工号
},
{name : 'custGroup' // 客户分群
},
{name : 'delayorderTime' // 延迟时间
},
{name : 'deliveryMode' // 提货方式
},
{name : 'departureId' // 始发网点id
},
{name : 'departLinkmannum' // 发货人联系人编码
},
{name : 'dshk' // 代收货款
},
{name : 'dshkType' // 代收货款类型
},
{name : 'endaccpTime' // 接货结束时间
},
{name : 'feedbackInfo' // 反馈信息
},
{name : 'goodsName' // 货物名称
},
{name : 'goodsType' // 货物类型
},
{name : 'hastenCount' // 催单次数
},
{name : 'id' // id
},
{name : 'isreceivegood' // 是否接货
},
{name : 'issendms' // 是否短信通知
},
{name : 'memberType' // 阿里巴巴会员类型
},
{name : 'ordercreateTime' // 订单创建时间
},
{name : 'orderNumber' // 订单号
},
{name : 'orderPerson' // 下单人
},
{name : 'orderStatus' // 订单状态
},
{name : 'orderTime' // 下单时间
},
{name : 'orderType' // 订单类型：数据字典数据
},
{name : 'packing' // 包装材料
},
{name : 'paymentType' // 付款方式
},
{name : 'procurementNumber' // 采购单号
},
{name : 'receiveAddress' // 收货联系人详细地址
},
{name : 'receiveArea' // 收货联系人区县
},
{name : 'receiveCity' // 收货联系人城市
},
{name : 'receiveComments' // 收货方地址备注
},
{name : 'receiveCustname' //
},
{name : 'receiveId' // 收货客户id
},
{name : 'receiveMobile' // 收货联系人手机
},
{name : 'receiveName' // 收货联系人姓名
},
{name : 'receiveNumber' // 收货客户编码
},
{name : 'receivePhone' // 收货联系人电话
},
{name : 'receiveProvince' // 收货联系人省份
},
{name : 'receivingtoPointid' // 到达网点id
},
{name : 'refundMode' // 退款方式
},
{name : 'remark' // 备注（网点名称、电话，快递员手机、电话）
},
{name : 'resource' // 订单来源
},
{name : 'returnbillType' // 签收单方式
},
{name : 'shipperId' // 发货客户id
},
{name : 'shipperName' // 发货客户名称
},
{name : 'shipperNumber' // 发货客户编码
},
{name : 'totalPiece' // 托运货物总件数
},
{name : 'totalVolume' // 托运货物总体积
},
{name : 'totalWeight' // 托运货物总重量
},
{name : 'towaitAcceptTime' // 最后一次待受理时间
},
{name : 'transportMode' // 运输方式
},
{name : 'transNote' // 储运事项
},
{name : 'waybillNumber' // 运单号
},

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 ="accepter != null">t.accepter = #{accepter},</if>
<if test ="accepterMobile != null">t.accepter_mobile = #{accepterMobile},</if>
<if test ="accepterName != null">t.accepter_name = #{accepterName},</if>
<if test ="acceptDeptid != null">t.accept_deptid = #{acceptDeptid},</if>
<if test ="acceptTime != null">t.accept_time = #{acceptTime},</if>
<if test ="arriveLinkmannum != null">t.arrive_linkmannum = #{arriveLinkmannum},</if>
<if test ="beginaccepTime != null">t.beginaccep_time = #{beginaccepTime},</if>
<if test ="bjsmjz != null">t.bjsmjz = #{bjsmjz},</if>
<if test ="channelNumber != null">t.channel_number = #{channelNumber},</if>
<if test ="contactAddress != null">t.contact_address = #{contactAddress},</if>
<if test ="contactArea != null">t.contact_area = #{contactArea},</if>
<if test ="contactCity != null">t.contact_city = #{contactCity},</if>
<if test ="contactComments != null">t.contact_comments = #{contactComments},</if>
<if test ="contactMobile != null">t.contact_mobile = #{contactMobile},</if>
<if test ="contactName != null">t.contact_name = #{contactName},</if>
<if test ="contactPhone != null">t.contact_phone = #{contactPhone},</if>
<if test ="contactProvince != null">t.contact_province = #{contactProvince},</if>
<if test ="createUsernum != null">t.create_usernum = #{createUsernum},</if>
<if test ="custGroup != null">t.cust_group = #{custGroup},</if>
<if test ="delayorderTime != null">t.delayorder_time = #{delayorderTime},</if>
<if test ="deliveryMode != null">t.delivery_mode = #{deliveryMode},</if>
<if test ="departureId != null">t.departure_id = #{departureId},</if>
<if test ="departLinkmannum != null">t.depart_linkmannum = #{departLinkmannum},</if>
<if test ="dshk != null">t.dshk = #{dshk},</if>
<if test ="dshkType != null">t.dshk_type = #{dshkType},</if>
<if test ="endaccpTime != null">t.endaccp_time = #{endaccpTime},</if>
<if test ="feedbackInfo != null">t.feedback_info = #{feedbackInfo},</if>
<if test ="goodsName != null">t.goods_name = #{goodsName},</if>
<if test ="goodsType != null">t.goods_type = #{goodsType},</if>
<if test ="hastenCount != null">t.hasten_count = #{hastenCount},</if>
<if test ="id != null">t.id = #{id},</if>
<if test ="isreceivegood != null">t.isreceivegood = #{isreceivegood},</if>
<if test ="issendms != null">t.issendms = #{issendms},</if>
<if test ="memberType != null">t.member_type = #{memberType},</if>
<if test ="ordercreateTime != null">t.ordercreate_time = #{ordercreateTime},</if>
<if test ="orderNumber != null">t.order_number = #{orderNumber},</if>
<if test ="orderPerson != null">t.order_person = #{orderPerson},</if>
<if test ="orderStatus != null">t.order_status = #{orderStatus},</if>
<if test ="orderTime != null">t.order_time = #{orderTime},</if>
<if test ="orderType != null">t.order_type = #{orderType},</if>
<if test ="packing != null">t.packing = #{packing},</if>
<if test ="paymentType != null">t.payment_type = #{paymentType},</if>
<if test ="procurementNumber != null">t.procurement_number = #{procurementNumber},</if>
<if test ="receiveAddress != null">t.receive_address = #{receiveAddress},</if>
<if test ="receiveArea != null">t.receive_area = #{receiveArea},</if>
<if test ="receiveCity != null">t.receive_city = #{receiveCity},</if>
<if test ="receiveComments != null">t.receive_comments = #{receiveComments},</if>
<if test ="receiveCustname != null">t.receive_custname = #{receiveCustname},</if>
<if test ="receiveId != null">t.receive_id = #{receiveId},</if>
<if test ="receiveMobile != null">t.receive_mobile = #{receiveMobile},</if>
<if test ="receiveName != null">t.receive_name = #{receiveName},</if>
<if test ="receiveNumber != null">t.receive_number = #{receiveNumber},</if>
<if test ="receivePhone != null">t.receive_phone = #{receivePhone},</if>
<if test ="receiveProvince != null">t.receive_province = #{receiveProvince},</if>
<if test ="receivingtoPointid != null">t.receivingto_pointid = #{receivingtoPointid},</if>
<if test ="refundMode != null">t.refund_mode = #{refundMode},</if>
<if test ="remark != null">t.remark = #{remark},</if>
<if test ="resource != null">t.resource = #{resource},</if>
<if test ="returnbillType != null">t.returnbill_type = #{returnbillType},</if>
<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>
<if test ="totalPiece != null">t.total_piece = #{totalPiece},</if>
<if test ="totalVolume != null">t.total_volume = #{totalVolume},</if>
<if test ="totalWeight != null">t.total_weight = #{totalWeight},</if>
<if test ="towaitAcceptTime != null">t.towait_accept_time = #{towaitAcceptTime},</if>
<if test ="transportMode != null">t.transport_mode = #{transportMode},</if>
<if test ="transNote != null">t.trans_note = #{transNote},</if>
<if test ="waybillNumber != null">t.waybill_number = #{waybillNumber},</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 ="accepter != null">accepter,</if>
<if test ="accepterMobile != null">accepter_mobile,</if>
<if test ="accepterName != null">accepter_name,</if>
<if test ="acceptDeptid != null">accept_deptid,</if>
<if test ="acceptTime != null">accept_time,</if>
<if test ="arriveLinkmannum != null">arrive_linkmannum,</if>
<if test ="beginaccepTime != null">beginaccep_time,</if>
<if test ="bjsmjz != null">bjsmjz,</if>
<if test ="channelNumber != null">channel_number,</if>
<if test ="contactAddress != null">contact_address,</if>
<if test ="contactArea != null">contact_area,</if>
<if test ="contactCity != null">contact_city,</if>
<if test ="contactComments != null">contact_comments,</if>
<if test ="contactMobile != null">contact_mobile,</if>
<if test ="contactName != null">contact_name,</if>
<if test ="contactPhone != null">contact_phone,</if>
<if test ="contactProvince != null">contact_province,</if>
<if test ="createUsernum != null">create_usernum,</if>
<if test ="custGroup != null">cust_group,</if>
<if test ="delayorderTime != null">delayorder_time,</if>
<if test ="deliveryMode != null">delivery_mode,</if>
<if test ="departureId != null">departure_id,</if>
<if test ="departLinkmannum != null">depart_linkmannum,</if>
<if test ="dshk != null">dshk,</if>
<if test ="dshkType != null">dshk_type,</if>
<if test ="endaccpTime != null">endaccp_time,</if>
<if test ="feedbackInfo != null">feedback_info,</if>
<if test ="goodsName != null">goods_name,</if>
<if test ="goodsType != null">goods_type,</if>
<if test ="hastenCount != null">hasten_count,</if>
<if test ="id != null">id,</if>
<if test ="isreceivegood != null">isreceivegood,</if>
<if test ="issendms != null">issendms,</if>
<if test ="memberType != null">member_type,</if>
<if test ="ordercreateTime != null">ordercreate_time,</if>
<if test ="orderNumber != null">order_number,</if>
<if test ="orderPerson != null">order_person,</if>
<if test ="orderStatus != null">order_status,</if>
<if test ="orderTime != null">order_time,</if>
<if test ="orderType != null">order_type,</if>
<if test ="packing != null">packing,</if>
<if test ="paymentType != null">payment_type,</if>
<if test ="procurementNumber != null">procurement_number,</if>
<if test ="receiveAddress != null">receive_address,</if>
<if test ="receiveArea != null">receive_area,</if>
<if test ="receiveCity != null">receive_city,</if>
<if test ="receiveComments != null">receive_comments,</if>
<if test ="receiveCustname != null">receive_custname,</if>
<if test ="receiveId != null">receive_id,</if>
<if test ="receiveMobile != null">receive_mobile,</if>
<if test ="receiveName != null">receive_name,</if>
<if test ="receiveNumber != null">receive_number,</if>
<if test ="receivePhone != null">receive_phone,</if>
<if test ="receiveProvince != null">receive_province,</if>
<if test ="receivingtoPointid != null">receivingto_pointid,</if>
<if test ="refundMode != null">refund_mode,</if>
<if test ="remark != null">remark,</if>
<if test ="resource != null">resource,</if>
<if test ="returnbillType != null">returnbill_type,</if>
<if test ="shipperId != null">shipper_id,</if>
<if test ="shipperName != null">shipper_name,</if>
<if test ="shipperNumber != null">shipper_number,</if>
<if test ="totalPiece != null">total_piece,</if>
<if test ="totalVolume != null">total_volume,</if>
<if test ="totalWeight != null">total_weight,</if>
<if test ="towaitAcceptTime != null">towait_accept_time,</if>
<if test ="transportMode != null">transport_mode,</if>
<if test ="transNote != null">trans_note,</if>
<if test ="waybillNumber != null">waybill_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 ="accepter != null">#{accepter},</if>
<if test ="accepterMobile != null">#{accepterMobile},</if>
<if test ="accepterName != null">#{accepterName},</if>
<if test ="acceptDeptid != null">#{acceptDeptid},</if>
<if test ="acceptTime != null">#{acceptTime},</if>
<if test ="arriveLinkmannum != null">#{arriveLinkmannum},</if>
<if test ="beginaccepTime != null">#{beginaccepTime},</if>
<if test ="bjsmjz != null">#{bjsmjz},</if>
<if test ="channelNumber != null">#{channelNumber},</if>
<if test ="contactAddress != null">#{contactAddress},</if>
<if test ="contactArea != null">#{contactArea},</if>
<if test ="contactCity != null">#{contactCity},</if>
<if test ="contactComments != null">#{contactComments},</if>
<if test ="contactMobile != null">#{contactMobile},</if>
<if test ="contactName != null">#{contactName},</if>
<if test ="contactPhone != null">#{contactPhone},</if>
<if test ="contactProvince != null">#{contactProvince},</if>
<if test ="createUsernum != null">#{createUsernum},</if>
<if test ="custGroup != null">#{custGroup},</if>
<if test ="delayorderTime != null">#{delayorderTime},</if>
<if test ="deliveryMode != null">#{deliveryMode},</if>
<if test ="departureId != null">#{departureId},</if>
<if test ="departLinkmannum != null">#{departLinkmannum},</if>
<if test ="dshk != null">#{dshk},</if>
<if test ="dshkType != null">#{dshkType},</if>
<if test ="endaccpTime != null">#{endaccpTime},</if>
<if test ="feedbackInfo != null">#{feedbackInfo},</if>
<if test ="goodsName != null">#{goodsName},</if>
<if test ="goodsType != null">#{goodsType},</if>
<if test ="hastenCount != null">#{hastenCount},</if>
<if test ="id != null">#{id},</if>
<if test ="isreceivegood != null">#{isreceivegood},</if>
<if test ="issendms != null">#{issendms},</if>
<if test ="memberType != null">#{memberType},</if>
<if test ="ordercreateTime != null">#{ordercreateTime},</if>
<if test ="orderNumber != null">#{orderNumber},</if>
<if test ="orderPerson != null">#{orderPerson},</if>
<if test ="orderStatus != null">#{orderStatus},</if>
<if test ="orderTime != null">#{orderTime},</if>
<if test ="orderType != null">#{orderType},</if>
<if test ="packing != null">#{packing},</if>
<if test ="paymentType != null">#{paymentType},</if>
<if test ="procurementNumber != null">#{procurementNumber},</if>
<if test ="receiveAddress != null">#{receiveAddress},</if>
<if test ="receiveArea != null">#{receiveArea},</if>
<if test ="receiveCity != null">#{receiveCity},</if>
<if test ="receiveComments != null">#{receiveComments},</if>
<if test ="receiveCustname != null">#{receiveCustname},</if>
<if test ="receiveId != null">#{receiveId},</if>
<if test ="receiveMobile != null">#{receiveMobile},</if>
<if test ="receiveName != null">#{receiveName},</if>
<if test ="receiveNumber != null">#{receiveNumber},</if>
<if test ="receivePhone != null">#{receivePhone},</if>
<if test ="receiveProvince != null">#{receiveProvince},</if>
<if test ="receivingtoPointid != null">#{receivingtoPointid},</if>
<if test ="refundMode != null">#{refundMode},</if>
<if test ="remark != null">#{remark},</if>
<if test ="resource != null">#{resource},</if>
<if test ="returnbillType != null">#{returnbillType},</if>
<if test ="shipperId != null">#{shipperId},</if>
<if test ="shipperName != null">#{shipperName},</if>
<if test ="shipperNumber != null">#{shipperNumber},</if>
<if test ="totalPiece != null">#{totalPiece},</if>
<if test ="totalVolume != null">#{totalVolume},</if>
<if test ="totalWeight != null">#{totalWeight},</if>
<if test ="towaitAcceptTime != null">#{towaitAcceptTime},</if>
<if test ="transportMode != null">#{transportMode},</if>
<if test ="transNote != null">#{transNote},</if>
<if test ="waybillNumber != null">#{waybillNumber},</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;

0 0