1. DB IOException 메시지
대량의 데이터를 bulk insert 하면 PostgreSQL에서 Tried to send an out-of-range integer as a 2-byte value IOException이 발생한다.
PostgreSQL의 JDBC에서 바인딩할 수 있는 매개변수 갯수에 제한이 있기 때문이다.
2. 해결방법
Controller에서 select 데이터를 잘라서 insert 한다.
아래 소스는 2개의 DB에 연결하여 첫 번재 DB는 select, 두 번째 DB는 insert하는 소스이다. 따라서 두 개의 mapper가 존재한다.
1) Controller.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| @RestController
public class RetailController {
@Resource
private RetailService retailService;
@GetMapping("/retailList")
public ResponseEntity retailList() {
// data select 40000건
List<RetailDTO> selectRetailList = retailService.selectRetailList();
ArrayList<RetailDTO> retailDataList = new ArrayList<RetailDTO>();
int count;
int insCount = 0;
int selSize = selectRetailList.size();
for (count = 0; count < selSize; count++) {
RetailDTO retailDTO = new RetailDTO();
retailDTO = selectRetailList.get(count);
retailDataList.add(retailDTO);
// 1000건씩 자른다.
if (insCount == 1000 || selSize == count) {
// 1000건씩 insert
retailService.insertRetailList(retailDataList);
retailDataList = new ArrayList<RetailDTO>();
insCount = 0;
} else {
insCount++;
}
}
return new ResponseEntity(HttpStatus.OK);
}
}
|
2) Service.java
1
2
3
4
| public interface RetailService {
List<RetailDTO> selectRetailList();
int insertRetailList(List<RetailDTO> list);
}
|
3) ServiceImpl.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| @Service
public class RetailServiceImpl implements RetailService {
@Autowired
PrimaryMapper primaryMapper;
@Autowired
SecondaryMapper secondaryMapper;
@Override
public List<RetailDTO> selectRetailList() {
return primaryMapper.selectRetailList();
}
@Override
public int insertRetailList(List<RetailDTO> list) {
return secondaryMapper.insertRetailList(list);
}
}
|
4) SelectMapper.java
1
2
3
4
| @Mapper
public interface SelectMapper {
List<RetailDTO> selectRetailList();
}
|
5) InsertMapper.java
1
2
3
4
| @Mapper
public interface InsertMapper {
int insertRetailList(List<RetailDTO> list);
}
|
6) DTO.java
1
2
3
4
5
6
7
8
9
10
| @Getter
@Setter
public class RetailDTO {
private String bizplCd;
private String bizplNm;
private String bizplAddr;
private String telNo;
private String openDt;
private String closeDt;
}
|
7) SelectQuery.xml
1
2
3
4
5
6
7
8
9
| <select id="selectRetailList" resultType="com.example.dto.RetailDTO">
SELECT DISTINCT BIZPL_CD
, BIZPL_NM
, BIZPL_ADDR
, TEL_NO
, OPEN_DT
, CLOSE_DT
FROM TS_BIZPL
</select>
|
8) InsertQuery.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| <insert id="insertRetailList" parameterType="java.util.List" useGeneratedKeys="true">
INSERT INTO BIZPL ( BIZPL_CD
, BIZPL_NM
, BIZPL_ADDR
, TEL_NO
, OPEN_DT
, CLOSE_DT)
VALUES
<foreach collection="list" item="item" separator=",">
( #{item.bizplCd}
, #{item.bizplNm}
, #{item.bizplAddr}
, #{item.telNo}
, #{item.openDt}
, #{item.closeDt})
</foreach>
</insert>
|
[출처 및 참고]
