지구정복

[JAVA] 12/03 | JDBC(코딩스타일, 드라이버로딩, 데이터베이스 커넥션, Statement사용, PreparedStatement사용, metadata가져오기), 우편번호 검색 프로그램만들기(JDBC사용) 본문

데이터 엔지니어링 정복/JAVA & JSP

[JAVA] 12/03 | JDBC(코딩스타일, 드라이버로딩, 데이터베이스 커넥션, Statement사용, PreparedStatement사용, metadata가져오기), 우편번호 검색 프로그램만들기(JDBC사용)

eeaarrtthh 2020. 12. 3. 17:16
728x90
반응형

배울내용

java -> 데이터를 저장
	임시
		변수/상수
		=>자료구조
	영구
		로컬 -파일
		원격 -데이터베이스
		Java Program
		Client

JDBC(Java Database Connectivity)

자바애플리케이션 -> JDBC API -> JDBC드라이버(라이브러리) -> 데이터베이스
JDBC드라이버는 데이터베이스 제작업체에서 제공한다.

 

 

아래 링크를 통해서 마리아디비의 jdbc드라이버를 다운로드받는다.

downloads.mariadb.com/Connectors/java/connector-java-2.7.1/

 

Connectors/java/connector-java-2.7.1/ - MariaDB

 

downloads.mariadb.com

다운로드받은 .jar파일을 이클립스 워크스페이션 APIs폴더에 집어넣는다.

 

 

 

5.3 JDBC 프로그래밍의 코딩 스타일

1. JDBC 드라이버 로딩

2. 데이터베이스 커넥션 구함

3. 쿼리 실행을 위한 Statement 객체 생성

4. 쿼리 실행

5. 쿼리 실행 결과 사용

6. Statement 종료

7. 데이터베이스 커넥션 종료

 

1. JDBC 드라이버 로딩

[새 프로젝트 생성] -> Next -> 

아래처럼 드라이버 로딩을 한다.

여기서 Class.forName()을 알아보자.

java.lang.Class 는 클래스의 정보를 얻기위한 클래스이다.

forName()은 물리적인 클래스 파일명을 인자로 넣어주면 이에 해당하는 클래스를 반환해준다.

클래스를 조사하기 위한 클래스이다.

 

즉, 지정한 클래스 정보를 담고 있는 Class 인스턴스를 구해주는 기능만을 제공한다.

JDBC 드라이버에 해당하는 클래스들은

Class.forName() 메서드를 통해서 로딩될 때 자동으로 JDBC 드라이버로 등록된다.

package JDBCEx01;

public class JDBCEx01 {

	public static void main(String[] args) {
		
		System.out.println("시작");
		try {
			//익셉션으로 가지않으면 동적 클래스 로딩
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩이 완료되었습니다.");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러"+e.getMessage());
			
		}
		//연결을 하기 위해서는 url(ip, port, database) / id / password가 필요하다.
		System.out.println("끝");
	}
}
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
시작
드라이버 로딩이 완료되었습니다.
끝

2. 데이터베이스 커넥션

주석에서 2번 부분이다.

package JDBCEx01;

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

public class JDBCEx02 {

	public static void main(String[] args) {
		
		System.out.println("시작");
		
		//1. 드라이버로딩
		try {
			//익셉션으로 가지않으면 동적 클래스 로딩
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩이 완료되었습니다.");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러"+e.getMessage());
			
		}
		
		//2. 데이터베이스 연결 및 커넥션구함
		//연결을 하기 위해서는 url(ip, port, database) / id / password가 필요하다.
		//url은 localhost는 127.0.0.1을 써도된다. 3307은 마리아디비 포트번호이다.
		//마리아디비의 아이디와 패스워드를 정확히 적는다.
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: "+e.getMessage());
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
		}
		
		System.out.println("끝");
	}
}

시작
드라이버 로딩이 완료되었습니다.
연결 성공
끝

 

위 코드를 트라이캐치부분을 하나로 묶어서 간단하게 만들자.

