mybatis通用功能程式碼生成工具

 

mybatis操作資料庫的過程中,如果只考慮單表操作,mapper和dao層基本80%的都是固定的,故而可以使用工具進行生成,文末提供自己編寫的工具(基於mysql存儲過程):
作者其實就是使用(mybatis-generator)這個工具過程中,有些想法,實踐下,編寫時很多實現留了口子,後續方便集成到開發框架中。

工具提供 mapper,dao層功能如下: 

通用查詢,返回對象
通用查詢,返回集合
通用主鍵查詢,返回集合
通過條件和主鍵in查詢,返回集合
通過主鍵更新
通過條件更新
通過條件和主鍵in更新
單條插入,id自增
單條插入,id不自增
批量插入

(如需訂製化生成程式碼,請翻閱前幾篇文章,本文僅將通用性程式碼抽取出來://www.cnblogs.com/wanglifeng717/p/15839391.html)

  • 1.查詢部分示例

因為查詢根據不同條件sql不同,可以使用動態語句。使用對象拼接查詢條件。此時mapper層只需要一個方法。(工具自動生成程式碼如下)

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

 

  • 2.更新部分示例

更新的前提基本都是已經查出來該記錄,直接根據主鍵更新即可。並沒有很多花樣。(工具自動生成程式碼如下)

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

如果更新的條件是不確定的,更新的內容也不確定,可以使用動態語句,基本一個更新語句包打天下(工具自動生成程式碼如下:)

// 通過條件更新
@Update({ 
"<script> ",
"update tbl_sapo_admin_account ",
"<set>",
"<if test='updateObj!=null'>",
"<if test = 'updateObj.create_time!=null'>  create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = 'updateObj.last_update_time!=null'>  last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>" ,
"<if test = 'updateObj.loginName !=null and updateObj.loginName !=&apos;&apos;'>  login_name=#{updateObj.loginName,jdbcType=VARCHAR} , </if>" ,
"<if test = 'updateObj.loginPassword !=null and updateObj.loginPassword !=&apos;&apos;'>  login_password=#{updateObj.loginPassword,jdbcType=VARCHAR} , </if>" ,
"<if test = 'updateObj.status!=null'>  status=#{updateObj.status,jdbcType=INTEGER} , </if>" ,
"<if test = 'updateObj.remark !=null and updateObj.remark !=&apos;&apos;'>  remark=#{updateObj.remark,jdbcType=VARCHAR} , </if>" ,
"<if test = 'updateObj.admin_user_id!=null'>  admin_user_id=#{updateObj.adminUserId,jdbcType=INTEGER} , </if>" ,
"</if>",
"</set>",
"<where>",
"<if test='queryObj!=null'>",
"<if test = 'queryObj.id!=null'> and id=#{queryObj.id,jdbcType=INTEGER}  </if>" ,
"<if test = 'queryObj.create_time!=null'> and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = 'queryObj.last_update_time!=null'> and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP}  </if>" ,
"<if test = 'queryObj.loginName !=null and queryObj.loginName !=&apos;&apos;'> and login_name=#{queryObj.loginName,jdbcType=VARCHAR}  </if>" ,
"<if test = 'queryObj.loginPassword !=null and queryObj.loginPassword !=&apos;&apos;'> and login_password=#{queryObj.loginPassword,jdbcType=VARCHAR}  </if>" ,
"<if test = 'queryObj.status!=null'> and status=#{queryObj.status,jdbcType=INTEGER}  </if>" ,
"<if test = 'queryObj.remark !=null and queryObj.remark !=&apos;&apos;'> and remark=#{queryObj.remark,jdbcType=VARCHAR}  </if>" ,
"<if test = 'queryObj.admin_user_id!=null'> and admin_user_id=#{queryObj.adminUserId,jdbcType=INTEGER}  </if>" ,
"</if>",
"</where>",
"</script>" 
})
int updateSapoAdminAccount(@Param("updateObj") SapoAdminAccount sapoAdminAccountForUpdate,@Param("queryObj") SapoAdminAccount sapoAdminAccountForQuery);
  • 3.插入部分示例
// 單條插入:id自增
@Insert({ 
    "insert into tbl_sapo_admin_account ",
    "(id,create_time,last_update_time,login_name,login_password,status,remark,admin_user_id)",
    "values ",
    "(#{item.id,jdbcType=INTEGER} ,#{item.createTime,jdbcType=TIMESTAMP} ,#{item.lastUpdateTime,jdbcType=TIMESTAMP} ,#{item.loginName,jdbcType=VARCHAR} ,#{item.loginPassword,jdbcType=VARCHAR} ,#{item.status,jdbcType=INTEGER} ,#{item.remark,jdbcType=VARCHAR} ,#{item.adminUserId,jdbcType=INTEGER} ) "
})
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insertSapoAdminAccount(@Param("item") SapoAdminAccount sapoAdminAccount);

 

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

 

工具生成dao層程式碼示例:

    // 批量插入
    @SuppressWarnings("unchecked")
    public int batchInsertSapoAdminAccount(Object object) {
        // 類型轉換,支援單個對象或者集合形式作為入參
        List<SapoAdminAccount> list = null;
        if (object instanceof SapoAdminAccount) {
            list = new ArrayList<>();
            list.add((SapoAdminAccount) object);
        } else if (object instanceof List) {
            for (Object o : (List<?>) object) {
                if (!(o instanceof SapoAdminAccount)) {
                    throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",error element: " + o.toString() + ",object type is error for batch insert" + BizLogUtils.getValueOfBizId());
                }
            }
            list = (List<SapoAdminAccount>) object;
        } else {
            throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",object type is error for batch insert"  + BizLogUtils.getValueOfBizId());
        }

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

        // 插入閾值, 每多少條commit一次,默認是200條做一次。
        int threshold = 200;

        int result = 0;
        int sum = list.size();
        int end = 0;
        for (int i = 0; i < sum; i = i + threshold) {
            end = i + threshold > sum ? sum : i + threshold;
            try {
                result += mapper.batchInsertSapoAdminAccount(list.subList(i, end));
            } catch (Exception e) {
                //  根據業務做補償機制,例如通過end值,將之前插入的值全部刪除或者狀態翻轉為無效
                batchInsertSapoAdminAccountFailOffset(list.subList(0, end));
                throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+ ",end value: " + end + ",batch insert has error,offset [batch insert error] success ,bizId=" + BizLogUtils.getValueOfBizId(),  e);
            }
        }
        return result;
    }

    // 批量插入失敗後,進行相關補償操作
    private void batchInsertSapoAdminAccountFailOffset(List<SapoAdminAccount> list) {

        //  補償操作,可以比插入操作的閾值大一點, 每多少條commit一次,默認是400條做一次。
        int threshold = 400;
        int sum = list.size();
        int end = 0;
        for (int i = 0; i < sum; i = i + threshold) {
            end = i + threshold > sum ? sum : i + threshold;
            try {
                // TODO 批量插入失敗後,需要進行補償的操作,例如:將之前插入的值全部刪除或者狀態翻轉為無效
                //List<Integer> idList = list.subList(i, end).stream().map(SapoAdminAccount::getId).collect(Collectors.toList());
                //SapoAdminAccount sapoAdminAccountForUpdate = new SapoAdminAccount();
                //sapoAdminAccountForUpdate.setxx();
                //updateSapoAdminAccount(idList,null,sapoAdminAccountForUpdate);
            } catch (Exception e) {
                // 如果做業務補償的時候也失敗了,只能將重要資訊列印在日誌裡面,運維干預進行恢復了
                throw new BusinessException( ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ", [offset batch insert error]  failed ,"+ ",bizId: " + BizLogUtils.getValueOfBizId(), e);
            }
        }

    }


