Food 테이블 생성문
drop table food;
create table food(
food_name varchar2(30) primary key,
count number(2),
exp_date date,
etc varchar2(100)
);
FoodVO.java
import java.sql.Date;
public class FoodVO {
private String foodName;
private int count;
private Date expDate;
private String etc;
public String getFoodName() {
return foodName;
}
public void setFoodName(String foodName) {
this.foodName = foodName;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public Date getExpDate() {
return expDate;
}
public void setExpDate(Date expDate) {
this.expDate = expDate;
}
public String getEtc() {
return etc;
}
public void setEtc(String etc) {
this.etc = etc;
}
@Override
public String toString() {
return "FoodVO [foodName=" + foodName + ", count=" + count + ", expDate=" + expDate + ", etc=" + etc + "]";
}
}
IFoodDAO.java
import java.util.List;
public interface IFoodDAO {
int insertFoodInfo(FoodVO food);
int updateFoodCount(FoodVO food);
int deleteFoodInfo(String foodName);
List<FoodVO> listFoodInfo();
}
FoodDAO.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class FoodDAO implements IFoodDAO {
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("드라이버가 로드되었습니다.");
} catch (ClassNotFoundException e) {
System.out.println("클래스를 찾을 수 없습니다.");
}
}
public static final String URL = "jdbc:oracle:thin:@localhost:1521/xe";
public static final String ID = "hr";
public static final String PW = "hr";
@Override
public int insertFoodInfo(FoodVO food) {
Connection con = null;
try {
con = DriverManager.getConnection(URL, ID, PW);
con.setAutoCommit(false);
String sql = "insert into food (food_name, count, exp_date, etc) "
+ "values (?, ?, ?, ?)";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, food.getFoodName());
stmt.setInt(2, food.getCount());
stmt.setDate(3, food.getExpDate());
stmt.setString(4, food.getEtc());
int row =stmt.executeUpdate();
con.commit();
return row;
} catch(Exception e) {
try {
con.rollback();
} catch (SQLException e1) { }
throw new RuntimeException(e);
} finally {
if(con!=null) {
try {
con.setAutoCommit(true);
con.close();
} catch(Exception e) { }
}
}
// return 0;
}
@Override
public int updateFoodCount(FoodVO food) {
Connection con = null;
try {
con = DriverManager.getConnection(URL, ID, PW);
if(food.getCount() > 0) {
String sql = "update food set count=? where food_name=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, food.getCount());
stmt.setString(2, food.getFoodName());
return stmt.executeUpdate();
}else {
String sql = "delete food where food_name=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, food.getFoodName());
return stmt.executeUpdate();
}
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
if(con!=null) {
try { con.close(); } catch(Exception e) { }
}
}
// return 0;
}
@Override
public int deleteFoodInfo(String foodName) {
Connection con = null;
try {
con = DriverManager.getConnection(URL, ID, PW);
String sql = "delete food where food_name=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, foodName);
return stmt.executeUpdate();
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
if(con!=null) {
try { con.close(); } catch(Exception e) { }
}
}
// return 0;
}
@Override
public List<FoodVO> listFoodInfo() {
Connection con = null;
try {
con = DriverManager.getConnection(URL, ID, PW);
String sql = "select * from food";
PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
List<FoodVO> foodList = new ArrayList<>();
while(rs.next()) {
FoodVO food = new FoodVO();
food.setFoodName(rs.getString("food_name"));
food.setCount(rs.getInt("count"));
food.setExpDate(rs.getDate("exp_date"));
food.setEtc(rs.getString("etc"));
foodList.add(food);
}
return foodList;
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
if(con!=null) {
try { con.close(); } catch(Exception e) { }
}
}
// return null;
}
}
FoodManager.java
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
public class FoodManager {
public static void main(String[] args) {
IFoodDAO dao = new FoodDAO();
FoodVO food = new FoodVO();
food.setFoodName("당근");
food.setCount(3);
// Date expDate = new Date(2018, 3, 20);
// food.setExpDate(expDate);
String date ="18-03-20";
SimpleDateFormat transFormat = new SimpleDateFormat("yy-MM-dd");
try {
food.setExpDate(new Date(transFormat.parse(date).getTime()));
} catch (ParseException e) {
System.out.println("날짜 형식 에러");
}
food.setEtc("몸에 좋은 당근");
// dao.insertFoodInfo(food);
// System.out.println("저장 OK");
List<FoodVO> foodList = dao.listFoodInfo();
for(FoodVO vo : foodList) {
System.out.println(vo);
}
}
}
|