자바 65강. 자바 DB 연동 SELECT

새로운 작업공간을 만들어

자바와 데이터베이스를 연동시켜보자.

 

 

 

 

 

 

 

 

 

다운로드한 JDBC 라이브러리의

사용법을 알아보기 위해

문서를 참고해보자.

 

https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html

OracleDriver (Oracle ® Database JDBC API Reference)

 

 

문서를 내려 보다 보면

getConnection() 부분이 있다.

 

DB와 연결하는 부분에 대한 설명이다.

 

user scott wit password tiger to a database with SID xe through port 1521 of host localhost using the Thin driver.

Connection conn = DriverManager.getConnection
  ("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");

conn이 ByteStream, 소켓!

package site.metacoding.db;

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

public class DBEx01 {

	public static void main(String[] args) {
		// 통신이니까 try-catch
		try {
			// 1. connection 연결(세션생성) port, ip, id, password, protocol★
			Connection conn = DriverManager.getConnection
					  ("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
			System.out.println("DB 연결완료");
			
			// 2. 버퍼 달아서 통신(ALL:SELECT * FROM emp) 프로토콜에 맞게!
			// 라이브러리를 사용하면 ALL: 안붙여도 됨
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}

 

 

!주의!

포트번호 잘못 적으면 Connection refused 오류 발생

IP 잘못적으면 Connection timed out 오류 발생

아이디나 비밀번호를 잘못적으면

invalid username/password; logon denied 오류 발생

 

 

포트번호 0~65535를 다 열려있는 상태를

DMZ 개방상태라고 한다.

모든 포트 허용하는 중인 것이다.

 

이를 방지하기 위해

OS가 방화벽으로 컴퓨터를 감싸 놓고

모든 포트를 차단해놓는다.

 

오라클을 설치할 때 동의를 했기 때문에

방화벽에서 1521번과 8080 포트가 열려있다.

 

 

DB와 자바를 연동한 후

자바에서 쿼리문을 적을 때 세미콜론은 필요 없다.

 

ResultSet은 DB에서 받아온 데이터 결과이다.

 

ResultSet에는 가리키는 방향(커서)이 있다.

항상 제일 위에 컬럼을 가리킨다.

 

디폴트 상태에서는 데이터를 읽어내지 못하기 때문에

커서를 한 칸씩 내려줘야 한다.

 

그런데 데이터가 없을 수도 있는데

무조건 커서를 내려야 할까?

 

그래서 ResultSet은 boolean을 리턴한다.

// 쿼리의 결과가 몇개가 나올지 모르기 때문에 while을 돌며 출력
while(rs.next()) {
	System.out.println("empno : " + rs.getInt("empno")); // getInt("컬럼명")
	System.out.println("ename : " + rs.getString("ename"));
	System.out.println("================");
}

 

package site.metacoding.db;

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

public class DBEx01 {

	public static void main(String[] args) {
		// 통신이니까 try-catch
		try {
			// 1. connection 연결(세션생성) port, ip, id, password, protocol★
			Connection conn = DriverManager.getConnection
					  ("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
			System.out.println("DB 연결완료");
			
			// 2. 버퍼 달아서 통신(ALL:SELECT * FROM emp) 프로토콜에 맞게!
			// 라이브러리를 사용하면 ALL: 안붙여도 됨! 
			// 프로토콜이 적용된 버퍼 PrepareStatement
			String sql = "SELECT empno, ename FROM emp";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			// flush 전송해서 rs로 응답받기
			ResultSet rs = pstmt.executeQuery();	// SELECT
			// pstmt.executeUpdate();	// INSERT, UPDATE, DELETE
			// System.out.println(rs.next()); // 커서 한 칸 내리기
		
			// 쿼리의 결과가 몇개가 나올지 모르기 때문에 while을 돌며 출력
			while(rs.next()) {
				System.out.println("empno : " + rs.getInt("empno")); // getInt("컬럼명")
				System.out.println("ename : " + rs.getString("ename"));
				System.out.println("================");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}

prepareStatement는 쿼리문 안에 변수를 넣을 수 없다.

아래 코드는 실패!

String sql = "SELECT * FROM userTbl WHERE username " + username + " AND password = " + password + "";

 

그래서 변수 자리에 물음표(?)를 넣어서 사용한다.

그리고 setString, setInt 등 타입에 맞게 메서드를 사용해

1번째 물음표 자리부터 set 해준다.

package site.metacoding.db;

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

// PrepareStatement 변수 바인딩 하기
public class DBEx03 {
	
	public static void login(String username, String password) {
		try {
			Connection conn = DriverManager.getConnection
					  ("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
			System.out.println("DB 연결완료");
			
			String sql = "SELECT * FROM userTbl WHERE username = ? AND password = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			// 물음표 시작번지 1
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery();	// SELECT
				
			while(rs.next()) {
				System.out.println(username + "님 로그인 되었습니다.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		login("ssar", "1234");
	}
}

 

 

---

 

 

원래 prepareStatement를 사용하기 이전에 Statement가 있었다.

String sql = "SELECT * FROM userTbl WHERE username = '" + username + "' AND password = '" + password + "'";
Statement pstmt = conn.createStatement();
ResultSet rs = pstmt.executeQuery(sql);	// SELECT

 

 

이때 아이디 비밀번호를 몰라도 로그인이 가능하게 할 수 있다.

Statement는 쿼리문 안에 변수를 넣을 수 있어서 취약했다.

String sql = "SELECT * FROM userTbl WHERE username =  " + username + " AND password = " + password;

 

// 2. 메서드 호출시에 홑따옴표 넣음
login("'ssar'", "'12345333' OR 1=1");

package site.metacoding.db;

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

// SQL Injection 개념잡기 (SQL 주입 공격)
public class DBEx04 {

	public static void login(String username, String password) {
		try {
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
			System.out.println("DB 연결완료");

			String sql = "SELECT * FROM userTbl WHERE username =  " + username + " AND password = " + password;
			Statement pstmt = conn.createStatement();
			ResultSet rs = pstmt.executeQuery(sql); // SELECT

			while (rs.next()) {
				System.out.println(username + "님 로그인 되었습니다.");
				Session.isLogin = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		// 2. 메서드 호출시에 홑따옴표 넣음
		login("'ssar'", "'12345333' OR 1=1");
		System.out.println(Session.isLogin);
	}
}

Statement와 prepareStatement의 차이는

변수 바인딩이 가능하냐 안하냐의 차이이다.

 

변수 바인딩은 쿼리문 안에 직접 변수를 넣는 것을 말한다.

 

SQL 인젝션 공격

: SQL 삽입은 응용 프로그램 보안 상의 허점을

의도적으로 이용해,

악의적인 SQL문을 실행되게 함으로써

데이터베이스를 비정상적으로

조작하는 코드 인젝션 공격 방법

 

즉, 악의적인 쿼리를 주입하는 공격이다.

 

이를 막기 위해 prepareStatement를 사용한다.

 

---

 

DB는 json이 아닌 Resultset을 리턴해준다.

 

.으로 참조하여 사용할 수 없다.

자바 오브젝트로 담아서 사용해야 편할 것이다.

 

DB의 테이블 스키마를

자바에서 똑같이 오브젝트로 바꾸는 것을

모델링이라고 한다.

 

package site.metacoding.db;

// DB -> 자바 모델링
public class Dept {
	private int deptno;
	private String dname;
	private String loc;
	
	// public Dept(){} 디폴트 생성자 존재 X
	
	public Dept(int deptno, String dname, String loc) {
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
}

 

package site.metacoding.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

// dept 테이블의 모든 내용을 출력하시오
// SELECT deptno, dname, loc FROM dept
public class DBEx05 {

	public static void main(String[] args) {
		try {
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
			System.out.println("DB 연결완료");

			String sql = "SELECT deptno, dname, loc FROM dept";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(); // SELECT

			List<Dept> depts = new ArrayList<>();

			// 파싱
			while (rs.next()) {
				// while 돌면서 dept가 초기화 되니까 컬렉션에 담아놓자
				Dept dept = new Dept(rs.getInt("deptno"), rs.getString("dname"), rs.getString("loc"));

				depts.add(dept);
			}

			for (Dept dept : depts) {
				System.out.println("deptno : " + dept.getDeptno()); // getInt("컬럼명")
				System.out.println("dname : " + dept.getDname());
				System.out.println("loc : " + dept.getLoc());
				System.out.println("====================");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

[출처]

https://cafe.naver.com/metacoding

메타코딩 : 네이버 카페

메타 코딩 유튜브
https://www.youtube.com/c/%EB%A9%94%ED%83%80%EC%BD%94%EB%94%A9

메타코딩