SpringJDBCTemplate行Map器对于少量记录来说太慢了

rqqzpn5f  于 2021-06-21  发布在  Mysql
关注(0)|答案(0)|浏览(177)

我正在使用spring、postgresql并尝试通过jdbctemplate行Map器运行查询。我的数据库中有大约100万条记录,从查询中只返回了12条记录。当我在数据库上运行这个查询时,它花费的时间不到一秒钟,但是当我使用 JdbcTemplate 用一个 RowMapper 大约需要11秒才能得到结果
这是我的密码:
控制器

@RequestMapping(
      path = arrayOf("/getAppData/{userId}", "/getAppData/{userId}/"),
      method = arrayOf(RequestMethod.GET))
open fun getAppData(@PathVariable("userId")userId:Long): ResponseEntity<*> {
    var user= userRepo.findOne(userId)?: throw exception(ExceptionEvent.UserIdNotFound,userId)
var groups= groupService.loadListOfNodeIds(user.groupId)
var res= appService.getLineChartByGroupYear(userId,groups)
return ResponseEntity(res, HttpStatus.OK)

}

服务

@Service("appService")
@Transactional
open class AppServiceImpl : BaseService(), AppService {

    @Autowired
    private lateinit var jdbc: JdbcTemplate

    @Autowired
    private lateinit var complianceRepo: ComplianceRepo

    @Autowired
    open lateinit var groupService: GroupService

//**The query**

    override fun getLineChartByGroupYear(userId: Long,groups: List<Long>): List<Map<LocalDateTime,Float>>? {

        var sql= """ SELECT
                 date,
                  CASE WHEN TOTAL = 0 THEN 0 ELSE 1.0 * compliant / total end as compliance_rate
                   from
                   (select
                    date_trunc('month',location_timestamp) as date,
                    count(case when event_type = 'COMPL_HANDWASH' then 1 else null end) as compliant,
                    count(1) as total
                    from compliance
                    where group_id = any(ARRAY $groups) and location_timestamp between (now() - interval '1 year') and now()
                     group by 1) A
                     ORDER BY date; """
        var stopwatch=StopWatch()
        stopwatch.start()

        var res=jdbc.query( sql,
            BeanPropertyRowMapper<lineChartDTO>(lineChartDTO::class.java))
          stopwatch.stop()
          logger.info("LineChartByGroupYear query BeanPropertyRowMapper1: ${stopwatch.totalTimeSeconds}")
        return res

     }
}

行Map器的linechartdto对象

public class lineChartDTO {
    private LocalDateTime date;
    private Float complianceRate;

    public lineChartDTO( LocalDateTime date, Float complianceRate) {
        this.date = date;
        this.complianceRate = complianceRate;
    }

    public lineChartDTO() {
    }

    public LocalDateTime getDate() {
        return date;
    }

    public void setDate(LocalDateTime date) {
        this.date = date;
    }

    public Float getComplianceRate() {
        return complianceRate;
    }

    public void setComplianceRate(Float complianceRate) {
        this.complianceRate = complianceRate;
    }
 }

配置

@Configuration
 @EnableJpaRepositories("com.nex")
 @EnableTransactionManagement
 open class DataSourceConfig {

   val DATA_PACKAGE = "com.nex.data"
   val INTERCEPTOR_KEY = "hibernate.ejb.interceptor"

     @Value("\${spring.datasource.username:${Const.NONE}}")
     lateinit var username: String

     @Value("\${spring.datasource.password:${Const.NONE}}")
     lateinit var password: String

  @Value("\${spring.datasource.url}")
  lateinit var url: String

  @Value("\${spring.datasource.driverClassName}")
  lateinit var driverClass: String

  @Value("\${spring.datasource.sslenabled}")
   var sslEnabled: Boolean = false

    @Bean
    @Primary
    open fun dataSource(): DataSource {
    var dbUrl = url
    val builder = DataSourceBuilder.create().driverClassName(driverClass)
     if (sslEnabled) {
    val sslPath = ClassPathResource("root.crt").uri.path
    val sslParams = "ssl=true&sslcert=$sslPath&sslrootcert=$sslPath&sslmode=verify-full"
    dbUrl = "$url?$sslParams"
    }
 if (username != Const.NONE && password != Const.NONE) builder.username(username).password(password)
return builder.url(dbUrl).build()
}

 @Bean
 open fun entityManagerFactory(
  factory: EntityManagerFactoryBuilder, dataSource: DataSource,
  properties: JpaProperties): LocalContainerEntityManagerFactoryBean {
  val jpaProperties = HashMap<String, Any>()
  jpaProperties.putAll(properties.getHibernateProperties(dataSource))
  jpaProperties.put(INTERCEPTOR_KEY, hibernateInterceptor())
  return factory.dataSource(dataSource).packages(DATA_PACKAGE)
    .properties(jpaProperties as MutableMap<String, *>)
    .build()
   }

  @Bean
  open fun hibernateInterceptor(): HibernateStatisticsInterceptor {
      return HibernateStatisticsInterceptor()
  }

  @Bean
  open fun requestStatisticsInterceptor(): RequestStatisticsInterceptor {
      return RequestStatisticsInterceptor()
  }
}

从查询中,每次只返回12条记录。我已经试过设置 fetchSize 但什么也没发生。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题