How do I connect to SQL Server using JDBC?

To connect to Microsoft SQL Server using JDBC, you’ll need the Microsoft JDBC Driver for SQL Server. Using Maven, you can add the dependency and use the DriverManager to establish a connection.

Here is the step-by-step process:

1. Add the Dependency

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.8.1.jre11</version> <!-- Check for the latest version -->
</dependency>

2. SQL Server Connection Details

The connection URL for SQL Server typically follows this format:

jdbc:sqlserver://[serverName][\instanceName]:[portNumber];databaseName=[databaseName];encrypt=true;trustServerCertificate=true;

• encrypt=true: Required by modern versions of the driver for security.
• trustServerCertificate=true: Useful for development/local environments where you might not have a formal SSL certificate installed.

3. Implementation Example

Here is a clean example using try-with-resources (which is best practice to ensure connections are closed automatically):

package org.kodejava.jdbc;

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

public class SqlServerConnection {
    // Replace with your actual database details
    private static final String URL = 
        "jdbc:sqlserver://localhost:1433;databaseName=YourDB;encrypt=true;trustServerCertificate=true;";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String query = "SELECT TOP 10 * FROM your_table";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                // Access your data
                System.out.println("Data: " + rs.getString(1));
            }

            System.out.println("Connected to SQL Server successfully!");

        } catch (SQLException e) {
            System.err.println("Error connecting to SQL Server:");
            e.printStackTrace();
        }
    }
}

Quick Tips for SQL Server:

• Default Port: SQL Server usually listens on port 1433.
• Authentication: If you want to use Windows Authentication (Integrated Security), you would add ;integratedSecurity=true; to the URL, but this requires the sqljdbc_auth.dll to be in your Java library path.
• Driver Class: In modern JDBC, you don’t need to manually call Class.forName(). The driver is loaded automatically.