与Java连接时出现SQLException错误 [英] SQLException error when connecting with java

问题描述

我一直面临着一个简单的SQL连接错误，并且我厌倦了修复它.我先安装了sql server 2014，然后又安装了sql server 2017，将规则添加到防火墙，甚至关闭了防火墙.服务多次重新启动. 去了配置管理器，启用了所有功能，添加了所需的所有jar，完成了清理和构建，完成了对google中此问题的所有搜索，仍然一次又一次出现相同的问题.使用的系统是 -Windows 10. -SQL Server管理Studio 2017. -Netbean 8.1

I have been facing a simple sql connection error, and i m tired of fixing it. I installed sql server 2014 and then sql server 2017, add rules to firewall and even turned off the firewall. services restarted multiple times. Went to configuration manager, enabled everything, added all the jar required, done clean and build, done all searches on this issue in google, still that same issue comes in again and again. System used are - Windows 10. - Sql server management studio 2017. - Netbean 8.1

代码:-

    import java.sql.*;
    public class DbConnect  { 
    public static void main(String[] args) throws SQLException,ClassNotFoundException {
    String url = "jdbc:sqlserver://localhost:1433;databaseName=productlist;user=db2017;password=db2017";

//commented :-String url = "jdbc:sqlserver://DESKTOP-7CI6DU0\\NIT2017:1433;databaseName=productlist;user=db2017;password=db2017";

                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
                Connection conn = DriverManager.getConnection(url);
                System.out.println("test");
                Statement sta = conn.createStatement();
                String Sql = "select * from productlist";
                ResultSet rs = sta.executeQuery(Sql);
                while (rs.next()) {
                System.out.println(rs.getString("CatName"));                 
        }  }  }

经过大量的故障排除之后，下面是异常错误再次出现.

Below is exception error comes again again, after so much troubleshooting.

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at DbConnect.main(DbConnect.java:11)
C:\Users\Nitish\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1

放弃之前，想过要在这里检查一下...

Before giving up, thought to check here...

推荐答案

您注释掉的连接字符串建议您连接到命名实例.除非另行配置，否则默认的SQL Server实例(未命名)在端口1433上进行侦听.除非另外配置，否则命名实例使用的动态端口不是1433.每台计算机只能有一个默认实例，每个SQL实例必须在不同的端口上侦听.

Your commented-out connection string suggests you are connecting to a named instance. A default SQL Server instance (not-named) listens on port 1433 unless configured otherwise. A named instance uses a dynamic port, which is not 1433, unless configured otherwise. One can have only one default instance per machine and every SQL instance must listen on a different port.

您可以使用 SQL Server配置管理器或查看SQL Server错误日志.错误日志将包含每个接口和SQL Server正在侦听的端口的消息.该消息将类似于服务器正在侦听['any'1433].".可以使用SSCM更改端口，该端口将在重新启动SQL Service后生效.

You can identify the port a SQL instance is listening on using SQL Server Configuration Manager or by viewing the SQL Server error log. The error log will include messages for each interface and port SQL Server is listening on. The message will be like "Server is listening on [ 'any' 1433]." The port can be changed using SSCM and will be effective after restarting the SQL Service.

要测试端口连接性，可以使用TELNET:

To test port connectivity, you can use TELNET:

TELNET YourServer 1433

如果连接成功，您将看到并清空窗口，否则显示错误.

You'll see and empty window if the connection is successful, otherwise and error.

如果未安装TELNET，则可以从命令提示符窗口中使用此Powershell命令测试端口连接:

If you don't have TELNET installed, you can test the port connectivity with this Powershell command from a command-prompt window:

powershell -Command echo ((new-object Net.Sockets.TcpClient).Client.Connect('YourServer', 1433)) 'success'

成功连接后，您将看到成功消息，否则会显示套接字异常.

You'll see the success message upon successful connection, otherwise a socket exception.

请注意，动态端口是在安装过程中分配的，实例将在每次启动时尝试使用相同的端口.但是，请注意，如果该端口在启动时不可用，则端口号可能会更改.可以使用SSCM配置静态端口(包括端口1433)，以避免更改命名实例的端口号.

Note that the dynamic port is assigned during installation and the instance will attempt to use the same port upon each startup. However, be aware the port number could change if that port is unavailable at startup. One can configure a static port (including port 1433) using SSCM to avoid the port number changes of a named instance.

这篇关于与Java连接时出现SQLException错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！