본문 바로가기
Back-end/JAVA

[JAVA] SQLite 무작정 시작하기 (7) - 다중 INSERT

by 허도치 2020. 11. 15.
이전글

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (1) - DATABASE 연결/해제

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (2) - TABLE 생성/삭제

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (3) - INSERT

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (4) - SELECT

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (5) - UPDATE

2020/11/15 - [Back-end/JAVA] - [JAVA] SQLite 무작정 시작하기 (6) - DELETE

 

 

 

서론

  이전에 만들었던 INSERT는 단일 데이터를 입력하는 기능이었는데, 데이터를 입력할 때는 다중으로 입력하는 경우가 많다. 그래서, 이번 포스트에서는 데이터를 다중으로 입력하는 방법에 대해서 다루어볼 계획이다.

 

 

 

소스코드
1. DMLService.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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
package com.dochi.db.ex;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
 
public class DMLService extends SQLiteManager {
    
    // 상수 설정
    //   - DateFormat 설정
    private static final SimpleDateFormat DATETIME_FMT = new SimpleDateFormat("yyyyMMddHHmmss");
    //   - Batch건수 설정
    //     * 100건마다 적재 시도
    private static final int OPT_BATCH_SIZE = 100;
    
    // 생성자
    public DMLService() {
        
    }
    public DMLService(String url) {
        super(url);
    }
    
