WebSocket实现实时更新前端数据

1.在PostgreSQL数据库中创建触发器和触发器函数：

  首先，在PostgreSQL数据库中创建一个触发器函数，当特定表发生变化时触发通知。

CREATE OR REPLACE FUNCTION notify_table_change() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('my_notification', 'Table ' || TG_TABLE_NAME || ' has changed');
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

  接着，为你想监控的每个表创建触发器。例如，如果你想监控名为my_table的表：

CREATE TRIGGER my_table_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION notify_table_change();

2.配置WebSocket

WebSocketConfig

package com.oa.config;

import com.oa.handler.MyWebSocketHandler;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.socket.config.annotation.EnableWebSocket;
import org.springframework.web.socket.config.annotation.WebSocketConfigurer;
import org.springframework.web.socket.config.annotation.WebSocketHandlerRegistry;

/**
 * @Auther: Zxd
 * @Date: 2024/06/17  17:24:41
 * @Description: 配置WebSocket，使Spring Boot能够处理WebSocket连接。
 */

@Configuration   // 声明这是一个配置类
@EnableWebSocket // 启用WebSocket支持
public class WebSocketConfig implements WebSocketConfigurer {
    // 重写父类的registerWebSocketHandlers方法，用于注册WebSocket处理器和映射URL路径
    @Override
    public void registerWebSocketHandlers(WebSocketHandlerRegistry registry) {
        // 添加一个MyWebSocketHandler处理器，并将其注册到/ws路径上
        registry.addHandler(myWebSocketHandler(), "/ws").setAllowedOrigins("*");
    }

    // 定义一个WebSocket处理类
    @Bean
    @Qualifier("myWebSocketHandler")
    // 创建一个名为myWebSocketHandler的Bean，并返回一个MyWebSocketHandler实例
    public MyWebSocketHandler myWebSocketHandler() {
        return new MyWebSocketHandler();
    }
}

 3.实现WebSocket处理器

MyWebSocketHandler

 package com.oa.handler;
import org.springframework.stereotype.Component;
import org.springframework.web.socket.CloseStatus;
import org.springframework.web.socket.TextMessage;
import org.springframework.web.socket.WebSocketSession;
import org.springframework.web.socket.handler.TextWebSocketHandler;

import java.util.Collections;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.logging.Logger;

/**
 * @Auther: Zxd
 * @Date: 2024/06/17  17:28:28
 * @Description: 处理WebSocket连接和消息。
 */
@Component("customWebSocketHandler")
public class MyWebSocketHandler extends TextWebSocketHandler {
    // 定义一个不可修改的Set集合，用于存储WebSocketSession对象，维护所有活跃的WebSocket会话
    private final Set<WebSocketSession> sessions = Collections.newSetFromMap(new ConcurrentHashMap<>());
    private static final Logger logger = Logger.getLogger(MyWebSocketHandler.class.getName());
    @Override
    // 当WebSocket连接建立时调用>>连接建立时，将会话添加到集合
    public void afterConnectionEstablished(WebSocketSession session) throws Exception {
        // 将session添加到sessions集合中
        sessions.add(session);
        logger.info("WebSocket connection established with session: " + session.getId());
    }

    @Override
    // 当WebSocket连接关闭时调用>>连接关闭时，从集合中移除会话
    public void afterConnectionClosed(WebSocketSession session, CloseStatus status) throws Exception {
        // 从sessions集合中移除session
        sessions.remove(session);
        logger.info("WebSocket connection closed with session: " + session.getId());
    }

    @Override
    // 处理客户端发送的文本消息
    public void handleTextMessage(WebSocketSession session, TextMessage message) throws Exception {
        // 处理客户端发送的消息
    }

    // 向所有活跃的客户端发送消息（）群发消息
    public void sendMessageToAllClients(String message) {
        // 遍历sessions集合
        for (WebSocketSession session : sessions) {
            // 向每个session发送消息
            try {
                session.sendMessage(new TextMessage(message));
//                logger.info("Sent message to session: " + session.getId());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

3.监听数据库变动并推送

DatabaseChangeListener

 package com.oa.listener;

import com.oa.handler.MyWebSocketHandler;
import jakarta.annotation.PostConstruct;
import jakarta.annotation.PreDestroy;
import org.postgresql.PGNotification;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.sql.*;

/**
 * @Auther: Zxd
 * @Date: 2024/06/17  17:31:42
 * @Description:
 */

@Service
public class DatabaseChangeListener {
    // 注入MyWebSocketHandler对象，用于发送消息
    @Autowired
    @Qualifier("myWebSocketHandler")
    private MyWebSocketHandler webSocketHandler;

    // 声明一个Connection对象
    private Connection connection;
    // 声明一个Statement对象
    private Statement statement;

    // 初始化方法，并连接数据库并设置监听，在构造函数之后执行
    @PostConstruct
    public void init() throws Exception {
        // 从DriverManager中获取连接，连接到名为TEST的PG数据库，用户名为postgres，密码为aofakeji2024
        connection = DriverManager.getConnection("jdbc:postgresql://localhost:8080/TEST", "user", "passwd");
        // 创建Statement对象
        statement = connection.createStatement();
        // 执行监听命令，监听名为my_notification的通知
        statement.execute("LISTEN my_notification");

        // 创建一个新线程，在循环中执行查询操作
        new Thread(() -> {
            while (true) {
                try {
                    PGNotification notifications[] = ((org.postgresql.PGConnection) connection).getNotifications();
                    if (notifications != null) {
                        for (PGNotification notification : notifications) {
                            String message = notification.getParameter();
                            webSocketHandler.sendMessageToAllClients(message);
                        }
                    }
                    Thread.sleep(500); // 等待500毫秒后再次检查通知
                } catch (SQLException | InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }).start();
    }

    // 销毁方法，在销毁对象之前执行，在销毁时关闭数据库连接
    @PreDestroy
    public void destroy() throws Exception {
        // 关闭Statement对象
        statement.close();
        // 关闭Connection对象
        connection.close();
    }
}

4.前端页面

test.vue

<script lang="ts" setup>
import { ref, onMounted, onUnmounted } from 'vue'

// 定义ref变量
const message = ref<string>('')
const operation = ref<string>('')
const data = ref<any>(null)
let socket: WebSocket

// 监听组件挂载
onMounted(() => {
    // 创建WebSocket连接
    socket = new WebSocket('ws://localhost:8086/api/ws')
    
    // 监听WebSocket连接打开
    socket.onopen = function(event) {
        console.log('WebSocket is open now.')
    }

    // 监听WebSocket接收消息
    socket.onmessage = function(event) {
        const receivedData = JSON.parse(event.data)
        operation.value = receivedData.operation
        data.value = receivedData.data
        console.log('WebSocket message received:', event.data)
    }

    // 监听WebSocket连接关闭
    socket.onclose = function(event) {
        console.log('WebSocket is closed now.')
    }

    // 监听WebSocket错误
    socket.onerror = function(event) {
        console.error('WebSocket error observed:', event)
    }

    console.log('WebSocket connected')
})

// 监听组件卸载
onUnmounted(() => {
    if (socket) {
        socket.close()
    }
})
</script>

<template>
    <div>
        <p>Operation: {{ operation }}</p>
        <p>Data: {{ JSON.stringify(data) }}</p>
    </div>
</template></script>

<template>
    <div>
        <p>Operation: {{ operation }}</p>
        <p>Data: {{ JSON.stringify(data) }}</p>
    </div>
</template>