package JDBCEx01;

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

public class JDBCEx03 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
		}

	}
}
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공

 

3. Statement를 사용한 쿼리 실행

 

ResultSet executeQuery(String query) : SELECT 쿼리 실행

int executeUpdate(String query) : INSERT, UPDATE, DELETE 쿼리실행

 

-int executeUpdate(String query)

먼저 마리아디비에 접속해서 DEPT2 테이블을 DEPT 테이블을 복사해서 만든다.

(마리아디비 사용법과 데이터베이스자료는 [SQL] 카테고리 참고)

create table dept2 as select * from dept;

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
+--------+------------+----------+

그럼 이제 이클립스로 돌아와서 스테이트객체를 만들고 쿼리를 실행해보자.

아래 코드 중 주석된 부분이 스테이트객체 생성, 쿼리 작성, 쿼리 실행하는 부분이다.

package JDBCEx01;

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

public class JDBCEx04 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		Statement stmt = null;		//statement 객체 생성
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			//쿼리작성
			String sql = "insert into dept2 values(60, '생산', '부산')";
			
			
			//쿼리 실행
			stmt = conn.createStatement();
			int result = stmt.executeUpdate(sql);
			System.out.println("result: "+ result);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {} //stmt close
		}
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
result: 1

그럼 이제 마리아디비에 돌아와서 insert문이 제대로 실행되었는지 확인한다.

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
+--------+------------+----------+

60번 부서가 이상없이 삽입된 것을 알 수 있다.

 

이번에는 자바에서 변수를 선언해서 쿼리안에 변수를 집어넣어보자.

String deptno = "91";
String dname = "개발";
String loc = "원주";
			
String sql = "insert into dept2 values("+deptno+",'"+dname+"','"+loc+"')";


SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     91 | 개발       | 원주     |
+--------+------------+----------+

위 처럼 사용하는데 이렇게 되면 너무 복잡하고 변수가 많아질 경우 사용하기 힘들다.

따라서 아래 방법을 사용한다.

String sql = String.format("insert into dept2 values(%s,'%s', '%s')", deptno, dname, loc);


SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     91 | 개발       | 원주     |
|     92 | 개발       | 원주     |
+--------+------------+----------+

 

이번에는 update와 delete 문을 사용해보자.

91번 부서의 위치를 서울로 바꿔보자.

String sql = String.format("update dept2 set loc='서울' where deptno=91");


SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     91 | 개발       | 서울     |
|     92 | 개발       | 원주     |
+--------+------------+----------+

update문도 변수를 이용할 수 있다. 대전으로 바꿔보자.

String sql = String.format("update dept2 set loc='%s' where deptno=%s", "대전", "91");

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     91 | 개발       | 대전     |
|     92 | 개발       | 원주     |
+--------+------------+----------+

이번에는 91번 부서를 삭제하는 sql을 만들고 실행해보자.

String sql = String.format("delete from dept2 where deptno=91");

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     92 | 개발       | 원주     |
+--------+------------+----------+

91번 부서가 삭제된 것을 알 수 있다.

 

이번에는 DDL 구문을 사용해보자.

dept3라는 테이블을 자바를 통해서 만들어보자.

String sql = String.format(
	"create table dept3("
	+ "deptno int(2), "
	+ "dname varchar(14), "
	+ "loc varchar(13)"
	+ ")");
    
    
show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
| dept2            |
| dept3            |
| emp              |
| salgrade         |
+------------------+

desc dept3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | YES  |     | NULL    |       |
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

이번에는 StringBuffer를 이용해서 sql문을 실행해보자.

이를 이용해서 dept4테이블을 만들어보자.

//쿼리작성
StringBuffer sql = new StringBuffer();
sql.append("create table dept4(");
sql.append("deptno int(2),");
sql.append("dname varchar(14),");
sql.append("loc varchar(13)");
sql.append(")");
			
//쿼리 실행
stmt = conn.createStatement();
int result = stmt.executeUpdate(sql.toString()); //toString()으로 바꾸어야한다.



