JdbcTemplate and BeanPropertyRowMapper 사용

1. Create - 새로운 상품 추가

public int addProduct(ProductDTO product) {
    String sql = "INSERT INTO product_table (PNUM, CATEGORY_FK, PRODUCTS_NAME, EP_CODE_FK, INPUT_PRICE, OUTPUT_PRICE, TRANS_COST, MILEAGE, COMPANY) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    return jdbcTemplate.update(sql, product.getPNUM(), product.getCATEGORY_FK(), product.getPRODUCTS_NAME(), product.getEP_CODE_FK(), product.getINPUT_PRICE(), product.getOUTPUT_PRICE(), product.getTRANS_COST(), product.getMILEAGE(), product.getCOMPANY());
}

2. 단일 객체 조회

public ProductDTO findProductById(Long id) {
    String sql = "SELECT * FROM product_table WHERE PNUM = ?";
    return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ProductDTO.class), id);
}

 

3. 여러 객체 조회

public List<ProductDTO> findAllProducts() {
    String sql = "SELECT * FROM product_table";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ProductDTO.class));
}

 

4. 매개변수와 함께 사용

public List<ProductDTO> findProductsByCategory(String category) {
    String sql = "SELECT * FROM product_table WHERE CATEGORY_FK = ?";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ProductDTO.class), category);
}

 

5. Update - 상품 정보 업데이트

public int updateProduct(ProductDTO product) {
    String sql = "UPDATE product_table SET CATEGORY_FK = ?, PRODUCTS_NAME = ?, EP_CODE_FK = ?, INPUT_PRICE = ?, OUTPUT_PRICE = ?, TRANS_COST = ?, MILEAGE = ?, COMPANY = ? WHERE PNUM = ?";
    return jdbcTemplate.update(sql, product.getCATEGORY_FK(), product.getPRODUCTS_NAME(), product.getEP_CODE_FK(), product.getINPUT_PRICE(), product.getOUTPUT_PRICE(), product.getTRANS_COST(), product.getMILEAGE(), product.getCOMPANY(), product.getPNUM());
}

6. Delete - 상품 삭제

public int deleteProduct(Long pnum) {
    String sql = "DELETE FROM product_table WHERE PNUM = ?";
    return jdbcTemplate.update(sql, pnum);
}