CREATE
model(DAO)
public class ProductDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
private static final String INSERT = "INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),1000) + 1 FROM PRODUCT),?,?,?)"; // 추가 쿼리
public boolean insert(ProductDTO pDTO) {
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(INSERT);
pstmt.setString(1, pDTO.getName());
pstmt.setInt(2, pDTO.getPrice());
pstmt.setInt(3, pDTO.getCnt());
int result = pstmt.executeUpdate();
if (result <= 0) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return true;
}
}
controller
String name = view.inputName();
int price = view.inputPrice();
int cnt = view.inputCnt();
ProductDTO pDTO = new ProductDTO();
pDTO.setName(name);
pDTO.setPrice(price);
pDTO.setCnt(cnt);
boolean flag = pDAO.insert(pDTO);
if (!flag) {
view.printFalse();
continue;
}
view.printTrue();
READ(All)
model(DAO)
public class ProductDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
private static final String SELECTALL = "SELECT * FROM PRODUCT";
public ArrayList<ProductDTO> selectAll(ProductDTO pDTO) {
ArrayList<ProductDTO> datas = new ArrayList<ProductDTO>();
conn = JDBCUtil.connect();
try {
// 3. pstmt 로 CRUD 수행
pstmt = conn.prepareStatement(SELECTALL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
ProductDTO data = new ProductDTO();
data.setPid(rs.getInt("PID"));
data.setName(rs.getString("NAME"));
data.setPrice(rs.getInt("PRICE"));
data.setCnt(rs.getInt("CNT"));
datas.add(data);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return datas;
}
}
contoller
view.printDatas(pDAO.selectAll(null));
READ(One)
model(DAO)
public class ProductDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
private static final String SELECTONE = "SELECT * FROM PRODUCT WHERE PID=?";
public ProductDTO selectOne(ProductDTO pDTO) {
ProductDTO data = null;
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(SELECTONE);
pstmt.setInt(1, pDTO.getPid());
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
data = new ProductDTO();
data.setPid(rs.getInt("PID"));
data.setName(rs.getString("NAME"));
data.setPrice(rs.getInt("PRICE"));
data.setCnt(rs.getInt("CNT"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return data;
}
}
controller
int pid = view.inputPid();
ProductDTO pDTO = new ProductDTO();
pDTO.setPid(pid);
view.printData(pDAO.selectOne(pDTO));
UPDATE
model(DAO)
public class ProductDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
private static final String UPDATE="UPDATE PRODUCT SET CNT=CNT-1 WHERE PID=?";
public boolean update(ProductDTO pDTO) {
conn=JDBCUtil.connect();
try {
pstmt=conn.prepareStatement(UPDATE);
pstmt.setInt(1, pDTO.getPid());
int result=pstmt.executeUpdate();
if(result<=0) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return true;
}
}
controller
int pid=view.inputPid();
ProductDTO pDTO=new ProductDTO();
pDTO.setPid(pid);
pDTO=pDAO.selectOne(pDTO);
if(pDTO==null || pDTO.getCnt()<=0) {
view.printInfo(pDTO);
continue;
}
boolean flag=pDAO.update(pDTO);
if(!flag) {
view.printFalse();
continue;
}
view.printTrue();
DELETE
model(DAO)
public class ProductDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
private static final String DELETE="DELETE FROM PRODUCT WHERE PID=?";
public boolean delete(ProductDTO pDTO) {
conn=JDBCUtil.connect();
try {
pstmt=conn.prepareStatement(DELETE);
pstmt.setInt(1, pDTO.getPid());
int result=pstmt.executeUpdate();
if(result<=0) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return true;
}
}
contoller
int pid=view.inputPid();
ProductDTO pDTO=new ProductDTO();
pDTO.setPid(pid);
boolean flag=pDAO.delete(pDTO);
if(!flag) {
view.printFalse();
continue;
}
view.printTrue();