建议每一个步骤都看一下
功能:实现实时推送数据库报警信息
客户需求:在主页实时显示数据库的报警信息(第三方只读数据库)。
一、开发环境
- JDK11
- MP
- Websocket
- springboot
- redis
- Oracle和mysql
二、思路
监听数据库-发布监听器-查询数据-websocket发送
三、实现代码示例
1.监听数据库
这位大神启发: link
@Component
public class OracleChangeConfiguration {
@Autowired
private ApplicationEventPublisher applicationEventPublisher;//注入spring事件发布者,用于发布事件
final String USERNAME = "****";//数据库用户名
final String PASSWORD = "****";//数据库密码
String URL;
public void OracleChangeConfiguration() {
URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.200.210)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))";
try {
OracleConnection conn = connect();
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
prop.setProperty(OracleConnection.NTF_TIMEOUT, "0");//如果为0或不设置,则用不过期,直到程序停止监听,当数据库发送更新通知时,因为没有监听端口,数据库随后释放资源
prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP, "true");
prop.setProperty(OracleConnection.DCN_IGNORE_UPDATEOP, "true");
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);
try {
dcr.addListener(new DatabaseChangeListener() {
@Override
public void onDatabaseChangeNotification(DatabaseChangeEvent arg0) {
TableChangeDescription[] td = arg0.getTableChangeDescription();
for (TableChangeDescription t : td) {
for (RowChangeDescription r : t.getRowChangeDescription()) {
ROWID rowid = r.getRowid();//获取修改ID
applicationEventPublisher.publishEvent(new OracleEvent("数据库发生改变=" + rowid));//发布事件。
}
}
}
});
Statement stmt = conn.createStatement();
((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select * from ATUDENT");
String[] tableNames = dcr.getTables();
for (int i = 0; i < tableNames.length; i++)
System.out.println(tableNames[i] + " is part of the registration.");
rs.close();
stmt.close();
} catch (SQLException ex) {
if (conn != null)
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
} finally {
try {
conn.close();
} catch (Exception innerex) {
innerex.printStackTrace();
}
}
} catch (SQLException mainSQLException) {
mainSQLException.printStackTrace();
}
}
/**
* Creates a connection the database.
*/
public OracleConnection connect() throws SQLException {
OracleDriver dr = new OracleDriver();
Properties prop = new Properties();
prop.setProperty("user", USERNAME);
prop.setProperty("password", PASSWORD);
return (OracleConnection) dr.connect(URL, prop);
}
}
2.发布监听器
2.1 自定义监听器
public class OracleEvent extends ApplicationEvent {
public OracleEvent(Object source) {
super(source);
}
}
2.2 发布自定义监听器
@Component
public class OracleEventListener implements ApplicationListener<OracleEvent> {
@Autowired
private IOracleDataService service;
@Override
public void onApplicationEvent(OracleEvent event) {
Object message = event.getSource();
//"这里就可以已经知道数据库添加了新数据,现在要去查询新数据"
service.getAlarm();//这是我的服务层接口,改成你自己的
System.out.println("自定义事件监听器(MyEventListener2)收到发布的消息: " + message);
}
}
3.查询数据并发送
//这里就很简单了,我贴一下我的代码,仅供参考
OilTankAlarmEntity alarmEntity = this.getAlarmByBJZT();//查询数据
if (alarmEntity == null) {
return null;
}
//封装对象
AlarmDTO alarmDTO = new AlarmDTO();
alarmDTO.setDate(alarmEntity.getBjsj());
alarmDTO.setNumber(alarmEntity.getYgh());
alarmDTO.setWdh(alarmEntity.getWdh());
alarmDTO.setContent(alarmEntity.getBjnr());
//解析token获取用户名:我解析是因为我使用userID发送的消息,我的id在redis中存储
String username = JwtUtil.parseToken(redisOpsUtils.get("web-token").toString()).get().getUsername();
try {
//websocket发送消息
webSocket.sendMessageByUserId(redisOpsUtils.get(webSocket.REDIS_KEY + username + "ID").toString(), alarmDTO);
} catch (InterruptedException e) {
e.printStackTrace();
}
总结
基本就这些,仅供参考,缺少哪里请联系,看到会补全更正
可能有些地方不全,因为我也是在程序完成之后记录的,如果你使用出现问题,留下问题,我会解答,我中间也出现了很多问题。