sql与mysql(mariadb)的冲突

我对自己的处境有点困惑，因为我对盖亚环境还不熟悉。
情况是，
我有两个数据库一个是oracle（外部数据库），另一个是mariadb平台的mysql。最终目标是通过springboot应用程序将数据从oracle传输到mariadb。
我现在有个问题，我可以通过gaiaconfig文件连接，
包com.jpmc.pulsar.config；

@Configuration
@Profile({"gaia"})
public class GaiaConfig {

    @Autowired
    private Environment env;

    private static final Logger LOG = LoggerFactory.getLogger(GaiaConfig.class);

    @Autowired
    private DataSource oracleDataSource;

    @Autowired
    private DataSource mysqlDataSource;

    @Bean
    public Cloud cloud() {
        return new CloudFactory().getCloud();
    }

    @Bean(name = "oracleDb")
    public DataSource oracleDataSource(final Cloud cloud) {
        return createDataSource("external-database", cloud);
    }

    @Bean(name = "oracleJdbcTemplate")
    public JdbcTemplate oracleJdbcTemplate() {
        return new JdbcTemplate(oracleDataSource);
    }

    private DataSource createDataSource(final String serviceName, final Cloud cloud) {
        final ExternalDependency externalDependency = cloud
                .getServiceConnector(serviceName, ExternalDependency.class, null);
        if (externalDependency == null) {
            throw new InvalidConfigurationException(
                    String.format("Error getting ServiceConnector for External Dependency with serviceName=[%s]", serviceName)
            );
        }
        HikariDataSource dataSource = null;

        try {
            final HikariConfig config = new HikariConfig();
            config.setDriverClassName(requiredStringProperty("driverClassName", externalDependency, serviceName));
            config.setJdbcUrl(requiredStringProperty("jdbcUrl", externalDependency, serviceName));
            config.setUsername(requiredStringProperty("username", externalDependency, serviceName));
            config.setPassword(requiredStringProperty("password", externalDependency, serviceName));
            config.setConnectionTestQuery(requiredStringProperty("connectionTestQuery", externalDependency, serviceName));
            config.setMinimumIdle(intProperty("minimumIdle", externalDependency, serviceName, 1));
            config.setMaximumPoolSize(intProperty("maximumPoolSize", externalDependency, serviceName, 5));

            dataSource = new HikariDataSource(config);
            String dataSourceDetails = dataSourceDetails(dataSource);
            LOG.info("Available DataSource: [{}]", dataSourceDetails);

        } catch (Exception e) {
            LOG.info("{}", e);
        }
        return dataSource;
    }

    private String dataSourceDetails(final HikariDataSource dataSource) {
        final StringBuilder sb = new StringBuilder();
        sb.append("driverClassName=[").append(dataSource.getDriverClassName()).append("],");
        sb.append("jdbcUrl=[").append(dataSource.getJdbcUrl()).append("],");
        sb.append("username=[").append(dataSource.getUsername()).append("],");
        sb.append("connectionTestQuery=[").append(dataSource.getConnectionTestQuery()).append("],");
        sb.append("validationTimeout=[").append(dataSource.getValidationTimeout()).append("],");
        sb.append("maximumPoolSize=[").append(dataSource.getMaximumPoolSize()).append("],");
        sb.append("minimumIdle=[").append(dataSource.getMinimumIdle()).append("],");
        sb.append("connectionTimeout=[").append(dataSource.getConnectionTimeout()).append("],");
        sb.append("connectionInitSql=[").append(dataSource.getConnectionInitSql()).append("],");
        sb.append("maxLifetime=[").append(dataSource.getMaxLifetime()).append("]");
        return sb.toString();
    }

    private Boolean booleanProperty(final String propertyName, final ExternalDependency externalDependency) {
        final String value = externalDependency.getCredential(propertyName);
        if (value != null) {
            return Boolean.parseBoolean(value);
        }
        return Boolean.FALSE;
    }

    private Integer intProperty(final String propertyName, final ExternalDependency externalDependency,
                                String serviceName, final int defaultValue) {
        final String value = externalDependency.getCredential(propertyName);
        if (value != null) {
            try {
                return Integer.valueOf(value);
            } catch (NumberFormatException ex) {
                throw new InvalidConfigurationException(
                        String.format("Property [%s] is not a valid int in External Dependency with serviceId=[%s]",
                                propertyName, serviceName), ex);
            }
        }
        return defaultValue;
    }

