[MySQL] CSV파일을 읽어서 DB저장하기 + Spring

CSV형식의 파일을 읽어와서 데이터베이스에 저장해보자

mysql 과 spring이 연동되어있다는 가정 하에 진행한다 !

 

일단 의존성을 추가하자

 

build.gradle

// Apache Commons CSV
    implementation 'org.apache.commons:commons-csv:1.8'

 

1. DatabaseUtil 

package com.tago.api.infra.csv.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseUtil {
    private static final String JDBC_URL = System.getenv("DATABASE_URL");
    private static final String USER = System.getenv("DATABASE_USERNAME");
    private static final String PASSWORD = System.getenv("DATABASE_PASSWORD");

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Failed to load JDBC driver", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
    }
}

데이터베이스 연결을 설정하고, 연결 객체를 반환하는 getConnection메서드를 작성한다.

Class.forName("com.mysql.cj.jdbc.Driver");   -> MySQL JDBC드라이버를 로드한다.

 

2. Inserter메서드 작성

package com.tago.api.infra.csv.inserter;


import com.tago.api.infra.csv.util.DatabaseUtil;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;

import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class PlaceDataInserter {
    public static void insertPlaceData(){

        String insertQuery = "INSERT INTO place (id, content_id, type_id, title, address, created_time, modified_time, img_url, mapx, mapy, visit, overview, homepage, telephone, rest_date, open_time, parking) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		//쿼리 정의. 넣고 싶은 데이터 칼럼 추가 
        try (
                Connection connection = DatabaseUtil.getConnection();
                Reader in = new FileReader("/파일경로.csv");
                PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
        ) {

            Iterable<CSVRecord> records = CSVFormat.EXCEL.withHeader().parse(in);
            //Apache Commons CSV 라이브러리를 사용하여 CSV 파일을 파싱
            
            Long id = 0L; 

            connection.setAutoCommit(false);

            try{

                for (CSVRecord record : records) {
                    id++;
                    preparedStatement.setLong(1, id);
                    preparedStatement.setLong(2, Long.parseLong(record.get("id")));
                    preparedStatement.setLong(3, Long.parseLong(record.get("type_id")));
                    preparedStatement.setString(4, record.get("title"));
                    preparedStatement.setString(5, record.get("address"));
                    preparedStatement.setString(6, record.get("created_time"));
                    preparedStatement.setString(7, record.get("modified_time"));
                    preparedStatement.setString(8, record.get("img_url"));
                    preparedStatement.setDouble(9, Double.parseDouble(record.get("mapx")));
                    preparedStatement.setDouble(10, Double.parseDouble(record.get("mapy")));
                    preparedStatement.setDouble(11, Double.parseDouble(record.get("visit")));
                    preparedStatement.setString(12, record.get("overview"));
                    preparedStatement.setString(13, record.get("homepage"));
                    preparedStatement.setString(14, record.get("telephone"));
                    preparedStatement.setString(15, record.get("rest_date"));
                    preparedStatement.setString(16, record.get("open_time"));
                    preparedStatement.setString(17, record.get("parking"));

                    preparedStatement.addBatch(); //현재 설정된 값들을 배치에 추가. 
                }
                preparedStatement.executeBatch();

                connection.commit(); //트랜잭션을 커밋하여 데이터베이스에 변경사항을 반영
        } catch (Exception e) {
                connection.rollback(); //트랜잭션을 롤백하여 변경사항을 취소
                e.printStackTrace();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

}

이를 변형해서 넣고 싶은 데이터를 넣을 수 있다. 

 

3. CSVToMySQL

package com.tago.api.infra.csv;

import com.tago.api.infra.csv.inserter.*;
import com.tago.domain.place.domain.PlaceTag;

public class CSVToMySQL {

    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");

        PlaceTagDataInserter.insertPlaceTagData();
        PlaceDataInserter.insertPlaceData();
        CourseDataInserter.insertCourseData();
        CoursePlaceDataInserter.insertCoursePlaceData();
        CourseTagDataInserter.insertCourseTagData();

    }

}

이제 이 파일을 실행하면 구현한 메서드가 실행되면서 데이터가 저장된다.

 

짜잔