mysql存储过程自动生成java代码工具

「爱情、让人受尽委屈。」 2024-04-17 06:27 74阅读 0赞

## 目标样式： ##

public static final String FIELD_PURCHASE_ORDER_HD_ID = "purchaseOrderHdId";
    public static final String FIELD_PURCHASE_ORDER_NUM = "purchaseOrderNum";
    public static final String FIELD_PURCHASE_COMPANY_ID = "purchaseCompanyId";
    public static final String FIELD_PURCHASE_ORG_ID = "purchaseOrgId";
    public static final String FIELD_PURCHASE_AGENT_ID = "purchaseAgentId";
    public static final String FIELD_PURCHASE_ORDER_TYPE = "purchaseOrderType";
    public static final String FIELD_SUPPLIER_COMPANY_ID = "supplierCompanyId";
    public static final String FIELD_SUPPLIER_ADDRESS = "supplierAddress";
    public static final String FIELD_ORDER_RELEASE_DATE = "orderReleaseDate";
    public static final String FIELD_TAX_EXCLUDED_AMOUNT = "taxExcludedAmount";
    public static final String FIELD_ORDER_SUMMARY = "orderSummary";
    public static final String FIELD_ORDER_STATUS = "orderStatus";
    public static final String FIELD_DELIVERY_FEEDBACK_DATE = "deliveryFeedbackDate";
    @Id
    @GeneratedValue
    @ApiModelProperty("表ID")
    private Long purchaseOrderHdId;
    @ApiModelProperty("采购订单编号")
    private String purchaseOrderNum;
    @ApiModelProperty("采购方公司hpfm_company.company_id")
    private Long purchaseCompanyId;
    @ApiModelProperty("采购组织hpfm_purchase_organization.purchase_org_id")
    private Long purchaseOrgId;
    @ApiModelProperty("采购员hpfm_purchase_agent.purchase_agent_id")
    private Long purchaseAgentId;
    @ApiModelProperty("订单类型")
    private String purchaseOrderType;
    @ApiModelProperty("供应方公司hpfm_company.company_id")
    private Long supplierCompanyId;
    @ApiModelProperty("供应商地址")
    private String supplierAddress;
    @ApiModelProperty("发布日期")
    private Date orderReleaseDate;
    @ApiModelProperty("不含税总全额")
    private BigDecimal taxExcludedAmount;
    @ApiModelProperty("订单摘要")
    private String orderSummary;
    @ApiModelProperty("订单状态")
    private String orderStatus;
    @ApiModelProperty("交期反馈日期")
    private Date deliveryFeedbackDate;
    public Long getPurchaseOrderHdId(){ return purchaseOrderHdId;}
    
    public void setPurchaseOrderHdId(Long purchaseOrderHdId){this.purchaseOrderHdId = purchaseOrderHdId;}
    
    public String getPurchaseOrderNum(){ return purchaseOrderNum;}
    
    public void setPurchaseOrderNum(String purchaseOrderNum){this.purchaseOrderNum = purchaseOrderNum;}
    
    public Long getPurchaseCompanyId(){ return purchaseCompanyId;}
    
    public void setPurchaseCompanyId(Long purchaseCompanyId){this.purchaseCompanyId = purchaseCompanyId;}
    
    public Long getPurchaseOrgId(){ return purchaseOrgId;}
    
    public void setPurchaseOrgId(Long purchaseOrgId){this.purchaseOrgId = purchaseOrgId;}
    
    public Long getPurchaseAgentId(){ return purchaseAgentId;}
    
    public void setPurchaseAgentId(Long purchaseAgentId){this.purchaseAgentId = purchaseAgentId;}
    
    public String getPurchaseOrderType(){ return purchaseOrderType;}
    
    public void setPurchaseOrderType(String purchaseOrderType){this.purchaseOrderType = purchaseOrderType;}
    
    public Long getSupplierCompanyId(){ return supplierCompanyId;}
    
    public void setSupplierCompanyId(Long supplierCompanyId){this.supplierCompanyId = supplierCompanyId;}
    
    public String getSupplierAddress(){ return supplierAddress;}
    
    public void setSupplierAddress(String supplierAddress){this.supplierAddress = supplierAddress;}
    
    public Date getOrderReleaseDate(){ return orderReleaseDate;}
    
    public void setOrderReleaseDate(Date orderReleaseDate){this.orderReleaseDate = orderReleaseDate;}
    
    public BigDecimal getTaxExcludedAmount(){ return taxExcludedAmount;}
    
    public void setTaxExcludedAmount(BigDecimal taxExcludedAmount){this.taxExcludedAmount = taxExcludedAmount;}
    
    public String getOrderSummary(){ return orderSummary;}
    
    public void setOrderSummary(String orderSummary){this.orderSummary = orderSummary;}
    
    public String getOrderStatus(){ return orderStatus;}
    
    public void setOrderStatus(String orderStatus){this.orderStatus = orderStatus;}
    
    public Date getDeliveryFeedbackDate(){ return deliveryFeedbackDate;}
    
    public void setDeliveryFeedbackDate(Date deliveryFeedbackDate){this.deliveryFeedbackDate = deliveryFeedbackDate;}

## 下划线转小驼峰方法 ##

