Connection과 Statement

MINIMI·2023년 1월 18일

JDBC

목록 보기

1/4

1) 개념

JDBC(Java DataBase Connectivity)
자바에서 데이터베이스에 접근할 수 있게 해주는 Programming API

1-1. 순서

Driver 등록
1. 폴더를 하나 만들고 거기에 ojdbc8.jar를 추가해준다
2. 프로젝트 > properties > Libraries에 Classpath > Add JARS에서 ojdbc8.jar를 추가해준다
3. new > file에서 properties를 작성해준다.
- JRE System Library에 없기 때문에 따로 오라클 드라이버를 설치해준다
```
//오라클 드라이버 클래스 경로
Class.forName("oracle.jdbc.driver.OracleDriver");
//try-catch 구문 필요
//드라이버 클래스명에 오타가 있거나 라이브러리가 추가 되지 않았을 경우 해당 클래스를 찾지 못했다는 exception 발생 가능
```
Connection 생성
1-1. properties를 이용하지 않는 방법
```
Connection 변수명 
= DriverManager.getConnection("jdbc:oracle:thin:@ip주소:포트번호:버전정보","user","password");
```
- DriverManager
  - 데이터 원본에 JDBC드라이버를 통해 커넥션을 만드는 역할
  - Class.forName() 메소드를 통해 생성되며 반드시 예외처리를 해야 한다
  - 주소값 / 계정명 / 비밀번호 등이 올바르게 입력되지 않아 연결 불가능할 경우 exception 발생
  - 직접 인스턴스 생성이 불가하고 getConnection() 메소드를 사용해야만 한다.
1-2. properties를 사용하는 방법
```
Properties prop = new Properties();

String driver = prop.getProperty("driver");
String url = prop.getProperty("url");

Class.forName(driver);
Connection conn = DriverManager.getConnection(url, prop);
```
- Connection
  - 특정 데이터 원본과 연결 된 커넥션
  - SQL 문장을 실행시키기 전에 우선 Connection 인스턴스가 있어야 한다.
Statement 생성
```
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,user,password);

Statement stmt = conn.createStatement();
```
- Connection 클래스의 createStatement() 메소드 호출해서 생성
- SQL 질의문을 String에 담아 인자로 전달하여 executeQuery()메소드를 호출하여 SQL 질의 수행
- Result set 형태로 반환됨
```
ResultSet rset = smtm.executeQuery("SELECT EMP_ID, EMP_NAME FROM EMPLOYEE");
```
Result set 형태의 결과문 출력
- getString(컬럼명 혹은 컬럼 번호) / getInt(컬럼명 혹은 컬럼번호) 등등 을 통해 꺼내오기
- 커서가 가리키는 행에서 인자로 전달한 컬럼명의 데이터를 요청 메소드 타입의 값으로 반환한다.
- .next() 다음 행이 있으면 그 값을 반환 / 없으면 종료
```
while(rset.next()){
	System.out.println(rset.getString("EMP_ID") + rset.getString("EMP_NAME");
```
결과 값 반환 후 close를 통해 사용한 자원 닫아주기
```
try{}
catch{}
finally{
	try{
	if(conn != null && !conn.isClosed())
	rset.close()
  }catch{}
```
1. 드라이버 등록
2. properties 생성
3. Class.forName을 통해 드라이브 등록
4. Connection C이름 ManagerDriver.getConnection()을 통해 연결
5. Statement 생성(Statement S이름 = C이름.createStatement();)
6. S이름.executeQuery("SQL 실행 구문") 을 통해 SQL 질의 수행
7. 결과문 출력(while(rset.next()))

1-2. DTO

DTO 생성
1. 가져올 테이블의 컬럼명과 변수형에 맞춰서 상수 필드 작성
2. 생성자를 생성
3. getters 와 setters 생성
4. toString()오버라이드

모든 내용을 조회하는 경우 결과를 ArrayList에 담아서 저장

EmployeeDTO = null;

Connection conn = getConnection();
Statement stmt = null;
ResultSet rset = null;

try {
				stmt = conn.createStatement();
				rset = stmt.executeQuery(query);
		
				empList = new ArrayList<>();
			
				while(rset.next()) {
					row = new EmployeeDTO();
				
					row.setEmpID(rset.getString("EMP_ID"));
					row.setEmpName(rset.getString("EMP_NAME"));
					row.setEmpNo(rset.getString("EMP_NO"));
					row.setEmail(rset.getString("EMAIL"));
					row.setPhone(rset.getString("PHONE"));
					row.setDeptCode(rset.getString("DEPT_CODE"));
					row.setJobCode(rset.getString("JOB_CODE"));
					row.setSalary(rset.getInt("SALARY"));
					row.setBonus(rset.getDouble("BONUS"));
					row.setManagerId(rset.getString("MANAGER_ID"));
					row.setHireDate(rset.getDate("HIRE_DATE"));
					row.setEntDate(rset.getDate("ENT_DATE"));
					row.setEntYn(rset.getString("ENT_YN"));	
				
					empList.add(row);
				}
			
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				close(conn);
				close(stmt);
				close(rset);
			}
		
			for(EmployeeDTO arr : empList) {
				System.out.println(arr);
			}

1-3. PreparedStatement

