-
Notifications
You must be signed in to change notification settings - Fork 0
/
RestaurantJdbcRepositoryImpl.java
129 lines (105 loc) · 5.05 KB
/
RestaurantJdbcRepositoryImpl.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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
package com.wanted.teamV.repository.impl;
import com.wanted.teamV.entity.Restaurant;
import com.wanted.teamV.repository.RestaurantJdbcRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
@RequiredArgsConstructor
public class RestaurantJdbcRepositoryImpl implements RestaurantJdbcRepository {
private final JdbcTemplate jdbcTemplate;
private static final Integer DEFAULT_CHUNK_SIZE = 1_000;
@Override
public void bulkInsert(List<Restaurant> restaurants) {
List<List<Restaurant>> partitions = partition(restaurants);
for (List<Restaurant> partition : partitions) {
insert(partition);
}
}
private void insert(List<Restaurant> restaurants) {
String sql = "INSERT IGNORE INTO restaurant (" +
"name, sigun, type, roadname_address, lot_address, zip_code, lat, lon, average_rating, bsn_state_nm, " +
"sigun_cd, license_de, clsbiz_de, locplc_ar, grad_faclt_div_nm, male_enflpsn_cnt, yy, " +
"multi_use_bizestbl_yn, grad_div_nm, tot_faclt_scale, female_enflpsn_cnt, " +
"circumfr_div_nm, sanittn_indutype_nm, tot_emply_cnt" +
") VALUES (" +
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" +
")";
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss");
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Restaurant restaurant = restaurants.get(i);
String licenseDe = restaurant.getLicenseDe() == null ? null : restaurant.getLicenseDe().format(formatter);
String clsbizDe = restaurant.getClsbizDe() == null ? null : restaurant.getClsbizDe().format(formatter);
ps.setString(1, restaurant.getName());
ps.setString(2, restaurant.getSigun());
ps.setString(3, restaurant.getType());
ps.setString(4, restaurant.getRoadnameAddress());
ps.setString(5, restaurant.getLotAddress());
ps.setString(6, restaurant.getZipCode());
ps.setDouble(7, restaurant.getLat());
ps.setDouble(8, restaurant.getLon());
ps.setDouble(9, restaurant.getAverageRating());
ps.setString(10, restaurant.getBsnStateNm());
ps.setString(11, restaurant.getSigunCd());
ps.setString(12, licenseDe);
ps.setString(13, clsbizDe);
if (restaurant.getLocplcAr() == null) {
ps.setString(14, null);
} else {
ps.setDouble(14, restaurant.getLocplcAr());
}
ps.setString(15, restaurant.getGradFacltDivNm());
if (restaurant.getMaleEnflpsnCnt() == null) {
ps.setString(16, null);
} else {
ps.setInt(16, restaurant.getMaleEnflpsnCnt());
}
if (restaurant.getYy() == null) {
ps.setString(17, null);
} else {
ps.setInt(17, restaurant.getYy());
}
if (restaurant.getMultiUseBizestblYn() == null) {
ps.setString(18, null);
} else {
ps.setBoolean(18, restaurant.getMultiUseBizestblYn());
}
ps.setString(19, restaurant.getGradDivNm());
if (restaurant.getTotFacltScale() == null) {
ps.setString(20, null);
} else {
ps.setDouble(20, restaurant.getTotFacltScale());
}
if (restaurant.getFemaleEnflpsnCnt() == null) {
ps.setString(21, null);
} else {
ps.setInt(21, restaurant.getFemaleEnflpsnCnt());
}
ps.setString(22, restaurant.getCircumfrDivNm());
ps.setString(23, restaurant.getSanittnIndutypeNm());
if (restaurant.getTotEmplyCnt() == null) {
ps.setString(24, null);
} else {
ps.setInt(24, restaurant.getTotEmplyCnt());
}
}
@Override
public int getBatchSize() {
return restaurants.size();
}
});
}
private List<List<Restaurant>> partition(List<Restaurant> restaurants) {
List<List<Restaurant>> partitions = new ArrayList<>();
for (int i = 0; i < restaurants.size(); i += DEFAULT_CHUNK_SIZE) {
partitions.add(restaurants.subList(i, Integer.min(i + DEFAULT_CHUNK_SIZE, restaurants.size())));
}
return partitions;
}
}