오늘은 Oracle의 두번째 시간이다.
어제 수업에서 Oracle의 기능들에 대해서 소개를 하는 시간이었다면, 오늘은 문법에 대해서 좀 더 심층적으로 실습을 하는 시간이 되겠다.
DB에 저장되어 있는 자료들을 가져올때
다음과 같은 순서를 따르면 되겠다.
1. 전체조회 (테이블의 개수와 상관없이)
2. 필터 조건으로 필요한 항목 필터링
3. 원하는 정렬 기준으로 조회하기
4. 가져온 데이터를 필요한 형태로 가공 (내장함수)
위의 순서대로 자료를 DB로부터 추출하면 된다.
그렇다면 어떠한 예제가 있는지
어제 저장한 데이터들을 바탕으로 한번 필터들을 적용해서 자료를 정렬하여 보자.
// SELECT 컬럼명 FROM 테이블명 별칭;
SELECT i.* FROM item i; -- 전체 가져오기
// SELECT 컬럼명 FROM 테이블명 별칭 ORDER BY 정렬컬럼 ASC|DESC (오름차순|내림차순) // java의 sort 필터와 기능 동일;
SELECT i.* FROM item i ORDER BY i.code ASC;
SELECT i.* FROM item i WHERE i.price >= 2000 ORDER BY i.code ASC; // 아이템 테이블에서 가격 2000원 이상인것들을 아이템 코드 오름차순으로 정렬
SELECT i.* FROM item i WHERE i.price >= 2000 AND i.price <= 6500 ORDER BY i.code DESC; // " 에서 가격 2000~6500원인 것들을 내림차순 "
SELECT i.code, i.name, iquantity FROM item i; // " 에서 코드, 이름, 수량 가져오기.
SELECT m.* FROM member m WHERE m.userage >=10 AND m.userage <=30 ORDER BY m.userid ASC; // 멤버 테이블에서 나이 10~30살인 유저의 id를 오름차순 정렬
SELECT i.* FROM item i WHERE i.code IN(1,3,19) ORDER BY i.code DESC; // 아이템 테이블 중 코드가 1, 3 ,19인 것 세개만 내림차순으로 정렬
// WHERE 컬럼명 LIKE '아무거나' || '포함할 내용' || '아무거나' 아무거나 = %
SELECT i.*, TO_CHAR(i.price*i.quantity, '999,999,999') FROM item i WHERE i.name LIKE '%'||'포'||'%'; // 아이템 테이블 중 이름에 '포'가 들어가는 항목들 정렬
// SELECT 컬럼명 "추가하고 싶은 항목" FROM 테이블명 별칭~;SELECT 컬럼명 FROM 테이블명 별칭;
이중 맨 아래의 LIKE 가 포함된 코드를 한번 실행시켜보도록 하겠다.
결과는 위와 같이 나오게 된다.
TO_CHAR() 함수에 대해서는 아래에서 조금 더 자세히 다뤄보도록 하겠다.
교재 141.pg
// 함수의 기능을 테스트할때 사용
// SELECT * FROM 테이블; (테이블은 DUAL로)
SELECT ABS(-2) FROM DUAL;
SELECT CURRENT_DATE FROM DUAL; // new Date();
SELECT TO_CHAR(123456789, '999,999,999') FROM DUAL; // 숫자를 세자리씩 ','으로 끊어서 표현
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') FROM DUAL; // 년 월 일
SELECT TO_CHAR(SYSDATE, 'AM') FROM DUAL; // 오전 (오후는 PM)
SELECT TO_CHAR(SYSDATE, 'DL') FROM DUAL; // 2023년 n월 n일 n요일이 이외에도 여러가지 기능이 있다.
// 실습 : 멤버 페이지에서 비밀번호와 성별을 빼고, 년월일 시간 순을 나타내는 항목 추가, 오름차순 조회
SELECT m.userid, m.username, m.userage, m.userphone, m.userdate,
TO_CHAR(m.userdate, 'YYYY-MM-DD HH') userdate1
FROM member m WHERE m.userage >= 0 ORDER BY m.userid ASC;위의 실습 문제의 조건을 만족하는 코드를 구성한 뒤 한번 실행해보았다.
userdate1이라는 항목에 성공적으로 추가가 됨과 동시에 원하는 항목들만 도출이 된 것을 확인할 수 있다.
그렇다면 아래의 코드를 참고해보자.
SELECT i.*, ROW_NUMBER() OVER( ORDER BY i.code DESC ) rown
FROM item i WHERE i.rown >= 1 AND i.rown <=5 ORDER BY i.code DESC;이 코드와 같은 경우 아이템 테이블에서 코드를 내림차순으로 하여 rown이라는 항목을 추가하여 생성한 뒤, rown 항목의 1~5까지의 데이터를 표시하려고 한다.
하지만 직접 시도하면 실행이 되지 않는다는것을 알 수 있는데, 그 이유는 위에서 설명한 내용과도 같다.
우리가 rown이라는 항목을 추가 생성을 한 단계는 4단계로써, 이미 가공된 데이터를 다시 필터링을 거치지 못하는 것이다.
(한마디로, 이미 단계를 건너뛰면 이전단계의 작업을 수행하지 못한다는 뜻이다.)
그렇다면 이러한 문제를 해결하기 위해서는 어떻게 하여야 할까??
SELECT i1.* FROM (
SELECT i.*, ROW_NUMBER() OVER( ORDER BY i.code DESC ) rown
FROM item i ORDER BY i.code DESC
) i1 WHERE i1.rown >= 1 AND i1.rown <=5;첫번째의 해결책으로 가상의 테이블을 만들어
기존의 데이터가 저장되어있는 데이터를 통째로 불러와서 거기에 필터링을 통해 결과를 도출한다는 것이다.
한마디로 가공이 끝난 물건을 다시 들고와서 내 입맛대로 다시 리폼하는것과 비슷하다고 보면 될것이다.
// view 생성하기
CREATE OR REPLACE VIEW itemview AS
SELECT i.*, ROW_NUMBER() OVER( ORDER BY i.code DESC ) rown
FROM item i ORDER BY i.code DESC;
// 실행시 "View ITEMVIEW이(가) 생성되었습니다." 라고 메세지가 뜸.
// 이후 생성된 view로 데이터 조회하기. (view는 조회(SELECT)만 된다.)
SELECT iv.* FROM itemview iv WHERE iv.rown >= 1 AND iv.rown<=5;다른 방법으로는 view를 생성하는 방법이 있다.
view와 같은 경우에는 일반적으로 생성된 테이블과는 다르게 데이터를 지울수가 없고, 데이터의 조회 (SELECT)만 가능하도록 특화되어있다는 점이다.
위의 첫번째 코드를 실행시키면
와 같이 생성이 완료된 메세지가 뜨게 되고, 이후 아래에 있는 코드를 실행시켜 조회를 해보면
조회가 성공적으로 잘 이루어 지는것을 확인할 수 있다.
또한 Java에서의 쾌적한 작업을 위하여 항상 view로 작업을 거친 이후에 java로 전송해야 하겠다.
CREATE OR REPLACE VIEW memberaddressview AS
SELECT m.*, a.userno, a.useraddr, a.userposrcode // 멤버테이블은 전체출력, 주소테이블은 번호, 주소, 주소코드만 출력
FROM member m , memberaddr a WHERE m.userid = a.userid;
// 결합조건 : member(m)테이블과 memberaddr(a)테이블의 userid값이 일치한다는 조건
DELETE FROM memberaddr WHERE userno=1001;
SELECT mav.* FROM memberaddressview mav;이번에는 결합조건을 통해 회원테이블과 회원주소테이블의 id값이 일치한다는 조건하에 두 테이블의 값을 한번에 묶어 view를 생성한뒤,
주소 시퀀스가 1001인 내용을 지우고 결과를 한번 보도록 하겠다.
확인해보니 정상적으로 잘 지워져 1001번 시퀀스의 데이터가 삭제된것을 볼 수 있다.
(데이터 변동이 있으니 잊지말고 ROLLBACK이나 COMMIT을 해주도록 하자!)
그렇다면 위의 내용들을 모두 응용하여 한가지 예제를 만들어 보겠다.
// INNER JOIN
CREATE OR REPLACE VIEW purchaseview AS
SELECT i.name, i.price, i.quantity, i.content, mp.* FROM item i,(
SELECT
m.userid, m.username, m.userage, m.userphone, m.usergender,
p.no, p.cnt, p.regdate, p.code
FROM member m , purchase p
WHERE m.userid = p.userid
) mp WHERE i.code = mp.code;
SELECT pv.* FROM purchaseview pv WHERE pv.quantity >= 100 ORDER BY regdate DESC;이번에는 '멤버'테이블과 '구매'테이블의 자료들 중
userid 의 값이 일치한다는 결합 조건을 바탕으로 데이터를 뽑아준 뒤, 다시 그 값들을 물품 테이블과 결합하여 물품코드가 일치하는 데이터만 도출되도록 필터링해주었다.
그리고 이 데이터를 view로 생성하여 물건의 갯수가 100개 이상인 것들만 불러오도록 필터링하였다.
(+내림차순)
실행을 시키게 되면..
위와 같이 결과가 도출됨을 알 수 있다.
그렇다면
이번엔 '배달의 민족'의 기능들을 실습을 통해 구현해도록 하겠다.
(아래의 실습 내용은 실제를 가장하여 만든 허구이니 혼동하지 마세용ㅎㅎ)
먼저 각 테이블들을 VScode를 통해서 설계를 마친다.
기존에 Oracle에 저장된 테이블과 데이터가 겹칠 수 이으니 이름을 주의해서 만들자!
이후 VScode에서 생성된 SQL DDL 명령어를 복사하여
Oracle에서 실행시키도록 한다.
값을 입력하다 생긴 오타때문에 처음에 오류때문에 고생좀 했다..
항상 입력타입이나 이름에 오타가 있는지 확인하도록 하자.
이후에 새로고침을 하면 이렇게 테이블에 새로운 항목 6개가 추가되었음을 확인할 수 있다.
VScode에서 미리 설계한것과 똑같이 테이블들이
구성이 완료되어 DB에 적용이 된 모습을 볼 수 있다.
(외부키, 기본키값 모두 제대로 설정이 되었다.)
이제는 비어있는 DB에 데이터들을 삽입하여보자.
// 메뉴, 식당 조회
SELECT m.* FROM menu m;
SELECT r.* FROM restaurant r;
// 메뉴추가
INSERT INTO menu (no, name, price, content, phone)
VALUES(seq_menu_no.NEXTVAL, '간장 반반', 20000, '맛있어용', '051-224-5752');
// 식당 종류별로 추가
INSERT INTO restaurant(phone, name, address, password )
VALUES('051-234-4123', '홍콩반점', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-252-2443', '한솥', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-314-6623', '미스터피자', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-721-4423', '이바돔 감자탕', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-284-5724', '오니기리와 이규동', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-624-1522', '베스킨라빈스31', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-435-5324', '호호닭발', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-184-6324', '버거킹', '부산 남구', '1234');
INSERT INTO restaurant(phone, name, address, password)
VALUES('051-224-5752', '노랑통닭', '부산 남구', '1234');
COMMIT;
ROLLBACK;기왕 실습하는거, 진짜로 배민 리스트처럼 꾸며놨다.
전화번호는 그냥 부산 지역번호로 그럴싸한 마구잡이로 아무거나 집어넣었다.
그리고 데이터를 저장한 이후 반드시 COMMIT!
이를 실행시키게 되면
이렇게 DB에 가게들이 정상적으로 등록되어있는 모습이다.
위에 있는 코드를 바탕으로 음식메뉴도 기본키(가게 전화번호)마다 저장해 줄 수 있다.
메뉴도 조회해보면
한30개 저장했는데 잠시 자리를 비운사이에 데이터가 유실되었다... ㅠㅠ 저장 꼭 하고 움직이자..
위와 같이 저장이 완료된 모습을 확인할 수 있다.
그렇다면 이번엔 Java에서 어떠한 방법으로 Oracle DB와 연동하여
데이터를 넣거나 지울 수 있을지에 대해서 알아보도록 하겠다.
먼저 지난번 VScode에서 생성시킨 'oracle' 폴더로 들어간 이후,
아무것도 적용되어있지 않은 pom.xml에 롬복과 같은 기능들을 추가시킨다.
이후 src폴더로 들어가 DB의 연동 및 데이터의 정렬을 담당해줄 패키지, 인터페이스, 클래스들을 생성한다.
금일은 시간관계상으로 간략하게 음식메뉴와 식당만 구현을 해보았다.
먼저 DB와의 연동 역할을 수행하는 MyBatisContext 클래스이다.
package connection;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import mapper.MenuMapper;
import mapper.RestaurantMapper;
// MyBatisContext.getSqlSession();
public class MyBatisContext {
public static SqlSession getSqlSession() {
try {
// DB접속용 dataSource객체 생성
BasicDataSource dataSource = new BasicDataSource();
// 오라클 기준
dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setUrl("오라클 기준 접속 ip");
dataSource.setUsername("아이디");
dataSource.setPassword("암호");
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration config = new Configuration(environment);
// 만든 매퍼 등록
config.addMapper(RestaurantMapper.class);
config.addMapper(MenuMapper.class);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(config);
return factory.openSession(true); //true이면 자동으로 commit을 수행함.
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}
}그다움으로 메뉴와 식당 클래스,
간단하니 사진으로 패스-
그리고 java에서 입력받은 데이터를 바탕으로 Oracle 클라이언트에서 명령어를 삽입하여 데이터를 삽입할 수 있도록 두 Interface의 Mapper를 구성해주었다.
package mapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import dto.Menu;
public interface MenuMapper {
@Insert({ " INSERT INTO menu ( no, name, price, content, phone ) ",
" VALUES( seq_menu_no.NEXTVAL, #{name}, #{price}, #{content}, #{phone} ) " })
public int insertMenu(Menu menu);
@Select({ " SELECT m.* FROM menu m ORDER BY name ASC" })
public List<Menu> selectMenuList();
@Update({ " UPDATE menu SET name =#{name}, price =#{price}, content=#{content} ",
" WHERE no=#{no} AND phone =#{phone} " })
public int updateMenu(Menu menu);
@Delete({ " DELETE FROM menu WHERE no=#{no} AND phone=#{phone}"
})
public int deleteMenu(Menu menu);
}메뉴 mapper에는 추가, 조회, 변형, 삭제의 기능을 추가하였고,
package mapper;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import dto.Restaurant;
@Mapper
public interface RestaurantMapper {
// String[] str ={"a","b","c"}
@Insert({
" INSERT INTO restaurant( phone, name, address, password ) " ,
" VALUES( #{phone}, #{name}, #{address}, #{password} ) "
})
public int insertRestaurant( Restaurant obj );
@Select({
" SELECT r.* FROM restaurant r "
})
public List<Restaurant> selectRestaurantList();
}식당 mapper에는 추가, 조회 두가지만 구현하였다.
각각의 항목에 적합한 명령어를 삽입해줬다.
추후에 다른 Mapper들을 구성한다면
MyBatisContext에 새 Mapper들을 등록해주어야 정상적인 사용이 가능하다.
(어떻게보면 ㄹㅇ 너무 귀찮다..)
이번엔 구성이 완료된 위의 코드들을 가져와 Main에서 실행시켜보도록 하자.
package main;
import java.util.List;
import connection.MyBatisContext;
import dto.Menu;
import dto.Restaurant;
import mapper.MenuMapper;
import mapper.RestaurantMapper;
public class Main {
public static void main(String[] args) {
// ------------------ 각 매퍼들의 기능을 불러옴.
MenuMapper mMapper // 메뉴 매퍼
=MyBatisContext.getSqlSession().getMapper(MenuMapper.class);
RestaurantMapper rMapper // 식당 매퍼
= MyBatisContext.getSqlSession().getMapper(RestaurantMapper.class);
// ------------------ 식당 조회
List<Restaurant> list = rMapper.selectRestaurantList();
for(Restaurant obj : list) {
System.out.println(obj.toString());
}
// ------------------ 메뉴추가 (5개만)
for (int i = 0; i < 5; i++) {
Menu menuAdd = new Menu();
menuAdd.setName("음식이름");
menuAdd.setContent("음식설명");
menuAdd.setPhone("추가할 가게의 전화번호"); // 추가시 가게의 전화번호와 일치하는지 확인할 것
menuAdd.setPrice((long) 지정할 가격 + i); // 가격이 1원씩 순차적으로 차이나도록 설정
int retAdd = mMapper.insertMenu(menuAdd);
System.out.println(retAdd);
} // 성공여부 0,1로 출력
List<Menu> list2 = mMapper.selectMenuList();
for (Menu obj : list2) {
System.out.println(obj.toString());
}
// ----------------------- 메뉴 수정
Menu menuFix = new Menu();
menuFix.setNo((long)메뉴의 시퀀스 번호);
menuFix.setContent("변경내용");
menuFix.setName("변경이름");
menuFix.setPhone("메뉴를 바꿀 가게의 번화번호");
menuFix.setPrice((long) 수정할 가격);
int retFix = mMapper.updateMenu(menuFix);
System.out.println(retFix);
// ----------------------- 메뉴 삭제
Menu menuDel = new Menu();
menuDel.setPhone("메뉴가 있는 가게의 전화번호");
menuDel.setNo((long) 삭제하려는 메뉴 번호);
int retDel = mMapper.deleteMenu(menuDel);
System.out.println(retDel);
// ----------------------- 식당 추가
Restaurant restAdd = new Restaurant();
restAdd.setPhone("추가할 가게의 전화번호"); // 중복되면 안됨 (기본키값)
restAdd.setName("추가할 가게이름");
restAdd.setAddress("가게의 주소");
restAdd.setPassword("설정할 비밀번호");
int retRestAdd = rMapper.insertRestaurant(restAdd);
System.out.println(retRestAdd );
}
}위와 같이 구성을 해주었다.
마지막으로
"080-1234-5678"이라는 전화번호를 가진
가상의 중국집 "Mongo반점"에
"오동통한 면발이 일품이에요!" 라는 설명의
11,000원짜리 짬짜면 한그릇을 추가하고 결과를 보여주는 것으로 오늘의 포스팅을 마치도록 하겠다.
아 주소랑 비밀번호를 안정했네..
그딴게 무슨 대수랴!
굿굿 잘올라갔네
이번엔 짬짜면을 메뉴에 추가시킬 차례이다.
엥?????? 뭐지
그렇게 큰건 안들어간다고..
아하 ㅎㅎ 필자의 실수 ㅎㅎ
전화번호의 길이를 최고 15자로 해놨는데
'080-1234-5678'은 너무 길어서 입구컷을 당한것이다.
그렇다면 080-123-4567로 한번 시도해보도록 하자!
재도전
아니 왜 또;;
(순간 패드립같아 보였다)
왜이런거지 하고 한참을 고민하다가..
불현듯 한 생각이 머릿속을 스쳐 지나갔다.
가게번호를 먼저 080-123-4567로 안했으니 당연히 부모키가 없는것이다..
이게 뭐라고 5분이나 고민을 했나..
근데15자 내외일텐데 쟤는 어떻게 들어갔지
아무튼 가게 하나 더 차려서 새로 넣어주고,
다시 한번 더 시도해보도록 하자.
드디어 된다 ㅠㅠㅠ
(아까 똑같은 코드였는데 10번 시도하다가 보니 갑자기 된다.. 왜이런건지 모르겠다;;)
이거 한그릇 먹을려고 수업마치고 1시간20분이나 시도했다..
짬짜면 한그릇 먹기 디게 힘드네
