英文:
Fetching large volume of data with Spring Boot
问题
我一直在尝试使用SpringBoot为我的REST API获取数据，在我尝试获取所需数据时，我发起的请求一直超时。期望从获取请求中返回约50列和约2万条记录。我尝试将查询限制为3000条记录，获取请求成功通过，但我确实需要一次性获取所有记录。我已经将我需要的所有数据暂存在一个Materialized View中，所以查询不应该有任何问题，在SQL Plus中运行快速的select *查询将花费约10秒来返回所有记录。有没有关于如何在不超时的情况下继续的想法？
编辑：通过Insomnia测试API时，我没有收到任何错误，但在5分钟后收到“没有返回任何响应主体”的消息。
以下是部分代码：
DataListController.java
@ApiResponses(value = {
@ApiResponse(code = 401, message = "当前用户未经身份验证"),
@ApiResponse(code = 403, message = "当前用户权限不足"),
@ApiResponse(code = 500, message = "未处理的系统异常")
})
@RestController
@RequestMapping("/api/v1")
public class DataListController {
private DataSource dataSource;
DataListService dataListService;
@Autowired
public DataListController(
DataListService dataListService,
DataSource dataSource
) {
this.dataListService = dataListService;
this.dataSource = dataSource;
}
@ApiOperation(
value = "获取所有数据"
)
@ApiResponses(value = {
@ApiResponse(code = 200, response = DataListRecord.class, responseContainer = "List", message = "获取所有记录")
})
@GetMapping({"/data/list"})
public HttpEntity<List<Map>> getAllData(HttpServletResponse response) {
List<Map> result = dataListService.getAllData();
return ResponseEntity.ok(result);
}
}
DataListService.java
@Service
public class DataListService {
private DataListDao dataListDao;
@Autowired
public DataListService(
DataListDao dataListDao
) {
this.dataListDao = dataListDao;
}
public List<Map> getAllData() {
return dataListDao.getAllData();
}
}
DataListDao.java
@Mapper
public interface DataListDao {
public List<Map> getAllData();
}
DataListDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="project.repository.DataListDao">
<select id="getAllData" resultType="map">
select * from MV_DATA_LIST
</select>
</mapper>
英文:
I've been trying to fetch data using SpringBoot for my REST API, and the request I'm making keeps timing out when I try to fetch the data I need. The expected results from the fetch request will return ~50 columns and around 20k records. I tried limiting my query to 3k records and my fetch request got through, but I really need to get all the records in one go. I have all the data I need staged in a Materialized View, so there should not be any problem with the query, and running a quick select * in SQL plus will take around 10 sec to return all the records. Any ideas on how I get through this without getting timed out?
EDIT: Testing the API through Insomnia, I am not getting any errors, but just get a "No body returned for response" message after 5 minutes.
Some codes below:
DataListController.java
@ApiResponses(value = {
@ApiResponse(code = 401, message = "Unauthenticated session for current user"),
@ApiResponse(code = 403, message = "Insufficient privileges for current user"),
@ApiResponse(code = 500, message = "Unhandled system exception")
})
@RestController
@RequestMapping("/api/v1")
public class DataListController {
private DataSource dataSource;
DataListService dataListService;
@Autowired
public DataListController(
DataListService dataListService,
DataSource dataSource
) {
this.dataListService = dataListService;
this.dataSource = dataSource;
}
@ApiOperation(
value = "Fetch All Data"
)
@ApiResponses(value = {
@ApiResponse(code = 200, response = DataListRecord.class, responseContainer = "List", message = "Fetch all records")
})
@GetMapping({"/data/list"})
public HttpEntity<List<Map>> getAllData(HttpServletResponse response) {
List<Map> result = dataListService.getAllData();
return ResponseEntity.ok(result);
}
}
DataListService.java
@Service
public class DataListService {
private DataListDao dataListDao;
@Autowired
public DataListService(
DataListDao dataListDao
) {
this.dataListDao = dataListDao;
}
public List<Map> getAllData() {
return dataListDao.getAllData();
}
}
DataListDao.java
@Mapper
public interface DataListDao {
public List<Map> getAllData();
}
DataListDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="project.repository.DataListDao">
<select id="getAllData" resultType="map">
select * from MV_DATA_LIST
</select>
</mapper>
答案1
得分: 0
尝试设置嵌入式Tomcat的超时时间
将以下设置添加到您的application.properties文件中
server.connection-timeout=1200000
或者如果您在使用properties.yml文件，则添加以下设置
server:
connection-timeout: 1200000
英文:
Try setting the timeout of your embedded Tomcat
Add this setting to your application.properties
server.connection-timeout=1200000
Or in the case, you are using properties.yml
server:
connection-timeout: 1200000
答案2
得分: 0
你可以在你的MyBatis配置中增加defaultStatementTimeout来防止对数据库的调用超时。根据文档，默认值为25秒。
英文:
You can increase defaultStatementTimeout in your mybatis configuration to prevent calls to the database to time out. According to the documentation the default value is 25s.
专注分享java语言的经验与见解，让所有开发者获益！
评论