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 + @[email protected]()); 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 || @[email protected]()==0){ 312 bizLogger.warn(" select @tbl_name@ List is null or size=0 ,@tbl_name_ref_camel@ForQuery : " 313 + @[email protected]()); 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 + @[email protected]()); 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 || @[email protected]()==0){ 420 bizLogger.warn(" select @tbl_name@ ,but result list is null or size=0 ,@tbl_name_ref_camel@ForQuery : " 421 + @[email protected]()+"; @col_camel@ListForQuery : "+@[email protected]()); 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 + @[email protected]()); 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+","+ @[email protected]()); 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 + @[email protected]()); 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+","+ @[email protected]()); 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 + @[email protected]()+" ; @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+","+ @[email protected]()+","+@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 + @[email protected]()); 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@: "+@[email protected]()); 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 + @[email protected]()); 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@: "+@[email protected]()); 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 //@[email protected](); 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.htmlView Code
本文來自博客園，作者：wanglifeng，轉載請注明原文鏈接：https://www.cnblogs.com/wanglifeng717/p/16219565.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/469634.html
標籤：其他
上一篇：dba+開源工具：MHA復刻版，輕松實作MySQL高可用故障轉移（附下載）
下一篇：MySQL 日志管理