DB&JDBC(03) - JDBC설계방식 - 수업 25일차
이번 시간에는 예제를 통해 JDBC 설계 방식을 사용해보고 수업 후반부에는 몇 가지 새로운 문법들과 Sequence에 대해 알아보겠습니다.
예제 코드
전체 코드는 깃허브에서 보실 수 있습니다.
멤버의 정보를 id(PRIMARY KEY)로 검색하는 예제입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public MemberVO findMemberById(String id) throws SQLException {
MemberVO vo = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(url, username, pass);
String sql = "select password, name, address from member where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);// 바인딩, 1: sql의 첫번째 물음표, 이 위치에 id를 set
rs = pstmt.executeQuery();
// primary key로 검색했으므로 회원 정보는 1명 존재하거나 존재하지 않는다.
if (rs.next()) {// 검색 결과 행(row)가 존재하면 true
//rs.getString(1) -> 위에서 설정한 sql의 column index(1번째 컬럼: password)
vo = new MemberVO(id, rs.getString(1), rs.getString(2), rs.getString(3));
}
return vo;
} finally {
closeAll(rs, pstmt, con);
}
}
이 코드의 목적은 이미 생성된 테이블의 정보를 id 컬럼 값 검색하는 것입니다. 주어진 id값과 일치하는 데이터가 존재할 경우 이를 반환하기 위해 MemberVO클래스의 인스턴스를 생성해 데이터를 할당하고 반환해줍니다.
먼저 구조를 살펴보면 반드시 사용해야 할 자원들이 있습니다. con, pstmt, rs 참조변수들로 사용을 마친 자원들은 해당 메서드가 종료되기 전에 close 해야 합니다. 그래서 finally구문을 사용해서 자원들을 close하는데 try finally구문은 각각 코드 블록이 형성되어 초기화를 하지 않을 경우 다른 블록에서는 사용이 안됩니다. 그래서 먼저 해당 자원들을 초기화 해줍니다. Memeber 테이블은 여러 번 사용될 예정이기에 closeAll()메서드를 미리 만들어 두었고 깃허브의 전체 코드를 참고하시면 보실 수 있습니다. 이를 오버라이딩한 closeAll(rs, pstmt, con)메서드를 현재 코드에서 사용 중입니다.
다음으로 설계 방식의 첫번째 단계인 드라이버 로딩은 서버마다 한 번씩만 선언해주면 되기 때문에 먼저 다른 클래스를 만들어서 불필요한 중복을 줄일 수 있습니다. 현재 코드에 보이지는 않지만 코드 앞에 먼저 선언해두어서 별도의 선언이 필요 없고 바로 Connection - sql 구문 - PreparedStatement - ResultSet 순으로 진행하게 됩니다.
새로운 SQL 문법
전에 배우지 않았던 SQL 문법들을 배워보도록 하겠습니다.
새로운 진행을 product테이블을 먼저 만들었다고 가정하고 진행하도록 하겠습니다. 아래 코드의 첫번째 라인부터 시작하는 주석에 설명이 있습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/*
테이블명: product
컬럼명: id, name, maker, price
데이터타입: id, price <= number
name, maker <= varchar2(100)
제약조건: id <= primary key
name, maker <= not null
price <= default 0
*/
-- DDL
CREATE TABLE product(
id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
maker VARCHAR2(100) NOT NULL,
price NUMBER DEFAULT 0
)
-- DML: INSERT
INSERT INTO product VALUES(1, '불닭볶음면', '삼양', 1500);
INSERT INTO product VALUES(2, '진라면', '오뚜기', 1100);
INSERT INTO product VALUES(3, '테라', '하이트진로', 1800);
INSERT INTO product VALUES(4, '참이슬후레쉬', '하이트진로', 1300);
-- DCL
COMMIT
-- DML: SELECT
SELECT * FROM product;
COUNT(*)
먼저 COUNT(*) 기능입니다. 특정 테이블의 열(rows)의 개수를 반환해줍니다.
1
2
3
-- COUNT(*)
SELECT COUNT(*) FROM product; -- 총 상품 수
SELECT COUNT(*) FROM member; -- 총 회원 수
MIN(), MAX(), AVG()
: 특정 컬럼의 데이터에서 최저값, 최고값 또는 평균값을 반환해줍니다.
(참고: ROUND() 기능을 통해 소수점을 반올림해서 반환합니다.)
-- 상품 최저가
SELECT MIN(price) FROM product;
-- 상품 최고가
SELECT MAX(price) FROM product;
-- 상품 평균가
SELECT AVG(price) FROM product;
-- 상품 평균가 반올림
SELECT ROUND(AVG(price)) FROM product;
ORDER BY + ASC / DESC
: 정렬 기능입니다. 오름차순은 ASC로, 내림차순은 DESC로 표현합니다. 기본 구조는 SELECT ~ FROM ~ (WHERE ~) ORDER BY ~ 형식입니다.
-- 정렬: 오름차순, 내림차순
-- price 오름차순 정렬
SELECT name, price FROM product ORDER BY price;
SELECT name, price FROM product ORDER BY price ASC;
-- price 내림차순 정렬
SELECT name, price FROM product ORDER BY price DESC;
일정 범위의 값은 AND를 사용해서 아래의 두 가지 방식으로 표현할 수 있습니다.
-- PRICE가 1300이상 2000이하인 상품의 NAME과 PRICE 조회(PRICE 오름차순)
SELECT name, price FROM product WHERE price>=1300 AND price<=2000 ORDER BY price ASC;
SELECT name, price FROM product WHERE price BETWEEN 1300 AND 2000 ORDER BY price ASC;
WHERE조건문을 사용하면서 문득 두 가지 이상의 SQL문을 하나의 SQL문으로 표현할 수 있지 않을까 생각이 들었습니다. 당연히 가능할 거라 생각을 했고, 조건문의 꼬리를 늘려서 작성해보려 했지만 에러만 발생하여 결국 강사님께 질문을 드려보니 앞으로 배울 내용으로 SUBQUERY라고 부르는 문법을 사용하면 된다고 하셨습니다. 간단한 예제를 통해 살펴보도록 하겠습니다.
1
2
3
4
5
-- SUBQUERY(추후 배울 예정)
SELECT MAX(price) FROM product
SELECT name FROM product WHERE price=1800;
-- 위 두 SQL을 하나의 SQL로 표현
SELECT NAME FROM PRODUCT WHERE price=(SELECT MAX(price) FROM product);
사실 비슷하게 시도했던 것 같은데 괄호를 사용한다는 것과 SQL문 전체를 집어넣어서 사용한다고는 생각을 못했었고, 이런 기능이 지금은 내용이 길어 낯설게 보이지만 표현이 명확한 것 같고 잘 익혀둬야겠다는 생각이 들었습니다.
다음 수업에는 SEQUENCE를 배울 예정입니다. 아직 기초적인 내용을 배우고 있지만 새로운 내용들이다 보니 지루할 틈 없이 공부를 하고 있는 것 같습니다. 다만 아직 중요한 것과 중요치 않은 부분들을 판단하는데 자신이 없어서 블로그 글을 다소 불필요하게 길게 작성하고 있습니다. 앞으로 보다 정제된 글을 올릴 수 있도록 노력하려 합니다.
댓글남기기