mybatis通用功能代码生成工具

• Z时代
• 2024-01-10
• 分类：综合

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")
publicint batchInsertSapoAdminAccount(Object object) {
// 类型转换，支持单个对象或者集合形式作为入参
        List<SapoAdminAccount> list = null;
if (object instanceof SapoAdminAccount) {
            list = new ArrayList<>();
            list.add((SapoAdminAccount) object);
        } elseif (object instanceof List) {
for (Object o : (List<?>) object) {
if (!(o instanceof SapoAdminAccount)) {
thrownew 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 {
thrownew 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) {
thrownew 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));
thrownew 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;
    }
// 批量插入失败后，进行相关补偿操作
privatevoid 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) {
// 如果做业务补偿的时候也失败了，只能将重要信息打印在日志里面，运维干预进行恢复了
thrownew BusinessException( ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ", [offset batch insert error]  failed ,"+ ",bizId: " + BizLogUtils.getValueOfBizId(), e);
            }
        }
    }
// 单条插入:id自增
publicint insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){
if(sapoAdminAccount == null  ){
        bizLogger.warn(" insert tbl_sapo_admin_account  sapoAdminAccount is null ");
thrownew 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());
thrownew 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());
thrownew BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }   
return insertResult;
}
// 单条插入：id不自增
publicvoid insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){
if(sapoAdminAccount == null  ){
        bizLogger.warn(" insert tbl_sapo_admin_account  sapoAdminAccount is null ");
thrownew 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());
thrownew 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());
thrownew 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 ");
thrownew 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());
thrownew 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 ");
thrownew 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());
thrownew 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 ");
thrownew 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());
thrownew BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
return sapoAdminAccountList;     
}
// 通过主键更新
publicvoid updateSapoAdminAccountByPrimaryKey(SapoAdminAccount sapoAdminAccountForUpdate){
if(sapoAdminAccountForUpdate == null){
        bizLogger.warn(" update tbl_sapo_admin_account  sapoAdminAccountForUpdate is null ");
thrownew 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());
thrownew 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更新
publicvoid 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");
thrownew 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 ");
thrownew 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);
thrownew 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");
thrownew 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());
thrownew BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId());
    }
