gi_dor

마이페이지 ver.2- SpringSecurity, MySQL , MyBatis 본문

Back_End/SpringBoot

마이페이지 ver.2- SpringSecurity, MySQL , MyBatis

기돌 2024. 4. 25. 18:02
728x90

최근 주문내역에서 SQL 쿼리가 복잡해서 시간이 오래 걸렸습니다

 

이름 , 등급 , 적립금 , 보유 쿠폰

HTML

  <!-- ########## 포인트 , 등급 , 쿠폰 , 이름 시작 ########## -->
        <div class="col-8">
            <!--마일리지 , 멤버십 -->
            <table class="table">
                <tbody>
                    <tr>
                        <td class="" colspan="3" >
                            <p style="text-align: center">
                                <span id="welcomeName" th:text="${user.name}"> XXXX 님 안녕하세요 </span>
                            </p>
                        </td>
                    </tr>
                    <tr>
                        <th class="">
                            <div class="">
                                <div class="fw-normal" style="text-align: center">
                                    <p class="">멤버십 등급 : <span class="" style="font-weight: bold" th:text="${user.userGrade.name}" >일반 &nbsp; </span>
                                        <br><br>
                                        <a href="#" class="btn btn-secondary btn-sm">
                                            <span class="">  자세히보기</span>
                                        </a>
                                    </p>
                                </div>
                            </div>
                        </th>
                        <td>
                            <div class="">
                                <div class="" style="text-align: center">
                                    <p class="">적립금 :
                                        <span class="" style="font-weight: bold" th:text="${user.point}"> Y </span> &nbsp;
                                        <br><br>
                                        <a href="#" class="btn btn-secondary btn-sm">
                                            <span class="">자세히보기</span>
                                        </a>
                                    </p>
                                </div>
                            </div>
                        </td>
                        <td>
                            <div class="">
                                <div class="" style="text-align: center">
                                    <p class=""> 보유 쿠폰 :
                                        <span class="" style="font-weight: bold" th:text="${couponCnt}"> X </span>개 &nbsp;
                                        <br><br>
                                        <a href="#" class="btn btn-secondary btn-sm">
                                            <span class="">자세히보기</span>
                                        </a>
                                    </p>
                                </div>
                            </div>
                        </td>
                    </tr>
                </tbody>
            </table>
            <!-- ########## 포인트 , 등급 , 쿠폰 , 이름 끝 ########## -->

 

2. vo

@Setter
@Getter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private Long no;
    private String id;
    private String password;
    private String name;
    private String email;
    private LocalDateTime createdDate;
    private LocalDateTime updatedDate;
    private String tel;
    private String zipCode;
    private String address;
    private String addressDetail;
    private String delYn;
    private Long point;
    private UserGrade userGrade;
    private CouponProduced couponPr;

    public String getFullAddress() {
        return String.format("%s %s %s", zipCode,address,addressDetail);
    }
}

 

지난번 코드에서 달라진점은

private Long point;
private UserGrade userGrade;
private CouponProduced couponPr;

 

@Data
public class CouponProduced {

    private long couponProducedNo;
    private Date dueDate;
    private String used;
    private User user;
    private Coupon coupon;
}
@Data
public class Coupon {

    private long couponNo;
    private String name;
    private int price;
}

다른 팀원이 작성 해 둔  CouponProduced , Coupon 입니다 

 

@Setter
@Getter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class CouponProduced {

    private Long no;
    private LocalDateTime dueDate;
    private String usedYn;
    private User user;
    private Coupon coupon;
}

제가 주로 작성하는 방식입니다

첫 번째 코드는 Lombok의 @Setter, @Getter, @ToString, @AllArgsConstructor, @NoArgsConstructor 어노테이션을 사용해 getter , setter, toString , 전체  생성자, 기본 생성자를 자동으로 생성.

두 번째 코드는 Lombok의 @Data 어노테이션만 사용하여 getter , setter , toString , equals 및 hashCode , 전체  생성자,  기본 생성자를 자동으로 생성

@Data 어노테이션을 사용하게되면 @Getter, @Setter, @ToString, @EqualsAndHashCode, @AllArgsConstructor, @NoArgsConstructor 어노테이션을 모두 포함하므로 더 간결하게 코드를 작성할 수 있습니다
롬복자체가 아직 손에 익지 않아서 그런지 직접 생성 할 때도 있습니다

 

