[WEB]DAY29_DML 실습2, AS(별칭), NULL, Java와 DB연동

DML

--DML(DELETE, UPDATE) 실습

--PLAYER 테이블에서 TEAM_ID가 'K01'인 선수 이름을 내 이름으로 바꾸기
--ROLLBACK
SELECT * FROM PLAYER;

UPDATE PLAYER
SET PLAYER_NAME = '홍길동'
WHERE TEAM_ID = 'K01';

--PLAYER 테이블에서 POSITION이 'MF'인 선수 삭제하기
--ROLLBACK
DELETE FROM PLAYER
WHERE "POSITION" ='MF';

SELECT * FROM PLAYER;

--PLAYER 테이블에서 HEIGHT가 180이상인 선수 삭제하기
--ROLLBACK
DELETE FROM PLAYER
WHERE HEIGHT >= 180;

SELECT * FROM PLAYER;

 

---

AS(ALIAS)

: 별칭 

 

  - SELECT절 : 별칭을 ALIAS 뒤에 쓰거나, 공백문자 뒤에 쓴다. 
  - FROM절 : 공백문자 뒤에 쓴다.

 

SELECT PLAYER_ID AS "선수 번호", PLAYER_NAME 이름 FROM PLAYER;

--PLAYER테이블에서 선수이름을, TEAM테이블에서 주소를 검색
SELECT P.TEAM_ID , P.PLAYER_NAME, T.ADDRESS 
FROM PLAYER P, TEAM T;

--STADIUM테이블에서 ADDRESS를. TEAM테이블에서 TEL을 검색
SELECT S.ADDRESS AS "경기장 주소", T.TEL 전화번호
FROM STADIUM S, TEAM T; 

 

NULL

 : 정의되지 않은 값

  - 빈 값 대신 미정값을 부여할 때 사용(PK는 불가능, FK는 가능)
  - NVL : NULL값 대신 다른 값으로 변경 후 검색
  - NVL2 : NULL일 떄의 값, NULL이 아닐 때의 값 2가지를 설정

 

SELECT NVL(NICKNAME, '별명 없음') AS 별명 FROM PLAYER;

--PLAYER테이블에서 NATION이 NULL이면 미등록
--NULL이 아니면 등록으로 변경 후 선수이름, 국가 검색
--AS는 국가 등록 여부로 주기
--NVL2(컬럼명, NULL이 아닐 때, NULL일 때)
SELECT NVL2(NATION, '등록', '미등록') "국가 등록 여부", PLAYER_NAME 선수이름 
FROM PLAYER;

---

Java와 DB 연동하기

 

---

1. oracle에 USER TABLE을 만들기.

-----------------------------------------------------------------------------------
--USER테이블이 있다면 DROP
DROP TABLE "USER";
DROP SEQUENCE USER_SEQ;

--USER_SEQ 만들기
--1부터 시작, 1씩 증가, 1000까지
CREATE SEQUENCE USER_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 1000;

--USER테이블 만들기
--USER_NUMBER NUMBER NOT NULL
--ID VC2(20)
--PW VC2(20),
--NAME VC2(20),
--AGE NUMBER,
--PHONE_NUMBER VC2(11),
--PK는 ID컬럼에 부여
CREATE TABLE "USER"(
	USER_NUMBER NUMBER NOT NULL,
  ID VARCHAR2(20),
  PW VARCHAR2(20),
  NAME VARCHAR2(20),
  AGE NUMBER,
  PHONE_NUMBER VARCHAR2(11),
  CONSTRAINT USER_PK PRIMARY KEY(ID)
);

SELECT * FROM "USER";

SELECT COUNT(*) FROM "USER"
WHERE ID = 'hds9638';

INSERT INTO "USER"
(USER_NUMBER, ID, PW, NAME, AGE, PHONE_NUMBER)
VALUES(USER_SEQ.NEXTVAL, 'hgd1204', '1234', '홍길동', 20, '01012341234');

SELECT * FROM "USER";

TRUNCATE  TABLE "USER";

---

2. dao

 - DBConnection 클래스 (DB연결 클래스) 

 

jdbc  
: 키보드와 모니터 연결하면 드라이브를 설치하듯이  
데이터베이스를 사용하기 위해서 자바에 드라이브를 설치하는것 

localhost : 내 ip주소 
Class.forName() : 드라이브를 실행시켜주는 메소드 

 

Connection은 sql에 있는 것으로 사용해야함 !!

 

package dao;

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

