mybatis操作数据库的过程中，如果只考虑单表操作，mapper和dao层基本80%的都是固定的，故而可以使用工具进行生成，文末提供自己编写的工具(基于mysql存储过程)： 作者其实就是使用(my
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层代码示例
@Repository public class SapoActDao extends BaseLogger{ @Autowired protected SapoActMapper mapper; // 通用查询,返回对象,对象不为空，否则报错 public SapoAct getSapoAct(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } SapoAct sapoAct = mapper.getSapoAct(sapoActForQuery); if(sapoAct == null){ bizLogger.warn(" select tbl_sapo_act result is null ,sapoActForQuery : " + sapoActForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } return sapoAct; } // 通用查询,返回对象，对象可为空 public SapoAct getSapoActWithNull(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } SapoAct sapoAct = mapper.getSapoAct(sapoActForQuery); return sapoAct; } // 通用查询,返回集合,集合不为空，否则报错 public List<SapoAct> getSapoActList(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActList(sapoActForQuery); if(sapoActList == null || sapoActList.size()==0){ bizLogger.warn(" select tbl_sapo_act List is null or size=0 ,sapoActForQuery : " + sapoActForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通用查询,返回集合,集合可以为空 public List<SapoAct> getSapoActListWithNull(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActList(sapoActForQuery); // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通用分页查询，返回当前页集合,集合不能为空，否则报错 public Page<SapoAct> getSapoActListByPage(Integer pageNum, Integer pageSize, SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListByPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } Page<SapoAct> pageList=null; // 默认按照create_time 降序排列。即刚创建的在前面显示 try { PageHelper.startPage(pageNum, pageSize," create_time desc "); pageList = (Page<SapoAct>) getSapoActList(sapoActForQuery); } finally { PageHelper.clearPage(); } if(pageList == null || pageList.size()==0 ){ bizLogger.warn(" select tbl_sapo_act List by page is null or size=0 ,[sapoActForQuery,pageNum,pageSize ]: " + sapoActForQuery.toString()+" ; "+pageNum+" ; "+pageSize); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } // service层可以从Page中获取总条数 return pageList; } // 通用分页查询，返回当前页集合,集合不能为空，否则报错 public Page<SapoAct> getSapoActListByPageWithNull(Integer pageNum, Integer pageSize, SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListByPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } Page<SapoAct> pageList=null; // 默认按照create_time 降序排列。即刚创建的在前面显示 try { PageHelper.startPage(pageNum, pageSize," create_time desc "); pageList = (Page<SapoAct>) getSapoActList(sapoActForQuery); } finally { PageHelper.clearPage(); } // service层可以从Page中获取总条数 return pageList; } // 通用分页模糊查询，返回当前页集合，集合不为空，否则报错 public Page<SapoAct> getSapoActListByFuzzyByPage(Integer pageNum, Integer pageSize, SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListByFuzzyByPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } Page<SapoAct> pageList=null; // 默认按照create_time 降序排列。即刚创建的在前面显示 try { PageHelper.startPage(pageNum, pageSize," create_time desc "); pageList = (Page<SapoAct>) getSapoActListByFuzzy(sapoActForQuery); } finally { PageHelper.clearPage(); } if(pageList == null || pageList.size()==0 ){ bizLogger.warn(" select tbl_sapo_act List by fuzzy by page is null or size=0 ,[sapoActForQuery,pageNum,pageSize ]: " + sapoActForQuery.toString()+" ; "+pageNum+" ; "+pageSize); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } // service层可以从Page中获取总条数 return pageList; } // 通用分页模糊查询，返回当前页集合，集合可空 public Page<SapoAct> getSapoActListByFuzzyByPageWithNull(Integer pageNum, Integer pageSize, SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListByFuzzyByPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } Page<SapoAct> pageList=null; // 默认按照create_time 降序排列。即刚创建的在前面显示 try { PageHelper.startPage(pageNum, pageSize," create_time desc "); pageList = (Page<SapoAct>) getSapoActListByFuzzy(sapoActForQuery); } finally { PageHelper.clearPage(); } // service层可以从Page中获取总条数 return pageList; } // 分页形式获取表全部数据，放在一个集合中，集合不能为空，否则报错 public List<SapoAct> getSapoActListAllPage( SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListAllPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } int pageNum = 1; // 分页默认每次拿500条 int pageSize = 500; Page<SapoAct> page = null; List<SapoAct> sapoActList= new ArrayList<SapoAct>(500); // 循环分页获取每页数据放入集合中 try { do { page = PageHelper.startPage(pageNum, pageSize); sapoActList.addAll(getSapoActList(sapoActForQuery)); pageNum++; } while (page.getPages() >= pageNum); } finally { PageHelper.clearPage(); } if( sapoActList.size()==0 ){ bizLogger.warn(" select tbl_sapo_act List all page is null or size=0 ,[sapoActForQuery,pageNum,pageSize ]: " + sapoActForQuery.toString()+" ; "+pageNum+" ; "+pageSize); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } return sapoActList; } // 分页形式获取表全部数据，放在一个集合中，集合可空 public List<SapoAct> getSapoActListAllPageWithNull( SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act , but dao layer getSapoActListAllPage input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } int pageNum = 1; // 分页默认每次拿500条 int pageSize = 500; Page<SapoAct> page = null; List<SapoAct> sapoActList= new ArrayList<SapoAct>(500); // 循环分页获取每页数据放入集合中 try { do { page = PageHelper.startPage(pageNum, pageSize); sapoActList.addAll(getSapoActList(sapoActForQuery)); pageNum++; } while (page.getPages() >= pageNum); } finally { PageHelper.clearPage(); } return sapoActList; } // 通用模糊查询(所有varchar字段都模糊),返回集合,集合不为空，否则报错 public List<SapoAct> getSapoActListByFuzzy(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act by fuzzy , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActListByFuzzy(sapoActForQuery); if(sapoActList == null || sapoActList.size()==0){ bizLogger.warn(" select tbl_sapo_act by fuzzy but result List is null or size=0 ,sapoActForQuery : " + sapoActForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通用模糊查询(所有varchar字段都模糊),返回集合,集合可空 public List<SapoAct> getSapoActListByFuzzyWithNull(SapoAct sapoActForQuery){ if(sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" select tbl_sapo_act by fuzzy , but dao layer input sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActListByFuzzy(sapoActForQuery); // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通用主键查询，返回对象 public SapoAct getSapoActByPrimaryKey(Integer id){ if(id == null){ bizLogger.warn(" select tbl_sapo_act , but dao layer input id is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer id is null , bizId=" + BizLogUtils.getValueOfBizId()); } SapoAct sapoAct = mapper.getSapoActByPrimaryKey(id); if(sapoAct == null){ bizLogger.warn(" select tbl_sapo_act by primary key ,but find null ,id : " + id.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } return sapoAct; } // 通过主键删除 public void deleteSapoActByPrimaryKey(Integer id){ if(id == null){ bizLogger.warn(" delete tbl_sapo_act , but dao layer id input is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer id is null , bizId=" + BizLogUtils.getValueOfBizId()); } int deleteResult = mapper.deleteSapoActByPrimaryKey(id); if (deleteResult == 0) { bizLogger.warn("delete tbl_sapo_act result == 0 [deleteResult, id] : "+deleteResult+","+ id.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 通过条件和主键in查询,返回集合,集合不为空，否则报错 public List<SapoAct> getSapoActList( List<Integer> idListForQuery, SapoAct sapoActForQuery){ if((idListForQuery == null || idListForQuery.size()==0 ) && (sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery))){ bizLogger.warn(" select tbl_sapo_act idListForQuery && sapoActForQuery is null at same time"); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer input idListForQuery and sapoActForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActListByIdList(idListForQuery,sapoActForQuery); if(sapoActList == null || sapoActList.size()==0){ bizLogger.warn(" select tbl_sapo_act , but result list is null or size=0 ,sapoActForQuery : " + sapoActForQuery.toString()+"; idListForQuery : "+idListForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通过条件和主键in查询,返回集合,集合不为空，否则报错 public List<SapoAct> getSapoActListWithNull( List<Integer> idListForQuery, SapoAct sapoActForQuery){ if((idListForQuery == null || idListForQuery.size()==0 ) && (sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery))){ bizLogger.warn(" select tbl_sapo_act idListForQuery && sapoActForQuery is null at same time"); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer input idListForQuery and sapoActForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); } List<SapoAct> sapoActList = mapper.getSapoActListByIdList(idListForQuery,sapoActForQuery); // 默认按照createTime降序排列，即刚创建的在前面显示 //Comparator<SapoAct> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); //sapoActList.sort(comparator); if(sapoActList !=null){ sapoActList.sort(new Comparator<SapoAct>() { public int compare(SapoAct o1, SapoAct o2) { return o2.getCreateTime().compareTo(o1.getCreateTime()); } }); } // sapoActList.forEach(System.out::println); /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 public int compare(Integer t1, Integer t2) { return t1-t2; //升序 } public int compare(Integer t1, Integer t2) { return t2-t1; //降序 } t1- t2>0 会交换两个元素的位置。 */ return sapoActList; } // 通过主键更新 public void updateSapoActByPrimaryKey(SapoAct sapoActForUpdate){ if(sapoActForUpdate == null || checkSapoActAllFieldsIsNull(sapoActForUpdate)){ bizLogger.warn(" update tbl_sapo_act dao layer input sapoActForUpdate is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer sapoActForUpdate is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoActForUpdate.setLastUpdateTime(LocalDateTime.now()); int updateResult = 0; try { updateResult = mapper.updateSapoActByPrimaryKey(sapoActForUpdate); } catch (DuplicateKeyException e) { bizLogger.warn(" update tbl_sapo_act duplicateKeyException ,sapoActForUpdate : " + sapoActForUpdate.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); } if (updateResult == 0) { bizLogger.warn("update tbl_sapo_act result == 0 [updateResult, sapoActForUpdate] : "+updateResult+","+ sapoActForUpdate.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 通过条件更新，如实更新，且查询条件有null,就code is null public void updateSapoActByNull(SapoAct sapoActForUpdate,SapoAct sapoActForQuery){ if(sapoActForUpdate == null || checkSapoActAllFieldsIsNull(sapoActForUpdate) || sapoActForQuery==null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" update tbl_sapo_act dao layer input , but sapoActForUpdate or sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoActForUpdate or sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoActForUpdate.setLastUpdateTime(LocalDateTime.now()); int updateResult = 0; try { updateResult = mapper.updateSapoActByNull(sapoActForUpdate,sapoActForQuery); } catch (DuplicateKeyException e) { bizLogger.error(" update tbl_sapo_act duplicateKeyException ,sapoActForQuery : " + sapoActForQuery.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); } if (updateResult == 0) { bizLogger.warn("update tbl_sapo_act result == 0 [updateResult, sapoActForQuery] : "+updateResult+","+ sapoActForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 通过条件更新 public void updateSapoAct(SapoAct sapoActForUpdate,SapoAct sapoActForQuery){ if(sapoActForUpdate == null || checkSapoActAllFieldsIsNull(sapoActForUpdate) || sapoActForQuery==null || checkSapoActAllFieldsIsNull(sapoActForQuery)){ bizLogger.warn(" update tbl_sapo_act dao layer input , but sapoActForUpdate or sapoActForQuery is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoActForUpdate or sapoActForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoActForUpdate.setLastUpdateTime(LocalDateTime.now()); int updateResult = 0; try { updateResult = mapper.updateSapoAct(sapoActForUpdate,sapoActForQuery); } catch (DuplicateKeyException e) { bizLogger.error(" update tbl_sapo_act duplicateKeyException ,sapoActForQuery : " + sapoActForQuery.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); } if (updateResult == 0) { bizLogger.warn("update tbl_sapo_act result == 0 [updateResult, sapoActForQuery] : "+updateResult+","+ sapoActForQuery.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 通过条件和主键in更新 public void updateSapoAct(List<Integer> idListForQuery,SapoAct sapoActForQuery,SapoAct sapoActForUpdate){ if((idListForQuery == null || idListForQuery.size()==0 ) && (sapoActForQuery == null || checkSapoActAllFieldsIsNull(sapoActForQuery))){ bizLogger.warn(" update tbl_sapo_act dao layer input , but idListForQuery and sapoActForQuery is null at same time"); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " idListForQuery and sapoActForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); } if(sapoActForUpdate == null ){ bizLogger.warn(" update tbl_sapo_act dao layer input , but sapoActForUpdate is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoActForUpdatey is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoActForUpdate.setLastUpdateTime(LocalDateTime.now()); int updateResult = 0; try { updateResult = mapper.updateSapoActByIdList(idListForQuery,sapoActForQuery,sapoActForUpdate); } catch (DuplicateKeyException e) { bizLogger.error(" update tbl_sapo_act duplicateKeyException ,sapoActForQuery : " + sapoActForQuery.toString()+" ; idListForQuery: "+idListForQuery); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); } if (updateResult == 0) { bizLogger.warn("update tbl_sapo_act result == 0 [updateResult, sapoActForQuery,idListForQuery] : "+updateResult+","+ sapoActForQuery.toString()+","+idListForQuery); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 单条插入:id自增 public void insertSapoAct(SapoAct sapoAct){ if(sapoAct == null || checkSapoActAllFieldsIsNull(sapoAct) ){ bizLogger.warn(" insert tbl_sapo_act dao layer input , but sapoAct is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAct is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoAct.setCreateTime(LocalDateTime.now()); sapoAct.setLastUpdateTime(LocalDateTime.now()); Integer insertResult = null; try { insertResult = mapper.insertSapoAct(sapoAct); } catch (DuplicateKeyException e) { bizLogger.error(" insert tbl_sapo_act duplicateKeyException ,sapoAct : " + sapoAct.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); } if (insertResult==null || insertResult==0) { bizLogger.warn("insert tbl_sapo_act result is null or result == 0 , sapoAct: "+sapoAct.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 单条插入：id不自增 public void insertSapoActNoIncr(SapoAct sapoAct){ if(sapoAct == null || checkSapoActAllFieldsIsNull(sapoAct) ){ bizLogger.warn(" insert tbl_sapo_act sapoAct is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " sapoAct is null , bizId=" + BizLogUtils.getValueOfBizId()); } sapoAct.setCreateTime(LocalDateTime.now()); sapoAct.setLastUpdateTime(LocalDateTime.now()); int insertResult = 0; try { insertResult = mapper.insertSapoAct(sapoAct); } catch (DuplicateKeyException e) { bizLogger.error(" insert tbl_sapo_act duplicateKeyException ,sapoAct : " + sapoAct.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_act result is null or result == 0 , sapoAct: "+sapoAct.toString()); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } } // 批量插入 @SuppressWarnings("unchecked") public int batchInsertSapoAct(Object object) { // 类型转换，支持单个对象或者集合形式作为入参 List<SapoAct> list = null; if (object instanceof SapoAct) { list = new ArrayList<SapoAct>(); list.add((SapoAct) object); } else if (object instanceof List) { for (Object o : (List<?>) object) { if (!(o instanceof SapoAct)) { 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<SapoAct>) 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()); } //设置创建时间 for(SapoAct sapoAct:list){ sapoAct.setCreateTime(LocalDateTime.now()); sapoAct.setLastUpdateTime(LocalDateTime.now()); } // 插入阈值， 每多少条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.batchInsertSapoAct(list.subList(i, end)); } catch (Exception e) { // 根据业务做补偿机制，例如通过end值，将之前插入的值全部删除或者状态翻转为无效 batchInsertSapoActFailOffset(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 batchInsertSapoActFailOffset(List<SapoAct> 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(SapoAct::getId).collect(Collectors.toList()); //SapoAct sapoActForUpdate = new SapoAct(); //sapoActForUpdate.setId(list.get(i).getId()); //sapoActForUpdate.setStatus(SapoAct.STATUS_INVALID); //deleteSapoActByPrimaryKey(list.get(i).getId()); //updateSapoAct(idList,null,sapoActForUpdate); } 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); } } } // 通用外键查询，返回对象，对象不为空，否则报错 public SapoAct getSapoActByCode(String code){ if(code == null){ bizLogger.warn(" select tbl_sapo_act , but dao layer input code is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " code is null , bizId=" + BizLogUtils.getValueOfBizId()); } SapoAct sapoAct = mapper.getSapoActByCode(code); if(sapoAct == null){ bizLogger.warn(" select tbl_sapo_act by code ,but find null ,code : " + code.toString()); throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); } return sapoAct; } // 通用外键查询，返回对象，对象可为空 public SapoAct getSapoActByCodeWithNull(String code){ if(code == null){ bizLogger.warn(" select tbl_sapo_act , but dao layer input code is null "); throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " code is null , bizId=" + BizLogUtils.getValueOfBizId()); } SapoAct sapoAct = mapper.getSapoActByCode(code); return sapoAct; } // 判断每个字段是否为空 private boolean checkSapoActAllFieldsIsNull(SapoAct object) { if (null == object) { return true; } try { for (java.lang.reflect.Field f : object.getClass().getDeclaredFields()) { f.setAccessible(true); if (f.get(object) != null && org.apache.commons.lang3.StringUtils.isNotBlank(f.get(object).toString())) { return false; } } } catch (Exception e) { throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), ResultInfo.SYS_INNER_ERROR.getDesc() + " checkSapoActAllFieldsIsNull error , bizId=" + BizLogUtils.getValueOfBizId(),e); } return true; } }View Code
工具生成mapper层代码示例：
@Mapper public interface SapoActMapper{ // 通用查询,返回对象,对象不为空，否则报错 @Select({ "<script> ", "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "<where> ", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </if>", "</where> ", "</script>" }) SapoAct getSapoAct(@Param("queryObj") SapoAct sapoActForQuery); // 通用查询,返回集合 @Select({ "<script> ", "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "<where> ", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </if>", "</where> ", "</script>" }) List<SapoAct> getSapoActList(@Param("queryObj") SapoAct sapoActForQuery); // 通用模糊查询(所有varchar字段都模糊),返回集合 @Select({ "<script> ", "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "<where> ", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code like concat('%',#{queryObj.code,jdbcType=VARCHAR},'%') </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name like concat('%',#{queryObj.name,jdbcType=VARCHAR},'%') </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail like concat('%',#{queryObj.detail,jdbcType=VARCHAR},'%') </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource like concat('%',#{queryObj.resource,jdbcType=VARCHAR},'%') </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time like concat('%',#{queryObj.beginTime,jdbcType=VARCHAR},'%') </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time like concat('%',#{queryObj.endTime,jdbcType=VARCHAR},'%') </if>", "</where> ", "</script>" }) List<SapoAct> getSapoActListByFuzzy(@Param("queryObj") SapoAct sapoActForQuery); // 通过主键查询,返回对象 @Select({ "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "where id = #{id,jdbcType=INTEGER}" }) SapoAct getSapoActByPrimaryKey(Integer id); // 通过主键删除 @Select({ "delete ", "from tbl_sapo_act ", "where id = #{id,jdbcType=INTEGER}" }) int deleteSapoActByPrimaryKey(Integer id); // 通过条件和主键in查询,返回集合 @Select({ "<script> ", "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "<where> ", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </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<SapoAct> getSapoActListByIdList(@Param("itemList") List<Integer> idListForQuery,@Param("queryObj") SapoAct sapoActForQuery); // 通过主键更新 @Update({ "update tbl_sapo_act set ", "code=#{updateObj.code,jdbcType=VARCHAR},create_time=#{updateObj.createTime,jdbcType=TIMESTAMP},last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP},name=#{updateObj.name,jdbcType=VARCHAR},detail=#{updateObj.detail,jdbcType=VARCHAR},resource=#{updateObj.resource,jdbcType=VARCHAR},act_form=#{updateObj.actForm,jdbcType=INTEGER},status=#{updateObj.status,jdbcType=INTEGER},begin_time=#{updateObj.beginTime,jdbcType=VARCHAR},end_time=#{updateObj.endTime,jdbcType=VARCHAR} ", "where id = #{updateObj.id,jdbcType=INTEGER} " }) int updateSapoActByPrimaryKey(@Param("updateObj") SapoAct sapoActForUpdate); // 通过条件更新 @Update({ "<script> ", "update tbl_sapo_act set ", "code=#{updateObj.code,jdbcType=VARCHAR},create_time=#{updateObj.createTime,jdbcType=TIMESTAMP},last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP},name=#{updateObj.name,jdbcType=VARCHAR},detail=#{updateObj.detail,jdbcType=VARCHAR},resource=#{updateObj.resource,jdbcType=VARCHAR},act_form=#{updateObj.actForm,jdbcType=INTEGER},status=#{updateObj.status,jdbcType=INTEGER},begin_time=#{updateObj.beginTime,jdbcType=VARCHAR},end_time=#{updateObj.endTime,jdbcType=VARCHAR} ", "<where>", "<choose><when test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </when><otherwise> and id is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </when><otherwise> and code is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </when><otherwise> and create_time is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </when><otherwise> and last_update_time is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </when><otherwise> and name is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </when><otherwise> and detail is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </when><otherwise> and resource is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </when><otherwise> and act_form is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </when><otherwise> and status is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </when><otherwise> and begin_time is null </otherwise></choose>", "<choose><when test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </when><otherwise> and end_time is null </otherwise></choose>", "</where>", "</script>" }) int updateSapoActByNull(@Param("updateObj") SapoAct sapoActForUpdate,@Param("queryObj") SapoAct sapoActForQuery); // 通过条件更新 @Update({ "<script> ", "update tbl_sapo_act ", "<set>", "<if test ='updateObj !=null and updateObj.code != null and updateObj.code != ''' > code=#{updateObj.code,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.createTime != null' > create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>", "<if test ='updateObj !=null and updateObj.lastUpdateTime != null' > last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>", "<if test ='updateObj !=null and updateObj.name != null and updateObj.name != ''' > name=#{updateObj.name,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.detail != null and updateObj.detail != ''' > detail=#{updateObj.detail,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.resource != null and updateObj.resource != ''' > resource=#{updateObj.resource,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.actForm != null' > act_form=#{updateObj.actForm,jdbcType=INTEGER} , </if>", "<if test ='updateObj !=null and updateObj.status != null' > status=#{updateObj.status,jdbcType=INTEGER} , </if>", "<if test ='updateObj !=null and updateObj.beginTime != null and updateObj.beginTime != ''' > begin_time=#{updateObj.beginTime,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.endTime != null and updateObj.endTime != ''' > end_time=#{updateObj.endTime,jdbcType=VARCHAR} , </if>", "</set>", "<where>", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </if>", "</where>", "</script>" }) int updateSapoAct(@Param("updateObj") SapoAct sapoActForUpdate,@Param("queryObj") SapoAct sapoActForQuery); // 通过条件和主键in更新 @Update({ "<script> ", "update tbl_sapo_act ", "<set>", "<if test ='updateObj !=null and updateObj.code != null and updateObj.code != ''' > code=#{updateObj.code,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.createTime != null' > create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} , </if>", "<if test ='updateObj !=null and updateObj.lastUpdateTime != null' > last_update_time=#{updateObj.lastUpdateTime,jdbcType=TIMESTAMP} , </if>", "<if test ='updateObj !=null and updateObj.name != null and updateObj.name != ''' > name=#{updateObj.name,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.detail != null and updateObj.detail != ''' > detail=#{updateObj.detail,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.resource != null and updateObj.resource != ''' > resource=#{updateObj.resource,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.actForm != null' > act_form=#{updateObj.actForm,jdbcType=INTEGER} , </if>", "<if test ='updateObj !=null and updateObj.status != null' > status=#{updateObj.status,jdbcType=INTEGER} , </if>", "<if test ='updateObj !=null and updateObj.beginTime != null and updateObj.beginTime != ''' > begin_time=#{updateObj.beginTime,jdbcType=VARCHAR} , </if>", "<if test ='updateObj !=null and updateObj.endTime != null and updateObj.endTime != ''' > end_time=#{updateObj.endTime,jdbcType=VARCHAR} , </if>", "</set>", "<where>", "<if test ='queryObj !=null and queryObj.id != null' > and id=#{queryObj.id,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.code != null and queryObj.code != ''' > and code=#{queryObj.code,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.createTime != null' > and create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.lastUpdateTime != null' > and last_update_time=#{queryObj.lastUpdateTime,jdbcType=TIMESTAMP} </if>", "<if test ='queryObj !=null and queryObj.name != null and queryObj.name != ''' > and name=#{queryObj.name,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.detail != null and queryObj.detail != ''' > and detail=#{queryObj.detail,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.resource != null and queryObj.resource != ''' > and resource=#{queryObj.resource,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.actForm != null' > and act_form=#{queryObj.actForm,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.status != null' > and status=#{queryObj.status,jdbcType=INTEGER} </if>", "<if test ='queryObj !=null and queryObj.beginTime != null and queryObj.beginTime != ''' > and begin_time=#{queryObj.beginTime,jdbcType=VARCHAR} </if>", "<if test ='queryObj !=null and queryObj.endTime != null and queryObj.endTime != ''' > and end_time=#{queryObj.endTime,jdbcType=VARCHAR} </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 updateSapoActByIdList(@Param("itemList") List<Integer> idListForQuery,@Param("queryObj") SapoAct sapoActForQuery,@Param("updateObj") SapoAct sapoActForUpdate); // 单条插入:id自增 @Insert({ "insert into tbl_sapo_act ", "( `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_form` , `status` , `begin_time` , `end_time` )", "values ", "(#{item.id,jdbcType=INTEGER},#{item.code,jdbcType=VARCHAR},#{item.createTime,jdbcType=TIMESTAMP},#{item.lastUpdateTime,jdbcType=TIMESTAMP},#{item.name,jdbcType=VARCHAR},#{item.detail,jdbcType=VARCHAR},#{item.resource,jdbcType=VARCHAR},#{item.actForm,jdbcType=INTEGER},#{item.status,jdbcType=INTEGER},#{item.beginTime,jdbcType=VARCHAR},#{item.endTime,jdbcType=VARCHAR}) " }) @Options(useGeneratedKeys = true, keyProperty = "item.id", keyColumn = "id") int insertSapoAct(@Param("item") SapoAct sapoAct); // 单条插入：id不自增 @Insert({ "insert into tbl_sapo_act ", "( `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_form` , `status` , `begin_time` , `end_time` )", "values ", "(#{item.id,jdbcType=INTEGER},#{item.code,jdbcType=VARCHAR},#{item.createTime,jdbcType=TIMESTAMP},#{item.lastUpdateTime,jdbcType=TIMESTAMP},#{item.name,jdbcType=VARCHAR},#{item.detail,jdbcType=VARCHAR},#{item.resource,jdbcType=VARCHAR},#{item.actForm,jdbcType=INTEGER},#{item.status,jdbcType=INTEGER},#{item.beginTime,jdbcType=VARCHAR},#{item.endTime,jdbcType=VARCHAR}) " }) int insertSapoActNoIncr(@Param("item") SapoAct sapoAct); // 批量插入 @Insert({ "<script> ", "insert into tbl_sapo_act ( `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_form` , `status` , `begin_time` , `end_time` ) values", "<foreach collection='itemList' item='item' index='index' open='(' separator='),(' close=')'>", "#{item.id,jdbcType=INTEGER},#{item.code,jdbcType=VARCHAR},#{item.createTime,jdbcType=TIMESTAMP},#{item.lastUpdateTime,jdbcType=TIMESTAMP},#{item.name,jdbcType=VARCHAR},#{item.detail,jdbcType=VARCHAR},#{item.resource,jdbcType=VARCHAR},#{item.actForm,jdbcType=INTEGER},#{item.status,jdbcType=INTEGER},#{item.beginTime,jdbcType=VARCHAR},#{item.endTime,jdbcType=VARCHAR} ", "</foreach>", "</script>" }) int batchInsertSapoAct(@Param("itemList") List<SapoAct> sapoActList); // 通过外键查询，返回对象 @Select({ "select t.`id` as 'id' , t.`code` as 'code' , t.`create_time` as 'create_time' , t.`last_update_time` as 'last_update_time' , t.`name` as 'name' , t.`detail` as 'detail' , t.`resource` as 'resource' , t.`act_form` as 'act_form' , t.`status` as 'status' , t.`begin_time` as 'begin_time' , t.`end_time` as 'end_time' ", "from tbl_sapo_act t ", "where code = #{code,jdbcType=VARCHAR}" }) SapoAct getSapoActByCode(String code); }View Code
工具代码：
1 DROP PROCEDURE IF EXISTS `print_code`; 2 DELIMITER $ 3 CREATE PROCEDURE `print_code`() 4 BEGIN 5 6 SET group_concat_max_len = 4294967295; 7 8 9 -- SET @noStrInTbl='tbl_ams'; 10 SET @noStrInTbl='tbl'; 11 12 13 14 -- 保存所有表及表的所有字段 15 DROP TABLE if EXISTS all_col_table; 16 CREATE table if not exists all_col_table( 17 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', 18 tbl_name VARCHAR(256) COMMENT '表名:tbl_sapo_admin_account', 19 tbl_name_comment VARCHAR(256) COMMENT '表注释', 20 21 tbl_name_upper_camel VARCHAR(1024) COMMENT '表名大写驼峰:SapoAdminAccount', 22 tbl_name_lower_camel VARCHAR(1024) COMMENT '表名小写驼峰:sapoAdminAccount', 23 24 col VARCHAR(256) COMMENT '字段名:create_time', 25 col_comment VARCHAR(512) COMMENT '字段注释', 26 col_type VARCHAR(256) COMMENT '字段类型，datetime', 27 28 col_upper_camel VARCHAR(256) COMMENT '字段大写:create_time', 29 col_lower_camel VARCHAR(256) COMMENT '字段驼峰形式:createTime', 30 31 col_setter VARCHAR(256) COMMENT 'setter模式：setCreateTime', 32 col_getter VARCHAR(256) COMMENT 'getter模式：getCreateTime', 33 34 java_type VARCHAR(256) COMMENT 'java类型，datetime', 35 jdbc_type VARCHAR(256) COMMENT 'jdbc类型：datetime->TIMESTAMP', 36 PRIMARY KEY (`id`) , 37 INDEX idx1(tbl_name,col) 38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 39 40 41 DROP TABLE if EXISTS all_col_code; 42 CREATE table if not exists all_col_code( 43 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', 44 tbl_name VARCHAR(256) COMMENT '表名:tbl_sapo_admin_account', 45 col VARCHAR(256) COMMENT '字段名:create_time', 46 col_type VARCHAR(256) COMMENT '字段类型，datetime', 47 48 query_if_test text COMMENT 'queryObj.create_time!=null', 49 update_if_test text COMMENT 'updateObj.create_time!=null', 50 col_for_query_jdbc text COMMENT 'create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} ', 51 fuzzy_col_for_query_jdbc text COMMENT 'create_time=#{queryObj.createTime,jdbcType=TIMESTAMP} ', 52 col_for_update_jdbc text COMMENT 'create_time=#{updateObj.createTime,jdbcType=TIMESTAMP} ', 53 col_for_insert_jdbc text COMMENT '#{item.createTime,jdbcType=TIMESTAMP} ', 54 PRIMARY KEY (`id`) , 55 INDEX idx1(tbl_name,col) 56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 57 58 -- select * from all_col_table; 59 60 -- 外键临时表 61 DROP TABLE if exists fk_def; 62 CREATE TABLE if not exists fk_def as 63 SELECT 64 t.TABLE_NAME AS tbl_name, 65 k.column_name AS col_name, 66 k.REFERENCED_TABLE_NAME AS rf_name, 67 k.REFERENCED_COLUMN_NAME AS rf_col 68 FROM 69 information_schema.TABLE_CONSTRAINTS t 70 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 71 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 72 AND t.TABLE_NAME = k.TABLE_NAME 73 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 74 WHERE 75 t.CONSTRAINT_TYPE = 'FOREIGN KEY' 76 AND t.table_schema = DATABASE(); 77 78 ALTER TABLE `fk_def` 79 ADD INDEX `idx1` (tbl_name,col_name); 80 ALTER TABLE `fk_def` 81 ADD INDEX `idx2` (rf_name,rf_col); 82 83 -- 将本库中所有表及所有字段插入表中: tbl_name,tbl_name_comment,col,col_comment,col_type 84 INSERT INTO all_col_table(tbl_name,tbl_name_comment,col,col_comment,col_type) 85 SELECT 86 t1.table_name, t2.TABLE_COMMENT,t1.column_name ,t1.COLUMN_COMMENT,t1.DATA_TYPE 87 FROM 88 information_schema.COLUMNS t1 JOIN information_schema.tables t2 ON t1.TABLE_NAME=t2.TABLE_NAME 89 WHERE 90 t1.table_schema= DATABASE() AND t1.TABLE_NAME LIKE 'tbl_%' ORDER BY t1.table_name,t1.ORDINAL_POSITION; 91 92 INSERT INTO all_col_code(tbl_name,col,col_type) 93 SELECT tbl_name,col,col_type FROM all_col_table ORDER BY tbl_name,id; 94 95 96 -- java类型转换 97 UPDATE all_col_table SET java_type= 98 case col_type 99 when 'datetime' then 'LocalDateTime' 100 when 'tinyint' then 'Byte' 101 when 'bigint' then 'Long' 102 when 'int' then 'Integer' 103 when 'varchar' then 'String' 104 END; 105 106 -- 转换成jdbc类型 107 UPDATE all_col_table SET jdbc_type= 108 case col_type 109 when 'datetime' then 'TIMESTAMP' 110 when 'tinyint' then 'TINYINT' 111 when 'bigint' then 'BIGINT' 112 when 'int' then 'INTEGER' 113 when 'float' then 'REAL' 114 when 'varchar' then 'VARCHAR' 115 END; 116 117 -- 字段转驼峰 118 UPDATE all_col_table SET col_lower_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'),'_','') 119 ,''); 120 UPDATE all_col_table SET col_upper_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(CONCAT_WS('','_',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'),'_','') 121 ,''); 122 -- getter,setter 123 UPDATE all_col_table SET col_setter=CONCAT_WS('','set',col_upper_camel); 124 UPDATE all_col_table SET col_getter=CONCAT_WS('','get',col_upper_camel); 125 126 -- 表名转驼峰 127 UPDATE all_col_table SET tbl_name_upper_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'),'_','') ; 128 UPDATE all_col_table SET tbl_name_lower_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'),'_','') ; 129 130 131 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 132 133 -- 配置每个字段代码 134 135 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 136 137 -- query_if_test 138 139 SET @temp="'queryObj !=null and queryObj.%col_lower_camel% != null' "; 140 141 UPDATE all_col_code SET query_if_test=@temp WHERE col_type != 'varchar'; 142 143 SET @temp="'queryObj !=null and queryObj.%col_lower_camel% != null and queryObj.%col_lower_camel% != ''' "; 144 145 UPDATE all_col_code SET query_if_test=@temp WHERE col_type = 'varchar'; 146 147 -- update_if_test 148 149 SET @temp="'updateObj !=null and updateObj.%col_lower_camel% != null' "; 150 151 UPDATE all_col_code SET update_if_test=@temp WHERE col_type != 'varchar'; 152 153 SET @temp="'updateObj !=null and updateObj.%col_lower_camel% != null and updateObj.%col_lower_camel% != ''' "; 154 155 UPDATE all_col_code SET update_if_test=@temp WHERE col_type = 'varchar'; 156 157 -- col_for_query_jdbc 158 159 SET @temp='%col%=#{queryObj.%col_lower_camel%,jdbcType=%jdbc_type%}'; 160 161 UPDATE all_col_code SET col_for_query_jdbc=@temp ; 162 163 -- fuzzy_col_for_query_jdbc 164 165 SET @temp='%col%=#{queryObj.%col_lower_camel%,jdbcType=%jdbc_type%}'; 166 167 UPDATE all_col_code SET fuzzy_col_for_query_jdbc=@temp WHERE col_type!='varchar'; 168 169 SET @temp='%col% like concat(\'%\',#{queryObj.%col_lower_camel%,jdbcType=%jdbc_type%},\'%\')'; 170 171 UPDATE all_col_code SET fuzzy_col_for_query_jdbc=@temp WHERE col_type ='varchar'; 172 173 174 175 -- col_for_update_jdbc 176 177 SET @temp='%col%=#{updateObj.%col_lower_camel%,jdbcType=%jdbc_type%}'; 178 179 UPDATE all_col_code SET col_for_update_jdbc=@temp ; 180 181 -- col_for_insert_jdbc 182 183 SET @temp='#{item.%col_lower_camel%,jdbcType=%jdbc_type%}'; 184 185 UPDATE all_col_code SET col_for_insert_jdbc=@temp ; 186 187 -- 统一替换 188 189 190 191 UPDATE all_col_code c SET 192 c.query_if_test=(select replace(replace(REPLACE(c.query_if_test,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col), 193 c.update_if_test=(select replace(replace(REPLACE(c.update_if_test,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col), 194 c.col_for_query_jdbc=(select replace(replace(REPLACE(c.col_for_query_jdbc,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col), 195 c.fuzzy_col_for_query_jdbc=(select replace(replace(REPLACE(c.fuzzy_col_for_query_jdbc,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col), 196 c.col_for_update_jdbc=(select replace(replace(REPLACE(c.col_for_update_jdbc,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col), 197 c.col_for_insert_jdbc=(select replace(replace(REPLACE(c.col_for_insert_jdbc,'%col_lower_camel%',a.col_lower_camel),'%jdbc_type%',a.jdbc_type),'%col%',a.col) FROM all_col_table a WHERE a.tbl_name=c.tbl_name AND a.col=c.col) 198 ; 199 200 -- SELECT * FROM all_col_code; 201 -- SELECT * FROM all_col_table; 202 203 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 204 205 -- 配置每个表代码 206 207 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 208 209 210 -- 表相关数据 211 DROP TABLE if EXISTS all_table; 212 CREATE table if not exists all_table( 213 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', 214 tbl_name VARCHAR(256) NOT NULL COMMENT '表名:tbl_sapo_admin_account', 215 tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount', 216 tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount', 217 pk VARCHAR(255) COMMENT '主键', 218 pk_upper_camel VARCHAR(255) COMMENT '主键', 219 pk_lower_camel VARCHAR(255) COMMENT '主键', 220 pk_java_type VARCHAR(255) COMMENT '主键', 221 pk_jdbc_type VARCHAR(255) COMMENT '主键', 222 insert_set_time LONGTEXT COMMENT '', 223 update_set_time LONGTEXT COMMENT '', 224 batch_insert_set_time LONGTEXT COMMENT '', 225 col_list LONGTEXT COMMENT '字段列表', 226 col_list_alias LONGTEXT COMMENT '字段别名列表', 227 insert_if_test LONGTEXT COMMENT 'insert语句', 228 query_if_test LONGTEXT COMMENT 'queryTest语句', 229 query_if_test_with_null LONGTEXT COMMENT 'queryTest语句', 230 fuzzy_query_if_test LONGTEXT COMMENT '模糊queryTest语句', 231 update_chase LONGTEXT COMMENT 'update固定语句', 232 update_if_test LONGTEXT COMMENT 'updateTest语句', 233 PRIMARY KEY (`id`) , 234 INDEX idx1(tbl_name) 235 ) ENGINE=InnoDB ; 236 237 238 -- 把所有表入库 239 240 INSERT INTO all_table(tbl_name,tbl_name_upper_camel,tbl_name_lower_camel) 241 SELECT distinct tbl_name,tbl_name_upper_camel,tbl_name_lower_camel FROM all_col_table ; 242 243 244 -- 更新主键 245 246 UPDATE all_table a SET a.pk= 247 (SELECT 248 column_name 249 FROM information_schema.columns t1 250 WHERE 251 t1.table_schema= DATABASE() AND t1.COLUMN_KEY='PRI' AND a.tbl_name=table_name 252 ); 253 254 UPDATE all_table a SET a.pk_upper_camel = (SELECT col_upper_camel FROM all_col_table c WHERE c.tbl_name =a.tbl_name AND c.col=a.pk); 255 UPDATE all_table a SET a.pk_lower_camel = (SELECT col_lower_camel FROM all_col_table c WHERE c.tbl_name =a.tbl_name AND c.col=a.pk); 256 UPDATE all_table a SET a.pk_java_type = (SELECT java_type FROM all_col_table c WHERE c.tbl_name =a.tbl_name AND c.col=a.pk); 257 UPDATE all_table a SET a.pk_jdbc_type = (SELECT jdbc_type FROM all_col_table c WHERE c.tbl_name =a.tbl_name AND c.col=a.pk); 258 259 -- col_list 260 261 UPDATE all_table a SET a.col_list= 262 ( 263 SELECT GROUP_CONCAT( CONCAT_WS('',' `',col,'` ') ORDER BY id ) FROM all_col_code WHERE tbl_name = a.tbl_name 264 ); 265 266 -- col_list_alias 267 268 UPDATE all_table a SET a.col_list_alias= 269 ( 270 SELECT GROUP_CONCAT( 271 CONCAT_WS('',' t.`',col,'` as ',"'",col,"' ") ORDER BY id 272 ) FROM all_col_code WHERE tbl_name = a.tbl_name 273 ); 274 275 -- insert_if_test 276 277 UPDATE all_table a SET a.insert_if_test= 278 ( 279 SELECT 280 GROUP_CONCAT(col_for_insert_jdbc ORDER BY id) 281 FROM all_col_code WHERE tbl_name = a.tbl_name 282 ); 283 284 -- query_if_test 285 286 SET @temp=' "<if test =%query_if_test%> and %col_for_query_jdbc% </if>",'; 287 288 UPDATE all_table a SET a.query_if_test= 289 ( 290 SELECT 291 GROUP_CONCAT( 292 replace( 293 REPLACE(@temp,'%query_if_test%',query_if_test) 294 ,'%col_for_query_jdbc%',col_for_query_jdbc) 295 ORDER BY id SEPARATOR '\r\n') 296 FROM all_col_code WHERE tbl_name = a.tbl_name 297 ); 298 299 -- query_if_test_with_null 300 301 SET @temp=' "<choose><when test =%query_if_test%> and %col_for_query_jdbc% </when><otherwise> and %col% is null </otherwise></choose>",'; 302 303 UPDATE all_table a SET a.query_if_test_with_null= 304 ( 305 SELECT 306 GROUP_CONCAT( 307 replace( 308 replace( 309 REPLACE(@temp,'%query_if_test%',query_if_test) 310 ,'%col_for_query_jdbc%',col_for_query_jdbc) 311 ,'%col%',col) 312 ORDER BY id SEPARATOR '\r\n') 313 FROM all_col_code WHERE tbl_name = a.tbl_name 314 ); 315 316 317 -- fuzzy_query_if_test 318 319 SET @temp=' "<if test =%query_if_test%> and %fuzzy_col_for_query_jdbc% </if>",'; 320 321 UPDATE all_table a SET a.fuzzy_query_if_test= 322 ( 323 SELECT 324 GROUP_CONCAT( 325 replace( 326 REPLACE(@temp,'%query_if_test%',query_if_test) 327 ,'%fuzzy_col_for_query_jdbc%',fuzzy_col_for_query_jdbc) 328 ORDER BY id SEPARATOR '\r\n') 329 FROM all_col_code WHERE tbl_name = a.tbl_name 330 ); 331 332 -- select * from all_table; 333 334 -- update_if_test 335 336 SET @temp=' "<if test =%update_if_test%> %col_for_update_jdbc% , </if>",'; 337 338 339 UPDATE all_table a SET a.update_if_test= 340 ( 341 SELECT 342 GROUP_CONCAT( 343 replace( 344 REPLACE(@temp,'%update_if_test%',update_if_test) 345 ,'%col_for_update_jdbc%',col_for_update_jdbc) 346 ORDER BY id SEPARATOR '\r\n') 347 FROM all_col_code WHERE tbl_name = a.tbl_name AND a.pk!=col 348 ); 349 350 351 -- update_chase : release_code=#{updateObj.releaseCode,jdbcType=VARCHAR} ,act_code=#{updateObj.actCode,jdbcType=VARCHAR} , 352 353 UPDATE all_table a SET a.update_chase= 354 ( 355 SELECT 356 GROUP_CONCAT( col_for_update_jdbc ORDER BY id) 357 FROM all_col_code WHERE tbl_name = a.tbl_name AND a.pk !=col 358 ); 359 360 -- SELECT * FROM all_table; 361 -- SELECT * FROM all_col_code; 362 363 364 -- ################################################################################# 365 -- #################################开始组建语句#################################### 366 -- ################################################################################# 367 -- ############################## mapper select #################################### 368 -- ################################################################################# 369 -- ################################################################################# 370 371 -- 保存所有表及表的所有字段 372 DROP TABLE if EXISTS java_code; 373 CREATE table if not exists java_code( 374 tbl_name VARCHAR(256) NOT NULL COMMENT '表名:tbl_sapo_admin_account', 375 code_type VARCHAR(255) COMMENT '代码类似，inert,update,select', 376 code_layer VARCHAR(255) COMMENT '代码层级 ，mapper,dao,domain', 377 func_desc VARCHAR(255) COMMENT '功能描述', 378 java_code TEXT COMMENT 'java代码', 379 versions VARCHAR(255) COMMENT '版本', 380 versions_desc VARCHAR(255) COMMENT '版本描述', 381 INDEX idx1(tbl_name) 382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 383 384 385 386 -- ###################################################################################################### 387 -- 通用查询，返回对象 388 -- ###################################################################################################### 389 390 391 SET @query_template1= 392 ' 393 // 通用查询,返回对象,对象不为空，否则报错 394 @Select({ 395 "<script> ", 396 "select %col_list_alias% ", 397 "from %tbl_name% t ", 398 "<where> ", 399 %query_if_test% 400 "</where> ", 401 "</script>" 402 }) 403 %tbl_name_upper_camel% get%tbl_name_upper_camel%(@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 404 '; 405 406 INSERT INTO java_code 407 SELECT tbl_name,'select','mapper','通用查询,返回对象,对象不为空，否则报错',@query_template1,'1','' FROM all_table; 408 409 -- dao层语句 410 SET @query_template1= 411 ' 412 // 通用查询,返回对象,对象不为空，否则报错 413 public %tbl_name_upper_camel% get%tbl_name_upper_camel%(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 414 415 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 416 bizLogger.warn(" select %tbl_name% , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 417 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 418 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 419 } 420 421 %tbl_name_upper_camel% %tbl_name_lower_camel% = mapper.get%tbl_name_upper_camel%(%tbl_name_lower_camel%ForQuery); 422 423 if(%tbl_name_lower_camel% == null){ 424 bizLogger.warn(" select %tbl_name% result is null ,%tbl_name_lower_camel%ForQuery : " 425 + %tbl_name_lower_camel%ForQuery.toString()); 426 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 427 } 428 429 return %tbl_name_lower_camel%; 430 } 431 '; 432 433 INSERT INTO java_code 434 SELECT tbl_name,'select','dao','通用查询,返回对象,对象不为空，否则报错',@query_template1,'1','' FROM all_table; 435 436 SET @query_template1= 437 ' 438 // 通用查询,返回对象，对象可为空 439 public %tbl_name_upper_camel% get%tbl_name_upper_camel%WithNull(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 440 441 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 442 bizLogger.warn(" select %tbl_name% , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 443 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 444 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 445 } 446 447 %tbl_name_upper_camel% %tbl_name_lower_camel% = mapper.get%tbl_name_upper_camel%(%tbl_name_lower_camel%ForQuery); 448 449 return %tbl_name_lower_camel%; 450 } 451 '; 452 453 INSERT INTO java_code 454 SELECT tbl_name,'select','dao','通用查询,返回对象,对象可为空',@query_template1,'1','' FROM all_table; 455 456 -- ###################################################################################################### 457 -- 通用查询，返回集合 458 -- ###################################################################################################### 459 460 SET @query_template1= 461 ' 462 // 通用查询,返回集合 463 @Select({ 464 "<script> ", 465 "select %col_list_alias% ", 466 "from %tbl_name% t ", 467 "<where> ", 468 %query_if_test% 469 "</where> ", 470 "</script>" 471 }) 472 List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%List(@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 473 '; 474 475 INSERT INTO java_code 476 SELECT tbl_name,'select','mapper','通用查询,返回集合',@query_template1,'1','' FROM all_table; 477 478 -- dao层 479 SET @query_template1= 480 ' 481 // 通用查询,返回集合,集合不为空，否则报错 482 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%List(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 483 484 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 485 bizLogger.warn(" select %tbl_name% , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 486 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 487 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 488 } 489 490 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery); 491 492 if(%tbl_name_lower_camel%List == null || %tbl_name_lower_camel%List.size()==0){ 493 bizLogger.warn(" select %tbl_name% List is null or size=0 ,%tbl_name_lower_camel%ForQuery : " 494 + %tbl_name_lower_camel%ForQuery.toString()); 495 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 496 } 497 498 %order_by_create_time% 499 500 return %tbl_name_lower_camel%List; 501 } 502 503 '; 504 505 INSERT INTO java_code 506 SELECT tbl_name,'select','dao','通用查询，返回集合，集合不为空，否则报错',@query_template1,'1','' FROM all_table; 507 508 509 -- dao层 510 SET @query_template1= 511 ' 512 // 通用查询,返回集合,集合可以为空 513 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListWithNull(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 514 515 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 516 bizLogger.warn(" select %tbl_name% , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 517 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 518 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 519 } 520 521 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery); 522 523 524 %order_by_create_time% 525 526 return %tbl_name_lower_camel%List; 527 } 528 529 '; 530 531 INSERT INTO java_code 532 SELECT tbl_name,'select','dao','通用查询，返回集合，集合可以为空',@query_template1,'1','' FROM all_table; 533 534 535 536 537 SET @query_template1= 538 ' 539 // 通用分页查询，返回当前页集合,集合不能为空，否则报错 540 public Page<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByPage(Integer pageNum, Integer pageSize, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 541 542 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 543 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListByPage input %tbl_name_lower_camel%ForQuery is null "); 544 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 545 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 546 } 547 548 549 Page<%tbl_name_upper_camel%> pageList=null; 550 551 // 默认按照create_time 降序排列。即刚创建的在前面显示 552 try { 553 PageHelper.startPage(pageNum, pageSize," create_time desc "); 554 pageList = (Page<%tbl_name_upper_camel%>) get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery); 555 } finally { 556 PageHelper.clearPage(); 557 } 558 559 if(pageList == null || pageList.size()==0 ){ 560 bizLogger.warn(" select %tbl_name% List by page is null or size=0 ,[%tbl_name_lower_camel%ForQuery,pageNum,pageSize ]: " 561 + %tbl_name_lower_camel%ForQuery.toString()+" ; "+pageNum+" ; "+pageSize); 562 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 563 } 564 // service层可以从Page中获取总条数 565 return pageList; 566 } 567 '; 568 569 INSERT INTO java_code 570 SELECT tbl_name,'select','dao','通用分页查询，返回当前页集合，集合可以为空',@query_template1,'1','' FROM all_table; 571 572 SET @query_template1= 573 ' 574 // 通用分页查询，返回当前页集合,集合不能为空，否则报错 575 public Page<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByPageWithNull(Integer pageNum, Integer pageSize, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 576 577 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 578 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListByPage input %tbl_name_lower_camel%ForQuery is null "); 579 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 580 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 581 } 582 583 584 Page<%tbl_name_upper_camel%> pageList=null; 585 586 // 默认按照create_time 降序排列。即刚创建的在前面显示 587 try { 588 PageHelper.startPage(pageNum, pageSize," create_time desc "); 589 pageList = (Page<%tbl_name_upper_camel%>) get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery); 590 } finally { 591 PageHelper.clearPage(); 592 } 593 594 // service层可以从Page中获取总条数 595 return pageList; 596 } 597 '; 598 599 INSERT INTO java_code 600 SELECT tbl_name,'select','dao','通用分页查询，返回当前页集合，集合可空',@query_template1,'1','' FROM all_table; 601 602 603 SET @query_template1= 604 ' 605 // 通用分页模糊查询，返回当前页集合，集合不为空，否则报错 606 public Page<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByFuzzyByPage(Integer pageNum, Integer pageSize, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 607 608 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 609 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListByFuzzyByPage input %tbl_name_lower_camel%ForQuery is null "); 610 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 611 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 612 } 613 614 Page<%tbl_name_upper_camel%> pageList=null; 615 616 // 默认按照create_time 降序排列。即刚创建的在前面显示 617 try { 618 PageHelper.startPage(pageNum, pageSize," create_time desc "); 619 pageList = (Page<%tbl_name_upper_camel%>) get%tbl_name_upper_camel%ListByFuzzy(%tbl_name_lower_camel%ForQuery); 620 } finally { 621 PageHelper.clearPage(); 622 } 623 624 if(pageList == null || pageList.size()==0 ){ 625 bizLogger.warn(" select %tbl_name% List by fuzzy by page is null or size=0 ,[%tbl_name_lower_camel%ForQuery,pageNum,pageSize ]: " 626 + %tbl_name_lower_camel%ForQuery.toString()+" ; "+pageNum+" ; "+pageSize); 627 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 628 } 629 // service层可以从Page中获取总条数 630 return pageList; 631 } 632 '; 633 634 INSERT INTO java_code 635 SELECT tbl_name,'select','dao','通用分页模糊查询，返回当前页集合，集合不为空，否则报错',@query_template1,'1','' FROM all_table; 636 637 SET @query_template1= 638 ' 639 // 通用分页模糊查询，返回当前页集合，集合可空 640 public Page<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByFuzzyByPageWithNull(Integer pageNum, Integer pageSize, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 641 642 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 643 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListByFuzzyByPage input %tbl_name_lower_camel%ForQuery is null "); 644 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 645 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 646 } 647 648 Page<%tbl_name_upper_camel%> pageList=null; 649 650 // 默认按照create_time 降序排列。即刚创建的在前面显示 651 try { 652 PageHelper.startPage(pageNum, pageSize," create_time desc "); 653 pageList = (Page<%tbl_name_upper_camel%>) get%tbl_name_upper_camel%ListByFuzzy(%tbl_name_lower_camel%ForQuery); 654 } finally { 655 PageHelper.clearPage(); 656 } 657 658 659 // service层可以从Page中获取总条数 660 return pageList; 661 } 662 '; 663 664 INSERT INTO java_code 665 SELECT tbl_name,'select','dao','通用分页模糊查询，返回当前页集合，集合可空',@query_template1,'1','' FROM all_table; 666 667 668 669 SET @query_template1= 670 ' 671 // 分页形式获取表全部数据，放在一个集合中，集合不能为空，否则报错 672 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListAllPage( %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 673 674 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 675 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListAllPage input %tbl_name_lower_camel%ForQuery is null "); 676 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 677 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 678 } 679 680 int pageNum = 1; 681 682 // 分页默认每次拿500条 683 int pageSize = 500; 684 685 Page<%tbl_name_upper_camel%> page = null; 686 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List= new ArrayList<%tbl_name_upper_camel%>(500); 687 688 // 循环分页获取每页数据放入集合中 689 try { 690 do { 691 page = PageHelper.startPage(pageNum, pageSize); 692 %tbl_name_lower_camel%List.addAll(get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery)); 693 pageNum++; 694 } while (page.getPages() >= pageNum); 695 } finally { 696 PageHelper.clearPage(); 697 } 698 699 if( %tbl_name_lower_camel%List.size()==0 ){ 700 bizLogger.warn(" select %tbl_name% List all page is null or size=0 ,[%tbl_name_lower_camel%ForQuery,pageNum,pageSize ]: " 701 + %tbl_name_lower_camel%ForQuery.toString()+" ; "+pageNum+" ; "+pageSize); 702 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 703 } 704 705 return %tbl_name_lower_camel%List; 706 707 } 708 709 '; 710 711 INSERT INTO java_code 712 SELECT tbl_name,'select','dao','分页形式获取表全部数据，放在一个集合中，集合不能为空，否则报错',@query_template1,'1','' FROM all_table; 713 714 SET @query_template1= 715 ' 716 // 分页形式获取表全部数据，放在一个集合中，集合可空 717 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListAllPageWithNull( %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 718 719 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 720 bizLogger.warn(" select %tbl_name% , but dao layer get%tbl_name_upper_camel%ListAllPage input %tbl_name_lower_camel%ForQuery is null "); 721 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 722 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 723 } 724 725 int pageNum = 1; 726 727 // 分页默认每次拿500条 728 int pageSize = 500; 729 730 Page<%tbl_name_upper_camel%> page = null; 731 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List= new ArrayList<%tbl_name_upper_camel%>(500); 732 733 // 循环分页获取每页数据放入集合中 734 try { 735 do { 736 page = PageHelper.startPage(pageNum, pageSize); 737 %tbl_name_lower_camel%List.addAll(get%tbl_name_upper_camel%List(%tbl_name_lower_camel%ForQuery)); 738 pageNum++; 739 } while (page.getPages() >= pageNum); 740 } finally { 741 PageHelper.clearPage(); 742 } 743 744 745 746 return %tbl_name_lower_camel%List; 747 748 } 749 750 '; 751 752 INSERT INTO java_code 753 SELECT tbl_name,'select','dao','分页形式获取表全部数据，放在一个集合中，集合可空',@query_template1,'1','' FROM all_table; 754 755 -- ###################################################################################################### 756 -- 通用模糊查询(所有varchar字段都模糊)，返回集合 757 -- ###################################################################################################### 758 759 -- SELECT * FROM all_table; 760 761 -- SELECT * FROM java_code; 762 763 SET @query_template1= 764 ' 765 // 通用模糊查询(所有varchar字段都模糊),返回集合 766 @Select({ 767 "<script> ", 768 "select %col_list_alias% ", 769 "from %tbl_name% t ", 770 "<where> ", 771 %fuzzy_query_if_test% 772 "</where> ", 773 "</script>" 774 }) 775 List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByFuzzy(@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 776 '; 777 778 INSERT INTO java_code 779 SELECT tbl_name,'select','mapper','通用模糊查询(所有varchar字段都模糊),返回集合',@query_template1,'1','' FROM all_table; 780 781 -- dao层 782 SET @query_template1= 783 ' 784 // 通用模糊查询(所有varchar字段都模糊),返回集合,集合不为空，否则报错 785 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByFuzzy(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 786 787 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 788 bizLogger.warn(" select %tbl_name% by fuzzy , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 789 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 790 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 791 } 792 793 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%ListByFuzzy(%tbl_name_lower_camel%ForQuery); 794 795 if(%tbl_name_lower_camel%List == null || %tbl_name_lower_camel%List.size()==0){ 796 bizLogger.warn(" select %tbl_name% by fuzzy but result List is null or size=0 ,%tbl_name_lower_camel%ForQuery : " 797 + %tbl_name_lower_camel%ForQuery.toString()); 798 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 799 } 800 801 %order_by_create_time% 802 803 return %tbl_name_lower_camel%List; 804 805 806 } 807 '; 808 809 INSERT INTO java_code 810 SELECT tbl_name,'select','dao','通用模糊查询(所有varchar字段都模糊)，返回集合，集合不为空，否则报错',@query_template1,'1','' FROM all_table; 811 812 813 -- dao层 814 SET @query_template1= 815 ' 816 // 通用模糊查询(所有varchar字段都模糊),返回集合,集合可空 817 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListByFuzzyWithNull(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 818 819 if(%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 820 bizLogger.warn(" select %tbl_name% by fuzzy , but dao layer input %tbl_name_lower_camel%ForQuery is null "); 821 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 822 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 823 } 824 825 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%ListByFuzzy(%tbl_name_lower_camel%ForQuery); 826 827 828 829 %order_by_create_time% 830 831 return %tbl_name_lower_camel%List; 832 833 834 } 835 '; 836 837 INSERT INTO java_code 838 SELECT tbl_name,'select','dao','通用模糊查询(所有varchar字段都模糊)，返回集合，集合可空',@query_template1,'1','' FROM all_table; 839 840 -- ###################################################################################################### 841 -- 通过主键查询，返回对象 842 -- ###################################################################################################### 843 844 SET @query_template1= 845 ' 846 // 通过主键查询,返回对象 847 @Select({ 848 "select %col_list_alias% ", 849 "from %tbl_name% t ", 850 "where %pk% = #{%pk_lower_camel%,jdbcType=%pk_jdbc_type%}" 851 }) 852 %tbl_name_upper_camel% get%tbl_name_upper_camel%ByPrimaryKey(%pk_java_type% %pk_lower_camel%); 853 '; 854 855 INSERT INTO java_code 856 SELECT tbl_name,'select','mapper','通过主键查询',@query_template1,'1','' FROM all_table; 857 858 859 -- dao层 860 SET @query_template1= 861 ' 862 // 通用主键查询，返回对象 863 public %tbl_name_upper_camel% get%tbl_name_upper_camel%ByPrimaryKey(%pk_java_type% %pk_lower_camel%){ 864 865 if(%pk_lower_camel% == null){ 866 bizLogger.warn(" select %tbl_name% , but dao layer input %pk_lower_camel% is null "); 867 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 868 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %pk_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 869 } 870 871 %tbl_name_upper_camel% %tbl_name_lower_camel% = mapper.get%tbl_name_upper_camel%ByPrimaryKey(%pk_lower_camel%); 872 873 if(%tbl_name_lower_camel% == null){ 874 bizLogger.warn(" select %tbl_name% by primary key ,but find null ,%pk_lower_camel% : " 875 + %pk_lower_camel%.toString()); 876 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 877 } 878 879 return %tbl_name_lower_camel%; 880 } 881 '; 882 883 884 885 INSERT INTO java_code 886 SELECT tbl_name,'select','dao','通用主键查询,返回对象',@query_template1,'1','' FROM all_table; 887 888 -- ###################################################################################################### 889 -- 通过主键删除 890 -- ###################################################################################################### 891 892 SET @query_template1= 893 ' 894 // 通过主键删除 895 @Select({ 896 "delete ", 897 "from %tbl_name% ", 898 "where %pk% = #{%pk_lower_camel%,jdbcType=%pk_jdbc_type%}" 899 }) 900 int delete%tbl_name_upper_camel%ByPrimaryKey(%pk_java_type% %pk_lower_camel%); 901 '; 902 903 INSERT INTO java_code 904 SELECT tbl_name,'delete','mapper','通过主键删除',@query_template1,'1','' FROM all_table; 905 906 907 -- dao层 908 SET @query_template1= 909 ' 910 // 通过主键删除 911 public void delete%tbl_name_upper_camel%ByPrimaryKey(%pk_java_type% %pk_lower_camel%){ 912 913 if(%pk_lower_camel% == null){ 914 bizLogger.warn(" delete %tbl_name% , but dao layer %pk_lower_camel% input is null "); 915 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 916 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %pk_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 917 } 918 919 int deleteResult = mapper.delete%tbl_name_upper_camel%ByPrimaryKey(%pk_lower_camel%); 920 921 if (deleteResult == 0) { 922 bizLogger.warn("delete %tbl_name% result == 0 [deleteResult, %pk_lower_camel%] : "+deleteResult+","+ %pk_lower_camel%.toString()); 923 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 924 } 925 926 } 927 '; 928 929 930 931 INSERT INTO java_code 932 SELECT tbl_name,'delete','dao','通过主键删除',@query_template1,'1','' FROM all_table; 933 934 935 -- ###################################################################################################### 936 -- 通过条件和主键in查询,返回集合 937 -- ###################################################################################################### 938 939 940 SET @query_template1= 941 ' 942 // 通过条件和主键in查询,返回集合 943 @Select({ 944 "<script> ", 945 "select %col_list_alias% ", 946 "from %tbl_name% t ", 947 "<where> ", 948 %query_if_test% 949 "<if test = \'itemList != null and itemList.size() > 0\'> AND id IN " , 950 " <foreach collection=\'itemList\' item=\'item\' index=\'index\' open=\'(\' separator=\',\' close=\')\'> " , 951 " #{item,jdbcType=%pk_jdbc_type%} " , 952 " </foreach> " , 953 "</if>" , 954 "</where> ", 955 "</script>" 956 }) 957 List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListBy%pk_upper_camel%List(@Param("itemList") List<%pk_java_type%> %pk_lower_camel%ListForQuery,@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 958 '; 959 960 INSERT INTO java_code 961 SELECT tbl_name,'select','mapper','通过条件和主键in查询,返回集合',@query_template1,'1','' FROM all_table; 962 963 964 -- dao层 965 SET @query_template1= 966 ' 967 // 通过条件和主键in查询,返回集合,集合不为空，否则报错 968 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%List( List<%pk_java_type%> %pk_lower_camel%ListForQuery, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 969 970 if((%pk_lower_camel%ListForQuery == null || %pk_lower_camel%ListForQuery.size()==0 ) 971 && (%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery))){ 972 bizLogger.warn(" select %tbl_name% %pk_lower_camel%ListForQuery && %tbl_name_lower_camel%ForQuery is null at same time"); 973 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 974 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer input %pk_lower_camel%ListForQuery and %tbl_name_lower_camel%ForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); 975 } 976 977 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%ListBy%pk_upper_camel%List(%pk_lower_camel%ListForQuery,%tbl_name_lower_camel%ForQuery); 978 979 if(%tbl_name_lower_camel%List == null || %tbl_name_lower_camel%List.size()==0){ 980 bizLogger.warn(" select %tbl_name% , but result list is null or size=0 ,%tbl_name_lower_camel%ForQuery : " 981 + %tbl_name_lower_camel%ForQuery.toString()+"; %pk_lower_camel%ListForQuery : "+%pk_lower_camel%ListForQuery.toString()); 982 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 983 } 984 985 %order_by_create_time% 986 987 return %tbl_name_lower_camel%List; 988 989 } 990 '; 991 992 INSERT INTO java_code 993 SELECT tbl_name,'select','dao','通过条件和主键in查询,返回集合，集合可空',@query_template1,'1','' FROM all_table; 994 995 SET @query_template1= 996 ' 997 // 通过条件和主键in查询,返回集合,集合不为空，否则报错 998 public List<%tbl_name_upper_camel%> get%tbl_name_upper_camel%ListWithNull( List<%pk_java_type%> %pk_lower_camel%ListForQuery, %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 999 1000 if((%pk_lower_camel%ListForQuery == null || %pk_lower_camel%ListForQuery.size()==0 ) 1001 && (%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery))){ 1002 bizLogger.warn(" select %tbl_name% %pk_lower_camel%ListForQuery && %tbl_name_lower_camel%ForQuery is null at same time"); 1003 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1004 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer input %pk_lower_camel%ListForQuery and %tbl_name_lower_camel%ForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); 1005 } 1006 1007 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = mapper.get%tbl_name_upper_camel%ListBy%pk_upper_camel%List(%pk_lower_camel%ListForQuery,%tbl_name_lower_camel%ForQuery); 1008 1009 1010 1011 %order_by_create_time% 1012 1013 return %tbl_name_lower_camel%List; 1014 1015 } 1016 '; 1017 1018 INSERT INTO java_code 1019 SELECT tbl_name,'select','dao','通过条件和主键in查询,返回集合，集合可空',@query_template1,'1','' FROM all_table; 1020 1021 1022 1023 -- ################################################################################# 1024 -- ################################################################################# 1025 -- ################################################################################# 1026 -- ################################################################################# 1027 -- ############################## mapper update #################################### 1028 -- ################################################################################# 1029 -- ################################################################################# 1030 1031 -- ###################################################################################################### 1032 -- 通过主键更新 1033 -- ###################################################################################################### 1034 1035 1036 SET @query_template1= 1037 ' 1038 // 通过主键更新 1039 @Update({ 1040 "update %tbl_name% set ", 1041 "%update_chase% ", 1042 "where %pk% = #{updateObj.%pk_lower_camel%,jdbcType=%pk_jdbc_type%} " 1043 }) 1044 int update%tbl_name_upper_camel%ByPrimaryKey(@Param("updateObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate); 1045 '; 1046 1047 1048 1049 INSERT INTO java_code 1050 SELECT tbl_name,'update','mapper','通过主键更新',@query_template1,'1','' FROM all_table; 1051 1052 -- dao 1053 1054 SET @query_template1= 1055 ' 1056 // 通过主键更新 1057 public void update%tbl_name_upper_camel%ByPrimaryKey(%tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate){ 1058 1059 if(%tbl_name_lower_camel%ForUpdate == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForUpdate)){ 1060 bizLogger.warn(" update %tbl_name% dao layer input %tbl_name_lower_camel%ForUpdate is null "); 1061 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1062 ResultInfo.SYS_INNER_ERROR.getDesc() + " dao layer %tbl_name_lower_camel%ForUpdate is null , bizId=" + BizLogUtils.getValueOfBizId()); 1063 } 1064 1065 %update_set_time% 1066 1067 int updateResult = 0; 1068 1069 try { 1070 updateResult = mapper.update%tbl_name_upper_camel%ByPrimaryKey(%tbl_name_lower_camel%ForUpdate); 1071 } catch (DuplicateKeyException e) { 1072 bizLogger.warn(" update %tbl_name% duplicateKeyException ,%tbl_name_lower_camel%ForUpdate : " 1073 + %tbl_name_lower_camel%ForUpdate.toString()); 1074 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1075 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1076 } 1077 1078 1079 if (updateResult == 0) { 1080 bizLogger.warn("update %tbl_name% result == 0 [updateResult, %tbl_name_lower_camel%ForUpdate] : "+updateResult+","+ %tbl_name_lower_camel%ForUpdate.toString()); 1081 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1082 } 1083 1084 } 1085 '; 1086 1087 1088 INSERT INTO java_code 1089 SELECT tbl_name,'update','dao','通过主键更新',@query_template1,'1','' FROM all_table; 1090 1091 -- ###################################################################################################### 1092 -- 通过条件更新,如实更新，查询条件有null,就code is null. 1093 -- ###################################################################################################### 1094 1095 SET @query_template1= 1096 ' 1097 // 通过条件更新 1098 @Update({ 1099 "<script> ", 1100 "update %tbl_name% set ", 1101 "%update_chase% ", 1102 "<where>", 1103 %query_if_test_with_null% 1104 "</where>", 1105 "</script>" 1106 }) 1107 int update%tbl_name_upper_camel%ByNull(@Param("updateObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate,@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 1108 '; 1109 1110 1111 INSERT INTO java_code 1112 SELECT tbl_name,'update','mapper','通过条件更新，如实更新，且查询条件有null,就code is null.',@query_template1,'1','' FROM all_table; 1113 1114 -- dao 1115 SET @query_template1= 1116 ' 1117 // 通过条件更新，如实更新，且查询条件有null,就code is null 1118 public void update%tbl_name_upper_camel%ByNull(%tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate,%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 1119 1120 if(%tbl_name_lower_camel%ForUpdate == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForUpdate) 1121 || %tbl_name_lower_camel%ForQuery==null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 1122 bizLogger.warn(" update %tbl_name% dao layer input , but %tbl_name_lower_camel%ForUpdate or %tbl_name_lower_camel%ForQuery is null "); 1123 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1124 ResultInfo.SYS_INNER_ERROR.getDesc() + " %tbl_name_lower_camel%ForUpdate or %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 1125 } 1126 1127 %update_set_time% 1128 1129 int updateResult = 0; 1130 1131 try { 1132 updateResult = mapper.update%tbl_name_upper_camel%ByNull(%tbl_name_lower_camel%ForUpdate,%tbl_name_lower_camel%ForQuery); 1133 } catch (DuplicateKeyException e) { 1134 bizLogger.error(" update %tbl_name% duplicateKeyException ,%tbl_name_lower_camel%ForQuery : " 1135 + %tbl_name_lower_camel%ForQuery.toString()); 1136 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1137 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1138 } 1139 1140 if (updateResult == 0) { 1141 bizLogger.warn("update %tbl_name% result == 0 [updateResult, %tbl_name_lower_camel%ForQuery] : "+updateResult+","+ %tbl_name_lower_camel%ForQuery.toString()); 1142 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1143 } 1144 1145 } 1146 '; 1147 1148 INSERT INTO java_code 1149 SELECT tbl_name,'update','dao','通过条件更新，如实更新，且查询条件有null,就code is null',@query_template1,'1','' FROM all_table; 1150 1151 -- ###################################################################################################### 1152 -- 通过条件更新 1153 -- ###################################################################################################### 1154 1155 SET @query_template1= 1156 ' 1157 // 通过条件更新 1158 @Update({ 1159 "<script> ", 1160 "update %tbl_name% ", 1161 "<set>", 1162 %update_if_test% 1163 "</set>", 1164 "<where>", 1165 %query_if_test% 1166 "</where>", 1167 "</script>" 1168 }) 1169 int update%tbl_name_upper_camel%(@Param("updateObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate,@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery); 1170 '; 1171 1172 1173 INSERT INTO java_code 1174 SELECT tbl_name,'update','mapper','通过条件更新',@query_template1,'1','' FROM all_table; 1175 1176 -- dao 1177 SET @query_template1= 1178 ' 1179 // 通过条件更新 1180 public void update%tbl_name_upper_camel%(%tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate,%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery){ 1181 1182 if(%tbl_name_lower_camel%ForUpdate == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForUpdate) 1183 || %tbl_name_lower_camel%ForQuery==null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery)){ 1184 bizLogger.warn(" update %tbl_name% dao layer input , but %tbl_name_lower_camel%ForUpdate or %tbl_name_lower_camel%ForQuery is null "); 1185 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1186 ResultInfo.SYS_INNER_ERROR.getDesc() + " %tbl_name_lower_camel%ForUpdate or %tbl_name_lower_camel%ForQuery is null , bizId=" + BizLogUtils.getValueOfBizId()); 1187 } 1188 1189 %update_set_time% 1190 1191 int updateResult = 0; 1192 1193 try { 1194 updateResult = mapper.update%tbl_name_upper_camel%(%tbl_name_lower_camel%ForUpdate,%tbl_name_lower_camel%ForQuery); 1195 } catch (DuplicateKeyException e) { 1196 bizLogger.error(" update %tbl_name% duplicateKeyException ,%tbl_name_lower_camel%ForQuery : " 1197 + %tbl_name_lower_camel%ForQuery.toString()); 1198 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1199 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1200 } 1201 1202 if (updateResult == 0) { 1203 bizLogger.warn("update %tbl_name% result == 0 [updateResult, %tbl_name_lower_camel%ForQuery] : "+updateResult+","+ %tbl_name_lower_camel%ForQuery.toString()); 1204 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1205 } 1206 1207 } 1208 '; 1209 1210 1211 INSERT INTO java_code 1212 SELECT tbl_name,'update','dao','通过条件更新',@query_template1,'1','' FROM all_table; 1213 1214 1215 1216 -- ###################################################################################################### 1217 -- 通过条件和主键in更新 1218 -- ###################################################################################################### 1219 1220 1221 SET @query_template1= 1222 ' 1223 // 通过条件和主键in更新 1224 @Update({ 1225 "<script> ", 1226 "update %tbl_name% ", 1227 "<set>", 1228 %update_if_test% 1229 "</set>", 1230 "<where>", 1231 %query_if_test% 1232 "<if test = \'itemList != null and itemList.size() > 0\'> AND %pk% IN " , 1233 " <foreach collection=\'itemList\' item=\'item\' index=\'index\' open=\'(\' separator=\',\' close=\')\'> " , 1234 " #{item,jdbcType=%pk_jdbc_type%} " , 1235 " </foreach> " , 1236 "</if>" , 1237 "</where>", 1238 "</script>" 1239 }) 1240 int update%tbl_name_upper_camel%By%pk_upper_camel%List(@Param("itemList") List<%pk_java_type%> %pk_lower_camel%ListForQuery,@Param("queryObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery,@Param("updateObj") %tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate); 1241 '; 1242 1243 1244 INSERT INTO java_code 1245 SELECT tbl_name,'update','mapper','通过条件和主键in更新',@query_template1,'1','' FROM all_table; 1246 1247 -- dao 1248 1249 SET @query_template1= 1250 ' 1251 // 通过条件和主键in更新 1252 public void update%tbl_name_upper_camel%(List<%pk_java_type%> %pk_lower_camel%ListForQuery,%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery,%tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate){ 1253 1254 if((%pk_lower_camel%ListForQuery == null || %pk_lower_camel%ListForQuery.size()==0 ) 1255 && (%tbl_name_lower_camel%ForQuery == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%ForQuery))){ 1256 bizLogger.warn(" update %tbl_name% dao layer input , but %pk_lower_camel%ListForQuery and %tbl_name_lower_camel%ForQuery is null at same time"); 1257 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1258 ResultInfo.SYS_INNER_ERROR.getDesc() + " %pk_lower_camel%ListForQuery and %tbl_name_lower_camel%ForQuery is null at same time , bizId=" + BizLogUtils.getValueOfBizId()); 1259 } 1260 1261 if(%tbl_name_lower_camel%ForUpdate == null ){ 1262 bizLogger.warn(" update %tbl_name% dao layer input , but %tbl_name_lower_camel%ForUpdate is null "); 1263 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1264 ResultInfo.SYS_INNER_ERROR.getDesc() + " %tbl_name_lower_camel%ForUpdatey is null , bizId=" + BizLogUtils.getValueOfBizId()); 1265 } 1266 1267 %update_set_time% 1268 1269 int updateResult = 0; 1270 1271 try { 1272 updateResult = mapper.update%tbl_name_upper_camel%By%pk_upper_camel%List(%pk_lower_camel%ListForQuery,%tbl_name_lower_camel%ForQuery,%tbl_name_lower_camel%ForUpdate); 1273 } catch (DuplicateKeyException e) { 1274 bizLogger.error(" update %tbl_name% duplicateKeyException ,%tbl_name_lower_camel%ForQuery : " 1275 + %tbl_name_lower_camel%ForQuery.toString()+" ; %pk_lower_camel%ListForQuery: "+%pk_lower_camel%ListForQuery); 1276 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1277 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1278 } 1279 1280 if (updateResult == 0) { 1281 bizLogger.warn("update %tbl_name% result == 0 [updateResult, %tbl_name_lower_camel%ForQuery,%pk_lower_camel%ListForQuery] : "+updateResult+","+ %tbl_name_lower_camel%ForQuery.toString()+","+%pk_lower_camel%ListForQuery); 1282 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1283 } 1284 1285 } 1286 '; 1287 1288 1289 INSERT INTO java_code 1290 SELECT tbl_name,'update','dao','通过条件和主键in更新',@query_template1,'1','' FROM all_table; 1291 1292 1293 1294 -- ################################################################################# 1295 -- ################################################################################# 1296 -- ################################################################################# 1297 -- ################################################################################# 1298 -- ############################## mapper insert #################################### 1299 -- ################################################################################# 1300 -- ################################################################################# 1301 1302 -- ###################################################################################################### 1303 -- 单条插入:id自增 1304 -- ###################################################################################################### 1305 1306 SET @query_template1= 1307 ' 1308 // 单条插入:id自增 1309 @Insert({ 1310 "insert into %tbl_name% ", 1311 "(%col_list%)", 1312 "values ", 1313 "(%insert_if_test%) " 1314 }) 1315 @Options(useGeneratedKeys = true, keyProperty = "item.%pk%", keyColumn = "%pk%") 1316 int insert%tbl_name_upper_camel%(@Param("item") %tbl_name_upper_camel% %tbl_name_lower_camel%); 1317 '; 1318 1319 1320 INSERT INTO java_code 1321 SELECT tbl_name,'insert','mapper','单条插入',@query_template1,'1','id自增' FROM all_table; 1322 1323 -- dao 1324 SET @query_template1= 1325 ' 1326 // 单条插入:id自增 1327 public void insert%tbl_name_upper_camel%(%tbl_name_upper_camel% %tbl_name_lower_camel%){ 1328 1329 if(%tbl_name_lower_camel% == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%) ){ 1330 bizLogger.warn(" insert %tbl_name% dao layer input , but %tbl_name_lower_camel% is null "); 1331 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1332 ResultInfo.SYS_INNER_ERROR.getDesc() + " %tbl_name_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 1333 } 1334 1335 %insert_set_time% 1336 1337 %pk_java_type% insertResult = null; 1338 1339 try { 1340 insertResult = mapper.insert%tbl_name_upper_camel%(%tbl_name_lower_camel%); 1341 } catch (DuplicateKeyException e) { 1342 bizLogger.error(" insert %tbl_name% duplicateKeyException ,%tbl_name_lower_camel% : " 1343 + %tbl_name_lower_camel%.toString()); 1344 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1345 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1346 } 1347 1348 if (insertResult==null || insertResult==0) { 1349 bizLogger.warn("insert %tbl_name% result is null or result == 0 , %tbl_name_lower_camel%: "+%tbl_name_lower_camel%.toString()); 1350 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1351 } 1352 1353 } 1354 '; 1355 1356 INSERT INTO java_code 1357 SELECT tbl_name,'insert','dao','单条插入',@query_template1,'1','id自增' FROM all_table; 1358 1359 -- ###################################################################################################### 1360 -- 单条插入：id不自增 1361 -- ###################################################################################################### 1362 1363 SET @query_template1= 1364 ' 1365 // 单条插入：id不自增 1366 @Insert({ 1367 "insert into %tbl_name% ", 1368 "(%col_list%)", 1369 "values ", 1370 "(%insert_if_test%) " 1371 }) 1372 int insert%tbl_name_upper_camel%NoIncr(@Param("item") %tbl_name_upper_camel% %tbl_name_lower_camel%); 1373 '; 1374 1375 1376 INSERT INTO java_code 1377 SELECT tbl_name,'insert','mapper','单条插入',@query_template1,'2','id不自增' FROM all_table; 1378 1379 -- dao 1380 SET @query_template1= 1381 ' 1382 // 单条插入：id不自增 1383 public void insert%tbl_name_upper_camel%NoIncr(%tbl_name_upper_camel% %tbl_name_lower_camel%){ 1384 1385 if(%tbl_name_lower_camel% == null || check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_lower_camel%) ){ 1386 bizLogger.warn(" insert %tbl_name% %tbl_name_lower_camel% is null "); 1387 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1388 ResultInfo.SYS_INNER_ERROR.getDesc() + " %tbl_name_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 1389 } 1390 1391 %insert_set_time% 1392 1393 int insertResult = 0; 1394 try { 1395 insertResult = mapper.insert%tbl_name_upper_camel%(%tbl_name_lower_camel%); 1396 } catch (DuplicateKeyException e) { 1397 bizLogger.error(" insert %tbl_name% duplicateKeyException ,%tbl_name_lower_camel% : " 1398 + %tbl_name_lower_camel%.toString()); 1399 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1400 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate exception ,bizId=" + BizLogUtils.getValueOfBizId(),e); 1401 } 1402 1403 if ( insertResult==0) { 1404 bizLogger.warn("insert %tbl_name% result is null or result == 0 , %tbl_name_lower_camel%: "+%tbl_name_lower_camel%.toString()); 1405 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1406 } 1407 1408 } 1409 '; 1410 1411 INSERT INTO java_code 1412 SELECT tbl_name,'insert','dao','单条插入',@query_template1,'2','id不自增' FROM all_table; 1413 1414 1415 -- ###################################################################################################### 1416 -- 批量插入 1417 -- ###################################################################################################### 1418 SET @query_template1= 1419 ' 1420 // 批量插入 1421 @Insert({ 1422 "<script> ", 1423 "insert into %tbl_name% ( %col_list% ) values", 1424 "<foreach collection=\'itemList\' item=\'item\' index=\'index\' open=\'(\' separator=\'),(\' close=\')\'>", 1425 "%insert_if_test% ", 1426 "</foreach>", 1427 "</script>" 1428 }) 1429 int batchInsert%tbl_name_upper_camel%(@Param("itemList") List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List); 1430 '; 1431 1432 1433 INSERT INTO java_code 1434 SELECT tbl_name,'insert','mapper','批量插入', @query_template1,'1','' FROM all_table; 1435 1436 -- dao 1437 1438 SET @query_template1= 1439 ' 1440 // 批量插入 1441 @SuppressWarnings("unchecked") 1442 public int batchInsert%tbl_name_upper_camel%(Object object) { 1443 // 类型转换，支持单个对象或者集合形式作为入参 1444 List<%tbl_name_upper_camel%> list = null; 1445 if (object instanceof %tbl_name_upper_camel%) { 1446 list = new ArrayList<%tbl_name_upper_camel%>(); 1447 list.add((%tbl_name_upper_camel%) object); 1448 } else if (object instanceof List) { 1449 for (Object o : (List<?>) object) { 1450 if (!(o instanceof %tbl_name_upper_camel%)) { 1451 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()); 1452 } 1453 } 1454 list = (List<%tbl_name_upper_camel%>) object; 1455 } else { 1456 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",object type is error for batch insert" + BizLogUtils.getValueOfBizId()); 1457 } 1458 1459 // 如果集合为空则报异常 1460 if (list == null || list.size() == 0) { 1461 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",batch insert empty ,bizId=" + BizLogUtils.getValueOfBizId()); 1462 } 1463 1464 //设置创建时间 1465 %batch_insert_set_time% 1466 1467 // 插入阈值， 每多少条commit一次，默认是200条做一次。 1468 int threshold = 200; 1469 1470 int result = 0; 1471 int sum = list.size(); 1472 int end = 0; 1473 for (int i = 0; i < sum; i = i + threshold) { 1474 end = i + threshold > sum ? sum : i + threshold; 1475 try { 1476 result += mapper.batchInsert%tbl_name_upper_camel%(list.subList(i, end)); 1477 } catch (Exception e) { 1478 // 根据业务做补偿机制，例如通过end值，将之前插入的值全部删除或者状态翻转为无效 1479 batchInsert%tbl_name_upper_camel%FailOffset(list.subList(0, end)); 1480 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); 1481 } 1482 } 1483 return result; 1484 } 1485 1486 // 批量插入失败后，进行相关补偿操作 1487 private void batchInsert%tbl_name_upper_camel%FailOffset(List<%tbl_name_upper_camel%> list) { 1488 1489 // 补偿操作，可以比插入操作的阈值大一点， 每多少条commit一次，默认是400条做一次。 1490 int threshold = 400; 1491 int sum = list.size(); 1492 int end = 0; 1493 for (int i = 0; i < sum; i = i + threshold) { 1494 end = i + threshold > sum ? sum : i + threshold; 1495 try { 1496 // TODO 批量插入失败后，需要进行补偿的操作，例如：将之前插入的值全部删除或者状态翻转为无效 1497 //List<%pk_java_type%> %pk%List = list.subList(i, end).stream().map(%tbl_name_upper_camel%::get%pk_upper_camel%).collect(Collectors.toList()); 1498 //%tbl_name_upper_camel% %tbl_name_lower_camel%ForUpdate = new %tbl_name_upper_camel%(); 1499 //%tbl_name_lower_camel%ForUpdate.set%pk_upper_camel%(list.get(i).get%pk_upper_camel%()); 1500 //%tbl_name_lower_camel%ForUpdate.setStatus(%tbl_name_upper_camel%.STATUS_INVALID); 1501 //delete%tbl_name_upper_camel%ByPrimaryKey(list.get(i).get%pk_upper_camel%()); 1502 //update%tbl_name_upper_camel%(%pk%List,null,%tbl_name_lower_camel%ForUpdate); 1503 } catch (Exception e) { 1504 // 如果做业务补偿的时候也失败了，只能将重要信息打印在日志里面，运维干预进行恢复了 1505 throw new BusinessException( ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ", [offset batch insert error] failed ,"+ ",bizId: " + BizLogUtils.getValueOfBizId(), e); 1506 } 1507 } 1508 1509 } 1510 '; 1511 1512 1513 INSERT INTO java_code 1514 SELECT tbl_name,'insert','dao','批量插入', @query_template1,'1','' FROM all_table; 1515 1516 1517 1518 -- ###################################################################################################### 1519 -- pojo setter方法 1520 -- ###################################################################################################### 1521 1522 INSERT INTO java_code 1523 SELECT tbl_name,'pojo','setter','实体类赋值',pojo_code,'1','' 1524 FROM ( 1525 SELECT tbl_name , 1526 ( 1527 SELECT CONCAT_WS('','/* 新建对象*/\r\n','%tbl_name_upper_camel%',' ','%tbl_name_lower_camel%','= new ','%tbl_name_upper_camel%','();\r\n\r\n/*设置属性*/\r\n', 1528 group_concat( 1529 /* cdkmallGoodsApply.setUserUuid(userUuid); */ 1530 CONCAT_WS( '' 1531 ,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') 1532 ,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'),'_','') 1533 ,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'),'_','') 1534 ,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'),'_','') 1535 ) SEPARATOR '\r\n' 1536 ) 1537 ) as pojo_code 1538 FROM 1539 information_schema.COLUMNS c 1540 WHERE 1541 c.table_schema= DATABASE() AND 1542 c.TABLE_NAME = a.tbl_name 1543 ) AS pojo_code 1544 FROM all_table a 1545 ) tt; 1546 1547 1548 -- ###################################################################################################### 1549 -- 通过外键字段查询，返回对象 1550 -- ###################################################################################################### 1551 1552 SET @query_template1= 1553 ' 1554 // 通过外键查询，返回对象 1555 @Select({ 1556 "select %col_list_alias% ", 1557 "from %tbl_name% t ", 1558 "where %col% = #{%col_lower_camel%,jdbcType=%jdbc_type%}" 1559 }) 1560 %tbl_name_upper_camel% get%tbl_name_upper_camel%By%col_upper_camel%(%java_type% %col_lower_camel%); 1561 '; 1562 1563 INSERT INTO java_code 1564 SELECT tbl_name,'select','mapper','通用外键字段查询，返回对象', 1565 REPLACE ( 1566 REPLACE ( 1567 REPLACE ( 1568 REPLACE ( 1569 REPLACE (@query_template1,'%col%',col) 1570 ,'%col_lower_camel%',col_lower_camel) 1571 ,'%jdbc_type%',jdbc_type) 1572 ,'%java_type%',java_type) 1573 ,'%col_upper_camel%',col_upper_camel),'1','' 1574 FROM 1575 ( /*外键表及字段列表*/ 1576 SELECT * 1577 FROM all_col_table a 1578 JOIN (select distinct rf_name,rf_col from fk_def) d 1579 ON d.rf_name=a.tbl_name AND a.col=d.rf_col 1580 ) t 1581 ; 1582 1583 1584 -- dao层 1585 SET @query_template1= 1586 ' 1587 // 通用外键查询，返回对象，对象不为空，否则报错 1588 public %tbl_name_upper_camel% get%tbl_name_upper_camel%By%col_upper_camel%(%java_type% %col_lower_camel%){ 1589 1590 if(%col_lower_camel% == null){ 1591 bizLogger.warn(" select %tbl_name% , but dao layer input %col_lower_camel% is null "); 1592 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1593 ResultInfo.SYS_INNER_ERROR.getDesc() + " %col_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 1594 } 1595 1596 %tbl_name_upper_camel% %tbl_name_lower_camel% = mapper.get%tbl_name_upper_camel%By%col_upper_camel%(%col_lower_camel%); 1597 1598 if(%tbl_name_lower_camel% == null){ 1599 bizLogger.warn(" select %tbl_name% by %col% ,but find null ,%col_lower_camel% : " 1600 + %col_lower_camel%.toString()); 1601 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 1602 } 1603 1604 return %tbl_name_lower_camel%; 1605 } 1606 '; 1607 1608 INSERT INTO java_code 1609 SELECT tbl_name,'select','dao','通用外键字段查询，返回对象，对象不为空，否则报错', 1610 REPLACE ( 1611 REPLACE ( 1612 REPLACE ( 1613 REPLACE ( 1614 REPLACE (@query_template1,'%col%',col) 1615 ,'%col_lower_camel%',col_lower_camel) 1616 ,'%jdbc_type%',jdbc_type) 1617 ,'%java_type%',java_type) 1618 ,'%col_upper_camel%',col_upper_camel),'1','' 1619 FROM 1620 ( /*外键表及字段列表*/ 1621 SELECT * 1622 FROM all_col_table a 1623 JOIN (select distinct rf_name,rf_col from fk_def) d 1624 ON d.rf_name=a.tbl_name AND a.col=d.rf_col 1625 ) t 1626 ; 1627 1628 1629 SET @query_template1= 1630 ' 1631 // 通用外键查询，返回对象，对象可为空 1632 public %tbl_name_upper_camel% get%tbl_name_upper_camel%By%col_upper_camel%WithNull(%java_type% %col_lower_camel%){ 1633 1634 if(%col_lower_camel% == null){ 1635 bizLogger.warn(" select %tbl_name% , but dao layer input %col_lower_camel% is null "); 1636 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1637 ResultInfo.SYS_INNER_ERROR.getDesc() + " %col_lower_camel% is null , bizId=" + BizLogUtils.getValueOfBizId()); 1638 } 1639 1640 %tbl_name_upper_camel% %tbl_name_lower_camel% = mapper.get%tbl_name_upper_camel%By%col_upper_camel%(%col_lower_camel%); 1641 1642 return %tbl_name_lower_camel%; 1643 } 1644 '; 1645 1646 INSERT INTO java_code 1647 SELECT tbl_name,'select','dao','通用外键字段查询，返回对象，对象可为空', 1648 REPLACE ( 1649 REPLACE ( 1650 REPLACE ( 1651 REPLACE ( 1652 REPLACE (@query_template1,'%col%',col) 1653 ,'%col_lower_camel%',col_lower_camel) 1654 ,'%jdbc_type%',jdbc_type) 1655 ,'%java_type%',java_type) 1656 ,'%col_upper_camel%',col_upper_camel),'1','' 1657 FROM 1658 ( /*外键表及字段列表*/ 1659 SELECT * 1660 FROM all_col_table a 1661 JOIN (select distinct rf_name,rf_col from fk_def) d 1662 ON d.rf_name=a.tbl_name AND a.col=d.rf_col 1663 ) t 1664 ; 1665 1666 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1667 1668 -- 自动为insert dao层添加 setCreateTime, update dao层设置setLastUpdateTime 1669 1670 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1671 1672 SET @temp='%tbl_name_lower_camel%ForUpdate.%col_setter%(LocalDateTime.now());'; 1673 1674 UPDATE all_table a SET a.update_set_time= 1675 IFNULL( 1676 ( 1677 SELECT 1678 REPLACE( 1679 REPLACE(@temp,'%tbl_name_lower_camel%',c.tbl_name_lower_camel) 1680 ,'%col_setter%',c.col_setter) 1681 FROM all_col_table c WHERE c.col = 'last_update_time' AND a.tbl_name=c.tbl_name 1682 ) 1683 ,' '); 1684 1685 SET @temp=' %tbl_name_lower_camel%.%col_setter%(LocalDateTime.now());'; 1686 1687 UPDATE all_table a SET a.insert_set_time= 1688 IFNULL( 1689 ( 1690 SELECT GROUP_CONCAT( 1691 REPLACE( 1692 REPLACE(@temp,'%tbl_name_lower_camel%',c.tbl_name_lower_camel) 1693 ,'%col_setter%',c.col_setter) order by id SEPARATOR '\r\n') 1694 FROM all_col_table c WHERE c.col IN( 'last_update_time','create_time') AND a.tbl_name=c.tbl_name 1695 ) 1696 ,' '); 1697 1698 SET @temp=' 1699 for(%tbl_name_upper_camel% %tbl_name_lower_camel%:list){ 1700 %insert_set_time% 1701 } 1702 '; 1703 1704 -- 为了格式化：replace(insert_set_time,' ',' ') 1705 UPDATE all_table SET batch_insert_set_time= 1706 REPLACE( 1707 REPLACE( 1708 REPLACE(@temp,'%tbl_name_lower_camel%',tbl_name_lower_camel) 1709 ,'%tbl_name_upper_camel%',tbl_name_upper_camel) 1710 ,'%insert_set_time%',replace(insert_set_time,' ',' ')); 1711 1712 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1713 1714 -- 有create_time字段，按照该字段排序 1715 1716 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1717 set @create_time_phase= 1718 ' 1719 // 默认按照createTime降序排列，即刚创建的在前面显示 1720 //Comparator<%tbl_name_upper_camel%> comparator = (first, second) -> second.getCreateTime().compareTo(first.getCreateTime()); 1721 //%tbl_name_lower_camel%List.sort(comparator); 1722 1723 if(%tbl_name_lower_camel%List !=null){ 1724 %tbl_name_lower_camel%List.sort(new Comparator<%tbl_name_upper_camel%>() { 1725 public int compare(%tbl_name_upper_camel% o1, %tbl_name_upper_camel% o2) { 1726 return o2.getCreateTime().compareTo(o1.getCreateTime()); 1727 } 1728 }); 1729 } 1730 1731 1732 // %tbl_name_lower_camel%List.forEach(System.out::println); 1733 1734 /* 排序依据：当返回的参数名称与形参名顺序相同时，则为升序。反之为降序。 1735 public int compare(Integer t1, Integer t2) { 1736 return t1-t2; //升序 1737 } 1738 public int compare(Integer t1, Integer t2) { 1739 return t2-t1; //降序 1740 } 1741 t1- t2>0 会交换两个元素的位置。 1742 */ 1743 '; 1744 -- 有create_time的设置按照时间排序 1745 UPDATE java_code c SET c.java_code= 1746 ( 1747 SELECT 1748 if(COUNT(*)=0,REPLACE(c.java_code,'%order_by_create_time%',''),REPLACE(c.java_code,'%order_by_create_time%',@create_time_phase)) 1749 FROM all_col_table a 1750 WHERE a.tbl_name=c.tbl_name AND a.col='create_time' 1751 ); 1752 1753 1754 -- ###################################################################################################### 1755 1756 SET @query_template1= 1757 ' 1758 // 判断每个字段是否为空 1759 private boolean check%tbl_name_upper_camel%AllFieldsIsNull(%tbl_name_upper_camel% object) { 1760 if (null == object) { 1761 return true; 1762 } 1763 try { 1764 for (java.lang.reflect.Field f : object.getClass().getDeclaredFields()) { 1765 f.setAccessible(true); 1766 if (f.get(object) != null 1767 && org.apache.commons.lang3.StringUtils.isNotBlank(f.get(object).toString())) { 1768 return false; 1769 } 1770 } 1771 } catch (Exception e) { 1772 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 1773 ResultInfo.SYS_INNER_ERROR.getDesc() + " check%tbl_name_upper_camel%AllFieldsIsNull error , bizId=" + BizLogUtils.getValueOfBizId(),e); 1774 } 1775 return true; 1776 } 1777 '; 1778 1779 INSERT INTO java_code 1780 SELECT tbl_name,'insert','dao','判断每个字段是否为空',@query_template1,'1','' FROM all_table; 1781 1782 1783 -- ###################################################################################################### 1784 -- ###################################################################################################### 1785 1786 -- 将模板中的%xx%占位符统一全部替换掉 1787 1788 /* 1789 SET @temp=' 1790 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,\'@%tbl_name%@\', 1791 ifnull((select a.%tbl_name% FROM %all_table% a WHERE c.tbl_name=a.tbl_name ),\'@%tbl_name%@\') 1792 ); 1793 '; 1794 select code from 1795 ( 1796 SELECT (@id:=@id+1) as id,t1.TABLE_NAME, 1797 replace( 1798 REPLACE(@temp,'%tbl_name%',t1.COLUMN_NAME) 1799 ,'%all_table%',t1.TABLE_NAME) 1800 AS code 1801 FROM 1802 information_schema.COLUMNS t1 1803 JOIN (SELECT @id:=0) tt 1804 WHERE 1805 t1.table_schema= DATABASE() AND t1.TABLE_NAME IN ('all_table') 1806 ) tt 1807 ; 1808 */ 1809 1810 1811 1812 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%tbl_name%', 1813 ifnull((select a.tbl_name FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%tbl_name%') 1814 ); 1815 1816 1817 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%tbl_name_upper_camel%', 1818 ifnull((select a.tbl_name_upper_camel FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%tbl_name_upper_camel%') 1819 ); 1820 1821 1822 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%tbl_name_lower_camel%', 1823 ifnull((select a.tbl_name_lower_camel FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%tbl_name_lower_camel%') 1824 ); 1825 1826 1827 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%pk%', 1828 ifnull((select a.pk FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%pk%') 1829 ); 1830 1831 1832 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%pk_upper_camel%', 1833 ifnull((select a.pk_upper_camel FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%pk_upper_camel%') 1834 ); 1835 1836 1837 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%pk_lower_camel%', 1838 ifnull((select a.pk_lower_camel FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%pk_lower_camel%') 1839 ); 1840 1841 1842 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%pk_java_type%', 1843 ifnull((select a.pk_java_type FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%pk_java_type%') 1844 ); 1845 1846 1847 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%pk_jdbc_type%', 1848 ifnull((select a.pk_jdbc_type FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%pk_jdbc_type%') 1849 ); 1850 1851 1852 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%col_list%', 1853 ifnull((select a.col_list FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%col_list%') 1854 ); 1855 1856 1857 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%col_list_alias%', 1858 ifnull((select a.col_list_alias FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%col_list_alias%') 1859 ); 1860 1861 1862 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%insert_if_test%', 1863 ifnull((select a.insert_if_test FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%insert_if_test%') 1864 ); 1865 1866 1867 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%query_if_test%', 1868 ifnull((select a.query_if_test FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%query_if_test%') 1869 ); 1870 1871 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%query_if_test_with_null%', 1872 ifnull((select a.query_if_test_with_null FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%query_if_test_with_null%') 1873 ); 1874 1875 1876 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%update_chase%', 1877 ifnull((select a.update_chase FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%update_chase%') 1878 ); 1879 1880 1881 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%update_if_test%', 1882 ifnull((select a.update_if_test FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%update_if_test%') 1883 ); 1884 1885 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%fuzzy_query_if_test%', 1886 ifnull((select a.fuzzy_query_if_test FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%fuzzy_query_if_test%') 1887 ); 1888 1889 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%insert_set_time%', 1890 ifnull((select a.insert_set_time FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%insert_set_time%') 1891 ); 1892 1893 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%update_set_time%', 1894 ifnull((select a.update_set_time FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%update_set_time%') 1895 ); 1896 1897 UPDATE java_code c SET c.java_code= REPLACE(c.java_code,'%batch_insert_set_time%', 1898 ifnull((select a.batch_insert_set_time FROM all_table a WHERE c.tbl_name=a.tbl_name ),'%batch_insert_set_time%') 1899 ); 1900 1901 1902 1903 1904 -- SELECT * FROM java_code; 1905 1906 -- #########################################################以上为通用代码###################################################################### 1907 1908 1909 1910 1911 1912 1913 1914 -- select * from all_col_table; 1915 -- select * from all_table; 1916 1917 1918 1919 -- SELECT * FROM java_code; 1920 1921 1922 1923 -- ############################################################################################################# 1924 1925 DROP TABLE if EXISTS java_code_file; 1926 CREATE table if not exists java_code_file( 1927 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', 1928 tbl_name VARCHAR(256) NOT NULL COMMENT '表名:tbl_sapo_admin_account', 1929 tbl_name_comment VARCHAR(255) COMMENT '注释', 1930 tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount', 1931 tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount', 1932 dao text COMMENT '', 1933 mapper text COMMENT '', 1934 PRIMARY KEY (`id`) , 1935 INDEX idx_1(tbl_name) 1936 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; 1937 1938 INSERT INTO java_code_file (tbl_name,tbl_name_comment,tbl_name_upper_camel,tbl_name_lower_camel) 1939 SELECT DISTINCT tbl_name,tbl_name_comment,tbl_name_upper_camel,tbl_name_lower_camel FROM all_col_table; 1940 1941 -- SELECT * FROM java_code_file; 1942 1943 -- mapper层代码 1944 SET @template=' 1945 1946 @Mapper 1947 public interface %tbl_name_upper_camel%Mapper{ 1948 %mapper% 1949 } 1950 '; 1951 1952 UPDATE java_code_file f SET f.mapper= 1953 REPLACE( 1954 REPLACE (@template,'%mapper%', 1955 (SELECT 1956 GROUP_CONCAT(java_code separator '') 1957 FROM java_code j 1958 WHERE j.tbl_name= f.tbl_name AND j.code_layer='mapper' 1959 )) 1960 ,'%tbl_name_upper_camel%',tbl_name_upper_camel) 1961 ; 1962 1963 -- dao层代码 1964 SET @template=' 1965 1966 @Repository 1967 public class %tbl_name_upper_camel%Dao extends BaseLogger{ 1968 @Autowired 1969 protected %tbl_name_upper_camel%Mapper mapper; 1970 %dao% 1971 } 1972 '; 1973 1974 UPDATE java_code_file f SET f.dao= 1975 REPLACE( 1976 REPLACE (@template,'%dao%', 1977 (SELECT 1978 GROUP_CONCAT(java_code separator '') 1979 FROM java_code j 1980 WHERE j.tbl_name= f.tbl_name AND j.code_layer='dao' 1981 )) 1982 ,'%tbl_name_upper_camel%',tbl_name_upper_camel) 1983 ; 1984 1985 -- SELECT * FROM java_code_file; 1986 1987 1988 -- 去除中间表 1989 -- DROP TABLE all_col_table; 1990 -- DROP TABLE all_table; 1991 -- drop table fk_def; 1992 1993 /* 使用范例 1994 SELECT java_code FROM java_code WHERE tbl_name = 'tbl_ams_award_pool' AND code_layer='mapper'; 1995 SELECT java_code FROM java_code WHERE tbl_name = 'tbl_ams_award_pool' AND code_layer='dao'; 1996 */ 1997 1998 END$ 1999 DELIMITER ; 2000 2001 2002 CALL print_code(); 2003 2004 SELECT dao FROM java_code_file ; 2005 2006 SELECT mapper FROM java_code_file ; 2007 2008 SELECT tbl_name, java_code FROM java_code WHERE code_type='pojo'; 2009 2010 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 2011 -- 通用 mapper 2012 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 2013 SET @temp= 2014 ' 2015 @Mapper 2016 public interface SapoMapper{ 2017 %mapper_code% 2018 } 2019 '; 2020 2021 SELECT 2022 REPLACE(@temp,'%mapper_code%', 2023 GROUP_CONCAT( 2024 java_code SEPARATOR '' 2025 ) 2026 ) 2027 AS 通用mapper 2028 FROM java_code WHERE code_layer='mapper'; 2029 2030 2031 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 2032 -- 通用dao 2033 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 2034 SET @temp= 2035 ' 2036 @Repository 2037 public class SapoDao extends BaseLogger{ 2038 @Autowired 2039 protected SapoMapper mapper; 2040 2041 %dao_code% 2042 } 2043 '; 2044 SELECT 2045 REPLACE(@temp,'%dao_code%', 2046 GROUP_CONCAT( 2047 java_code SEPARATOR '' 2048 ) 2049 ) 2050 AS 通用dao 2051 FROM java_code WHERE code_layer='dao'; 2052 2053 2054 /* 2055 DROP TABLE if exists all_col_code; 2056 DROP TABLE if exists all_col_table; 2057 DROP TABLE if exists all_table; 2058 DROP TABLE if exists exe_sql_tbl; 2059 DROP TABLE if exists fk_all_col_table; 2060 DROP TABLE if exists fk_def; 2061 DROP TABLE if exists sys_code_mapper; 2062 DROP TABLE if exists fk_list; 2063 drop table if exists java_code; 2064 drop table if exists java_code_file; 2065 drop PROCEDURE if exists print_pojo; 2066 drop PROCEDURE if exists print_code; 2067 2068 */ 2069 2070 /* 2071 @Select({ 2072 "select t.`area_code` as 'area_code' , t.`brand_code` as 'brand_code' , t.`create_time` as 'create_time' , t.`device_exe_remain_time` as 'device_exe_remain_time' , t.`device_fee` as 'device_fee' , t.`expire_time` as 'expire_time' , t.`id` as 'id' , t.`invoice_flag` as 'invoice_flag' , t.`last_update_time` as 'last_update_time' , t.`merch_code` as 'merch_code' , t.`merch_type_code` as 'merch_type_code' , t.`order_no` as 'order_no' , t.`pay_fee` as 'pay_fee' , t.`refund_status` as 'refund_status' , t.`service_price_code` as 'service_price_code' , t.`service_spec_code` as 'service_spec_code' , t.`service_type_code` as 'service_type_code' , t.`status` as 'status' , t.`store_code` as 'store_code' , t.`store_device_code` as 'store_device_code' , t.`store_device_location` as 'store_device_location' , t.`store_type_code` as 'store_type_code' , t.`sub_status` as 'sub_status' , t.`total_fee` as 'total_fee' , t.`trace_no` as 'trace_no' , t.`user_uuid` as 'user_uuid' , t.`wechat_user_uuid` as 'wechat_user_uuid' ", 2073 "from tbl_sapo_order t ", 2074 "where expire_time is not null and expire_time <= #{taskTime,jdbcType=VARCHAR} order by expire_time desc limit #{limitNum,jdbcType=INTEGER}" 2075 }) 2076 List<SapoOrder> getExpiredSapoOrderList(@Param("taskTime") String taskTime,@Param("limitNum") Integer limitNum); 2077 2078 // 通用外键查询，返回对象 2079 public List<SapoOrder> getExpiredSapoOrderList(String taskTime,Integer limitNum){ 2080 2081 List<SapoOrder> sapoOrderList = mapper.getExpiredSapoOrderList(taskTime,limitNum); 2082 2083 return sapoOrderList; 2084 } 2085 */View Code