关于JdbcTemplate动态切换数据库

背景：项目中有一个需求是要根据DB的配置重新编译存储过程，但是要执行多个数据库的存储过程。因为数据库很多（大概10个左右），不想使用切换数据源的方式去实现。故有此文章来记录遇到的问题和解决过程。

技术：springboot，springdata ，sqlserver

流程：在DB中有一个配置类Config，其中两列保存执行的数据库名字（数据库有多个A，B，C，D …）和执行的存储过程名字两个值。java中通过查询config表来得到需要编译的存储过程，执行编译。是不是很简单的一个需求呢。（我当时也是这么想的）

问题：不管怎么设置，都不会切换DB名字，当执行到不同数据库的存储过程时就会报错：没有找到这个存储过程（如果执行不同的数据库里面的存储过程，需要修改数据源中的catalog属性，达到切换数据库的效果）。下面看代码

/**数据源的配置
本来只有一个datasource，但是这里有个问题，重新编译存储过程需要dbo帐号，其它的帐号是没有权限re-compile的，
报错信息：org.springframework.dao.TransientDataAccessResourceException: StatementCallback; SQL [EXEC sp_recompile 
N'aaa_sp']; The user does not have permission to perform this action.; nested exception is java.sql.SQLException: 
The user does not have permission to perform this action.所以新加了一个dbo帐号的数据源，接下来的程序是用到的都是这个数据源。
**/

public class DBConfig {

    @Autowired
    private Environment env;

    public DBConfig() {
    }
    
    @Primary
    @Bean(destroyMethod = "close", name = "defaultDataSource")
    public DataSource dataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbcDriverClassName"));
        String hostEnv = EnvironmentReader.getEnv();
        dataSource.setUrl(env.getProperty(hostEnv + "jdbcUrl"));
        dataSource.setUsername(env.getProperty(hostEnv + "dbuser"));
        dataSource.setPassword(env.getProperty(hostEnv + "dbpassword"));
        dataSource.setDefaultCatalog("A");
        return dataSource;
     }
     
	@Bean(destroyMethod = "close", name = "secondDataSource")
    public DataSource dataSourceSecond() {
    	BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbcDriverClassName"));
        String hostEnv = EnvironmentReader.getEnv();
        dataSource.setUrl(env.getProperty(hostEnv + "jdbcUrl"));
        dataSource.setUsername(env.getProperty(hostEnv + "dbodbuser"));
        dataSource.setPassword(env.getProperty(hostEnv + "dbodbpassword"));
        dataSource.setDefaultCatalog("A");
        return dataSource;
    }
 }

@Service
public class CompileProcedureServiceImpl {

    private static final Logger logger = LoggerFactory.getLogger(CompileProcedureServiceImpl.class);

    @Autowired
    private CodeDetailHeaderRepository codeDetailHeaderRepository;

    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier(value = "secondDataSource")
    private DataSource secondDataSource;

    /**
     * @Description compileSPByDB
     *
     * @return void
     **/
    public void compileSPByDB() {
        //从DB的config表中读取需要执行的存储过程
        jdbcTemplate = new JdbcTemplate(secondDataSource);
        List<CodeDetail_vw> codeDetailByCodeHeaderNm = codeDetailHeaderRepository.getCodeDetailByHeaderNmAndHeaderActive(PBBillingServiceConstants.COMPILE_PROCEDURE);
        //遍历存储过程
        for (CodeDetail_vw codeDetail : codeDetailByCodeHeaderNm) {
        	//数据库名字
            String dbName = codeDetail.getCodeDetailCd();
            //存储过程名字
            String spName = codeDetail.getCodeDetailNm();
            //拼接重新编译存储过程SQL
            String sql = "EXEC sp_recompile N'" + spName + "'";
            try {
            	/**
				第一次呢想通过datasource去改变catalog去切换数据库，然而并没有用，debug查看datasource的defaultcatalog
				属性依旧是修改前的。
				secondDataSource.getConnection().setCatalog(dbName);
                jdbcTemplate = new JdbcTemplate(secondDataSource);
                jdbcTemplate.execute(sql);
				**/
				/**
				第二次呢想通过BasicDataSource（在注入数据源的时候类型为BasicDataSource：@Autowired 
				@Qualifier(value = "secondDataSource") private BasicDataSource secondDataSource;）去改变catalog达到切
				换数据库，然而也没有用，虽然debug查看datasource的defaultcatalog属性已经变成修改后的了。可是依旧失败。
				（想不明白为什么）
				secondDataSource.setDefaultCatalog(dbName);
                jdbcTemplate = new JdbcTemplate(secondDataSource);
                jdbcTemplate.execute(sql);
				**/
				/**
				之后呢又作了很多的尝试，比如每循环一次把jdbcTemplate置为空，也都不好用。
				**/
				//接下来是重点了，终于皇天不负有心人，找到了办法解决问题
				//我们需要创建一个类，继承DelegatingDataSource，封装DataSource并且重写getConnection方法
				//在创建jdbcTemplate 时候把这个类作为参数传给jdbcTemplate
                jdbcTemplate = new JdbcTemplate(new DataSourceDelegating(secondDataSource, dbName));
                jdbcTemplate.execute(sql);
                logger.info("********** compileSPByDB Successful ---> " + dbName + "." + spName + " **********");
            } catch (Exception e) {
                logger.error("********** compileSPByDB Error ---> " + dbName + "." + spName + " || Throw Exception --> " + e + " **********");
                continue;
            }
        }
        logger.info("**************************** compileSPByDB end ***************************************");
    }
}

public class DataSourceDelegating extends DelegatingDataSource {

    private String catalogName;
	//创建时需要传入数据源和catalogName
    public DataSourceDelegating(DataSource dataSource, String catalogName) {
        super(dataSource);
        this.catalogName = catalogName;
    }
	//重写getConnection方法去重新set catalogName的值
    @Override
    public Connection getConnection() throws SQLException {
        final Connection connection = super.getConnection();
        connection.setCatalog(this.catalogName);
        return connection;
    }

}

如此一来，问题解决。