테이블명: Product
SQL:
drop sequence prdseq;
create sequence prdseq
increment by 1
start with 1
minvalue 1
nocache;
drop table products;
create table products(
id number primary key,
name varchar2(30),
stock number,
price number,
category varchar2(30),
inputdate date default sysdate
);
insert into products values(prdseq.nextval,'mp3',20,300,'IT', default);
insert into products values(prdseq.nextval,'갤럭시S6',30,200,'IT', default);
insert into products values(prdseq.nextval,'iPhone',40,500,'IT', default);
insert into products values(prdseq.nextval,'세탁기',20,300,'KJ', default);
insert into products values(prdseq.nextval,'냉장고',30,200,'KJ', default);
insert into products values(prdseq.nextval,'TV',40,500,'KJ', default);
insert into products values(prdseq.nextval,'Computer',20,300,'IT', default);
insert into products values(prdseq.nextval,'iMac',30,200,'IT', default);
commit ;
select * from products ;
Main
import java.util.ArrayList;
import java.util.Scanner;
public class ProductMain {
static ProductDao pdao = new ProductDao();
static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
init();
}
static public void init() {
while(true) {
System.out.println("===메뉴 선택===");
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.print("번호 입력>>");
int menu = sc.nextInt();
switch(menu) {
case 1:
ArrayList<ProductBean> lists =pdao.getAllProduct();
showProducts(lists);
break;
case 2:
getProductById();
break;
case 3:
getProductByCategory();
break;
case 4:
updateData();
break;
case 5:
deleteData();
break;
case 6:
insertData();
break;
case 7:
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default :
System.out.println("1~7만 입력 가능합니다.");
break;
}
}
}//init
public static void updateData() {
System.out.print("수정할 번호 입력:");
int id=sc.nextInt();
System.out.print("상품명 입력:");
String name=sc.next();
System.out.print("재고수량 입력:");
int stock = sc.nextInt();
System.out.print("가격 입력:");
int price = sc.nextInt();
System.out.print("카테고리 입력:");
String category=sc.next();
System.out.print("입고일자 입력:");
String inputdate=sc.next();
ProductBean pb = new ProductBean();
pb.setId(id);
pb.setName(name);
pb.setStock(stock);
pb.setPrice(price);
pb.setCategory(category);
pb.setInputdate(inputdate);
int result = pdao.updateProducts(pb);
if(result>0) {
System.out.println("update 성공");
}
else if(result==0) {
System.out.println("조건에 맞는 레코드 없음");
}
else {
System.out.println("update 실패");
}
}
public static void deleteData() {
System.out.print("삭제할 ID 입력:");
int del = sc.nextInt();
int result= pdao.deleteProducts(del);
if(result>0) {
System.out.println("delete 성공");
} else if(result==0) {
System.out.println("조건에 맞는 레코드 없음");
} else {
System.out.println("delete 실패");
}
}
public static void insertData() {
System.out.println("ID는 시퀀스로 입력됩니다.");
System.out.print("상품명 입력:");
String name=sc.next();
System.out.print("재고수량 입력:");
int stock = sc.nextInt();
System.out.print("가격 입력:");
int price = sc.nextInt();
System.out.print("카테고리 입력:");
String category=sc.next();
System.out.print("입고일자 입력:");
String inputdate=sc.next();
ProductBean pb = new ProductBean();
pb.setName(name);
pb.setStock(stock);
pb.setPrice(price);
pb.setCategory(category);
pb.setInputdate(inputdate);
int result =pdao.insertProduct(pb);
if(result>0) {
System.out.println("insert 성공");
} else {
System.out.println("insert 실패");
}
}
public static void getProductByCategory() {
System.out.print("검색할 카테고리 입력:");
String cate =sc.next();
ArrayList<ProductBean> lists=pdao.getProductByCategory(cate);
if(lists.size()==0) {
System.out.println("찾는 카테고리 없음.");
}else {
showProducts(lists);
}
}
public static void getProductById() {
System.out.print("검색할 아이디 입력:");
int id=sc.nextInt();
ProductBean pb=pdao.getProductById(id);
if(pb==null) {
System.out.println("찾는 아이디 없음");
} else {
System.out.println(pb);
}
}
public static void showProducts(ArrayList<ProductBean> lists){
for(int i=0;i<lists.size();i++) {
ProductBean pb =lists.get(i);
System.out.println(pb.getId()+","+pb.getName()+
","+pb.getStock()+","+pb.getPrice()+","+
pb.getCategory()+','+pb.getInputdate());
}
}
}
Bean
public class ProductBean {
private int id;
private String name;
private int stock;
private int price;
private String category;
private String inputdate;
public String toString() {
return id + "," + name + "," + stock + "," + price + ","
+ category + "," + inputdate;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getStock() {
return stock;
}
public void setStock(int stock) {
this.stock = stock;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
public String getInputdate() {
return inputdate;
}
public void setInputdate(String date) {
this.inputdate = date;
}
}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 ProductDao {
String driver="oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "sqlid";
String pw = "sqlpw";
Connection conn=null;
ProductDao(){
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");
}
}// connect
public ArrayList<ProductBean> getAllProduct() {
connect();
ArrayList<ProductBean> lists=new ArrayList<ProductBean>();
String sql = "select * from products order by id";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
// int id=rs.getInt("id");
// String name=rs.getString("name");
// int stock=rs.getInt("stock");
// int price=rs.getInt("price");
// String category=rs.getString("category");
// String inputdate=String.valueOf(rs.getDate("inputdate"));
ProductBean pb=new ProductBean();
pb.setId(rs.getInt("id"));
pb.setName(rs.getString("name"));
pb.setStock(rs.getInt("stock"));
pb.setPrice(rs.getInt("price"));
pb.setCategory(rs.getString("category"));
pb.setInputdate(String.valueOf(rs.getDate("inputdate")));
lists.add(pb);
}
} catch (SQLException e) {
System.out.println("error 3");
} finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return lists;
}//getAll
public ProductBean getProductById(int id) {
connect();
String sql = "select * from products where id="+id;
ProductBean pb = null;
try {
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
pb=new ProductBean();
pb.setId(rs.getInt("id"));
pb.setName(rs.getString("name"));
pb.setStock(rs.getInt("stock"));
pb.setPrice(rs.getInt("price"));
pb.setCategory(rs.getString("category"));
pb.setInputdate(String.valueOf(rs.getDate("inputdate")));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return pb;
}
public ArrayList<ProductBean> getProductByCategory(String cate) {
connect();
String sql = "select * from products where upper(category)=?";
ProductBean pb = null;
ArrayList<ProductBean> lists=new ArrayList<ProductBean>();
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, cate.toUpperCase());
ResultSet rs = ps.executeQuery();
while(rs.next()) {
pb=new ProductBean();
pb.setId(rs.getInt("id"));
pb.setName(rs.getString("name"));
pb.setStock(rs.getInt("stock"));
pb.setPrice(rs.getInt("price"));
pb.setCategory(rs.getString("category"));
pb.setInputdate(String.valueOf(rs.getDate("inputdate")));
lists.add(pb);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return lists;
}
public int insertProduct(ProductBean pb) {
connect();
String sql ="insert into products values"
+ "(prdseq.nextval,?,?,?,?,?)";
int result=-1;
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,pb.getName());
ps.setInt(2, pb.getStock());
ps.setInt(3, pb.getPrice());
ps.setString(4, pb.getCategory());
ps.setString(5, pb.getInputdate());
result = ps.executeUpdate();
System.out.println("result:"+result);
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return result;
}
public int deleteProducts(int del) {
connect();
String sql ="delete products where id=?";
int result = -1;
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,del);
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return result;
}
public int updateProducts(ProductBean pb) {
connect();
int result=0;
String sql ="update products set "
+ "name=?,stock=?,price=?,category=?,inputdate=? where id=?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,pb.getName());
ps.setInt(2, pb.getStock());
ps.setInt(3, pb.getPrice());
ps.setString(4, pb.getCategory());
ps.setString(5, pb.getInputdate());
ps.setInt(6, pb.getId());
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
}
}
return result;
}
}//Dao'Develop > JAVA' 카테고리의 다른 글
| 0624 JDBC 응용 마무리 (0) | 2022.06.24 |
|---|---|
| 0623 JDBC 응용 2일차 (0) | 2022.06.24 |
| 0621 JDBC 사용 (0) | 2022.06.24 |
| 0620 JAVA JDBC를 통한 SQL 연동 (0) | 2022.06.20 |
| 0609 JAVA 열여섯번째... (0) | 2022.06.10 |