mybatis通用功能代码生成工具「终于解决」

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说mybatis通用功能代码生成工具「终于解决」,希望您对编程的造诣更进一步.

mybatis通用功能代码生成工具

mybatis操作数据库的过程中，如果只考虑单表操作，mapper和dao层基本80%的都是固定的，故而可以使用工具进行生成，文末提供自己编写的工具(基于mysql存储过程)：
作者其实就是使用(mybatis-generator)这个工具过程中，有些想法，实践下，编写时很多实现留了口子，后续方便集成到开发框架中。

工具提供 mapper,dao层功能如下：　

通用查询,返回对象
通用查询，返回集合
通用主键查询，返回集合
通过条件和主键in查询,返回集合
通过主键更新
通过条件更新
通过条件和主键in更新
单条插入，id自增
单条插入，id不自增
批量插入

（如需定制化生成代码,请翻阅前几篇文章，本文仅将通用性代码抽取出来：https://www.cnblogs.com/wanglifeng717/p/15839391.html）

1.查询部分示例

因为查询根据不同条件sql不同,可以使用动态语句。使用对象拼接查询条件。此时mapper层只需要一个方法。（工具自动生成代码如下）

// 通用查询，返回对象
@Select({ 
"<script> ",
"select t.id as id,t.create_time as create_time,t.last_update_time as last_update_time,t.login_name as login_name,t.login_password as login_password,t.status as status,t.remark as remark,t.admin_user_id as admin_user_id ",
"from tbl_sapo_admin_account t ",
"<where> ",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where> ",
"</script>" 
})
SapoAdminAccount getSapoAdminAccount(@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);

2.更新部分示例

更新的前提基本都是已经查出来该记录，直接根据主键更新即可。并没有很多花样。（工具自动生成代码如下）

// 通过主键更新
@Update({
    "update tbl_sapo_admin_account set ",
    "create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} ,last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} ,login_name=#{updateObj.loginName,jdbcType=VARCHAR} ,login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} ,status=#{updateObj.status,jdbcType=INTEGER} ,remark=#{updateObj.remark,jdbcType=VARCHAR} ,admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER}  ",
    "where id = #{updateObj.id,jdbcType=INTEGER} "
})
int updateSapoAdminAccountByPrimaryKey(@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate);

如果更新的条件是不确定的，更新的内容也不确定，可以使用动态语句，基本一个更新语句包打天下（工具自动生成代码如下：）

// 通过条件更新
@Update({ 
"<script> ",
"update tbl_sapo_admin_account ",
"<set>",
"<if test="updateObj!=null">",
"<if test = "updateObj.create_time!=null">  create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.last_update_time!=null">  last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.loginName !=null and updateObj.loginName !=&apos;&apos;">  login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.loginPassword !=null and updateObj.loginPassword !=&apos;&apos;">  login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.status!=null">  status=#{updateObj.status,jdbcType=INTEGER} , </if>" ,
"<if test = "updateObj.remark !=null and updateObj.remark !=&apos;&apos;">  remark=#{updateObj.remark,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.admin_user_id!=null">  admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER} , </if>" ,
"</if>",
"</set>",
"<where>",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where>",
"</script>" 
})
int updateSapoAdminAccount(@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate,@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);

3.插入部分示例

// 单条插入:id自增
@Insert({ 
    "insert into tbl_sapo_admin_account ",
    "(id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id)",
    "values ",
    "(#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER} ) "
})
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insertSapoAdminAccount(@Param("item") SapoAdminAccount sapoAdminAccount);

// 批量插入
@Insert({
    "<script> ",
        "insert into tbl_sapo_admin_account ( id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id ) values",
        "<foreach collection="itemList" item="item" index="index" open="(" separator="),(" close=")">",
            "#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER}  ",
        "</foreach>",
    "</script>" 
})
int batchInsertSapoAdminAccount(@Param("itemList") List<SapoAdminAccount> sapoAdminAccountList);

工具生成dao层代码示例：

    // 批量插入
    @SuppressWarnings("unchecked")
    public int batchInsertSapoAdminAccount(Object object) {
        // 类型转换，支持单个对象或者集合形式作为入参
        List<SapoAdminAccount> list = null;
        if (object instanceof SapoAdminAccount) {
            list = new ArrayList<>();
            list.add((SapoAdminAccount) object);
        } else if (object instanceof List) {
            for (Object o : (List<?>) object) {
                if (!(o instanceof SapoAdminAccount)) {
                    throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",error element: " + o.toString() + ",object type is error for batch insert" + BizLogUtils.getValueOfBizId());
                }
            }
            list = (List<SapoAdminAccount>) object;
        } else {
            throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",object type is error for batch insert"  + BizLogUtils.getValueOfBizId());
        }

        // 如果集合为空则报异常
        if (list == null || list.size() == 0) {
            throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",batch insert empty ,bizId="  + BizLogUtils.getValueOfBizId());
        }

        // 插入阈值， 每多少条commit一次，默认是200条做一次。
        int threshold = 200;

        int result = 0;
        int sum = list.size();
        int end = 0;
        for (int i = 0; i < sum; i = i + threshold) {
            end = i + threshold > sum ? sum : i + threshold;
            try {
                result += mapper.batchInsertSapoAdminAccount(list.subList(i, end));
            } catch (Exception e) {
                //  根据业务做补偿机制，例如通过end值，将之前插入的值全部删除或者状态翻转为无效
                batchInsertSapoAdminAccountFailOffset(list.subList(0, end));
                throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+ ",end value： " + end + ",batch insert has error,offset [batch insert error] success ,bizId=" + BizLogUtils.getValueOfBizId(),  e);
            }
        }
        return result;
    }

    // 批量插入失败后，进行相关补偿操作
    private void batchInsertSapoAdminAccountFailOffset(List<SapoAdminAccount> list) {

        //  补偿操作，可以比插入操作的阈值大一点， 每多少条commit一次，默认是400条做一次。
        int threshold = 400;
        int sum = list.size();
        int end = 0;
        for (int i = 0; i < sum; i = i + threshold) {
            end = i + threshold > sum ? sum : i + threshold;
            try {
                // TODO 批量插入失败后，需要进行补偿的操作，例如：将之前插入的值全部删除或者状态翻转为无效
                //List<Integer> idList = list.subList(i, end).stream().map(SapoAdminAccount::getId).collect(Collectors.toList());
                //SapoAdminAccount sapoAdminAccountForUpdate = new SapoAdminAccount();
                //sapoAdminAccountForUpdate.setxx();
                //updateSapoAdminAccount(idList,null,sapoAdminAccountForUpdate);
            } catch (Exception e) {
                // 如果做业务补偿的时候也失败了，只能将重要信息打印在日志里面，运维干预进行恢复了
                throw new BusinessException( ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ", [offset batch insert error]  failed ,"+ ",bizId: " + BizLogUtils.getValueOfBizId(), e);
            }
        }

    }


