Java利用套接字实现应用程序对数据库的访问

Java利用套接字实现应用程序对数据库的访问，需要经过以下步骤：

1. 配置数据库信息：在Java应用程序中，我们可以通过配置文件比如Property文件来存储数据库信息，比如数据库名称、用户名、密码、地址、端口等等。

2. 建立连接：使用Java JDBC API中的该库驱动连接数据库。

3. 字段验证：避免SQL注入攻击，对输入的字段进行验证和过滤。

4. 构建SQL语句：使用Java JDBC API中的SQL语句构建模块，构建所需的SQL查询/修改语句。

5. 执行操作：向数据库发送SQL语句并执行，如果需要则从结果集中提取结果。

示例1：

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

// 服务器端线程，用于处理客户端请求
public class ServerThread implements Runnable {

    private Socket socket;
    // 定义客户端请求常量
    private final String REQUEST_IP = "query_by_ip";
    private final String REQUEST_NAME = "query_by_name";
    private final String REQUEST_EXIT = "exit";
    private final String RESPONSE_SUCCESS = "success";
    private final String RESPONSE_FAIL = "fail";

    public ServerThread(Socket socket) {
        this.socket = socket;
    }

    @Override
    public void run() {
        try {
            // 获取客户端请求类型
            ObjectInputStream in = new ObjectInputStream(socket.getInputStream());
            String requestType = (String) in.readObject();
            // 获取请求参数
            String requestParam = (String) in.readObject();
            // 执行数据库操作
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs;
            if (REQUEST_IP.equals(requestType)) {
                rs = stmt.executeQuery("SELECT * FROM userinfo WHERE ip='" + requestParam + "'");
            } else {
                rs = stmt.executeQuery("SELECT * FROM userinfo WHERE name='" + requestParam + "'");
            }
            // 构建返回结果
            ObjectOutputStream out = new ObjectOutputStream(socket.getOutputStream());
            if (rs.next()) {
                String result = rs.getString("name") + " " + rs.getString("ip") + " " + rs.getString("phone");
                out.writeObject(result);
                out.writeObject(RESPONSE_SUCCESS);
            } else {
                out.writeObject(null);
                out.writeObject(RESPONSE_FAIL);
            }
            // 关闭连接
            rs.close();
            stmt.close();
            conn.close();
            in.close();
            out.close();
            socket.close();
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() {
        try {
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "123456";
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

示例2：

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

// 服务器端线程，用于处理客户端请求
public class ServerThread implements Runnable {

    private Socket socket;
    // 定义客户端请求常量
    private final String REQUEST_INSERT = "insert";
    private final String REQUEST_DELETE = "delete";
    private final String RESPONSE_SUCCESS = "success";
    private final String RESPONSE_FAIL = "fail";

    public ServerThread(Socket socket) {
        this.socket = socket;
    }

    @Override
    public void run() {
        try {
            // 获取客户端请求类型
            ObjectInputStream in = new ObjectInputStream(socket.getInputStream());
            String requestType = (String) in.readObject();
            // 执行数据库操作
            Connection conn = getConnection();
            PreparedStatement stmt = null;
            if (REQUEST_INSERT.equals(requestType)) {
                // 获取请求参数
                String name = (String) in.readObject();
                String ip = (String) in.readObject();
                String phone = (String) in.readObject();
                stmt = conn.prepareStatement("INSERT INTO userinfo (name, ip, phone) VALUES (?, ?, ?)");
                stmt.setString(1, name);
                stmt.setString(2, ip);
                stmt.setString(3, phone);
                int rows = stmt.executeUpdate();
                // 构建返回结果
                ObjectOutputStream out = new ObjectOutputStream(socket.getOutputStream());
                if (rows > 0) {
                    out.writeObject(RESPONSE_SUCCESS);
                } else {
                    out.writeObject(RESPONSE_FAIL);
                }
                out.close();
            } else {
                // 获取请求参数
                int id = (int) in.readObject();
                stmt = conn.prepareStatement("DELETE FROM userinfo WHERE id = ?");
                stmt.setInt(1, id);
                int rows = stmt.executeUpdate();
                // 构建返回结果
                ObjectOutputStream out = new ObjectOutputStream(socket.getOutputStream());
                if (rows > 0) {
                    out.writeObject(RESPONSE_SUCCESS);
                } else {
                    out.writeObject(RESPONSE_FAIL);
                }
                out.close();
            }
            // 关闭连接
            stmt.close();
            conn.close();
            in.close();
            socket.close();
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() {
        try {
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "123456";
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

阅读剩余 74%