    private String requiredStringProperty(final String propertyName, final ExternalDependency externalDependency,
                                          String serviceName) {
        final String value = stringProperty(propertyName, externalDependency);
        if (value == null || value.trim().length() == 0) {
            throw new InvalidConfigurationException(
                    String.format("No property [%s] defined as part of External Dependency with serviceId=[%s]",
                            propertyName, serviceName));
        } else {
            return value;
        }
    }

    private String stringProperty(final String propertyName, final ExternalDependency externalDependency) {
        return externalDependency.getCredential(propertyName);
    }

    //MySQL Bean and jdbcTemplate Working Fine
    @Primary
    @Bean(name = "mysqlDb")
    public DataSource mysqlDataSource(final Cloud cloud) {
        final String serviceId = "bsc-mariadb";

        // https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html
        final Properties mysqlProperties = new Properties();
        mysqlProperties.setProperty("cachePrepStmts", "true");
        mysqlProperties.setProperty("prepStmtCacheSize", "250");
        mysqlProperties.setProperty("prepStmtCacheSqlLimit", "2048");
        mysqlProperties.setProperty("useServerPrepStmts", "true");
        mysqlProperties.setProperty("useLegacyDatetimeCode", "false");
        mysqlProperties.setProperty("serverTimezone", "UTC");
        mysqlProperties.setProperty("connectionCollation", "utf8mb4_unicode_ci");

        // https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
        final Map<String, Object> poolProperties = new HashMap<>();
        poolProperties.put("poolName", serviceId + "-pool");
        poolProperties.put("maximumPoolSize", 10);
        poolProperties.put("maxLifetime", Duration.ofMinutes(5).toMillis());
        poolProperties.put("connectionInitSql", "SET character_set_client = utf8mb4;");
        poolProperties.put("dataSourceProperties", mysqlProperties);

        final DataSourceConfig serviceConfig = new DataSourceConfig(poolProperties);
        final DataSource dataSource = cloud.getServiceConnector(serviceId, DataSource.class, serviceConfig);
        LOG.info("Available DataSource: [{}]", dataSource);
        return dataSource;
    }

    @Bean(name = "mysqlJdbcTemplate")
    public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) {
        return new JdbcTemplate(dsMySQL);
    }

}

我的存储库类是这样的，