1) PreparedStatement

.preparedStatement(SQL 구문)로 호출
.createStatement는 preparedStatement와 다르게 생성 후 executeQuery(SQL)를 통해 실행구문을 전달하였지만, preparedStatement는 실행과 동시에 전달.

Class.forName(driver);
Connection conn = DriverManager(driver,user,password);

PreparedStatement pstmt = null;
ResultSet rset = null;

pstmt = conn.preparedStatement(SQL구문);

SQL 구문을 미리 컴파일 하고, 위치 홀더에 들어갈 값만 변경하면 되기 때문에 컴파일을 한번만 해서 실행 속도가 빠르다.(Statement는 매번 컴파일 해야 함)

2) ? 위치홀더

쿼리문의 조건절(WHERE)의 리터럴 값이 들어갈 자리에 ? 로 표기
PreparedStatement 객체 생성 후 위치홀더에 들어가야 하는 값 설정
만약 위치 홀더의 개수와 일치하지 않으면 java.sql.SQLException: 인덱스에서 누락된 IN 또는 OUT 매개변수:: 와 같은 exception 발생

Class.forName(driver);
Connection conn = DriverManager(url, user, password);
  
PreparedStatement pstmt = null;
ResultSet rset = null;
  
String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = ? AND EMP_NAME = ?"

pstmt = conn.preparedStatment(query);
  
// 위치 홀더에 값 전달
// 해당 상황에서는 2개이므로 1, 2로 표기한다
pstmt.setString(인덱스(1), 전달할 값);
pstmt.setStriing(인덱스(2), 전달할 값);

//실행문에서는 메소드에 아무것도 전달하지 않는다.
rset = pstmt.executeQuery();

추가적 이용
- 조회할 사원 이름의 성을 입력 받아 해당 성씨를 가진 사원의 정보 모두 출력 쿼리
- 연결연산자 || 을 이용해 '%'를 연결해준다
```
String query = "SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE ? || '%' ";
```

3) 쿼리문 작성

XML 파일 생성해서 작성

Properties prop = new Properties();
		
prop.setProperty("keyString", "valueString");
		
try {
	//new FileOutputStream("파일 경로" , "코멘트")
	prop.storeToXML(new FileOutputStream("src/com/greedy/section02/preparedstatement/employee-query.xml"), "");
} catch (IOException e) {
	e.printStackTrace();
}

document type definition : dtd (파일 타입 정의)

XML 작성

entry key : 구분할 수 있는 이름(String)

valueString : 쿼리문 내용

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
		<!-- 성으로 직원 조회하는 쿼리 -->
		<entry key="selectEmpByFamilyName">
			SELECT
				   E.*
			  FROM EMPLOYEE E
			 WHERE E.EMP_NAME LIKE ? || '%'
		</entry>

</properties>

실행할 경우

Properties prop  = new Properties();
prop.loadFromXML(new FileInputStream("경로"));
String query = prop.getProperty("설정한 키 이름");

pstmt = conn.preparedStatement(query);
pstmt.setString(인덱스, 변수)

rset = pstmt.executeQuery();

4) 장점

SQL injection을 막아줄 수 있음

1-4. SQLinjection

원래 개인정보를 반드시 알아야 올바르게 작동하도록 코드를 작성해야 하는데 변수에 SQL 구문을 작성해서 개인정보를 몰라도 원하는 코드가 작동하도록 하는 것
값을 입력해야 하는 상황에 SQL 구문을 입력해서 데이터가 없어도 조회가 동작하도록 하는 공격

1) Statement

변수를 입력받아 쿼리문을 전달하는 방식의 경우 변수에 SQL 구문을 넣어 올바른 값을 넣지 않아도 수행된 쿼리문의 조회값을 받아오도록 동작한다.

private static String empId = "200";
private static String empName = "' OR 1=1 AND EMP_ID = '200";

String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = '" + empId + "' AND EMP_NAME = '" + empName + "'";
		
		try {
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			
			if(rset.next()) {
				System.out.println(rset.getString("EMP_NAME") + "님 환영합니다.");
			}else {
				System.out.println("회원 정보가 없습니다.");
			}

2) PreparedStatement

미완성된 쿼리를 작성하고, 쿼리를 완성하면서 올바른 정보 전달

위치 홀더에 올바르지 않은 값이 전달되면서, SELECT 절에서 일치하는 행을 찾을 수 없으므로 동작이 수행되지 않는다.

private static String empId = "200";
private static String empName = "' OR 1=1 AND EMP_ID = '200";

String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = ? AND EMP_NAME = ?";
		
			try {
				pstmt = conn.prepareStatement(query);
          //미완성된 쿼리를 완성하면서 정보를 전달.
				pstmt.setString(1, empId);
				pstmt.setString(2, empName);
			
				rset = pstmt.executeQuery();
			
				if(rset.next()) {
					System.out.println(rset.getString("EMP_NAME") + "님 환영합니다.");
				}else {
					System.out.println("회원 정보가 없습니다.");
				}

MINIMI

DREAM STARTER

다음 포스트

Connection과 Statement

JDBC

1-1. 순서

1-2. DTO

1-3. PreparedStatement

1-4. SQLinjection

CRUD

0개의 댓글

관련 채용 정보