[Java] [SQL Server] Connect to local SQL Server 2017 using JDBC for SQL Server

I leave it as a reminder

Environment </ b>

Type Tool name Remarks
OS Windows 10 Pro
IDE Pleiades 2018 Use full edition of Java
Java Java8
DB SQL Server 2017 Developer

Add SQL Server JDBC to your Java project </ b>

① Download the corresponding version of JDBC ② Unzip to "C: \ Program Files"

  • Microsoft seems to recommend saving to the above program folder
  • It is specified in install.txt in the unzipped JDBC. (3) Move the JDBC JAR file to any folder in the Eclipse project.
  • I used "mssql-jdbc-7.2.1.jre8.jar" and made "link to file" without copying the file. Select Properties from the context menu (right-click) in the project to which you want to add JDBC in Project Explorer. (4) Select the "Library" tab in the Java build path and press the "Add JAR" button. ⑤ Select the folder containing JDBC and press the "OK" button. ⑥ Click the "Apply and Close" button

Error details </ b>

`com.microsoft.sqlserver.jdbc.SQLServerException: TCP / IP connection to host [servername], port 1433 failed. Error: "Connection refused: connect. Check the connection properties and an instance of SQL Server is running on the host, TCP / IP connection to the port is allowed, and TCP connection to that port is blocked by the firewall. Make sure it is not. ". ``

Cause </ b>

As described. In my case, the above error occurred because I did not allow TCP / IP communication of SQL Server.

Solution </ b>

  • Other causes will be described when time is available.

(1) Start SQL Server Configuration Manager with "SQLServerManager14.msc" in the execution dialog by specifying the file name displayed in Windows + R. (2) SQL Server Configuration Manager> SQL Server Network Configuration> Select the protocol of MSSQLSERVER, select TCP / IP, and select Enable from the context menu (right-click display). (3) Select SQL Server (MSSQLSERVER) in SQL Server Configuration Manager> SQL Server Services, and select "Restart" from the context menu (right-click display) to restart the SQL Server service. ④ Execute the following code

Test code for connection confirmation

  • Please change to any value

ConnectURL.java



package testdbconnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectURL {
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://[servername:[port];databaseName=[dbname];user=[username];password=[password]";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
            String SQL = "SELECT TOP 10 * FROM [tablename]";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("[columnname1]") + " " + rs.getString("[columnname2]"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
            System.out.println("can't connectioned");
        }
    }
}

Recommended Posts