How to Connect to SQL Server from Java using JDBC

Summary: in this tutorial, you will learn how to connect to the SQL Server from a Java program.

Checking Java & JDK

Step 1. Open a terminal on your computer.

Step 2. Run the following command:

java -versionCode language: Java (java)

It’ll return the following information like this:

java version "22.0.2" 2024-07-16
Java(TM) SE Runtime Environment (build 22.0.2+9-70)
Java HotSpot(TM) 64-Bit Server VM (build 22.0.2+9-70, mixed mode, sharing)Code language: Java (java)

The output shows the Java version 22.0.2

To verify the JDK compiler, you can issue the javac command:

javac -versionCode language: Java (java)

Output:

javac 22.0.2Code language: Java (java)

If you see an error instead, you need to install JDK on your computer.

Downloading Microsoft JDBC Driver for SQL Server

Step 1. Open the Download Microsoft JDBC Driver for SQL Server page.

Step 2. Download Microsoft JDBC Driver for SQL Server.

Step 3. Unzip the download file and copy the file mssql-jdbc-12.6.3.jre11.jar to a directory such as D:\jdbc driver\mssql. We’ll add this file to the library of a Java project in the next step.

Creating a new SQL Server database

We’ll create a new database on the local SQL Server called BookStore. Please follow this tutorial to create it.

Creating a new Java Project

Step 1. Launch the IntelliJ IDE.

Step 2. Create a new project called SQLServerJava.

Step 3. Right-click the project name and choose the Open Module Settings.

Step 4. Choose the Libraries under Project Settings and click New Project Library.

Step 5. Select the Microsoft JDBC Driver for the SQL Server file D:\jdbc driver\mssql\mssql-jdbc-12.6.3.jre11.jar and click the OK button.

Creating a Java Program

Step 1. In the src directory, create a new file Main.java that stores the Main class:

import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        // Register JDBC driver
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            System.err.println("SQL exception occurred while connecting to the SQL Server.");
            e.printStackTrace();
            return;
        }

        // Connect to the SQL Server
        var url = "jdbc:sqlserver://localhost:1433;databaseName=BookStore;encrypt=true;trustServerCertificate=true;";
        var user = "joe";
        var password = "YourPassword";

        try (var connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to the SQL Server database successfully.");
        } catch (SQLException e) {
            System.err.println("SQL exception occurred while connecting to the SQL Server.");
            e.printStackTrace();
        }
    }
}Code language: Java (java)

How it works.

First, import DriverManager and SQLException classes from the java.sql package:

import java.sql.DriverManager;
import java.sql.SQLException;Code language: Java (java)

Second, register the JDBC driver:

try {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
    System.err.println("SQL exception occurred while connecting to the SQL Server.");
    e.printStackTrace();
    return;
}Code language: Java (java)

Third, initialize the url, user, and password for connecting to the SQL Server:

var url = "jdbc:sqlserver://localhost:1433;databaseName=BookStore;encrypt=true;trustServerCertificate=true;";
var user = "joe";
var password = "YourStrongPassword";Code language: Java (java)

The url represents the connection string to connect Java to SQL Server, which has the following structure:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]Code language: Java (java)

In this syntax:

serverName represents the name or IP address of the server where the SQL Server is currently running.
instanceName: an optional SQL Server instance name. It is the SQL Server service name on Windows.
portNumber: the port number on which the SQL Server listens. The default port number is 1433.
property=value specifies the connection settings.

In the Java program, the url allows us to connect to the BookStore database in a local SQL Server and port 1433.

The connection settings encrypt=true;trustServerCertificate=true; are required to connect to the local SQL Server.

The user should have sufficient permission to connect to the BookStore database.

The password is the password of the user account.

Fourth, establish a connection to the SQL Server by calling the getConnection static method of the DriverManger class:

try (var connection = DriverManager.getConnection(url, user, password))Code language: Java (java)

Since we use the try-with-resources statement, the database connection will be automatically closed once it goes out of scope.

Fifth, display a message that indicates the connection was successful:

System.out.println("Connected to the SQL Server database successfully.");Code language: Java (java)

Finally, show an error message with a detailed stack trace if there were any errors during the connection:

System.err.println("SQL exception occurred while connecting to the SQL Server.");
e.printStackTrace();Code language: Java (java)

Step 2. Run the Java program.

If you see the following output, you’ve successfully connected to the SQL Server from the Java program:

Connected to the SQL Server database successfully.Code language: Java (java)

The program works fine but is not secure because it has hardcoded information including a user account with a password within the source code.

To fix this, you can store the sensitive information in a configuration file such as a .properties file.

Creating database configuration file

Step 1. Create a new file db.properties file in the src directory.

Step 2. Add the connection parameters to the db.properties file:

db.url=jdbc:sqlserver://localhost:1433;databaseName=BookStore;encrypt=true;trustServerCertificate=true;
db.username=joe
db.password=YourStrongPasswordCode language: Java (java)

In this db.properties file, we define three parameters db.url, db.username, and db.password. To use these parameters in the program, we can define a new class.

Defining Config class

Create a new Config class that reads the db.properties file:

import java.io.IOException;
import java.util.Properties;

public class Config {

    private static final Properties properties = new Properties();

    static {
        try (var input = Config.class.getClassLoader().getResourceAsStream("db.properties")) {
            if (input == null) {
                System.err.println("Sorry, could not find the db.properties file");
                System.exit(1);
            }

            // Load the properties file
            properties.load(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String getDbUrl() {
        return properties.getProperty("db.url");
    }

    public static String getDbUsername() {
        return properties.getProperty("db.username");
    }

    public static String getDbPassword() {
        return properties.getProperty("db.password");
    }
}Code language: Java (java)

The Config class reads the connection parameters from the db.properties file. It includes three static methods that expose the database parameters:

getDbUrl() – Return the database URL.
getDbUsername() – Return the database username.
getDbPassword() – Return the database password.

Create a DBException class

public class DBException extends Exception {
    public DBException(String message) {
        super(message);
    }
}Code language: JavaScript (javascript)

Creating SQLSeverConnection class

Create a reusable SQLServerConnection class that connects to the SQL Server:

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

public class SQLServerConnection {
    public static Connection connect() throws DBException {

        try {
            // Register JDBC Driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Create a new connection
            return DriverManager.getConnection(
                    Config.getDbUrl(),
                    Config.getDbUsername(),
                    Config.getDbPassword()
            );

        } catch (SQLException | ClassNotFoundException e) {
            throw new DBException(e.getMessage());
        }
    }
}Code language: Java (java)

Modifying the Main class

Modify the main() method of the Main class to use the SQLServerConnection class to connect to the SQL Server:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try(var connection = SQLServerConnection.connect()){
            System.out.println("Connected to the SQL Server database successfully!");
        } catch (SQLException | DBException e){
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

If you run the program and see the following message, you’ve connected successfully to the SQL Server using JDBC:

Connected to the SQL Server database successfully!Code language: Java (java)

We’ll utilize the SQLSeverConnection class in the next tutorials.

Download the project source code

Summary

Download Microsoft JDBC Driver for SQL Server and use it to connect to a SQL Server from a Java program.

Was this tutorial helpful?