【Java+MySql】好友关系数据库设计及Java代码实战

　　业务需求：A、B两个用户的关注/取消关注接口

　　1、建表

由数据库设计文档和数据库存放关系可知，

数据 用户的userId 既可以在数据库的user_id位置，也可以在数据库的follower_id位置上。

　　2、该接口的实现思路如下

　　　　2.1、关注/取消关注 用户的设计思路

　　　　参数：用户A、用户B、关注/取消关注flag

　　　　A和B的关系，假设A在数据库的A位置

　　　　00 A关注B

　　　　01 B关注A

　　　　02 A、B相互关注

　　　　备注：A在数据库的B位置时，如上关系为01、00、02

　　　　2.2、实现思路如下：

　　　　0.去数据库查询A、B这两个用户有没有关系(00/01/02)

　　　　1.点击关注 --说明A和B的关系：1>没有关系；2>B关注A

　　　　　　1-1.判断A和B的关系

　　　　　　1-2.如果没有关系，则添加一条数据

　　　　　　1-3.如果有关系，则将关系改为相互关注(这个要结合实际，如果A已经关注了B，则A不能在关注B了，即进来

的肯定是B点击了关注A)

　　　　2.3、点击取消关注 --说明A和B的关系：

　　　　　　1>A关注B；2>A、B相互关注

　　　　2.4、判断A和B的关系

　　　　　　2.4.1.如果是关注(结合实际，只有相互关注和关注两种关系)，则取消关注，即删除这条数据

　　　　　　2.4.2.如果是相互关注，则需要判断A和B的位置

　　　　　　2.4.3 如果A在数据库的A位置，则修改A和B的关系为被关注--即关系为01

　　　　　　2.4.4 如果A在数据库的B位置，则修改A和B的关系为被关注--即关系为00

