百木园
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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>\" , \"<if test = \'updateObj.loginPassword !=null and updateObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>\" , \"<if test = \'updateObj.loginPassword !=null and updateObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>\" , \"<if test = \'updateObj.loginPassword !=null and updateObj.loginPassword !=''\'> 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 !=''\'> 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 !=''\'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR} </if>\" , \"<if test = \'queryObj.loginPassword !=null and queryObj.loginPassword !=''\'> 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 !=''\'> 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!='' 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,\' !=''\',\"\'\") 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,\' !=''\',\"\'\") 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 \'\\r\\n\') 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 \'\\r\\n\') 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(\'\',\'/* 新建对象*/\\r\\n\',\'@tbl_name_camel@\',\' \',\'@tbl_name_ref_camel@\',\'= new \',\'@tbl_name_camel@\',\'();\\r\\n\\r\\n/*设置属性*/\\r\\n\', 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\'),\' */ \\r\\n\') 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 \'\\r\\n\' 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/p/16219565.html
本站部分图文来源于网络，如有侵权请联系删除。