show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
| dept2            |
| dept3            |
| dept4            |
| emp              |
| salgrade         |
+------------------+

desc dept4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | YES  |     | NULL    |       |
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

 

-ResultSet executeQuery(String query)

p32~34

next() 메소드가 있어서 next()로 테이블의 한 행씩 읽는다.

만약 next()가 없으면 검색이 종료된다.

 

이때 하나의 행만 출력하고 싶으면 if문을 사용하고

여러개의 행을 출력하고 싶으면 while문을 사용한다.

package JDBCEx01;

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

public class JDBCEx08 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		Statement stmt = null;		
		ResultSet rs = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			//쿼리작성
			String sql = String.format("select deptno, dname, loc from dept");
			
			//쿼리 실행
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			//여러 행 데이터 읽기
			while( rs.next() ) {
				//System.out.println("결과처리");
				System.out.println( rs.getString("deptno"));
				System.out.println( rs.getString("dname"));
				System.out.println( rs.getString("loc"));
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(rs!=null) try {rs.close();} catch(SQLException e) {} 
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {} 
		}
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
50
영업
서울

만약 while문 대신 if문을 사용하면 맨 위에 하나의 행만 출력한다.

 

이번에는 출력할 때 컬러명 대신 순서값을 사용할 수 있다.

while( rs.next() ) {
	System.out.println( rs.getString(1));
	System.out.println( rs.getString(2));
	System.out.println( rs.getString(3));
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
50
영업
서울

이번에는 10번 부서에 대한 사원번호, 사원명, 직책, 급여를 출력해보자.

//쿼리작성
String sql = String.format("select empno, ename, job, sal from emp where deptno = 10");
			
//쿼리 실행
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
			
//여러 행 데이터 읽기
while( rs.next() ) {
	System.out.printf( "%s %s %s %s%n", rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
}
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
7839 KING PRESIDENT 5000.00
7934 MILLER CLERK 1300.00

이번에는 연봉까지 출력해보자

String sql = String.format("select empno, ename, job, sal, sal*12+ifnull(comm,0) annual from emp where deptno = 10");

while( rs.next() ) {
	System.out.printf( "%s %s %s %s%n", rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString("annual"));
}

드라이버 로딩 성공
연결 성공
7839 KING PRESIDENT 5000.00
7934 MILLER CLERK 1300.00

 

이번에는 10부서의 사원번호, 사원명, 직책, 입사일(yyyy/mm/dd)를 출력해보자.

이때 String.format에서는 %를 한 번 더 써주어야 한다.

String sql = String.format("select empno, ename, job, date_format(hiredate, '%%Y/%%m/%%d') hiredate from emp where deptno = 10");

stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

while( rs.next() ) {
    System.out.printf( "%s %s %s %s%n", rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
}


ㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
7839 KING PRESIDENT 2011/11/17
7934 MILLER CLERK 2012/01/23

 

5.11 PreparedStatement를 사용한 쿼리 실행

java. sql. PreparedStatement는 java. sql. Statemen와 동일한 기능을 제공한다.

차이점이 있다면 PreparedStatement는 SQL 쿼리의 틀을 미리 생성해 놓고 값을 나중에 지정한다는 것이다. 

 

PreparedStatement를 사용하는 순서는 다음과 같다.


1. Connection. prepareStatement() 메서드를 사용하여 PreparedStatement 생성
2. PreparedSta tement의 set 메서드를 사용하여 필요한 값 지정
3. PreparedSta tement의 executeQuery() 또는 executeUpdate() 메서드를 사용하여 쿼리를 실행
4. finally 블록에서 사용한 PreparedStatement를 닫음(close() 메서드 실행)


PreparedStatement를 생성할 때에는 실행할 쿼리를 미리 입력하는데

이때 다음과 같이 값 부분을 물음표('?')로 대치한 쿼리를 사용한다

PreparedStatement pstmt = null;
pstmt = conn.prepareStatement(
"insert into MEMBER (MEMBERID, NAME，EMAIL values (?, ?, ?)");

 

실제로 사용해보자.

package JDBCEx01;

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

public class JDBCEx10 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		PreparedStatement pstmt = null; 	//preparedstatement 객체 생성
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			String sql = "insert into dept2 values( ?, ?, ?)";   //미리 값을 ?로 만들어놓는다.
			pstmt = conn.prepareStatement(sql);		//위 sql문을 실행
			pstmt.setString(1, "92");	//1이 물음표의 순서고 그다음 넣을 값
			pstmt.setString(2, "개발");
			pstmt.setString(3, "대전");	
			
			int result = pstmt.executeUpdate(); //sql이 들어가면 안된다.
			System.out.println("결과: "+ result);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
		}

	}
}