    // 다중 데이터 삽입 함수
    public int insertBlogArticle(List<Map<String, Object>> dataMapList) throws SQLException {
        // 상수 설정
        //   - 데이터 입력 SQL 변수
        final String sql = "INSERT INTO CW_BLOG_ATCL_LIST ("+"\n"
                   + "    BLOG_ID,                         "+"\n"
                   + "    CATE_ID,                         "+"\n"
                   + "    ATCL_ID,                         "+"\n"
                   + "    URL,                             "+"\n"
                   + "    TITLE,                           "+"\n"
                   + "    WORK_YN,                         "+"\n"
                   + "    REG_DTTM                         "+"\n"
                   + ") VALUES (                           "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?                                "+"\n"
                   + ")";
 
        // 변수설정
        //   - Database 변수
        Connection conn = ensureConnection();
        PreparedStatement pstmt = null;
 
        //   - 입력 결과 변수
        int inserted = 0;
 
        try {
            // PreparedStatement 생성
            pstmt = conn.prepareStatement(sql);
            
            // 입력받은 데이터를 Batch 처리
            for(int i = 0; i < dataMapList.size(); i++ ) {
                // 입력 데이터 객체
                Map<String, Object> dataMap = dataMapList.get(i);
                
                // 입력 데이터 매핑
                pstmt.setObject(1, dataMap.get("BLOG_ID"));
                pstmt.setObject(2, dataMap.get("CATE_ID"));
                pstmt.setObject(3, dataMap.get("ATCL_ID"));
                pstmt.setObject(4, dataMap.get("URL"));
                pstmt.setObject(5, dataMap.get("TITLE"));
                pstmt.setObject(6, dataMap.get("WORK_YN"));
                pstmt.setObject(7, DATETIME_FMT.format(new Date()));
                
                // Batch에 추가
                pstmt.addBatch();
                
                // Batch 실행
                if( i % OPT_BATCH_SIZE == 0 ) {
                    inserted += pstmt.executeBatch().length;
                }
            }
 
            // 입력 건수 조회
            inserted += pstmt.executeBatch().length;
 
            // 트랜잭션 COMMIT
            conn.commit();
 
        } catch (SQLException e) {
            // 오류출력
            System.out.println(e.getMessage());
            
            // 오류
            inserted = -1;
            
            // 트랜잭션 ROLLBACK
            if( conn != null ) {
                conn.rollback();
            }
 
        } finally {
            // PreparedStatement 종료
            if( pstmt != null ) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
 
        // 결과 반환
        //   - 입력된 데이터 건수
        return inserted;
    }
    
    // 데이터 삽입 함수
    public int insertBlogArticle(Map<String, Object> dataMap) throws SQLException {
        // 상수 설정
        //   - 데이터 입력 SQL 변수
        final String sql = "INSERT INTO CW_BLOG_ATCL_LIST ("+"\n"
                   + "    BLOG_ID,                         "+"\n"
                   + "    CATE_ID,                         "+"\n"
                   + "    ATCL_ID,                         "+"\n"
                   + "    URL,                             "+"\n"
                   + "    TITLE,                           "+"\n"
                   + "    WORK_YN,                         "+"\n"
                   + "    REG_DTTM                         "+"\n"
                   + ") VALUES (                           "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?,                               "+"\n"
                   + "    ?                                "+"\n"
                   + ")";
 
        // 변수설정
        //   - Database 변수
        Connection conn = ensureConnection();
        PreparedStatement pstmt = null;
 
        //   - 입력 결과 변수
        int inserted = 0;
 
        try {
            // PreparedStatement 생성
            pstmt = conn.prepareStatement(sql);
 
            // 입력 데이터 매핑
            pstmt.setObject(1, dataMap.get("BLOG_ID"));
            pstmt.setObject(2, dataMap.get("CATE_ID"));
            pstmt.setObject(3, dataMap.get("ATCL_ID"));
            pstmt.setObject(4, dataMap.get("URL"));
            pstmt.setObject(5, dataMap.get("TITLE"));
            pstmt.setObject(6, dataMap.get("WORK_YN"));
            pstmt.setObject(7, DATETIME_FMT.format(new Date()));
 
            // SQL 실행
            pstmt.executeUpdate();
 
            // 입력 건수 조회
            inserted = pstmt.getUpdateCount();
 
            // 트랜잭션 COMMIT
            conn.commit();
 
        } catch (SQLException e) {
            // 오류출력
            System.out.println(e.getMessage());
            
            // 오류
            inserted = -1;
            
            // 트랜잭션 ROLLBACK
            if( conn != null ) {
                conn.rollback();
            }
 
        } finally {
            // PreparedStatement 종료
            if( pstmt != null ) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
 
        // 결과 반환
        //   - 입력된 데이터 건수
        return inserted;
    }
 
    // 데이터 수정 함수
    public int updateBlogArticle(Map<String, Object> dataMap, Map<String, Object> updateMap) throws SQLException {
        // 상수 설정
        //   - 수정할 컬럼을 동적으로 작성하기 위한 SQL 변수
        final StringBuilder sqlBuilder = new StringBuilder();
        //   - PreparedStatement 객체에 데이터를 매핑할 순서를 저장할 변수
        final List<String> updateSeq = new ArrayList<String>();
 
        // 수정일자 추가
        updateMap.put("UPD_DTTM", DATETIME_FMT.format(new Date()));
        
        // 동적 SQL 작성
        sqlBuilder.append("UPDATE CW_BLOG_ATCL_LIST \n");
        sqlBuilder.append("SET ");
        sqlBuilder.append(
            // 수정할 Data를 조회하여 동적 SQL 작성
            updateMap.keySet().stream().reduce("", (prev, current)->{
                // 데이터를 매핑할 순서를 저장
                updateSeq.add(current);
                
                current = current + " = ? ";
                if!"".equals(prev) ) {
                    prev = prev+", ";
                }
                
                return prev+current+"\n";
            })
        );
        sqlBuilder.append("WHERE 1=1 \n");
        sqlBuilder.append("  AND BLOG_ID = ? \n");
        sqlBuilder.append("  AND CATE_ID = ? \n");
        sqlBuilder.append("  AND ATCL_ID = ? \n");
 
        //   - 최종 SQL
        final String SQL = sqlBuilder.toString();
 
        // 변수설정
        //   - Database 변수
        Connection conn = ensureConnection();
        PreparedStatement pstmt = null;
 
        //   - 수정 결과 변수
        int updated = 0;
 
        try {
            // PreparedStatement 객체 생성
            pstmt = conn.prepareStatement(SQL);
 
            // 수정할 데이터 매핑
            int updateSize = updateSeq.size();
            for(int i=1; i<=updateSize; i++ ) {
                pstmt.setObject(i, updateMap.get(updateSeq.get(i-1)));
            }
 
            // 수정할 데이터 조건 매핑
            pstmt.setObject(updateSize+1, dataMap.get("BLOG_ID"));
            pstmt.setObject(updateSize+2, dataMap.get("CATE_ID"));
            pstmt.setObject(updateSize+3, dataMap.get("ATCL_ID"));
 
            // SQL 실행
            pstmt.executeUpdate();
    
            // 수정 건수 조회
            updated = pstmt.getUpdateCount();
 
            // 트랜잭션 COMMIT
            conn.commit();
 
        } catch (SQLException e) {
            // 오류처리
            System.out.println(e.getMessage());
            
            // 오류
            updated = -1;
            
            // 트랜잭션 ROLLBACK
            conn.rollback();
 
        } finally  {
            // PreparedStatement 종료
            if( pstmt != null ) {
                try {
                    pstmt.close();
                } catch ( SQLException e ) {
                    e.printStackTrace();
                }
            }
        }
 
        // 결과 반환
        //   - 수정된 데이터 건수
        return updated;
    }
 
    // 데이터 삭제 함수
    public int deleteBlogArticle(Map<String, Object> dataMap) throws SQLException {
        final String sql = "DELETE FROM CW_BLOG_ATCL_LIST  "+"\n"
                         + " WHERE 1=1                     "+"\n"
                         + "   AND BLOG_ID = ?             "+"\n"
                         + "   AND CATE_ID = ?             "+"\n"
                         + "   AND ATCL_ID = ?             "+"\n"
                         ;
 
        // 변수설정
        //   - Database 변수
        Connection conn = ensureConnection();
        PreparedStatement pstmt = null;
 
        //   - 삭제 결과 변수
        int deleted = 0;
 
        try {
            // PreparedStatement 객체 생성
            pstmt = conn.prepareStatement(sql);
 
            // 조회 데이터 조건 매핑
            pstmt.setObject(1, dataMap.get("BLOG_ID"));
            pstmt.setObject(2, dataMap.get("CATE_ID"));
            pstmt.setObject(3, dataMap.get("ATCL_ID"));
 
            // SQL 실행
            pstmt.executeUpdate();
            
            // 데이터 삭제 건수
            deleted = pstmt.getUpdateCount();
 
            // 트랜잭션 COMMIT
            conn.commit();
 
        } catch (SQLException e) {
            // 오류처리
            System.out.println(e.getMessage());
            
            // 오류
            deleted = -1;
 
            // 트랜잭션 ROLLBACK
            conn.commit();
 
        } finally  {
            // PreparedStatement 종료
            if( pstmt != null ) {
                try {
                    pstmt.close();
                } catch ( SQLException e ) {
                    e.printStackTrace();
                }
            }
        }
 
        // 결과 반환
        //   - 삭제된 데이터 건수
        return deleted;
    }
}



 
cs

 

2. App.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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
package com.dochi.db.ex;
 
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import com.dochi.db.ex.DDLService.ResultType;
 
public class App {
 