3 - 1 쿠폰 갯수 조회 XML

  <select id="countCoupon" parameterType="string"  resultType="int">
    select count(*)
    from COUPON_PRODUCED cp , USER u
    where cp.USER_NO = u.USER_NO
      and u.USER_ID =  #{value}
    </select>

 

3 - 2 사용자 정보조회 XML

<select id="selectUserById" parameterType="string" resultType="com.example.bookhub.user.vo.User">
        SELECT
               u.user_no              as no,
               u.user_id              as id,
               u.user_password        as password,
               u.user_name            as name,
               u.user_email           as email ,
               u.user_tel             as tel ,
               u.user_zip_code        as zipCode ,
               u.user_address         as address ,
               u.user_address_detail  as addressDetail ,
               u.user_created_date    as createdDate,
               u.user_updated_date    as updatedDate ,
               u.user_del_yn          as delYn ,
               u.user_point           as point,
               g.grade_no             as "userGrade.no",
               g.grade_name           as "userGrade.name"
        FROM USER u , USER_GRADE g
        WHERE user_id = #{id}
        and user_del_yn = 'N'
        and u.grade_no = g.grade_no
    </select>

 

4. Mapper 

@Mapper
public interface MyPageMapper {

    int countCoupon(String id);
}

@Mapper
public interface UserMapper {

    User selectUserById(String id);
}

 

5. Service

@Service
@RequiredArgsConstructor
public class UserService implements UserDetailsService {

    private final UserMapper userMapper;
    private final PasswordEncoder passwordEncoder;
  public User selectUserById(String id) {
        System.out.println("userService.selectUserById = " +id);
        User user = userMapper.selectUserById(id);
        if (user == null) {
            throw new RuntimeException("해당 아이디에 해당하는 사용자를 찾을 수 없습니다: " + id);
        }
        return user;
    }
}

@Service
@RequiredArgsConstructor
public class MyPageService {

    private final MyPageMapper myPageMapper;
    private final UserMapper userMapper;

    public int countCoupon (String id) {
       int cnt =  myPageMapper.countCoupon(id);
        System.out.println("보유한 쿠폰 갯수 : " + cnt );
       return cnt;
    }
}

 

6. Controller

@Slf4j
@Controller
@RequiredArgsConstructor
@RequestMapping("/mypage")
public class UserMyPageController {

    private static final Logger logger = LoggerFactory.getLogger(UserController.class);
    private final UserService userService;
    private final MyPageService myPageService;


    // 마이페이지
    @PreAuthorize("isAuthenticated()")
    @GetMapping()
    public String myPage(Model model , Principal principal) {

        // 로그인 ID 사용자 정보 조회
        String userId = principal.getName();
        User user = userService.selectUserById(userId);

        // 쿠폰 갯수 카운팅
        int couponCnt = myPageService.countCoupon(userId);


        model.addAttribute("user" , user);
        model.addAttribute("couponCnt",couponCnt);

        return "/user/mypage";
    }

 

 

최근 주문 내역 조회

// 마이페이지
    @PreAuthorize("isAuthenticated()")
    @GetMapping()
    public String myPage(Model model , Principal principal) {

        // 로그인 ID 사용자 정보 조회
        String userId = principal.getName();
        User user = userService.selectUserById(userId);

        // 쿠폰 갯수 카운팅
        int couponCnt = myPageService.countCoupon(userId);

        // 최근 주문목록 가져오기
        List<Buy> orderList =  myPageService.getOrderListById(userId);

        model.addAttribute("user" , user);
        model.addAttribute("couponCnt",couponCnt);
        model.addAttribute("orderList",orderList);
        
        return "/user/mypage/myPageMain";
    }

 

@Slf4j
@Service
@RequiredArgsConstructor
public class MyPageService {

    private final PasswordEncoder passwordEncoder;
    private final MyPageMapper myPageMapper;
    private final UserMapper userMapper;
    private final UserService userService;
    
