DataBase/MySQL
[MySQL] JDBC
hvoon
2022. 9. 9. 16:37
1. booklist, memberlist, rentlist 테이블 생성
-아래 파일을 오라클에서 실행해 테이블 생성
2. rentdetail view 생성(sql 파일)
create view rentdetail as
select
date_format(a.rentdate,'%y-%m-%d') as rd,
a.num as rentnum,
c.name as mname,
b.subject,
b.rentprice,
a.discount,
(b.rentprice-a.discount) as rentprice2
from rentlist a, booklist b, memberlist c
where b.num=a.bnum and c.num=a.mnum
order by rentnum desc;3. DBManager
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBManager {
private static String driver="com.mysql.cj.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/scott";
private static String id="root";
private static String pw="adminuser";
public static Connection getConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, id, pw);
} catch (ClassNotFoundException e) {e.printStackTrace();
} catch (SQLException e) {e.printStackTrace();}
return con;
}
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();}
}
}4. DTO
public class RentDto {
private String rentdate;
private int num;
private int bnum;
private int mnum;
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 getBnum() {
return bnum;
}
public void setBnum(int bnum) {
this.bnum = bnum;
}
public int getMnum() {
return mnum;
}
public void setMnum(int mnum) {
this.mnum = mnum;
}
public int getDiscount() {
return discount;
}
public void setDiscount(int discount) {
this.discount = discount;
}
}
public class RentDetailDto {
private String rentdate;
private int num;
private int mnum;
private String mname;
private int bnum;
private String subject;
private int rentprice;
private int discount;
private int rentprice2;
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 getMnum() {
return mnum;
}
public void setMnum(int mnum) {
this.mnum = mnum;
}
public String getMname() {
return mname;
}
public void setMname(String mname) {
this.mname = mname;
}
public int getBnum() {
return bnum;
}
public void setBnum(int bnum) {
this.bnum = bnum;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public int getRentprice() {
return rentprice;
}
public void setRentprice(int rentprice) {
this.rentprice = rentprice;
}
public int getDiscount() {
return discount;
}
public void setDiscount(int discount) {
this.discount = discount;
}
public int getRentprice2() {
return rentprice2;
}
public void setRentprice2(int rentprice2) {
this.rentprice2 = rentprice2;
}
}5. DTO
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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;
// select
public ArrayList<RentDetailDto> selectAll() {
ArrayList<RentDetailDto> list= new ArrayList<RentDetailDto>();
String sql="select *from rentdetail";
con=DBManager.getConnection();
try {
pstmt=con.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
RentDetailDto rdto = new RentDetailDto();
rdto.setRentdate( rs.getString("rd") );
rdto.setNum( rs.getInt("rentnum"));
rdto.setMname( rs.getString("mname") );
rdto.setSubject( rs.getString("subject") );
rdto.setRentprice( rs.getInt("rentprice") );
rdto.setDiscount( rs.getInt("discount") );
rdto.setRentprice2( rs.getInt("rentprice2") );
list.add(rdto);
}
} catch (SQLException e) {e.printStackTrace();
} finally {DBManager.close(con,pstmt,rs);}
return list;
}
// insert
public boolean checkBookNum(int bnum) {
boolean result = false;
String sql = "select * from booklist where num=?";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, bnum);
rs = pstmt.executeQuery();
if( rs.next() ) result = true;
} catch (SQLException e) {
} finally { DBManager.close(con, pstmt, rs); }
return result;
}
public boolean checkMemberNum(int mnum) {
boolean result = false;
String sql = "select * from memberlist where num=?";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, mnum);
rs = pstmt.executeQuery();
if( rs.next() ) result = true;
} catch (SQLException e) {
} finally { DBManager.close(con, pstmt, rs); }
return result;
}
public int insertRent(RentDto rdto) {
int result = 0;
String sql = "insert into rentlist( bnum, mnum, discount ) values( ?, ?, ?)";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, rdto.getBnum() );
pstmt.setInt(2, rdto.getMnum() );
pstmt.setInt(3, rdto.getDiscount() );
result = pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace();
} finally {DBManager.close( con, pstmt, rs ); }
return result;
}
// update
public RentDetailDto getRentDetail(int num) {
RentDetailDto rdto = null;
String sql = "select * from rentdetail where rentnum = ?";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if( rs.next() ) {
rdto = new RentDetailDto();
rdto.setRentdate( rs.getString("rd") );
rdto.setNum( rs.getInt("rentnum") );
rdto.setSubject( rs.getString("subject") );
rdto.setMname( rs.getString("mname") );
rdto.setRentprice( rs.getInt("rentprice") );
rdto.setDiscount( rs.getInt("discount") );
rdto.setRentprice2( rs.getInt("rentprice2") );
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs); }
return rdto;
}
public RentDto getRent(int num) {
RentDto rdto = null;
String sql = "select date_format(rentdate, '%Y-%m-%d') as rd,"
+ " num, bnum, mnum, discount "
+ " from rentlist where num = ?";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if( rs.next() ) {
rdto = new RentDto();
rdto.setRentdate( rs.getString("rd") );
rdto.setNum( rs.getInt("num") );
rdto.setBnum( rs.getInt("bnum") );
rdto.setMnum( rs.getInt("mnum") );
rdto.setDiscount( rs.getInt("discount") );
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBManager.close(con, pstmt, rs); }
return rdto;
}
public int updateRent(RentDto rdto) {
int result = 0;
String sql = "update rentlist set rentdate = "
+ " str_to_date(concat('',?,''), '%Y-%m-%d') , "
+ " bnum =?, mnum=?, discount=? where num=?";
con = DBManager.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, rdto.getRentdate() );
pstmt.setInt(2, rdto.getBnum() );
pstmt.setInt(3, rdto.getMnum() );
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;
}
// delete
public int deleteRent(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;
}
}6. Main
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Scanner;
public class RentMain {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
while(true) {
System.out.println("\n---메뉴 선택---");
System.out.printf("1. 데이터 열람 2. 데이터 추가 ");
System.out.printf("3. 데이터 수정 4. 데이터 삭제 ");
System.out.printf("5. 프로그램 종료. >> 메뉴선택: ");
String choice=sc.nextLine();
if(choice.equals("5")) break;
switch(choice) {
case "1":
select(); break;
case "2":
insert(sc); break;
case "3":
update(sc); break;
case "4":
delete(sc); break;
default: System.out.println("메뉴 선택이 잘못되었음");
}
}
System.out.println("프로그램 종료");
}
private static void delete(Scanner sc) {
RentDao rdao=RentDao.getInstance();
RentDto rdto=null;
System.out.printf("삭제할 대여건의 number을 입력: ");
String input;
while(true) {
input=sc.nextLine();
if(input.equals("")) System.out.println("number 입력 필수");
else break;
}
int num=Integer.parseInt(input);
rdto=rdao.getRent(num);
if(rdto==null) {
System.out.println("입력한 number의 대여건 없음");
return;
}
rdao.deleteRent(num);
}
private static void update(Scanner sc) {
RentDao rdao=RentDao.getInstance();
System.out.printf("수정할 대여변호: ");
int num=0;
String input="";
RentDetailDto rddto=null;
RentDto rdto=null;
while(true) {
input=sc.nextLine();
if(input.equals("")) {
System.out.printf("수정할 대여번호: ");
continue;
}
num=Integer.parseInt(input);
rddto=rdao.getRentDetail(num);
rdto=rdao.getRent(num);
if(rddto==null)
System.out.println("입력한 number의 대여건이 없음. 다시 입력: ");
else break;
}
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date d=null;
while(true) {
System.out.printf("기존 날짜: %s\t수정할 날짜 입력(yyyy-MM-dd) ->",rddto.getRentdate());
input=sc.nextLine();
if(input.equals("")) break;
try {
d=sdf.parse(input);
// java.sql.Date s=new java.sql.Date(d.getTime());
// rdto.setRentdate(s);
rdto.setRentdate(input);
break;
} catch (ParseException e) {e.printStackTrace();
System.out.println("날짜를 입력양식에 맞춰서 다시 입력(yyyy-MM-dd): ");
}
}
int bnum=0;
System.out.printf("기존도서제목: %s\t수정할 도서번호 입력 -> ",rddto.getSubject());
while(true) {
input=sc.nextLine();
if(input.equals("")) break;
bnum=Integer.parseInt(input);
boolean state=rdao.checkBookNum(bnum);
if(state) {
rdto.setBnum(bnum);
break;
}
else System.out.printf("입력한 도서번호의 도서가 존재하지 않음. 다시 입력: ");
}
int mnum=0;
System.out.printf("회원이름: %s\t수정할 회원 이름 -> ",rddto.getMname());
while(true) {
input=sc.nextLine();
if(input.equals("")) break;
mnum=Integer.parseInt(input);
boolean state=rdao.checkBookNum(mnum);
if(state) {
rdto.setMnum(mnum);
break;
}
else System.out.printf("입력한 회원번호의 도서가 존재하지 않음. 다시 입력: ");
}
System.out.printf("할인금액: ");
String discount=sc.nextLine();
if(!discount.equals("")) rdto.setDiscount(Integer.parseInt(discount));
int result=rdao.updateRent(rdto);
if(result==1) System.out.println("레코드 수정 성공");
else System.out.println("레코드 수정 실패");
}
private static void insert(Scanner sc) {
RentDao rdao=RentDao.getInstance();
RentDto rdto=new RentDto();
int bnum=0;
String input="";
System.out.printf("대여할 도서번호: ");
while(true) {
input=sc.nextLine();
if(input.equals("")) {
System.out.println("대여할 도서번호: ");
continue;
}
bnum=Integer.parseInt(input);
boolean state=rdao.checkBookNum(bnum);
if(state) break;
else System.out.printf("도서가 존재하지 않음. 다시 입력: ");
}
System.out.printf("대여 회원번호: ");
int mnum=0;
while(true) {
input=sc.nextLine();
if(input.equals("")) {
System.out.println("대여 회원번호: ");
continue;
}
mnum=Integer.parseInt(input);
boolean state=rdao.checkMemberNum(mnum);
if(state) break;
else System.out.printf("회원의 대여도서가 존재하지 않음. 다시 입력: ");
}
rdto.setBnum(bnum);
rdto.setMnum(mnum);
System.out.printf("할인금액: ");
input=sc.nextLine();
int discount=0;
if(!input.equals("")) discount=Integer.parseInt(input);
rdto.setDiscount(discount);
int result=rdao.insertRent(rdto);
if(result==1) System.out.println("레코드 추가 성공");
else System.out.println("레코드 추가 실패");
}
private static void select() {
RentDao rdao=RentDao.getInstance();
ArrayList<RentDetailDto> list = rdao.selectAll();
System.out.printf("날짜\t\t순번\t\t회원이름\t대여금액");
System.out.println("\t할인금액\t실제대여료\t도서제목");
System.out.println("---------------------------------"
+ "---------------------------------------------------");
for(RentDetailDto rdto: list) {
System.out.printf("%s\t%d\t\t%s\t\t%d\t\t%d\t\t%d\t\t%s\n",
rdto.getRentdate(), rdto.getNum(), rdto.getMname(),
rdto.getRentprice(), rdto.getDiscount(),
rdto.getRentprice2(), rdto.getSubject());
}
}
}실행결과
