DB&JDBC(04) - Sequence - 수업 26일차
DB&JDBC 4일차 수업 복습 시작하겠습니다.
목차
전에 작성해둔 productDTO를 기반으로 해서 productDAO에 기능을 추가해 DB를 활용해보도록 하겠습니다.
SQL CREATE 예제
먼저 product를 추가하는 CREATE 기능을 만드는 예제입니다. SQL로 시험 코드를 만들어 기능을 확인해보겠습니다.
1
insert into product(id, name, maker, price) values(6,'진짬뽕','오뚜기',2000);
SQL문은 위와 같은데 INSERT INTO키워드를 사용하고 추가를 원하는 TABLE의 이름과 괄호 안에 해당 컬럼을 기입합니다. 그리고 저장을 원하는 데이터들은 VALUES키워드를 선언한 뒤 인자로 각 컬럼에 해당하는 데이터를 기입해주시면 됩니다.
여기서 주목할 점은 데이터 타입인데 id에 VARCHAR2 타입을 기입해도 되지만 여기서는 NUMBER타입으로 선언을 했습니다.
어느 타입을 사용해도 문제는 없지만 일반적으로 편의 상 id값을 NUMBER타입으로 선언해준다고 합니다.
(참고로 모든 컬럼을 사용하는 경우 컬럼명은 안적어도 되지만 가독성과 유지보수 측면을 고려하면 가급적 써주는게 좋다고 합니다.)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public void register(ProductDTO dto) throws SQLException {
//사용을 자원을 닫아줘야 하는데 반드시 처리해주어야 하는 작업이기에 finally를 사용합니다.
//그리고 try블록과 finally 블록이 나뉘기에 자원을 닫아야 하는 변수들을 먼저 지역변수로 초기화를 해줍니다.
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection(url, username, password);
String sql = "insert into product(id, name, maker, price) values(?,?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getId());
pstmt.setString(2, dto.getName());
pstmt.setString(3, dto.getMaker());
pstmt.setInt(4, dto.getPrice());
pstmt.executeUpdate();
} finally {
closeAll(pstmt, con);
}
}
방식은 전에 진행했던 코드들과 큰 차이가 없습니다. 위의 프로그램은 전달받은 데이터를 저장하는 것이 목적이기에 INSERTSQL문을 가용하고 setString() 메서드로 전달받은 값을 세팅해서 실행시키면 등록됩니다.
코드를 작성할 때에는 먼저 리턴값을 확인해보고, 없다면 사용할 자원들을 확인한 후 연결을 닫을 수 있도록 초기화와 try finally블록을 만들어줍니다. 그래서 Connection과 PreparedStatement를 초기화해주고 try블록에서 DB와 연결을 진행합니다.
SQL문에서는 PreparedStatement를 사용했기 때문에 전달받은 인자값을 바로 할당하지 않고 우선 values()에 ?를 할당해서 보안성을 높여줍니다. 그리고 할당한 SQL문을 pstmt에 연결하고 setString으로 각각 칼럼의 데이터 값을 매개변수로 전달받았던 dto객체의 값의 데이터로 순서에 유의해서 할당해줍니다.
이제 할당한 값들이 제대로 세팅되도록 실행 명령어인 executeUpdate()를 호출하고 마지막으로 finally블록에서 사용한 모든 자원들을 닫아서 프로그램을 마칩니다.
SQL SELECT DISTINCT 예제
아래 예제는 전체 product의 maker들을 확인하는 예제로 중복은 제외한 채 출력되도록 합니다.
1
SELECT DISTINCT MAKER FROM PRODUCT;
sql문을 보시면 SELECT뒤에 DISTINCT를 사용했습니다. 이를 통해 원하는 데이터들을 중복 없이 조회할 수 있습니다.
자바 프로그램에서 살펴보면 아래와 같은 코드를 짤 수 있습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public ArrayList<String> getMakerKindList() throws SQLException {
ArrayList<String> list = new ArrayList<String>();
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(url, username, password);
String sql = "select distinct maker from product";
pstmt = con.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
list.add(rs.getString(1));
}
}finally {
closeAll(rs, pstmt, con);
}
return list;
}
일반적인 SELECET SQL문과 다른 부분은 리스트에 원하는 컬럼을 rs.getString(1)로 반환받는 부분인데, 여기서 요구한 항목이 maker 하나 뿐이기에 column이 1개만 존재하여 getString()안에 1을 넣어줍니다.
다른 조건이 없기에 WHERE문과 ?는 사용할 필요가 없고 하나의 행씩 결과를 조회하기에 while()문 안에 rs.next()로 전체 행을 조회해서 리스트에 해당 값을 추가합니다.
출력 결과를 보시면 id나 maker 이름을 기준으로 정렬되어 출력되지 않고 랜덤하게 출력되는 것으로 보이는데 정렬되어 출력되는것을 원하신다면 ORDER BY를 추가해서 실행하시면 됩니다.
SQL - BETWEEN 예제
전체 product의 중 price가 1300 이상이고 1700 이하인 product들을 출력하는 예제입니다.
sql문을 확인해보겠습니다.
1
2
3
4
5
SELECT *
FROM product
WHERE price
BETWEEN 1300 AND 1700
ORDER BY price DESC;
위처럼 WHERE조건문에서 BETWEEN A AND B 를 사용하셔도 되고 BETWEEN을 사용하지 않으신다면 직접 조건을 WHERE price >= 1300 AND price <= 1700과 같이 작성하셔도 됩니다.
이제 자바로 작성해보겠습니다.
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
public ArrayList<ProductDTO> findProductListByPriceOrderByPriceDesc(int lowPrice, int highPrice) throws SQLException {
ArrayList<ProductDTO> list = new ArrayList<ProductDTO>();
ProductDTO dto = new ProductDTO();
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(url, username, password);
StringBuilder sql = new StringBuilder();// sql문이 길 경우 자원의 효율적 사용
sql.append("SELECT * ");
sql.append("FROM product ");
sql.append("WHERE price BETWEEN ? AND ? ");// 보안성을 위해 ? 사용
sql.append("ORDER BY price DESC");
pstmt = con.prepareStatement(sql.toString());// toString() 필요
pstmt.setInt(1, lowPrice);
pstmt.setInt(2, highPrice);
rs=pstmt.executeQuery();
while(rs.next()) {
list.add(new ProductDTO(rs.getString(1),rs.getString(2),rs.getString(3),rs.getInt(4)));
}
}finally {
closeAll(rs, pstmt, con);
}
return list;
}
전에 배운 부분과는 다르게 StringBuilder를 사용해서 진행했습니다. 물론 String으로 진행해도 되지만 변화를 주는 등 다른 값을 생성하기에 String은 imuutable(불변) 특성을 갖고 있어서 StringBuilder를 사용하면 보다 효율적인 자원 사용이 가능합니다.
조심해야할 부분은 SQL문의 컬럼을 구분하기 위해 각각의 append()문의 마지막에 공백을 넣어주는 점입니다. 그리고 이렇게 생성한 값을 pstmt 참조변수로 실행하는데 앞에서 StringBuilder로 생성한 변수 sql자체는 String이 아니므로 toString()을 사용해서 실행하도록 합니다.
다음으로 setInt로 미리 SQL문에서 지정한 ? 값에 각각 lowPrice와 highPrice를 사용해서 보안성을 보장하도록 합니다.
그리고 while loop를 통해 list 실행 결과를 새로운 객체에서 rs.getString()으로 각 컬럼의 데이터들을 저장한 뒤 list를 반환해주면 해당 메서드는 작동하게 됩니다. 메인 메서드 안에서 작동되는 코드를 살펴보면 아래와 같습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import java.sql.SQLException;
import java.util.ArrayList;
import model.ProductDAO;
public static void main(String[] args) {
try {
ProductDAO dao = new ProductDAO();
ArrayList<String> list = dao.getMakerKindList();
for(int i=0; i<list.size();i++) {
System.out.println(list.get(i));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
SEQUENCE
시퀸스에 대해 알아보도록 하겠습니다.
DB마다 데이터에 유일값(id와 같은 PRIMARY KEY)을 1씩 증가하면서 생성해주는 객체가 있는데, Oracle에서는 이를 Sequence라고 합니다.
테이블과는 독립적인 구조를 지니는데 순차적으로 증가하며 유일한 값을 생성하기 위한 객체입니다.
주로 primary key( unique + not null ) 를 생성하기 위해 사용합니다.
아래의 기능들이 존재하지만 모두 다루지는 않을 예정이며 이런 기능들이 있다는 정도로만 알고 넘어가도록 하겠습니다.
CREATE SEQUENCE 시퀀스명START WITH 시작번호INCREMENT BY 증가값MAXVALUE 최대값MINVALUE 최소값CYCLE or NOCYCLENOCACHE
Oracle dual table
: 오라클에서 제공하는 기본 테이블입니다.
컬럼 하나만 존재하며 주로 시퀀스, 날짜함수, 산술연산에 사용합니다.
system Admin 계정에서 관리하며 수정이나 삭제 등의 조작은 불가능합니다.
시퀀스 예제
시퀀스를 생성해보겠습니다. 시퀀스명은 test_seq로 하겠습니다.
1
CREATE SEQUENCE test_seq;
삭제는 아래와 같습니다.
1
DROP SEQUENCE test_seq;
오라클에서 제공하는 DUAL TABLE을 활용해보겠습니다.
1
2
3
4
5
6
-- dual table을 조회
SELECT * FROM DUAL;
-- 연산 시 사용
SELECT 2*5 FROM DUAL;
-- 현재 시간 SYSDATE
SELECT SYSDATE FROM DUAL;
nextval은 시퀀스를 사용하면서 항상 접하게 될 키워드입니다. 다음 시퀀스 값이 반환됩니다. DUAL TABLE을 이용해 sequence 값을 확인할 수 있습니다.
1
2
-- 시퀀스명.NEXTVAL -> 시퀀스 다음 값을 생성
SELECT test_seq.nextval FROM DUAL;
START WITH 를 이용해 특정 숫자부터 sequence 값을 시작하도록 합니다.
-- DROP한 test_seq 시퀀스명으로 다시 시퀀스를 생성해본다(옵션을 start with 7 적용해서 생성)
CREATE SEQUENCE test_seq START WITH 7;
JAVA 예제
등록 예제
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public void register(GuestBookDTO dto) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection(url, username, password);
String sql = "insert into guestbook(guestbook_no, title, content) values(guestbook_seq.NEXTVAL,?,?)";
pstmt = con.prepareStatement(sql.toString());
pstmt.setString(1, dto.getTitle());
pstmt.setString(2, dto.getContent());
pstmt.executeUpdate();
} finally {
closeAll(pstmt, con);
}
}
String 타입의 sql 참조변수를 생성하는 과정을 보시면 values()에 값을 입력할 때 guestbook_seq.NEXTVAL을 사용합니다.
아래는 등록 메서드를 실행하는 test 클래스입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package test;
import java.sql.SQLException;
import model.GuestBookDAO;
import model.GuestBookDTO;
public class TestGuestBookDAO1 {
public static void main(String[] args) {
try {
GuestBookDAO dao = new GuestBookDAO();
GuestBookDTO dto = new GuestBookDTO("불금", "잘먹고 잘놀자");
dao.register(dto);
System.out.println("방명록에 글 등록");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
출력 예제
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public ArrayList<GuestBookDTO> getAllGuestBookList() throws SQLException {
ArrayList<GuestBookDTO> list = new ArrayList<GuestBookDTO>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(url, username, password);
String sql = "SELECT * FROM guestbook ORDER BY guestbook_no DESC";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
list.add(new GuestBookDTO(rs.getInt(1), rs.getString(2), rs.getString(3)));
}
} finally {
closeAll(rs, pstmt, con);
}
return list;
}
출력 기능을 정의하는 sql문은 길이가 짧기 때문에 String 타입으로 선언을 했습니다. PRIMARY KEY인 guestbook_no를 내림차순으로 출력하기 위해 ORDER BY ~ DESC를 사용했고 리스트에 각 컬럼 데이터를 추가해서 마지막으로 리스트를 반환하며 마칩니다.
아래는 출력 메서드가 실행되는 Test 클래스입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package test;
import java.sql.SQLException;
import java.util.ArrayList;
import model.GuestBookDAO;
import model.GuestBookDTO;
public class TestGuestBookDAO2 {
public static void main(String[] args) throws SQLException {
// guestbook에 insert(guestbook_no는 sequence로 자동 발급)
try {
GuestBookDAO dao = new GuestBookDAO();
// 최근 글부터 출력되도록 조회한다(guestbook_no 내림차순)
ArrayList<GuestBookDTO> list = dao.getAllGuestBookList();
for(int i=0; i<list.size(); i++) {
System.out.println(list.get(i));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
이번 수업에서는 예제를 풀어보며 SQL문이 더 익숙해지도록 다양하게 사용해보고, sequence에 대해 알아보는 시간을 가졌습니다. 다음 시간에는 트랜잭션과 서브쿼리에 대해 알아보도록 하겠습니다.
댓글남기기