    public List<Buy> getOrderListById(String id) {
        User user = userMapper.selectUserById(id);
      return   myPageMapper.selectOrderListById(user.getId());
    }
}
@Mapper
public interface MyPageMapper {
   List<Buy> selectOrderListById(String id);
}

 

<select id="selectOrderListById" parameterType="string" resultType="com.example.bookhub.product.vo.Buy">
      select
          b.BUY_NO                            as buyNo,
          b.TOTAL_PRICE                       as totalPrice,
          b.FINAL_PRICE                       as finalPrice,
          b.COMMON_ENTRANCE_APPROACH          as commonEntranceApproach,
          b.BUY_DATE                          as buyDate,
          b.BUY_PAY_METHOD_NO                 as buyPayMethod,
          bs.BUY_STATUS_NAME                  as "buyStatus.name",
          u.USER_ID                           as "user.id",
          bdr.BUY_DELIVERY_REQUEST_NO         as "buyDeliveryRequest.buyDeliveryRequestNo",
          (select count(*)
           from BUY_BOOK x
           where x.BUY_NO = b.BUY_NO)            as cnt,
          (select y.BOOK_NAME
           from BUY_BOOK x, BOOK y
           where x.BUY_NO = b.BUY_NO
             and x.BOOK_NO = y.BOOK_NO
            limit 1)                             as "book.name"
      from BUY b , USER u , BUY_STATUS bs , BUY_DELIVERY_REQUEST bdr
      where b.USER_NO = u.USER_NO
        and b.BUY_STATUS_NO = bs.BUY_STATUS_NO
        and b.BUY_DELIVERY_REQUEST_NO = bdr.BUY_DELIVERY_REQUEST_NO
        and u.USER_ID = #{value}
      ORDER BY
          b.BUY_NO DESC
          LIMIT 3;
  </select>

 

 

(select count(*)
 from BUY_BOOK x
 where x.BUY_NO = b.BUY_NO)            as cnt,

cnt 를 사용해 xxx 외 Y개 

(select y.BOOK_NAME
 from BUY_BOOK x, BOOK y
 where x.BUY_NO = b.BUY_NO
   and x.BOOK_NO = y.BOOK_NO
 limit 1)                             as "book.name"

limit 1을 사용해 첫번째로 조회되는 책 이름을 보여주고 그외에 책들은 위에 사용한 cnt로 건수 표현

<!-- ####   주문내역 검색 결과 시작  ### --->
<div style="margin:0 0 30px 0;">
    <a href="#">
        <img src="/image/userInfo/recentlyOrderList.jpg" alt="" style="margin:0 0 10px 0; width: auto; height: 35px;">
    </a>
    <table class="table" style="text-align: center">
        <colgroup>
            <col width="">
            <col width="">
            <col width="">
            <col width="">
            <col width="">
        </colgroup>
        <thead>
        <tr>
            <th>주문 일자</th>
            <th>주문 번호</th>
            <th>주문 상품</th>
            <th>주문 상태</th>
            <th> 가격 </th>
        </tr>
        </thead>
        <tbody>
            <tr th:each="order : ${orderList}">
                <td th:text="${#temporals.format(order.buyDate, 'yyyy-MM-dd')}"></td>
                <td><a href="#" th:text="${order.buyNo}"></a></td>
                <td>
                    <!-- 주문에 대한 상품 목록 -->
                    <span th:text="${order.book.name}"></span>
                    <!-- 만약 주문한 상품이 여러 개인 경우 -->
                    <span th:if="${order.cnt > 1}" th:text="' 외 ' + ${order.cnt} + '개' "></span>
                </td>
                <td th:text="${order.buyStatus.name}"></td>
                <td th:text="${order.finalPrice}"></td>
            </tr>
        </tbody>
    </table>
</div>
<!-- ####   주문내역 검색 결과 종료  ### --->

 

나의 1:1 문의

// 마이페이지
@PreAuthorize("isAuthenticated()")
@GetMapping()
public String myPage(Model model , Principal principal) {

    // 로그인 ID 사용자 정보 조회
    String userId = principal.getName();
    User user = userService.selectUserById(userId);

    // 쿠폰 갯수 카운팅
    int couponCnt = myPageService.countCoupon(userId);

    // 최근 주문목록 가져오기
    List<Buy> orderList =  myPageService.getOrderListById(userId);

    // 내가 작성한 1:1 문의 가져오기
    List<Inquiry> inquiryList = myPageService.getInquiryListById(userId);


    model.addAttribute("user" , user);
    model.addAttribute("couponCnt",couponCnt);
    model.addAttribute("orderList",orderList);
    model.addAttribute("inquiryList",inquiryList);

    return "/user/mypage/myPageMain";
}

 

 

@Slf4j
@Service
@RequiredArgsConstructor
public class MyPageService {

