select o from ProductType o where o.parent is null;所搜顶级目录

本文详细解析了在使用JPA进行数据库查询时遇到的javax.persistence.EntityNotFoundException异常,并探讨了导致该问题的原因和解决方案,特别关注了查询语句中参数的影响。

select o from ProductType o where o.parent is null;所搜顶级目录

2012-07-31 16:57 w103437w2008  |  浏览 522 次   悬赏: 25
javax.persistence.EntityNotFoundException: Unable to find com.bfxy.bean.producttype.ProductType with id 0
这个异常怎么回事想不通
id  0什么意思啊
我有更好的答案

2条回答

2012-07-31 17:06 laistars  | 五级  最快回答
select o from ProductType o where o.parent is null
第一个o是什么,字段名么,对于这个我很费解
追问:
o ProductType 是别名JPA里面的 这没问题 问题是我加上o.parent is null 就报错
追答:
从提示错误上看,应该不是语句的问题,你自己想想吧!我没遇到过这样的问题
追问:
Unable to find com.bfxy.bean.producttype.ProductType with id 0

怎么会出现这样的错呢, 我查的是 id=0拿来的啊
追答:
id=0...?你的id字段是自增字段不,怎么会等于0
追问:
(Query query =em.createQuery("select o from ProductType o where o.parent is null ");执行没问题)
  for(Object ob:query.getResultList()){
} 执行报错
评论  |  0  0
2012-09-26 21:46 itaobao100  | 五级
o其实是一个对象,该方法是按照对象查询的
你传入的类的类型和数据库中你查找的表不匹配
<!--软件地图首页列表信息--> <select id="queryAllSoftwareFigure2" resultType="com.huawei.kunpeng.center.business.softwarefigure.vo.SoftwareFigureRespVO"> select tsf.software_figure_id, software_name, is_opensource, software_type productType, tsf.partner_name, tsf.partner_id, tsf.admin_region, tsf.industry, tsf.national_power nationalPower, sum(pt.trade_power) tradePower, sum(pt.native_trade_power) nativeTradePower, tsf.national_ranking nationalRanking, admin_region_ranking adminRegionRanking, <choose> <when test="dto.singleTrade != null and dto.singleTrade != '' and dto.industry == 'KP'"> sum(ifnull(pt.trade_product_num,0)) versionNum, sum(ifnull(pt.trade_adapted_product_num,0)) certVersionNum, sum(ifnull(pt.trade_compatible_num,0)) cCertifiedNum, sum(ifnull(pt.trade_validated_num,0)) vCertifiedNum, sum(ifnull(pt.trade_native_num,0)) nCertifiedNum, sum(ifnull(pt.trade_cust_num,0)) customerNum, sum(ifnull(pt.trade_project_num,0)) projectNum, </when> <otherwise> product_num versionNum, adapted_product_num certVersionNum, Compatible_num cCertifiedNum, Validated_num vCertifiedNum, native_num nCertifiedNum, tsf.cust_num customerNum, tsf.project_num projectNum, </otherwise> </choose> tsf.created_time, tsf.last_updated_time from t_software_figure tsf left join t_software_trade_figure pt on pt.software_figure_id = tsf.software_figure_id LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id where 1=1 <if test="dto.industry != null and dto.industry != ''"> and tsf.industry = #{dto.industry} AND tpf.industry = #{dto.industry} </if> <if test="dto.addrAreaId!=null and dto.addrAreaId!=''"> and tpf.admin_region in <foreach collection="dto.addrAreaId.split(',')" separator="," item="item" open="(" close=")"> #{item} </foreach> </if> <if test="dto.trade!=null and dto.trade!=''"> AND pt.trade in <foreach collection="dto.trade.split(',')" separator="," item="item" open="(" close=")"> #{item} </foreach> </if> <if test="dto.partnerLevel!=null and dto.partnerLevel!=''"> and tpf.partner_level in <foreach collection="dto.partnerLevel.split(',')" separator="," item="item" open="(" close=")"> #{item} </foreach> </if> <if test="dto.softwareType!=null and dto.softwareType!=''"> and tsf.software_type in <foreach collection="dto.softwareType.split(',')" separator="," item="item" open="(" close=")"> #{item} </foreach> </if> <if test="dto.isOpenSourceReal!=null and dto.isOpenSourceReal!=''"> and tsf.is_openSource in <foreach collection="dto.isOpenSourceReal" separator="," item="item" open="(" close=")"> #{item} </foreach> </if> <if test="dto.softwareName!=null and dto.softwareName!=''"> and lower(tsf.software_name) like CONCAT('%',lower(#{dto.softwareName}),'%') </if> <if test="dto.partnerName!=null and dto.partnerName!=''"> and tsf.partner_name like CONCAT('%',#{dto.partnerName},'%') </if> <if test="dto.isNativeSoftware != null and dto.isNativeSoftware != '' "> and tsf.native_num > 0 </if> <if test="dto.productType!=null and dto.productType!=''"> and software_type in <foreach collection="dto.productType.split(',')" item="item" open="(" close=")" separator="," > #{item} </foreach> </if> group by tsf.software_figure_id <choose> <when test="sortJson != null"> order by ${sortJson.filed} ${sortJson.mode} </when> <otherwise> order by tsf.national_power desc </otherwise> </choose> </select> <!--开发语言--> <select id="getSoftwareDevelopLanguage" resultType="java.lang.String"> SELECT DISTINCT tp.develop_language FROM t_product tp LEFT JOIN t_isv ti on tp.isv_credit_code = ti.isv_credit_code where tp.product_name = #{vo.softwareName} AND ti.partner_id = #{vo.partnerId} AND tp.develop_language is not null AND tp.develop_language != '' </select> <!--算力--> <select id="getAdaptedPowerInfo" resultType="java.lang.Long"> select SUM(t.power) FROM ( select ifnull(SUM(tppl.opr_customer_power), 0) power from t_software_figure tsf LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id LEFT JOIN t_isv ti on ti.partner_id = tsf.partner_id LEFT JOIN t_product tp on ti.isv_credit_code = tp.isv_credit_code and tp.product_name = tsf.software_name LEFT JOIN t_product_power_log tppl on tp.product_id = tppl.product_id where tppl.opr_type in ('A', 'U-A', 'U-D') AND tp.product_id is not null AND tppl.adapt_status = '2' AND tsf.software_figure_id = #{softwareFigureId} AND tsf.industry = 'KP' AND tpf.industry = 'KP' UNION ALL select ifnull(SUM(tpts.customer_power), 0) power from t_software_figure tsf LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id LEFT JOIN t_isv ti on ti.partner_id = tsf.partner_id LEFT JOIN t_product tp on ti.isv_credit_code = tp.isv_credit_code and tp.product_name = tsf.software_name LEFT JOIN t_product_trade_scene tpts on tpts.product_id = tp.product_id where tpts.approve_status = '1' AND tpts.demand_source = '0' AND tp.adapt_status = '2' AND tp.product_id is not null AND tsf.software_figure_id = #{softwareFigureId} AND tsf.industry = 'KP' AND tpf.industry = 'KP') t </select> <!--区域伙伴数量--> <select id="getPartnerNumByAdministrative" resultType="com.huawei.kunpeng.center.business.partnerfigure.dto.PartnerFigureByAdmResDTO"> select t.admin_region addrAreaId, IFNULL(t3.name_,t.admin_region) addrAreaName, t3.name_en addrAreaNameEn, count(DISTINCT t.partner_id) partnerNum, count(DISTINCT if(t.partner_level='super_top',t.partner_id,null)) superHeadNum, count(DISTINCT if(t.partner_level='trade_top',t.partner_id,null)) industryHeadNum, count(DISTINCT if(t.partner_level='trade_center',t.partner_id,null)) industryWaistNum, count(DISTINCT if(t.partner_level='area_center',t.partner_id,null)) regionalWaistNum, count(DISTINCT if(t.partner_level='long_tail',t.partner_id,null)) tailPartnersNum, <choose> <when test=" dto.industry == 'KP'"> sum(pt.trade_power) partnerPowerNum , sum(if(t.partner_level='super_top',pt.trade_power,null)) superHeadPowerNum, sum(if(t.partner_level='trade_top',pt.trade_power,null)) industryHeadPowerNum, sum(if(t.partner_level='trade_center',pt.trade_power,null)) industryWaistPowerNum, sum(if(t.partner_level='area_center',pt.trade_power,null)) regionalWaistPowerNum, sum(if(t.partner_level='long_tail',pt.trade_power,null)) tailPartnersPowerNum </when> <otherwise> sum(pt.trade_power) partnerPowerNum , sum(if(t.partner_level='super_top',t.national_power,null)) superHeadPowerNum, sum(if(t.partner_level='trade_top',t.national_power,null)) industryHeadPowerNum, sum(if(t.partner_level='trade_center',t.national_power,null)) industryWaistPowerNum, sum(if(t.partner_level='area_center',t.national_power,null)) regionalWaistPowerNum, sum(if(t.partner_level='long_tail',t.national_power,null)) tailPartnersPowerNum </otherwise> </choose> from t_partner_figure t <if test="dto.industry == 'KP'"> left join t_partner_trade_figure pt on pt.partner_figure_id = t.partner_figure_id </if> left join (select value_,name_,name_en from t_dictionary_detail t3 where t3.dict_id = (select id_ from t_dictionary t where t.value_ = 'partner_map_area') union all select if(t4.value_ = 450000, 460000,640000) value_,name_,name_en from t_dictionary_detail t4 where t4.dict_id = (select id_ from t_dictionary t where t.value_ = 'partner_map_area') and t4.value_ in ('450000','610000') ) t3 on t.admin_region=t3.value_ where t.industry = #{dto.industry} and t3.name_ is not null and EXISTS (select 1 from t_isv isv where isv.partner_id = t.partner_id) <include refid="where_sql"/> GROUP BY t3.name_ order by partnerNum desc,t3.name_ </select> <!--伙伴区域--> SELECT value_, name_, name_en FROM t_dictionary_detail t3 WHERE t3.dict_id = (SELECT id_ FROM t_dictionary t WHERE t.value_ = 'partner_map_area') UNION ALL SELECT IF(t4.value_ = 450000, 460000, 640000) value_, name_, name_en FROM t_dictionary_detail t4 WHERE t4.dict_id = (SELECT id_ FROM t_dictionary t WHERE t.value_ = 'partner_map_area') AND t4.value_ IN ('450000', '610000') 伙伴数量信息 <select id="getPartnerLevelNumInfo" resultType="com.huawei.kunpeng.center.business.partnerfigure.dao.PartnerLevelInfoDAO"> SELECT tpf.partner_level partnerLevel, COUNT(DISTINCT tpf.partner_id) resultValue, sum(if(t.level is null, 1, 0)) unregisteredNum, sum(if(t.level = 1, 1, 0)) certificationLevelNum, sum(if(t.level = 2, 1, 0)) priorityNum, sum(if(t.level = 3, 1, 0)) leadingLevelNum, sum(if(t.level = 4, 1, 0)) strategicLevelNum FROM t_partner_figure tpf LEFT JOIN ( SELECT tpl.partner_id, max( tpl.level ) level FROM t_partner_level tpl LEFT JOIN t_dictionary_detail tdd1 ON tpl.partner_program = tdd1.value_ AND tdd1.dict_id =( SELECT id_ FROM t_dictionary WHERE value_ = 'partner_program' ) LEFT JOIN t_dictionary_detail tdd2 ON tpl.level = tdd2.value_ AND tdd2.dict_id =( SELECT id_ FROM t_dictionary WHERE value_ = 'partner_auth_level' ) WHERE tdd1.value_ IS NOT NULL AND tdd1.org_type = '0' AND tdd2.value_ IS NOT NULL GROUP BY tpl.partner_id ) t ON t.partner_id = tpf.partner_id where 1=1 and EXISTS (select 1 from t_isv isv where isv.partner_id = tpf.partner_id) GROUP BY partner_level </select> <select id="getNativePartnerLevelNumInfo" resultType="com.huawei.kunpeng.center.business.partnerfigure.dao.PartnerLevelInfoDAO"> SELECT temp.partner_level partnerLevel,COUNT(DISTINCT temp.partner_id) resultValue FROM ( select tpf.partner_id,tpf.partner_level from t_partner_figure tpf left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id AND tpf.native_software_num > 0 AND tpf.partner_id in ( SELECT DISTINCT t3.partner_id FROM t_product t1 JOIN t_adaptation_project t2 ON t1.product_id = t2.product_id AND t1.region_id = t2.region_id JOIN t_adaptation_authentication ta ON t2.id = ta.adaptation_project_id AND t2.region_id = ta.region_id JOIN t_isv t3 ON t1.isv_credit_code = t3.isv_credit_code AND ta.region_id = t3.region_id WHERE t2.promotion_phase = '1391' AND ta.auth_level = 'Native' ) )temp GROUP BY temp.partner_level </select> 超级头部、行业头部、行业腰部、区域腰部、中长尾 // 排序 按照顺序输出 "super_top", "trade_top", "trade_center", "area_center", "long_tail" List<PartnerLevelNumInfoRespVO> sort = new ArrayList<>(); List<String> sortRule = ListUtil.toList("super_top", "trade_top", "trade_center", "area_center", "long_tail"); sortRule.stream().forEach(item -> { Optional<PartnerLevelNumInfoRespVO> optional = result.stream().filter(i -> item.equals(i.getPartnerLevel())).findFirst(); optional.ifPresent(sort::add); }); -- 行业 SELECT * FROM t_dictionary_detail WHERE dict_id = '2' AND status_ = 0 AND parent_id = '0'; -- 行业 伙伴数量 select count(DISTINCT tpf.partner_id) resultValue, '419' trade from t_partner_figure tpf left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id WHERE pt.trade = '419' and EXISTS (select 1 from t_isv isv where isv.partner_id = tpf.partner_id); -- 按行业 同辕伙伴数量 select count(DISTINCT tpf.partner_id) resultValue,'419' trade from t_partner_figure tpf left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id WHERE pt.trade = '419' AND tpf.native_software_num > 0 -- 伙伴列表 SELECT t1.partner_id, t1.partner_name, tab.certificateLevel, t.partner_level, t1.kp_region_id realRegionId, t2.short_org_name regionName, t.admin_region partnerAddr, sum(pt.trade_cust_num) customerNum, sum(pt.trade_project_num) projectNum, t.national_power partnerPower, sum(pt.trade_power) tradePower, sum(pt.native_trade_power) nativePower, pt.trade_software_num productNum, pt.trade_adapted_software_num adaptProductNum, pt.trade_certificate_software_num certProductNum, pt.trade_native_software_num nativeProductNum, t.national_ranking nationalRanking, t.region_ranking regionRanking FROM t_partner_figure t LEFT JOIN t_partner_trade_figure pt ON pt.partner_figure_id = t.partner_figure_id LEFT JOIN t_partners t1 ON t1.partner_id = t.partner_id LEFT JOIN sys_org t2 ON t2.org_id = t1.kp_region_id LEFT JOIN ( SELECT tpl.partner_id, GROUP_CONCAT(tpl.LEVEL) LEVEL, GROUP_CONCAT(CONCAT(tpl.identity, '-', tdd2.name_)) certificateLevel FROM t_partner_level tpl LEFT JOIN t_dictionary_detail tdd1 ON tpl.partner_program = tdd1.value_ AND tdd1.dict_id = (SELECT id_ FROM t_dictionary WHERE value_ = 'partner_program' -- 伙伴计划 ) LEFT JOIN t_dictionary_detail tdd2 ON tpl.LEVEL = tdd2.value_ AND tdd2.dict_id = (SELECT id_ FROM t_dictionary WHERE value_ = 'partner_auth_level') WHERE tdd1.value_ IS NOT NULL AND tdd1.org_type = '0' AND tdd2.value_ IS NOT NULL GROUP BY tpl.partner_id ) tab ON tab.partner_id = t.partner_id WHERE 1 = 1 AND EXISTS (SELECT 1 FROM t_isv isv WHERE isv.partner_id = t.partner_id) GROUP BY t1.partner_id 根据上边关系帮我生成表关系图
09-11
为什么下面的sql语句会输出重复的结果:SELECT tp.parent_production_orders AS parent_production_orders, tp.production_orders AS production_orders, tp.work_order AS work_order, tp.contract AS contract, tp.sbbh AS sbbh, tp.batch_num AS batch_num, tp.product_code AS product_code, tp.product_number AS product_number, tp.product_name AS product_name, to_char( middle.create_date, 'yyyy-mm-dd' ) AS issued_date, to_char( to_timestamp( tp.delivery_time / 1000 ), 'yyyy-mm-dd' ) AS delivery_time, middle.line_code AS work_area_code, middle.line_name AS work_area_name, tp.workorder_number AS workorder_number, tp.complete_number AS complete_number, tp.part_unit AS part_unit, middle.work_time_type AS work_time_type, middle.process_time AS process_time, CASE WHEN sc.totalSubmitHours IS NULL THEN 0 ELSE sc.totalSubmitHours END AS submit_work_hours, CASE WHEN middle.process_time > 0 AND sc.totalSubmitHours IS NOT NULL THEN round( ( sc.totalSubmitHours / middle.process_time ), 2 ) * 100 ELSE 0 END plan_achievement_rate, CASE WHEN sc.totalSubmitHours IS NULL THEN 0 ELSE round( CAST ( sc.totalSubmitHours AS NUMERIC ) / CAST ( 60 AS NUMERIC ), 1 ) END AS submit_work_hours_h, round( CAST ( middle.process_time AS NUMERIC ) / CAST ( 60 AS NUMERIC ), 1 ) AS process_time_h, pinfo.material_channel AS material_channel FROM hm_model_work_order_report_middle middle LEFT JOIN hm_model_trc_plan tp ON middle.work_order = tp.work_order LEFT JOIN ( SELECT oro.work_order AS orderNo, oro.work_area_code AS lineCode, SUM ( submit_work_hours ) AS totalSubmitHours, '自制' AS workHourType FROM hm_model_trc_order_report_operation_u orou LEFT JOIN hm_model_trc_order_report_operation oro ON orou.work_order_process_id = oro.ID WHERE orou.work_order_process_id IS NOT NULL AND oro.work_area_code IS NOT NULL GROUP BY oro.work_order, oro.work_area_code UNION all SELECT ohs.work_order_no AS orderNo, ohs.line_code AS lineCode, SUM ( receiving_hour ) AS totalSubmitHours, '外委' AS workHourType FROM hm_model_outsourcing_hour_statistics ohs GROUP BY ohs.work_order_no, ohs.line_code ) sc ON middle.work_order = sc.orderNo AND middle.line_code = sc.lineCode AND middle.work_time_type = sc.workHourType LEFT JOIN hm_model_part_info AS pinfo ON tp.product_number = pinfo.part_code WHERE middle.process_time > 0 AND tp.delivery_time IS NOT NULL AND tp.production_orders LIKE'FJ2023051100286' ORDER BY to_char( to_timestamp( tp.delivery_time / 1000 ), 'yyyy-mm-dd' ) DESC, tp.parent_production_orders DESC, tp.node_level ASC
06-06
CREATE TABLE `t_tree_product_approval` ( `ID` varchar(64) NOT NULL COMMENT '主键id', `APPROVAL_STATUS` tinyint(1) DEFAULT '1' COMMENT '审批状态(1草稿 2审批中 3通过 4驳回 5撤销)', `SUBMIT_BY` varchar(64) DEFAULT NULL COMMENT '提交人', `SUBMIT_DATE` varchar(100) DEFAULT NULL COMMENT '提交时间', `APPROVAL_BY` varchar(255) DEFAULT NULL COMMENT '一级审批人', `APPROVAL_DATE` datetime DEFAULT NULL COMMENT '一级审批时间', `APPROVAL_URL` varchar(500) DEFAULT NULL COMMENT '一级审批链接', `APPROVAL_REMARK` varchar(255) DEFAULT NULL COMMENT '一级审批说明', `WITHDRAWAL_DATE` datetime DEFAULT NULL COMMENT '撤回时间', `LAST_UPDATE_BY` varchar(36) DEFAULT NULL COMMENT '最后更新人', `LEVEL_APPROVAL_STATUS` tinyint(1) DEFAULT NULL COMMENT '一级审批状态(3通过, 4驳回)', `APPROVAL_CONFIRM` varchar(36) DEFAULT NULL COMMENT '二级审批人', `CONFIRM_DATE` datetime DEFAULT NULL COMMENT '二级审批时间', `CONFIRM_REMARK` varchar(255) DEFAULT NULL COMMENT '二级审批说明', `W3_DESC` text COMMENT 'w3发送对象', `LAST_UPDATE_TIME` datetime DEFAULT NULL COMMENT '最后更新时间', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='资产架构树审批表' CREATE TABLE `t_tree_product_approval_detail` ( `ID` varchar(64) NOT NULL COMMENT '主键id', `APPROVAL_ID` varchar(64) NOT NULL COMMENT '审批表主键id', `NODE_ID` varchar(64) NOT NULL COMMENT '节点id', `ASSETS_TYPE` tinyint(1) NOT NULL COMMENT '资产类型(1产品 2子产品 3模块 4应用服务 5软件单元)', `CHANGE_TYPE` tinyint(1) NOT NULL COMMENT '审批类型(1新增 2修改 3删除)', `NAME_CN` varchar(64) DEFAULT NULL COMMENT '中文名称', `NAME_EN` varchar(64) DEFAULT NULL COMMENT '英文名称', `APPROVAL_BY` varchar(255) DEFAULT NULL COMMENT '审批人', `APPLY_REMARK` varchar(255) DEFAULT NULL COMMENT '申请说明', `ATTACHMENT` varchar(500) DEFAULT NULL COMMENT '附件链接', `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间', `CREATE_BY` varchar(36) DEFAULT NULL COMMENT '创建人', `LAST_UPDATE_TIME` datetime DEFAULT NULL COMMENT '最后更新时间', `LAST_UPDATE_BY` varchar(36) DEFAULT NULL COMMENT '最后更新人', `PARENT_NODE_ID` varchar(64) DEFAULT NULL COMMENT '父节点id', `CODE` varchar(255) DEFAULT NULL COMMENT '节点code', `ORIGINAL` text COMMENT '原始数据内容', `CONTENT` text COMMENT '构建的对象', `MODIFY` text COMMENT '解析修改内容', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='资产架构树审批详情表' 这是表格初始状态,这是执行的sql脚本DROP PROCEDURE IF EXISTS PROC_TABLES_CHANGE; DELIMITER $$ CREATE PROCEDURE PROC_TABLES_CHANGE() SQL SECURITY INVOKER BEGIN -- 检查并删除列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'W3_DESC') THEN ALTER TABLE `t_tree_product_approval` DROP COLUMN `W3_DESC`; END IF; -- 检查并修改列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'LEVEL_APPROVAL_STATUS') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `LEVEL_APPROVAL_STATUS` `APPROVAL_ID` VARCHAR(64) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI NULL DEFAULT NULL COMMENT '审批表主键id' AFTER `ID`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_CONFIRM') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `APPROVAL_CONFIRM` `APPROVAL_TYPE` VARCHAR(255) NULL DEFAULT NULL COMMENT '审批节点类型' AFTER `APPROVAL_ID`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'CONFIRM_DATE') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `CONFIRM_DATE` `SORT_NUMBER` INT(11) NULL DEFAULT null COMMENT '节点排序' AFTER `APPROVAL_STATUS`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'SUBMIT_DATE') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `SUBMIT_DATE` `IMPLEMENTATION_RESULT` VARCHAR(255) NULL DEFAULT NULL COMMENT '实施结果' AFTER `APPROVAL_REMARK`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'SUBMIT_BY') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `SUBMIT_BY` `CREATE_BY` VARCHAR(64) NULL DEFAULT NULL COMMENT '创建人' AFTER `CONFIRM_REMARK`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_DATE') THEN ALTER TABLE `t_tree_product_approval` CHANGE COLUMN `APPROVAL_DATE` `CREATE_TIME` DATETIME NULL DEFAULT NULL COMMENT '创建时间' AFTER `CREATE_BY`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_STATUS') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_STATUS` TINYINT(1) NULL DEFAULT 1 COMMENT '审批状态(1草稿 2审批中 3通过 4驳回 5撤销)' AFTER `APPROVAL_TYPE`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_BY') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_BY` VARCHAR(36) NULL DEFAULT NULL COMMENT '一级审批人' AFTER `SORT_NUMBER`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'CONFIRM_REMARK') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `CONFIRM_REMARK` VARCHAR(255) NULL DEFAULT NULL COMMENT '二级审批说明' AFTER `IMPLEMENTATION_RESULT`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_REMARK') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_REMARK` VARCHAR(255) NULL DEFAULT NULL COMMENT '一级审批说明' AFTER `APPROVAL_URL`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_URL') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_URL` VARCHAR(500) NULL DEFAULT NULL COMMENT '审批链接' AFTER `APPROVAL_BY`; END IF; -- 检查并删除列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'APPROVAL_ID') THEN ALTER TABLE `t_tree_product_approval_detail` DROP COLUMN `APPROVAL_ID`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'APPROVAL_BY') THEN ALTER TABLE `t_tree_product_approval_detail` DROP COLUMN `APPROVAL_BY`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'PARENT_NODE_ID') THEN ALTER TABLE `t_tree_product_approval_detail` DROP COLUMN `PARENT_NODE_ID`; END IF; -- 检查并修改列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'ASSETS_TYPE') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `ASSETS_TYPE` TINYINT(1) NULL DEFAULT null COMMENT '资产类型(1产品 2子产品 3模块 4应用服务 5软件单元)' AFTER `NODE_ID`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'CONTENT') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `CONTENT` TEXT NULL DEFAULT NULL COMMENT '构建的对象' AFTER `LAST_UPDATE_BY`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'CHANGE_TYPE') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `CHANGE_TYPE` TINYINT(1) NULL DEFAULT null COMMENT '审批类型(1新增 2修改 3删除)' AFTER `ASSETS_TYPE`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'CODE') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `CODE` VARCHAR(255) NULL DEFAULT NULL COMMENT '节点code' AFTER `MODIFY`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'MODIFY') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `MODIFY` TEXT NULL DEFAULT NULL COMMENT '解析修改内容' AFTER `ORIGINAL`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_URL') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_URL` VARCHAR(1024) NULL DEFAULT NULL COMMENT '审批链接' AFTER `APPROVAL_BY`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_STATUS') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_STATUS` int(11) NULL DEFAULT NULL COMMENT '节点状态' AFTER `APPROVAL_TYPE`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_BY') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_BY` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审批人' AFTER `SORT_NUMBER`; END IF; -- 检查并添加列 IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_DATE') THEN ALTER TABLE `t_tree_product_approval` ADD COLUMN `APPROVAL_DATE` datetime NULL DEFAULT NULL COMMENT '审批时间' AFTER `APPROVAL_BY`; END IF; -- 检查并修改列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'APPROVAL_REMARK') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `APPROVAL_REMARK` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审批说明' AFTER `APPROVAL_URL`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'CREATE_BY') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `CREATE_BY` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人' AFTER `IMPLEMENTATION_RESULT`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'LAST_UPDATE_BY') THEN ALTER TABLE `t_tree_product_approval` MODIFY COLUMN `LAST_UPDATE_BY` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '最后更新人' AFTER `CREATE_TIME`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'ORIGINAL') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `ORIGINAL` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '原始入参' AFTER `CONTENT`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL_DETAIL' AND UPPER(COLUMN_NAME) = 'MODIFY') THEN ALTER TABLE `t_tree_product_approval_detail` MODIFY COLUMN `CODE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '软件单元CODE' AFTER `MODIFY`; END IF; -- 检查并删除列 IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'CONFIRM_REMARK') THEN ALTER TABLE `t_tree_product_approval` DROP COLUMN `CONFIRM_REMARK`; END IF; IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE UPPER(TABLE_SCHEMA) = 'RBI_TREE' AND UPPER(TABLE_NAME) = 'T_TREE_PRODUCT_APPROVAL' AND UPPER(COLUMN_NAME) = 'WITHDRAWAL_DATE') THEN ALTER TABLE `t_tree_product_approval` DROP COLUMN `WITHDRAWAL_DATE`; END IF; -- 设置表注释 ALTER TABLE `rbi_tree`.`t_tree_product_approval` COMMENT = '资产架构树审批流程表'; END; $$ DELIMITER ; CALL PROC_TABLES_CHANGE(); DROP PROCEDURE IF EXISTS PROC_TABLES_CHANGE; 给我回退的sql脚本
最新发布
11-15
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值