create table likes (
member_idx int,
product_idx int,
foreign key (member_idx) references member (idx),
foreign key (product_idx) references product (idx),
primary key (member_idx, product_idx)
);
@ResponseBody
@GetMapping("/like/{idx}")
public String likeProduct(@AuthenticationPrincipal UserLoginRes userLoginRes, @PathVariable int idx) {
return productService.likeProduct(userLoginRes.getIdx(),idx);
}
public String likeProduct(int userLoginResIdx, int idx){
return productDao.likeProduct(userLoginResIdx, idx);
}
public String likeProduct(int userLoginResIdx, int idx) {
String getLikeQuery = "select * from likes where member_idx=? and product_idx=?";
List<Map<String, Object>> rows = this.jdbcTemplate.queryForList(getLikeQuery, userLoginResIdx, idx);
if (rows.size() == 0) {
String createProductQuery = "insert into likes (member_idx, product_idx) VALUES (?, ?)";
Object[] createProductParams = new Object[]{userLoginResIdx, idx};
this.jdbcTemplate.update(createProductQuery, createProductParams);
String getLastInsertIdxQuery = "select last_insert_id()";
int lastInsertIdx = this.jdbcTemplate.queryForObject(getLastInsertIdxQuery, int.class);
return "added";
}
else {
String createProductQuery = "DELETE FROM likes WHERE member_idx=? and product_idx=?";
Object[] createProductParams = new Object[]{userLoginResIdx, idx};
this.jdbcTemplate.update(createProductQuery, createProductParams);
String getLastInsertIdxQuery = "select last_insert_id()";
int lastInsertIdx = this.jdbcTemplate.queryForObject(getLastInsertIdxQuery, int.class);
return "deleted";
}
@Getter
@Setter
@AllArgsConstructor
public class GetProductWithImageAndLikesRes {
private int idx;
private String name;
private int brandIdx;
private int categoryIdx;
private int price;
private int salePrice;
private String deliveryType;
private String isTodayDeal;
private String filename;
private Boolean like_check;
}
public List<GetProductWithImageAndLikesRes> getProductsWithProductImage() {
String getProductsQuery = "SELECT * FROM product left JOIN (SELECT productIdx, group_concat(filename) FROM productImage group by productIdx) as pi ON product.idx=pi.productIdx";
return this.jdbcTemplate.query(getProductsQuery,
(rs, rowNum) -> new GetProductWithImageAndLikesRes(
rs.getObject("idx", int.class),
rs.getString("name"),
rs.getObject("brandIdx", int.class),
rs.getObject("categoryIdx", int.class),
rs.getObject("price", int.class),
rs.getObject("salePrice", int.class),
rs.getString("deliveryType"),
rs.getString("isTodayDeal"),
rs.getString("filename"),
false
));
}
public List<GetProductWithImageAndLikesRes> getProductsWithProductImageAndLikes(int member_idx) {
String getProductsQuery = "SELECT *, ifnull(product_idx, false) as like_ckeck FROM product left JOIN (SELECT productIdx, group_concat(filename) as filename FROM productImage group by productIdx) as pi ON product.idx=pi.productIdx LEFT JOIN (SELECT product_idx FROM likes WHERE member_idx=?) as likes ON likes.product_idx=product.idx;";
return this.jdbcTemplate.query(getProductsQuery,
(rs, rowNum) -> new GetProductWithImageAndLikesRes(
rs.getObject("idx", int.class),
rs.getString("name"),
rs.getObject("brandIdx", int.class),
rs.getObject("categoryIdx", int.class),
rs.getObject("price", int.class),
rs.getObject("salePrice", int.class),
rs.getString("deliveryType"),
rs.getString("isTodayDeal"),
rs.getString("filename"),
rs.getBoolean("like_ckeck")
),member_idx);
}
select 컬럼명 as 별명 from 테이블명;
select number as 사원번호, sales as 매출 from table_sales;