@Autowired
    private JdbcTemplate mysqlJdbcTemplate;

    @Autowired
    private JdbcTemplate oracleJdbcTemplate;

    private String tableName = "BSC_INCIDENT_STG";

    private String sql = "INSERT INTO `BSC_INCIDENT_STG`(`INCIDENT_NUMBER`,`APPLICATION_ID`,`CATEGORY`,`OPEN_TIME`,`SEVERITY_CODE`,`ASSIGNMENT`,`STATUS`,`CLOSE_TIME`,`ELAPSED_TIME`,`RESOLUTION_CODE`,`TYPE`,`OPEN_GROUP`,`RESOLVED_GROUP`,`RESOLVED_TIME`,`USER_PRIORITY`,`JP_ACCT_LOB`,`JP_ACCT_APP_RTO`,`JP_IMPACT`,`JP_IMPACT_DURATION_MIN`,`JP_OUTAGE_DURATION_MIN`,`JP_TTR_D1`,`JP_TTR_D2`,`JP_TTR_R1`,`JP_TTR_R2`,`JP_TTR_D2R2`,`LOGICAL_NAME`,`JP_EXTERNAL_ID`,`JP_EXTERNAL_SYSTEM`,`JP_CONFIG_ITEM`,`JP_MASTER_HOST`,`JP_SITE`,`JP_APPSERVICE_NAME`,`JP_APPSERVICE_ID`,`JP_VERUMIDENTIFIER`)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    public boolean truncateAndLoad(final List<Incident> incidentList) {

        mysqlJdbcTemplate.execute("TRUNCATE BSC_INCIDENT_STG");
        mysqlJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, incidentList.get(i).getIncidentNumber());
                ps.setString(2, incidentList.get(i).getApplicationId());
                ps.setString(3, incidentList.get(i).getCategory());
                ps.setString(4, incidentList.get(i).getOpenTime());
                ps.setString(5, incidentList.get(i).getSeverityCode());
                ps.setString(6, incidentList.get(i).getAssignment());
                ps.setString(7, incidentList.get(i).getStatus());
                ps.setString(8, incidentList.get(i).getCloseTime());
                ps.setString(9, incidentList.get(i).getElapsedTime());
                ps.setString(10, incidentList.get(i).getResolutionCode());
                ps.setString(11, incidentList.get(i).getType());
                ps.setString(12, incidentList.get(i).getOpenGroup());
                ps.setString(13, incidentList.get(i).getResolvedGroup());
                ps.setString(14, incidentList.get(i).getResolvedTime());
                ps.setString(15, incidentList.get(i).getUserPriority());
                ps.setString(16, incidentList.get(i).getJpAcctLob());
                ps.setString(17, incidentList.get(i).getJpAcctAppRto());
                ps.setString(18, incidentList.get(i).getJpImpact());
                ps.setString(19, incidentList.get(i).getJpImpactDurationMin());

                ps.setString(20, incidentList.get(i).getJpOutageDurationMin());
                ps.setString(21, incidentList.get(i).getJpTtrD1());
                ps.setString(22, incidentList.get(i).getJpTtrD2());
                ps.setString(23, incidentList.get(i).getJpTtrR1());
                ps.setString(24, incidentList.get(i).getJpTtrR2());
                ps.setString(25, incidentList.get(i).getJpTtrD2R2());  

                ps.setString(26, incidentList.get(i).getLogicalName());
                ps.setString(27, incidentList.get(i).getJpExternalId());
                ps.setString(28, incidentList.get(i).getJpExternalSystem());
                ps.setString(29, incidentList.get(i).getJpConfigItem());
                ps.setString(30, incidentList.get(i).getJpMasterHost());
                ps.setString(31, incidentList.get(i).getJpSite());
                ps.setString(32, incidentList.get(i).getJpAppserviceName());
                ps.setString(33, incidentList.get(i).getJpAppserviceId());
                ps.setString(34, incidentList.get(i).getJpVerumidentifier());
            }

            @Override
            public int getBatchSize() {
                return incidentList.size();
            }
        });
        return true;
    }

    public String getTableName() {
        return tableName;
    }

    public List<Incident> listAll() {
        String cprSql = "SELECT a.NUMBERPRGN as INCIDENT_NUMBER,"
                + "a.JP_ACCT_APP_APPID as APPLICATION_ID,"
                + "a.CATEGORY,"
                + "to_char(a.OPEN_TIME_EST,'DD-MON-YYYY hh24:mi:ss') OPEN_TIME,"
                + "a.SEVERITY_CODE,"
                + "a.ASSIGNMENT,"
                + "a.STATUS,"
                + "to_char(a.CLOSE_TIME_EST,'DD-MON-YYYY hh24:mi:ss') CLOSE_TIME,"
                + "to_char(a.ELAPSED_TIME,'DD-MON-YYYY hh24:mi:ss') ELAPSED_TIME,"
                + "a.RESOLUTION_CODE,"
                + "a.TYPE,"
                + "a.OPEN_GROUP,"
                + "a.RESOLVED_GROUP,"
                + "to_char(a.RESOLVED_TIME,'DD-MON-YYYY hh24:mi:ss') RESOLVED_TIME,"
                + "a.USER_PRIORITY,"
                + "a.JP_ACCT_LOB,"
                + "a.JP_ACCT_APP_RTO,"
                + "a.JP_IMPACT,"
                + "a.jp_impact_duration_min,"
                + "a.JP_OUTAGE_DURATION_MIN,"
                + "a.JP_TTR_D1,"
                + "a.JP_TTR_D2,"
                + "a.JP_TTR_R1,"
                + "a.JP_TTR_R2,"
                + "a.JP_TTR_D2R2,"
                + "a.LOGICAL_NAME,"
                + "b.JP_EXTERNAL_ID,"
                + "b.JP_EXTERNAL_SYSTEM,"
                + "b.JP_CONFIG_ITEM,"
                + "b.JP_MASTER_HOST,"
                + "b.JP_SITE,"
                + "b.JP_APPSERVICE_NAME,"
                + "b.JP_APPSERVICE_ID,"
                + "b.JP_VERUMIDENTIFIER "
                + "FROM SCUSER.PROBSUMMARYM1 a LEFT JOIN SCUSER.DEVICEM1 b ON (DECODE(a.LOGICAL_NAME,b.LOGICAL_NAME,1,0)=1) "
                + "where a.SEVERITY_CODE IN ('P1/S1','P1/S2','P1/S3') "
                + "and a.CPR_BUSINESS_OPERATIONS = 'f' "
                + "and a.ASSIGNMENT like 'X%' "
                + "and (TRUNC(OPEN_TIME_EST) >=TRUNC(SYSDATE-30) OR TRUNC(CLOSE_TIME_EST) >=TRUNC(SYSDATE-30)) and (a.JP_CONFIDENTIAL IS NULL OR a.JP_CONFIDENTIAL= 'f')";

        logger.info(cprSql);

        return oracleJdbcTemplate.query(cprSql, new IncidentMapper());

问题是，当我在本地运行这些文件时，我能够从oracle获取到mysql（mariadb）的数据，但是当我在云（gaia）中部署相同的文件时，我会遇到冲突。冲突在于repository类中的cprsql查询正在连接到gaia环境中的mysql。
下面是我访问url时遇到的问题
2018年10月15日星期一12:21:49 utc出现意外错误（类型=内部服务器错误，状态=500）。语句回调；错误的sql语法[

SELECT  a.NUMBERPRGN as INCIDENT_NUMBER,a.JP_ACCT_APP_APPID as APPLICATION_ID,
        a.CATEGORY,to_char(a.OPEN_TIME_EST,'DD-MON-YYYY hh24:mi:ss') OPEN_TIME,
a.SEVERITY_CODE,a.ASSIGNMENT,a.STATUS,
to_char(a.CLOSE_TIME_EST, 'DD-MON-YYYY hh24:mi:ss') CLOSE_TIME,
to_char(a.ELAPSED_TIME, 'DD-MON-YYYY hh24:mi:ss') ELAPSED_TIME,
a.RESOLUTION_CODE, a.TYPE,a.OPEN_GROUP,a.RESOLVED_GROUP,
to_char(a.RESOLVED_TIME, 'DD-MON-YYYY hh24:mi:ss') RESOLVED_TIME,
a.USER_PRIORITY, a.JP_ACCT_LOB,a.JP_ACCT_APP_RTO,a.JP_IMPACT,
a.jp_impact_duration_min, a.JP_OUTAGE_DURATION_MIN,a.JP_TTR_D1,
a.JP_TTR_D2,a.JP_TTR_R1, a.JP_TTR_R2,a.JP_TTR_D2R2,a.LOGICAL_NAME,
b.JP_EXTERNAL_ID, b.JP_EXTERNAL_SYSTEM,b.JP_CONFIG_ITEM,b.JP_MASTER_HOST,
        b.JP_SITE,b.JP_APPSERVICE_NAME,b.JP_APPSERVICE_ID,b.JP_VERUMIDENTIFIER
    FROM  SCUSER.PROBSUMMARYM1 a
    LEFT JOIN  SCUSER.DEVICEM1 b  ON (DECODE(a.LOGICAL_NAME,b.LOGICAL_NAME,
                        1,0)=1
                          )
    where  a.SEVERITY_CODE IN ('P1/S1','P1/S2','P1/S3')
      and  a.CPR_BUSINESS_OPERATIONS = 'f'
      and  a.ASSIGNMENT like 'X%'
      and  (TRUNC(OPEN_TIME_EST) >=TRUNC(SYSDATE-30)
              OR  TRUNC(CLOSE_TIME_EST) >=TRUNC(SYSDATE-30)
           )
      and  (a.JP_CONFIDENTIAL IS NULL
              OR  a.JP_CONFIDENTIAL= 'f'
           )

]; 嵌套异常为com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception:对本机函数“decode”的调用中的参数计数不正确
有人能帮我一下吗…我从早上就被困在这里了！！。。

sql与mysql(mariadb)的冲突

暂无答案！

相关问题

热门标签

最新问答