java - 如何从jdbc连接中知道客户端ip端口

Question

我正在测试 MSSQL。 “如何长连接后创建的 session 信息？”

因此，尝试使用 MSSQL JDBC 连接，然后选择带有客户端 Ip 和端口的 sys.dm_exec_session 和 sys.dm_exec_connection。

但是，我不知道如何从 JDBC 连接获取客户端 IP 和端口。

或者是否可以使用套接字创建 JDBC 连接？

显示我的测试代码。

try {
        DriverManager.registerDriver(
                new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        DriverManager.setLoginTimeout(10);
    } catch (Exception ex) {
        System.out.println("Fail to register sqlserver driver.");
        return;
    }

    String url = "jdbc:sqlserver://";
    String ip = "10.1.1.1";
    String port = "1433";
    String catalog = "master";
    String user = "jtlee";
    String pwd = "123456789";

    url = url + ip + ":" + port + ";databasename=" + catalog;

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        long startTime = System.nanoTime();

        conn = DriverManager.getConnection(url, user, pwd);
        pstmt = conn.prepareStatement(
                "SELECT "
                + "     count(*) "
                + "FROM "
                + "     sys.dm_exec_connections connections,"
                + "     sys.dm_exec_sessions sessions "
                + "WHERE "
                + "     connections.session_id = sessions.session_id "
                + "     and connections.client_net_address= ? "
                + "     and connections.client_tcp_port= ? ");

        /* I need this information... */
        String clientIp;
        int clientPort;

        pstmt.setString(1, clientIp);
        pstmt.setInt(2, clientPort);
        rs = pstmt.executeQuery();

        if (rs.next()) {
            ...

感谢您阅读我奇怪的英语。

Answer 1

如果您想获取 Activity 连接的 IP 和端口，您只需使用 WHERE session_id=@@SPID 查询 sys.dm_exec_connections，例如，

sql = 
        "SELECT client_net_address, client_tcp_port, local_net_address, local_tcp_port " + 
        "FROM sys.dm_exec_connections " + 
        "WHERE session_id=@@SPID";
try (
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql)) {
    while (rs.next()) {
        System.out.printf("Connection is between %s:%d on the server and %s:%d as the client.%n", 
                rs.getString("local_net_address"),
                rs.getInt("local_tcp_port"),
                rs.getString("client_net_address"),
                rs.getInt("client_tcp_port")
                );
    }
}

制作

Connection is between 192.168.1.12:1433 on the server and 192.168.1.101:49744 as the client.