학원/DB
21-12-23 JDBC
링규
2021. 12. 23. 11:25
VIEW 생성
-> 조인 처럼 긴 SQL문장을 DAO 파일에 담기 어렵기 때문에 VIEW를 생성해서 처리하면 좋다
CREATE OR REPLACE VIEW RENTDETAIL AS
SELECT TO_CHAR(A.RENTDATE, 'YYYY-MM-DD') AS RENTDATE, A.NUM AS RENTNUM, C.MEMBERNUM AS MEMBERNUM, C.NAME AS MEMBERNAME,
B.RENTPRICE-A.DISCOUNT AS PRICE, B.NUM AS BOOKNUM, B.SUBJECT AS SUBJECT
FROM RENTLIST A, BOOKLIST B, MEMBERLIST C
WHERE A.BOOKNUM=B.NUM AND A.MEMBERNUM = C.MEMBERNUM
ORDER BY A.NUM DESC;
SELECT * FROM RENTDETAIL;
위처럼 VEIW를 만들면 DAO 파일 메서드 내에 SQL을
String sql = "SELECT TO_CHAR(A.RENTDATE, 'YYYY-MM-DD') AS RENTDATE, A.NUM AS RENTNUM, "+
"C.MEMBERNUM AS MEMBERNUM, C.NAME AS MEMBERNAME, B.RENTPRICE-A.DISCOUNT AS PRICE, " +
"B.NUM AS BOOKNUM, B.SUBJECT AS SUBJECT FROM RENTLIST A, BOOKLIST B, MEMBERLIST C " +
"WHERE A.BOOKNUM=B.NUM AND A.MEMBERNUM = C.MEMBERNUM "+
"ORDER BY A.NUM DESC";
String sql = "SELECT * FROM RENTDETAIL";
위에서 아래와 같이 줄일 수 있다. 또 WHERE문을 덧붙여 검색 기능에 활용할 수 도 있다!
전문
*DB connection, close용 클래스
package jdbc05;
import java.sql.*;
public class DBManager {
static String driver = "oracle.jdbc.driver.OracleDriver";
static String url = "jdbc:oracle:thin:@localhost:1521:xe";
static String id = "scott";
static String pwd = "tiger";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, id, pwd);
} catch (ClassNotFoundException e) { e.printStackTrace();
} catch (SQLException e) { e.printStackTrace();
}
return conn;
}
public static void close(Connection con, PreparedStatement pstmt, ResultSet rs) {
try {
if(con!=null) con.close();
if(pstmt!=null) pstmt.close();
if(rs!=null) rs.close();
} catch (SQLException e) {e.printStackTrace();
}
}
}
*DTO
public class RentDto {
private String rentdate;
private int num;
private int booknum;
private int membernum;
private int discount;
public String getRentdate() {
return rentdate;
}
public void setRentdate(String rentdate) {
this.rentdate = rentdate;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public int getBooknum() {
return booknum;
}
public void setBooknum(int booknum) {
this.booknum = booknum;
}
public int getMembernum() {
return membernum;
}
public void setMembernum(int membernum) {
this.membernum = membernum;
}
public int getDiscount() {
return discount;
}
public void setDiscount(int discount) {
this.discount = discount;
}
}
*DAO
import java.sql.*;
import java.util.ArrayList;
import jdbc04.MemberDto;
public class RentDao {
private RentDao() {}
private static RentDao itc = new RentDao();
public static RentDao getInstance() { return itc; }
//싱글턴 객체
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
public ArrayList<RentDto> select(){
ArrayList<RentDto> list = new ArrayList<>();
con = DBManager.getConnection();
String sql = "SELECT TO_CHAR(RENTDATE,'YYYY-MM-DD') AS RENTDATE, "
+ "NUM, BOOKNUM, MEMBERNUM, DISCOUNT FROM RENTLIST ORDER BY NUM DESC";
try {
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
RentDto rdto = new RentDto();
//rentdate 필드 : Date 형식 => DTO의 rentDate : String으로 변환필요
//=> Select 문 안에서 to_Char() 함수 사용
rdto.setRentdate(rs.getString("rentdate"));
rdto.setNum(rs.getInt("num"));
rdto.setBooknum(rs.getInt("booknum"));
rdto.setMembernum(rs.getInt("membernum"));
rdto.setDiscount(rs.getInt("discount"));
list.add(rdto);
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return list;
}
public boolean checkBooknum(int booknum) {
con = DBManager.getConnection();
boolean state = false;
String sql = "SELECT * FROM RENTLIST WHERE BOOKNUM = ?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, booknum);
rs = pstmt.executeQuery();
if(rs.next()) {
state = true;
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return state;
}
public boolean checkMembernum(int membernum) {
con = DBManager.getConnection();
boolean state = false;
String sql = "SELECT * FROM RENTLIST WHERE MEMBERNUM = ?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, membernum);
rs = pstmt.executeQuery();
if(rs.next()) {
state = true;
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return state;
}
public int insert( RentDto rdto ){
int result = 0;
con = DBManager.getConnection();
String sql = "insert into rentlist values(sysdate, rent_seq.nextVal, ? , ? , ? )";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, rdto.getBooknum() );
pstmt.setInt(2, rdto.getMembernum() );
pstmt.setInt(3, rdto.getDiscount() );
result = pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace();
} finally { DBManager.close( con, pstmt, rs ); }
return result;
}
public RentDto getRent(int num) {
RentDto rdto = null;
con = DBManager.getConnection();
String sql = "SELECT TO_DATE(RENTDATE,'YYYY-MM-DD') AS RENTDATE, NUM, BOOKNUM,"
+ "MEMBERNUM, DISCOUNT FROM RENTLIST WHERE NUM = ?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next()) {
rdto = new RentDto();
rdto.setRentdate(rs.getString("rentdate"));
rdto.setBooknum(rs.getInt("booknum"));
rdto.setMembernum(rs.getInt("membernum"));
rdto.setDiscount(rs.getInt("discount"));
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return rdto;
}
public int update(RentDto rdto) {
int result = 0;
con = DBManager.getConnection();
String sql = "UPDATE RENTLIST SET RENTDATE=TO_DATE(''||?||'', 'YYYYMMDD'), BOOKNUM=?, MEMBERNUM=?, DISCOUNT=? WHERE NUM =?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, rdto.getRentdate());
pstmt.setInt(2, rdto.getBooknum());
pstmt.setInt(3, rdto.getMembernum());
pstmt.setInt(4, rdto.getDiscount());
pstmt.setInt(5, rdto.getNum());
result = pstmt.executeUpdate();
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return result;
}
public int delete(int num) {
int result = 0;
con = DBManager.getConnection();
String sql = "DELETE FROM RENTLIST WHERE NUM = ?";
try {
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, num);
result = pstmt.executeUpdate();
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con,pstmt,rs);
}
return result;
}
public ArrayList<RentDetailDto> selectAll() {
ArrayList<RentDetailDto> list = new ArrayList<>();
con = DBManager.getConnection();
String sql = "SELECT * FROM RENTDETAIL";
try {
pstmt=con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
RentDetailDto rdto = new RentDetailDto();
rdto.setRentdate(rs.getString("rentdate"));
rdto.setRentnum(rs.getInt("rentnum"));
rdto.setMembernum(rs.getInt("membernum"));
rdto.setMembername(rs.getString("membername"));
rdto.setPrice(rs.getInt("price"));
rdto.setBooknum(rs.getInt("booknum"));
rdto.setSubject(rs.getString("subject"));
list.add(rdto);
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs);
}
return list;
}
}
*Driver
package jdbc05;
import java.util.ArrayList;
import java.util.Scanner;
public class RentDriver {
public static void main(String[] args) {
while(true){
Scanner sc = new Scanner(System.in);
System.out.println("\n---메뉴선택---");
System.out.print("1.데이터열람 2.데이터추가 3.데이터수정 4.데이터삭제");
System.out.print("5.데이터검색 6.프로그램 종료.>> 메뉴선택 : ");
String choice = sc.nextLine();
if(choice.equals("6")) break;
switch(choice) {
case"1":
select();
break;
case"2":
insert();
break;
case"3":
update();
break;
case"4":
delete();
break;
case"5" :
selectAll();
break;
default : System.out.println("메뉴 선택이 잘못되었습니다.");
}
}
System.out.println("프로그램을 종료합니다.");
}
private static void selectAll() {
Scanner sc = new Scanner(System.in);
RentDao rdao = RentDao.getInstance();
ArrayList<RentDetailDto> list = rdao.selectAll();
System.out.println("날짜\t\t번호\t회원번호\t성명\t\t대여금액\t도서번호\t도서제목");
System.out.println("------------------------------------------------------------------------------------------------------------------");
for(RentDetailDto dto : list) {
System.out.printf("%s\t%d\t%d\t\t%s\t\t%d\t\t%d\t\t%s\n",
dto.getRentdate(), dto.getRentnum(), dto.getMembernum(), dto.getMembername(), dto.getPrice(), dto.getBooknum(), dto.getSubject());
}
}
private static void delete() {
Scanner sc = new Scanner(System.in);
RentDao rdao = RentDao.getInstance();
RentDto rdto = null;
System.out.print("삭제할 대여 번호를 입력하세요 : ");
String input;
while(true) {
input = sc.nextLine();
if(input.equals("")) System.out.println("대여 번호 입력은 필수 입니다.");
else break;
}
int num = Integer.parseInt(input);
rdto = rdao.getRent(num);
if(rdto == null) {
System.out.println("입력한 대여 번호가 존재하지 않습니다.");
return;
}
int result = rdao.delete(num);
if(result==1) System.out.println("\n레코드 삭제 성공");
else System.out.println("\n레코드 삭제 실패");
}
private static void update() {
Scanner sc = new Scanner(System.in);
RentDao rdao = RentDao.getInstance();
RentDto rdto = null;
//대여번호
System.out.print("수정할 대여 번호를 입력하세요 : ");
String input;
while(true) {
input = sc.nextLine();
if(input.equals("")) System.out.println("대여 번호 입력은 필수 입니다.");
else break;
}
int num = Integer.parseInt(input);
rdto = rdao.getRent(num);
if(rdto == null) {
System.out.println("입력한 대여 번호가 존재하지 않습니다.");
return;
}
//대여일
System.out.printf("수정할 날짜를 입력하세요(YYYY-MM-DD) (기존 대여일 : %s) : ", rdto.getRentdate());
String rentdate = sc.nextLine();
if(!rentdate.equals("")) rdto.setRentdate(rentdate);
//도서번호
System.out.printf("수정할 도서 번호를 입력하세요 (기존 도서 번호 : %d) : ", rdto.getBooknum());
int booknum = 0;
while(true) {
input = sc.nextLine();
if(input.equals("")) break;
else {
booknum = Integer.parseInt(sc.nextLine());
boolean state = rdao.checkBooknum(booknum);
if (state) { rdto.setBooknum(booknum); break; }
else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");
}
}
//회원번호
int membernum = 0;
System.out.printf("수정할 회원 번호를 입력하세요 (기존 회원 번호 : %d): ", rdto.getMembernum());
while(true) {
input = sc.nextLine();
if(input.equals("")) break;
else {
membernum = Integer.parseInt(sc.nextLine());
boolean state = rdao.checkMembernum(membernum);
if (state==true) { rdto.setMembernum(membernum); break; }
else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");
}
}
//할인
System.out.printf("수정할 할인금액을 입력하세요 (기존 할인 금액 : %d) : ", rdto.getDiscount());
input = sc.nextLine();
if(!input.equals("")) rdto.setDiscount(Integer.parseInt(input));
int result = rdao.update(rdto);
if(result==1) System.out.println("\n레코드 수정 성공");
else System.out.println("\n레코드 수정 실패");
}
//삽입 메소드
private static void insert() {
Scanner sc = new Scanner(System.in);
RentDao rdao = RentDao.getInstance();
RentDto rdto = new RentDto();
//rentdate는 오라클의 sysdate 또는 default 값을 이용
//테이블 생성시 제약조건으로 설정되어 있으면 굳이 하지 않아도 됨
System.out.print("대여할 도서의 번호를 입력하세요 : ");
int booknum = 0;
while(true) {
booknum = Integer.parseInt(sc.nextLine());
boolean state = rdao.checkBooknum(booknum);
if (state==true) break;
else System.out.print("도서가 존재하지 않습니다. 다시 입력해 주세요 : ");;
}
rdto.setBooknum(booknum);
System.out.print("회원 번호를 입력하세요 : ");
int membernum = 0;
while(true) {
membernum = Integer.parseInt(sc.nextLine());
boolean state = rdao.checkMembernum(membernum);
if (state==true) break;
else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");;
}
rdto.setMembernum(membernum);
System.out.print("할인 금액을 입력하세요 : ");
String input = sc.nextLine();
int discount = 0;
if(!input.equals("")) discount = Integer.parseInt(input);
rdto.setDiscount(discount);
int result = rdao.insert(rdto);
if(result==1) System.out.println("\n레코드 추가 성공");
else System.out.println("\n레코드 추가 실패");
}
//조회 메소드
private static void select() {
Scanner sc = new Scanner(System.in);
RentDao rdao = RentDao.getInstance();
ArrayList<RentDto> list = rdao.select();
System.out.println("날짜\t\t번호\t도서번호\t회원번호\t할인금액");
System.out.println("---------------------------------------------------------------------------");
for(RentDto dto : list) {
System.out.printf("%s\t%d\t%d\t\t%d\t\t%d\n",
dto.getRentdate(), dto.getNum(), dto.getBooknum(), dto.getMembernum(), dto.getDiscount());
}
}
}
update 가 에러는 안나고 계속 실패하는데 원인을 모르겠어서 나중에 수정 예정,,,,
->> update set을 할 때 모든 컬럼 다 입력
