SQL:
drop sequence albumseq;
create sequence albumseq
increment by 1
start with 1
minvalue 1
maxvalue 100;
drop table albums;
create table albums(
num number primary key,
song varchar2(30) not null,
singer varchar2(30) not null,
company varchar2(20) not null,
price number default 0,
pub_day date default sysdate
);
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'바운스','조용필','다음',5000,'2014/1/26');
insert into albums(num,song,singer,company,pub_day)
values(albumseq.nextval,'미스코리아','이효리','네이버','2014/1/26');
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'badgirl','이효리','네이버',1000,'2012/1/06');
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'허공','조용필','네이버',7000,'2014/3/26');
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'허공','조용필','네이버',3000,'2011/2/26');
insert into albums(num,song,singer,company,pub_day)
values(albumseq.nextval,'hello','조용필','구글','2013/7/1');
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'좋은 날','아이유','다음',2000,'2006/12/3');
insert into albums(num,song,singer,company,price,pub_day)
values(albumseq.nextval,'마시멜로','아이유','네이버',4000,'2001/4/7');
insert into albums(num,song,singer,company,price)
values(albumseq.nextval,'단발머리','조용필','다음',8000);
select rownum, num, song, singer, price, rank
from (select num, song, singer, price, rank() over(order by price desc) as rank
from albums)
where rank between 3 and 5;
select * from albums;
Main
import java.util.ArrayList;
import java.util.Scanner;
public class AlbumsMain {
Scanner sc = new Scanner(System.in);
AlbumsDao adao = new AlbumsDao();
public void init() {
while(true) {
System.out.println("\n======메뉴 선택하기======");
System.out.println("1.모든 정보 조회");
System.out.println("2.앨범 추가");
System.out.println("3.앨범 수정");
System.out.println("4.앨범 삭제");
System.out.println("5.조건 조회");
System.out.println("6.가격 등수 검색(가격:내림 가수:오름)");
System.out.println("7.정렬");
System.out.println("8.프로그램 종료");
System.out.print("메뉴 선택 >> ");
int menu = sc.nextInt();
switch(menu) {
case 1 :
ArrayList<AlbumsBean> lists = adao.getAllAlbums();
showAlbums(lists);
break;
case 2 :
insertAlbums();
break;
case 3 :
updateAlbums();
break;
case 4 :
deleteAlbums();
break;
case 5:
getAlbumsBySearch();
break;
case 6:
getAlbumsByRange();
break;
case 7:
align();
break;
case 8 :
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default :
System.out.println("1~8만 입력 가능합니다.");
break;
}
}
}
private void align() {
System.out.println("정렬할 항목을 선택하세요.");
System.out.print("번호:1 노래제목:2 가수명:3 번호입력>>");
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="num";
break;
case 2 : column="song";
break;
case 3 : column="singer";
break;
default :
System.out.println("1~3만 입력 가능합니다");
return;
}
switch(align_way) {
case 1 : way=" asc";
break;
case 2 : way=" desc";
break;
default :
System.out.println("1과 2만 입력 가능합니다");
return;
}
ArrayList<AlbumsBean> lists = adao.align(column,way);
showAlbums(lists);
}
private void getAlbumsByRange() {
System.out.print("시작등수 입력 : ");
int from=sc.nextInt();
System.out.print("끝등수 입력 : ");
int to=sc.nextInt();
ArrayList<AlbumsBean> lists = adao.getAlbumsByRange(from,to);
showAlbums(lists);
}
private void getAlbumsBySearch() {
System.out.println("제목 검색:1 가수 검색:2 회사 검색:3");
System.out.print("검색할 항목 선택>>");
int search = sc.nextInt();
String column=null;
switch(search) {
case 1 :
System.out.print("검색할 제목 입력:");
column="song";
break;
case 2 :
System.out.print("검색할 가수 입력:");
column="singer";
break;
case 3 :
System.out.print("검색할 회사 입력:");
column="company";
break;
default :
System.out.println("1~3만 입력 가능합니다.");
return;
}
String searchWord= sc.next();
ArrayList<AlbumsBean> lists = adao.getAlbumsBySearch(column,searchWord);
showAlbums(lists);
}
private void deleteAlbums() {
System.out.print("삭제할 번호 입력>>");
int num=sc.nextInt();
int result = adao.deleteAlbums(num);
if(result>0) {
System.out.println("삭제 완료");
} else {
System.out.println("삭제 실패");
}
}
private void updateAlbums() {
System.out.print("수정할 번호 입력 >>");
int num=sc.nextInt();
System.out.print("곡 제목 입력 >>");
String song=sc.next();
System.out.print("가수 입력 >>");
String singer=sc.next();
System.out.print("음반사 입력 >>");
String company=sc.next();
System.out.print("가격 입력 >>");
int price = sc.nextInt();
System.out.print("발매일 입력 >>");
String pub_day=sc.next();
AlbumsBean ab= new AlbumsBean(num,song,singer,company,price,pub_day);
int result = adao.updateAlbums(ab);
if(result>0) {
System.out.println("수정 성공");
} else {
System.out.println("수정 실패");
}
}
private void insertAlbums() {
System.out.println("num은 시퀀스로 자동 추가 됩니다.");
System.out.print("곡 제목 입력 >>");
String song=sc.next();
System.out.print("가수 입력 >>");
String singer=sc.next();
System.out.print("음반사 입력 >>");
String company=sc.next();
System.out.print("가격 입력 >>");
int price = sc.nextInt();
System.out.print("발매일 입력 >>");
String pub_day=sc.next();
AlbumsBean ab= new AlbumsBean(0,song,singer,company,price,pub_day);
int result = adao.insertAlbums(ab);
if(result>0) {
System.out.println("추가 성공");
} else {
System.out.println("추가 실패");
}
}
private void showAlbums(ArrayList<AlbumsBean> lists) {
AlbumsBean ab= new AlbumsBean();
for(int i=0;i<lists.size();i++) {
ab=lists.get(i);
System.out.println(ab);
}
}
AlbumsMain(){
init();
}
public static void main(String[] args) {
AlbumsMain am = new AlbumsMain();
}
}
Bean
public class AlbumsBean {
private int num;
private String song;
private String singer;
private String company;
private int price;
private String pub_day;
public AlbumsBean() {
}
public AlbumsBean(int num, String song, String singer, String company, int price, String pub_day) {
super();
this.num = num;
this.song = song;
this.singer = singer;
this.company = company;
this.price = price;
this.pub_day = pub_day;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getSong() {
return song;
}
public void setSong(String song) {
this.song = song;
}
public String getSinger() {
return singer;
}
public void setSinger(String singer) {
this.singer = singer;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
if(price>0)
this.price = price;
}
public String getPub_day() {
return pub_day;
}
public void setPub_day(String pub_day) {
this.pub_day = pub_day;
}
@Override
public String toString() {
return num + "," + song + "," + singer + "," + company + ","
+ price + "," + pub_day;
}
}
//private 이유:예)가격을 넣을때 100을 넣어야 하는데 -100을 넣어버렸다.
//private으로 설정해두면 생성자를 통해서나 setter 메서드를 통해서만 값을 넣을수 있다.
//한번 체크가 가능하고 음수가 들어가지 않는다, private가 아니라면 음수도 들어가 버릴수가 있다. 안전하게 처리 가능
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 AlbumsDao {
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;
// AlbumsDao(){
// 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<AlbumsBean> getAllAlbums() {
connect();
String sql="select * from albums order by num";
ArrayList<AlbumsBean> lists = new ArrayList<AlbumsBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
AlbumsBean abean = new AlbumsBean(rs.getInt("num"),rs.getString("song"),rs.getString("singer")
,rs.getString("company"),rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));
lists.add(abean);
}
if(lists.size()==0) {
System.out.println("해당 단어는 존재하지 않음");
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return lists;
}
public int insertAlbums(AlbumsBean ab) {
connect();
String sql = "insert into albums values(albumseq.nextval,?,?,?,?,?)";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1,ab.getSong());
ps.setString(2,ab.getSinger());
ps.setString(3,ab.getCompany());
ps.setInt(4,ab.getPrice());
ps.setString(5,ab.getPub_day());
result=ps.executeUpdate();
} catch (SQLException e) {
System.out.println("error 3");
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int updateAlbums(AlbumsBean ab) {
connect();
String sql = "update albums set song=?,"
+ "singer=?,company=?,price=?,pub_day=? where num=?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setString(1,ab.getSong());
ps.setString(2,ab.getSinger());
ps.setString(3,ab.getCompany());
ps.setInt(4,ab.getPrice());
ps.setString(5,ab.getPub_day());
ps.setInt(6,ab.getNum());
result=ps.executeUpdate();
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public int deleteAlbums(int num) {
connect();
String sql = "delete albums where num =?";
int result=-1;
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, num);
result=ps.executeUpdate();
} catch (SQLException e) {
System.out.println("error 3");
}finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return result;
}
public ArrayList<AlbumsBean> getAlbumsBySearch(String column, String searchWord) {
connect();
String sql = "select * from albums where upper("+column+") like ?";
ArrayList<AlbumsBean> lists = new ArrayList<AlbumsBean>();
try {
ps=conn.prepareStatement(sql);
ps.setString(1,"%"+searchWord.toUpperCase()+"%");
rs=ps.executeQuery();
while(rs.next()) {
AlbumsBean abean = new AlbumsBean(rs.getInt("num"),rs.getString("song"),rs.getString("singer")
,rs.getString("company"),rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));
lists.add(abean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
System.out.println("검색한 항목은 "+lists.size()+"건입니다.");
return lists;
}
public ArrayList<AlbumsBean> getAlbumsByRange(int from, int to) {
connect();
String sql="select num, song, singer, price, company, pub_day, rank from"
+ "(select num, song, singer, price, company, pub_day, "
+ "rank() over(order by price desc) as rank from albums)"
+ " where rank between ? and ?";
ArrayList<AlbumsBean> lists = new ArrayList<AlbumsBean>();
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, from);
ps.setInt(2, to);
rs=ps.executeQuery();
while(rs.next()) {
AlbumsBean abean = new AlbumsBean(rs.getInt("num"),rs.getString("song"),rs.getString("singer")
,rs.getString("company"),rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));
lists.add(abean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return lists;
}
public ArrayList<AlbumsBean> align(String column, String way) {
connect();
String sql="select * from albums order by "+column+way;
ArrayList<AlbumsBean> lists = new ArrayList<AlbumsBean>();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
AlbumsBean abean = new AlbumsBean(rs.getInt("num"),rs.getString("song"),rs.getString("singer")
,rs.getString("company"),rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));
lists.add(abean);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
System.out.println("error");
}
}
return lists;
}
}'Develop > JAVA' 카테고리의 다른 글
| 0628 JDBC와 Swing을 이용한 테이블 구성 (0) | 2022.06.29 |
|---|---|
| 0627 JDBC 개인 과제 (0) | 2022.06.29 |
| 0623 JDBC 응용 2일차 (0) | 2022.06.24 |
| 0622 JDBC 응용 (0) | 2022.06.24 |
| 0621 JDBC 사용 (0) | 2022.06.24 |