设计模式之工厂方法模式

设计模式之工厂方法模式

功能需求：

实现不同数据库类型之间的数据交换；  
可界面化配置源数据库与目标数据库；  
可界面化配置源字段与目标字段对应结构，数据类型识别；  
可界面化配置实现针对某个字段的过滤；    
可界面化配置选择任务调度运行方式；    

针对上述需求，选择了站在巨人的肩膀上（DataX）,一个阿里开发的开源项目。

当然上面说的都是废话，这里我们主要讲的是参数化工厂模式，

具体点就是在实现上述功能的过程中在用户配置的数据源与一些过滤

条件的情况下先去判断数据源是否有数据，如果有数据再根据用户配

置拼接查询的SQL语句，也就是DATAX的查询语句参数。

参数化工厂方法：

我们的最终目的是根据用户创建的不同类型的数据库类型    
使用同一个接口调用指定的不同数据库类型的方法，  
扩展数据库类型时不需要更改调用代码，只需实现    
接口，继承操作类重写工厂方法即可

类图：

结构与说明

• DbSql：定义工厂方法所创建对象的接口，也就是实际需要使用的对象的接口。
• MysqlSelectSql/OracleSelectSql：具体的DbSql接口的实现对象
• DbSqlOperate：操作DbSql的对象，创建器，声明参数化工厂方法
  

/**
 * @Description: 数据库sql顶层接口
 * @Author: 
 * @Version: 
 */
public interface DbSql {

    /**
     * 获取查询SQL语句
     * @return
     */
    String getSelctSql();

    /**
     * 获取查询数量SQL语句
     * @return
     */
    String getCountSql();
}

/**
 * @Description: 实现操作DbSql的对象
 * @Author:
 * @Version:
 */
public class DbSqlOperate {

    /**
     * 获取查询SQL语句
     * @return
     */
    public String getSelctSql(DsEtlTemplate dsEtlTemplate, String isIncrement, String dbType, Long recordsNum) {
        DbSql dbSql = factoryMethod(dsEtlTemplate, isIncrement, dbType, recordsNum);
        return dbSql.getSelctSql();
    }

    /**
     * 获取查询数量SQL语句
     * @return
     */
    public String getCountSql(DsEtlTemplate dsEtlTemplate, String isIncrement, String dbType) {
        DbSql dbSql = factoryMethod(dsEtlTemplate, isIncrement, dbType, null);
        return dbSql.getCountSql();
    }

    /**
     * 工厂方法，创建不同数据库获取sql的接口对象
     * @param dsEtlTemplate 业务对象
     * @param isIncrement   是否增量
     * @param dbType    数据库类型
     * @return
     */
    protected DbSql factoryMethod(DsEtlTemplate dsEtlTemplate, String isIncrement, String dbType, Long recordsNum) {
        DbSql dbSql = null;
        if ("MYSQL".equalsIgnoreCase(dbType)) {
            dbSql = new MysqlSelectSql(dsEtlTemplate, isIncrement);
        } else if ("ORACLE".equalsIgnoreCase(dbType)) {
            dbSql = new OracleSelectSql(dsEtlTemplate, isIncrement, recordsNum);
        }
        return dbSql;
    }
}

/**
 * @Description: DbSql基类，公共属性, 方法
 * @Author: 
 * @Version: 
 */
public class BaseDbSql {

    protected DsEtlTemplate dsEtlTemplate;

    protected String isIncrement;

    protected StringBuilder sql = new StringBuilder();

    protected BaseDbSql(DsEtlTemplate dsEtlTemplate, String isIncrement) {
        this.dsEtlTemplate = dsEtlTemplate;
        this.isIncrement = isIncrement;
    }

    protected String getTableName(DsEtlTemplate dsEtlTemplate) {
        return dsEtlTemplate.getSrcTbname();
    }