select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
|     60 | 생산       | 부산     |
|     92 | 개발       | 원주     |
|     92 | 개발       | 대전     |    <-- 데이터 삽입 성공
+--------+------------+----------+

 

실습

emp테이블로 emp2 테이블의 구조만 만든다.

1. create table emp2 as select * from emp where 1 != 1;

2. 프로그램에서 emp 테이블의 데이터를 읽은 다음 insert해서 emp2 테이블에 집어넣어 보자.

3. 자바 클래스 이름은 'CopyTableEx01'로 만든다.

package JDBCEx01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CopyTableEx01 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		Statement stmt = null;
		Statement stmt2 = null;
		ResultSet rs = null;
		
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			String sql = String.format("select * from emp");

			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			while( rs.next() ) {
				String empno = rs.getString(1);
				String ename = rs.getString(2);
				String job = rs.getString(3);
				String mgr = rs.getString(4);
				String hiredate = rs.getString(5);
				String sal = rs.getString(6);
				String comm = rs.getString(7);
				String deptno = rs.getString(8);
				
				String insert = String.format("insert into emp2 values(%s,'%s','%s',%s,'%s',%s,%s,%s)", empno, ename, job, mgr, hiredate, sal, comm, deptno);
				stmt2 = conn.createStatement();
				int result = stmt.executeUpdate(insert);
				System.out.println("result : " +result);
			}

			
		} catch (ClassNotFoundException 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 (NullPointerException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		}finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(rs!=null) try {rs.close();} catch(SQLException e) {}
			if(stmt2!=null) try {stmt2.close();} catch(SQLException e) {}
		}
	}
}

select * from emp2;
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |
+-------+--------+-----------+------+------------+---------+--------+--------+

 

이번에는 preparedstate로 사용해보자.

package JDBCEx01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CopyTableEx02 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			String sql = String.format("select * from emp");
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			String sql2 = "insert into emp2 values(?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql2);
			int sum = 0;
			
			while( rs.next() ) {
				for(int i=1; i<9; i++) {
					pstmt.setString(i, rs.getString(i));
				}
				int result = pstmt.executeUpdate();
				sum += result;
			}
			System.out.println("result: "+ sum);
			
	
		} catch (ClassNotFoundException 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 (NullPointerException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		}finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(rs!=null) try {rs.close();} catch(SQLException e) {}
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
		}

	}
}

보통 PreparedStatement가 속도가 빨라서 더 많이 사용한다.

 

 

정리

java.sql.* 패키지소속
	Connection					-접속을 위한 클래스
	Statement/PreparedStatement	                -sql문 처리
	ResultSet					-select의 처리

 

MetaData가져오기

주변정보를 의미하고 여기에는 
Database와 ResultSet에 대한 metadata가 있다.

 

-데이터베이스의 metadata 가져오기

package JDBCEx01;

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

public class JDBCEx11 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			//데이터베이스에 대한 정보 가져오기
			DatabaseMetaData dmd = conn.getMetaData();
			
			System.out.println(dmd.getDatabaseProductName());
			System.out.println(dmd.getDatabaseProductVersion());
            
			System.out.println(dmd.getDriverName());
			System.out.println(dmd.getDriverVersion());
			
			System.out.println(dmd.getURL());
			System.out.println(dmd.getUserName());
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
		}

	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
