CODE [day_03] DB / 05. SEQUENCE 시퀀스(SQL 쿼리, java JDBC)

SQL

GUESTBOOK Table 생성

CREATE TABLE guestbook(
  guestbook_no	NUMBER			PRIMARY KEY,
  title			VARCHAR2(100)	NOT NULL,
  content		VARCHAR2(1000) 	NOT NULL
);

 

guestbook_seq Sequence 생성

CREATE SEQUENCE guestbook_seq;

 

INSERT

INSERT INTO guestbook(guestbook_no, title, content) VALUES(guestbook_seq.nextval, '즐거운 8월', '열공');

 

 

---

JAVA JDBC

TestDAO class (main class)

1. 새로운 DB 등록 - register method

2. 등록된 DB 출력 (최근 등록된 순으로 출력) - getAllGuestBookListOrderByNoDesc method

/TestGuestBookDAO.java

package test;

import java.util.ArrayList;

import model.GuestBookDAO;
import model.GuestBookDTO;

public class TestGuestBookDAO {
	public static void main(String[] args) {
		try {
			GuestBookDTO dto = new GuestBookDTO("test", "test 구문입니다.");
			GuestBookDAO dao = new GuestBookDAO();
			
			// 새로운 DB 등록
			dao.register(dto);
			System.out.println(dto + " 등록완료!\n");
			
			// 등록된 DB 출력 - 최근 등록된 순으로 출력
			ArrayList<GuestBookDTO> list = dao.getAllGuestBookListOrderByNoDesc();
			System.out.println("GuestBook TABLE에 등록된 정보 리스트");
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}//main
}
 

---

DAO ( Data Access Object ) class

: 데이베이스 연동 로직을 정의한 객체

 

/GuestBookDAO.java

package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class GuestBookDAO {
	private String driver = "oracle.jdbc.OracleDriver";
	private String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	private String user = "scott";
	private String password = "tiger";
	
	//1. 드라이버 로딩
	public GuestBookDAO() throws ClassNotFoundException {
		Class.forName(driver);
	}
	
	//closeAll method 1 - ResultSet 있는 ver
	public void closeAll(PreparedStatement pstmt, Connection con) throws SQLException {
		if(pstmt != null)
			pstmt.close();
		if(con != null)
			con.close();
	}
	
	//closeAll method 2 - ResultSet 없는 ver  // OverLoading
	public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) throws SQLException {
		if(rs != null)
			rs.close();
		closeAll(pstmt, con);
	}

	
	//register method
	public void register(GuestBookDTO dto) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			//2. sql문 정의
			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO guestbook(guestbook_no, title, content) ");
			sb.append("VALUES(guestbook_seq.nextval, ?, ?)");
			
			//2. Connection
			con = DriverManager.getConnection(dbUrl, user, password);
			
			//3. prepareStatement 정의 
			pstmt = con.prepareStatement(sb.toString());
			
			//4. sql문 완성 (? 채우기)
			pstmt.setString(1, dto.getTitle());
			pstmt.setString(2, dto.getContent());
			
			//5. sql 실행
			pstmt.executeUpdate();
			
		} finally {
			closeAll(pstmt, con);
		}
	}


	//getAllGuestBookListOrderByNoDesc method
	public ArrayList<GuestBookDTO> getAllGuestBookListOrderByNoDesc() throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<GuestBookDTO> list = new ArrayList<GuestBookDTO>();
		
		try {
			con = DriverManager.getConnection(dbUrl, user, password);
			
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT guestbook_no, title, content ");
			sb.append("FROM guestbook ");
			sb.append("ORDER BY guestbook_no DESC");
			
			pstmt = con.prepareStatement(sb.toString());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				GuestBookDTO dto = new GuestBookDTO();
				dto.setGuestBookNo(rs.getInt("guestbook_no"));
				dto.setTitle(rs.getString("title"));
				dto.setContent(rs.getString("content"));
				list.add(dto);
			}
		} finally {
			closeAll(rs, pstmt, con);
		}
		return list;
	}
}

 

---

DTO ( Data Transfer Object ) class

: 정보를 저장하고 (계층 간/ 원격으로)전송하기 위한 class

 

/GuestBookDTO.java

package model;
//DTO : Data Transfer Object
//VO : Value Object
public class GuestBookDTO {
	private int guestBookNo;
	private String title;
	private String content;
	
	// 1. 기본 생성자
	public GuestBookDTO() {
		super();
	}
	
	// 2. INSERT용 생성자 <- sequence로 자동 입력되어 별도의 입력이 필요 없기 때문
	public GuestBookDTO(String title, String content) {
		super();
		this.title = title;
		this.content = content;
	}

	// 3. 인스턴스 변수 다 있는 생성자
	public GuestBookDTO(int guestBookNo, String title, String content) {
		super();
		this.guestBookNo = guestBookNo;
		this.title = title;
		this.content = content;
	}

	@Override
	public String toString() {
		return "GuestBookDTO [guestBookNo=" + guestBookNo + ", title=" + title + ", content=" + content + "]";
	}

	public int getGuestBookNo() {
		return guestBookNo;
	}

	public void setGuestBookNo(int guestBookNo) {
		this.guestBookNo = guestBookNo;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}
	
}