// 單條插入:id自增
public int insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){

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

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


// 單條插入:id不自增
public void insertSapoAdminAccount(SapoAdminAccount sapoAdminAccount){

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

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


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


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


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


// 通過主鍵更新
public void updateSapoAdminAccountByPrimaryKey(SapoAdminAccount sapoAdminAccountForUpdate){

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

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


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


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


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

View Code

工具生成mapper層程式碼示例:

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


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


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


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


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


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


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


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


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


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

View Code

工具程式碼:

  1 -- 本文來自部落格園,作者:wanglifeng,轉載請註明原文鏈接://www.cnblogs.com/wanglifeng717/p/16219565.html
  2 DROP PROCEDURE IF EXISTS `print_code`;
  3 DELIMITER $
  4 CREATE  PROCEDURE `print_code`()
  5 BEGIN
  6 
  7 SET  group_concat_max_len = 4294967295;
  8 
  9 
 10 -- SET @noStrInTbl='tbl_ams';
 11  SET @noStrInTbl='tbl';
 12 
 13 
 14 -- 保存所有表及表的所有欄位
 15 DROP TABLE if EXISTS all_col_table;
 16 CREATE table if not exists all_col_table(
 17 tbl_name VARCHAR(256)  NOT NULL  COMMENT '表名:tbl_sapo_admin_account',
 18 col VARCHAR(256) NOT NULL  COMMENT '欄位名:create_time',
 19 col_camel VARCHAR(256) COMMENT '欄位駝峰形式:createTime',
 20 col_type VARCHAR(256) COMMENT '欄位類型,datetime',
 21 java_type VARCHAR(256) COMMENT 'java類型,datetime',
 22 jdbc_type VARCHAR(256) COMMENT 'jdbc類型:datetime->TIMESTAMP',
 23 if_test VARCHAR(1024) COMMENT 'queryObj.create_time!=null',
 24 update_if_test VARCHAR(1024) COMMENT 'updateObj.create_time!=null',
 25 col_for_query_jdbc VARCHAR(256) COMMENT 'create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} ',
 26 col_for_update_jdbc VARCHAR(256) COMMENT 'create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} ',
 27 col_for_insert_jdbc VARCHAR(256) COMMENT '#{item.createTime,jdbcType=TIMESTAMP} ',
 28 col_comment VARCHAR(512) COMMENT '欄位注釋'
 29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 30 
 31 -- select * from all_col_table;
 32 
 33 -- 將本庫中所有表及所有欄位插入表中
 34 INSERT INTO all_col_table(tbl_name,col) 
 35 SELECT 
 36     t1.table_name, t1.column_name 
 37 FROM
 38     information_schema.COLUMNS t1
 39 WHERE
 40       t1.table_schema= DATABASE() ;
 41 
 42 -- 欄位轉駝峰
 43 UPDATE all_col_table SET col_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') 
 44 ,'');
 45 
 46 
 47 -- 更新欄位類型id  --> int ,name  -->varchar      
 48 UPDATE all_col_table a SET a.col_type = 
 49 (
 50 SELECT t1.data_type
 51 FROM
 52     information_schema.COLUMNS t1
 53 WHERE
 54     t1.table_schema= DATABASE()  
 55     and t1.TABLE_NAME = a.tbl_name 
 56     and t1.column_name =a.col
 57 );
 58 -- select * from all_col_table;
 59 
 60 -- 轉換成jdbc類型
 61 UPDATE all_col_table SET jdbc_type=
 62 case  col_type
 63     when 'datetime' then 'TIMESTAMP' 
 64     when 'tinyint'  then 'TINYINT' 
 65     when 'bigint'   then 'BIGINT' 
 66     when 'int'      then 'INTEGER'
 67     when 'float'      then 'REAL' 
 68     when 'varchar'  then 'VARCHAR' 
 69 END;
 70 
 71 -- java類型轉換
 72 UPDATE all_col_table SET java_type=
 73 case  col_type 
 74     when 'datetime' then 'Date' 
 75     when 'tinyint'  then 'Byte' 
 76     when 'bigint'   then 'Long' 
 77     when 'int'      then 'Integer' 
 78     when 'varchar'  then 'String' 
 79 END;
 80 
 81 -- 組語句:create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} 
 82 UPDATE all_col_table SET col_for_query_jdbc=CONCAT_WS('',col,'=#{queryObj.',col_camel,',jdbcType=',jdbc_type,'} ');
 83 UPDATE all_col_table SET col_for_update_jdbc=CONCAT_WS('',col,'=#{updateObj.',col_camel,',jdbcType=',jdbc_type,'} ');
 84 UPDATE all_col_table SET col_for_insert_jdbc=CONCAT_WS('','#{item.',col_camel,',jdbcType=',jdbc_type,'} ');
 85 
 86 -- 組語句:queryObj.java_desc!=null and queryObj.java_desc!=&apos;&apos;
 87 UPDATE all_col_table SET if_test=
 88 case  col_type
 89     when 'varchar'  then CONCAT_WS('',"'",'queryObj.',col_camel,' !=null and queryObj.',col_camel,' !=&apos;&apos;',"'") 
 90     else CONCAT_WS('',"'",'queryObj.',col,'!=null',"'")
 91 END;
 92 -- #######################################
 93 UPDATE all_col_table SET update_if_test=
 94 case  col_type
 95     when 'varchar'  then CONCAT_WS('',"'",'updateObj.',col_camel,' !=null and updateObj.',col_camel,' !=&apos;&apos;',"'") 
 96     else CONCAT_WS('',"'",'updateObj.',col,'!=null',"'")
 97 END;
 98 
 99 
100 -- 表相關數據
101 DROP TABLE if EXISTS all_table;
102 CREATE table if not exists all_table(
103 tbl_name VARCHAR(256)  NOT NULL  COMMENT '表名:tbl_sapo_admin_account',
104 primary_key VARCHAR(255) COMMENT '主鍵',
105 tbl_name_camel VARCHAR(1024) COMMENT '表名駝峰:SapoAdminAccount',
106 tbl_name_ref_camel VARCHAR(1024) COMMENT '表名引用駝峰:sapoAdminAccount',
107 col_list TEXT COMMENT '欄位列表',
108 col_list_alias TEXT COMMENT '欄位別名列表',
109 insert_if_test TEXT COMMENT 'insert語句',
110 query_if_test TEXT COMMENT 'queryTest語句',
111 update_chase TEXT COMMENT 'update固定語句',
112 update_if_test TEXT COMMENT 'updateTest語句'
113 ) ENGINE=InnoDB ;
114 
115 
116 
117 
118 -- 把所有表入庫
119 INSERT INTO all_table(tbl_name) 
120 SELECT 
121     t1.table_name
122 FROM
123     information_schema.tables t1
124 WHERE
125       t1.table_schema= DATABASE() AND t1.TABLE_NAME NOT IN('all_col_table','all_table');
126       
127       -- 表名轉駝峰
128 UPDATE all_table SET tbl_name_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, @noStrInTbl, ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ;
129 UPDATE all_table SET tbl_name_ref_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, CONCAT(@noStrInTbl,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ;
130 
131 
132  
133  
134  
135  
136 -- 更新主鍵
137 UPDATE all_table a SET  a.primary_key=
138 (SELECT 
139 column_name
140 FROM information_schema.columns t1
141 WHERE 
142  t1.table_schema= DATABASE() AND t1.COLUMN_KEY='PRI' AND a.tbl_name=table_name 
143  );
144 
145 -- 更新每個表的欄位列表 t.id as id,t.create_time as create_time,t.last_update_time as last_update_time
146 UPDATE all_table a SET a.col_list_alias=
147 (
148 SELECT GROUP_CONCAT(
149     CONCAT_WS('','t.',col,' as ',col)
150 ) FROM all_col_table WHERE tbl_name = a.tbl_name
151 );
152 -- #######################################
153 UPDATE all_table a SET a.col_list=
154 (
155 SELECT GROUP_CONCAT( col ) FROM all_col_table WHERE tbl_name = a.tbl_name
156 );
157 -- 更新結果為:"<if test = 'queryObj.id!=null '>  and id=#{queryObj.id,jdbcType=INTEGER}   </if>",
158 UPDATE all_table a SET a.query_if_test=
159 (
160 SELECT 
161         GROUP_CONCAT(
162             CONCAT_WS('','"<if test = ',if_test,'> and ',col_for_query_jdbc,' </if>" ,')
163         SEPARATOR '\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,轉載請註明原文鏈接://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,轉載請註明原文鏈接://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,轉載請註明原文鏈接://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,轉載請註明原文鏈接://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,轉載請註明原文鏈接://www.cnblogs.com/wanglifeng717/p/16219565.html

View Code

 

本文來自部落格園,作者:wanglifeng,轉載請註明原文鏈接://www.cnblogs.com/wanglifeng717/p/16219565.html