mysql实现微博关注粉丝
首先从原型上说存在四种关系状态
+关注（0 表示无关系 1表示粉丝）
已关注（2表示已关注）
互相关注（3表示相互关注）
表设计
1.select * from s_follower where u1=?
查询用户id = ？的粉丝列表
2.select * from s_follower where u2=?
查询用户id = ？的关注列表
然后按原型实现关注列表/粉丝列表/关注（取消关注）
1.关注列表
/**
* 关注列表
* @param params
* @return
*/
@RequestMapping("getFollowList")
@ResponseBody
public ResponseVO getFollowList(@RequestBody FollowerParams params){
try{
Integer userId=params.getUserId();
//通过userId查询关注列表
PageHelper.startPage(params.getPageNum(),params.getPageSize());
List<FollowerVO> follows = followerService.getFollowList(userId);
List<FollowerVO> followers = followerService.getFollowerList(userId);
List<Integer> FollowUS= new ArrayList<>();
List<Integer> FollowerUS=new ArrayList<>();
List<Integer> TempUs=new ArrayList<>();
for (int i = 0; i < follows.size(); i++) {
Integer u1 = follows.get(i).getU1();
//关注列表为userId=u2
FollowUS.add(u1);
}
for (int i = 0; i < followers.size(); i++) {
Integer u2 = followers.get(i).getU2();
//关注列表为userId=u2
FollowerUS.add(u2);
}
//交集TempUs 为相互关注
TempUs.addAll(FollowUS);
TempUs.retainAll(FollowerUS);
/**
* 封装view对象返回
*/
for (int i = 0; i <follows.size() ; i++) {
//如果U为交集，肯定相互关注，否则已关注
if(TempUs.contains(follows.get(i).getU1())){
follows.get(i).setStatus(3);
}else{
follows.get(i).setStatus(2);
}
}
PageInfo<Follower> pageInfo=new PageInfo(follows);
return ResponseVO.createBySuccess(pageInfo);
} catch (Exception e) {
logger.info("我的关注列表出现异常{}",e.getMessage());
return ResponseVO.createByError(null);
}
}
/**
* 粉丝列表
* @param params
* @return
*/
@RequestMapping("getFollowerList")
@ResponseBody
public ResponseVO getFollowerList(@RequestBody FollowerParams params){
try{
Integer userId=params.getUserId();
//关注列表
List<FollowerVO> follows = followerService.getFollowList(userId);
//通过userId查询关注列表
PageHelper.startPage(params.getPageNum(),params.getPageSize());
//粉丝列表
List<FollowerVO> followers = followerService.getFollowerList(userId);
List<Integer> FollowUS= new ArrayList<>();
List<Integer> FollowerUS=new ArrayList<>();
List<Integer> TempUs=new ArrayList<>();
for (int i = 0; i < follows.size(); i++) {
Integer u1 = follows.get(i).getU1();
//关注列表为userId=u2
FollowUS.add(u1);
}
for (int i = 0; i < followers.size(); i++) {
Integer u2 = followers.get(i).getU2();
//关注列表为userId=u2
FollowerUS.add(u2);
}
//交集TempUs 为相互关注
TempUs.addAll(FollowerUS);
TempUs.retainAll(FollowUS);
/**
* 封装view对象返回
*/
for (int i = 0; i <followers.size() ; i++) {
//如果U为交集，肯定相互关注，否则已关注
if(TempUs.contains(followers.get(i).getU2())){
followers.get(i).setStatus(3);
}else{
followers.get(i).setStatus(1);
}
}
PageInfo<Follower> pageInfo=new PageInfo(followers);
/**
* 查看有无新粉丝，有则更新数据
*/
List<Follower> newfollowers = followerService.getNewfollowers(userId);
if(newfollowers.size()>0){
followerService.editFollowers(newfollowers);
}
return ResponseVO.createBySuccess(pageInfo);
} catch (Exception e) {
logger.info("我的关注列表出现异常{}",e.getMessage());
return ResponseVO.createByError(null);
}
}
//添加关注
@RequestMapping("addFollowing")
@ResponseBody
@Version(value = "1.0.0",desc = "userId添加关注",type = "all")
public ResponseVO AddFollowing(@RequestBody FollowerParams params){
try{
Integer userId=params.getUserId();
Integer u=params.getU();
Integer status=params.getStatus();
if(status==0){
/**
* 添加关注
*/
Follower follower=new Follower();
follower.setU1(u);
follower.setU2(userId);
follower.setCreateTime(DateUtils.getNowDate());
Follower exist = followerService.getIsFollower(u, userId);
if(exist==null){
int i=followerService.addFollower(follower);
//粉丝数 关注数
if(i>0){
publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
}
}
return ResponseVO.createBySuccess(2);
}else if(status==1){
/**
* 添加关注
*/
Follower follower=new Follower();
follower.setU1(u);
follower.setU2(userId);
follower.setCreateTime(DateUtils.getNowDate());
Follower exist = followerService.getIsFollower(u, userId);
if(exist==null) {
int i = followerService.addFollower(follower);
if (i > 0) {
//粉丝数 关注数
publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化", userId, u, status));
}
}
return ResponseVO.createBySuccess(3);
}else if(status==2){
/**
* 删除关注
*/
int i= followerService.deleteFollower(userId,u);
if(i>0){
//粉丝数 关注数
publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
}
return ResponseVO.createBySuccess(0);
}else{
/**
* 删除关注
*/
int i=followerService.deleteFollower(userId,u);
if(i>0){
//粉丝数 关注数
publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
}
return ResponseVO.createBySuccess(1);
}
} catch (Exception e) {
logger.error("userId添加关注{}",e.getMessage());
return ResponseVO.createByError(null);
}
}
@Data
public class FollowerParams {
private Integer pageNum;
private Integer pageSize;
/**
* 当前用户
*/
private Integer userId;
/**
* u 其他用户
*/
private Integer u;
// 0 默认无关系，1 粉丝未关注 2 已关注 3 相互关注
private Integer status;
}
最后对表的优化时建立索引
alter table s_follower add unique index idx_follow_u1_u2(u1,u2);这里对添加操作使用了更新
insert into s_follower(u1,u2)
VALUES
(47,15)ON DUPLICATE KEY UPDATE u1=VALUES(u1),u2=VALUES(u2)
最后说一下关注数，粉丝数。使用了监听
@Service
public class UserFollowListener implements ApplicationListener<UserFollowEvent> {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
private UserService userService;
@Override
public void onApplicationEvent(UserFollowEvent userFollowEvent) {
logger.info("用户添加关注信息{}", JSON.toJSONString(userFollowEvent));
User user = new User();
if (userFollowEvent.getStatus().equals(0) || userFollowEvent.getStatus().equals(1)) {
user.setId(userFollowEvent.getUserId());
userService.updateUserFollowerNumById(user, 1);
user.setId(userFollowEvent.getU());
userService.updateUserFansNumByUserId(user, 1);
}
//
if (userFollowEvent.getStatus().equals(2) || userFollowEvent.getStatus().equals(3)) {
//粉丝减一 关注减一
user.setId(userFollowEvent.getUserId());
userService.updateUserFollowerNumById(user, -1);
user.setId(userFollowEvent.getU());
userService.updateUserFansNumByUserId(user, -1);
}
}
}
public class UserFollowEvent extends ApplicationEvent {
public UserFollowEvent(Object source, Integer userId, Integer u, Integer status) {
super(source);
this.userId = userId;
this.u = u;
this.status = status;
}
/**
* 当前用户
*/
private Integer userId;
/**
* 被关注用户信息
*/
private Integer u;
/**
* 加好友状态信息
*/
private Integer status;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getU() {
return u;
}
public void setU(Integer u) {
this.u = u;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
}
由于测试过程出现许多脏数据，使用存储过程更新关注粉丝数
循环更新关注数，粉丝数
delimiter // #定义标识符为双斜杠
drop procedure if exists test; #如果存在test存储过程则删除
create procedure test() #创建无参存储过程,名称为test
begin
declare i int; #申明变量
set i = 0; #变量赋值
lp : loop #lp为循环体名,可随意 loop为关键字
UPDATE s_users set fans_num=(SELECT count(*) from s_follower where u1=i),follow_num=(SELECT count(*) from s_follower where u2=i)
where id=i; #往test表添加数据
set i = i + 1; #循环一次,i加一
if i > 133 then #结束循环的条件: 当i大于10时跳出loop循环
leave lp;
end if;
end loop;
select * from test; #查看test表数据
end // #结束定义语句
call test();