public class DBConnection {
	//jdbc : Java Database Connectivity
	public static Connection getConnection() {
		Connection conn = null;
		//url, id, pw
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:XE";
			String user = "hr";
			String pw = "hr";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, pw);
			System.out.println("DB 연결 성공!");
		} catch (ClassNotFoundException cnfe) {
			System.out.println("드라이버 로딩 실패");
		} catch (SQLException sqle) {
			System.out.println("DB 접속 실패");
		} catch (Exception e) {
			System.out.println("알 수 없는 오류");
		}
		return conn;
	}
}

---

 

3. vo

 - DB에 있는 data값들.

package vo;

public class UserVO {
	private String user_number;
	private String id;
	private String pw;
	private String name;
	private int age;
	private String phone_number;
	
	public UserVO() {}

	public String getUser_number() {
		return user_number;
	}

	public void setUser_number(String user_number) {
		this.user_number = user_number;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPw() {
		return pw;
	}

	public void setPw(String pw) {
		this.pw = pw;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getPhone_number() {
		return phone_number;
	}

	public void setPhone_number(String phone_number) {
		this.phone_number = phone_number;
	}
	
}

 

---

 

3. dao

  - 사용할 메소드

 

package dao;

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

import vo.UserVO;

public class UserDAO {
	Connection conn;
	PreparedStatement pstm;
	ResultSet rs;
	UserVO user;
	
	private final int KEY = 3;

	// 아이디 검사
	public boolean checkId(String id) {
		String query = "SELECT COUNT(*) FROM \"USER\"" + " WHERE ID = ?";
		boolean check = true;
		try {
			conn = DBConnection.getConnection();
			pstm = conn.prepareStatement(query);
			pstm.setString(1, id);
			rs = pstm.executeQuery();
			// ResultSet객체는 결과를 가져오기 전 next()메소드를
			// 반드시 사용해주어야 한다.
			rs.next();
			if (rs.getInt(1) == 0) {
				check = false;
			}
		} catch (SQLException sqle) {
			System.out.println("checkId() 쿼리문 오류");
		} catch (Exception e) {
			System.out.println("알 수 없는 오류(checkId())");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new RuntimeException(e.getMessage());
			}
		}
		return check;
	}

	// 회원가입
	public boolean join(String id, String pw, String name, int age, String phone_number) {
		String query = "";
		boolean check = false;
		if (!checkId(id)) {
			query = "INSERT INTO \"USER\" (USER_NUMBER, ID, PW, NAME, AGE, PHONE_NUMBER) "
					+ "VALUES(USER_SEQ.NEXTVAL, ?, ?, ?, ?, ?)";

			try {
				int idx = 0;
				conn = DBConnection.getConnection();
				pstm = conn.prepareStatement(query);
				pstm.setString(++idx, id);
				pstm.setString(++idx,encrypt(pw));
				pstm.setString(++idx, name);
				pstm.setInt(++idx, age);
				pstm.setString(++idx, phone_number);
				pstm.executeQuery();
				check = true;
				
			} catch (SQLException sqle) {
				System.out.println(sqle);
				System.out.println("checkId() 쿼리문 오류");
			} catch (Exception e) {
				System.out.println(e);
				System.out.println("알 수 없는 오류(join())");
			} finally {
				try {
					if (rs != null) {
						rs.close();
					}
					if (pstm != null) {
						pstm.close();
					}
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					throw new RuntimeException(e.getMessage());
				}
			}
		}
		return check;
	}
	
	//암호화
	public String encrypt(String pw) {
		String en_pw = "";
		for (int i = 0; i < en_pw.length(); i++) {
			en_pw += (char)(pw.charAt(i)*KEY);
		}
		return en_pw;
	}
	//복호화
	public String decrypt(String en_pw) {
		String de_pw = "";
		for (int i = 0; i < en_pw.length(); i++) {
			de_pw += (char)(en_pw.charAt(i)/KEY);
		}
		return de_pw;
	}
	// 로그인
	// 수정
	// 삭제
	// 검색
	// 목록
	// 아이디 찾기
	// 비번 찾기
}

 

-------- 단축키 --------

ctrl+ ] : sql파일 새로만들기

--------------------------

---

TO DO LIST

 

?) String url = "~" : ~ 이부분도 경로인지.. 어디경로

 

?) Class.forName("oracle.jdbc.driver.OracleDriver")

   A) Oracle 드라이버 로딩.

      Class.forName("oracle.jdbc.driver.OracleDriver") 은 드라이버들이 읽히기만 하면

      자동 객체가 생성되고 DriverManager에 등록된다.

 

?) conn = DriverManager.getConnection(url, id, pw);

   A) Connection 객체 생성 + 접속 작업.

 

---

참고사이트

 

https://hyeonstorage.tistory.com/110

[JSP] JDBC를 사용한 데이터베이스 연동

 

https://sas-study.tistory.com/116

[JDBC] 오라클과 JDBC 연결하는 방법