Socket实战——查询数据库

最新推荐文章于 2023-09-06 19:35:34 发布

原创最新推荐文章于 2023-09-06 19:35:34 发布 · 2.5k 阅读

19 ·

CC 4.0 BY-SA版权

Java 同时被 2 个专栏收录

158 篇文章

订阅专栏

Socket

11 篇文章

订阅专栏

本文详细介绍了一种使用TCP协议通过Socket进行数据库查询的方法。客户端发送数据库、表及属性信息，服务端接收并执行查询，将结果返回给客户端。文章包括了服务端和客户端的Java代码实现，以及用于测试的SQL脚本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Socket实战系列：
Socket实战——UDP连接：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601468
Socket实战——TCP连接：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601522
Socket实战——查询数据库：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601566
Socket实战——监听数据库：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601584
Socket实战——聊天：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601771
Socket实战——文件上传：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86601850
Socket实战——文件下载：https://blog.youkuaiyun.com/haoranhaoshi/article/details/86632897

项目下载地址（IDEA搭建）：https://download.youkuaiyun.com/download/haoranhaoshi/10933044

每次点击发送，客户端与服务端建立TCP的Socket连接，客户端发送数据库、表、属性，服务端返回属性值的查询结果。

package TableSelectByTCP;

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.InetAddress;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.*;

/**
 * 每次点击发送，客户端与服务端建立TCP的Socket连接，客户端发送数据库、表、属性，服务端返回属性值的查询结果
 */
public class SelectTableServer 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传入数据库、表、属性、筛选条件等
                            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");
                                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.shutdownOutput();
                                }
                            } 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("SelectTableServer");
        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 TableSelectByTCP;

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.InetAddress;
import java.net.Socket;

public class SelectTableClient 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 {
                // 客户端向服务端发送
                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");
                } 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;
                            }

                            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);
                            }else{
                                label.setText(label.getText() + "——变化为了"+ finalMessage);
                            }
                        });
                    }
                }).start();
            } catch (IOException e) {
                e.printStackTrace();
            }
        });

        primaryStage.setTitle("SelectTableClient");
        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');