Read CSV File and store data to MySQL

 

 

Read CSV File and store the data to MySQL in database table.
CSV File에서 가지고 온 데이터를 MySQL DB에 저장하기

 

 

Use preparedStatement object to insert data to MySQL.

 

 

You can use Statement object to insert

 

Statement statement = conn.createStatement();

 

 

PreparedStatement is more secure so, it's preferred.

 

PreparedStatement st = conn.prepareStatement(query);

 

*SELECT query createStatement() 

 

setString(int, String)

setCharacter(int, Reader, int)

setInt(int, int)

setLong(int, long)

setDouble(int, double)

setFloat(int, float)

setTime(int, Time)

 

 

 

 

Example Code

 

package customList;
 
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.ArrayList;
 
public class Main {
 
    public static void main(String[] args) {
        CustomManager manager = CustomManager.createCustomManager();
 
        try {
            manager.readFile("customList.csv");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
 
        ///////다른 class 에서 data 가지고 오기 //////
        ArrayList<CustomInfo> list = manager.getList();
        //System.out.println(list.get(0)); // 0번째
 
        /////////////// MYSQL //////////////////////
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
 
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); //com.mysql.jdbc.Driver 에서 com.mysql.cj.jdbc.Driver 로 변경하니까 됨.
 
            String url = "jdbc:mysql://localhost/customers?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true";
 
            //create connection to DB
            conn = DriverManager.getConnection(url, "root", "1234");
 
            //연결상태 보여주는 toString()
            System.out.println(conn.toString());
 
            int size = list.size();
 
            for (int i = 0; i < size; i++) {
                CustomInfo cusList = list.get(i);
                String sql = "insert into customers values(?,?,?,?,?,?)"; //PreparedStatement 는 변수값대신 '?' 사용
                //INSERT
                pstmt = conn.prepareStatement(sql);
 
                pstmt.setInt(1, cusList.getId()); //'?' 의 순서를 1부터 차례대로 숫자로 표시해 준다.
                pstmt.setString(2, cusList.getName());
                pstmt.setString(3, cusList.getPhone());
                pstmt.setString(4, cusList.getEmail());
                pstmt.setString(5, cusList.getAccountNum());
                pstmt.setString(6, cusList.getAddress());
                pstmt.executeUpdate();
            }
 
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close(); //사용한 부분은 꼭 닫아주자 
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
 
    }
 
}
 

 

 

 

CSV File

 

 

 

 

stored to Mysql 

 

 

 

 

 

 

 

Reference

https://cocodo.tistory.com/11

https://alvinalexander.com/blog/post/jdbc/create-use-preparedstatement/

https://victorydntmd.tistory.com/145

http://www.hanyang.or.kr/board_JpEa93/4589?ckattempt=1