在工具类(非controlled、service)静态static方法中使用JdbcTemplate

场景：

在工具类中使用下面这种注解注入，是不能用static方法调用的，

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

@Autowired 注解不能注入使用static修饰的实例。

解决方案：

使用手工装配，并在resources目录下配置url.properties文件，写入数据源信息

package com.yonyou.occ.b2b.sync.nc;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.Properties;

/**
 * 静态方法调用 JdbcTemplateStaticUtils ，使用手工配置，自动配置对 static 不生效
 */
@Component
public class JdbcTemplateStaticUtils {

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

    private static Properties props = loadProperties();

    private static Properties loadProperties() {
        Properties properties = new Properties();
        try (InputStream in = JdbcTemplateStaticUtils.class.getClassLoader().getResourceAsStream("url.properties")) {
            if (in == null) {
                logger.error("url.properties not found.");
                throw new IllegalStateException("url.properties not found.");
            }
            properties.load(in);
        } catch (Exception e) {
            logger.error("Failed to load properties.", e);
            throw new IllegalStateException("Failed to initialize properties.", e);
        }
        return properties;
    }

    /**
     * 手工配置数据源
     * @return 数据源
     */
    public static DruidDataSource ncDataSource() {
        String driverClassName = props.getProperty("spring.datasource.nc.driver");
        String url = props.getProperty("spring.datasource.nc.url");
        String username = props.getProperty("spring.datasource.nc.username");
        String password = props.getProperty("spring.datasource.nc.password");

        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);

        return druidDataSource;
    }

    /**
     * 根据处理ID，查询转换前仓库ID
     * @param handleId
     * @return
     */
    public static String getCbodyWarehouseIdByHandleId(String handleId) {
        DruidDataSource druidDataSource = ncDataSource();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);

        String sql = "SELECT DISTINCT c.cbodywarehouseid FROM ic_transform_b c WHERE c.dr = 0 AND NVL(c.vbdef5, '~') <> '~' AND c.vbdef5 = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{handleId}, String.class);
        } catch (Exception e) {
            logger.error("Failed to query warehouse ID by handle ID.", e);
            return null;
        }
    }

    /**
     * 测试调用接口
     * @param args
     */
    public static void main(String[] args) {
        String handleId = "a9e72f2f-0819-48dc-9097-0d69ad708039";
        String cbodyWarehouseIdByHandleId = getCbodyWarehouseIdByHandleId(handleId);
        System.out.println("cbodyWarehouseIdByHandleId: " + cbodyWarehouseIdByHandleId);
    }
}

tip:网上还提供了下面这种方法，不知为何我用了却不生效，放这里参考一下

    @Autowired
    private NcDataQueryService ncDataQueryService;

    public static JdbcTemplateStaticUtils jdbcTemplateStaticUtils;

    @PostConstruct
    public void init() {
        jdbcTemplateStaticUtils = this;
        jdbcTemplateStaticUtils.ncDataQueryService = ncDataQueryService;
    }
public static void main(String[] args) {
        Integer i = jdbcTemplateStaticUtils.ncDataQueryService.queryDaysFormNc();
        System.out.println(i);
    }