JDBC模板工作速度慢

Question

@Configuration
public class DatabaseConfig {

    @Autowired
    private Environment env;

    @Bean(name = "fraudDb")
    public DataSource masterDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("driver-class-name"));
        dataSource.setUrl(env.getProperty("fraud.url"));
        dataSource.setUsername(env.getProperty("fraud.username"));
        dataSource.setPassword(env.getProperty("fraud.password"));

        return dataSource;
    }

    @Bean(name = "ndvliveDb")
    public DataSource secondDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("driver-class-name"));
        dataSource.setUrl(env.getProperty("ndvlive.url"));
        dataSource.setUsername(env.getProperty("ndvlive.username"));
        dataSource.setPassword(env.getProperty("ndvlive.password"));

        return dataSource;
    }

    @Bean(name = "fraudJdbcTemplate")
    @Autowired
    public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
        return new JdbcTemplate(fraudDb);
    }

    @Bean(name = "ndvliveJdbcTemplate")
    @Autowired
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
        return new JdbcTemplate(ndvliveDb);
    }
}

private RbtranServiceInputModel services(FraudActionsRestRequest fraudActionsRestRequest) {
Long start = System.nanoTime();
        String debitSegmentId = ndvliveCustomerInfoService.getSBUCode(Integer.parseInt(cifNoSender));
        Long end = System.nanoTime();
        System.out.println("debitSegmentId " + (end - start) / 1e6);

        //10 ms - 20 ms
        start = System.nanoTime();
        String debitName = ndvliveCustomerInfoService.getNameSurname(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("debitName " + (end - start) / 1e6);

        //3 ms
        start = System.nanoTime();
        ResponseGetBMSInfo bmsInfo = ndvliveCustomerInfoService.getOnlineCustomerInfo(new BigDecimal(cifNoSender));
        end = System.nanoTime();
        System.out.println("bmsInfo " + (end - start) / 1e6);

        //10 MS
        start = System.nanoTime();
        Date passwordChangeDate = ndvliveCustomerInfoService.getPasswordChangeDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("passwordChangeDate " + (end - start) / 1e6);

        //10ms
        start = System.nanoTime();
        Date smartSmsGsmNoRegistrationDate = ndvliveCustomerInfoService.getSmartSmsGsmNoRegistrationDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("smartSmsGsmNoRegistrationDate " + (end - start) / 1e6);

        //6 ms
        start = System.nanoTime();
        Date membershipDate = ndvliveCustomerInfoService.getMembershipDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("membershipDate " + (end - start) / 1e6);

        start = System.nanoTime();
        BigDecimal smartSmsNo = ndvliveCustomerInfoService.getSmsGsmNo(new BigDecimal(cifNoSender));//TODO sms;
        end = System.nanoTime();
        System.out.println("smartSmsNo " + (end - start) / 1e6);

        start = System.nanoTime();
        String habitInfo = ndvliveCustomerInfoService.getHabitInfo(new BigDecimal(cifNoSender), channel);
        end = System.nanoTime();
        System.out.println("habitInfo " + (end - start) / 1e6);

        start = System.nanoTime();
        Date lastBlockSimDate = ndvliveCustomerInfoService.getLastBlockSimDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("lastBlockSimDate " + (end - start) / 1e6);

        start = System.nanoTime();
        boolean isFamiliar = ndvliveCustomerAccountInfoService.getFamiliarAccount(new BigDecimal(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getProcessCode(), fraudActionsRestRequest.getCreditAcctNumber(), fraudActionsRestRequest.getSmartSmsNo());
        end = System.nanoTime();
        System.out.println("isFamiliar " + (end - start) / 1e6);
        rbtranServiceInputModel.setKnownAccount(isFamiliar);

        start = System.nanoTime();
        boolean isInWhitelist = ndvliveDeviceInfoService.isInWhiteList(Long.parseLong(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getUserData02(), fraudActionsRestRequest.getDeviceId());
        end = System.nanoTime();
        System.out.println("isInWhitelist " + (end - start) / 1e6);
        rbtranServiceInputModel.setInWhitelist(isInWhitelist);
    }

public interface CustomerInformationTempDao {
    String getSbuCodeByClientNo(Integer clientNo);
}

@Repository
public class CustomerInformationTempDaoImpl implements CustomerInformationTempDao {

    @Autowired
    @Qualifier("ndvliveJdbcTemplate")
    private JdbcTemplate ndvliveJdbcTemplate;

    public String getSbuCodeByClientNo(Integer clientNo) {
        String query = "SELECT * FROM mytable WHERE client_no=" + clientNo;
        try {
            return ndvliveJdbcTemplate.queryForObject(query, (resultSet, i) -> resultSet.getString("SBU_CODE"));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}

@Configuration
public class DatabaseConfig {

    @Autowired
    private Environment env;

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Bean(name = "fraudDb")
    public DataSource masterDataSource() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.dataSourceProperties.getClassLoader())
                .driverClassName(env.getProperty("driver-class-name"))
                .url(env.getProperty("fraud.url"))
                .username(env.getProperty("fraud.username"))
                .password(env.getProperty("fraud.password"));
        return factory.build();
    }

    @Bean(name = "ndvliveDb")
    public DataSource secondDataSource() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.dataSourceProperties.getClassLoader())
                .driverClassName(env.getProperty("driver-class-name"))
                .url(env.getProperty("ndvlive.url"))
                .username(env.getProperty("ndvlive.username"))
                .password(env.getProperty("ndvlive.password"));
        return factory.build();
    }

    @Bean(name = "fraudJdbcTemplate")
    @Autowired
    public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
        return new JdbcTemplate(fraudDb);
    }

    @Bean(name = "ndvliveJdbcTemplate")
    @Autowired
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
        return new JdbcTemplate(ndvliveDb);
    }
}

Answer 1

要提高性能，您可以做的事情很少：

1. 不使用DriverManagerDataSource定义具有固定连接计数的连接池。为了简化，请使用singleConnectionDataSource，它不会执行任何在线借用连接验证。这不是一个生产等级设置，但在您的测试中会有较少的活动片段。
2. 而不是select*只指定所需的列。解析*在JDBC和命令行客户端之间可能不同，最好删除此未知。
3. 与client_no=？一起使用准备好的语句，而不是client_no=“+clientno级联。这将在检查不同的客户端号码时实现更好的查询计划缓存。

请注意，JVM需要对一个方法进行10,000次以上的调用才能开始JIT编译和优化。如果您有一个简单的main()，它没有预热JVM，那么代码将会更慢。命令行客户端已编译为本机代码。