[jdbcDriver/mariaDB] csv 파일을 database로 가져오기

데이터베이스 project

아이디 project

비밀번호 123456

MariaDB [sample]> grant all privileges on project.* to project@'%' identified by '123456';
Query OK, 0 rows affected (0.003 sec)

 

먼저 아래 파일 다운

zipcode_seoul_utf8_type2.csv

1.08MB

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 108
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]> create table zipcode (
    -> zipcode char(7) not null,
    -> sido varchar(4) not null,
    -> gugun varchar(17),
    -> dong varchar(26) not null,
    -> ri varchar(45) not null,
    -> bunji varchar(17) not null,
    -> seq int(5) unsigned not null
    -> );
Query OK, 0 rows affected (0.013 sec)

 

csv -> db 테이블 이동....

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.SQLException;
import java.sql.Statement;

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;
		Statement stmt = null;
		
		BufferedReader br = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			
			stmt = conn.createStatement();
			
			br = new BufferedReader( new FileReader( "./zipcode_seoul_utf8_type2.csv" ) );
					
			String line = null;
			while (( line = br.readLine()) != null) {
				String[] addresses = line.split(",");
				String sql = String.format("insert into zipcode values ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",
						addresses[0], addresses[1],addresses[2],addresses[3],addresses[4],addresses[5], addresses[6]);
				stmt.executeUpdate(sql);
			}
			
			System.out.println("저장 완료");
		} 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 (stmt != null) try { stmt.close(); } catch (SQLException e) {}
			if (conn != null) try { conn.close(); } catch (SQLException e) {}
		}

	}

}

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)

MariaDB [project]> select count(*) from zipcode;
+----------+
| count(*) |
+----------+
|    17078 |
+----------+
1 row in set (0.010 sec)