비트교육센터/MySql
[비트교육센터] MySql 23일차 ERD 추가문제, JDBC, 싱글톤패턴
달의요정루나
2023. 6. 30. 17:51
1. ERD 추가문제
https://julian5383.tistory.com/188
[비트교육센터] MySql 22일차 ERD 실습 추가문제
1. ERD(feat. sakila 데이터를 이용한 실습예제) https://julian5383.tistory.com/187 [비트교육센터] MySql 21일차 DDL, ERD 1. DDL - DML (Data Manipulation Language): 데이터베이스 내에서 데이터를 삽입, 업데이트 및 삭제하
julian5383.tistory.com
- 해당 게시글에서 이어진다.
1) 실습문제3
[1]
-- 고객별 연체 건수가 많은 수부터 출력하시오(고객 first_name, last_name, 연체건수)
-- 나
select cu.first_name, cu.last_name, count(*) as 연체건수
from customer cu
-- customer 테이블을 이용한다.
join rental re on re.customer_id = cu.customer_id
join inventory iv on iv.inventory_id = re.inventory_id
join film fl on fl.film_id = iv.film_id
-- rental, inventory, film 테이블을 조인한다.
where datediff(ifnull(re.return_date,curdate()),re.rental_date) > fl.rental_duration
-- rental 테이블과 film 테이블을 이용하고 datediff 함수로 연체일을 계산한다.
group by cu.first_name, cu.last_name
-- count 그룹함수를 썼기 때문에 first_name, last_name을 group by 한다.
order by count(*) desc;
-- 연체건수를 내림차순으로 정렬한다.
-- 선생님
select CS.first_name, CS.last_name, count(RT.rental_id)
from rental RT join inventory IV on RT.inventory_id = IV.inventory_id
join film FL on IV.film_id = FL.film_id
join customer CS on RT.customer_id = CS.customer_id
where datediff(IFNULL(RT.return_date, curdate()), RT.rental_date) > FL.rental_duration
group by CS.first_name, CS.last_name
order by count(RT.rental_id) desc;
--> 결과(나)
--> 결과(선생님)
[2]
-- 대여횟수 상위 5위 고객이 대여한 영화의 title을 알파벳순으로 출력
-- 출력 컬럼 : 상위 5위 고객이름(first, last), 영화제목, 총 대여수
-- 나(틀린구문)
select cu.first_name, cu.last_name, fl.title as '영화 제목', count(fl.title) as '총 대여수'
-- 이름, 영화제목, 영화 제목을 이용한 총대여수를 출력한다.
from customer cu
-- customer 테이블을 조회한다.
join rental rt on rt.customer_id = cu.customer_id
join inventory iv on iv.inventory_id = rt.inventory_id
join film fl on fl.film_id = iv.film_id
-- rental, inventory, film 테이블을 조회한다.
group by cu.first_name, cu.last_name, fl.title
order by count(fl.title) desc
-- 영화 제목 개수의 내림차순으로 정렬한다.
limit 5;
-- 데이터 출력을 5개로 제한한다.
-- 나(맞은 구문)
select cu.first_name, cu.last_name, fl.title as '영화 제목', top5.total as '총 대여수'
from (select customer_id, count(rental_id) as total
from rental
group by customer_id
order by count(rental_id)desc
limit 5) as top5
-- 서브쿼리를 선언해 상위 5개의 고객id와 대여id 갯수를 출력한다.
join customer cu on top5.customer_id = cu.customer_id
join rental rt on rt.customer_id = cu.customer_id
join inventory iv on iv.inventory_id = rt.inventory_id
join film fl on fl.film_id = iv.film_id
-- 테이블들을 조인해준다.
order by top5.total desc;
-- 총 대여수를 기준으로 내림차순으로 정렬한다.
-- 선생님
select CS.first_name, CS.last_name, FL.title, top5.tot_cnt
from (select customer_id, count(rental_id) as tot_cnt
from rental
group by customer_id
order by count(rental_id) desc
limit 5) as top5
join rental RT on top5.customer_id = RT.customer_id
join inventory IV on RT.inventory_id = IV.inventory_id
join film FL on IV.film_id = FL.film_id
join customer CS on top5.customer_id = CS.customer_id
order by top5.tot_cnt desc, FL.title;
-- FL.title이 있는 이유는 순위가 동률일 경우 제목순으로 정렬할려고 하는 것이다.--> 결과(나) 틀린구문
--> 결과(나) 맞은구문
--> 결과(선생님)
[3]
-- 배우 'WALTER TORN' 보다 출연작이 많은 배우의 정보를, 배우ID, 배우 이름(first_name, last_name), 출연작 타이틀, 작품 출시일을 출력하시오
-- (정렬순서: 배우ID, 타이틀, 출시일)
-- 나(잘못된 답변)
select ac.actor_id, ac.first_name, ac.last_name, fl.title, fl.release_year
from actor ac
-- actor 테이블을 선언한다.
join film_actor fa on fa.actor_id = ac.actor_id
join film fl on fl.film_id = fa.film_id
-- film_actor와 film을 조인한다.
where count(fa.film_id) >(
select count(film_id) from film_actor fa
join actor ac on ac.actor_id = fa.actor_id
where ac.first_name = 'WALTER' and ac.last_name = 'TORN'
);
-- where에 서브쿼리를 선언해 배우 walter torn이 작품 개수를 계산한다.
-- 선생님
select id, famous_actor.f_name, famous_actor.l_name, FL.title, FL.release_year
from
(
select AC.actor_id as id, AC.first_name as f_name, AC.last_name as l_name, count(film_id) as cnt
from actor AC
join film_actor FA on AC.actor_id = FA.actor_id
group by id, f_name, l_name
having cnt
-- 다른 배우들의 작품개수를 계산한다. select 절에 쓰기 위해 배우의 아이디와 이름도 같이 출력한다.
-- 'WALTER TORN'의 작품개수와 비교를 위해 having절에 작품개수를 넣는다.
>
(
select count(film_id)
from actor AC
join film_actor FA on AC.actor_id = FA.actor_id
where AC.first_name = 'WALTER'
and AC.last_name = 'TORN' -- actor테이블과 film_actor테이블을 조인해 배우 walter torn이 작품 개수를 계산한다.
)
) as famous_actor
join film_actor FA on famous_actor.id = FA.actor_id
join film FL on FA.film_id = FL.film_id
order by id, FL.title, FL.release_year;
-- 배우ID, 타이틀, 출시일 순으로 정렬한다.--> 결과(나)
- 코딩오류
--> 결과(선생님)
2. JDBC
- JDBC: Java DataBase Connectivity의 약자로 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API이다.
1) mysql connector 파일 복사하기
- mysql connector 파일의 위치는 이번 코딩과 상관은 없지만 편의성을 위해서 이클립스 workspace로 옮길예정이다.
[1] JdbcProject 프로젝트 만들기
[2] mysql connector 찾기
- Program Files(x86)에 들어가서 mysql-connector.jar을 복사한다.
[3] mysql connector 옮겨오기
- 자신이 작업하는 workspace에 복사, 붙여넣기를 한다.
[4] Build Path -> Configure Build Path... 로 가기
[5] Classpath를 클릭하고 Add External JARs... 클릭하기
[6] 커넥터 파일 가져오기
[7] 파일을 가져오고 Apply and Close 하기
[8] 프로젝트를 Refresh하면 jar파일이 생성되어 있는 것을 볼 수 있다.
2) JDBC를 이용해 sakila 데이터 가져오기
[1] Film.java
package com.julian5383.model;
public class Film {
// Film 테이블에 관련된 필드를 선언한다.
private int filmId;
private String title;
private String genre;
private String releaseYear;
private String language;
// 필드의 getter, setter를 선언한다.
public int getFilmId() {
return filmId;
}
public void setFilmId(int filmId) {
this.filmId = filmId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getGenre() {
return genre;
}
public void setGenre(String genre) {
this.genre = genre;
}
public String getReleaseYear() {
return releaseYear;
}
public void setReleaseYear(String releaseYear) {
this.releaseYear = releaseYear.substring(0,4);
//db 년도를 string으로 변환하면서 앞에 있는 4자리만 가져온다.
//ex) 2006-01-01에서 앞에 있는 4자리 2006만 가져온다.
}
public String getLanguage() {
return language;
}
public void setLanguage(String language) {
this.language = language;
}
}[2] Actor.java
package com.julian5383.model;
public class Actor {
//Actor 테이블에 관련된 필드를 선언한다.
private String firstname;
private String lastname;
private String title;
private String releaseyear;
private String rentalrate;
//해당필드에 관련된 getter, setter를 선언한다.
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getReleaseyear() {
return releaseyear;
}
public void setReleaseyear(String releaseyear) {
this.releaseyear = releaseyear;
}
public String getRentalrate() {
return rentalrate;
}
public void setRentalrate(String rentalrate) {
this.rentalrate = rentalrate;
}
}[3] SakilaDao.java
package com.julian5383.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;
import com.julian5383.model.Actor;
import com.julian5383.model.Film;
public class SakilaDao { //Dao: Data Access Object
private Connection conn;
//특정 DB와 연결해줍니다.
//SQL문을 실행시켜주고 결과를 리턴해줍니다.
private PreparedStatement pStmt;
//미리 컴파일된 SQL문을 나타내는 객체이다.
private ResultSet rSet;
//DB 쿼리문들을 실행해 생성된 결과를 나타내준다.
private String url = "jdbc:mysql://localhost:3306/sakila?serverTimezone=UTC";
private String username = "lastcoder";
private String password = "1234";
//데이터베이스 접속 url, 사용자이름, DB 비밀번호를 선언해준다.
private void getConnection() {
try {
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
//DriverManager(JDBC드라이버 관리)클래스의 getConnection메소드로 DB를 연결해준다.
private void closeConnection() {
try {
if(rSet != null) {
rSet.close();
}
if (pStmt != null) {
pStmt.close();
}
if(conn != null) {
conn.close();
}
} catch (Exception e2) {
// TODO: handle exception
}
}
//DB와의 연결을 해제해주는 메소드이다.
public List<Film> getFilms(String genre){//film테이블 관련 메소드다.
List<Film> films = new ArrayList<Film>();
//쿼리문을 통해 들어올 데이터를 담아둘 리스트를 선언한다.
getConnection();
//DB와 연결한다.
StringBuffer sb = new StringBuffer(); //문자열을 붙이기 위한 메소드
sb.append("select FL.film_id, FL.title, CG.name, FL.release_year, LG.name ");
sb.append("from film as FL join film_category as FC on FL.film_id = FC.film_id ");
sb.append("join category as CG on FC.category_id = CG.category_id ");
sb.append("join language as LG on FL.language_id = LG.language_id "); //끝에 한칸씩 띄어야함.
sb.append("where CG.name = ?");
//쿼리문을 작성한다.
String sql = sb.toString();
//toString을 통해 문자열 변수에 할당
try {
pStmt = conn.prepareStatement(sql); //ps객체를 받는다.
pStmt.setString(1, genre); //쿼리문의 첫번째 ?에 값을 집어넣음.
rSet = pStmt.executeQuery(); //쿼리문 실행
Film film;
while (rSet.next()) {
film=new Film();
film.setFilmId(rSet.getInt(1));
film.setTitle(rSet.getString(2));
film.setGenre(rSet.getString(3));
film.setReleaseYear(rSet.getString(4));
film.setLanguage(rSet.getString(5));
//쿼리문 실행시 다음행이 존재할때마다 sql문의 칼럼값을 가져온다.
films.add(film);
//해당 칼럼값을 films에 추가해준다.
}
} catch (Exception e) {
// TODO: handle exception
} finally {
closeConnection();
}
return films; //메소드 완성
}
public List<Actor> getActor(String firstname, String lastname){
List<Actor> actors = new ArrayList<Actor>();
getConnection();
StringBuffer sb = new StringBuffer();
sb.append("select a.first_name, a.last_name, f.title, f.release_year, f.rental_rate ");
sb.append("from film_actor fa ");
sb.append("join actor as a on a.actor_id = fa.actor_id ");
sb.append("join film as f on f.film_id = fa.film_id ");
sb.append("where a.first_name = ? and a.last_name = ? ");
sb.append("order by f.title");
String sql = sb.toString();
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, firstname);
pStmt.setString(2, lastname);
rSet = pStmt.executeQuery();
Actor actor;
while (rSet.next()) {
actor = new Actor();
actor.setFirstname(rSet.getString(1));
actor.setLastname(rSet.getString(2));
actor.setTitle(rSet.getString(3));
actor.setReleaseyear(rSet.getString(4));
actor.setRentalrate(rSet.getString(5));
actors.add(actor);
}
} catch (Exception e) {
// TODO: handle exception
} finally {
closeConnection();
}
return actors;
}
}[4] SakilaMain.java
package com.julian5383.main;
import java.util.List;
import java.util.Scanner;
import com.julian5383.db.SakilaDao;
import com.julian5383.model.Actor;
import com.julian5383.model.Film;
public class SakilaMain {
private static Scanner scanner = new Scanner(System.in);
public static void main(String[] args) {
String inputString;
while(true) {
System.out.println("Select Menu");
System.out.println("=====================================");
System.out.println("a. Films by genre");
System.out.println("b. Films by actor"); //first_name, last_name 입력받음
System.out.println("q. Quit");
System.out.print("> ");
// 어떤 테이블 값을 볼지 물어보는 글을 작성한다.
inputString = scanner.nextLine();
//어떤 테이블을 사용할지 입력받는다.
if (inputString.equals("a")) {
getFilms(); //장르 테이블 선택시 getFilms메소드를 이용한다.
}else if (inputString.equals("b")) {
getActor(); //배우 테이블 선택시 getActor메소드를 이용한다.
} else if(inputString.equals("q")) {
System.out.println("Program Terminated!");
scanner.close();
break;
}
}
}
public static void getFilms() {
System.out.print("Input genre: ");
String genre = scanner.nextLine();
//어떤 장르인지 입력받는다.(ex) drama)
SakilaDao sakilaDao = new SakilaDao();
//SakilaDao 클래스이 객체를 선언한다.
List<Film> films = sakilaDao.getFilms(genre);
//sakilaDao객체의 getFilms 메소드를 호출하고 입력받은 genre를 넣는다.
if (films.size() == 0) {
System.out.println("No data found~");
}else {
for (Film film: films) {
System.out.printf("%5s %30s %15s %5s %15s\n",
film.getFilmId(), film.getTitle(), film.getGenre(), film.getReleaseYear(), film.getLanguage());
}
}
//데이터가 없을시 No data found를 출력하지만 데이터가 있을시 for문을 통해서
//sql쿼리 실행결과가 출력된다.
}
public static void getActor() {
// TODO Auto-generated method stub
System.out.print("Input First_Name: ");
String first_name = scanner.nextLine();
System.out.print("Input Last_Name: ");
String last_name = scanner.nextLine();
SakilaDao sakilaDao = new SakilaDao();
List<Actor> actors = sakilaDao.getActor(first_name, last_name);
if(actors.size() == 0) {
System.out.println("No data found~");
}else {
for(Actor actor: actors) {
System.out.printf("%s %18s %13s %14s %11s\n",
actor.getFirstname(), actor.getLastname(), actor.getTitle(), actor.getReleaseyear(), actor.getRentalrate());
}
}
}
}--> 결과(결과가 길어서 접은글로 표시합니다)
더보기
Select Menu
=====================================
a. Films by genre
b. Films by actor
q. Quit
> a
Input genre: drama
33 APOLLO TEEN Drama 2006 English
61 BEAUTY GREASE Drama 2006 English
64 BEETHOVEN EXORCIST Drama 2006 English
79 BLADE POLISH Drama 2006 English
98 BRIGHT ENCOUNTERS Drama 2006 English
107 BUNCH MINDS Drama 2006 English
143 CHILL LUCK Drama 2006 English
146 CHITTY LOCK Drama 2006 English
172 CONEHEADS SMOOCHY Drama 2006 English
173 CONFESSIONS MAGUIRE Drama 2006 English
179 CONQUERER NUTS Drama 2006 English
186 CRAFT OUTFIELD Drama 2006 English
204 DALMATIONS SWEDEN Drama 2006 English
209 DARKNESS WAR Drama 2006 English
218 DECEIVER BETRAYED Drama 2006 English
225 DESTINATION JERK Drama 2006 English
230 DIARY PANIC Drama 2006 English
272 EDGE KISSING Drama 2006 English
282 ENCOUNTERS CURTAIN Drama 2006 English
366 GOLDFINGER SENSIBILITY Drama 2006 English
368 GONE TROUBLE Drama 2006 English
379 GREEDY ROOTS Drama 2006 English
396 HANGING DEEP Drama 2006 English
401 HAROLD FRENCH Drama 2006 English
403 HARRY IDAHO Drama 2006 English
418 HOBBIT ALIEN Drama 2006 English
439 HUNCHBACK IMPOSSIBLE Drama 2006 English
473 JACKET FRISCO Drama 2006 English
504 KWAI HOMEWARD Drama 2006 English
514 LEBOWSKI SOLDIERS Drama 2006 English
521 LIES TREATMENT Drama 2006 English
539 LUCK OPUS Drama 2006 English
585 MOB DUFFEL Drama 2006 English
618 NECKLACE OUTBREAK Drama 2006 English
630 NOTTING SPEAKEASY Drama 2006 English
643 ORIENT CLOSER Drama 2006 English
662 PATHS CONTROL Drama 2006 English
666 PAYCHECK WAIT Drama 2006 English
683 PITY BOUND Drama 2006 English
706 QUEEN LUKE Drama 2006 English
709 RACER EGG Drama 2006 English
728 REUNION WITCHES Drama 2006 English
739 ROCKY WAR Drama 2006 English
758 SAINTS BRIDE Drama 2006 English
766 SAVANNAH TOWN Drama 2006 English
771 SCORPION APOLLO Drama 2006 English
772 SEA VIRGIN Drama 2006 English
775 SEATTLE EXPECATIONS Drama 2006 English
790 SHOOTIST SUPERFLY Drama 2006 English
803 SLACKER LIAISONS Drama 2006 English
818 SOMETHING DUCK Drama 2006 English
827 SPICE SORORITY Drama 2006 English
882 TENENBAUMS COMMAND Drama 2006 English
897 TORQUE BOUND Drama 2006 English
907 TRANSLATION SUMMER Drama 2006 English
910 TREATMENT JEKYLL Drama 2006 English
923 UNFAITHFUL KILL Drama 2006 English
942 VIETNAM SMOOCHY Drama 2006 English
944 VIRGIN DAISY Drama 2006 English
958 WARDROBE PHANTOM Drama 2006 English
969 WEST LION Drama 2006 English
979 WITCHES PANIC Drama 2006 English
Select Menu
=====================================
a. Films by genre
b. Films by actor
q. Quit
> b
Input First_Name: walter
Input Last_Name: torn
WALTER TORN AMELIE HELLFIGHTERS 2006-01-01 4.99
WALTER TORN ARABIA DOGMA 2006-01-01 0.99
WALTER TORN BANG KWAI 2006-01-01 2.99
WALTER TORN CASABLANCA SUPER 2006-01-01 4.99
WALTER TORN CASPER DRAGONFLY 2006-01-01 4.99
WALTER TORN CROW GREASE 2006-01-01 0.99
WALTER TORN CURTAIN VIDEOTAPE 2006-01-01 0.99
WALTER TORN DANCES NONE 2006-01-01 0.99
WALTER TORN EARLY HOME 2006-01-01 4.99
WALTER TORN FLYING HOOK 2006-01-01 2.99
WALTER TORN FORREST SONS 2006-01-01 2.99
WALTER TORN FREDDY STORM 2006-01-01 4.99
WALTER TORN GASLIGHT CRUSADE 2006-01-01 2.99
WALTER TORN HOBBIT ALIEN 2006-01-01 0.99
WALTER TORN HOOSIERS BIRDCAGE 2006-01-01 2.99
WALTER TORN HYSTERICAL GRAIL 2006-01-01 4.99
WALTER TORN JERSEY SASSY 2006-01-01 4.99
WALTER TORN LAMBS CINCINATTI 2006-01-01 4.99
WALTER TORN LESSON CLEOPATRA 2006-01-01 0.99
WALTER TORN LIES TREATMENT 2006-01-01 4.99
WALTER TORN LOCK REAR 2006-01-01 2.99
WALTER TORN LONELY ELEPHANT 2006-01-01 2.99
WALTER TORN MADISON TRAP 2006-01-01 2.99
WALTER TORN MOTIONS DETAILS 2006-01-01 0.99
WALTER TORN MULHOLLAND BEAST 2006-01-01 2.99
WALTER TORN MUMMY CREATURES 2006-01-01 0.99
WALTER TORN NIGHTMARE CHILL 2006-01-01 4.99
WALTER TORN NOVOCAINE FLIGHT 2006-01-01 0.99
WALTER TORN RAIDERS ANTITRUST 2006-01-01 0.99
WALTER TORN REUNION WITCHES 2006-01-01 0.99
WALTER TORN ROOTS REMEMBER 2006-01-01 0.99
WALTER TORN SIERRA DIVIDE 2006-01-01 0.99
WALTER TORN SKY MIRACLE 2006-01-01 2.99
WALTER TORN SLUMS DUCK 2006-01-01 0.99
WALTER TORN SPIKING ELEMENT 2006-01-01 2.99
WALTER TORN STAGE WORLD 2006-01-01 2.99
WALTER TORN STEPMOM DREAM 2006-01-01 4.99
WALTER TORN STRANGELOVE DESIRE 2006-01-01 0.99
WALTER TORN WARDROBE PHANTOM 2006-01-01 2.99
WALTER TORN WITCHES PANIC 2006-01-01 4.99
WALTER TORN WIZARD COLDBLOODED 2006-01-01 4.99
Select Menu
=====================================
a. Films by genre
b. Films by actor
q. Quit
> q
Program Terminated!
3) 싱글톤 패턴(feat. sakila 데이터)
- 싱글톤 패턴: 객체의 인스턴스를 한개만 생성되게 하는 패턴이다.
[1] SakilaDao.java
public enum SakilaDao {
//public static final SakilaDao INSTANCE = new SakilaDao();
//객체가 상수가 아니라 객체의 레퍼런스가 담긴 "INSTANCE"가 상수
//enum이여서 다른 곳에서 새 객체를 만들수 없다
//그래서 INSTANCE의 SakilaDao객체를 사용만 할수있음
INSTANCE;
}- class에서 enum으로 바꾸고 INSTANCE를 선언한다.
[2] SakilaMain.java
public static void getFilms() {
System.out.print("Input genre: ");
String genre = scanner.nextLine();
SakilaDao sakilaDao = SakilaDao.INSTANCE;
//enum을 통한 싱글턴을 만든것
List<Film> films = sakilaDao.getFilms(genre);
if (films.size() == 0) {
System.out.println("No data found~");
}else {
for (Film film: films) {
System.out.printf("%5s %30s %15s %5s %15s\n",
film.getFilmId(), film.getTitle(), film.getGenre(), film.getReleaseYear(), film.getLanguage());
}
}
}
public static void getActor() {
// TODO Auto-generated method stub
System.out.print("Input First_Name: ");
String first_name = scanner.nextLine();
System.out.print("Input Last_Name: ");
String last_name = scanner.nextLine();
SakilaDao sakilaDao = SakilaDao.INSTANCE;
//enum을 통한 싱글턴을 만든것
List<Actor> actors = sakilaDao.getActor(first_name, last_name);
if(actors.size() == 0) {
System.out.println("No data found~");
}else {
for(Actor actor: actors) {
System.out.printf("%s %18s %13s %14s %11s\n",
actor.getFirstname(), actor.getLastname(), actor.getTitle(), actor.getReleaseyear(), actor.getRentalrate());
}
}
}- enum을 통한 싱글턴을 만든다.
- 이렇게 함으로서 dao객체를 만드는 시간을 줄일 수 있다.(공유객체를 쓸때 특히 부각된다.)