    protected String getFilterFields(){
        String condition = "";
        List<DsEtlFieldMapping> dsEtlFieldMapping = dsEtlTemplate.getDsEtlFieldMapping();
        for (int i = 0; i < dsEtlFieldMapping.size(); i++) {
            if (DSConstants.INCREMENT_ON.equals(dsEtlFieldMapping.get(i).getIsFilter())
                    && StringUtils.isNotBlank(dsEtlFieldMapping.get(i).getFilterType())
                    && StringUtils.isNotBlank(dsEtlFieldMapping.get(i).getFilterContent())) {

                if ("LIKE".equalsIgnoreCase(dsEtlFieldMapping.get(i).getFilterType())) {
                    condition += (" AND " + dsEtlFieldMapping.get(i).getSrcField()
                            + " " + dsEtlFieldMapping.get(i).getFilterType() + " '%"
                            + dsEtlFieldMapping.get(i).getFilterContent() + "%'");
                } else {
                    condition += (" AND " + dsEtlFieldMapping.get(i).getSrcField()
                            + " " + SysboFilter.getSymbol(dsEtlFieldMapping.get(i).getFilterType())
                            + " " + dsEtlFieldMapping.get(i).getFilterContent());
                }
            }
        }
        return condition;
    }

}

/**
 * @Description:    Mysql构造SQL对象
 *  example：
 *      [SELECT COUNT(*) FROM (SELECT @rownum:=@rownum+1 rownum, ASSET.* FROM (SELECT @rownum:=0) r, ASSET) T
 *      WHERE rownum BETWEEN 1 AND 10000]
 *
 *      [SELECT
 *      ASSETREGASSETTYPE,EN_CODE,UPDATEPERSON,ASSETREGASSETMGRDATE,ASSETREGCHECKFLAG,ASSETREGASSETNO,
 *      ASSETREGPK,EN_NAME,ASSETREGREMARK,PARENT_ACNAME,ASSETREGINTRINSICVALUE,ASSETREGNETVALUE,
 *      ACNAME,PARENT_ACCODE,ASSETREGLISTDATE,ASSETREGASSETNAME,LASTESTUPDATE,ASSETREGVOUCHERNO,ASSETREGBILLNO,
 *      ASSETREGACCOUNTDATE,ASSETREGENPRCODE
 *      FROM (SELECT @rownum:=@rownum+1 rownum, ASSET.*
 *      FROM (SELECT @rownum:=0) r, ASSET) T
 *      WHERE rownum BETWEEN 1 AND 10000 AND EN_CODE = 150002 AND EN_NAME like '%广东美术馆%']
 *
 * @Author: 
 * @Version: 
 */
public class MysqlSelectSql extends BaseDbSql implements DbSql {

    public MysqlSelectSql(DsEtlTemplate dsEtlTemplate, String isIncrement) {
        super(dsEtlTemplate, isIncrement);
    }

    private MysqlSelectSql select() {
        sql.append("SELECT");
        return this;
    }

    private MysqlSelectSql columns() {
        List<DsEtlFieldMapping> dsEtlField = dsEtlTemplate.getDsEtlFieldMapping();
        sql.append(" ");
        for (int i = 0; i < dsEtlField.size(); i++) {
            if (dsEtlField.size() - 1 == i) {
                sql.append(dsEtlField.get(i).getSrcField());
            } else {
                sql.append(dsEtlField.get(i).getSrcField()).append(DSConstants.SYMBOL_COMMA);
            }
        }
        return this;
    }

    private MysqlSelectSql count() {
        sql.append(" COUNT(*)");
        return this;
    }

    private MysqlSelectSql from(){
        sql.append(" FROM");
        return this;
    }

    private MysqlSelectSql fromPrefix() {
        sql.append(" FROM (SELECT @rownum:=@rownum+1 rownum,");
        return this;
    }

    private MysqlSelectSql table() {
        sql.append(" " + getTableName(dsEtlTemplate));
        return this;
    }

    private MysqlSelectSql fromSuffix() {
        sql.append(".* FROM (SELECT @rownum:=0) r,");
        return this;
    }

    private MysqlSelectSql alias() {
        sql.append(") T");
        return this;
    }

    private MysqlSelectSql where() {
        sql.append(" WHERE");
        return this;
    }

    private MysqlSelectSql condition() {
        String condition = "";
        if (DSConstants.ROW_NUM_FILETER_ON.equals(dsEtlTemplate.getRowNumFilter())) {
            condition = " rownum BETWEEN " + dsEtlTemplate.getStartNum() + " AND " + dsEtlTemplate.getEndNum();
        } else if (DSConstants.INCREMENT_ON.equals(isIncrement)) {
            condition = " rownum " + DSConstants.SYMBOK_GTE + dsEtlTemplate.getRowNum();
        } else {
            condition = " rownum " + DSConstants.SYMBOK_GTE + 1;
        }
        sql.append(condition).append(getFilterFields());
        return this;
    }