    // 변수 생성
    //   - DDL 객체 변수 ( dochi.db 파일 생성 )
    private DDLService DDL = new DDLService("jdbc:sqlite:dochi.db");
 
    //   - DML 객체 변수 ( dochi.db 파일 수정 )
    private DMLService DML = new DMLService("jdbc:sqlite:dochi.db");
    
    //   - DQL 객체 변수 ( dochi.db 파일 조회 )
    private DQLService DQL = new DQLService("jdbc:sqlite:dochi.db");
 
    // 테이블 생성 함수
    public void createTable() throws SQLException {
        final String SQL = "CREATE TABLE IF NOT EXISTS CW_BLOG_ATCL_LIST (   "+"\n"
                         + "  BLOG_ID     TEXT           NOT NULL,           "+"\n"
                         + "  CATE_ID     TEXT           NOT NULL,           "+"\n"
                         + "  ATCL_ID     TEXT           NOT NULL,           "+"\n"
                         + "  URL         TEXT           NOT NULL,           "+"\n"
                         + "  TITLE       TEXT,                              "+"\n"
                         + "  WORK_YN     INTEGER        DEFAULT 0,          "+"\n"
                         + "  REG_DTTM    TEXT,                              "+"\n"
                         + "  UPD_DTTM    TEXT,                              "+"\n"
                         + "  PRIMARY KEY (BLOG_ID, CATE_ID, ATCL_ID)       )";
 
        // 테이블 생성
        ResultType result = DDL.createTable("CW_BLOG_ATCL_LIST", SQL);
 
        // 테이블 생성 결과 출력
        switch( result ) {
            case SUCCESS:
                System.out.println("테이블 생성 완료.");
                break;
            case WARNING:
                System.out.println("테이블이 이미 존재합니다.");
                break;
            case FAILURE:
                System.out.println("테이블 생성 실패.");
                break;
        }
 
        // DB 연결 종료
        DDL.closeConnection();
    }
 