return sapoAdminAccountList;     
}
// 通过条件更新
publicvoid updateSapoAdminAccount(SapoAdminAccount sapoAdminAccountForUpdate,SapoAdminAccount sapoAdminAccountForQuery){
if(sapoAdminAccountForUpdate == null || sapoAdminAccountForQuery==null ){
        bizLogger.warn(" update tbl_sapo_admin_account  sapoAdminAccountForUpdate or sapoAdminAccountForQuery is null ");
thrownew 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());
thrownew 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({ 
    "insertinto 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({ 
    "insertinto 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> ",
        "insertinto 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
  2DROPPROCEDUREIFEXISTS `print_code`;
  3DELIMITER $
  4CREATEPROCEDURE `print_code`()
  5BEGIN
  6
  7SET  group_concat_max_len =4294967295;
  8
  9
 10-- SET @noStrInTbl="tbl_ams";
 11SET@noStrInTbl="tbl";
 12
 13
 14-- 保存所有表及表的所有字段
 15DROPTABLEifEXISTS all_col_table;
 16CREATEtableifnotexists all_col_table(
 17 tbl_name VARCHAR(256)  NOTNULL  COMMENT "表名:tbl_sapo_admin_account",
 18 col VARCHAR(256) NOTNULL  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-- 将本库中所有表及所有字段插入表中
 34INSERTINTO all_col_table(tbl_name,col) 
 35SELECT
 36    t1.table_name, t1.column_name 
 37FROM
 38    information_schema.COLUMNS t1
 39WHERE
 40       t1.table_schema=DATABASE() ;
 41
 42-- 字段转驼峰
 43UPDATE 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      
 48UPDATE all_col_table a SET a.col_type =
 49(
 50SELECT t1.data_type
 51FROM
 52    information_schema.COLUMNS t1
 53WHERE
 54     t1.table_schema=DATABASE()  
 55and t1.TABLE_NAME = a.tbl_name 
 56and t1.column_name =a.col
 57);
 58-- select * from all_col_table;
 59
 60-- 转换成jdbc类型
 61UPDATE all_col_table SET jdbc_type=
 62case  col_type
 63when"datetime"then"TIMESTAMP"
 64when"tinyint"then"TINYINT"
 65when"bigint"then"BIGINT"
 66when"int"then"INTEGER"
 67when"float"then"REAL"
 68when"varchar"then"VARCHAR"
 69END;
 70
 71-- java类型转换
 72UPDATE all_col_table SET java_type=
 73case  col_type 
 74when"datetime"then"Date"
 75when"tinyint"then"Byte"
 76when"bigint"then"Long"
 77when"int"then"Integer"
 78when"varchar"then"String"
 79END;
 80
 81-- 组语句：create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} 
 82UPDATE all_col_table SET col_for_query_jdbc=CONCAT_WS("",col,"=#{queryObj.",col_camel,",jdbcType=",jdbc_type,"} ");
 83UPDATE all_col_table SET col_for_update_jdbc=CONCAT_WS("",col,"=#{updateObj.",col_camel,",jdbcType=",jdbc_type,"} ");
 84UPDATE 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!=''
 87UPDATE all_col_table SET if_test=
 88case  col_type
 89when"varchar"then CONCAT_WS("",""","queryObj.",col_camel,"!=nulland queryObj.",col_camel,"!=''",""") 
 90else CONCAT_WS("",""","queryObj.",col,"!=null",""")
 91END;
 92-- #######################################
 93UPDATE all_col_table SET update_if_test=
 94case  col_type
 95when"varchar"then CONCAT_WS("",""","updateObj.",col_camel,"!=nulland updateObj.",col_camel,"!=''",""") 
 96else CONCAT_WS("",""","updateObj.",col,"!=null",""")
 97END;
 98
 99
100-- 表相关数据
101DROPTABLEifEXISTS all_table;
102CREATEtableifnotexists all_table(
103 tbl_name VARCHAR(256)  NOTNULL  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-- 把所有表入库
119INSERTINTO all_table(tbl_name) 
120SELECT
121    t1.table_name
122FROM
123    information_schema.tables t1
124WHERE
125       t1.table_schema=DATABASE() AND t1.TABLE_NAME NOTIN("all_col_table","all_table");
126
127-- 表名转驼峰
128UPDATE 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"),"_","") ;
129UPDATE 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-- 更新主键
137UPDATE all_table a SET  a.primary_key=
138 (SELECT
139column_name
140FROM information_schema.columns t1
141WHERE
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
146UPDATE all_table a SET a.col_list_alias=
147(
148SELECT GROUP_CONCAT(
149     CONCAT_WS("","t.",col," as ",col)
150 ) FROM all_col_table WHERE tbl_name = a.tbl_name
151);
152-- #######################################
153UPDATE all_table a SET a.col_list=
154(
155SELECT 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>",
158UPDATE all_table a SET a.query_if_test=
159(
160SELECT
161        GROUP_CONCAT(
162             CONCAT_WS("",""<if test = ",if_test,"> and ",col_for_query_jdbc," </if>" ,")
163         SEPARATOR "
") 
164FROM all_col_table WHERE tbl_name = a.tbl_name
165);
166
167-- #######################################
168UPDATE all_table a SET a.update_if_test=
169(
170SELECT
171        GROUP_CONCAT(
172             CONCAT_WS("",""<if test = ",update_if_test,">  ",col_for_update_jdbc,", </if>" ,")
173         SEPARATOR "
") 
174FROM all_col_table WHERE tbl_name = a.tbl_name AND a.primary_key!=col
175);
176
177-- #######################################
178UPDATE all_table a SET a.insert_if_test=
179(
180SELECT
181        GROUP_CONCAT(col_for_insert_jdbc) 
182FROM all_col_table WHERE tbl_name = a.tbl_name
183);
184
185
186
187-- #######################################
188-- 更新update_chase
189UPDATE all_table a SET a.update_chase=
190(
191SELECT
192        GROUP_CONCAT( col_for_update_jdbc ) 
193FROM 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-- 保存所有表及表的所有字段
207DROPTABLEifEXISTS java_code;
208CREATEtableifnotexists java_code(
209 tbl_name VARCHAR(256)  NOTNULL  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
225SET@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
242INSERTINTO java_code
243SELECT tbl_name,"select","mapper","通用查询，返回对象",@query_template1,"1",""FROM all_table;
244
245-- dao层语句
246SET@query_template1=
247"
248// 通用查询，返回对象
249public @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
269INSERTINTO java_code
270SELECT tbl_name,"select","dao","通用查询,返回对象",@query_template1,"1",""FROM all_table;
271
272
273-- ######################################################################################################
274-- 通用查询，返回集合
275-- ######################################################################################################
276
277SET@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})
291List<@tbl_name_camel@> get@tbl_name_camel@List(@Param("queryObj") @tbl_name_camel@ @tbl_name_ref_camel@ForQuery);
292";
293
294INSERTINTO java_code
295SELECT tbl_name,"select","mapper","通用查询，返回集合",@query_template1,"1",""FROM all_table;
296
297-- dao层
298SET@query_template1=
299"
300// 通用查询，返回集合
301public 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
321INSERTINTO java_code
322SELECT 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
329SET@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
340INSERTINTO java_code
341SELECT tbl_name,"select","mapper","通过主键查询",@query_template1,"1",""FROM  all_table;
342
343
344-- dao层
345SET@query_template1=
346"
347// 通用主键查询，返回对象
348public @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
370INSERTINTO java_code
371SELECT tbl_name,"select","dao","通用主键查询，返回集合",@query_template1,"1",""FROM all_table;
372
373
374-- ######################################################################################################
375-- 通过条件和主键in查询,返回集合
376-- ######################################################################################################
377
378
379SET@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 !=nulland 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})
398List<@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
401INSERTINTO java_code
402SELECT tbl_name,"select","mapper","通过条件和主键in查询,返回集合",@query_template1,"1",""FROM  all_table;
403
404
405-- dao层
406SET@query_template1=
407"
408// 通过条件和主键in查询,返回集合
409public 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
429INSERTINTO java_code
430SELECT 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
447SET@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})
455int update@tbl_name_camel@ByPrimaryKey(@Param("updateObj") @tbl_name_camel@ @tbl_name_ref_camel@ForUpdate);
456";
457
458
459INSERTINTO java_code
460SELECT tbl_name,"update","mapper","通过主键更新",@query_template1,"1",""FROM all_table;
461
462-- dao
463
464SET@query_template1=
465"
466// 通过主键更新
467public 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
496INSERTINTO java_code
497SELECT tbl_name,"update","dao","通过主键更新",@query_template1,"1",""FROM all_table;
498
499-- ######################################################################################################
500-- 通过条件更新
501-- ######################################################################################################
502
503
504SET@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})
522int 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
526INSERTINTO java_code
527SELECT tbl_name,"update","mapper","通过条件更新",@query_template1,"1",""FROM all_table;
528
529-- dao
530SET@query_template1=
531"
532// 通过条件更新
533public 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
561INSERTINTO java_code
562SELECT tbl_name,"update","dao","通过条件更新",@query_template1,"1",""FROM all_table;
563
564
565
566-- ######################################################################################################
567-- 通过条件和主键in更新
568-- ######################################################################################################
569
570
571SET@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 !=nulland 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})
594int 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
598INSERTINTO java_code
599SELECT tbl_name,"update","mapper","通过条件和主键in更新",@query_template1,"1",""FROM all_table;
600
601-- dao
602
603SET@query_template1=
604"
605// 通过条件和主键in更新
606public 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
641INSERTINTO java_code
642SELECT 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
658SET@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")
668int insert@tbl_name_camel@(@Param("item") @tbl_name_camel@ @tbl_name_ref_camel@);
669";
670
671
672INSERTINTO java_code
673SELECT tbl_name,"insert","mapper","单条插入",@query_template1,"1","id自增"FROM all_table;
674
675-- dao
676SET@query_template1=
677"
678// 单条插入:id自增
679public 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
706INSERTINTO java_code
707SELECT 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
713SET@query_template1=
714"
715// 单条插入：id不自增
716@Insert({ 
717    "insert into @tbl_name@ ",
718    "(@col_list@)",
719    "values ",
720    "(@insert_if_test@) "
721})
722int insert@tbl_name_camel@(@Param("item") @tbl_name_camel@ @tbl_name_ref_camel@);
723";
724
725
726INSERTINTO java_code
727SELECT tbl_name,"insert","mapper","单条插入",@query_template1,"2","id不自增"FROM all_table;
728
729-- dao
730SET@query_template1=
731"
732// 单条插入：id不自增
733public 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
759INSERTINTO java_code
760SELECT tbl_name,"insert","dao","单条插入",@query_template1,"2","id不自增"FROM all_table;
761
762
763-- ######################################################################################################
764-- 批量插入
765-- ######################################################################################################
766SET@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})
777int batchInsert@tbl_name_camel@(@Param("itemList") List<@tbl_name_camel@> @tbl_name_ref_camel@List);
778";
779
780
781INSERTINTO java_code
782SELECT tbl_name,"insert","mapper","批量插入", @query_template1,"1",""FROM all_table;
783
784-- dao
785
786SET@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
856INSERTINTO java_code
857SELECT tbl_name,"insert","dao","批量插入", @query_template1,"1",""FROM all_table;
858
859
860
861-- ######################################################################################################
862-- pojo setter方法
863-- ######################################################################################################
864
865INSERTINTO java_code
866SELECT tbl_name,"pojo","setter","实体类赋值",pojo_code,"1",""
867FROM (
868SELECT tbl_name ,
869    (
870SELECT 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
881FROM
882        information_schema.COLUMNS c
883WHERE
884         c.table_schema=DATABASE() AND
885         c.TABLE_NAME = a.tbl_name
886     ) AS pojo_code
887FROM all_table a
888) tt;
889
890-- ######################################################################################################
891-- ######################################################################################################
892-- 本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
893-- 将模板中的@xx@占位符统一全部替换掉
894UPDATE java_code j SET j.java_code=
895(
896SELECT
897REPLACE(
898REPLACE(
899REPLACE(
900REPLACE(
901REPLACE(
902REPLACE(
903REPLACE(
904REPLACE(
905REPLACE(
906REPLACE(
907REPLACE(
908REPLACE(
909REPLACE(
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
923FROM
924(
925SELECT
926a.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
928FROM all_table a
929JOIN all_col_table c
930ON a.tbl_name=c.tbl_name 
931WHERE a.primary_key = c.col  
932) t
933WHERE j.tbl_name =t.tbl_name
934);
935
936DELETEFROM java_code WHERE tbl_name NOTLIKE"tbl%";
937
938
939DROPTABLE all_col_table;
940DROPTABLE all_table;
941
942
943-- select * from all_col_table;
944-- select * from all_table;
945SELECT*FROM java_code;
946
947/*
948 SELECT java_code FROM java_code WHERE tbl_name = "tbl_ams_award_pool" AND code_layer="mapper";
949
950SELECT java_code FROM java_code WHERE tbl_name = "tbl_ams_award_pool" AND code_layer="dao";
951
952*/
953
954END$
955DELIMITER ;
956
957
958CALL 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

以上是 mybatis通用功能代码生成工具 的全部内容， 来源链接： utcz.com/z/536471.html