MariaDB
10.5.8-MariaDB
MariaDB Connector/J
2.7.1
jdbc:mysql://localhost:3307/sample
root

 

-테이블에 대한 metadata 가져오기

아래의 메타데이터를 이용해서 데이터베이스의 desc 명령어도 만들 수 있다.

package JDBCEx01;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class JDBCEx12 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			//쿼리에 대한 메타데이터 얻어내기
			String sql = "select deptno, dname, loc from dept";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			System.out.println(rsmd.getColumnCount());		//컬럼의 개수
			
			for(int i=1; i<=rsmd.getColumnCount(); i++) {
				System.out.println("---"+i+"컬럼---");
				System.out.println(rsmd.getColumnName(i));		//컬럼명 출력
				System.out.println(rsmd.getColumnTypeName(i));	//컬럼의 자료형 출력
				System.out.println(rsmd.getPrecision(i));		//컬럼 내부 단위(bit)	
				System.out.println(rsmd.getScale(i));			//
				System.out.println(rsmd.getColumnDisplaySize(i));	//우리가 알 수있는 단위(byte)
				System.out.println(rsmd.isNullable(i));			//null 허용 여부(0:false, 1:true)
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {} 
			if(rs!=null) try {rs.close();} catch(SQLException e) {} 
		}

	}
}
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
3
---1컬럼---
DEPTNO
INTEGER
2
0
2
0
---2컬럼---
DNAME
VARCHAR
56
0
14
1
---3컬럼---
LOC
VARCHAR
52
0
13
1

 

실제로 자바에서 desc 명령어를 출력해보자.

package JDBCEx01;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class JDBCEx12 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 성공");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 성공");
			
			//쿼리에 대한 메타데이터 얻어내기
			String sql = "select deptno, dname, loc from dept";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			for(int i=1; i<=rsmd.getColumnCount(); i++) {
				System.out.printf("%-10s %s(%s) %-13s\n",
						rsmd.getColumnName(i), 
						rsmd.getColumnTypeName(i), 
						rsmd.getColumnDisplaySize(i),
						(rsmd.isNullable(i) == 1 ? "YES":"NO") );
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("에러: " + e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {} 
			if(rs!=null) try {rs.close();} catch(SQLException e) {} 
		}

	}
}
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
드라이버 로딩 성공
연결 성공
DEPTNO     INTEGER(2)         NO
DNAME      VARCHAR(14)        YES
LOC        VARCHAR(13)        YES

 

실습과제

파일(우편번호)를 불러와서 테이블을 만들고 자바에서 출력해보자.

먼저 우편번호 테이블 마리아디비에서 아래와 같이 zipcode 테이블을 만든다.

CREATE TABLE zipcode(
zipcode char(7) not null,
sido varchar(4) not null,
gugun varchar(17), 
dong varchar(16) not null,
ri varchar(45),
bunji varchar(17),
seq int(5) unsigned not null,
constraint zipcode_pk primary key (seq)
);

그리고 아래의 우편번호 파일(.csv)을 이클립스 자바프로젝트 내부안에 드래그해서 넣는다.

zipcode_seoul_euckr_type2.csv
0.86MB

 

 

이제 csv파일의 주소를 데이터베이스 zipcode 테이블에 넣어보자.

아래 코드를 실행하면된다.

package SearchZipcode;

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

public class InsertZipcode {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		BufferedReader bis = null;
		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
	
