DBMS의 SQL문 실행과정
- SQL 구분 분석(Parsing)
- 문법의 유효성 검사(Syntax Check) : 문법의 규칙을 준수하는지 검사한다.
예) select form pms_board-> form은 잘못된 SQL 문법이다. - SQL 문의 의미 검사(Semantic Check) : SQL 문에서 지정하는 컬럼이나 테이블, 뷰 등이 유효한지 검사한다.
예) select from pms_okok -> pms_okok 테이블이 없다면, semantic 오류이다.
- 문법의 유효성 검사(Syntax Check) : 문법의 규칙을 준수하는지 검사한다.
- SQL 공유 풀 검사(Shared Pool Check) : 공유 풀은 SQL 문에 대해 생성된 실행 계획(execution plan)등을 보관한다.
공유 풀에 저장된 값 중에서 SQL ID와 일치하는 값이 있는지 조사한다.- 일치하는 값이 없으면 해당 SQL 문에 대하여 해시(hash) 연산 (SQL에 대한 해시값을 부여)을 수행하여 SQL ID를 생성하고,
- 만약 있다면, 즉시 해당 값을 꺼내 실행 계획에 따라 SQL 문을 실행한다.
- 하드 파싱 - SQL 최적화(Optimization) : SQL 문을 가장 효율적으로 실행할 수 있게 재구성한 다음, 각 문장 별로 실행 계획(execution plan)이라는 명령 코드를 생성한다. 여러 개의 실행 계획을 검토한 후 실행 비용을 계산하여 최적의 실행 계획을 생성한다.
- SQL 컴파일(Row Source Generation) : 최적화 단계에서 생성된 실행 계획을 입력으로 받고, 각 실행 단계 별로 결과 데이터(result set)를 리턴할 바이너리 명령을 생성한다. 이 바이너리 명령을 Row Source - (결과를 만들어내는 소스) 라고 부른다.
- Row Source Generator는 실행 순서에 따라 Row Source Tree를 생성한다.
- SQL 실행 : SQL 엔진은 Row Source Tree에 따라가면서 Row Source 바이너리 명령을 실행한다. 각 Row Source은 테이블이나 뷰, 조인 또는 그룹 연산 결과를 생성한다.
- 최종 실행 결과는 애플리케이션에게 리턴할 결과 데이터(Result Set)이다.
실습 - 무결성 제약 조건
Board 데이터는 작성자 Member의 번호를 저장한다. 그러나 Board가 저장되고 나서 그 작성자 데이터가 삭제되어도 Board 데이터는 그대로 남아있을 것이다. 이러한 데이터의 결함을 방지하기 위해 외부 키를 사용하여 관계 테이블과, 제약 조건을 만들어줘야 한다.
먼저 프로젝트 테이블에 외부 키 제약 조건을 설정한다.
project 테이블을 재정의한다. owner가 member 테이블의 pk와 연결된 외부키가 될것이므로 varchar -> int로 바꿔준다.
create table pms_project(
no int not null,
title varchar(255) not null,
content text not null,
sdt date not null,
edt date not null,
owner int not null,
members varchar(255) not null
);컬럼 no을 pk, auto-increment로 설정하고, owner 컬럼을 pms_member의 no 컬럼과 연결한 외부키로 설정한다.
alter table pms_project
add constraint pms_project_pk primary key(no);
alter table pms_project
modify column no int not null auto_increment;
alter table pms_project
add constraint pms_project_fk foreign key(owner) references pms_member(no);project와 member 두 테이블의 관계는 다대다 관계이다. project 데이터 하나당 member를 1개 이상 참조할 수 있으며 member 데이터 하나당 project를 0개 이상 참조할 수 있기 때문이다.
문제는 DBMS는 다대다 관계를 자체적으로 구현할 수가 없다. 즉, project 테이블의 members 컬럼은 여러 회원의 번호를 저장하므로 FK로 설정할 수 없다. members 컬럼에 유효하지 않은 회원 번호를 넣는 것을 막을 수 없기 때문이다.
따라서 이 다대다 관계를 해소하기 위해 pms_member_project 테이블을 만들어 각각의 테이블과 1대 다 관계를 형성시켜준다. 이와 같이 다대다 관계를 해소하기 위해 만들어지는 테이블을 관계 테이블이라고 한다.
관계 테이블 : 특정 두 테이블의 다대다 관계를 해소하기 위해 만들어지는 테이블이다. 보통 이 테이블의 이름은 pms_member_project / pms_memb_proj처럼 두 테이블의 이름을 나열하거나, 두 테이블의 관계를 잘 표현할 수 있는 단어를 사용하기도 한다.
일단 pms_member_project 테이블을 생성해준다.
create table pms_member_project (
member_no int not null,
project_no int not null
);그리고 제약 조건을 만든다.
alter table pms_member_project
add constraint pms_member_project_fk1 foreign key(member_no) references pms_member(no),
add constraint pms_member_project_fk2 foreign key(project_no) references pms_project(no);
단, 두 컬럼의 조합(!)이 중복되면 안되기 때문에 둘을 묶어서 PK로 설정한다.
alter table pms_member_project
add constraint pms_member_project_pk primary key(member_no, project_no);이렇게 지정했으면 pms_project에서 members 컬럼을 빼준다.
create table pms_project(
no int not null,
title varchar(255) not null,
content text not null,
sdt date not null,
edt date not null,
owner int not null
);이렇게 재정의하기 위해서는 다시 프로젝트 테이블을 지워야하는데 이것을 지우려면 자식테이블인 pms_member_project 테이블을 먼저 지워야한다.
이제 테이블을 재정의했으니 자바 객체들의 관계를 DBMS에서의 데이터 관계와 유사하게 만들어야 한다. 요즘에는 메모리에 별도로 데이터를 임시 저장하여 굳이 DBMS를 매번 조회하지 않고, 자바 객체 상태에서의 데이터를 가져오는 방법을 채택한다. 따라서 자바 객체들의 형태와 관계가 DBMS에서의 데이터들의 형태와 관계와 유사하게 만드는 것이 좋다.
Project의 owner 는 기존에 회원의 이름을 저장하는 String 타입이었으나 이것을 int 타입으로 변경할 수도, 혹은 Member 타입으로 변경할 수도 있다. DBMS에서는 Project 테이블이 member 테이블을 외부키를 통해 참조하고 있으므로 이 관계를 자바 객체들 사이에서도 맺어주기 위해서는 int 보다는 Member 타입으로 해주는 것이 좋다. 또한 members 필드도 삭제하지 않고, List<Member>로 바꿔준다. 이에 따라 게터, 세터 메서드에서 사용되는 데이터 타입도 바꿔준다.
public class Project {
private int no;
private String title;
private String content;
private Date startDate;
private Date endDate;
private Member owner;
private List<Member> members;이제 Project를 등록하는 ProjectAddCommand 클래스를 수정한다. 수정 내용은 다음과 같다.
- 사용자에게 데이터를 입력받아 Project 객체로 만들기
- Project의 owner 필드 값을 받을 때, 이름을 String 값으로 지정하지 않고, findByName을 통해서 리턴받은 Member 객체를 그대로 지정한다.
- Project의 member 필드 값을 받을 때에도 finyByName을 통해 얻은 Member을 그대로 List<Member>에 저장한다.
- Project 객체의 필드값을 데이터베이스에 저장하기
- owner 컬럼 값을 입력할 때에는 setString이 아니라 setInt로 바꿔주고 owner가 되는 Member 객체의 no 값을 파라미터로 넘겨준다.
- 하나의 Project 객체를 pms_project의 한 레코드로 입력을 완료했다면, 그 레코드에서 자동으로 입력된 no 컬럼 값 리턴받는다.
- 해당 레코드의 no 값을 알았다면, pms_member_project 테이블에 프로젝트의 no 값과 members에 저장된 Member 객체의 no 값을 갖는 레코드를 입력한다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.eomcs.pms.domain.Member;
import com.eomcs.pms.domain.Project;
import com.eomcs.util.Prompt;
public class ProjectAddCommand implements Command {
MemberListCommand memberListCommand;
public ProjectAddCommand(MemberListCommand memberListCommand) {
this.memberListCommand = memberListCommand;
}
@Override
public void execute() {
System.out.println("[프로젝트 등록]");
Project project = new Project();
project.setTitle(Prompt.inputString("프로젝트명? "));
project.setContent(Prompt.inputString("내용? "));
project.setStartDate(Prompt.inputDate("시작일? "));
project.setEndDate(Prompt.inputDate("종료일? "));
while (true) {
String name = Prompt.inputString("관리자?(취소: 빈 문자열) ");
if (name.length() == 0) {
System.out.println("프로젝트 등록을 취소합니다.");
return;
} else {
Member member = memberListCommand.findByName(name);
if (member == null) {
System.out.println("등록된 회원이 아닙니다.");
continue;
}
project.setOwner(member);
break;
}
}
// 프로젝트의 참여할 회원 정보를 담는다.
List<Member> members = new ArrayList<>();
while (true) {
String name = Prompt.inputString("팀원?(완료: 빈 문자열) ");
if (name.length() == 0) {
break;
} else {
Member member = memberListCommand.findByName(name);
if (member != null) {
System.out.println("등록된 회원이 아닙니다.");
continue;
}
members.add(member);
}
}
project.setMembers(members);
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"insert into pms_project(title,content,sdt,edt,owner)"
+ " values(?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS)) {
stmt.setString(1, project.getTitle());
stmt.setString(2, project.getContent());
stmt.setDate(3, project.getStartDate());
stmt.setDate(4, project.getEndDate());
stmt.setInt(5, project.getOwner().getNo());
stmt.executeUpdate();
try (ResultSet keyRS = stmt.getGeneratedKeys()) {
keyRS.next();
project.setNo(keyRS.getInt(1));
}
// 프로젝트에 참여하는 멤버의 정보를 저장한다.
try (PreparedStatement stmt2 = con.prepareStatement("insert into pms_member_project(member_no, project_no) values(?,?)")) {
for (Member member : project.getMembers()) {
stmt2.setInt(1, member.getNo());
stmt2.setInt(2, project.getNo());
stmt2.executeUpdate();
}
}
System.out.println("프로젝트를 등록하였습니다.");
} catch (Exception e) {
System.out.println("프로젝트 등록 중 오류 발생!");
e.printStackTrace();
}
}
}ProjectListCommand도 수정한다. 수정 내용은 다음과 같다.
- 프로젝트의 번호, 제목, 시작일, 종료일, 관리자의 이름 구하기 : pms_project에서 pms_member와 inner join 하여 p.owner와 m.no가 같은 레코드를 뽑아 p.no, p.title, p.sdt, p.edt, m.name을 가져오는 SQL문을 준비한다.
- 해당 SQL문을 실행한 결과를 ResultSet으로 받아 rs.next()를 한번씩 호출할 때마다
- 프로젝트의 팀원 구하기 : 이미 구해놓은 p.no를 갖고 pms_member_project에서 pms_member과 inner join 하여 mp.member_no와 m.no가 같은 레코드를 뽑아, mp.member_no, m.name을 가져오는 SQL문을 실행하고, 결과를 담은 ResultSet을 리턴받는다. 반복문을 돌려 각 m.name 값을 StringBuilder에 넣는다.
- 구해놓은 컬럼 값을 번호, 제목, 시작일, 종료일, 관리자, 팀원 순으로 한 줄에 출력한다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ProjectListCommand implements Command {
@Override
public void execute() {
System.out.println("[프로젝트 목록]");
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"select p.no, p.title, p.sdt, p.edt, m.name owner_name"
+ " from pms_project p inner join pms_member m on p.owner=m.no"
+ " order by p.no desc")) {
try (ResultSet rs = stmt.executeQuery()) {
System.out.println("번호, 프로젝트명, 시작일 ~ 종료일, 관리자, 팀원");
while (rs.next()) {
StringBuilder members = new StringBuilder();
try (PreparedStatement stmt2 = con.prepareStatement(
"select mp.member_no, m.name"
+ " from pms_member_project mp"
+ " inner join pms_member m on mp.member_no=m.no"
+ " where mp.project_no=" + rs.getInt("no"));
ResultSet memberRs = stmt2.executeQuery()) {
while (memberRs.next()) {
if (members.length() > 0) {
members.append(",");
}
members.append(memberRs.getString("name"));
}
}
System.out.printf("%d, %s, %s ~ %s, %s, [%s]\n",
rs.getInt("no"),
rs.getString("title"),
rs.getString("sdt"),
rs.getString("edt"),
rs.getString("owner"),
members.toString());
}
}
} catch (Exception e) {
System.out.println("프로젝트 목록 조회 중 오류 발생!");
e.printStackTrace();
}
}
}
ProjectDetailCommand도 수정한다. 수정 내용은 다음과 같다.
- ProjectListCommand와 같이 pms_project 테이블만 조회한 것을 pms_member와 inner join하여 owner의 이름 데이터까지 구할 수 있도록 SQL문을 수정하고, 실행한 결과를 ResultSet 객체로 받아, 하나씩 rs.next() 메서드를 호출할 때마다
- pms_member_project와 pms_member를 inner join 하여 팀원의 각 이름 데이터를 구하고 StringBuilder에 하나씩 집어넣는다.
- 해당 프로젝트에 대해서 구한 모든 값을 프로젝트명, 내용, 기간(시작일과 종료일), 관리자, 팀원 순으로 출력한다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.eomcs.util.Prompt;
public class ProjectDetailCommand implements Command {
@Override
public void execute() {
System.out.println("[프로젝트 상세보기]");
int no = Prompt.inputInt("번호? ");
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"select p.no, p.title, p.sdt, p.edt, m.name owner_name"
+ " from pms_project p inner join pms_member m on p.owner=m.no"
+ " where p.no=?")) {
stmt.setInt(1, no);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
StringBuilder members = new StringBuilder();
while (rs.next()) {
try (PreparedStatement stmt2 = con.prepareStatement(
"select mp.member_no, m.name"
+ " from pms_member_project mp"
+ " inner join pms_member m on mp.member_no=m.no"
+ " where mp.project_no=" + rs.getInt("no"));
ResultSet memberRs = stmt2.executeQuery()) {
while (memberRs.next()) {
if (members.length() > 0) {
members.append(",");
}
members.append(memberRs.getString("name"));
}
}
}
System.out.printf("프로젝트명: %s\n", rs.getString("title"));
System.out.printf("내용: %s\n", rs.getString("content"));
System.out.printf("기간: %s ~ %s\n", rs.getDate("sdt"), rs.getDate("edt"));
System.out.printf("관리자: %s\n", rs.getString("owner"));
System.out.printf("팀원: %s\n", members.toString());
} else {
System.out.println("해당 번호의 프로젝트가 존재하지 않습니다.");
}
}
} catch (Exception e) {
System.out.println("프로젝트 조회 중 오류 발생!");
e.printStackTrace();
}
}
}ProjectUpdateCommand도 수정한다. 수정 내용은 다음과 같다.
- 사용자가 변경하고자 하는 프로젝트의 번호를 받아, 해당 프로젝트의 기존 값들을 출력한다.
- pms_project 와 pms_member를 inner join 하여 제목, 내용, 시작일, 종료일, 관리자의 번호, 관리자의 이름 데이터를 얻고, 이 데이터를 통해 모두 새로 생성한 Project 객체를 초기화한다.
- 관리자는 관리자의 번호와 이름을 갖는 임시 Member 객체를 생성하여 저장한다.
- 사용자가 이 프로젝트에 대하여 변경할 값을 입력받아 Project의 필드 값들을 변경한다. 이때 members에 저장할 List<Member>도 만들어 저장한다.
- pms_project에서 해당 Project에 대응하는 레코드를 변경된 필드값으로 변경한다.
- pms_member-Project에서 팀원 정보를 변경한다.
- pms_member_project에서 project_no 컬럼 값이 해당 Project의 번호였던 모든 레코드를 삭제한다.
- pms_member_project에서 Project의 members에 저장된 모든 Member 객체의 no 값과 Project의 no 값을 저장한다.
package com.eomcs.pms.handler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.eomcs.pms.domain.Member;
import com.eomcs.pms.domain.Project;
import com.eomcs.util.Prompt;
public class ProjectUpdateCommand implements Command {
MemberListCommand memberListCommand;
public ProjectUpdateCommand(MemberListCommand memberListCommand) {
this.memberListCommand = memberListCommand;
}
@Override
public void execute() {
System.out.println("[프로젝트 변경]");
int no = Prompt.inputInt("번호? ");
Project project = new Project();
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"select p.title, p.content, p.sdt, p.edt, p.owner, m.name owner_name"
+ " from pms_project p inner join pms_member m on p.owner=m.no"
+ " where p.no = ?")) {
stmt.setInt(1, no);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
project.setNo(no);
project.setTitle(rs.getString("title"));
project.setContent(rs.getString("content"));
project.setStartDate(rs.getDate("sdt"));
project.setEndDate(rs.getDate("edt"));
Member owner = new Member();
owner.setNo(rs.getInt("owner"));
owner.setName(rs.getString("owner_name"));
project.setOwner(owner);
} else {
System.out.println("해당 번호의 프로젝트가 존재하지 않습니다.");
return;
}
}
} catch (Exception e) {
System.out.println("프로젝트 조회 중 오류 발생!");
e.printStackTrace();
return;
}
project.setTitle(Prompt.inputString(String.format(
"프로젝트명(%s)? ", project.getTitle())));
project.setContent(Prompt.inputString(String.format(
"내용(%s)? ", project.getContent())));
project.setStartDate(Prompt.inputDate(String.format(
"시작일(%s)? ", project.getStartDate())));
project.setEndDate(Prompt.inputDate(String.format(
"종료일(%s)? ", project.getEndDate())));
while (true) {
String name = Prompt.inputString(String.format(
"관리자(%s)?(취소: 빈 문자열) ", project.getOwner().getName()));
if (name.length() == 0) {
System.out.println("프로젝트 등록을 취소합니다.");
return;
} else {
Member member = memberListCommand.findByName(name);
if (member == null) {
System.out.println("등록된 회원이 아닙니다.");
continue;
}
project.setOwner(member);
break;
}
}
List<Member> members = new ArrayList<>();
while (true) {
String name = Prompt.inputString("팀원?(완료: 빈 문자열) ");
if (name.length() == 0) {
break;
} else {
Member member = memberListCommand.findByName(name);
if (member == null) {
System.out.println("등록된 회원이 아닙니다.");
continue;
}
members.add(member);
}
}
project.setMembers(members);
String response = Prompt.inputString("정말 변경하시겠습니까?(y/N) ");
if (!response.equalsIgnoreCase("y")) {
System.out.println("프로젝트 변경을 취소하였습니다.");
return;
}
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"update pms_project set"
+ " title = ?,"
+ " content = ?,"
+ " sdt = ?,"
+ " edt = ?,"
+ " owner = ?"
+ " where no = ?")) {
stmt.setString(1, project.getTitle());
stmt.setString(2, project.getContent());
stmt.setDate(3, project.getStartDate());
stmt.setDate(4, project.getEndDate());
stmt.setInt(5, project.getOwner().getNo());
stmt.setInt(6, project.getNo());
int count = stmt.executeUpdate();
if (count == 0) {
System.out.println("해당 번호의 프로젝트가 존재하지 않습니다.");
return;
}
try (PreparedStatement stmt2 = con.prepareStatement(
"delete from pms_member_project where project_no=" + project.getNo())) {
stmt2.executeUpdate();
}
try (PreparedStatement stmt2 = con.prepareStatement(
"insert into pms_member_project(member_no, project_no) values(?,?)")) {
for (Member member : project.getMembers()) {
stmt2.setInt(1, member.getNo());
stmt2.setInt(2, project.getNo());
stmt2.executeUpdate();
}
}
System.out.println("프로젝트를 변경하였습니다.");
} catch (Exception e) {
System.out.println("프로젝트 변경 중 오류 발생!");
e.printStackTrace();
}
}
}
ProjectDeleteCommand 도 수정한다. 수정 내용은 다음과 같다.
- 사용자에게 삭제하고자 하는 프로젝트의 번호를 입력받는다.
- pms_member_project에서 사용자가 입력한 번호를 project_no 값으로 갖는 모든 레코드를 찾아 삭제한다.
- pms_project에서 사용자가 입력한 번호를 no 값으로 갖는 레코드를 찾아 삭제한다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import com.eomcs.util.Prompt;
public class ProjectDeleteCommand implements Command {
@Override
public void execute() {
System.out.println("[프로젝트 삭제]");
int no = Prompt.inputInt("번호? ");
String response = Prompt.inputString("정말 삭제하시겠습니까?(y/N) ");
if (!response.equalsIgnoreCase("y")) {
System.out.println("프로젝트 삭제를 취소하였습니다.");
return;
}
try (Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
PreparedStatement stmt = con.prepareStatement(
"delete from pms_project where no=?")) {
try (PreparedStatement stmt2 = con.prepareStatement(
"delete from pms_member_project where project_no=" + no)) {
stmt2.executeUpdate();
}
stmt.setInt(1, no);
int count = stmt.executeUpdate();
if (count == 0) {
System.out.println("해당 번호의 프로젝트가 존재하지 않습니다.");
} else {
System.out.println("프로젝트를 삭제하였습니다.");
}
} catch (Exception e) {
System.out.println("프로젝트 삭제 중 오류 발생!");
e.printStackTrace();
}
}
}'국비 교육' 카테고리의 다른 글
| 2020.11.5 일자 수업 : DAO interface, 커넥션 객체 공유, 로그인 (0) | 2020.11.06 |
|---|---|
| 2020.10.29 일자 수업 : JDBC, JDBC Driver (0) | 2020.10.29 |
| 2020.10.26일자 수업 : stateless logIn 기능 구현, SQL문법 (0) | 2020.10.27 |
