java - 访问存储过程时的 Spring JDBCTemplate 与简单的 JDBC 性能

Question

我在使用 JDBCTemplate 通过存储过程访问数据时遇到了与性能相关的问题。相反，当我从 Simple JDBC 访问时，它运行得非常快。

2019-12-04 11:55:43.924 INFO 9512 --- [main] etTestStoredProcPerformanceApplication：未设置活动配置文件，回退到默认配置文件：默认 2019-12-04 11:55:46.002 INFO 9512 --- [main] etTestStoredProcPerformanceApplication ：在 2.831 秒内启动 TestStoredProcPerformanceApplication（JVM 运行 3.57）

尝试使用简单的 Jdbc

Calling get_all_persons Attempt : 1 took 1173 milliseconds
 Calling get_all_persons Attempt : 2 took 29 milliseconds
 Calling get_all_persons Attempt : 3 took 27 milliseconds
 Calling get_all_persons Attempt : 4 took 67 milliseconds
 Calling get_all_persons Attempt : 5 took 31 milliseconds
 Calling get_all_persons Attempt : 6 took 30 milliseconds
 Calling get_all_persons Attempt : 7 took 30 milliseconds
 Calling get_all_persons Attempt : 8 took 27 milliseconds
 Calling get_all_persons Attempt : 9 took 29 milliseconds

尝试使用 jdbcTemplate

2019-12-04 11:55:47.475 INFO 9512 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - 开始... 2019-12-04 11:55:47.514 INFO 9512 --- [ main] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - 驱动程序不支持获取/设置连接的网络超时。(oracle.jdbc.driver.T4CConnection.getNetworkTimeout()I) 2019-12-04 11:55:47.524 INFO 9512 --- [main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - 启动完成。

 Calling get_all_persons Attempt : 1 took 226 milliseconds
 Calling get_all_persons Attempt : 2 took 111 milliseconds
 Calling get_all_persons Attempt : 3 took 112 milliseconds
 Calling get_all_persons Attempt : 4 took 128 milliseconds
 Calling get_all_persons Attempt : 5 took 111 milliseconds
 Calling get_all_persons Attempt : 6 took 97  milliseconds
 Calling get_all_persons Attempt : 7 took 98  milliseconds
 Calling get_all_persons Attempt : 8 took 98  milliseconds
 Calling get_all_persons Attempt : 9 took 99  milliseconds

2019-12-04 11:55:48.547 INFO 9512 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - 已启动关闭... 2019-12-04 11:55:48.566 INFO 9512 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - 关闭完成。

这个结果来自我的本地机器。当我实际使用 JDBCTemplate 访问我的办公室测试服务器时，它需要 5 到 7 秒，而简单的 JDBC 只需要 2 秒，即使我在每次迭代中创建连接。使用 spring jdbc 模板，它从 application.properties 创建 2 个连接

应用程序属性

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=system
spring.datasource.password=*****
spring.datasource.driver-class=oracle.jdbc.driver.OracleDriver
spring.datasource.type = com.zaxxer.hikari.HikariDataSource 
spring.datasource.hikari.maximum-pool-size=2
spring.datasource.hikari.leakDetectionThreshold=2000
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.idleTimeout=500000
spring.datasource.hikari.maxLifetime=1700000

访问存储过程的代码。

@Component
public class Initializer2 implements ApplicationListener<ApplicationReadyEvent> {

    @Autowired
    private JdbcTemplate jdbcTemplate; 
    public static String packageName = "myPackage";
    public static String procName = "get_all_persons";

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        testStoredProcWithJdbc("");
        testStoredProcWithJdbcTemplate("");
    }

    private void testStoredProcWithJdbcTemplate(String string) {
        System.out.println("Trying with jdbcTemplate");
        try {
            for(int i = 1; i < 10; i++) {
                long startTime = System.currentTimeMillis();
                SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate);
                jdbcCall.withCatalogName(packageName);
                jdbcCall.withProcedureName(procName);
                jdbcCall.addDeclaredParameter(new SqlOutParameter("o_result_set", oracle.jdbc.OracleTypes.CURSOR));
                jdbcCall.addDeclaredParameter(new SqlOutParameter("o_message", oracle.jdbc.OracleTypes.VARCHAR));
                jdbcCall.execute();
                long endTime = System.currentTimeMillis();
                System.out.println("Calling get_all_persons Attempt : "+i+" took "+ (endTime - startTime) + " milliseconds");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void testStoredProcWithJdbc(String procName) {
        System.out.println("Trying with Simple Jdbc");
        try {
            for(int i = 1; i < 10; i++) {
                long startTime = System.currentTimeMillis();
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "*****");
                CallableStatement cstmt = conn.prepareCall("call "+packageName+"."+procName+"(?,?)");
                cstmt.registerOutParameter("o_result_set", oracle.jdbc.OracleTypes.CURSOR);
                cstmt.registerOutParameter("o_message", oracle.jdbc.OracleTypes.VARCHAR); 
                cstmt.execute();
                ResultSet resultSet = (ResultSet) cstmt.getObject("o_result_set");
                List<Person> persons = new ArrayList<>();
                Map<String, Object> map = new HashMap<>();
                map.put("o_message", cstmt.getString("o_message"));
                while(resultSet.next()) {
                    Person person = new Person();
                    person.setId(resultSet.getInt("id"));
                    person.setName(resultSet.getString("name"));
                    person.setEmail(resultSet.getString("email"));
                    person.setMobile(resultSet.getLong("mobile"));
                    if(resultSet.getTimestamp("birthdate") != null)
                        person.setBirthDate(resultSet.getTimestamp("birthdate").toLocalDateTime());
                    persons.add(person);
                }
                map.put("o_result_set", persons);
                cstmt.close();
                conn.close();
                long endTime = System.currentTimeMillis();
                System.out.println("Calling get_all_persons Attempt : "+i+" took "+ (endTime - startTime) + " milliseconds");
            }
            System.out.println("Successful...");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

标签： javaperformancejdbcspring-jdbcjdbctemplate