빅데이터 Java 개발자 교육 - 20일차 [Oracle 5번째 시간 (테이블뷰 만들기 및 GROUP BY 실습)]

Jun_Gyu·2023년 2월 26일
0
post-thumbnail

지난시간은 우리가 고객, 메뉴, 식당 기능까지 Mapper를 구성하여 시험까지 마친 상태이다.

오늘은 다른 테이블과 키를 공유하고 있는 나머지 테이블들(주문, 배달 테이블)의

쿼리문 작성 및 eclipse 코드작성 실습을 할 예정이다.

지금까지 배운 개념들은 실제 업무에서도 사용되는것이니

생각이 나지 않을때마다 블로그 글들 참고하여

문제들을 잘 해결하도록 하자.


먼저 Oracle 쿼리문이다.

// 주문하기(기본키, 고객아이디 + 메뉴의 정보)
INSERT INTO ordertbl (no, regdate, cnt, email, menuno)
     VALUES(seq_ordertbl_no.NEXTVAL, CURRENT_DATE, '1', 'junkue20@naver.com', '1043');

// 주문 수량변경
UPDATE ordertbl SET cnt = '4'  WHERE no = '100001';

// 주문내역조회
SELECT o.no, o.menuno, o.cnt, o.regdate FROM ordertbl o WHERE email = 'junkue20@naver.com';

// ((주문 + 고객) + 메뉴)을 조인한 뷰 만들기, 불필요한것 중복컬럼은 제거
CREATE OR REPLACE VIEW ordercusview AS
    SELECT o.no,  TO_CHAR(o.regdate, 'YYYY:MM:DD')orderdate, o.cnt, o.email, o.menuno,
    c.phone, c.chk, c.address  FROM ordertbl o INNER JOIN customer c ON o.email = c.email;
    
SELECT * FROM ordercusview; 

CREATE OR REPLACE VIEW ordercusmenuview AS
SELECT m.name, m.price, m.content , m.phone restphone,
ocv.* FROM menu m INNER JOIN ordercusview ocv ON m.no = ocv.menuno;

SELECT * FROM ordercusmenuview;

위의 경우들을 가정하여 작성을 하였다.

포함된 문법들은 지난시간 배웠었던 테이블뷰, INNER JOIN, 날짜 분류하기 등이 포함되었다.

아래 사진은 위의 쿼리문들을

차례대로 실행하여 도출해낸 결과들의 화면이다.

세 테이블들의 정보를 보기쉽게끔 테이블뷰로 한곳에 묶어둔 모습이다.

테이블뷰를 만들 때 중복되는 값이 발생하면 꼭 확인하여 구성할 수 있도록 하자.

위의 쿼리문을 바탕으로 eclipse에서 OrdertblMapper 인터페이스를 만들어보자.

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

import dto.Ordertbl;

public interface OrdertblMapper {

	// 주문하기(기본키, 고객아이디 + 메뉴의 정보)
	@Insert({ " INSERT INTO ordertbl (no, regdate, cnt, email, menuno) ",
			" VALUES(seq_ordertbl_no.NEXTVAL, CURRENT_DATE, #{obj.cnt},#{obj.email}, #{obj.menuno}) " })
	public int insertOrder(@Param("obj") Ordertbl obj);

	// 주문 수량변경
	@Update({ " UPDATE ordertbl SET cnt = #{obj.cnt}  WHERE no = #{obj.no}; " })
	public int updateOrdercnt(@Param("obj") Ordertbl obj);

	// 주문내역조회
	@Select({ " SELECT o.no, o.menuno, o.cnt, o.regdate FROM ordertbl WHERE email = #{obj.email} " })
	public Ordertbl selectOrder(@Param("obj") Ordertbl obj);

	// ((고객 + 주문) + 메뉴)을 조인한 뷰 만들기, 불필요한것 중복컬럼은 제거
	@Results({ @Result(column = "CONTENT", property = "음식설명 ", // column에 해당하는항목을 property로 바꾼다.
			javaType = String.class, jdbcType = JdbcType.CLOB), 

			@Result(column = "RESTPHONE", property = "가게번호 ", 
					javaType = String.class, jdbcType = JdbcType.VARCHAR),

			@Result(column = "PRICE", property = "음식가격 ",
					javaType = String.class, jdbcType = JdbcType.NUMERIC)

	})
	@Select({ " SELECT m.name, m.price, m.content , m.phone restphone, ocv.* ",
			" FROM menu m INNER JOIN ordercusview ocv ON m.no = ocv.menuno " })
	public List<Map<String, Object>> selectOrderView();

}