		try {
			//zipcode 파일 불러오기
			bis = new BufferedReader(
					new FileReader("./zipcode_seoul_euckr_type2.csv"));
			
			//데이터베이스 접속
			Class.forName("org.mariadb.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			
			//PreparedStatement로 insert문 미리 준비
			String insertSql = "insert into zipcode values(?,?,?,?,?,?,?)";
			pstmt1 = conn.prepareStatement(insertSql);
			
			//zipcode 파일 내용 읽기 + 데이터베이스에 데이터 집어넣기
			int sum = 0;
			String data = null;
			while( (data = bis.readLine()) != null ) {
				String[] datas = data.split(",");
				for(int i=1; i<=7; i++) {
					pstmt1.setString(i, datas[i-1]);
				}
				int result = pstmt1.executeUpdate();
				sum += result;
			}
			System.out.println("실행한 행의 개수: "+sum);
			
			//select문으로 제대로 입력되었는지 위에 5개 행만 출력
			String selectSql = "select * from zipcode limit 5";
			pstmt2 = conn.prepareStatement(selectSql);
			rs = pstmt2.executeQuery();
			int a = 1;
			while( a <= 5 ) {
				rs.next();
				System.out.printf("%s %s %s %s %s %s %s\n", 
						rs.getString(1),
						rs.getString(2),
						rs.getString(3),
						rs.getString(4),
						rs.getString(5),
						rs.getString(6),
						rs.getString(7) );
				a++;
			}
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(bis!=null) try {bis.close();} catch(IOException e) {} 
			if(conn!=null) try {conn.close();} catch(SQLException e) {} 
			if(pstmt1!=null) try {pstmt1.close();} catch(SQLException e) {} 
			if(pstmt2!=null) try {pstmt2.close();} catch(SQLException e) {}
			if(rs!=null) try {rs.close();} catch(SQLException e) {} 
		}
		
	}	
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
실행한 행의 개수: 18308
135-806 서울 강남구 개포1동 경남아파트  1
135-807 서울 강남구 개포1동 우성3차아파트 (1∼6동) 2
135-806 서울 강남구 개포1동 우성9차아파트 (901∼902동) 3
135-770 서울 강남구 개포1동 주공아파트 (1∼16동) 4
135-805 서울 강남구 개포1동 주공아파트 (17∼40동) 5

데이터베이스에도 데이터들이 제대로 들어갔는지 확인하기위해 위 5개행과 아래 5개행만 출력해본다.

select * from zipcode limit 5;
+---------+------+--------+---------+---------------+--------------+-----+
| 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 |
| 135-770 | 서울 | 강남구 | 개포1동 | 주공아파트    | (1∼16동)    |   4 |
| 135-805 | 서울 | 강남구 | 개포1동 | 주공아파트    | (17∼40동)   |   5 |
+---------+------+--------+---------+---------------+--------------+-----+

select * from zipcode order by seq desc limit 5;
+---------+------+--------+--------+---------------+--------------+-------+
| zipcode | sido | gugun  | dong   | ri            | bunji        | seq   |
+---------+------+--------+--------+---------------+--------------+-------+
| 486-850 | 경기 | 연천군 | 청산면 |               |              | 30841 |
| 486-852 | 경기 | 연천군 | 청산면 | 초성리        |              | 30840 |
| 480-799 | 경기 | 연천군 | 청산면 | 초성리 사서함 | 130-(17∼20) | 30839 |
| 487-809 | 경기 | 연천군 | 청산면 | 초성리 사서함 | 118-(13)     | 30838 |
| 482-839 | 경기 | 연천군 | 청산면 | 초성리 사서함 | 94-(17)      | 30837 |
+---------+------+--------+--------+---------------+--------------+-------+

 

이번에는 특정 동을 출력하는 우편번호 검색 프로그램을 만들자.

동을 출력하는 쿼리는 아래와 같다.

select * from zipcode
where dong like '개포%' or
ri like '개포%';

위 쿼리가 출력이 되도록 자바프로그래밍 해보자.

클래스의 이름은 SearchZipcode이고, 

출력결과는 아래와 같다.

동이름 입력 : 개포동
2자리가 아닙니다. 프로그램을 종료합니다.

동이름 입력 : 1f
한글이 아닙니다. 프로그램을 종료합니다.

동이름 입력 : 개포
(개포동인 주소 모두 출력)

동이름 입력 : 강남
(강남동인 주소 모두 출력)

동이름 입력 : 서초
(서초동인 주소 모두 출력)

동이름 입력 : exit
프로그램을 종료합니다.

 

코드는 아래와 같다.

package SearchZipcode;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class SearchZipcode {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3307/sample";
		String user = "root";
		String password = "!123456";
		
		Scanner sc = new Scanner(System.in);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		while(true) {
			//입력값 받기
			System.out.printf("동이름을 검색해주세요. (ex: 개포) / (종료는 exit) : ");
			String dong = sc.nextLine();
			
			//입력값 검사(종료, 자릿수, 한글)
			if ( ( dong.hashCode() == "exit".hashCode() ) && dong.equals("exit") ) {
				System.out.println("프로그램을 종료합니다.");
				System.exit(0);
			} else if( dong.length() != 2 ) {
				System.out.println("2자리가 아닙니다. 프로그램을 종료합니다.");
				System.exit(0);
			} else if ( !dong.matches(".*[ㄱ-ㅎ ㅏ-ㅣ 가-힣]+.*") ) {
				System.out.println("한글이 아닙니다. 프로그램을 종료합니다.");
				System.exit(0);
			}
			System.out.println();
			
			try {
				//데이터베이스 접속
				Class.forName("org.mariadb.jdbc.Driver");
				conn = DriverManager.getConnection(url, user, password);
				
				//쿼리 실행
				String selectSql = "select * from zipcode where dong like \'"+dong+"%\' or ri like \'%"+dong+"%\'" ;
				stmt = conn.createStatement();
				rs = stmt.executeQuery(selectSql);
				
				//쿼리 출력
				while( rs.next() ) {
					System.out.printf("%s %s %s %s %s %s %s\n",
							rs.getString(1),
							rs.getString(2),
							rs.getString(3),
							rs.getString(4),
							rs.getString(5),
							rs.getString(6),
							rs.getString(7) );
				}
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if(conn !=null) try {conn.close();} catch(SQLException e) {}
				if(stmt !=null) try {stmt.close();} catch(SQLException e) {} 
				if(rs !=null) try {rs.close();} catch(SQLException e) {} 
			}
		}
	}
}

