Spring Boot 2.6.2添加iSeries数据源至JdbcTemplate遇配置问题

现有部署于OpenLiberty 23.0.0.10（Java 8环境）的Spring Boot 2.6.2微服务，需新增iSeries数据源调用。已完成以下操作：

1. 创建iSeries数据库DAO：

public class iSeriesDatabaseDao {

       @Autowired
       private JdbcTemplate jdbcTemplate;

       public String getPhoneNumber(String number)
                                    throws SSOServiceException {

                try {
                       String sqlString = String.join(" ",
                            "SELECT phone AS SPECIALITY, (wrkph1 CONCAT wrkph2 CONCAT wrkph3) AS PHONE FROM PRVMAS where PROVNO = '%s'");
                       String getPhone = String.format(sqlString,number);
                       return String.valueOf(jdbcTemplate.queryForList(getPhone));

                   } catch (Exception e) {
                         throw new SSOServiceException("Error in the query the configuration table: " + e.getMessage());

                  }
              }

2. 更新server.xml配置新JDBC驱动及数据源：

<jdbcDriver id="DB2iSeries">
    <library name="DB2iToolboxLib">
        <fileset dir="${wlp.user.dir}/shared/resources" includes="jt400.jar" />
    </library>
</jdbcDriver>

<dataSource jdbcDriverRef="DB2iSeries" jndiName="jdbc/iSeriesDataSource">
    <properties serverName="${env.DB_ISERIES_URL}" password="${env.DB_ISERIES_PWD}" user="${env.DB_USERID}" />
</dataSource>

3. 更新application.properties：

spring.datasource.jndi-name=jdbc/PostgresSQLDataSource
spring.datasource.jndi-name-iSeries=jdbc/iSeriesDataSource

测试时出现PostgreSQL连接的SSL错误（而非iSeries连接）：

[ERROR   ] Connection error:
SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
[ERROR   ] Failed to create a ConnectionPoolDataSource from PostgreSQL JDBC Driver 42.1.4 for user_xxx at jdbc:postgresql://Dev.xxx.com:3306/xxx?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&ssl=true&tcpKeepAlive=false&loginTimeout=0&connectTimeout=10&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
              at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:67)
              at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:359)
              at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:148)
              at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
              at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
              at org.postgresql.Driver.makeConnection(Driver.java:450)

需完成新iSeries连接的搭建，同时处理当前PostgreSQL的SSL错误。

一、修复PostgreSQL SSL错误（优先处理，避免服务启动失败） #

当前错误是PostgreSQL服务器证书未被Java信任库认可，与iSeries配置无关，但会阻塞服务启动：

• 将PostgreSQL服务器的根证书导入到OpenLiberty使用的Java 8信任库（默认路径为${JAVA_HOME}/jre/lib/security/cacerts），执行命令：

  keytool -importcert -file postgres_root_cert.crt -alias postgres-dev -keystore ${JAVA_HOME}/jre/lib/security/cacerts -storepass changeit
  

• 测试环境可临时在PostgreSQL的JDBC URL中添加sslmode=require（跳过证书验证），但生产环境禁止此操作。

二、配置Spring Boot多数据源 #

Spring Boot默认仅识别spring.datasource.*作为主数据源，需调整配置实现多数据源区分：

1. 修正application.properties配置

# 主数据源（PostgreSQL）
spring.datasource.jndi-name=jdbc/PostgresSQLDataSource

# iSeries数据源
spring.datasource.iseries.jndi-name=jdbc/iSeriesDataSource

2. 新增iSeries数据源配置类
   创建配置类注入独立的iSeries数据源和JdbcTemplate：

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class IseriesDataSourceConfig {

    @Bean(name = "iseriesDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.iseries")
    public DataSource iseriesDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "iseriesJdbcTemplate")
    public JdbcTemplate iseriesJdbcTemplate(@Qualifier("iseriesDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

3. 修改DAO注入指定JdbcTemplate
   将原有DAO的JdbcTemplate替换为iSeries专属实例，同时优化SQL避免注入风险：

public class iSeriesDatabaseDao {

       @Autowired
       @Qualifier("iseriesJdbcTemplate")
       private JdbcTemplate jdbcTemplate;

       public String getPhoneNumber(String number) throws SSOServiceException {
                try {
                       String sqlString = "SELECT phone AS SPECIALITY, (wrkph1 CONCAT wrkph2 CONCAT wrkph3) AS PHONE FROM PRVMAS where PROVNO = ?";
                       return String.valueOf(jdbcTemplate.queryForList(sqlString, number));

                   } catch (Exception e) {
                         throw new SSOServiceException("Error querying configuration table: " + e.getMessage());
                  }
              }
}

三、优化OpenLiberty的iSeries数据源配置 #

补充iSeries特有的配置项，确保连接稳定：

<dataSource jdbcDriverRef="DB2iSeries" jndiName="jdbc/iSeriesDataSource">
    <properties 
        serverName="${env.DB_ISERIES_URL}" 
        password="${env.DB_ISERIES_PWD}" 
        user="${env.DB_USERID}"
        databaseName="YOUR_TARGET_LIBRARY"
        naming="system"
        />
</dataSource>

• 确认jt400.jar版本适配Java 8和OpenLiberty 23.0.0.10，路径配置正确。

内容的提问来源于stack exchange，提问作者Tom Henricksen