CREATE DEFINER=`root`@`localhost` FUNCTION `under_line_to_lower_camel`(`column_name` varchar(4000)) RETURNS varchar(4000) CHARSET utf8
    BEGIN
    	DECLARE pos int(25);
    	DECLARE res VARCHAR(255);
    	DECLARE tmp VARCHAR(255);
    	set pos = LOCATE('_',column_name);
    	-- LOCATE(subcolumn_name,column_name)：查到返回从1开始的下标。
    	WHILE pos > 0
    	DO
    		set column_name = CONCAT(SUBSTR(column_name,1,pos-1),UPPER(SUBSTR(column_name,pos+1,1)),SUBSTR(column_name FROM pos+2));
    		-- SUBcolumn_name(column_name,pos,len) 从pos开始取len个字符，
    		-- SUBcolumn_name(column_name FROM pos) 取pos开始的后面所有字符。
    		set pos = LOCATE('_',column_name);
    	END WHILE;
    	RETURN column_name;
    END

## 小驼峰转大驼峰方法 ##

CREATE DEFINER=`root`@`localhost` FUNCTION `lower_camel_to_upper_camel`(`column_name` varchar(4000)) RETURNS varchar(4000) CHARSET utf8
    BEGIN
    	RETURN CONCAT( UPPER(SUBSTR(column_name,1,1)), SUBSTR(column_name FROM 2) );
    END

## mysql类型转java类型 ##

CREATE DEFINER=`root`@`localhost` FUNCTION `get_java_type`(`mysql_type` varchar(400)) RETURNS varchar(400) CHARSET utf8
    BEGIN
    	declare java_type VARCHAR(400);
    	case mysql_type
    		when 'varchar' then	set java_type = 'String';
    		WHEN 'bigint' then	set java_type = 'Long';
    		WHEN 'int' then	set java_type = 'Long';
    		WHEN 'Tinyint' then	set java_type = 'Integer';
    		WHEN 'datetime' then	set java_type = 'Date';
    		WHEN 'date' then	set java_type = 'Date';
    		WHEN 'char' then	set java_type = 'String';
    		WHEN 'decimal' then	set java_type = 'BigDecimal';
    		ELSE set java_type = 'error';
    		END case;
    	RETURN java_type;
    END

## 生成get/set方法 ##

CREATE DEFINER=`root`@`localhost` FUNCTION `generator_get_set`(`column_name` varchar(4000),java_type VARCHAR(300)) RETURNS varchar(4000) CHARSET utf8
    BEGIN
    	DECLARE set_func VARCHAR(4000);
    	declare get_func VARCHAR(4000);
    	declare column_name_u VARCHAR(4000);
    	set column_name_u = CONCAT( UPPER(SUBSTR(column_name,1,1)), SUBSTR(column_name FROM 2) );
    	SET set_func = concat("public void set",column_name_u,"(",java_type," ",column_name,"){this.",column_name," = ",column_name,";}\n\n");
    	SET get_func = concat("public ",java_type," get",column_name_u,"(){ return ",column_name,";}\n\n");
    	RETURN concat(get_func,set_func);
    END

## 生成java代码： ##

CREATE DEFINER=`root`@`localhost` PROCEDURE `generator`()
    BEGIN
    	-- 'hpfm_purchase_order_hd' , srm_order
    	declare my_column_name varchar(255);
    	declare java_column_name varchar(255);
    	declare my_column_comment varchar(255);
    	declare my_column_key varchar(255);
    	declare my_extra varchar(255);
    	declare mysql_type varchar(255);
    	declare final_var VARCHAR(10000);	-- final常量生成。
    	declare var_declare varchar(10000);			-- 字段生成。
    	declare get_and_set varchar(20000);
    	declare done int default false;
    	-- 从系统中选出该表中所有列的列名，注释，类型,键，增长类型。
    	declare cur cursor for select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE,COLUMN_KEY,EXTRA from information_schema.columns where TABLE_NAME = 'hpfm_purchase_order_hd' and TABLE_SCHEMA = 'srm_order';
    	declare continue HANDLER for not found set done = true;
    	open cur;
    	set var_declare = '';
    	set final_var = '';
    	set get_and_set = '';
    	read_loop:loop
    		fetch cur into my_column_name,my_column_comment,mysql_type,my_column_key,my_extra;
    		if done then
    			leave read_loop;
    		end if;
    		-- 除who，version
    		if my_column_name != 'creation_date' AND my_column_name != 'created_by' AND my_column_name != 'last_updated_by' AND my_column_name != 'last_update_date' AND my_column_name != 'object_version_number' THEN
    			-- 获取对应java变量名
    			set java_column_name = under_line_to_lower_camel(my_column_name);
    			-- 处理主键
    			if my_column_key = 'PRI' and my_extra = 'auto_increment' THEN
    				set var_declare = CONCAT(var_declare,'@Id\n@GeneratedValue\n');
    			end if;
    			-- 拼接ApiModel与变量声明
    			set var_declare = CONCAT(var_declare,'@ApiModelProperty("',my_column_comment,'")\nprivate ',get_java_type(mysql_type)," ",java_column_name,";\n");
    			set final_var = CONCAT(final_var,'public static final String FIELD_',UPPER(my_column_name),' = "',java_column_name,'";\n');
    			-- 获取get/set方法
    			set get_and_set = concat(get_and_set,generator_get_set(java_column_name,get_java_type(mysql_type)));
    			
    		END IF;
    	end loop;
    	-- 输出结果
    	SELECT concat(final_var,var_declare,get_and_set)entity;
    	close cur;
    	
    END