此工具的优点
代码整洁美观
bad case
eda4716bba674fe1b19ce8f018e0ff32_image.png
good case
57af69eaae0c4d5fb490f8d7606ea2df_image.png
代码规范化
bad case
7ee7727c861b4182bfe17387beec4ec2_image.png
good case
e4bb723531ea418281c56433501d8dbb_image.png
bad case
628f87539fcb46219759e96a52144149_image.png
good case
a87c7c07bb174c4b889ff007d27ecfdc_image.png
更新/插入空字段过滤
bad case
09e477e4eb5643fdacd5988150f539b1_image.png
good case
5769ad423fe24203bbf0632faf5a0b35_image.png
命名规范化
1. 数据库实体对象以DO结尾
2. 数据库接口以Dao结尾
3. 数据库接口模板函数规范化
insert,
upsert,
selectByPrimaryKey,
updateByPrimaryKey,
deleteByPrimaryKey,
listByParams,
batchInsert,
batchUpdate,
batchUpsert
自动生成代码和人工添加方法隔离
表结构变更时, 重新生成DO, 自动生成的xml即可, 避免表结构变更带来繁琐的改动
f96873070b084f70950b7912817e2a9d_image.png
批量操作支持
bad case
1e366160c66c496f8fa45cd27de33b2b_image.png
b4d17452de3b4695856c366206db98d8_image.png
good case
416f750ba28c475ea21437dd28d0a2c9_image.png
批量插入
批量更新
批量插入或更新
f58dd06183554d2b91cf0e340fd2641f_image.png
7a2adb512c7045a0bee379f01dcf2614_image.png
实现细节
git仓库地址
目录结构
ca97e73e702149e7a8d436955c4aa848_image.png
数据库接口抽象基类
/**
* 基础类 MAPPER
*/
public interface IBaseDao {
/**
* 插入记录
* @param obj DO对象
* @return 成功数量
*/
int insert(T obj);
/**
* description: 插入数据, 主键重复则更新数据
*
* @author wyh
* @date 2019/10/9 11:41
* @param obj DO对象
* @return 成功数量
*/
int upsert(T obj);
/**
* 物理删除记录
* @param seq 主键
* @return 成功数量
*/
int deleteByPrimaryKey(@Param("id") K seq);
/**
* 更新记录(有效字段,即非空字段)
* @param obj DO对象
* @return 修改成功数量
*/
int updateByPrimaryKey(T obj);
/**
* 根据主键 返回记录
* @param seq 主键
* @return 查询数据
*/
T selectByPrimaryKey(@Param("id") K seq);
/**
* 根据 条件返回记录
* @param params 参数map
* @return 数据列表
*/
List listByParams(Map params);
/**
* 批量插入数据
*
* 缺陷: list中, 每个对象是否为空需要保持一致, 按照第一个对象中字段是否为空判断, 非空才进行插入
*
* @param list 插入列表
* @return 成功数量
*/
int batchInsert(@Param("list") List list);
/**
* 批量插入数据, 主键重复进行更新
*
* 缺陷: list中, 每个对象是否为空需要保持一致, 按照第一个对象中字段是否为空判断, 非空才进行插入/更新
*
* @param list 插入列表
* @return 成功数量
*/
int batchUpsert(@Param("list") List list);
/**
* 批量更新
*
* 缺陷: list中, 每个对象是否为空需要保持一致, 按照第一个对象中字段是否为空判断, 非空才进行更新
*
* @param list 跟新列表 批量数量建议小于1000, 数量太大销量会很低
* @return 成功数量
*/
int batchUpdate(@Param("list") List list);
}
样例
数据实体对象: BizEnumDO.java
/**
*@Author: wyh
*@Date: 2020-04-10
*/
package com.clubfactory.center.product;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;
/**
* @ClassName: BizEnum
* @Description:
* @author wyh
* @date 2020-04-10
*/
@Data
@Accessors(chain = true)
public class BizEnumDO implements Serializable{
/**
* 主键
*/
private Integer id;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新时间
*/
private Date updateTime;
/**
* 逻辑删除标示 1:删除
*/
private Integer isDelete;
/**
* 枚举key
*/
private String mainKey;
/**
* 枚举名称
*/
private String mainValue;
/**
* 枚举子项key
*/
private String itemKey;
/**
* 枚举子项value
*/
private String itemValue;
/**
* 排序
*/
private Integer sequence;
}
数据库接口: BizEnumDao.java
/**
*@Author: wyh
*@Date: 2019-10-09
*/
package com.clubfactory.center.product;
import com.clubfactory.center.product.BizEnumDO;
import org.springframework.stereotype.Repository;
/**
* @author wyh
* @date 2019-10-09
*/
@Repository
public interface BizEnumDao extends IBaseDao<BizEnumDO> {
}
自动生成的数据库接口实现: BizEnumMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.clubfactory.center.product.BizEnumDao">
<resultMap id="BaseResultMap" type="com.clubfactory.center.product.BizEnumDO">
<id property="id" column="ID"/><!--主键-->
<result property="createTime" column="CREATE_TIME"/><!--创建时间-->
<result property="updateTime" column="UPDATE_TIME"/><!--更新时间-->
<result property="isDelete" column="IS_DELETE"/><!--逻辑删除标示 1:删除-->
<result property="mainKey" column="MAIN_KEY"/><!--枚举key-->
<result property="mainValue" column="MAIN_VALUE"/><!--枚举名称-->
<result property="itemKey" column="ITEM_KEY"/><!--枚举子项key-->
<result property="itemValue" column="ITEM_VALUE"/><!--枚举子项value-->
<result property="sequence" column="SEQUENCE"/><!--排序-->
</resultMap>
<sql id="Base_Column_List">
<trim suffixOverrides=",">
ID,
CREATE_TIME,
UPDATE_TIME,
IS_DELETE,
MAIN_KEY,
MAIN_VALUE,
ITEM_KEY,
ITEM_VALUE,
SEQUENCE,
</trim>
</sql>
<sql id="idCnd">
<where>
<if test="id!=null">ID=#{id,jdbcType=INTEGER}</if>
<if test="id==null">1=0</if>
</where>
</sql>
<insert id="insert" parameterType="com.clubfactory.center.product.BizEnumDO"
keyProperty="id" useGeneratedKeys="true">
INSERT INTO
biz_enum
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id!=null">ID,</if>
<if test="createTime!=null">CREATE_TIME,</if>
<if test="updateTime!=null">UPDATE_TIME,</if>
<if test="isDelete!=null">IS_DELETE,</if>
<if test="mainKey!=null">MAIN_KEY,</if>
<if test="mainValue!=null">MAIN_VALUE,</if>
<if test="itemKey!=null">ITEM_KEY,</if>
<if test="itemValue!=null">ITEM_VALUE,</if>
<if test="sequence!=null">SEQUENCE,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id!=null">#{id,jdbcType=INTEGER},</if>
<if test="createTime!=null">#{createTime,jdbcType=TIMESTAMP},</if>
<if test="updateTime!=null">#{updateTime,jdbcType=TIMESTAMP},</if>
<if test="isDelete!=null">#{isDelete,jdbcType=TINYINT},</if>
<if test="mainKey!=null">#{mainKey,jdbcType=VARCHAR},</if>
<if test="mainValue!=null">#{mainValue,jdbcType=VARCHAR},</if>
<if test="itemKey!=null">#{itemKey,jdbcType=VARCHAR},</if>
<if test="itemValue!=null">#{itemValue,jdbcType=VARCHAR},</if>
<if test="sequence!=null">#{sequence,jdbcType=TINYINT},</if>
</trim>
</insert>
<insert id="upsert" parameterType="com.clubfactory.center.product.BizEnumDO"
keyProperty="id" useGeneratedKeys="true">
INSERT INTO
biz_enum
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id!=null">ID,</if>
<if test="createTime!=null">CREATE_TIME,</if>
<if test="updateTime!=null">UPDATE_TIME,</if>
<if test="isDelete!=null">IS_DELETE,</if>
<if test="mainKey!=null">MAIN_KEY,</if>
<if test="mainValue!=null">MAIN_VALUE,</if>
<if test="itemKey!=null">ITEM_KEY,</if>
<if test="itemValue!=null">ITEM_VALUE,</if>
<if test="sequence!=null">SEQUENCE,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id!=null">#{id,jdbcType=INTEGER},</if>
<if test="createTime!=null">#{createTime,jdbcType=TIMESTAMP},</if>
<if test="updateTime!=null">#{updateTime,jdbcType=TIMESTAMP},</if>
<if test="isDelete!=null">#{isDelete,jdbcType=TINYINT},</if>
<if test="mainKey!=null">#{mainKey,jdbcType=VARCHAR},</if>
<if test="mainValue!=null">#{mainValue,jdbcType=VARCHAR},</if>
<if test="itemKey!=null">#{itemKey,jdbcType=VARCHAR},</if>
<if test="itemValue!=null">#{itemValue,jdbcType=VARCHAR},</if>
<if test="sequence!=null">#{sequence,jdbcType=TINYINT},</if>
</trim>
ON DUPLICATE KEY UPDATE
<trim suffixOverrides=",">
<if test="createTime!=null">CREATE_TIME=#{createTime,jdbcType=TIMESTAMP},</if>
<if test="updateTime!=null">UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP},</if>
<if test="isDelete!=null">IS_DELETE=#{isDelete,jdbcType=TINYINT},</if>
<if test="mainKey!=null">MAIN_KEY=#{mainKey,jdbcType=VARCHAR},</if>
<if test="mainValue!=null">MAIN_VALUE=#{mainValue,jdbcType=VARCHAR},</if>
<if test="itemKey!=null">ITEM_KEY=#{itemKey,jdbcType=VARCHAR},</if>
<if test="itemValue!=null">ITEM_VALUE=#{itemValue,jdbcType=VARCHAR},</if>
<if test="sequence!=null">SEQUENCE=#{sequence,jdbcType=TINYINT},</if>
</trim>
</insert>
<delete id="deleteByPrimaryKey">
DELETE FROM biz_enum
<include refid="idCnd"/>
</delete>
<update id="updateByPrimaryKey">
UPDATE biz_enum
<set>
<if test="id!=null">ID=#{id,jdbcType=INTEGER},</if>
<if test="createTime!=null">CREATE_TIME=#{createTime,jdbcType=TIMESTAMP},</if>
<if test="updateTime!=null">UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP},</if>
<if test="isDelete!=null">IS_DELETE=#{isDelete,jdbcType=TINYINT},</if>
<if test="mainKey!=null">MAIN_KEY=#{mainKey,jdbcType=VARCHAR},</if>
<if test="mainValue!=null">MAIN_VALUE=#{mainValue,jdbcType=VARCHAR},</if>
<if test="itemKey!=null">ITEM_KEY=#{itemKey,jdbcType=VARCHAR},</if>
<if test="itemValue!=null">ITEM_VALUE=#{itemValue,jdbcType=VARCHAR},</if>
<if test="sequence!=null">SEQUENCE=#{sequence,jdbcType=TINYINT},</if>
</set>
<include refid="idCnd"/>
</update>
<select id="selectByPrimaryKey" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
biz_enum
<include refid="idCnd"/>
</select>
<sql id="where_clause">
<where>
<if test="id!=null">AND ID=#{id,jdbcType=INTEGER}</if>
<if test="createTime!=null">AND CREATE_TIME=#{createTime,jdbcType=TIMESTAMP}</if>
<if test="updateTime!=null">AND UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP}</if>
<if test="isDelete!=null">AND IS_DELETE=#{isDelete,jdbcType=TINYINT}</if>
<if test="mainKey!=null">AND MAIN_KEY=#{mainKey,jdbcType=VARCHAR}</if>
<if test="mainValue!=null">AND MAIN_VALUE=#{mainValue,jdbcType=VARCHAR}</if>
<if test="itemKey!=null">AND ITEM_KEY=#{itemKey,jdbcType=VARCHAR}</if>
<if test="itemValue!=null">AND ITEM_VALUE=#{itemValue,jdbcType=VARCHAR}</if>
<if test="sequence!=null">AND SEQUENCE=#{sequence,jdbcType=TINYINT}</if>
</where>
</sql>
<select id="listByParams" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
biz_enum
<include refid="where_clause"/>
</select>
<insert id="batchInsert" parameterType="com.clubfactory.center.product.BizEnumDO"
keyProperty="id" useGeneratedKeys="true">
INSERT INTO
biz_enum
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="list[0].id!=null">ID,</if>
<if test="list[0].createTime!=null">CREATE_TIME,</if>
<if test="list[0].updateTime!=null">UPDATE_TIME,</if>
<if test="list[0].isDelete!=null">IS_DELETE,</if>
<if test="list[0].mainKey!=null">MAIN_KEY,</if>
<if test="list[0].mainValue!=null">MAIN_VALUE,</if>
<if test="list[0].itemKey!=null">ITEM_KEY,</if>
<if test="list[0].itemValue!=null">ITEM_VALUE,</if>
<if test="list[0].sequence!=null">SEQUENCE,</if>
</trim>
values
<foreach collection="list" item="item" separator=",">
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="list[0].id!=null">#{item.id,jdbcType=INTEGER},</if>
<if test="list[0].createTime!=null">#{item.createTime,jdbcType=TIMESTAMP},</if>
<if test="list[0].updateTime!=null">#{item.updateTime,jdbcType=TIMESTAMP},</if>
<if test="list[0].isDelete!=null">#{item.isDelete,jdbcType=TINYINT},</if>
<if test="list[0].mainKey!=null">#{item.mainKey,jdbcType=VARCHAR},</if>
<if test="list[0].mainValue!=null">#{item.mainValue,jdbcType=VARCHAR},</if>
<if test="list[0].itemKey!=null">#{item.itemKey,jdbcType=VARCHAR},</if>
<if test="list[0].itemValue!=null">#{item.itemValue,jdbcType=VARCHAR},</if>
<if test="list[0].sequence!=null">#{item.sequence,jdbcType=TINYINT},</if>
</trim>
</foreach>
</insert>
<insert id="batchUpsert" parameterType="com.clubfactory.center.product.BizEnumDO"
keyProperty="id" useGeneratedKeys="true">
INSERT INTO
biz_enum
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="list[0].id!=null">ID,</if>
<if test="list[0].createTime!=null">CREATE_TIME,</if>
<if test="list[0].updateTime!=null">UPDATE_TIME,</if>
<if test="list[0].isDelete!=null">IS_DELETE,</if>
<if test="list[0].mainKey!=null">MAIN_KEY,</if>
<if test="list[0].mainValue!=null">MAIN_VALUE,</if>
<if test="list[0].itemKey!=null">ITEM_KEY,</if>
<if test="list[0].itemValue!=null">ITEM_VALUE,</if>
<if test="list[0].sequence!=null">SEQUENCE,</if>
</trim>
values
<foreach collection="list" item="item" separator=",">
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="list[0].id!=null">#{item.id,jdbcType=INTEGER},</if>
<if test="list[0].createTime!=null">#{item.createTime,jdbcType=TIMESTAMP},</if>
<if test="list[0].updateTime!=null">#{item.updateTime,jdbcType=TIMESTAMP},</if>
<if test="list[0].isDelete!=null">#{item.isDelete,jdbcType=TINYINT},</if>
<if test="list[0].mainKey!=null">#{item.mainKey,jdbcType=VARCHAR},</if>
<if test="list[0].mainValue!=null">#{item.mainValue,jdbcType=VARCHAR},</if>
<if test="list[0].itemKey!=null">#{item.itemKey,jdbcType=VARCHAR},</if>
<if test="list[0].itemValue!=null">#{item.itemValue,jdbcType=VARCHAR},</if>
<if test="list[0].sequence!=null">#{item.sequence,jdbcType=TINYINT},</if>
</trim>
</foreach>
ON DUPLICATE KEY UPDATE
<trim suffixOverrides=",">
<if test="list[0].createTime!=null">CREATE_TIME=values(CREATE_TIME),</if>
<if test="list[0].updateTime!=null">UPDATE_TIME=values(UPDATE_TIME),</if>
<if test="list[0].isDelete!=null">IS_DELETE=values(IS_DELETE),</if>
<if test="list[0].mainKey!=null">MAIN_KEY=values(MAIN_KEY),</if>
<if test="list[0].mainValue!=null">MAIN_VALUE=values(MAIN_VALUE),</if>
<if test="list[0].itemKey!=null">ITEM_KEY=values(ITEM_KEY),</if>
<if test="list[0].itemValue!=null">ITEM_VALUE=values(ITEM_VALUE),</if>
<if test="list[0].sequence!=null">SEQUENCE=values(SEQUENCE),</if>
</trim>
</insert>
<update id="batchUpdate" parameterType="com.clubfactory.center.product.BizEnumDO">
update
biz_enum
<trim prefix="set" suffixOverrides=",">
<if test="list[0].createTime!=null">
<trim prefix="CREATE_TIME = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.createTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
</if>
<if test="list[0].updateTime!=null">
<trim prefix="UPDATE_TIME = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.updateTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
</if>
<if test="list[0].isDelete!=null">
<trim prefix="IS_DELETE = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.isDelete,jdbcType=TINYINT}
</foreach>
</trim>
</if>
<if test="list[0].mainKey!=null">
<trim prefix="MAIN_KEY = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.mainKey,jdbcType=VARCHAR}
</foreach>
</trim>
</if>
<if test="list[0].mainValue!=null">
<trim prefix="MAIN_VALUE = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.mainValue,jdbcType=VARCHAR}
</foreach>
</trim>
</if>
<if test="list[0].itemKey!=null">
<trim prefix="ITEM_KEY = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.itemKey,jdbcType=VARCHAR}
</foreach>
</trim>
</if>
<if test="list[0].itemValue!=null">
<trim prefix="ITEM_VALUE = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.itemValue,jdbcType=VARCHAR}
</foreach>
</trim>
</if>
<if test="list[0].sequence!=null">
<trim prefix="SEQUENCE = case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.sequence,jdbcType=TINYINT}
</foreach>
</trim>
</if>
</trim>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
</mapper>
手动添加的数据库接口实现: BizEnumMapper-Manual.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.clubfactory.center.product.BizEnumDao">
</mapper>
下载地址
git仓库地址