【经验与坑】WebSocket实时更新MySql数据到前端

码农智涵的程序人生 2022-01-24

2318

之前记录了Spring Boot集成WebSocket的简单的demo，知道了如何使用websocket进行前后端通信。那么这部分回到使用websocket的初衷，就是要实时更新mysql中的报警信息到web页面展示。

一.数据格式

根据上图各字段的相应数据类型来构造实体类

二.代码实现部分

接下来编写 jdbc 从数据库中读取数据，这里仅做测试，具体使用什么框架来连接数据库视具体情况而定。

数据库连接成功。

package com.example.websockettest.utils;


import com.alibaba.fastjson.JSON;
import com.example.websockettest.bean.ResultsBean;
import org.junit.Test;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
*@description 功能描述
*@author han56
*@create 2022/1/22 下午4:08
*/public class DBUtil {


    public List<ResultsBean> getFromDB() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        List<ResultsBean> list = new ArrayList<>();


        String driver = "com.mysql.cj.jdbc.Driver";
        String user = "xoxoxo";
        String psd = "xoxoxo";


        String db = "xox";


        String tableName = "xoxooxoxo";


        String url = "jdbc:mysql://localhost:3306/"+db+"?user="+user+"&password="+psd;


        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url);


        Statement stat = conn.createStatement();


        String sql = "select * from " +tableName;


        ResultSet rs = stat.executeQuery(sql);


        while (rs.next()){
            ResultsBean resultsBean = new ResultsBean(rs.getInt(1),rs.getString(2),
                    rs.getDouble(3),rs.getDouble(4),rs.getDouble(5),
                    rs.getString(6),rs.getDouble(7),rs.getDouble(11),
                    rs.getDouble(13),rs.getDouble(14));
            list.add(resultsBean);
        }
        rs.close();
        stat.close();
        conn.close();
        return list;
    }


    @Test
    public void testDBConn() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        System.out.println(JSON.toJSONString(getFromDB()));
    }


}

下面开始写WebSocket，写一个线程用于发送新数据到页面，run中开启无限循环，用一个变量 currentIndex 记录当前数据量，当有新数据时，发送新数据。

package com.example.websockettest.socket;


import com.example.websockettest.bean.ResultsBean;
import com.example.websockettest.utils.DBUtil;


import javax.websocket.Session;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * @author han56
 * @description 功能描述
 * @create 2022/1/24 下午12:21
 */
public class OneThread extends Thread{


    private Session session;


    private List<ResultsBean> resultsBeanList;


    private DBUtil dbUtil;


    private int currentIndex;


    public OneThread(Session session){
        this.session = session;
        resultsBeanList = new ArrayList<>();
        dbUtil = new DBUtil();
        currentIndex = 0;//当前0条信息
    }


    @Override
    public void run() {
        while (true){
            List<ResultsBean> list = null;
            try {
                list= dbUtil.getFromDB();
            } catch (SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException throwables) {
                throwables.printStackTrace();
            }
            if (list!=null&&currentIndex< list.size()){
                for (int i=currentIndex;i< list.size();i++){
                    try {
                        session.getBasicRemote().sendText(list.get(i).getQuackTime()
                        +","+list.get(i).getNengliang()+","+list.get(i).getbValue()+","+list.get(i).getQuackGrade());
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                currentIndex = list.size();
            }
            try {
                //一秒刷新一次
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}

然后对WebSocket类中的OnOpen方法中启动发送新数据线程

        this.session=session;
        OneThread oneThread = null;
        oneThread=new OneThread(session);
        oneThread.start();

继续使用上次写的前端页面进行测试，

    ws.onmessage = function(evt){
      var msg = evt.data;
      if("[object Blob]" !== msg){
        var msgdiv = document.getElementById("msgtext");
        var span = document.createElement("span");
        span.innerHTML = msg+"<br />";
        msgdiv.appendChild(span);
      }else{
        var msgdiv = document.getElementById("msgtext");
        var span = document.createElement("span");
        var br = document.createElement("br");
        var can = document.createElement("canvas");
        var context = can.getContext("2d");
        var image = new Image();
        image.onload = function () {
          //image.height
          context.clearRect(0, 0, can.width, can.height);
          context.drawImage(image, 0, 0, can.width, can.height);
        }
        image.src = URL.createObjectURL(msg);
        span.appendChild(can);
        span.appendChild(br);
        msgdiv.appendChild(span);
      }
    }