    // 테이블 삭제 함수
    public void dropTable() throws SQLException {
 
        // 테이블 삭제
        ResultType result = DDL.dropTable("CW_BLOG_ATCL_LIST");
 
        // 테이블 삭제 결과 출력
        switch( result ) {
            case SUCCESS:
                System.out.println("테이블 삭제 완료.");
                break;
            case WARNING:
                System.out.println("테이블이 존재하지 않습니다.");
                break;
            case FAILURE:
                System.out.println("테이블 삭제 실패.");
                break;
        }
 
        // DB 연결 종료
        DDL.closeConnection();
    }
    
    // 데이터 입력 함수
    public void insert() throws SQLException {
        // 상수 설정
        //   - Data를 저장할 객체 생성
        //     * 입력/수정/삭제/조회 에서 공통으로 사용
        final Map<String, Object> dataMap = new HashMap<String, Object>();
        dataMap.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap.put("CATE_ID"   , "/");
        dataMap.put("ATCL_ID"   , "0");
        dataMap.put("URL"       , "https://heodolf.tistory.com/134");
        dataMap.put("TITLE"     , "[JAVA] Quartz 스케줄러 만들기 (1) - 실행");
        dataMap.put("WORK_YN"   , 0);
 
        // 데이터 입력
        int inserted = DML.insertBlogArticle(dataMap);
        if( inserted >= 0 ) {
            System.out.println(String.format("데이터 입력 성공: %d건", inserted));
        } else {
            System.out.println("데이터 입력 실패");
        }
    }
    
    // 다중 데이터 입력 함수
    public void insertList() throws SQLException {
        // 상수 설정
        //   - Data를 저장할 객체 생성
        final List<Map<String, Object>> dataMapList = new ArrayList<Map<String, Object>>();
        
        final Map<String, Object> dataMap1 = new HashMap<String, Object>();
        dataMap1.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap1.put("CATE_ID"   , "/");
        dataMap1.put("ATCL_ID"   , "0");
        dataMap1.put("URL"       , "https://heodolf.tistory.com/134");
        dataMap1.put("TITLE"     , "[JAVA] Quartz 스케줄러 만들기 (1) - 실행");
        dataMap1.put("WORK_YN"   , 0);
        dataMapList.add(dataMap1);
 
        final Map<String, Object> dataMap2 = new HashMap<String, Object>();
        dataMap2.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap2.put("CATE_ID"   , "/");
        dataMap2.put("ATCL_ID"   , "1");
        dataMap2.put("URL"       , "https://heodolf.tistory.com/135");
        dataMap2.put("TITLE"     , "[JAVA] Quartz 스케줄러 만들기 (2) - Listener");
        dataMap2.put("WORK_YN"   , 0);
        dataMapList.add(dataMap2);
 
        // 데이터 입력
        int inserted = DML.insertBlogArticle(dataMapList);
        if( inserted >= 0 ) {
            System.out.println(String.format("데이터 입력 성공: %d건", inserted));
        } else {
            System.out.println("데이터 입력 실패");
        }
    }
    
