JDBC를 활용해서 테이블 두개를 가지고 와서 각각의 조회 추가 수정 삭제 기능 그리고 통합조회,비교,랜덤,정렬등의
기능을 구현해 봤습니다.
여러 기능들을 구현해 보느라 문장 수가 엄청나게 많아졌지만,
설계부터 구현까지 제 손으로 해보니 감회가 새로웠습니다.
SQL 생성
create sequence krmseq
increment by 1
start with 1
minvalue 1
maxvalue 100
nocache;
create table kicks( --구매정보
shoename varchar2(30) not null,
brand varchar2(30) not null,
price number not null,
launch_date date default '2010-01-01'
);
create table kream( --판매정보
no number primary key,
shoename varchar2(30) not null,
brand varchar2(30) not null,
resell number,
sizes number not null,
sell_date date default sysdate
);
insert into kicks values('yeezy350','adidas',4300,'2014/03/01');
insert into kicks values('yeezy750','adidas',3100,'2017/11/12');
insert into kicks values('yeezy950','adidas',5700,'2015/08/21');
insert into kicks values('jordan1','nike',8900,'1993/08/13');
insert into kicks values('jordan6','nike',2600,'2001/07/07');
insert into kicks values('masyard2','nike',9500,'2020/04/28');
insert into kream values(krmseq.nextval,'yeezy950','adidas',12300,265,'2021/3/4');
insert into kream values(krmseq.nextval,'jordan6','nike',5500,245,'2019/8/8');
insert into kream values(krmseq.nextval,'jordan1','nike',21400,280,'2022/9/14');
insert into kream values(krmseq.nextval,'masyard2','nike',50800,250,default);
insert into kream values(krmseq.nextval,'yeezy350','adidas',4700,295,default);
insert into kream values(krmseq.nextval,'yeezy750','adidas',1700,230,'2021/4/21');
commit;
JDBC Main
import java.util.ArrayList;
import java.util.Scanner;
public class KreamKicksMain {
KreamKicksDao kkdao = new KreamKicksDao();
Scanner sc = new Scanner(System.in);
KreamKicksMain(){
init();
}
private void init() {
while(true) {
System.out.println("===메뉴 선택===");
System.out.println("1.통합 조회");
System.out.println("2.Kicks 조회");
System.out.println("3.Kream 조회");
System.out.println("4.프로그램 종료");
System.out.print("번호 선택>>");
int menu =sc.nextInt();
switch(menu) {
case 1 :
AllMain();
break;
case 2 :
KicksMain();
break;
case 3 :
KreamMain();
break;
case 4 :
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default : break;
}
}
}
private void AllMain() {
while(true) {
System.out.println("=== 전체 통합 메뉴 선택 ===");
System.out.println("1.전체 조회\t 2.조건 조회");
System.out.println("3.조건 정렬\t 4.손익 계산");
System.out.println("5.이전 메뉴\t 6.프로그램 종료");
System.out.print("메뉴 선택>>");
int menu=sc.nextInt();
switch(menu) {
case 1 :
ArrayList<KreamKicksBean> kkList=kkdao.getAllKK();
showKreamKicks(kkList);
break;
case 2 :
getKKBySearch();
break;
case 3 :
alignAll();
break;
case 4 :
priceCheck();
break;
case 5 :
return;
case 6 :
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default :
System.out.println("1~6만 입력 가능합니다.");
break;
}
}
}
private void priceCheck() {
System.out.print("비교할 제품 1.제품명으로 검색하기 2.번호로 검색하기 >>");
String column=null;
int select=sc.nextInt();
String shoename=null;
int no=0;
switch(select) {
case 1 :
System.out.print("제품명 입력:");
shoename=sc.next();
column="shoename";
break;
case 2 :
System.out.print("제품번호 입력:");
no=sc.nextInt();
column="no";
break;
default :
System.out.println("1과 2만 선택가능합니다.");
return;
}
KreamKicksBean kkBean=kkdao.priceCheck(column,shoename,no);
if(kkBean.getNo()==0) {
System.out.println("찾는 항목이 존재하지 않습니다.");
return;
}
System.out.println("검색한 항목:");
System.out.println("번호/제품명/브랜드/사이즈/발매가/판매가/발매일/판매일");
System.out.println(kkBean);
System.out.print("결과:");
int result = kkBean.getResell()-kkBean.getPrice();
if(result>0) {
System.out.println(result+"원 이득");
} else {
System.out.println(-result+"원 손해");
}
}
private void alignAll() {
System.out.println("정렬할 항목을 선택하세요.");
System.out.print("번호:1 판매가:2 발매가:3 사이즈:4 판매일:5 번호입력>>");
int align_num=sc.nextInt();
System.out.println("정렬 방법을 선택하세요");
System.out.print("오름차순:1 내림차순:2 번호입력>>");
int align_way =sc.nextInt();
String column = null;
String way = null;
switch(align_num) {
case 1 : column="no";
break;
case 2 : column="resell";
break;
case 3 : column="price";
break;
case 4 : column="sizes";
break;
case 5 : column="sell_date";
break;
default :
System.out.println("1~4만 입력 가능합니다");
return;
}
switch(align_way) {
case 1 : way=" asc";
break;
case 2 : way=" desc";
break;
default :
System.out.println("1과 2만 입력 가능합니다");
return;
}
ArrayList<KreamKicksBean> kkList=kkdao.alignAll(column,way);
showKreamKicks(kkList);
}
private void getKKBySearch() {
System.out.println("1.제품명\t 2.브랜드\t 3.사이즈\t 4.판매가격대\t 5.발매가격대\t 6.발매연도");
System.out.print("검색할 항목 선택>>");
int search = sc.nextInt();
String column=null;
switch(search) {
case 1 :
System.out.print("검색할 제품명 입력:");
column="shoename";
case 2 :
System.out.print("검색할 브랜드 1.나이키 2.아디다스>>");
column="brand";
break;
case 3 :
System.out.print("검색할 사이즈 입력:");
column="sizes";
break;
case 4 :
System.out.print("검색할 가격대 입력:");
column="resell";
break;
case 5 :
System.out.print("검색할 가격대 입력:");
column="price";
break;
case 6 :
System.out.print("검색할 발매연도 입력:");
column="launch_date";
break;
default :
System.out.println("1~6만 입력 가능합니다.");
return;
}
String searchWord= sc.next();
ArrayList<KreamKicksBean> kkList=kkdao.getKKBySearch(column,searchWord);
showKreamKicks(kkList);
}
private void showKreamKicks(ArrayList<KreamKicksBean> kkList) {
if(kkList.size()==0) {
System.out.println("검색 가능한 레코드 없음.");
return;
}
System.out.println("번호/제품명/브랜드/사이즈/발매가/판매가/발매일/판매일");
for(int i=0;i<kkList.size();i++) {
KreamKicksBean kkBean=kkList.get(i);
System.out.println(kkBean);
}
}
private void KicksMain() {
while(true) {
System.out.println("=== Kicks 메뉴 선택 ===");
System.out.println("1.전체 조회\t 2.조건 조회");
System.out.println("3.정보 추가\t 4.정보 수정");
System.out.println("5.정보 삭제\t 6.이전 메뉴");
System.out.print("7.프로그램 종료\t 메뉴 선택>>");
int menu=sc.nextInt();
switch(menu) {
case 1:
ArrayList<KicksBean> kiList = kkdao.getAllKicks();
showKicks(kiList);
break;
case 2:
getKicksBySearch();
break;
case 3:
insertKicks();
break;
case 4:
updateKicks();
break;
case 5:
deleteKicks();
break;
case 6:
return;
case 7:
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default:
System.out.println("1~7만 입력 가능합니다.");
break;
}
}
}
private void KreamMain() {
while(true) {
System.out.println("=== Kream 메뉴 선택 ===");
System.out.println("1.전체 조회\t 2.조건 조회");
System.out.println("3.정보 추가\t 4.정보 수정");
System.out.println("5.정보 삭제\t 6.제품 추천");
System.out.println("7.정렬\t 8.이전 메뉴\t 9.프로그램 종료");
System.out.print("메뉴 선택>>");
int menu=sc.nextInt();
switch(menu) {
case 1 :
ArrayList<KreamBean> krList=kkdao.getAllKream();
showKream(krList);
break;
case 2 :
getKreamBySearch();
break;
case 3 :
insertKream();
break;
case 4 :
updateKream();
break;
case 5 :
deleteKream();
break;
case 6 :
randomKream();
break;
case 7 :
align();
break;
case 8 :
return;
case 9 :
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default:
System.out.println("1~9만 입력 가능합니다.");
break;
}
}
}
private void align() {
System.out.println("정렬할 항목을 선택하세요.");
System.out.print("번호:1 가격:2 사이즈:3 판매일:4 번호입력>>");
int align_num=sc.nextInt();
System.out.println("정렬 방법을 선택하세요");
System.out.print("오름차순:1 내림차순:2 번호입력>>");
int align_way =sc.nextInt();
String column = null;
String way = null;
switch(align_num) {
case 1 : column="no";
break;
case 2 : column="resell";
break;
case 3 : column="sizes";
break;
case 4 : column="sell_date";
break;
default :
System.out.println("1~4만 입력 가능합니다");
return;
}
switch(align_way) {
case 1 : way=" asc";
break;
case 2 : way=" desc";
break;
default :
System.out.println("1과 2만 입력 가능합니다");
return;
}
ArrayList<KreamBean> krBean=kkdao.align(column,way);
showKream(krBean);
}
private void randomKream() {
System.out.println("이 제품은 어떠세요?");
int max=kkdao.KreamMax();
int random=(int)(Math.random()*max)+1;
KreamBean krBean=kkdao.randomKream(random);
System.out.println("번호/제품명/브랜드/판매가/사이즈/판매일");
System.out.println(krBean);
}
private void deleteKream() {
System.out.print("삭제할 번호 입력>>");
int no =sc.nextInt();
int result = kkdao.deleteKream(no);
if(result>0) {
System.out.println("삭제 성공");
} else {
System.out.println("삭제 실패");
}
}
private void updateKream() {
System.out.print("수정할 번호 입력>>");
int no = sc.nextInt();
System.out.print("제품명 입력>>");
String shoename=sc.next();
System.out.print("브랜드 입력>>");
String brand=sc.next();
System.out.print("판매가 입력>>");
int resell=sc.nextInt();
System.out.print("사이즈 입력>>");
int sizes=sc.nextInt();
System.out.print("판매일 입력>>");
String sell_date=sc.next();
KreamBean krBean=new KreamBean(no,shoename,brand,resell,sizes,sell_date);
int result = kkdao.updateKream(krBean);
if(result>0) {
System.out.println("수정 성공");
} else {
System.out.println("수정 실패");
}
}
private void insertKream() {
System.out.println("번호는 시퀀스로 자동 입력됩니다.");
System.out.print("제품명 입력>>");
String shoename=sc.next();
System.out.print("브랜드 입력>>");
String brand=sc.next();
System.out.print("판매가 입력>>");
int resell=sc.nextInt();
System.out.print("사이즈 입력>>");
int sizes=sc.nextInt();
System.out.print("판매일 입력>>");
String sell_date=sc.next();
KreamBean krBean=new KreamBean(0,shoename,brand,resell,sizes,sell_date);
int result = kkdao.insertKream(krBean);
if(result>0) {
System.out.println("추가 성공");
} else {
System.out.println("추가 실패");
}
}
private void getKreamBySearch() {
System.out.println("1.제품명\t 2.브랜드\t 3.가격대\t 4.사이즈");
System.out.print("검색할 항목 선택>>");
int search = sc.nextInt();
String column=null;
switch(search) {
case 1 :
System.out.print("검색할 제품명 입력:");
column="shoename";
break;
case 2 :
System.out.print("검색할 브랜드 1.나이키 2.아디다스>>");
column="brand";
break;
case 3 :
System.out.print("검색할 가격대 입력:");
column="resell";
break;
case 4 :
System.out.print("검색할 사이즈 입력:");
column="sizes";
break;
default :
System.out.println("1~4만 입력 가능합니다.");
return;
}
String searchWord= sc.next();
ArrayList<KreamBean> krBean=kkdao.getKreamBySearch(column,searchWord);
showKream(krBean);
}
private void showKream(ArrayList<KreamBean> krList) {
if(krList.size()==0) {
System.out.println("검색 가능한 레코드 없음.");
return;
}
System.out.println("번호/제품명/브랜드/판매가/사이즈/판매일");
for(int i=0;i<krList.size();i++) {
KreamBean krBean=krList.get(i);
System.out.println(krBean);
}
}
private void deleteKicks() {
System.out.print("삭제할 제품명 입력>>");
String shoename=sc.next();
int result = kkdao.deleteKicks(shoename);
if(result>0) {
System.out.println("삭제 성공");
} else {
System.out.println("삭제 실패");
}
}
private void updateKicks() {
System.out.println("수정은 제품명 기준으로 됩니다.");
System.out.print("수정할 제품명 입력>>");
String shoename=sc.next();
System.out.print("브랜드 입력>>");
String brand=sc.next();
System.out.print("가격 입력>>");
int price=sc.nextInt();
System.out.print("발매일 입력>>");
String launch_date=sc.next();
KicksBean kiBean=new KicksBean(shoename,brand,price,launch_date);
int result = kkdao.updateKicks(kiBean);
if(result>0) {
System.out.println("수정 성공");
} else {
System.out.println("수정 실패");
}
}
private void insertKicks() {
System.out.print("제품명 입력>>");
String shoename=sc.next();
System.out.print("브랜드 입력>>");
String brand=sc.next();
System.out.print("가격 입력>>");
int price=sc.nextInt();
System.out.print("발매일 입력>>");
String launch_date=sc.next();
KicksBean kiBean=new KicksBean(shoename,brand,price,launch_date);
int result = kkdao.insertKicks(kiBean);
if(result>0) {
System.out.println("추가 성공");
} else {
System.out.println("추가 실패");
}
}
private void getKicksBySearch() {
System.out.println("1.제품명\t 2.브랜드\t 3.가격대\t 4.발매연도");
System.out.print("검색할 항목 선택>>");
int search = sc.nextInt();
String column=null;
switch(search) {
case 1 :
System.out.print("검색할 제품명 입력:");
column="shoename";
case 2 :
System.out.print("검색할 브랜드 1.나이키 2.아디다스>>");
column="brand";
break;
case 3 :
System.out.print("검색할 가격대 입력:");
column="price";
break;
case 4 :
System.out.print("검색할 발매연도 입력:");
column="launch_date";
break;
default :
System.out.println("1~4만 입력 가능합니다.");
return;
}
String searchWord= sc.next();
ArrayList<KicksBean> kiList =kkdao.getKicksBySearch(column,searchWord);
showKicks(kiList);
}
private void showKicks(ArrayList<KicksBean> kiList) {
if(kiList.size()==0) {
System.out.println("검색 가능한 레코드 없음.");
return;
}
System.out.println("제품명/브랜드/발매가/발매일");
for(int i=0;i<kiList.size();i++) {
KicksBean kiBean=kiList.get(i);
System.out.println(kiBean);
}
}
public static void main(String[] args) {
KreamKicksMain kkmain = new KreamKicksMain();
}
}JDBC Dao(연산/도출 구간)
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 KreamKicksDao {
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String id="sqlid";
String pw="sqlpw";
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs = null;
KreamKicksDao(){
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("error 1");
}
}//생성자
public void connect() {
try {
conn=DriverManager.getConnection(url,id,pw);
} catch (SQLException e) {
System.out.println("error 2");
}
}
public ArrayList<KicksBean> getAllKicks() {
connect();
String sql = "select * from kicks";
ArrayList<KicksBean> kiList = new ArrayList<KicksBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
KicksBean kiBean= new KicksBean(rs.getString("shoename"),
rs.getString("brand"),rs.getInt("price")
,String.valueOf(rs.getDate("launch_date")));
kiList.add(kiBean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kiList;
}
public ArrayList<KicksBean> getKicksBySearch(String column, String searchWord) {
connect();
String sql="select * from kicks where upper("+column+") like ?";
if(column.equals("brand")) {
if(searchWord.equals("1")) {
searchWord="nike";
} else if(searchWord.equals("2")) {
searchWord="adidas";
}
}
if(column.equals("brand")||column.equals("shoename")) {
searchWord="%"+searchWord+"%";
} else {
searchWord=searchWord+"%";
}
ArrayList<KicksBean> kiList = new ArrayList<KicksBean>();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, searchWord.toUpperCase());
rs=ps.executeQuery();
while(rs.next()) {
KicksBean kiBean= new KicksBean(rs.getString("shoename"),
rs.getString("brand"),rs.getInt("price")
,String.valueOf(rs.getDate("launch_date")));
kiList.add(kiBean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kiList;
}
public int insertKicks(KicksBean kiBean) {
connect();
String sql = "insert into kicks values(?,?,?,?)";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, kiBean.getShoename());
ps.setString(2, kiBean.getBrand());
ps.setInt(3, kiBean.getPrice());
ps.setString(4, kiBean.getLaunch_date());
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int updateKicks(KicksBean kiBean) {
connect();
String sql = "update kicks set brand=?,price=?,launch_date=? where shoename=?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, kiBean.getBrand());
ps.setInt(2, kiBean.getPrice());
ps.setString(3, kiBean.getLaunch_date());
ps.setString(4, kiBean.getShoename());
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int deleteKicks(String shoename) {
connect();
String sql = "delete kicks where shoename=?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, shoename);
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public ArrayList<KreamBean> getAllKream() {
connect();
String sql = "select * from kream order by no";
ArrayList<KreamBean> krList = new ArrayList<KreamBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
KreamBean krBean= new KreamBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("resell"),rs.getInt("sizes"),
String.valueOf(rs.getDate("sell_date")));
krList.add(krBean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return krList;
}
public ArrayList<KreamBean> getKreamBySearch(String column, String searchWord) {
connect();
String sql="select * from kream where upper("+column+") like ?";
if(column.equals("brand")) {
if(searchWord.equals("1")) {
searchWord="nike";
} else if(searchWord.equals("2")) {
searchWord="adidas";
}
}
if(column.equals("brand")||column.equals("shoename")) {
searchWord="%"+searchWord+"%";
} else {
searchWord=searchWord+"%";
}
ArrayList<KreamBean> krList = new ArrayList<KreamBean>();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, searchWord.toUpperCase());
rs=ps.executeQuery();
while(rs.next()) {
KreamBean krBean=new KreamBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("resell"),rs.getInt("sizes"),
String.valueOf(rs.getDate("sell_date")));
krList.add(krBean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return krList;
}
public int insertKream(KreamBean krBean) {
connect();
String sql="insert into kream values(krmseq.nextval,?,?,?,?,?)";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, krBean.getKr_shoename());
ps.setString(2, krBean.getKr_brand());
ps.setInt(3, krBean.getResell());
ps.setInt(4, krBean.getSizes());
ps.setString(5, krBean.getSell_date());
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int updateKream(KreamBean krBean) {
connect();
String sql="update kream set shoename=?,brand=?,resell=?,size=?,sell_date=? where no=?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, krBean.getKr_shoename());
ps.setString(2, krBean.getKr_brand());
ps.setInt(3, krBean.getResell());
ps.setInt(4, krBean.getSizes());
ps.setString(5, krBean.getSell_date());
ps.setInt(6, krBean.getNo());
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int deleteKream(int no) {
connect();
String sql="delete kream where no=?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, no);
result=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public KreamBean randomKream(int random) {
connect();
String sql = "select * from kream where no=?";
KreamBean krBean=new KreamBean();
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, random);
rs=ps.executeQuery();
while(rs.next()) {
krBean= new KreamBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("resell"),rs.getInt("sizes"),
String.valueOf(rs.getDate("sell_date")));
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return krBean;
}
public int KreamMax() {
connect();
String sql = "select max(no) from kream";
int max=0;
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
max=rs.getInt("max(no)");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return max;
}
public ArrayList<KreamBean> align(String column, String way) {
connect();
String sql = "select * from kream order by "+column+way;
ArrayList<KreamBean> krList = new ArrayList<KreamBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
KreamBean krBean= new KreamBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("resell"),rs.getInt("sizes"),
String.valueOf(rs.getDate("sell_date")));
krList.add(krBean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return krList;
}
public ArrayList<KreamKicksBean> getAllKK() {
connect();
String sql="select no,shoename,brand,sizes,price,resell,launch_date,sell_date "
+ "from kream natural join kicks order by no";
ArrayList<KreamKicksBean> kkList=new ArrayList<KreamKicksBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
KreamKicksBean kkBean= new KreamKicksBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("sizes"),rs.getInt("price"),rs.getInt("resell"),
String.valueOf(rs.getDate("launch_date")),String.valueOf(rs.getDate("sell_date")));
kkList.add(kkBean);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kkList;
}
public ArrayList<KreamKicksBean> getKKBySearch(String column, String searchWord) {
connect();
String sql="select no,shoename,brand,sizes,price,resell,launch_date,sell_date "
+ "from kream natural join kicks where upper("+column+") like ?";
ArrayList<KreamKicksBean> kkList=new ArrayList<KreamKicksBean>();
if(column.equals("brand")) {
if(searchWord.equals("1")) {
searchWord="nike";
} else if(searchWord.equals("2")) {
searchWord="adidas";
}
}
if(column.equals("brand")||column.equals("shoename")) {
searchWord="%"+searchWord+"%";
} else {
searchWord=searchWord+"%";
}
try {
ps=conn.prepareStatement(sql);
ps.setString(1, searchWord.toUpperCase());
rs=ps.executeQuery();
while(rs.next()) {
KreamKicksBean kkBean= new KreamKicksBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("sizes"),rs.getInt("price"),rs.getInt("resell"),
String.valueOf(rs.getDate("launch_date")),String.valueOf(rs.getDate("sell_date")));
kkList.add(kkBean);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kkList;
}
public ArrayList<KreamKicksBean> alignAll(String column, String way) {
connect();
String sql="select no,shoename,brand,sizes,price,resell,launch_date,sell_date "
+ "from kream natural join kicks order by "+column+way;
ArrayList<KreamKicksBean> kkList=new ArrayList<KreamKicksBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
KreamKicksBean kkBean= new KreamKicksBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("sizes"),rs.getInt("price"),rs.getInt("resell"),
String.valueOf(rs.getDate("launch_date")),String.valueOf(rs.getDate("sell_date")));
kkList.add(kkBean);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kkList;
}
public KreamKicksBean priceCheck(String column, String shoename, int no) {
connect();
String sql="select no,shoename,brand,sizes,price,resell,launch_date,sell_date "
+ "from kream natural join kicks where upper("+column+") like ?";
KreamKicksBean kkBean = new KreamKicksBean();
try {
ps=conn.prepareStatement(sql);
if(no==0) {
ps.setString(1, shoename.toUpperCase());
} else {
ps.setInt(1, no);
}
rs=ps.executeQuery();
while(rs.next()) {
kkBean= new KreamKicksBean(rs.getInt("no"),rs.getString("shoename")
,rs.getString("brand"),rs.getInt("sizes"),rs.getInt("price"),rs.getInt("resell"),
String.valueOf(rs.getDate("launch_date")),String.valueOf(rs.getDate("sell_date")));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return kkBean;
}
}//끝1테이블 Bean(데이터 타입)
public class KicksBean {
String shoename;
String brand;
int price;
String launch_date;
public KicksBean() {
}
public KicksBean(String shoename, String brand, int price, String launch_date) {
super();
this.shoename = shoename;
this.brand = brand;
this.price = price;
this.launch_date = launch_date;
}
public String getShoename() {
return shoename;
}
public void setShoename(String shoename) {
this.shoename = shoename;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getLaunch_date() {
return launch_date;
}
public void setLaunch_date(String launch_date) {
this.launch_date = launch_date;
}
@Override
public String toString() {
return shoename + "," + brand + ","
+ price + "," + launch_date ;
}
}2테이블 Bean(데이터)
public class KreamBean {
int no;
String shoename;
String brand;
int resell;
int sizes;
String sell_date;
public KreamBean() {
}
public KreamBean(int no, String kr_shoename, String kr_brand, int resell, int sizes, String sell_date) {
super();
this.no = no;
this.shoename = kr_shoename;
this.brand = kr_brand;
this.resell = resell;
this.sizes = sizes;
this.sell_date = sell_date;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getKr_shoename() {
return shoename;
}
public void setKr_shoename(String kr_shoename) {
this.shoename = kr_shoename;
}
public String getKr_brand() {
return brand;
}
public void setKr_brand(String kr_brand) {
this.brand = kr_brand;
}
public int getResell() {
return resell;
}
public void setResell(int resell) {
this.resell = resell;
}
public int getSizes() {
return sizes;
}
public void setSizes(int sizes) {
this.sizes = sizes;
}
public String getSell_date() {
return sell_date;
}
public void setSell_date(String sell_date) {
this.sell_date = sell_date;
}
@Override
public String toString() {
return no + "," + shoename + "," + brand + "," + resell
+ "," + sizes + "," + sell_date;
}
}통합용 Bean
public class KreamKicksBean {
int no;
String shoename;
String brand;
int sizes;
int price;
int resell;
String launch_date;
String sell_date;
public KreamKicksBean() {
}
public KreamKicksBean(int no, String shoename,
String brand, int sizes, int price, int resell, String launch_date,
String sell_date) {
super();
this.no = no;
this.shoename = shoename;
this.brand = brand;
this.sizes = sizes;
this.price = price;
this.resell = resell;
this.launch_date = launch_date;
this.sell_date = sell_date;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getShoename() {
return shoename;
}
public void setShoename(String shoename) {
this.shoename = shoename;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public int getSizes() {
return sizes;
}
public void setSizes(int sizes) {
this.sizes = sizes;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getResell() {
return resell;
}
public void setResell(int resell) {
this.resell = resell;
}
public String getLaunch_date() {
return launch_date;
}
public void setLaunch_date(String launch_date) {
this.launch_date = launch_date;
}
public String getSell_date() {
return sell_date;
}
public void setSell_date(String sell_date) {
this.sell_date = sell_date;
}
@Override
public String toString() {
return no + "," + shoename + "," + brand + "," + sizes
+ "," + price + "," + resell + "," + launch_date + ","
+ sell_date;
}
}
'Develop > JAVA' 카테고리의 다른 글
| 0628 JDBC와 Swing을 이용한 테이블 구성 (0) | 2022.06.29 |
|---|---|
| 0624 JDBC 응용 마무리 (0) | 2022.06.24 |
| 0623 JDBC 응용 2일차 (0) | 2022.06.24 |
| 0622 JDBC 응용 (0) | 2022.06.24 |
| 0621 JDBC 사용 (0) | 2022.06.24 |