위시리스트 CRD
wishList 테이블은 member테이블, game테이블을 참조한다.
또한 기본키인 wishId를 만들었다
(위시리스트가 추가될때마다 잔존하는 위시테이블의 튜플 갯수에 +1을 하여 Id를 만드는 함수를 이용하였다)
-- wishId를 생성하는 함수
create or replace function fn_wishlist_id
return varchar2
is
v_id varchar(30);
begin
SELECT 'wish' || (count(*) + 1) into v_id
FROM wishlist;
return v_id;
end; 
1. xml파일에 쿼리를 매핑
wishList.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="wishList">
<insert id="insertWishList" parameterClass="WishListVO">
insert into wishlist values(fn_wishlist_id, #memId#, #gameId#,-1)
</insert>
<delete id="deleteWishList" parameterClass="String">
delete from wishlist
where wish_id = #wishId#
</delete>
<resultMap class="WishListVO" id="WishListMap">
<result property="wishId" column="wish_id"/>
<result property="memId" column="mem_id"/>
<result property="gameId" column="game_id"/>
</resultMap>
<!-- 주문완료된 게임을 제외한 위시리스트 -->
<select id="getWishList" parameterClass="String" resultMap="WishListMap">
select wish_id, mem_id, game_id
from wishlist
where mem_id= #memId#
and game_id not in (
select b.game_id
from orders a, cart_game b, wishlist c, cart d
where b.cart_no = a.cart_no and b.game_id = c.game_id and b.cart_no = d.cart_no
and d.mem_id = #memId#
)
</select>
<!-- 인서트 전에 이미 해당 wishId가 DB에 있는지확인하기 -->
<select id="checkExist" parameterClass="String" resultClass="String">
select wish_id
from wishlist
where wish_id = #wishId#
</select>
<resultMap class="GameVO" id="GameDetail">
<result property="game_id" column="game_id"/>
<result property="game_name" column="game_name"/>
<result property="game_price" column="game_price"/>
<result property="game_img" column="game_img"/>
<result property="age" column="age"/>
</resultMap>
<select id="gameDetailList" parameterClass="String" resultMap="GameDetail">
select *
from game
where game_id=#gameId#
</select>
<select id="getMemberWishList" parameterClass="String" resultMap="WishListMap">
select * from wishlist where mem_id = #mem_id#
</select>
<resultMap class="FreeGameVO" id="freeMap">
<result property="game_id" column="game_id"/>
<result property="fgm_fdt" column="fgm_fdt"/>
<result property="game_price" column="game_price"/>
</resultMap>
<select id="freeGameInWish" parameterClass="String" resultMap="freeMap">
select a.game_id, a.fgm_fdt, b.GAME_PRICE
from freegame a
inner join game b
on (a.GAME_ID = b.GAME_ID)
where a.game_id= #gameId#
</select>
<select id="getWishId" parameterClass="String" resultClass="String">
select wish_id
from wishlist
where mem_id=#memId# and game_id=#gameId#
</select>
</sqlMap>이제 sqlMap의 태그들을 매핑하면 sql쿼리 결과를 받아 사용할 수 있다
2. VO객체 생성
WishListVO
package wishList.vo;
public class WishListVO {
private String wishId;
private String memId;
private String gameId;
private int wishArm = -1;
public String getWishId() {
return wishId;
}
public void setWishId(String wishId) {
this.wishId = wishId;
}
public String getMemId() {
return memId;
}
public void setMemId(String memId) {
this.memId = memId;
}
public String getGameId() {
return gameId;
}
public void setGameId(String gameId) {
this.gameId = gameId;
}
public int getWishArm() {
return wishArm;
}
public void setWishArm(int wishArm) {
this.wishArm = wishArm;
}
}3. DAO 생성
IWishListDAO
package wishList.dao;
import java.util.List;
import com.ibatis.sqlmap.client.SqlMapClient;
import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import wishList.vo.WishListVO;
public interface IWishListDAO {
public int insertWishList(SqlMapClient smc,WishListVO wishVO);
public int deleteWishList(SqlMapClient smc,String wishId);
public List<WishListVO> getWishList(SqlMapClient smc, String memId);
public String checkExist(SqlMapClient smc,String wishId);
public GameVO gameDetail (SqlMapClient smc,String gameId);
public List<WishListVO> getMemberWishList(SqlMapClient smc, String mem_id);
public FreeGameVO freeGameInWish (SqlMapClient smc,String gameId);
public String getWishId(SqlMapClient smc, String memId, String gameId);
}자바 프로젝트와 DB를 연결하여 쿼리결과를 가져온다.
WishList에 게임을 insert, delete, get(조회) 하는 메소드등을 만든다.
WishListDaoImpl
package wishList.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ibatis.sqlmap.client.SqlMapClient;
import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import wishList.vo.WishListVO;
public class WishListDaoImpl implements IWishListDAO{
private static IWishListDAO wishDAO;
private WishListDaoImpl() {
}
public static IWishListDAO getInstance() {
if(wishDAO==null) {
wishDAO = new WishListDaoImpl();
}
return wishDAO;
}
@Override
public int insertWishList(SqlMapClient smc,WishListVO wishVO) {
int cnt = 0;
try {
cnt = smc.update("wishList.insertWishList",wishVO);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("위시리스트에 게임 추가중 예외 발생",ex);
}
return cnt;
}
@Override
public int deleteWishList(SqlMapClient smc, String wishId) {
int cnt = 0;
try {
cnt = smc.delete("wishList.deleteWishList",wishId);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("위시리스트에 게임 삭제중 예외 발생",ex);
}
return cnt;
}
@Override
public List<WishListVO> getWishList(SqlMapClient smc, String memId) {
List<WishListVO> wishLists = new ArrayList<WishListVO>();
try {
wishLists = (List<WishListVO>) smc.queryForList("wishList.getWishList",memId);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("회원의 주문완료된 게임을 제외한 위시리스트 조회중 예외 발생!",ex);
}
return wishLists;
}
@Override
public String checkExist(SqlMapClient smc, String wishId) {
String check = null;
try {
check = (String) smc.queryForObject("wishList.checkExist",wishId);
System.out.println(check);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("위시리스트 게임존재 체크중 예외발생",ex);
}
return check;
}
@Override
public GameVO gameDetail(SqlMapClient smc, String gameId) {
GameVO gameDetails = null;
try {
gameDetails = (GameVO) smc.queryForObject("wishList.gameDetailList",gameId);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("위시리스트의 게임디테일 조회중 예외 발생!",ex);
}
return gameDetails;
}
@Override
public List<WishListVO> getMemberWishList(SqlMapClient smc, String mem_id) {
// TODO Auto-generated method stub
List<WishListVO> list = null;
try {
list = smc.queryForList("wishList.getMemberWishList",mem_id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public FreeGameVO freeGameInWish(SqlMapClient smc, String gameId) {
FreeGameVO freeGame = new FreeGameVO();
try {
freeGame = (FreeGameVO) smc.queryForObject("wishList.freeGameInWish",gameId);
System.out.println("freeGame : "+freeGame);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("위시리스트 게임존재 체크중 예외발생",ex);
}
return freeGame;
}
@Override
public String getWishId(SqlMapClient smc, String memId, String gameId) {
String wishId = null;
try {
wishId = (String) smc.queryForObject("wishList.getWishId",memId,gameId);
System.out.println(wishId);
} catch (SQLException ex) {
ex.printStackTrace();
throw new RuntimeException("멤버아이디아 겜아이디에 해당하는 위시아이디 가져오는쿼리 예외발생",ex);
}
return wishId;
}
}4. Service 생성
IWishListService
package wishList.service;
import java.util.List;
import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import wishList.vo.WishListVO;
public interface IWishListService {
/** WishListVO에 담은 자료를 DB에 insert하는 메소드
* @param wv insert할 자료가 저장된 VO객체
* @return 성공시 1 실패시 0 반환
*/
public int insertWishList(WishListVO wishVO);
/** 게임Id를 매개변수로 받아서 해당 게임을 위시리스트에서 삭제
* @param gameId 삭제할 게임ID
* @return 작업성공:1, 작업실패:0
*/
public int deleteWishList(String wishId);
public List<WishListVO> getWishList(String memId);
public String checkExist(String wishId);
public GameVO gameDetail (String gameId);
public List<WishListVO> getMemberWishList(String mem_id);
public FreeGameVO freeGameInWish(String gameId);
public String getWishId(String memId, String gameId);
}
controller에서 직접 호출하는 메소드들을 기술한다.
WishListServiceImpl
package wishList.service;
import java.util.List;
import com.ibatis.sqlmap.client.SqlMapClient;
import config.SqlMapClientFactory;
import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import wishList.dao.IWishListDAO;
import wishList.dao.WishListDaoImpl;
import wishList.vo.WishListVO;
public class WishListServiceImpl implements IWishListService {
private static IWishListService wishService;
private IWishListDAO wishDAO;
private SqlMapClient smc;
private WishListServiceImpl() {
wishDAO = WishListDaoImpl.getInstance();
smc = SqlMapClientFactory.getInstance();
}
public static IWishListService getInstance() {
if(wishService == null){
wishService = new WishListServiceImpl();
}
return wishService;
}
@Override
public int insertWishList(WishListVO wishVO) {
return wishDAO.insertWishList(smc,wishVO);
}
@Override
public int deleteWishList(String wishId) {
return wishDAO.deleteWishList(smc,wishId);
}
@Override
public List<WishListVO> getWishList(String memId) {
return wishDAO.getWishList(smc, memId);
}
@Override
public String checkExist(String wishId) {
return wishDAO.checkExist(smc, wishId);
}
@Override
public GameVO gameDetail(String gameId) {
return wishDAO.gameDetail(smc, gameId);
}
@Override
public List<WishListVO> getMemberWishList(String mem_id) {
// TODO Auto-generated method stub
return wishDAO.getMemberWishList(smc,mem_id);
}
@Override
public FreeGameVO freeGameInWish(String gameId) {
return wishDAO.freeGameInWish(smc, gameId);
}
@Override
public String getWishId(String memId, String gameId) {
return wishDAO.getWishId(smc, memId, gameId);
}
}
5. Controller 작성
detailWishListController
package wishList.controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import members.member.vo.MemberVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;
@WebServlet("/wishList/wishListDetail.do")
public class detailWishListController extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
HttpSession session = req.getSession();
if (session.getAttribute("loginMember") == null) {
req.setAttribute("msg", "위시리스트는 로그인 후에 확인이 가능합니다");
req.getRequestDispatcher("/main/main.jsp").forward(req, resp);
return;
}
// 위시리스트엔 구매 완료된 게임은 띄우면 X
MemberVO member = (MemberVO) session.getAttribute("loginMember");
String memId = member.getMem_id();
IWishListService wishService = WishListServiceImpl.getInstance();
// 구매완료된 게임 제외한 위시리스트
List<WishListVO> wishLists = wishService.getWishList(memId);
for (WishListVO vo : wishLists) {
System.out.println("위시리스트겜아이디 리스트 확인 :" + vo.getGameId());
System.out.println("위시리스트위시아이디 리스트 확인 :" + vo.getWishId());
}
List<GameVO> gameDetailList = new ArrayList<GameVO>();
// 선별한 게임 아이디에 대한 게임정보
for (WishListVO vo : wishLists) {
gameDetailList.add((GameVO) wishService.gameDetail(vo.getGameId()));
}
for (GameVO vo : gameDetailList) {
System.out.println("위시리스트겜기타정보 리스트 확인 " + vo.getGame_name());
}
// 한시적무료
List<FreeGameVO> freeGameList = new ArrayList<FreeGameVO>();
for (WishListVO vo : wishLists) {
if (wishService.freeGameInWish(vo.getGameId()) != null) {
freeGameList.add(wishService.freeGameInWish(vo.getGameId()));
}
}
for (FreeGameVO vo : freeGameList) {
System.out.println("[리스트] 위시리스트에 있는 무료게임 아이디 : " + vo.getGame_id());
}
// 주문완료 제외한 위시리스트 / 해당 위시리스트의 게임디테일, 해당 위시리스트중 무료게임 req
req.setAttribute("wishLists", wishLists);
req.setAttribute("gameDetailList", gameDetailList);
req.setAttribute("freeGameList", freeGameList);
req.getRequestDispatcher("/wishList/detail.jsp").forward(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
jsp파일은 태그때문에 너무 길어서 생략!
대신 깃허브에 올려두었다
InsertWishListController
package wishList.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import members.member.vo.MemberVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;
@WebServlet("/wishList/insertWishList.do")
public class InsertWishListController extends HttpServlet{
// 위시리스트에 추가 버튼을 누르면 나오는 페이지!!!!!! 새로운 게임을 insert하자
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
HttpSession session = req.getSession();
MemberVO vo = (MemberVO) session.getAttribute("loginMember");
String memId = vo.getMem_id();
String gameId = req.getParameter("gameId");
WishListVO wishVO = new WishListVO();
wishVO.setGameId(gameId);
wishVO.setMemId(memId);
// 인서트 처리 하기 전에 이미 존재하는지 검색부터 하고 보내자!
IWishListService wishService = WishListServiceImpl.getInstance();
String wishId = wishService.getWishId(memId, gameId);
int cnt=0;
if(wishId != null) {
System.out.println("해당 위시아이디가 위시리스트에 이미 존재함");
} else {
System.out.println("해당 위시아이디가 위시리스트에 존재하지 않으므로 위시에 추가");
cnt = wishService.insertWishList(wishVO);
}
String msg ="";
if(cnt>0) {
msg = "위시리스트추가성공";
}else {
msg="위시리스트추가실패";
}
System.out.println(msg);
session.setAttribute("msg", msg);
String redirectUrl = req.getContextPath()+"/detailGame.do?&id="+gameId;
resp.sendRedirect(redirectUrl);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req,resp);
}
}deleteWishListController
package wishList.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import members.member.vo.MemberVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;
@WebServlet("/wishList/deleteWishList.do")
public class deleteWishListController extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("\n\n[deleteWishList] get 진입");
HttpSession session = req.getSession();
MemberVO member = (MemberVO) session.getAttribute("loginMember");
String memId = member.getMem_id();
String wishId= (String) req.getParameter("wishId");
System.out.println("url의 위시아이디 파라미터 확인:"+wishId+"\n");
IWishListService wishService = WishListServiceImpl.getInstance();
int cnt = wishService.deleteWishList(wishId);
String msg ="";
if(cnt>0) {
msg = "위시삭제성공";
}else {
msg="위시삭제실패";
}
session.setAttribute("msg", msg);
String redirectUrl = req.getContextPath()+"wishListDetail.do";
resp.sendRedirect(redirectUrl);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req,resp);
}
}
WishToCartController
package wishList.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import members.member.vo.MemberVO;
import orders.cart.service.CartServiceImpl;
import orders.cart.service.ICartService;
import orders.cart.vo.CartVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;
@WebServlet("/wishList/wishToCart.do")
public class WishToCartController extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("\n\n[wishToCart doGet()요청 진입]");
HttpSession session = req.getSession();
MemberVO vo = (MemberVO) session.getAttribute("loginMember");
String memId = vo.getMem_id();
String gameId = (String) req.getParameter("gameId");
System.out.println("파라미터를 게임아이디로 변환 : "+gameId);
// 멤버와 게임아이디 가지고있는 상태
// 장바구니에 집어넣는 연산 전에 이미 해당 memId에 해당하는 gmaeId가 장바구니에 있는지 확인부터 한다
ICartService cartService = CartServiceImpl.getInstance();
CartVO cvo = new CartVO();
cvo.setMemId(memId);
cvo.setGameId(gameId);
String price = cartService.getPrice(gameId);
cvo.setCartPrice(price);
System.out.println("\n\nwishToCart할 겜아이디:"+gameId+"가격"+price);
String check = cartService.checkExist(cvo);
int cnt1=0;
int cnt2=0;
String msg ="";
if(check != null) {
System.out.println("[위시>장바구니]장바구니에 이미 존재함");
msg="위시리스트게임을 장바구니 담기 실패";
} else {
cnt1 = cartService.insertCart(cvo); // memId, gameId, price 넣음 sysdate는 설정할필요없고..
cnt2 = cartService.insertCartGame(gameId);
}
if(cnt1>0&&cnt2>0) {
msg = "위시리스트게임을 장바구니 담기 성공";
}else {
msg="위시리스트게임을 장바구니 담기 실패";
}
session.setAttribute("msg", msg);
String redirectUrl = req.getContextPath()+"/wishList/wishListDetail.do";
resp.sendRedirect(redirectUrl);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req,resp);
}
}