    // 데이터 조회 함수
    public void select() {
        // 상수 설정
        //   - 조회할 데이터
        final Map<String, Object> dataMap = new HashMap<String, Object>();
        dataMap.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap.put("CATE_ID"   , "/");
        
        // 데이터 조회
        //   - 수집한 블로그 목록 조
        List<Map<String, Object>> result = DQL.selectBlogArticleList(dataMap);
 
        // 조회 결과 출력
        DQL.printMapList(result);
    }
    
    // 데이터 수정 함수
    public void update() throws SQLException {
        // 상수 설정
        //   - 조회할 데이터
        final Map<String, Object> dataMap = new HashMap<String, Object>();
        dataMap.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap.put("CATE_ID"   , "/");
        dataMap.put("ATCL_ID"   , "0");
 
        //   - 수정할 데이터
        final Map<String, Object> updateMap = new HashMap<String, Object>();
        updateMap.put("WORK_YN"    , 1);
        updateMap.put("TITLE"    , "원하는 컬럼을 마음대로 수정!");
 
        // 수정 결과 출력
        int updated = DML.updateBlogArticle(dataMap, updateMap);
        if( updated >= 0 ) {
            System.out.println(String.format("데이터 수정 성공: %d건", updated));
        } else {
            System.out.println("데이터 수정 실패");
        }
    }
 
    // 데이터 삭제 함수
    public void delete() throws SQLException {
        // 상수 설정
        //   - 조회할 데이터
        final Map<String, Object> dataMap = new HashMap<String, Object>();
        dataMap.put("BLOG_ID"   , "heodolf.tistory.com");
        dataMap.put("CATE_ID"   , "/");
        dataMap.put("ATCL_ID"   , "0");
 
        // 데이터 삭제
        int deleted = DML.deleteBlogArticle(dataMap);
        if( deleted >= 0 ) {
            System.out.println(String.format("데이터 삭제 성공: %d건", deleted));
        } else {
            System.out.println("데이터 삭제 실패");
        }
    }
 
    public static void main(String[] args) throws SQLException {
        App db = new App();
 
        db.dropTable();       // 테이블 삭제
        db.createTable();   // 테이블 생성
        
        db.insert();        // 데이터 입력
        db.select();        // 데이터 조회
        
        db.update();        // 데이터 수정
        db.select();        // 데이터 조회
        
        db.delete();        // 데이터 삭제
        db.select();        // 데이터 조회
        
        db.insertList();    // 다중 데이터 입력
        db.select();        // 데이터 조회
    }
}
 
cs

 

 

 

실행결과

[ 다중 데이터 삽입 실행 결과 ]

 

 

마치며

  이것으로 간단하게 다중 INSERT를 구현해보았으며, 이것으로 SQLite 사용법을 모두 정리해보았다. 사실 SQLite라기 보다는 java.sql 객체를 이용한 SQLite 사용법이 더 정확하다고 할 수 있다. 왜냐하면, 이렇게 구현한 소스에서 접속 정보나 테이블 생성 정보만 바꾸면, MySQL, MariaDB, Oracle 등에도 적용할 수 있기 때문이다.

 

  다음 포스트에서 지금까지 만든 소스를 MariaDB로 바꿔서 실행해 보도록 하겠다.

 

댓글