Socket实战系列：
Socket实战——UDP连接：https://blog.csdn.net/haoranhaoshi/article/details/86601468
Socket实战——TCP连接：https://blog.csdn.net/haoranhaoshi/article/details/86601522
Socket实战——查询数据库：https://blog.csdn.net/haoranhaoshi/article/details/86601566
Socket实战——监听数据库：https://blog.csdn.net/haoranhaoshi/article/details/86601584
Socket实战——聊天：https://blog.csdn.net/haoranhaoshi/article/details/86601771
Socket实战——文件上传：https://blog.csdn.net/haoranhaoshi/article/details/86601850
Socket实战——文件下载：https://blog.csdn.net/haoranhaoshi/article/details/86632897
项目下载地址（IDEA搭建）：https://download.csdn.net/download/haoranhaoshi/10933044
监听数据库思想：
（１）客户端轮询数据库。适合数据量小、改动频率小、客户端主机性能优秀，三者至少居其一。
（２）客户端和服务端建立Socket长连接，服务端轮询数据库，数据库变化时将变化发送给客户端。适合服务端主机性能优秀。
（３）数据库中建立触发器，数据库变化时触发器启动服务端程序，和客户端建立Socket连接。适合频率中等偏小。
（２）对应的实战：
点击发送，客户端与服务端建立TCP的Socket连接，客户端发送数据库、表、属性，服务端返回初始值，并进行轮询，监听变化，通过客户端发送的IP、端口建立UDP的Socket连接，在属性值变化时实时返回。
package TableListenerByUDP;
import javafx.application.Application;
import javafx.application.Platform;
import javafx.scene.Scene;
import javafx.scene.control.Label;
import javafx.scene.layout.Pane;
import javafx.stage.Stage;
import javafx.stage.WindowEvent;
import org.json.JSONException;
import org.json.JSONObject;
import java.io.*;
import java.net.*;
import java.sql.*;
/**
* 点击发送，客户端与服务端建立TCP的Socket连接，客户端发送数据库、表、属性，服务端返回初始值，并进行轮询，监听变化，通过
* 客户端发送的IP、端口建立UDP的Socket连接，在属性值变化时实时返回
*/
public class ListenTableServer extends Application {
public static void main(String[] args) {
launch(args);
}
@Override
public void start(Stage primaryStage) {
Label label = new Label("等待客户端发送！");
try {
ServerSocket serverSocket = new ServerSocket(10086, 50, InetAddress.getByName("127.0.0.1"));
new Thread(() -> {
// 等待客户端的连接
while (true) {
try {
// 服务端接收
Socket socket = serverSocket.accept();
new Thread(() -> {
// 可扩展，message中可通过xml或json传入表、属性、筛选条件等，可传入查询ID并存储，对应ID传入end可结束绑定的轮询线程。
String message = "";
try {
// 未收到则后续不执行
InputStream inputStream = socket.getInputStream();
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
String lineMessage;
while ((lineMessage = bufferedReader.readLine()) != null) {
message += lineMessage;
}
// 关闭输入流
socket.shutdownInput();
JSONObject root = new JSONObject(message);
String dbName = root.getString("dbName");
String tableName = root.getString("tableName");
String columnName = root.getString("columnName");
String listenIP = root.getString("listenIP");
int listenPort = root.getInt("listenPort");
Platform.runLater(() -> label.setText("监听" + dbName + "数据库" + tableName + "表的" + columnName + "属性"));
String result;
Connection dbConn = CONN(dbName);
String sql = "select " + columnName + " from " + tableName;
Statement statement = dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
result = resultSet.getString(columnName);
if(result == null){
result = "空数据";
}
System.out.println("查询（" + sql + "）结果为：" + result);
// 服务端往客户端发送
OutputStream outputStream = socket.getOutputStream();
PrintWriter printWriter = new PrintWriter(outputStream);
printWriter.write(result);
printWriter.flush();
// 关闭输出流，才能发送
socket.close();
// 可扩展，传入心跳时间，节约资源
while(true){
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
String contentResult = resultSet.getString(columnName);
if(contentResult == null){
contentResult = "空数据";
}
if(result.equals(contentResult)){
continue;
}
result = contentResult;
System.out.println("查询（" + sql + "）结果为：" + result);
byte[] resultBytes = result.getBytes();
try {
DatagramSocket datagramSocket = new DatagramSocket();
// 数据包发往IP：127.0.0.1，端口：10002
DatagramPacket datagramPacket = new DatagramPacket(resultBytes, resultBytes.length, InetAddress.getByName(listenIP), listenPort);
datagramSocket.send(datagramPacket);
} catch (UnknownHostException e) {
e.printStackTrace();
} catch (SocketException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}catch (JSONException e) {
e.printStackTrace();
}
}).start();
} catch (IOException e) {
e.printStackTrace();
}
}
}).start();
} catch (IOException e) {
e.printStackTrace();
}
primaryStage.setTitle("ListenTableServer");
Pane pane = new Pane(label);
primaryStage.setScene(new Scene(pane, 400, 200));
primaryStage.setX(100);
primaryStage.setY(100);
primaryStage.show();
primaryStage.addEventHandler(WindowEvent.WINDOW_CLOSE_REQUEST, event -> {
Platform.exit();
System.exit(0);
});
}
public static Connection CONN(String dbName) {
// 加载JDBC驱动
String driverName = "com.mysql.jdbc.Driver";
// 连接服务器和数据库
String dbURL = "jdbc:mysql://127.0.0.1:3306/" + dbName;
String userName = "root"; //默认用户名
String userPwd = "root"; //密码
Connection dbConn = null;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
// 如果连接成功 控制台输出Connection Successful!
System.out.println("Connection Successful!");
} catch (Exception e) {
e.printStackTrace();
}
return dbConn;
}
}
package TableListenerByUDP;
import javafx.application.Application;
import javafx.application.Platform;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.layout.Pane;
import javafx.stage.Stage;
import javafx.stage.WindowEvent;
import org.json.JSONException;
import org.json.JSONObject;
import java.io.*;
import java.net.*;
public class ListenTableClient extends Application {
public static void main(String[] args) {
launch(args);
}
@Override
public void start(Stage primaryStage) {
Label label = new Label("等待服务端回复！");
label.setTranslateY(50);
Button button = new Button("发送");
button.setOnAction(event -> {
try {
String listenIP = "127.0.0.1";
int listenPort = 10002;
new Thread(() -> {
try {
// 创建IP：127.0.0.1，端口：10002的Socket监听
DatagramSocket datagramSocket = new DatagramSocket(listenPort, InetAddress.getByName(listenIP));
while(true){
byte[] receivedData = new byte[1024];
DatagramPacket datagramPacket = new DatagramPacket(receivedData, receivedData.length);
datagramSocket.receive(datagramPacket);
// 未收到则后续不执行
Platform.runLater(() ->
label.setText(label.getText()+ "——变化为了"+ new String(datagramPacket.getData(), 0 ,datagramPacket.getLength()))
);
}
} catch (UnknownHostException e) {
e.printStackTrace();
} catch (SocketException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}).start();
// 客户端向服务端发送
Socket socket = new Socket(InetAddress.getByName("127.0.0.1"), 10086);
OutputStream outputStream = socket.getOutputStream();
PrintWriter printWriter = new PrintWriter(outputStream);
JSONObject root = new JSONObject();
try {
root.put("dbName", "test");
root.put("tableName", "test");
root.put("columnName", "content");
root.put("listenIP", listenIP);
root.put("listenPort", listenPort);
} catch (JSONException e) {
e.printStackTrace();
}
printWriter.write(root.toString());
printWriter.flush();
socket.shutdownOutput();
new Thread(() -> {
while (true) {
String message = "";
// 客户端接收服务端的反馈
InputStream inputStream;
try {
inputStream = socket.getInputStream();
if(inputStream.available() == 0){
continue;
}
//socket.setSoTimeout(3000);
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
String lineMessage;
while ((lineMessage = bufferedReader.readLine()) != null) {
message += lineMessage;
}
} catch (IOException e) {
e.printStackTrace();
}
String finalMessage = message;
Platform.runLater(() -> {
if(label.getText().equals("等待服务端回复！")){
label.setText("初始值为" + finalMessage);
button.setDisable(true);
}
});
}
}).start();
} catch (IOException e) {
e.printStackTrace();
}
});
primaryStage.setTitle("ListenTableClient");
Pane pane = new Pane(label, button);
primaryStage.setScene(new Scene(pane, 400, 200));
primaryStage.setX(500);
primaryStage.setY(100);
primaryStage.show();
primaryStage.addEventHandler(WindowEvent.WINDOW_CLOSE_REQUEST, event -> {
Platform.exit();
System.exit(0);
});
}
}
附测试所用的test数据库sql，可导入sql文件：
/*
Navicat MySQL Data Transfer
Source Server : test
Source Server Version : 50520
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50520
File Encoding : 65001
Date: 2019-02-19 15:22:15
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`content` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('18');
3965
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
