java - 使用 Java spring 和休眠的 Oracle 插入需要太多时间
问题描述
我有一个微服务,它从 API 获取一组对象,然后连接到远程 oracle BBDD 并插入信息。在一个电话中,我会得到大约 7k-10k 个寄存器,平均要节省 1 小时,这太疯狂了。为什么会这样?是服务器的问题还是我做错了什么?
application.properties 文件
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.datasource.url= jdbc:oracle:thin:url
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.jpa.hibernate.ddl-auto=none
hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.default_schema=schema
spring.jpa.show-sql=true
spring.data.rest.basePath=/api
spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.poolName=springBootJPAHikariCP
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=10000
桌子的型号
@Entity
@Table(name = "CALL")
public class Call {
@Column(name = "ID")
@Id
private String id;
@Column(name = "QUEUEID")
private String queueId;
@Column(name = "QUEUETYPE")
private String queueType;
@Column(name = "AGENTID")
private String agentId;
@Column(name = "DATASET")
private String dataSet;
@Column(name = "CALLDATE")
private Timestamp callDate;
@Column(name = "CALLDURATION")
private String callDuration;
@Column(name = "RINGTIME")
private String ringTime;
@Column(name = "RESULT")
private String result;
@Column(name = "DATE_LOAD")
private Date fecCarga = new Date();
//GETTERS AND SETTERS
JAVA逻辑
逻辑很简单
ObjectMapper objectMapper = new ObjectMapper();
String response = reportingServices.getToken();
Token token = objectMapper.readValue(response, Token.class);
String tokenString = token.setToken(token.getToken());
String startDate = req.getStartDate();
String endDate = req.getEndDate() ;
final String uri = API_URL
RestTemplate restTemplate = new RestTemplate();
String result = restTemplate.getForObject(uri, String.class);
JSONObject json = new JSONObject(result);
JSONArray jsonArray = json.getJSONArray("list");
try{
for (int i = 0, size = jsonArray.length(); i < size; i++)
{
JSONObject objectInArray = jsonArray.getJSONObject(i);
Call call = new Call();
String callid = (String) objectInArray.get("callid");
String qid = (String) objectInArray.get("qid");
String type = (String) objectInArray.get("type");
String agent = (String) objectInArray.get("agent");
String dataset = (String) objectInArray.get("dataset");
String datetime = (String) objectInArray.get("datetime");
String duration = (String) objectInArray.get("duration");
String ringtime = (String) objectInArray.get("ringtime");
String resultTable = (String) objectInArray.get("result");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date parsedDate = dateFormat.parse(datetime);
Timestamp timestamp = new java.sql.Timestamp(parsedDate.getTime());
call.setId(callid);
call.setQueueId(qid);
call.setQueueType(type);
call.setAgentId(agent);
call.setDataSet(dataset);
call.setCallDate(timestamp);
call.setCallDuration(duration);
call.setRingTime(ringtime);
call.setResult(resultTable);
call.setFecCarga(call.getFecCarga());
callRepository.save(call);
}
return ResponseHandler.generateResponse(HttpStatus.CREATED, "Info created successfully");
} catch (Exception e) {
return ResponseHandler.generateResponse(HttpStatus.INTERNAL_SERVER_ERROR, "Something went wrong.");
}
存储库
import org.springframework.data.jpa.repository.JpaRepository;
public interface callRepository extends JpaRepository<Call,Long> {
}
解决方案
即使您配置spring.jpa.properties.hibernate.jdbc.batch_size
了它也无济于事,因为您正在单独保存列表中的每个条目。使用saveAll()
JpaRepository 中的方法并至少分批保存 100 个。
推荐阅读
- node.js - NPM 自动排除文件 >
- c# - 使用不在单独表中的 Linq 查询
- python - 如何使用作为同一 Python 脚本中的字符串的超级块扩展 jinja2 模板
- php - 传播数组值
- c++ - 如何检查两个不同类型的向量是否相等
- python - 检查列表中字符串的整数并添加它们
- installation - Azure 函数(云):安装 cython 依赖模块
- apache - Blazor 服务器端 + Apache + Azure AD
- javascript - 未捕获的 ReferenceError: loaderOff 未在 HTMLIFrameElement.onload 中定义
- java - Spring Rest 模板使用 % 将值注入参数