    @Override
    public String getSelctSql() {
        this.select()
                .columns()
                .fromPrefix()
                .table()
                .fromSuffix()
                .table()
                .alias()
                .where()
                .condition();

        return sql.toString();
    }

    @Override
    public String getCountSql() {
        this.select()
                .count()
                .fromPrefix()
                .table()
                .fromSuffix()
                .table()
                .alias()
                .where()
                .condition();

        return sql.toString();
    }
}

/**
 * @Description: Oracle构造SQL对象
 * @Author: 
 * @Version: 
 */
public class OracleSelectSql extends BaseDbSql implements DbSql {

    private Long recordsNum;

    public OracleSelectSql(DsEtlTemplate dsEtlTemplate, String isIncrement, Long recordsNum) {
        super(dsEtlTemplate, isIncrement);
        this.recordsNum = recordsNum;
    }

    private OracleSelectSql select() {
        sql.append("SELECT");
        return this;
    }

    private OracleSelectSql columns() {
        List<DsEtlFieldMapping> dsEtlField = dsEtlTemplate.getDsEtlFieldMapping();
        sql.append(" ");
        for (int i = 0; i < dsEtlField.size(); i++) {
            if (dsEtlField.size() - 1 == i) {
                sql.append(dsEtlField.get(i).getSrcField());
            } else {
                sql.append(dsEtlField.get(i).getSrcField()).append(DSConstants.SYMBOL_COMMA);
            }
        }
        return this;
    }

    private OracleSelectSql count() {
        sql.append(" COUNT(*)");
        return this;
    }

    private OracleSelectSql from() {
        sql.append(" FROM");
        return this;
    }

    private OracleSelectSql selectRowNum() {
        sql.append(" (SELECT ROWNUM RN,");
        return this;
    }

    private OracleSelectSql table() {
        sql.append(" " + getTableName(dsEtlTemplate));
        return this;
    }

    private OracleSelectSql allColumns() {
        sql.append(".*");
        return this;
    }

    private OracleSelectSql rowNumGTE() {
        if (DSConstants.ROW_NUM_FILETER_ON.equals(dsEtlTemplate.getRowNumFilter())) {
            sql.append(" WHERE ROWNUM <=" + dsEtlTemplate.getEndNum() + ")");
        } else if (DSConstants.INCREMENT_ON.equals(isIncrement)) {
            sql.append(" )");
        }else {
            sql.append(" WHERE ROWNUM <=" + recordsNum + ")");
        }
        return this;
    }

    private OracleSelectSql where() {
        sql.append(" WHERE");
        return this;
    }

    private OracleSelectSql condition() {
        String condition = "";
        if (DSConstants.ROW_NUM_FILETER_ON.equals(dsEtlTemplate.getRowNumFilter())) {
            condition = " RN >=" + dsEtlTemplate.getStartNum();
        } else if (DSConstants.INCREMENT_ON.equals(isIncrement)) {
            condition = " RN >=" + dsEtlTemplate.getRowNum();
        } else {
            condition = " RN >=" + 1;
        }
        sql.append(condition).append(getFilterFields());
        return this;
    }

    @Override
    public String getSelctSql() {
        this.select()
                .columns()
                .from()
                .selectRowNum()
                .table()
                .allColumns()
                .from()
                .table()
                .rowNumGTE()
                .where()
                .condition();

        return sql.toString();
    }

    @Override
    public String getCountSql() {
        if (!DSConstants.ROW_NUM_FILETER_ON.equals(dsEtlTemplate.getRowNumFilter())
                && !DSConstants.INCREMENT_ON.equals(isIncrement)) {
            this.select()
                    .count()
                    .from()
                    .table();
            // TODO 验证是否可以加上condition
        } else {
            this.select()
                    .count()
                    .from()
                    .selectRowNum()
                    .table()
                    .allColumns()
                    .from()
                    .table()
                    .rowNumGTE()
                    .where()
                    .condition();
        }

        return sql.toString();
    }
}