|
Orders
package jdbc.vo;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import jdbc.connectionpool.MyConnectionPool;
import jdbc.dao.BookDao;
import jdbc.dao.CustomerDao;
public class Orders {
private int orderid;
private int custid;
private int bookid;
private int saleprice;
private Date orderdate;
private String 고객이름;
private String 책이름;
public int getOrderid() {
return orderid;
}
public void setOrderid(int orderid) {
this.orderid = orderid;
}
public int getCustid() {
return custid;
}
public void setCustid(int custid) throws SQLException {
.
CustomerDao dao = new CustomerDao();
Connection conn = MyConnectionPool.getInstance().getConnection();
try {
Customer vo = dao.selectOne(custid, conn);
this.고객이름 = vo.getName();
if (vo == null)
throw new SQLException();
this.custid = custid;
} catch (SQLException e) {
throw new SQLException("포린키에 해당하는 자료가 없습니다." + e.getMessage());
}
}
public int getBookid() {
return bookid;
}
public void setBookid(int bookid) throws SQLException {
BookDao dao = new BookDao();
Connection conn = MyConnectionPool.getInstance().getConnection();
try {
Book vo = dao.selectOne(bookid, conn);
this.책이름 = vo.getBookname();
if (vo == null)
throw new SQLException();
this.bookid = bookid;
} catch (SQLException e) {
throw new SQLException("포린키에 해당하는 자료가 없습니다." + e.getMessage());
}
}
public int getSaleprice() {
return saleprice;
}
public void setSaleprice(int saleprice) {
this.saleprice = saleprice;
}
public Date getOrderdate() {
return orderdate;
}
public void setOrderdate(Date orderdate) {
this.orderdate = orderdate;
}
@Override
public String toString() {
return "Orders [orderid=" + orderid + ", custid=" + custid + 고객이름 + ", bookid=" + bookid + 책이름 + ", saleprice="
+ saleprice
+ ", orderdate=" + orderdate + "]";
}
}
OrdersDao
package jdbc.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jdbc.connectionpool.MyConnectionPool;
import jdbc.vo.Orders;
public class OrdersDao implements IDao<Orders, Integer> {
@Override
public int insert(Orders vo, Connection conn) throws SQLException {
String sql = "INSERT INTO ORDERS (ORDERID, CUSTID, BOOKID, SALEPRICE,ORDERDATE) " +
" VALUES (ORDERS_SEQ.NEXTVAL, ?, ?, ?, TO_DATE(?,'yyyy-mm-dd') )";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, vo.getCustid());
pstmt.setInt(2, vo.getBookid());
pstmt.setInt(3, vo.getSaleprice());
pstmt.setString(4, vo.getOrderdate().toString());
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new SQLException("Error while inserting Orders: " + e.getMessage());
} finally {
close(conn);
}
}
@Override
public List<Orders> selectAll(Connection conn) throws SQLException {
List<Orders> list = new ArrayList<>();
String sql = "SELECT * FROM ORDERS";
try (PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
Orders vo = new Orders();
vo.setOrderid(rs.getInt("ORDERID"));
vo.setCustid(rs.getInt("CUSTID"));
vo.setBookid(rs.getInt("BOOKID"));
vo.setSaleprice(rs.getInt("SALEPRICE"));
vo.setOrderdate(rs.getDate("ORDERDATE"));
list.add(vo);
}
} catch (SQLException e) {
throw new SQLException("Error while selecting all orders: " + e.getMessage());
} finally {
close(conn);
}
return list;
}
@Override
public Orders selectOne(Integer key, Connection conn) throws SQLException {
String sql = "SELECT * FROM ORDERS WHERE ORDERID = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, key);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
Orders vo = new Orders();
vo.setOrderid(rs.getInt("ORDERID"));
vo.setCustid(rs.getInt("CUSTID"));
vo.setBookid(rs.getInt("BOOKID"));
vo.setSaleprice(rs.getInt("SALEPRICE"));
vo.setOrderdate(rs.getDate("ORDERDATE"));
return vo;
}
}
} catch (SQLException e) {
throw new SQLException("Error while selecting an order: " + e.getMessage());
} finally {
releaseConnection(conn); // releaseConnection으로 수정
}
return null;
}
private void releaseConnection(Connection conn) {
// TODO Auto-generated method stub
}
@Override
public int delete(Integer key, Connection conn) throws SQLException {
String deleteSql = "DELETE FROM ORDERS WHERE ORDERID=?";
try (PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {
pstmt.setInt(1, key);
int rowsDeleted = pstmt.executeUpdate();
return rowsDeleted;
} catch (SQLException e) {
throw new SQLException("Error while deleting order: " + e.getMessage());
} finally {
close(conn);
}
}
@Override
public int update(Orders vo, Connection conn) throws SQLException {
String updateSql = "UPDATE ORDERS SET CUSTID=?, BOOKID=?, SALEPRICE=?, ORDERDATE=TO_DATE(?,'yyyy-mm-dd') WHERE ORDERID=?";
try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
pstmt.setInt(1, vo.getCustid());
pstmt.setInt(2, vo.getBookid());
pstmt.setInt(3, vo.getSaleprice());
pstmt.setString(4, vo.getOrderdate().toString());
pstmt.setInt(5, vo.getOrderid());
int rowsUpdated = pstmt.executeUpdate();
return rowsUpdated;
} catch (SQLException e) {
throw new SQLException("Error while updating update: " + e.getMessage());
} finally {
close(conn); // close 메서드가 어떤 역할을 하는지 명시되어 있지 않아 가정한 것입니다.
}
}
@Override
public void close(Connection conn) throws SQLException {
MyConnectionPool.getInstance().releaseConnection(conn);
}
}
OrdersDaoTest
package jdbc.dao;
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import jdbc.connectionpool.MyConnectionPool;
import jdbc.vo.Orders;
public class OrdersDaoTest {
private OrdersDao dao;
private MyConnectionPool dbcp;
private Connection conn;
@Before
public void setUp() {
dao = new OrdersDao();
dbcp = MyConnectionPool.getInstance();
conn = dbcp.getConnection();
}
@After
public void tearDown() throws SQLException {
dbcp.releaseConnection(conn);
System.out.println("테스트 작업이 마무리되었습니다.");
}
@Test
public void testSelectAll() throws SQLException {
List<Orders> ordersList = dao.selectAll(conn);
assertNotNull(ordersList);
assertTrue(ordersList.size() > 0);
for (Orders order : ordersList) {
System.out.println(order);
}
}
@Test
public void testSelectOne() throws SQLException {
int orderIdToSelect = 34; // Replace with an actual order ID
Orders order = dao.selectOne(orderIdToSelect, conn);
assertNotNull(order);
assertEquals(orderIdToSelect, order.getOrderid());
System.out.println(order);
}
@Test
public void testInsert() throws SQLException {
Orders vo = new Orders();
vo.setCustid(1);
vo.setBookid(1);
vo.setSaleprice(8000);
vo.setOrderdate(new Date(123, 7, 23));
int res = dao.insert(vo, conn);
assertTrue(res >= 1);
}
@Test
public void testUpdate() throws SQLException {
Orders vo = new Orders();
vo.setOrderid(34);
vo.setCustid(2);
vo.setBookid(2);
vo.setSaleprice(8000);
vo.setOrderdate(new Date(123, 7, 25));
int res = dao.update(vo, conn);
assertTrue(res >= 1);
}
// @Test
// public void testDelete() throws SQLException {
// conn = dbcp.getConnection();
// int res = dao.delete(40, conn);
// assertTrue(res > 0); // 0보다 크다면 정상
// }
}