    private final PasswordEncoder passwordEncoder;
    private final MyPageMapper myPageMapper;
    private final UserMapper userMapper;
    private final UserService userService;
    
    public List<Inquiry> getInquiryListById(String id) {
        User user = userMapper.selectUserById(id);
        return myPageMapper.selectInquiryList(user.getId());
    }
}
@Mapper
public interface MyPageMapper {
	 List<Inquiry> selectInquiryList(String id );
}
<select id="selectInquiryList" parameterType="string" resultType="com.example.bookhub.board.vo.Inquiry">
    select
        i.INDIVIDUAL_INQUIRY_NO             as no,
        i.INQUIRY_CATEGORY_NO               as "faqCategory.no",
        f.FAQ_CATEGORY_NAME                 as "faqCategory.name",
        i.INQUIRY_USER_NO                   as "user.no",
        u.USER_ID                           as "user.id",
        i.INDIVIDUAL_INQUIRY_TITLE          as title,
        i.INDIVIDUAL_INQUIRY_CONTENT        as content,
        i.INDIVIDUAL_INQUIRY_ANSWER_YN      as answerYn,
        i.INDIVIDUAL_INQUIRY_DELETE_YN      as deleteYn,
        i.INDIVIDUAL_INQUIRY_CREATE_DATE    as createdDate ,
        i.INDIVIDUAL_INQUIRY_UPDATE_DATE    as updatedDate,
        u.USER_NAME                         as "user.name"
    from INDIVIDUAL_INQUIRIES i , USER u , FAQ_CATEGORIES f
    where i.INQUIRY_USER_NO = u.USER_NO
    and i.INQUIRY_CATEGORY_NO = f.FAQ_CATEGORY_NO
    and u.USER_ID = #{id}
    order by  i.INDIVIDUAL_INQUIRY_CREATE_DATE DESC
    Limit 3
</select>

 

 

<!--#####  나의 1:1 문의 시작 ##### -->
<div style="margin:0 0 30px 0;">
    <a href="#">
        <img src="/image/userInfo/1vs1.jpg" alt="나의 1:1 문의" style="margin:0 0 10px 0; width: auto; height: 35px;">
    </a>
    <table class="table">
        <colgroup>
            <col width="15%">
            <col width="50%">
            <col width="20%">
            <col width="15%">
        </colgroup>
        <tr align="center">
            <th> 문의 종류</th>
            <th> 글 제목</th>
            <th> 작성 날짜 </th>
            <th> 답변 완료</th>
        </tr>
        <tr th:if="${#lists.isEmpty(inquiryList)}">
            <td colspan="4" align="center">등록하신 1:1 문의 글이 없습니다.</td>
        </tr>
        <tr th:each="inquiry : ${inquiryList}" align="center">
            <td th:text="${inquiry.faqCategory.name}"> 교환/환불 </td>
            <td th:text="${inquiry.title}"> 환불 신청 하고 물건 보냈는데 입금 언제되나요 ?</td>
            <td th:text="${#temporals.format(inquiry.createdDate, 'yyyy-MM-dd')}"></td>
            <td align="center" th:text="${inquiry.answerYn}"> N </td>
        </tr>
    </table>
</div>
<!--##### 나의 1:1 문의 끝 #####0-->

 

 

728x90
저작자표시 비영리 변경금지

'Back_End > SpringBoot' 카테고리의 다른 글

스프링부트 설정파일(application.properties) 암호화 (Jasypt)  (0) 2024.05.02
비밀번호 찾기 + 임시비밀번호 이메일전송  (0) 2024.04.30
회원가입 - 아이디 중복체크 비동기 ajax  (4) 2024.04.25
암호화된 비밀번호 변경 (비동기)  (0) 2024.04.24
회원가입 - 이메일 , 전화번호 <select>  (0) 2024.04.23
'Back_End/SpringBoot' Related Articles more