지난시간 배운 @Results 레어노테이션을 사용하여 몇몇 정보들을 바꿔주었다.

package test;

import java.util.List;
import java.util.Map;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import dto.Ordertbl;
import mapper.OrdertblMapper;

class OrdertblTest {
	OrdertblMapper mapper = MyBatisContext.getSqlSession().getMapper(OrdertblMapper.class);

	// 주문하기(기본키, 고객아이디 + 메뉴의 정보)
	@Test
	void insertOrder() {
		Ordertbl order = new Ordertbl();
		order.setCnt(10L);
		order.setEmail("abcde");
		order.setMenuno(1048L);
		int ret = mapper.insertOrder(order);
		System.out.println(ret);
	}

	// 주문 수량변경
	@Test
	void updateOrdercnt() {
		Ordertbl order = new Ordertbl();
		order.setNo(100003L);
		int ret = mapper.updateOrdercnt(order);
		System.out.println(ret);
	}

	// 주문내역조회
	@Test
	void selectOrder() {
		Ordertbl order = new Ordertbl();
		order.setEmail("abcde");
		Ordertbl ret = mapper.selectOrder(order);
		System.out.println(ret.toString());
	}

	// ((고객 + 주문) + 메뉴)을 조인한 뷰 만들기, 불필요한것 중복컬럼은 제거
	@Test
	void selectOrderView() {
		List<Map<String, Object>> list = mapper.selectOrderView();
		for (Map<String, Object> obj : list) {
			System.out.println(obj);
		}
	}

}

테스트 클래스까지 완료하였다.

이번에는 배달라이더, 배달주문 테이블이다.

-- 배달자 등록
INSERT INTO rider (phone, name, regdate, password)
VALUES ( '010-2222-0001', '김배달',CURRENT_DATE, '비밀번호');

-- 배달자 1명 조회
SELECT phone, name, regdate FROM rider WHERE phone = '010-2222-0001' ;

배달자 관련 기능은 위의 두가지만 추가하였다.

package mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import dto.Rider;

public interface RiderMapper {

	// 배달자 등록
	@Insert({ " INSERT INTO rider (phone, name, regdate, password) ",
			" VALUES ( #{obj.phone}, #{obj.},CURRENT_DATE, #{obj.password}) " })
	public int insertRider(@Param("obj") Rider obj);

	// 배달자 1명 조회
	@Select({ " SELECT phone, name, regdate FROM rider WHERE phone = #{obj.phone} " })
	public Rider selectRider(@Param("obj") Rider obj);

}

RiderMapper의 구성 모습이다.

package test;

import java.security.MessageDigest;
import java.util.List;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import dto.Rider;
import mapper.RiderMapper;

class RiderMapperTest {
	RiderMapper mapper = MyBatisContext.getSqlSession().getMapper(RiderMapper.class);

