[SpringBoot] WebSocket 채팅방 구현(6) - mapper

이전글

https://greed-yb.tistory.com/284

[SpringBoot] WebSocket 채팅방 구현(5) - service

 

 

 

mapper.class

import com.example.practice.vo.UserVo;
import com.example.practice.vo.messenger.MessageVo;
import com.example.practice.vo.messenger.RoomVo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface WidgetMapper {
    
    /**
     * 유저 정보 가져오기
     * @return
     * @throws Exception
     */
    List<UserVo> messengerInfo(String username) throws Exception;

    Long chatRoomInfo(String targetId , String userId) throws Exception;

    void createRoom(Long roonId) throws Exception;

    Long selectNextRoomId() throws Exception;

    List<MessageVo> selectChatInfo(Long id) throws Exception;

    void createRoomUser(Long roomId , String userId);

    /**
     * 웹소켓 전달할 대상의 ID 가져오기
     * @param roomId
     * @param userId
     * @return
     * @throws Exception
     */
    String targetId(String roomId, String userId) throws Exception;


    void insertMsg(Long roomId , String roomDetail, String userId) throws Exception;

    Long messengerReadCount(String username) throws Exception;

    List<RoomVo> roomList(String username) throws Exception;

    List<MessageVo> chatRoomEnter(String id) throws Exception;

    /**
     * 채팅방 접속 시 글 읽음으로 업데이트
     * @param id
     * @param username
     * @throws Exception
     */
    void readChat(String id, String username) throws Exception;

 

 

 

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.practice.mapper.widget.WidgetMapper">

    <select id="messengerInfo" resultType="com.example.practice.vo.UserVo">
        SELECT ID,
               NAME,
               PHONE_NUMBER,
               POSITION,
               DEPARTMENT,
               PROFILE
        FROM USERMEMBER
        WHERE ID != #{username}
    </select>


    <select id="chatRoomInfo" resultType="long">
        SELECT A.ROOM_ID
        FROM ROOM_USER A
                 INNER JOIN ROOM_USER B
                            ON A.ROOM_ID = B.ROOM_ID
        WHERE A.USER_ID = #{targetId}
          AND B.USER_ID = #{userId}
        GROUP BY A.ROOM_ID
    </select>

    <select id="selectNextRoomId" resultType="Long">
        SELECT NVL(Max(ROOM_ID), 0) + 1 AS ROOM_ID
        FROM MESSENGER
    </select>

    <insert id="createRoom">
        INSERT INTO MESSENGER(ROOM_ID)
        VALUES (#{roomId})
    </insert>

    <select id="selectChatInfo" resultType="com.example.practice.vo.messenger.MessageVo">
        SELECT DETAIL_NO,
               ROOM_ID,
               ROOM_DETAIL,
               DETAIL_READ,
               SEND_TIME,
               SEND_USER_ID
        FROM MESSENGER_DETAIL
        WHERE ROOM_ID = #{id}
        ORDER BY SEND_TIME ASC
    </select>

    <select id="targetId" resultType="string">
        SELECT USER_ID FROM ROOM_USER WHERE ROOM_ID = #{roomId} AND USER_ID != #{userId}
    </select>


    <insert id="createRoomUser">
        INSERT INTO ROOM_USER (ROOM_ID,
                               USER_ID,
                               ROOM_TITLE)
        VALUES (#{roomId},
                #{userId},
                (SELECT NAME AS ROOM_TITLE FROM USERMEMBER WHERE ID = #{userId}))
    </insert>

    <insert id="insertMsg">
        INSERT INTO MESSENGER_DETAIL(DETAIL_NO,
                                     ROOM_ID,
                                     ROOM_DETAIL,
                                     SEND_USER_ID)
        VALUES ((SELECT NVL(MAX(DETAIL_NO), 0) + 1 AS DETAIL_NO FROM MESSENGER_DETAIL WHERE ROOM_ID = #{roomId}),
                #{roomId},
                #{roomDetail},
                #{userId})
    </insert>


    <select id="messengerReadCount" resultType="Long">
        SELECT COUNT(DETAIL_READ) AS COUNT
        FROM MESSENGER_DETAIL
        WHERE ROOM_ID IN (SELECT ROOM_ID FROM ROOM_USER WHERE USER_ID = #{username})
          AND SEND_USER_ID != #{username}
          AND DETAIL_READ = 'N'
    </select>

    <select id="roomList" resultType="com.example.practice.vo.messenger.RoomVo">
        WITH TEMP AS (
            SELECT
                MD.DETAIL_NO,
                MD.ROOM_ID,
                CASE
                    WHEN LENGTH(MD.ROOM_DETAIL) > 20 THEN SUBSTR(MD.ROOM_DETAIL, 0,21) || '....'
                    ELSE MD.ROOM_DETAIL
                    END AS ROOM_DETAIL,
                MD.SEND_TIME,
                MD.SEND_USER_ID,
                RU.ROOM_TITLE ,
                (SELECT PROFILE FROM USERMEMBER WHERE ID = RU.USER_ID) AS PROFILE,
                COUNT(CASE WHEN MD.DETAIL_READ = 'N' AND MD.SEND_USER_ID != #{username} THEN 1 END) OVER (PARTITION BY MD.ROOM_ID) AS UNREAD_COUNT,
                    ROW_NUMBER() OVER (PARTITION BY MD.ROOM_ID ORDER BY MD.SEND_TIME DESC) AS RN
            FROM
                MESSENGER_DETAIL MD INNER JOIN ROOM_USER RU
                                               ON MD.ROOM_ID = RU.ROOM_ID
            WHERE
                MD.ROOM_ID IN (SELECT ROOM_ID FROM ROOM_USER WHERE USER_ID = #{username})
              AND RU.USER_ID  != #{username}
            )
        SELECT
            DETAIL_NO,
            ROOM_ID,
            ROOM_DETAIL,
            SEND_TIME,
            SEND_USER_ID,
            ROOM_TITLE,
            PROFILE,
            UNREAD_COUNT
        FROM
            TEMP
        WHERE
            RN = 1
    </select>


    <select id="chatRoomEnter" resultType="com.example.practice.vo.messenger.MessageVo">
        SELECT
            ROOM_ID ,
            DETAIL_NO ,
            ROOM_DETAIL ,
            DETAIL_READ ,
            SEND_USER_ID
        FROM MESSENGER_DETAIL
        WHERE ROOM_ID = #{id}
        ORDER BY SEND_TIME ASC
    </select>

    <update id="readChat">
        UPDATE MESSENGER_DETAIL SET DETAIL_READ ='Y'  WHERE ROOM_ID = #{id}  AND SEND_USER_ID != #{username}
    </update>
</mapper>