// 单条插入:id自增
public int insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){

    if(sapoAdminAccount == null  ){
        bizLogger.warn(" insert tbl_sapo_admin_account  sapoAdminAccount is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccount is null  , bizId=" + BizLogUtils.getValueOfBizId());
    }

    int insertResult =0;
    try {
        insertResult =  mapper.insertSapoAdminAccount(sapoAdminAccount);
    } catch (DuplicateKeyException e) {
        bizLogger.error(" update tbl_sapo_admin_account duplicateKeyException ,sapoAdminAccount : "
                + sapoAdminAccount.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
    }
    
    if (insertResult==0) {
        bizLogger.warn("insert  tbl_sapo_admin_account  result == 0 , sapoAdminAccount: "+sapoAdminAccount.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }   
    
    return insertResult;
}


// 单条插入：id不自增
public void insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){

    if(sapoAdminAccount == null  ){
        bizLogger.warn(" insert tbl_sapo_admin_account  sapoAdminAccount is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccount is null  , bizId=" + BizLogUtils.getValueOfBizId());
    }

    int insertResult =0;
    try {
        insertResult =  mapper.insertSapoAdminAccount(sapoAdminAccount);
    } catch (DuplicateKeyException e) {
        bizLogger.error(" update tbl_sapo_admin_account duplicateKeyException ,sapoAdminAccount : "
                + sapoAdminAccount.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
    }
    
    if (insertResult!=1) {
        bizLogger.warn("insert  tbl_sapo_admin_account  result ！= 1 , sapoAdminAccount: "+sapoAdminAccount.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }   
    
}


// 通用主键查询，返回对象
public SapoAdminAccount getSapoAdminAccountByPrimaryKey(Integer id){
    
    if(id == null){
        bizLogger.warn(" select tbl_sapo_admin_account  id is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " id is null , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    SapoAdminAccount sapoAdminAccount = mapper.getSapoAdminAccountByPrimaryKey(id);
    
    if(sapoAdminAccount == null){
        bizLogger.warn(" select tbl_sapo_admin_account  by primary key ,but find null ,id : "
                + id.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
 
    return sapoAdminAccount;     
}


// 通用查询，返回对象
public SapoAdminAccount getSapoAdminAccount(SapoAdminAccount sapoAdminAccountForQuery){
    
    if(sapoAdminAccountForQuery == null){
        bizLogger.warn(" select tbl_sapo_admin_account  sapoAdminAccountForQuery is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccountForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    SapoAdminAccount sapoAdminAccount = mapper.getSapoAdminAccount(sapoAdminAccountForQuery);
    
    if(sapoAdminAccount == null){
        bizLogger.warn(" select tbl_sapo_admin_account  result is null ,sapoAdminAccountForQuery : "
                + sapoAdminAccountForQuery.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
 
    return sapoAdminAccount;     
}


// 通用查询，返回集合
public List<SapoAdminAccount> getSapoAdminAccountList(SapoAdminAccount sapoAdminAccountForQuery){
    
    if(sapoAdminAccountForQuery == null){
        bizLogger.warn(" select tbl_sapo_admin_account  sapoAdminAccountForQuery is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccountForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    List<SapoAdminAccount> sapoAdminAccountList = mapper.getSapoAdminAccountList(sapoAdminAccountForQuery);
    
    if(sapoAdminAccountList == null || sapoAdminAccountList.size()==0){
        bizLogger.warn(" select tbl_sapo_admin_account  List is null or size=0 ,sapoAdminAccountForQuery : "
                + sapoAdminAccountForQuery.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
 
    return sapoAdminAccountList;     
}


// 通过主键更新
public void updateSapoAdminAccountByPrimaryKey(SapoAdminAccount sapoAdminAccountForUpdate){

    if(sapoAdminAccountForUpdate == null){
        bizLogger.warn(" update tbl_sapo_admin_account  sapoAdminAccountForUpdate is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccountForUpdate is null , bizId=" + BizLogUtils.getValueOfBizId());
    }

     int updateResult = 0;
    
    try {
        updateResult =  mapper.updateSapoAdminAccountByPrimaryKey(sapoAdminAccountForUpdate);
    } catch (DuplicateKeyException e) {
        bizLogger.warn(" update tbl_sapo_admin_account duplicateKeyException ,sapoAdminAccountForUpdate : "
                + sapoAdminAccountForUpdate.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
    }
    
    /*
    if (updateResult!=1) {
        bizLogger.warn("update  tbl_sapo_admin_account  result !=1 [updateResult, sapoAdminAccountForUpdate] : "+updateResult+","+ sapoAdminAccountForUpdate.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
    */
}


// 通过条件和主键in更新
public void updateSapoAdminAccount(List<Integer> idListForQuery,SapoAdminAccount sapoAdminAccountForQuery,SapoAdminAccount sapoAdminAccountForUpdate){
    
    if(idListForQuery == null && sapoAdminAccountForQuery==null ){
        bizLogger.warn(" update tbl_sapo_admin_account  idListForQuery and sapoAdminAccountForQuery is null at same time");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " idListForQuery and sapoAdminAccountForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    if(sapoAdminAccountForUpdate == null  ){
        bizLogger.warn(" update tbl_sapo_admin_account  sapoAdminAccountForUpdate is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccountForUpdatey is null  , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    
    int updateResult = 0;
    
    try {
        updateResult =  mapper.updateSapoAdminAccount(idListForQuery,sapoAdminAccountForQuery,sapoAdminAccountForUpdate);
    } catch (DuplicateKeyException e) {
        bizLogger.error(" update tbl_sapo_admin_account duplicateKeyException ,sapoAdminAccountForQuery : "
                + sapoAdminAccountForQuery.toString()+" ; idListForQuery: "+idListForQuery);
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
    }
    /*
    if (updateResult!=1) {
        bizLogger.warn("update  tbl_sapo_admin_account result  !=1 [updateResult, sapoAdminAccountForQuery,idListForQuery] : "+updateResult+","+ sapoAdminAccountForQuery.toString()+","+idListForQuery);
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
    */
}


// 通过条件和主键in查询,返回集合
public List<SapoAdminAccount> getSapoAdminAccountList( List<Integer> idListForQuery, SapoAdminAccount sapoAdminAccountForQuery){
    
    if(idListForQuery == null && sapoAdminAccountForQuery == null){
        bizLogger.warn(" select tbl_sapo_admin_account  idListForQuery  && sapoAdminAccountForQuery  is null at same time");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " idListForQuery  && sapoAdminAccountForQuery  is null at same time , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    List<SapoAdminAccount> sapoAdminAccountList = mapper.getSapoAdminAccountList(idListForQuery,sapoAdminAccountForQuery);
    
    if(sapoAdminAccountList == null || sapoAdminAccountList.size()==0){
        bizLogger.warn(" select tbl_sapo_admin_account  ,but result list is null or size=0 ,sapoAdminAccountForQuery : "
                + sapoAdminAccountForQuery.toString()+"; idListForQuery : "+idListForQuery.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
 
    return sapoAdminAccountList;     
}


// 通过条件更新
public void updateSapoAdminAccount(SapoAdminAccount sapoAdminAccountForUpdate,SapoAdminAccount sapoAdminAccountForQuery){
    
    if(sapoAdminAccountForUpdate == null || sapoAdminAccountForQuery==null ){
        bizLogger.warn(" update tbl_sapo_admin_account  sapoAdminAccountForUpdate or sapoAdminAccountForQuery is null ");
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAdminAccountForUpdate or sapoAdminAccountForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
    }
    
    int updateResult = 0;
    
    try {
        updateResult =  mapper.updateSapoAdminAccount(sapoAdminAccountForUpdate,sapoAdminAccountForQuery);
    } catch (DuplicateKeyException e) {
        bizLogger.error(" update tbl_sapo_admin_account duplicateKeyException ,sapoAdminAccountForQuery : "
                + sapoAdminAccountForQuery.toString());
        throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
                ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
    }
    /*
    if (updateResult!=1) {
        bizLogger.warn("update  tbl_sapo_admin_account  result !=1 [updateResult, sapoAdminAccountForQuery] : "+updateResult+","+ sapoAdminAccountForQuery.toString());
        throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
    */
}

View Code

工具生成mapper层代码示例：

// 通用查询，返回对象
@Select({ 
"<script> ",
"select t.id as id,t.create_time as create_time,t.last_update_time as last_update_time,t.login_name as login_name,t.login_password as login_password,t.status as status,t.remark as remark,t.admin_user_id as admin_user_id ",
"from tbl_sapo_admin_account t ",
"<where> ",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where> ",
"</script>" 
})
SapoAdminAccount getSapoAdminAccount(@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);


// 通用查询，返回集合
@Select({ 
"<script> ",
"select t.id as id,t.create_time as create_time,t.last_update_time as last_update_time,t.login_name as login_name,t.login_password as login_password,t.status as status,t.remark as remark,t.admin_user_id as admin_user_id ",
"from tbl_sapo_admin_account t ",
"<where> ",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where> ",
"</script>" 
})
List<SapoAdminAccount> getSapoAdminAccountList(@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);


// 通过主键查询，返回对象
@Select({
    "select t.id as id,t.create_time as create_time,t.last_update_time as last_update_time,t.login_name as login_name,t.login_password as login_password,t.status as status,t.remark as remark,t.admin_user_id as admin_user_id ",
    "from tbl_sapo_admin_account t ",
    "where id = #{id,jdbcType=INTEGER}"
})
SapoAdminAccount getSapoAdminAccountByPrimaryKey(Integer id);


// 通过条件和主键in查询,返回集合
@Select({ 
"<script> ",
"select t.id as id,t.create_time as create_time,t.last_update_time as last_update_time,t.login_name as login_name,t.login_password as login_password,t.status as status,t.remark as remark,t.admin_user_id as admin_user_id ",
"from tbl_sapo_admin_account t ",
"<where> ",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"<if test = "itemList != null and itemList.size() > 0"> AND id IN " ,
"    <foreach collection="itemList" item="item" index="index" open="(" separator="," close=")"> " ,
"        #{item,jdbcType=INTEGER}   " ,
"    </foreach> " ,
"</if>" ,
"</where> ",
"</script>" 
})
List<SapoAdminAccount> getSapoAdminAccountList(@Param("itemList") List<Integer> idListForQuery,@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);


// 通过主键更新
@Update({
    "update tbl_sapo_admin_account set ",
    "create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} ,last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} ,login_name=#{updateObj.loginName,jdbcType=VARCHAR} ,login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} ,status=#{updateObj.status,jdbcType=INTEGER} ,remark=#{updateObj.remark,jdbcType=VARCHAR} ,admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER}  ",
    "where id = #{updateObj.id,jdbcType=INTEGER} "
})
int updateSapoAdminAccountByPrimaryKey(@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate);


// 通过条件更新
@Update({ 
"<script> ",
"update tbl_sapo_admin_account ",
"<set>",
"<if test="updateObj!=null">",
"<if test = "updateObj.create_time!=null">  create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.last_update_time!=null">  last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.loginName !=null and updateObj.loginName !=&apos;&apos;">  login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.loginPassword !=null and updateObj.loginPassword !=&apos;&apos;">  login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.status!=null">  status=#{updateObj.status,jdbcType=INTEGER} , </if>" ,
"<if test = "updateObj.remark !=null and updateObj.remark !=&apos;&apos;">  remark=#{updateObj.remark,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.admin_user_id!=null">  admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER} , </if>" ,
"</if>",
"</set>",
"<where>",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where>",
"</script>" 
})
int updateSapoAdminAccount(@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate,@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);


// 通过条件和主键in更新
@Update({ 
"<script> ",
"update tbl_sapo_admin_account ",
"<set>",
"<if test="updateObj!=null">",
"<if test = "updateObj.create_time!=null">  create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.last_update_time!=null">  last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = "updateObj.loginName !=null and updateObj.loginName !=&apos;&apos;">  login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.loginPassword !=null and updateObj.loginPassword !=&apos;&apos;">  login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.status!=null">  status=#{updateObj.status,jdbcType=INTEGER} , </if>" ,
"<if test = "updateObj.remark !=null and updateObj.remark !=&apos;&apos;">  remark=#{updateObj.remark,jdbcType=VARCHAR} , </if>" ,
"<if test = "updateObj.admin_user_id!=null">  admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER} , </if>" ,
"</if>",
"</set>",
"<where>",
"<if test="queryObj!=null">",
"<if test = "queryObj.id!=null"> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.create_time!=null"> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.last_update_time!=null"> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = "queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;"> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;"> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.status!=null"> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = "queryObj.remark !=null and queryObj.remark !=&apos;&apos;"> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = "queryObj.admin_user_id!=null"> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"<if test = "itemList != null and itemList.size() > 0"> AND id IN " ,
"    <foreach collection="itemList" item="item" index="index" open="(" separator="," close=")"> " ,
"        #{item,jdbcType=INTEGER}   " ,
"    </foreach> " ,
"</if>" ,
"</where>",
"</script>" 
})
int updateSapoAdminAccount(@Param("itemList") List<Integer> idListForQuery,@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery,@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate);


// 单条插入:id自增
@Insert({ 
    "insert into tbl_sapo_admin_account ",
    "(id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id)",
    "values ",
    "(#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER} ) "
})
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insertSapoAdminAccount(@Param("item") SapoAdminAccount sapoAdminAccount);


// 单条插入：id不自增
@Insert({ 
    "insert into tbl_sapo_admin_account ",
    "(id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id)",
    "values ",
    "(#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER} ) "
})
int insertSapoAdminAccount(@Param("item") SapoAdminAccount sapoAdminAccount);


// 批量插入
@Insert({
    "<script> ",
        "insert into tbl_sapo_admin_account ( id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id ) values",
        "<foreach collection="itemList" item="item" index="index" open="(" separator="),(" close=")">",
            "#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER}  ",
        "</foreach>",
    "</script>" 
})
int batchInsertSapoAdminAccount(@Param("itemList") List<SapoAdminAccount> sapoAdminAccountList);

View Code

工具代码：

  1 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
  2 DROP PROCEDURE IF EXISTS `print_code`;
  3 DELIMITER $
  4 CREATE  PROCEDURE `print_code`()
  5 BEGIN
  6 
  7 SET  group_concat_max_len = 4294967295;
  8 
  9 
 10 -- SET @noStrInTbl="tbl_ams";
 11  SET @noStrInTbl="tbl";
 12 
 13 
 14 -- 保存所有表及表的所有字段
 15 DROP TABLE if EXISTS all_col_table;
 16 CREATE table if not exists all_col_table(
 17 tbl_name VARCHAR(256)  NOT NULL  COMMENT "表名:tbl_sapo_admin_account",
 18 col VARCHAR(256) NOT NULL  COMMENT "字段名:create_time",
 19 col_camel VARCHAR(256) COMMENT "字段驼峰形式:createTime",
 20 col_type VARCHAR(256) COMMENT "字段类型，datetime",
 21 java_type VARCHAR(256) COMMENT "java类型，datetime",
 22 jdbc_type VARCHAR(256) COMMENT "jdbc类型：datetime->TIMESTAMP",
 23 if_test VARCHAR(1024) COMMENT "queryObj.create_time!=null",
 24 update_if_test VARCHAR(1024) COMMENT "updateObj.create_time!=null",
 25 col_for_query_jdbc VARCHAR(256) COMMENT "create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} ",
 26 col_for_update_jdbc VARCHAR(256) COMMENT "create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} ",
 27 col_for_insert_jdbc VARCHAR(256) COMMENT "#{item.createTime,jdbcType=TIMESTAMP} ",
 28 col_comment VARCHAR(512) COMMENT "字段注释"
 29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 30 
 31 -- select * from all_col_table;
 32 
 33 -- 将本库中所有表及所有字段插入表中
 34 INSERT INTO all_col_table(tbl_name,col) 
 35 SELECT 
 36     t1.table_name, t1.column_name 
 37 FROM
 38     information_schema.COLUMNS t1
 39 WHERE
 40       t1.table_schema= DATABASE() ;
 41 
 42 -- 字段转驼峰
 43 UPDATE all_col_table SET col_camel =CONCAT_WS("",REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","") 
 44 ,"");
 45 
 46 
 47 -- 更新字段类型id  --> int ,name  -->varchar      
 48 UPDATE all_col_table a SET a.col_type = 
 49 (
 50 SELECT t1.data_type
 51 FROM
 52     information_schema.COLUMNS t1
 53 WHERE
 54     t1.table_schema= DATABASE()  
 55     and t1.TABLE_NAME = a.tbl_name 
 56     and t1.column_name =a.col
 57 );
 58 -- select * from all_col_table;
 59 
 60 -- 转换成jdbc类型
 61 UPDATE all_col_table SET jdbc_type=
 62 case  col_type
 63     when "datetime" then "TIMESTAMP" 
 64     when "tinyint"  then "TINYINT" 
 65     when "bigint"   then "BIGINT" 
 66     when "int"      then "INTEGER"
 67     when "float"      then "REAL" 
 68     when "varchar"  then "VARCHAR" 
 69 END;
 70 
 71 -- java类型转换
 72 UPDATE all_col_table SET java_type=
 73 case  col_type 
 74     when "datetime" then "Date" 
 75     when "tinyint"  then "Byte" 
 76     when "bigint"   then "Long" 
 77     when "int"      then "Integer" 
 78     when "varchar"  then "String" 
 79 END;
 80 
 81 -- 组语句：create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} 
 82 UPDATE all_col_table SET col_for_query_jdbc=CONCAT_WS("",col,"=#{queryObj.",col_camel,",jdbcType=",jdbc_type,"} ");
 83 UPDATE all_col_table SET col_for_update_jdbc=CONCAT_WS("",col,"=#{updateObj.",col_camel,",jdbcType=",jdbc_type,"} ");
 84 UPDATE all_col_table SET col_for_insert_jdbc=CONCAT_WS("","#{item.",col_camel,",jdbcType=",jdbc_type,"} ");
 85 
 86 -- 组语句：queryObj.java_desc!=null and queryObj.java_desc!=&apos;&apos;
 87 UPDATE all_col_table SET if_test=
 88 case  col_type
 89     when "varchar"  then CONCAT_WS("",""","queryObj.",col_camel," !=null and queryObj.",col_camel," !=&apos;&apos;",""") 
 90     else CONCAT_WS("",""","queryObj.",col,"!=null",""")
 91 END;
 92 -- #######################################
 93 UPDATE all_col_table SET update_if_test=
 94 case  col_type
 95     when "varchar"  then CONCAT_WS("",""","updateObj.",col_camel," !=null and updateObj.",col_camel," !=&apos;&apos;",""") 
 96     else CONCAT_WS("",""","updateObj.",col,"!=null",""")
 97 END;
 98 
 99 
100 -- 表相关数据
101 DROP TABLE if EXISTS all_table;
102 CREATE table if not exists all_table(
103 tbl_name VARCHAR(256)  NOT NULL  COMMENT "表名:tbl_sapo_admin_account",
104 primary_key VARCHAR(255) COMMENT "主键",
105 tbl_name_camel VARCHAR(1024) COMMENT "表名驼峰:SapoAdminAccount",
106 tbl_name_ref_camel VARCHAR(1024) COMMENT "表名引用驼峰:sapoAdminAccount",
107 col_list TEXT COMMENT "字段列表",
108 col_list_alias TEXT COMMENT "字段别名列表",
109 insert_if_test TEXT COMMENT "insert语句",
110 query_if_test TEXT COMMENT "queryTest语句",
111 update_chase TEXT COMMENT "update固定语句",
112 update_if_test TEXT COMMENT "updateTest语句"
113 ) ENGINE=InnoDB ;
114 
115 
116 
117 
118 -- 把所有表入库
119 INSERT INTO all_table(tbl_name) 
120 SELECT 
121     t1.table_name
122 FROM
123     information_schema.tables t1
124 WHERE
125       t1.table_schema= DATABASE() AND t1.TABLE_NAME NOT IN("all_col_table","all_table");
126       
127       -- 表名转驼峰
128 UPDATE all_table SET tbl_name_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, @noStrInTbl, ""), "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","") ;
129 UPDATE all_table SET tbl_name_ref_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, CONCAT(@noStrInTbl,"_"), ""), "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","") ;
130 
131 
132  
133  
134  
135  
136 -- 更新主键
137 UPDATE all_table a SET  a.primary_key=
138 (SELECT 
139 column_name
140 FROM information_schema.columns t1
141 WHERE 
142  t1.table_schema= DATABASE() AND t1.COLUMN_KEY="PRI" AND a.tbl_name=table_name 
143  );
144 
145 -- 更新每个表的字段列表 t.id as id,t.create_time as create_time,t.last_update_time as last_update_time
146 UPDATE all_table a SET a.col_list_alias=
147 (
148 SELECT GROUP_CONCAT(
149     CONCAT_WS("","t.",col," as ",col)
150 ) FROM all_col_table WHERE tbl_name = a.tbl_name
151 );
152 -- #######################################
153 UPDATE all_table a SET a.col_list=
154 (
155 SELECT GROUP_CONCAT( col ) FROM all_col_table WHERE tbl_name = a.tbl_name
156 );
157 -- 更新结果为："<if test = "queryObj.id!=null ">  and id=#{queryObj.id,jdbcType=INTEGER}   </if>",
158 UPDATE all_table a SET a.query_if_test=
159 (
160 SELECT 
161         GROUP_CONCAT(
162             CONCAT_WS("",""<if test = ",if_test,"> and ",col_for_query_jdbc," </if>" ,")
163         SEPARATOR "
") 
164     FROM all_col_table WHERE tbl_name = a.tbl_name
165 );
166 
167 -- #######################################
168 UPDATE all_table a SET a.update_if_test=
169 (
170 SELECT 
171         GROUP_CONCAT(
172             CONCAT_WS("",""<if test = ",update_if_test,">  ",col_for_update_jdbc,", </if>" ,")
173         SEPARATOR "
") 
174     FROM all_col_table WHERE tbl_name = a.tbl_name AND a.primary_key!=col
175 );
176 
177 -- #######################################
178 UPDATE all_table a SET a.insert_if_test=
179 (
180 SELECT   
181         GROUP_CONCAT(col_for_insert_jdbc) 
182     FROM all_col_table WHERE tbl_name = a.tbl_name
183 );
184 
185 
186 
187 -- #######################################
188 -- 更新update_chase
189 UPDATE all_table a SET a.update_chase=
190 (
191 SELECT 
192         GROUP_CONCAT( col_for_update_jdbc ) 
193     FROM all_col_table WHERE tbl_name = a.tbl_name AND a.primary_key !=col
194 );
195  
196 
197 
198 
199 -- #################################################################################
200 -- #################################开始组建语句####################################
201 -- #################################################################################
202 -- ############################## mapper select ####################################
203 -- #################################################################################
204 -- #################################################################################
205 
206 -- 保存所有表及表的所有字段
207 DROP TABLE if EXISTS java_code;
208 CREATE table if not exists java_code(
209 tbl_name VARCHAR(256)  NOT NULL  COMMENT "表名:tbl_sapo_admin_account",
210 code_type VARCHAR(255) COMMENT "代码类似，inert,update,select",
211 code_layer VARCHAR(255) COMMENT "代码层级 ，mapper,dao,domain",
212 func_desc VARCHAR(255) COMMENT "功能描述",
213 java_code TEXT COMMENT "java代码",
214 versions VARCHAR(255) COMMENT "版本",
215 versions_desc VARCHAR(255) COMMENT "版本描述"
216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
217 
218 
219 
220 -- ######################################################################################################
221 -- 通用查询，返回对象
222 -- ######################################################################################################
223 
224 
225 SET @query_template1=
226 "
227 // 通用查询，返回对象
228 @Select({ 
229 "<script> ",
230 "select @col_list_alias@ ",
231 "from @tbl_name@ t ",
232 "<where> ",
233 "<if test="queryObj!=null">",
234 @query_if_test@
235 "</if>",
236 "</where> ",
237 "</script>" 
238 })
239 @tbl_name_camel@ get@tbl_name_camel@(@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery);
240 ";
241 
242 INSERT INTO java_code
243 SELECT tbl_name,"select","mapper","通用查询，返回对象",@query_template1,"1","" FROM all_table;
244 
245 -- dao层语句
246 SET @query_template1=
247 "
248 // 通用查询，返回对象
249 public @tbl_name_camel@ get@tbl_name_camel@(@tbl_name_camel@ @tbl_name_ref_camel@ForQuery){
250     
251     if(@tbl_name_ref_camel@ForQuery == null){
252         bizLogger.warn(" select @tbl_name@  @tbl_name_ref_camel@ForQuery is null ");
253         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
254                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
255     }
256     
257     @tbl_name_camel@ @tbl_name_ref_camel@ = mapper.get@tbl_name_camel@(@tbl_name_ref_camel@ForQuery);
258     
259     if(@tbl_name_ref_camel@ == null){
260         bizLogger.warn(" select @tbl_name@  result is null ,@tbl_name_ref_camel@ForQuery : "
261                 + @tbl_name_ref_camel@ForQuery.toString());
262         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
263     }
264  
265     return @tbl_name_ref_camel@;     
266 }
267 ";
268 
269 INSERT INTO java_code
270 SELECT tbl_name,"select","dao","通用查询,返回对象",@query_template1,"1","" FROM all_table;
271 
272 
273 -- ######################################################################################################
274 -- 通用查询，返回集合
275 -- ######################################################################################################
276 
277 SET @query_template1=
278 "
279 // 通用查询，返回集合
280 @Select({ 
281 "<script> ",
282 "select @col_list_alias@ ",
283 "from @tbl_name@ t ",
284 "<where> ",
285 "<if test="queryObj!=null">",
286 @query_if_test@
287 "</if>",
288 "</where> ",
289 "</script>" 
290 })
291 List<@tbl_name_camel@> get@tbl_name_camel@List(@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery);
292 ";
293 
294 INSERT INTO java_code
295 SELECT tbl_name,"select","mapper","通用查询，返回集合",@query_template1,"1","" FROM all_table;
296 
297 -- dao层
298 SET @query_template1=
299 "
300 // 通用查询，返回集合
301 public List<@tbl_name_camel@> get@tbl_name_camel@List(@tbl_name_camel@ @tbl_name_ref_camel@ForQuery){
302     
303     if(@tbl_name_ref_camel@ForQuery == null){
304         bizLogger.warn(" select @tbl_name@  @tbl_name_ref_camel@ForQuery is null ");
305         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
306                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
307     }
308     
309     List<@tbl_name_camel@> @tbl_name_ref_camel@List = mapper.get@tbl_name_camel@List(@tbl_name_ref_camel@ForQuery);
310     
311     if(@tbl_name_ref_camel@List == null || @tbl_name_ref_camel@List.size()==0){
312         bizLogger.warn(" select @tbl_name@  List is null or size=0 ,@tbl_name_ref_camel@ForQuery : "
313                 + @tbl_name_ref_camel@ForQuery.toString());
314         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
315     }
316  
317     return @tbl_name_ref_camel@List;     
318 }
319 ";
320 
321 INSERT INTO java_code
322 SELECT tbl_name,"select","dao","通用查询，返回集合",@query_template1,"1","" FROM all_table;
323 
324 
325 -- ######################################################################################################
326 -- 通过主键查询，返回对象
327 -- ######################################################################################################
328 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
329 SET @query_template1=
330 "
331 // 通过主键查询，返回对象
332 @Select({
333     "select @col_list_alias@ ",
334     "from @tbl_name@ t ",
335     "where @primary_key@ = #{@col_camel@,jdbcType=@jdbc_type@}"
336 })
337 @tbl_name_camel@ get@tbl_name_camel@ByPrimaryKey(@java_type@ @col_camel@);
338 ";
339 
340 INSERT INTO java_code
341 SELECT tbl_name,"select","mapper","通过主键查询",@query_template1,"1","" FROM  all_table;
342 
343 
344 -- dao层
345 SET @query_template1=
346 "
347 // 通用主键查询，返回对象
348 public @tbl_name_camel@ get@tbl_name_camel@ByPrimaryKey(@java_type@ @col_camel@){
349     
350     if(@col_camel@ == null){
351         bizLogger.warn(" select @tbl_name@  @col_camel@ is null ");
352         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
353                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @col_camel@ is null , bizId=" + BizLogUtils.getValueOfBizId());
354     }
355     
356     @tbl_name_camel@ @tbl_name_ref_camel@ = mapper.get@tbl_name_camel@ByPrimaryKey(@col_camel@);
357     
358     if(@tbl_name_ref_camel@ == null){
359         bizLogger.warn(" select @tbl_name@  by primary key ,but find null ,@col_camel@ : "
360                 + @col_camel@.toString());
361         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
362     }
363  
364     return @tbl_name_ref_camel@;     
365 }
366 ";
367 
368 
369 
370 INSERT INTO java_code
371 SELECT tbl_name,"select","dao","通用主键查询，返回集合",@query_template1,"1","" FROM all_table;
372 
373 
374 -- ######################################################################################################
375 -- 通过条件和主键in查询,返回集合
376 -- ######################################################################################################
377 
378 
379 SET @query_template1=
380 "
381 // 通过条件和主键in查询,返回集合
382 @Select({ 
383 "<script> ",
384 "select @col_list_alias@ ",
385 "from @tbl_name@ t ",
386 "<where> ",
387 "<if test="queryObj!=null">",
388 @query_if_test@
389 "</if>",
390 "<if test = "itemList != null and itemList.size() > 0"> AND id IN " ,
391 "    <foreach collection="itemList" item="item" index="index" open="(" separator="," close=")"> " ,
392 "        #{item,jdbcType=@jdbc_type@}   " ,
393 "    </foreach> " ,
394 "</if>" ,
395 "</where> ",
396 "</script>" 
397 })
398 List<@tbl_name_camel@> get@tbl_name_camel@List(@Param("itemList") List<@java_type@> @col_camel@ListForQuery,@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery);
399 ";
400 
401 INSERT INTO java_code
402 SELECT tbl_name,"select","mapper","通过条件和主键in查询,返回集合",@query_template1,"1","" FROM  all_table;
403 
404 
405 -- dao层
406 SET @query_template1=
407 "
408 // 通过条件和主键in查询,返回集合
409 public List<@tbl_name_camel@> get@tbl_name_camel@List( List<@java_type@> @col_camel@ListForQuery, @tbl_name_camel@ @tbl_name_ref_camel@ForQuery){
410     
411     if(@col_camel@ListForQuery == null && @tbl_name_ref_camel@ForQuery == null){
412         bizLogger.warn(" select @tbl_name@  @col_camel@ListForQuery  && @tbl_name_ref_camel@ForQuery  is null at same time");
413         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
414                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @col_camel@ListForQuery  && @tbl_name_ref_camel@ForQuery  is null at same time , bizId=" + BizLogUtils.getValueOfBizId());
415     }
416     
417     List<@tbl_name_camel@> @tbl_name_ref_camel@List = mapper.get@tbl_name_camel@List(@col_camel@ListForQuery,@tbl_name_ref_camel@ForQuery);
418     
419     if(@tbl_name_ref_camel@List == null || @tbl_name_ref_camel@List.size()==0){
420         bizLogger.warn(" select @tbl_name@  ,but result list is null or size=0 ,@tbl_name_ref_camel@ForQuery : "
421                 + @tbl_name_ref_camel@ForQuery.toString()+"; @col_camel@ListForQuery : "+@col_camel@ListForQuery.toString());
422         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
423     }
424  
425     return @tbl_name_ref_camel@List;     
426 }
427 ";
428 
429 INSERT INTO java_code
430 SELECT tbl_name,"select","dao","通过条件和主键in查询,返回集合",@query_template1,"1","" FROM all_table;
431 
432 
433 
434 -- #################################################################################
435 -- #################################################################################
436 -- #################################################################################
437 -- #################################################################################
438 -- ############################## mapper update ####################################
439 -- #################################################################################
440 -- #################################################################################
441 
442 -- ######################################################################################################
443 -- 通过主键更新
444 -- ######################################################################################################
445 
446 
447 SET @query_template1=
448 "
449 // 通过主键更新
450 @Update({
451     "update @tbl_name@ set ",
452     "@update_chase@ ",
453     "where @primary_key@ = #{updateObj.@col_camel@,jdbcType=@jdbc_type@} "
454 })
455 int update@tbl_name_camel@ByPrimaryKey(@Param("updateObj") @tbl_name_camel@ @tbl_name_ref_camel@ForUpdate);
456 ";
457 
458 
459 INSERT INTO java_code
460 SELECT tbl_name,"update","mapper","通过主键更新",@query_template1,"1","" FROM all_table;
461 
462 -- dao
463 
464 SET @query_template1=
465 "
466 // 通过主键更新
467 public void update@tbl_name_camel@ByPrimaryKey(@tbl_name_camel@ @tbl_name_ref_camel@ForUpdate){
468 
469     if(@tbl_name_ref_camel@ForUpdate == null){
470         bizLogger.warn(" update @tbl_name@  @tbl_name_ref_camel@ForUpdate is null ");
471         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
472                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ForUpdate is null , bizId=" + BizLogUtils.getValueOfBizId());
473     }
474 
475      int updateResult = 0;
476     
477     try {
478         updateResult =  mapper.update@tbl_name_camel@ByPrimaryKey(@tbl_name_ref_camel@ForUpdate);
479     } catch (DuplicateKeyException e) {
480         bizLogger.warn(" update @tbl_name@ duplicateKeyException ,@tbl_name_ref_camel@ForUpdate : "
481                 + @tbl_name_ref_camel@ForUpdate.toString());
482         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
483                 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
484     }
485     
486     /*
487     if (updateResult!=1) {
488         bizLogger.warn("update  @tbl_name@  result !=1 [updateResult, @tbl_name_ref_camel@ForUpdate] : "+updateResult+","+ @tbl_name_ref_camel@ForUpdate.toString());
489         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
490     }
491     */
492 }
493 ";
494 
495 
496 INSERT INTO java_code
497 SELECT tbl_name,"update","dao","通过主键更新",@query_template1,"1","" FROM all_table;
498 
499 -- ######################################################################################################
500 -- 通过条件更新
501 -- ######################################################################################################
502 
503 
504 SET @query_template1=
505 "
506 // 通过条件更新
507 @Update({ 
508 "<script> ",
509 "update @tbl_name@ ",
510 "<set>",
511 "<if test="updateObj!=null">",
512 @update_if_test@
513 "</if>",
514 "</set>",
515 "<where>",
516 "<if test="queryObj!=null">",
517 @query_if_test@
518 "</if>",
519 "</where>",
520 "</script>" 
521 })
522 int update@tbl_name_camel@(@Param("updateObj") @tbl_name_camel@ @tbl_name_ref_camel@ForUpdate,@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery);
523 ";
524 
525 
526 INSERT INTO java_code
527 SELECT tbl_name,"update","mapper","通过条件更新",@query_template1,"1","" FROM all_table;
528 
529 -- dao
530 SET @query_template1=
531 "
532 // 通过条件更新
533 public void update@tbl_name_camel@(@tbl_name_camel@ @tbl_name_ref_camel@ForUpdate,@tbl_name_camel@ @tbl_name_ref_camel@ForQuery){
534     
535     if(@tbl_name_ref_camel@ForUpdate == null || @tbl_name_ref_camel@ForQuery==null ){
536         bizLogger.warn(" update @tbl_name@  @tbl_name_ref_camel@ForUpdate or @tbl_name_ref_camel@ForQuery is null ");
537         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
538                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ForUpdate or @tbl_name_ref_camel@ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId());
539     }
540     
541     int updateResult = 0;
542     
543     try {
544         updateResult =  mapper.update@tbl_name_camel@(@tbl_name_ref_camel@ForUpdate,@tbl_name_ref_camel@ForQuery);
545     } catch (DuplicateKeyException e) {
546         bizLogger.error(" update @tbl_name@ duplicateKeyException ,@tbl_name_ref_camel@ForQuery : "
547                 + @tbl_name_ref_camel@ForQuery.toString());
548         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
549                 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
550     }
551     /*
552     if (updateResult!=1) {
553         bizLogger.warn("update  @tbl_name@  result !=1 [updateResult, @tbl_name_ref_camel@ForQuery] : "+updateResult+","+ @tbl_name_ref_camel@ForQuery.toString());
554         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
555     }
556     */
557 }
558 ";
559 
560 
561 INSERT INTO java_code
562 SELECT tbl_name,"update","dao","通过条件更新",@query_template1,"1","" FROM all_table;
563 
564 
565 
566 -- ######################################################################################################
567 -- 通过条件和主键in更新
568 -- ######################################################################################################
569 
570 
571 SET @query_template1=
572 "
573 // 通过条件和主键in更新
574 @Update({ 
575 "<script> ",
576 "update @tbl_name@ ",
577 "<set>",
578 "<if test="updateObj!=null">",
579 @update_if_test@
580 "</if>",
581 "</set>",
582 "<where>",
583 "<if test="queryObj!=null">",
584 @query_if_test@
585 "</if>",
586 "<if test = "itemList != null and itemList.size() > 0"> AND id IN " ,
587 "    <foreach collection="itemList" item="item" index="index" open="(" separator="," close=")"> " ,
588 "        #{item,jdbcType=@jdbc_type@}   " ,
589 "    </foreach> " ,
590 "</if>" ,
591 "</where>",
592 "</script>" 
593 })
594 int update@tbl_name_camel@(@Param("itemList") List<@java_type@> @col_camel@ListForQuery,@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery,@Param("updateObj") @tbl_name_camel@ @tbl_name_ref_camel@ForUpdate);
595 ";
596 
597 
598 INSERT INTO java_code
599 SELECT tbl_name,"update","mapper","通过条件和主键in更新",@query_template1,"1","" FROM all_table;
600 
601 -- dao
602 
603 SET @query_template1=
604 "
605 // 通过条件和主键in更新
606 public void update@tbl_name_camel@(List<@java_type@> @col_camel@ListForQuery,@tbl_name_camel@ @tbl_name_ref_camel@ForQuery,@tbl_name_camel@ @tbl_name_ref_camel@ForUpdate){
607     
608     if(@col_camel@ListForQuery == null && @tbl_name_ref_camel@ForQuery==null ){
609         bizLogger.warn(" update @tbl_name@  @col_camel@ListForQuery and @tbl_name_ref_camel@ForQuery is null at same time");
610         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
611                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @col_camel@ListForQuery and @tbl_name_ref_camel@ForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId());
612     }
613     
614     if(@tbl_name_ref_camel@ForUpdate == null  ){
615         bizLogger.warn(" update @tbl_name@  @tbl_name_ref_camel@ForUpdate is null ");
616         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
617                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ForUpdatey is null  , bizId=" + BizLogUtils.getValueOfBizId());
618     }
619     
620     
621     int updateResult = 0;
622     
623     try {
624         updateResult =  mapper.update@tbl_name_camel@(@col_camel@ListForQuery,@tbl_name_ref_camel@ForQuery,@tbl_name_ref_camel@ForUpdate);
625     } catch (DuplicateKeyException e) {
626         bizLogger.error(" update @tbl_name@ duplicateKeyException ,@tbl_name_ref_camel@ForQuery : "
627                 + @tbl_name_ref_camel@ForQuery.toString()+" ; @col_camel@ListForQuery: "+@col_camel@ListForQuery);
628         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
629                 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
630     }
631     /*
632     if (updateResult!=1) {
633         bizLogger.warn("update  @tbl_name@ result  !=1 [updateResult, @tbl_name_ref_camel@ForQuery,@col_camel@ListForQuery] : "+updateResult+","+ @tbl_name_ref_camel@ForQuery.toString()+","+@col_camel@ListForQuery);
634         throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
635     }
636     */
637 }
638 ";
639 
640 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
641 INSERT INTO java_code
642 SELECT tbl_name,"update","dao","通过条件和主键in更新",@query_template1,"1","" FROM all_table;
643 
644 
645 
646 -- #################################################################################
647 -- #################################################################################
648 -- #################################################################################
649 -- #################################################################################
650 -- ############################## mapper insert ####################################
651 -- #################################################################################
652 -- #################################################################################
653 
654 -- ######################################################################################################
655 -- 单条插入:id自增
656 -- ######################################################################################################
657 
658 SET @query_template1=
659 "
660 // 单条插入:id自增
661 @Insert({ 
662     "insert into @tbl_name@ ",
663     "(@col_list@)",
664     "values ",
665     "(@insert_if_test@) "
666 })
667 @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
668 int insert@tbl_name_camel@(@Param("item") @tbl_name_camel@ @tbl_name_ref_camel@);
669 ";
670 
671 
672 INSERT INTO java_code
673 SELECT tbl_name,"insert","mapper","单条插入",@query_template1,"1","id自增" FROM all_table;
674 
675 -- dao
676 SET @query_template1=
677 "
678 // 单条插入:id自增
679 public int insert@tbl_name_camel@(@tbl_name_camel@ @tbl_name_ref_camel@){
680 
681     if(@tbl_name_ref_camel@ == null  ){
682         bizLogger.warn(" insert @tbl_name@  @tbl_name_ref_camel@ is null ");
683         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
684                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ is null  , bizId=" + BizLogUtils.getValueOfBizId());
685     }
686 
687     int insertResult =0;
688     try {
689         insertResult =  mapper.insert@tbl_name_camel@(@tbl_name_ref_camel@);
690     } catch (DuplicateKeyException e) {
691         bizLogger.error(" update @tbl_name@ duplicateKeyException ,@tbl_name_ref_camel@ : "
692                 + @tbl_name_ref_camel@.toString());
693         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
694                 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
695     }
696     
697     if (insertResult==0) {
698         bizLogger.warn("insert  @tbl_name@  result == 0 , @tbl_name_ref_camel@: "+@tbl_name_ref_camel@.toString());
699         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
700     }   
701     
702     return insertResult;
703 }
704 ";
705 
706 INSERT INTO java_code
707 SELECT tbl_name,"insert","dao","单条插入",@query_template1,"1","id自增" FROM all_table;
708 
709 -- ######################################################################################################
710 -- 单条插入：id不自增
711 -- ######################################################################################################
712 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
713 SET @query_template1=
714 "
715 // 单条插入：id不自增
716 @Insert({ 
717     "insert into @tbl_name@ ",
718     "(@col_list@)",
719     "values ",
720     "(@insert_if_test@) "
721 })
722 int insert@tbl_name_camel@(@Param("item") @tbl_name_camel@ @tbl_name_ref_camel@);
723 ";
724 
725 
726 INSERT INTO java_code
727 SELECT tbl_name,"insert","mapper","单条插入",@query_template1,"2","id不自增" FROM all_table;
728 
729 -- dao
730 SET @query_template1=
731 "
732 // 单条插入：id不自增
733 public void insert@tbl_name_camel@(@tbl_name_camel@ @tbl_name_ref_camel@){
734 
735     if(@tbl_name_ref_camel@ == null  ){
736         bizLogger.warn(" insert @tbl_name@  @tbl_name_ref_camel@ is null ");
737         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
738                 ResultInfo.SYS_INNER_ERROR.getDesc() + " @tbl_name_ref_camel@ is null  , bizId=" + BizLogUtils.getValueOfBizId());
739     }
740 
741     int insertResult =0;
742     try {
743         insertResult =  mapper.insert@tbl_name_camel@(@tbl_name_ref_camel@);
744     } catch (DuplicateKeyException e) {
745         bizLogger.error(" update @tbl_name@ duplicateKeyException ,@tbl_name_ref_camel@ : "
746                 + @tbl_name_ref_camel@.toString());
747         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),
748                 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e);
749     }
750     
751     if (insertResult!=1) {
752         bizLogger.warn("insert  @tbl_name@  result ！= 1 , @tbl_name_ref_camel@: "+@tbl_name_ref_camel@.toString());
753         throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
754     }   
755     
756 }
757 ";
758 
759 INSERT INTO java_code
760 SELECT tbl_name,"insert","dao","单条插入",@query_template1,"2","id不自增" FROM all_table;
761 
762 
763 -- ######################################################################################################
764 -- 批量插入
765 -- ######################################################################################################
766 SET @query_template1=
767 "
768 // 批量插入
769 @Insert({
770     "<script> ",
771         "insert into @tbl_name@ ( @col_list@ ) values",
772         "<foreach collection="itemList" item="item" index="index" open="(" separator="),(" close=")">",
773             "@insert_if_test@ ",
774         "</foreach>",
775     "</script>" 
776 })
777 int batchInsert@tbl_name_camel@(@Param("itemList") List<@tbl_name_camel@> @tbl_name_ref_camel@List);
778 ";
779 
780 
781 INSERT INTO java_code
782 SELECT tbl_name,"insert","mapper","批量插入", @query_template1,"1",""  FROM all_table;
783 
784 -- dao
785 
786 SET @query_template1=
787 "
788     // 批量插入
789     @SuppressWarnings("unchecked")
790     public int batchInsert@tbl_name_camel@(Object object) {
791         // 类型转换，支持单个对象或者集合形式作为入参
792         List<@tbl_name_camel@> list = null;
793         if (object instanceof @tbl_name_camel@) {
794             list = new ArrayList<>();
795             list.add((@tbl_name_camel@) object);
796         } else if (object instanceof List) {
797             for (Object o : (List<?>) object) {
798                 if (!(o instanceof @tbl_name_camel@)) {
799                     throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",error element: " + o.toString() + ",object type is error for batch insert" + BizLogUtils.getValueOfBizId());
800                 }
801             }
802             list = (List<@tbl_name_camel@>) object;
803         } else {
804             throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",object type is error for batch insert"  + BizLogUtils.getValueOfBizId());
805         }
806 
807         // 如果集合为空则报异常
808         if (list == null || list.size() == 0) {
809             throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",batch insert empty ,bizId="  + BizLogUtils.getValueOfBizId());
810         }
811 
812         // 插入阈值， 每多少条commit一次，默认是200条做一次。
813         int threshold = 200;
814 
815         int result = 0;
816         int sum = list.size();
817         int end = 0;
818         for (int i = 0; i < sum; i = i + threshold) {
819             end = i + threshold > sum ? sum : i + threshold;
820             try {
821                 result += mapper.batchInsert@tbl_name_camel@(list.subList(i, end));
822             } catch (Exception e) {
823                 //  根据业务做补偿机制，例如通过end值，将之前插入的值全部删除或者状态翻转为无效
824                 batchInsert@tbl_name_camel@FailOffset(list.subList(0, end));
825                 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+ ",end value： " + end + ",batch insert has error,offset [batch insert error] success ,bizId=" + BizLogUtils.getValueOfBizId(),  e);
826             }
827         }
828         return result;
829     }
830 
831     // 批量插入失败后，进行相关补偿操作
832     private void batchInsert@tbl_name_camel@FailOffset(List<@tbl_name_camel@> list) {
833 
834         //  补偿操作，可以比插入操作的阈值大一点， 每多少条commit一次，默认是400条做一次。
835         int threshold = 400;
836         int sum = list.size();
837         int end = 0;
838         for (int i = 0; i < sum; i = i + threshold) {
839             end = i + threshold > sum ? sum : i + threshold;
840             try {
841                 // TODO 批量插入失败后，需要进行补偿的操作，例如：将之前插入的值全部删除或者状态翻转为无效
842                 //List<Integer> idList = list.subList(i, end).stream().map(@tbl_name_camel@::getId).collect(Collectors.toList());
843                 //@tbl_name_camel@ @tbl_name_ref_camel@ForUpdate = new @tbl_name_camel@();
844                 //@tbl_name_ref_camel@ForUpdate.setxx();
845                 //update@tbl_name_camel@(idList,null,@tbl_name_ref_camel@ForUpdate);
846             } catch (Exception e) {
847                 // 如果做业务补偿的时候也失败了，只能将重要信息打印在日志里面，运维干预进行恢复了
848                 throw new BusinessException( ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ", [offset batch insert error]  failed ,"+ ",bizId: " + BizLogUtils.getValueOfBizId(), e);
849             }
850         }
851 
852     }
853 ";
854 
855 
856 INSERT INTO java_code
857 SELECT tbl_name,"insert","dao","批量插入", @query_template1,"1",""  FROM all_table;
858 
859 
860 
861 -- ######################################################################################################
862 -- pojo setter方法
863 -- ######################################################################################################
864 
865 INSERT INTO java_code
866 SELECT tbl_name,"pojo","setter","实体类赋值",pojo_code,"1",""
867 FROM (
868 SELECT tbl_name ,
869     (
870     SELECT CONCAT_WS("","/* 新建对象*/
","@tbl_name_camel@"," ","@tbl_name_ref_camel@","= new ","@tbl_name_camel@","();

/*设置属性*/
",
871                 group_concat(
872                    /* cdkmallGoodsApply.setUserUuid(userUuid); */
873                     CONCAT_WS( ""
874                         ,CONCAT_WS("","/*",c.column_comment," | ",c.column_type," | ",if(c.is_nullable="YES","可空","非空"),if(c.extra="","",CONCAT_WS(""," | ",c.extra))," | ","默认=",ifnull(c.COLUMN_DEFAULT,"null"),"  */ 
")
875                         ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME, CONCAT(@noStrInTbl,"_"), ""), "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","")  
876                          ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS("",".set","_",c.column_name), "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","") 
877                          ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS("","(",c.column_name,");"), "_z", "Z"), "_y", "Y"), "_x", "X"), "_w", "W"), "_v", "V"), "_u", "U"), "_t", "T"), "_s", "S"), "_r", "R"), "_q", "Q"), "_p", "P"), "_o", "O"), "_n", "N"), "_m", "M"), "_l", "L"), "_k", "K"), "_j", "J"), "_i", "I"), "_h", "H"), "_g", "G"), "_f", "F"), "_e", "E"), "_d", "D"), "_c", "C"), "_b", "B"), "_a", "A"),"_","")  
878                      ) SEPARATOR "
"
879                 )
880             ) as pojo_code
881     FROM
882         information_schema.COLUMNS c
883         WHERE
884         c.table_schema= DATABASE() AND 
885         c.TABLE_NAME = a.tbl_name
886     ) AS pojo_code
887 FROM all_table a
888 ) tt;
889 
890 -- ######################################################################################################
891 -- ######################################################################################################
892 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
893 -- 将模板中的@xx@占位符统一全部替换掉
894 UPDATE java_code j SET j.java_code=
895 (
896 SELECT 
897 REPLACE(
898 REPLACE(
899 REPLACE(
900 REPLACE(
901 REPLACE(
902 REPLACE(
903 REPLACE(
904 REPLACE(
905 REPLACE(
906 REPLACE(
907 REPLACE(
908 REPLACE(
909 REPLACE(
910 j.java_code,"@col_list_alias@",col_list_alias),
911 "@tbl_name@",tbl_name),
912 "@primary_key@",primary_key),
913 "@col_camel@",col_camel),
914 "@jdbc_type@",jdbc_type),
915 "@tbl_name_camel@",tbl_name_camel),
916 "@tbl_name_ref_camel@",tbl_name_ref_camel),
917 "@query_if_test@",query_if_test),
918 "@update_if_test@",update_if_test),
919 "@col_list@",col_list),
920 "@insert_if_test@",insert_if_test),
921 "@update_chase@",update_chase),
922 "@java_type@",java_type) AS code
923 FROM 
924 (
925 SELECT 
926 a.tbl_name,a.col_list_alias,a.primary_key,c.col_camel,c.jdbc_type,a.tbl_name_camel,c.java_type,a.query_if_test,a.tbl_name_ref_camel,a.update_if_test,a.update_chase
927 ,a.col_list,a.insert_if_test
928 FROM all_table a
929 JOIN all_col_table c
930 ON a.tbl_name=c.tbl_name 
931 WHERE a.primary_key = c.col  
932 ) t
933 WHERE j.tbl_name =t.tbl_name
934 );
935 
936 DELETE FROM java_code WHERE tbl_name NOT LIKE "tbl%";
937 
938 
939 DROP TABLE all_col_table;
940 DROP TABLE all_table;
941 
942 
943 -- select * from all_col_table;
944 -- select * from all_table;
945  SELECT * FROM java_code;
946  
947  /*
948  SELECT java_code FROM java_code WHERE tbl_name = "tbl_ams_award_pool" AND code_layer="mapper";
949 
950 SELECT java_code FROM java_code WHERE tbl_name = "tbl_ams_award_pool" AND code_layer="dao";
951 
952  */
953 
954 END$
955 DELIMITER ;
956 
957 
958 CALL print_code();
959 
960 
961 
962 -- 本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html

View Code

本文来自思创斯聊编程，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html

原文地址：https://www.cnblogs.com/wanglifeng717/archive/2022/05/03/16219565.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5311.html

mybatis通用功能代码生成工具「终于解决」

相关推荐

发表回复