	// 비밀번호 hash암호화 알고리즘
	public String hashPW(String id, String pw) {
		try {
			// 1. Hash알고리즘 SHA-256, 단방향 aa => 3asdfjkikewjwkj21k2km...
			MessageDigest md = MessageDigest.getInstance("SHA-256");
			// ex)A라는 사용자 1234(암호) + salt (사용자id)
			// ex)B라는 사용자 1234(암호) + salt (사용자id) (같은 암호를 치더라도 다른 암호키가 발생한다!)
			md.update((id + pw).getBytes());

			// byte to string으로 변경
			byte[] pwdSalt = md.digest();

			StringBuffer sb = new StringBuffer();
			for (byte b : pwdSalt) {
				sb.append(String.format("%02x", b));
			}
			String result = sb.toString();
			return result;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	// 배달자 등록
	@Test
	void insertRider() {
		Rider rider = new Rider();
		rider.setPhone("010-2222-0002");
		rider.setName("최배달");
		rider.setPassword("1234");
		String hash = this.hashPW(rider.getPhone(), rider.getPassword());
		rider.setPassword(hash);
		int ret = mapper.insertRider(rider);
		System.out.println(ret);
	}

	// 배달자 1명 조회
	@Test
	void selectRider() {
		Rider rider = new Rider();
		rider.setPhone("010-2222-0002");
		List<Rider> ret = mapper.selectRider(rider);
		System.out.println(ret.toString());

	}
}

rider 테이블에 암호를 넣도록 설정되어있기 때문에 어제 사용했던 암호화 알고리즘을 사용하여 똑같이 구현해보았다.

이번에는 배달 테이블과 라이더 테이블을 합쳐서 테이블 뷰를 한번 만들어 보겠다.

-- 배달 등록 (기본키, 외래키 2개)
CREATE SEQUENCE seq_delivery_no INCREMENT BY 1 START WITH 101 NOMAXVALUE NOCACHE; -- 시퀀스 생성
INSERT INTO delivery (no, regdate, phone, orderno)
VALUES ( seq_delivery_no.NEXTVAL, CURRENT_DATE, '010-2222-0001', '100001');

DELETE FROM delivery;
SELECT * FROM delivery;

--ordercusmenuview + 식당정보를 inner join한 orderrestview
CREATE OR REPLACE VIEW orderrestview AS
SELECT r.name restname, ocv.* FROM ordercusmenuview ocv INNER JOIN restaurant r ON ocv.restphone = r.phone;

SELECT * FROM orderrestview;

-- 배달 + 배달자를 inner join한 deliveryview 생성
CREATE OR REPLACE VIEW deliveryview AS
SELECT r.phone riderphone, r.name, d.no deliveryno,
d.orderno,d.regdate deliverytime FROM rider r INNER JOIN delivery d ON r.phone = d.phone;


commit;
SELECT * FROM deliveryview;

-- orderrestview + deliveryview를 inner join한 deliveryinfoview 생성
CREATE OR REPLACE VIEW deliveryinfoview AS
SELECT orv.restname, orv.name menuname, orv.price, orv.content, orv.restphone, orv.no orderno, 
orv.orderdate, orv.cnt, orv.email, orv.menuno, orv.phone customerphone, orv.chk, orv.address customeradd,  
dev.riderphone, dev.name ridername, dev.deliveryno, dev.deliverytime
FROM deliveryview dev INNER JOIN orderrestview orv ON dev.orderno = orv.no;

SELECT * FROM deliveryinfoview;

아래는 Mapper의 코드이다.

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import dto.Delivery;

public interface DeliveryMapper {
	// 배달 등록
	@Insert({ " INSERT INTO delivery (no, regdate, phone, orderno)",
			" VALUES ( seq_delivery_no.NEXTVAL, CURRENT_DATE, #{phone}, #{orderno}) "

	})
	public int insertDelivery(@Param("obj") Delivery obj);

	// ordercusmenuview + 식당정보를 inner join한 orderrestview
	@Select({
			" SELECT r.name restname, ocv.* FROM ordercusmenuview ocv INNER JOIN restaurant r ON ocv.restphone = r.phone " })
	public List<Map<String, Object>> selectOrderRestView();

	// 배달 + 배달자를 inner join한 deliveryview 생성
	@Select({ " SELECT r.phone riderphone, r.name, d.no deliveryno, "
			+ " d.orderno,d.regdate deliverytime FROM rider r INNER JOIN delivery d ON r.phone = d.phone "

	})
	public List<Map<String, Object>> selectDeliveryView();

	// orderrestview + deliveryview를 inner join한 deliveryinview 생성
	@Select({ " SELECT orv.restname, orv.name menuname, orv.price, orv.content, orv.restphone, orv.no orderno, "
			+ " orv.orderdate, orv.cnt, orv.email, orv.menuno, orv.phone customerphone, orv.chk, orv.address customeradd,  "
			+ " dev.riderphone, dev.name ridername, dev.deliveryno, dev.deliverytime "
			+ " FROM deliveryview dev INNER JOIN orderrestview orv ON dev.orderno = orv.no " })
	public List<Map<String, Object>> selectDeliveryInfoview();
}

그리고 테스트 페이지까지.

package test;

import java.util.List;
import java.util.Map;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import dto.Delivery;
import mapper.DeliveryMapper;

class DeliveryMapperTest {
	DeliveryMapper mapper = MyBatisContext.getSqlSession().getMapper(DeliveryMapper.class);

	@Test
	void insertDelivery() {
		Delivery delivery = new Delivery();
		delivery.setPhone("010-2222-0001");
		delivery.setOrderno(1044L);
		int ret = mapper.insertDelivery(delivery);
		System.out.println(ret);
	}

	@Test
	void selectOrderRestView() {
		List<Map<String, Object>> list = mapper.selectOrderRestView();
		for (Map<String, Object> obj : list) {
			System.out.println(obj.toString());
		}

	}

	@Test
	void selectDeliveryView() {
		List<Map<String, Object>> list = mapper.selectDeliveryView();
		for (Map<String, Object> obj : list) {
			System.out.println(obj.toString());
		}
	}
	
	@Test
	void selectDeliveryInfoview() {
		List<Map<String, Object>> list = mapper.selectDeliveryInfoview();
		for (Map<String, Object> obj : list) {
			System.out.println(obj.toString());
		}
	}
	
}

그리고 추가적으로

위에서 만들어진 테이블 뷰를 가지고 예제를 조금 더 만들어보았다.

아래의 예제들은 'GROUP BY'를 통해서

조회하고자 하는 목록들을 추려서 데이터를 추출해보도록 하자.

GROUP BY

-- *** delivertinfoview를 이용한 통계 구하기***
-- 메뉴별 전체 주문수량 및 전체금액 (어떤 메뉴가 잘 팔리는가?) MAX값 
SELECT
d.menuno,
d.menuname,
SUM(d.cnt) sumcnt,
SUM(d.price*d.cnt) sumprice
FROM deliveryinfoview d WHERE d.restname ='노랑통닭' GROUP BY d.menuno, d.menuname ; 


-- 고객별 전체주문 횟수 및 전체금액 (어떤 고객이 vip인가??) MAX값
SELECT
d.customerphone 고객전화번호,
SUM(d.cnt) 전체주문횟수,
SUM(d.price*d.cnt) 전체주문금액
FROM  deliveryinfoview d GROUP BY d.customerphone;

-- 주문 시간대별 주문횟수 및 전체금액 (어느 시간대에 주문을 많이 하는가??)
SELECT
TO_CHAR(d.orderdate, 'HH24') 주문시간대,
SUM(d.cnt) 주문횟수,
SUM(d.price*d.cnt) 전체주문금액
FROM  deliveryinfoview d GROUP BY TO_CHAR(d.orderdate, 'HH24');

-- 배달 시간대별 배달횟수 (어느 시간대 배달을 많이하는가?)
SELECT
TO_CHAR(d.deliverytime, 'HH24'),
COUNT(deliverno) 
FROM  deliveryinfoview d GROUP BY d.deliverytime;

-- 배달자별 배달건수 (어느배달기사가 배달을 가장 많이했는가?)
SELECT
d.riderphone,
COUNT(*) 배달건수 
FROM deliveryinfoview d GROUP BY d.riderphone, ;

-- 식당 연락처가 전달되면 해당 식당의 메뉴별 전체 주문수량?(우리가게에는 어떤 메뉴가 잘나가나?)
SELECT
d.restname 음식점이름,
d.menuname 메뉴이름,
COUNT(*) 배달건수 
FROM deliveryinfoview d WHERE restphone = '051-224-5752' GROUP BY d.restname,d.menuname;

-- 요일별 주문횟수
SELECT
 TO_CHAR(sysdate, 'dy') 요일구분,
 SUM(cnt) 주문횟수
FROM deliveryinfoview d GROUP BY sysdate;

다음과 같이 쿼리문을 작성 완료하였다.

현재 입력시킨 데이터의 값들이 많지않아서

결과값이 내가 원하는대로 촤라락 뜨진 않지만..

어쨋든 잘 작동하는것을 확인하였다.


profile
시작은 미약하지만, 그 끝은 창대하리라

0개의 댓글