TRS.WCM.Developer/test/com/trs/web2frame/SqlAutoCreateTest.java

package com.trs.web2frame;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.trs.infra.util.CMyException;
import com.trs.infra.util.CMyFile;
import com.trs.infra.util.CMyString;
import com.trs.infra.util.Loader;
import junit.framework.TestCase;

import java.util.Map;

/**
 * create by li.jihong
 * 2019/7/3
 */
public class SqlAutoCreateTest extends TestCase {

    /**
     * 合并视图数据
     * @throws CMyException
     */
    public void testCreateSql() throws CMyException {
        //TODO 需要项目组根据自己需要合并的表和视图填写
        //目标视图ID
        int toViewId = 12;
        //目标视图名称 **注意如果是元数据表 ID需要必填，所以新建号统一视图表后，可以通过Navicat修改表字段ID为自增。统一视图后修改回来，重启项目
        String toTabelName = "WCMMetaTableGovOpenDataGovOpenData";
        //来源表名称
        String srcTabelName = "wcmdocument";
        //需要合并到空视图的栏目IDS
        String channelIds = "1,2";
        //来源表数据过滤条件
        String where = " DOCCHANNEL in (" + channelIds + ") ";
        //TODO


        //生成批量插入sql
        String insertSql = buildInsertSql(srcTabelName, toTabelName, where);
        System.out.println("-- insert SQL");
        System.out.println(insertSql);
        //生成chnldoc SQL
        String updateChnldocSql = buildUpdateChnldocSql(toViewId, channelIds);
        System.out.println("-- update chnldoc SQL");
        System.out.println(updateChnldocSql);

        //生成wcmdocument sql
        String updateDocumentSql = buildUpdateDocumentSql(toViewId, channelIds);
        System.out.println("-- update wcmdocument SQL");
        System.out.println(updateDocumentSql.toString());

        //生成xwcmmetaviewemployer sql
        String deleteViewEmployerSql = buildDeleteViewEmployerSql(channelIds);
        System.out.println("-- delete xwcmmetaviewemployer SQL");
        System.out.println(deleteViewEmployerSql.toString());

        System.out.println("-- insert xwcmmetaviewemployer SQL");
        buildUpdateViewEmployerSql(channelIds, toViewId);

    }

    private void buildUpdateViewEmployerSql(String channelIds, int toViewId) {
        String[] channelIdArr = CMyString.split(channelIds, ",");
        for (String channelIdStr : channelIdArr){
            StringBuilder updateViewEmployerSql = new StringBuilder();
            updateViewEmployerSql.append("insert into xwcmmetaviewemployer" +
                    " (" +
                    " VIEWID," +
                    " CHANNELID," +
                    " METAVIEWEMPLOYERID," +
                    " EmployerType," +
                    " EmployerId" +
                    " )" +
                    " SELECT ")
                    .append(toViewId).append(",")
                    .append(channelIdStr).append(",")
                    .append(" ifnull(max(METAVIEWEMPLOYERID), 0) + 1").append(",")
                    .append(101).append(",")
                    .append(channelIdStr)
                    .append(" FROM xwcmmetaviewemployer as METAVIEWEMPLOYERID; ");
            System.out.println(updateViewEmployerSql);
        }
    }

    private String buildDeleteViewEmployerSql(String channelIds) {

        StringBuilder deleteViewEmployerSql = new StringBuilder();
        deleteViewEmployerSql.append("DELETE FROM xwcmmetaviewemployer WHERE EmployerType = 101 and EmployerId in (")
                .append(channelIds)
                .append(") ;");
        return deleteViewEmployerSql.toString();
    }

    private String buildUpdateDocumentSql(int toViewId, String channelIds) {

        StringBuilder updateDocumentSql = new StringBuilder();
        updateDocumentSql.append("UPDATE wcmdocument SET DOCKIND = ")
                .append(toViewId)
                .append(" WHERE DOCCHANNEL in (")
                .append(channelIds)
                .append(") ;");

        return updateDocumentSql.toString();
    }

    private String buildUpdateChnldocSql(int toViewId, String channelIds) {

        StringBuilder updateChnldocSql = new StringBuilder();
        updateChnldocSql.append("UPDATE wcmchnldoc SET DOCKIND = ")
                .append(toViewId)
                .append(" WHERE CHNLID in (")
                .append(channelIds)
                .append(") ;");

        return updateChnldocSql.toString();
    }

    private String buildInsertSql(String srcTabelName, String toTabelName, String where) throws CMyException {

        String filePath = Loader.getResource("template").getPath() + "/relation.json";
        String jsonTxt = CMyFile.readFile(filePath, "utf-8");
        JSONObject jsonObject = JSON.parseObject(jsonTxt);
        StringBuilder keyBuilder = new StringBuilder();
        StringBuilder valueBuilder = new StringBuilder();
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            keyBuilder.append(entry.getKey()).append(",");
            valueBuilder.append(entry.getValue()).append(",");
        }
        if (!jsonObject.isEmpty()){
            keyBuilder.deleteCharAt(keyBuilder.length() - 1);
            valueBuilder.deleteCharAt(valueBuilder.length() - 1);
        }
        StringBuilder insertSql = new StringBuilder(" INSERT INTO ");
        insertSql.append(toTabelName)
                .append(" (")
                .append(toTabelName+"Id,")
                .append(keyBuilder)
                .append(") SELECT ")
                .append(" (@max_num := @max_num + 1),")
                .append(valueBuilder)
                .append(" FROM ")
                .append(srcTabelName);
        if (!CMyString.isEmpty(where)){
            insertSql.append(" WHERE ").append(where);
        }
        insertSql.append(";");
        return insertSql.toString();
    }

}