　　3、Java代码实现

    /**
     * 添加用户关注(关注、取消关注)
     * @param json
     * @return
     */
    @PostMapping("/addUserRelationShip")
    public Map<String,Object> addUserRelationShip(@RequestBody String json) {
        Map<String, Object> resultMap = new HashMap<>();
        UserRelationShip relationShip = JSON.parseObject(json, UserRelationShip.class);
        relationShip.setRelationId(UUID.randomUUID().toString().replace("-", ""));
        System.out.println(relationShip);

        JSONObject jsonObject = JSONObject.parseObject(json);
        Integer status = jsonObject.getInteger("status");
        String userId = jsonObject.getString("userId");
        String followerId = jsonObject.getString("followerId");

        //获取A和B的关系
        List<UserRelationShip> userRelationShips = relationShipService.checkRelation(userId,followerId);
        if (userRelationShips.size() > 0 ) { //已经有关系了
            System.out.println("有关系了");
            UserRelationShip userRelationShip = userRelationShips.get(0);
            System.out.println(userRelationShip.getRelation());
            String relation = userRelationShip.getRelation();
            String relationUserId = userRelationShip.getUserId();
            String relationFollowerId = userRelationShip.getFollowerId();
            String position = "";
            if (userId.equals(relationUserId)) {
                position = "left";
            } else if (userId.equals(relationFollowerId)) {
                position = "right";
            }
            if (status == 1) { //执行关注操作
                if (position.equals("left")) {
                    if (relation.equals("00") || relation.equals("02")) { //自己在左边：00:主动关注过对方，02：两人互关着呢，不能再执行关注操作了
                        resultMap.put("message", "关注失败，不能重复关注");
                        resultMap.put("state", JsonResult.failing);
                    } else {//对方在关注你，但是你没有关注对方，所有执行操作，更改状态为02，变成互关的状态
                        int updateCount = relationShipService.updateRelationStatus(userId,followerId,"02");
                        if (updateCount > 0) {
                            resultMap.put("message", "关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    }
                } else { //说明自己在右边，关注是自己被对方主动关注过，
                    if (relation.equals("01") || relation.equals("02")) { //自己在右边：01：自己关注别人了，02：两人互关呢，不能再执行关注操作了
                        resultMap.put("message", "关注失败，不能重复关注");
                        resultMap.put("state", JsonResult.failing);
                    } else { //00:对方在关注你，但是你没有关注对方，所有执行操作，更改状态为02，变成互关的状态
                        int updateCount = relationShipService.updateRelationStatus(userId,followerId,"02");
                        if (updateCount > 0) {
                            resultMap.put("message", "关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    }
                }
            } else {//执行取关操作
                if (position.equals("left")) {
                    if (relation.equals("00")) { //此时只有自己关注了对方，但是对方，却没有关注自己，所以执行取消操作的话，直接删除就行了
                        int deleteCount = relationShipService.deleteUserRelationShip(userId,followerId);
                        if (deleteCount > 0) {
                            resultMap.put("message", "取消关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "取消关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    } else if (relation.equals("02")) { //两人互关呢，自己执行取关操作，要更改状态为01
                        int updateCount = relationShipService.updateRelationStatus(userId,followerId,"01");
                        if (updateCount > 0) {
                            resultMap.put("message", "取消关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "取消关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    } else { //01时候，对方在关注者自己，就没关注别人，取关个毛线啊
                        resultMap.put("message", "取消关注失败");
                        resultMap.put("state", JsonResult.failing);
                    }
                } else { //说明自己在右边，关注是自己被对方主动关注过，
                    if (relation.equals("01")) { //自己关注了对方，但是对方却没有关注自己，直接删除就行了
                        int deleteCount = relationShipService.deleteUserRelationShip(userId,followerId);
                        if (deleteCount > 0) {
                            resultMap.put("message", "取消关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "取消关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    } else if (relation.equals("02")) {
                        int updateCount = relationShipService.updateRelationStatus(userId,followerId,"00");
                        if (updateCount > 0) {
                            resultMap.put("message", "取消关注成功");
                            resultMap.put("state", JsonResult.SUCCESS);
                        } else {
                            resultMap.put("message", "取消关注失败");
                            resultMap.put("state", JsonResult.failing);
                        }
                    } else { //你都没关注别人，取关个毛线啊
                        resultMap.put("message", "取消关注失败");
                        resultMap.put("state", JsonResult.failing);
                    }
                }
            }
        } else { //没有关系
            System.out.println("没有关系");
            relationShip.setRelation("00"); //既然没有关系，就新增一条，记录状态标记为00
            int addCount = relationShipService.addUserRelationShip(relationShip);
            if (addCount > 0) {
                System.out.println("新增关系成功");
                resultMap.put("message", "新增关注成功");
                resultMap.put("state", JsonResult.SUCCESS);
            } else {
                resultMap.put("message", "新增关注失败");
                resultMap.put("state", JsonResult.failing);
            }
        }

        return  resultMap;
    }

　　备注：上述的sql语句：addUserRelationShip / deleteUserRelationShip / updateRelationStatus

    <!-- 检查好友关系 -->
    <select id="checkRelation" resultMap="BasePlusResultMap">
        select * from follower where (user_id = #{userId} and follower_id = #{followerId}) or (user_id = #{followerId} and follower_id = #{userId})
    </select>

    <!-- 关注某人 -->
    <insert id="addUserRelationShip" >
        insert into follower(relation_id,user_id,follower_id,status,relation) values (#{relationId},#{userId},#{followerId},#{status},#{relation})
    </insert>

    <!-- 取消关注某人 -->
    <delete id="deleteUserRelationShip">
        delete from follower where user_id=#{userId} and follower_id = #{followerId}
    </delete>

    <!-- 更新还有关系状态 -->
    <update id="updateRelationStatus">
        update follower set relation = #{relation} where (user_id = #{userId} and follower_id = #{followerId}) or (user_id = #{followerId} and follower_id = #{userId})
    </update>

　　封装检查好友关系的逻辑代码，可以贴过去直接用的

    public static int checkRelationStatus(String userId,String targetId,List<Fans> userRelationShips) {

        int relationStatus = 0;
        for (int index = 0;index < userRelationShips.size();index++) {

            Fans userRelationShip = userRelationShips.get(index);
            String relationShipUserId = userRelationShip.getUserId();
            String relationShipFollowerId = userRelationShip.getFollowerId();
            String relation = userRelationShip.getRelation();

            if (relationShipUserId.equals(userId) && relationShipFollowerId.equals(targetId) || (relationShipUserId.equals(targetId) && relationShipFollowerId.equals(userId))) {
                String position = "";
                if (userId.equals(relationShipUserId)) {
                    position = "left";
                } else if (userId.equals(relationShipFollowerId)) {
                    position = "right";
                }
                if (position.equals("left")) {
                    if (relation.equals("00") || relation.equals("02")) { //自己在左边：00:主动关注过对方，02：两人互关着呢，不能再执行关注操作了
                        relationStatus = 1;
                    }
                } else { //说明自己在右边，关注是自己被对方主动关注过，
                    if (relation.equals("01") || relation.equals("02")) { //自己在右边：01：自己关注别人了，02：两人互关呢，不能再执行关注操作了
                        relationStatus = 1;
                    }
                }
                break;
            }

        }

        return  relationStatus;
    }

　　4、获取我的关注列表

    /* *//**
     * 获取我的关注列表
     * @param userId
     * @param page
     * @param pageSize
     * @return
     */

    @GetMapping("/selectUserAttention")
    public Map<String,Object> selectUserAttention(String userId,int page,int pageSize){

        Map<String,Object> resultMap = new HashMap<>();
        List<RelationShipUser> relationUsers = new ArrayList<>();
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("userId",userId);
        paramMap.put("followerId",userId);

        int pageCount = (page)* pageSize;
        paramMap.put("page", pageCount);
        paramMap.put("size",pageSize);
        List<Fans> userRelationShips = fansUserService.selectUserAttention(paramMap);
        if(userRelationShips.size() > 0) {
            List<String> userIds = new ArrayList<>();
            for (int index = 0;index < userRelationShips.size();index++) {
                Fans userRelationShip = userRelationShips.get(index);
                String relationShipUserId = userRelationShip.getUserId();
                String relationShipFollowerId = userRelationShip.getFollowerId();

                if (relationShipFollowerId.equals(userId)) {
                    userIds.add(relationShipUserId);
                } else {
                    userIds.add(relationShipFollowerId);
                }
            }

            System.out.println(userIds);
            //获取用户
            List<UserClone> users = relationShipService.queryUserWithIds(userIds);
            if (users.size() > 0) {
                for (int index = 0;index < users.size(); index++) {
                    RelationShipUser relationShipUser = new RelationShipUser();
                    relationShipUser.setUser(users.get(index));
                    relationUsers.add(relationShipUser);
                }
            }
            for (int idx = 0;idx < relationUsers.size();idx++) {
                RelationShipUser relationShipUser = relationUsers.get(idx);
                String targetId = relationShipUser.getUser().getUserId();
                int relationStatus = checkRelationStatus(userId,targetId,userRelationShips);
                relationShipUser.setStatus(relationStatus);
                relationUsers.set(idx,relationShipUser);
            }
            resultMap.put("data",relationUsers);
            resultMap.put("message", "查询关注人成功");
            resultMap.put("state", JsonResult.SUCCESS);

        }else if(userRelationShips.size() == 0){
            resultMap.put("data",relationUsers);
            resultMap.put("message", "你还没关注任何人");
            resultMap.put("state", JsonResult.SUCCESS);
        }else {
            resultMap.put("message", "查询关注人失败");
            resultMap.put("state", JsonResult.failing);
        }

        return resultMap;
    }

　　以上代码所用的Sql语句：selectUserAttention

    <!--我的关注-->
    <select id="selectUserAttention" parameterType="Map" resultMap="BasePlusResultMap">
        select * from follower where (follower.user_id = #{userId} and follower.relation in ('00','02')) or
        (follower.follower_id = #{followerId} and follower.relation in ('01','02'))
         LIMIT #{page}, #{size}
    </select>

　　5、获取我的粉丝

    /**
     * 获取我的粉丝列表
     * @param userId
     * @param page
     * @param pageSize
     * @return
     */
    @GetMapping("/selectUserVermicelli")
    public Map<String,Object> selectUserVermicelli(String userId,int page,int pageSize){

        Map<String,Object> resultMap = new HashMap<>();
        List<RelationShipUser> relationUsers = new ArrayList<>();
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("userId",userId);
        paramMap.put("followerId",userId);

        int pageCount = (page)* pageSize;
        paramMap.put("page", pageCount);
        paramMap.put("size",pageSize);
        List<Fans> userRelationShips = fansUserService.selectUserVermicelli(paramMap);
        if(userRelationShips.size() > 0){
            List<String> userIds = new ArrayList<>();

            for (int index = 0;index < userRelationShips.size();index++) {
                Fans userRelationShip = userRelationShips.get(index);

                String relationShipUserId = userRelationShip.getUserId();

                String relationShipFollowerId = userRelationShip.getFollowerId();

                if (relationShipFollowerId.equals(userId)) {
                    userIds.add(relationShipUserId);
                } else {
                    userIds.add(relationShipFollowerId);
                }
            }

            System.out.println(userIds);
            //获取用户
            List<UserClone> users = relationShipService.queryUserWithIds(userIds);
            if (users.size() > 0) {
                for (int index = 0;index < users.size(); index++) {
                    RelationShipUser relationShipUser = new RelationShipUser();
                    relationShipUser.setUser(users.get(index));
                    relationUsers.add(relationShipUser);
                }
            }
            for (int idx = 0;idx < relationUsers.size();idx++) {
                RelationShipUser relationShipUser = relationUsers.get(idx);
                String targetId = relationShipUser.getUser().getUserId();
                int relationStatus = checkRelationStatus(userId,targetId,userRelationShips);
                relationShipUser.setStatus(relationStatus);
                relationUsers.set(idx,relationShipUser);
            }

            resultMap.put("data",relationUsers);
            resultMap.put("message", "查询成功");
            resultMap.put("state", JsonResult.SUCCESS);

        }else if(userRelationShips.size() == 0){
            resultMap.put("data",relationUsers);
            resultMap.put("message", "");
            resultMap.put("state", JsonResult.SUCCESS);
        }else {
            resultMap.put("message", "查询失败");
            resultMap.put("state", JsonResult.failing);
        }

        return resultMap;
    }

　　以上代码所用到的Sql：selectUserVermicelli

   <!-- &lt;!&ndash;我的粉丝-->
    <select id="selectUserVermicelli" resultMap="BasePlusResultMap" parameterType="Map">

        select u2.nickname, u2.phone,u2.avatar,follower.follower_id  ,follower.relation_id,u2.user_id,follower.relation,u2.description,follower.user_id
            from follower
                inner join dnx_app_user u1 on follower.follower_id = u1.user_id

                inner join dnx_app_user u2 on follower.user_id = u2.user_id

                                where (follower.user_id = #{userId} and follower.relation in ('01','02')) or
        (follower.follower_id = #{followerId} and follower.relation in ('00','02'))
         LIMIT #{page}, #{size}
    </select>