[JDBC Driver/mariaDB] preparedstatement 사용해서 csv 파일을 database로 가져오기

zipcode_seoul_utf8_type2라는 이름의 csv 파일을 project라는 이름의 데이터베이스 내에 있는 zipcode 테이블에 집어넣어 보자

zipcode_seoul_utf8_type2.csv

1.08MB

 

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertZipcodeEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		String url = "jdbc:mariadb://localhost:3306/project";
		String user = "project";
		String password = "123456";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		BufferedReader br = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			
			String sql = "insert into zipcode values (?, ?, ?, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			
			br = new BufferedReader(new FileReader("./zipcode_seoul_utf8_type2.csv"));
			
			String address = null;
			while ((address = br.readLine()) != null) {
				String[] addresses = address.split(",");
				
				pstmt.setString(1, addresses[0]);
				pstmt.setString(2, addresses[1]);
				pstmt.setString(3, addresses[2]);
				pstmt.setString(4, addresses[3]);
				pstmt.setString(5, addresses[4]);
				pstmt.setString(6, addresses[5]);
				pstmt.setString(7, addresses[6]);
				
				pstmt.executeUpdate();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("[에러] " + e.getMessage());
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("[에러] " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("[에러] " + e.getMessage());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("[에러] " + e.getMessage());
		} finally {
			if (br != null) try {br.close();} catch (IOException e) {}
			if (pstmt != null) try {pstmt.close();} catch (SQLException e) {}
			if (conn != null) try {conn.close();} catch (SQLException e) {}
		}
		

	}

}

 

결과는 아래와 같다

C:\WINDOWS\System32>mysql -u project -p project
Enter password: ******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 153
Server version: 11.3.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [project]> select * from zipcode limit 3;
+---------+--------+-----------+------------+---------------------+----------------+-----+
| zipcode | sido   | gugun     | dong       | ri                  | bunji          | seq |
+---------+--------+-----------+------------+---------------------+----------------+-----+
| 135-806 | 서울   | 강남구    | 개포1동    | 경남아파트          |                |   1 |
| 135-807 | 서울   | 강남구    | 개포1동    | 우성3차아파트       | (1∼6동)        |   2 |
| 135-806 | 서울   | 강남구    | 개포1동    | 우성9차아파트       | (901∼902동)    |   3 |
+---------+--------+-----------+------------+---------------------+----------------+-----+
3 rows in set (0.000 sec)