PreparedStatement를 사용하면 아래와 같다.

import java.sql.PreparedStatement;
    //생략
try {
    //생략
    String selectSql = "select * from zipcode where dong like ? or ri like ?";
    pstmt = conn.prepareStatement(selectSql);
    pstmt.setString(1, dong+"%");
    pstmt.setString(2, "%"+dong+"%");
				
    rs = pstmt.executeQuery();
    //생략
} finally {
    if(pstmt !=null) try {pstmt.close();} catch(SQLException e) {} 
}

 

728x90
반응형
저작자표시 동일조건

'데이터 엔지니어링 정복 > JAVA & JSP' 카테고리의 다른 글

[JAVA] 12/07 | 항상 대기중인 서버, C/S이용하기(구구단, 우편번호검색기), 스레드 병렬처리  (0) 2020.12.08
[JAVA] 12/04 | DAO/DTO를 이용한 우편번호검색 프로그램, 네트워크기초(기본개념, 특정url 크롤링, TCP네트워킹), TCP네트워크를 이용한 구구단 출력  (0) 2020.12.04
[JAVA] 11/26 | 멀티스레드, 작업스레드, 스레드상태, 스레드를 이용한 구구단, 스레드 이름, 스레드 우선순위, 스레드 상태제어, 데몬스레드, 스레드 동기화,스레드그룹, 공유객체, 숫자야구게임..  (0) 2020.11.27
[JAVA] 11/25 | 콘솔입출력, 문자변환보조스트림, 기본타입입출력보조스트림, 객체입출력보조스트림, 버퍼, 파일채널, 파일복사, excel우편번호검색하기  (0) 2020.11.27
[JAVA] 11/24 (2)| 보조스트림,우편번호 주소 검색프로그램  (0) 2020.11.26
'데이터 엔지니어링 정복/JAVA & JSP' Related Articles more
Comments