拼接sql模板

 

代码摘抄：

public static String createOracleSQLTemplate(List<DwdVersionDetailFieldVO> dwdVersionDetailTabVOS, String tabEnName) {


        String SQLTemplate = "INSERT  INTO ";
        String finalSQL = SQLTemplate + tabEnName + "(";

        StringBuffer sb = new StringBuffer();

        try {
//            Map<String, String> fieldsEnAndCnMap = dwdVersionDetailTabVOS.stream().collect(Collectors.toMap(DwdVersionDetailFieldVO::getFieldEnName, DwdVersionDetailFieldVO::getFieldCnName,(oldValue,newValue)->newValue));
            Map<String, String> fieldsEnAndCnMap = dwdVersionDetailTabVOS.stream().collect(Collectors.toMap(DwdVersionDetailFieldVO::getFieldEnName, DwdVersionDetailFieldVO::getFieldCnName));

            if (ObjectUtil.isEmpty(fieldsEnAndCnMap)) {
                return null;
            }
            StringBuffer fieldsInsertStr = new StringBuffer();
            StringBuffer fieldsSelectStr = new StringBuffer();
            for (Map.Entry<String, String> entry : fieldsEnAndCnMap.entrySet()) {
                fieldsInsertStr.append(entry.getKey()).append(",");
                fieldsSelectStr.append("NULL as ").append(entry.getKey()).append(",").append(" --").append(entry.getValue()).append("\n");
            }

            sb.append(finalSQL).append(fieldsInsertStr.deleteCharAt(fieldsInsertStr.length() - 1)).append(")").append("\n")
                    .append(" select").append("\n");
            sb.append(fieldsSelectStr.deleteCharAt(fieldsSelectStr.lastIndexOf(","))).append(" from input_your_table_name");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return sb.toString();

    }

　示例：

INSERT OVERWRITE TABLE DICT_EMPLOYEE(NATION_NAME,EMPLOYEE_ID,BIRTH_ADDR_VILLAGE,EMAIL,EMPLOYEE_INTRODUCTION,CERTIFICATE_NO,REGISTER_ADDR,ENTRY_TIME,REGISTER_ADDR_VILLAGE,MARRIAGE_NAME,CURR_ADDR_PROV_NAME,BIRTH_DATE,BIRTH_ADDR_COUNTY_NAME,NATION_CODE,COUNTRY_NAME,PHOTO,VALID_FLAG,CURR_ADDR_COUNTY_NAME,DEPT_CODE,MARRIAGE_CODE,REGISTER_ADDR_CITY_CODE,BIRTH_ADDR_COUNTY_CODE,REGISTER_ADDR_TOWN_CODE,ORG_CODE,ORG_ADMINISTRATIVE_LEVEL_NAME,CURR_ADDR_HOUSE,CURR_ADDR_COUNTY_CODE,ID_TYPE_NAME,ORI_DEPT_CODE,COUNTRY_CODE,ID_NO,TEL_NO,REGISTER_ADDR_COUNTY_NAME,DEPT_NAME,CURR_ADDR_VILLAGE,ELECTRONIC_SIGNATURE,BIRTH_ADDR_CITY_NAME,TITLE_NAME,BIRTH_ADDR,BIRTH_ADDR_PROV_NAME,ROLE_CODE,SEX_CODE,CURR_ADDR_TOWN_CODE,REGISTER_ADDR_PROV_CODE,ORI_DEPT_NAME,GROUP_CODE,BIRTH_ADDR_TOWN_NAME,ID_TYPE_CODE,BIRTH_ADDR_TOWN_CODE,ORG_ADMINISTRATIVE_LEVEL_ID,ACTIVE_FLAG,TITLE_CODE,REGISTER_ADDR_TOWN_NAME,REGISTER_ADDR_PROV_NAME,ORG_NAME,BIRTH_ADDR_PROV_CODE,CERTIFICATE_NAME,LAST_MODIFY_TIME,CURR_ADDR_CITY_CODE,ROLE_NAME,EMPLOYEE_NAME,DIMISSION_TIME,CURR_ADDR,REGISTER_ADDR_CITY_NAME,GROUP_NAME,CURR_ADDR_CITY_NAME,CURR_ADDR_PROV_CODE,NATIVE_ADDR,BIRTH_ADDR_HOUSE,REGISTER_ADDR_COUNTY_CODE,CURR_ADDR_TOWN_NAME,REGISTER_ADDR_HOUSE,SEX_NAME,BIRTH_ADDR_CITY_CODE)
 select
NULL as NATION_NAME, --民族名称
NULL as EMPLOYEE_ID, --员工编号
NULL as BIRTH_ADDR_VILLAGE, --出生地址-村(街、路、弄等
NULL as EMAIL, --电子邮件地址
NULL as EMPLOYEE_INTRODUCTION, --职工简介
NULL as CERTIFICATE_NO, --个人最高级别职业证书编码
NULL as REGISTER_ADDR, --户籍地址
NULL as ENTRY_TIME, --入职时间
NULL as REGISTER_ADDR_VILLAGE, --户籍地址-村(街、路、弄等
NULL as MARRIAGE_NAME, --婚姻状况名称
NULL as CURR_ADDR_PROV_NAME, --现住地址-省(自治区、直辖市名称
NULL as BIRTH_DATE, --出生日期
NULL as BIRTH_ADDR_COUNTY_NAME, --出生地址-县(区名称
NULL as NATION_CODE, --民族代码
NULL as COUNTRY_NAME, --国籍名称
NULL as PHOTO, --照片
NULL as VALID_FLAG, --有效标志
NULL as CURR_ADDR_COUNTY_NAME, --现住地址-县(区名称
NULL as DEPT_CODE, --科室代码
NULL as MARRIAGE_CODE, --婚姻状况代码
NULL as REGISTER_ADDR_CITY_CODE, --户籍地址-市(地区、州代码
NULL as BIRTH_ADDR_COUNTY_CODE, --出生地址-县(区代码
NULL as REGISTER_ADDR_TOWN_CODE, --户籍地址-乡(镇、街道办事处代码
NULL as ORG_CODE, --医疗机构代码
NULL as ORG_ADMINISTRATIVE_LEVEL_NAME, --机构内行政级别名称
NULL as CURR_ADDR_HOUSE, --现住地址-门牌号码
NULL as CURR_ADDR_COUNTY_CODE, --现住地址-县(区代码
NULL as ID_TYPE_NAME, --身份证件类别名称
NULL as ORI_DEPT_CODE, --机构内科室代码
NULL as COUNTRY_CODE, --国籍代码
NULL as ID_NO, --身份证件号码
NULL as TEL_NO, --电话号码
NULL as REGISTER_ADDR_COUNTY_NAME, --户籍地址-县(区名称
NULL as DEPT_NAME, --科室名称
NULL as CURR_ADDR_VILLAGE, --现住地址-村(街、路、弄等
NULL as ELECTRONIC_SIGNATURE, --电子签名
NULL as BIRTH_ADDR_CITY_NAME, --出生地址-市(地区、州名称
NULL as TITLE_NAME, --职称名称
NULL as BIRTH_ADDR, --出生地址
NULL as BIRTH_ADDR_PROV_NAME, --出生地址-省(自治区、直辖市名称
NULL as ROLE_CODE, --角色代码
NULL as SEX_CODE, --性别代码
NULL as CURR_ADDR_TOWN_CODE, --现住地址-乡(镇、街道办事处代码
NULL as REGISTER_ADDR_PROV_CODE, --户籍地址-省(自治区、直辖市代码
NULL as ORI_DEPT_NAME, --机构内科室名称
NULL as GROUP_CODE, --诊疗组/护理组代码
NULL as BIRTH_ADDR_TOWN_NAME, --出生地址-乡(镇、街道办事处名称
NULL as ID_TYPE_CODE, --身份证件类别代码
NULL as BIRTH_ADDR_TOWN_CODE, --出生地址-乡(镇、街道办事处代码
NULL as ORG_ADMINISTRATIVE_LEVEL_ID, --机构内行政级别编号
NULL as ACTIVE_FLAG, --在编标志
NULL as TITLE_CODE, --职称编号
NULL as REGISTER_ADDR_TOWN_NAME, --户籍地址-乡(镇、街道办事处名称
NULL as REGISTER_ADDR_PROV_NAME, --户籍地址-省(自治区、直辖市名称
NULL as ORG_NAME, --医疗机构名称
NULL as BIRTH_ADDR_PROV_CODE, --出生地址-省(自治区、直辖市代码
NULL as CERTIFICATE_NAME, --个人最高级别职业证书名称
NULL as LAST_MODIFY_TIME, --最后修改时间
NULL as CURR_ADDR_CITY_CODE, --现住地址-市(地区、州代码
NULL as ROLE_NAME, --角色名称
NULL as EMPLOYEE_NAME, --员工姓名
NULL as DIMISSION_TIME, --离职时间
NULL as CURR_ADDR, --现住地址
NULL as REGISTER_ADDR_CITY_NAME, --户籍地址-市(地区、州名称
NULL as GROUP_NAME, --诊疗组/护理组名称
NULL as CURR_ADDR_CITY_NAME, --现住地址-市(地区、州名称
NULL as CURR_ADDR_PROV_CODE, --现住地址-省(自治区、直辖市代码
NULL as NATIVE_ADDR, --籍贯
NULL as BIRTH_ADDR_HOUSE, --出生地址-门牌号码
NULL as REGISTER_ADDR_COUNTY_CODE, --户籍地址-县(区代码
NULL as CURR_ADDR_TOWN_NAME, --现住地址-乡(镇、街道办事处名称
NULL as REGISTER_ADDR_HOUSE, --户籍地址-门牌号码
NULL as SEX_NAME, --性别名称
NULL as BIRTH_ADDR_CITY_CODE --出生地址-市(地区、州代码
 from input_your_table_name