Java JDBC Connection with SQL Server

In this tutorial, we will explore how to establish a JDBC connection with a SQL Server database in Java. We will cover the steps to set up the SQL Server database, establish a connection using JDBC, and perform basic operations such as inserting and querying data.

Introduction

What is JDBC?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with databases. It provides methods to query and update data in a database, as well as to retrieve metadata about the database itself.

SQL Server Database

SQL Server is a relational database management system developed by Microsoft. It is widely used for storing, managing, and retrieving data in various applications.

Table of Contents

1. Setting Up the SQL Server Database
2. Adding SQL Server JDBC Driver to Your Project
3. Establishing a Database Connection
4. Executing SQL Queries
5. Retrieving Data
6. Closing the Connection
7. Conclusion

1. Setting Up the SQL Server Database

First, create a database named jdbc_example and a products table within it. Use SQL Server Management Studio or any SQL Server client to execute the following commands:

CREATE DATABASE jdbc_example;

USE jdbc_example;

CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(255),
    price DECIMAL(10, 2)
);

INSERT INTO products (name, description, price) VALUES
('Laptop', 'Dell Inspiron', 75000.00),
('Smartphone', 'Samsung Galaxy', 30000.00),
('Tablet', 'Apple iPad', 50000.00);

2. Adding SQL Server JDBC Driver to Your Project

To interact with a SQL Server database, you need to add the SQL Server JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.4.0.jre8</version>
</dependency>

3. Establishing a Database Connection

We will start by establishing a connection to the SQL Server database using JDBC.

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

public class JDBCExample {
    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=jdbc_example";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the SQL Server database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Executing SQL Queries

We can use the Connection interface to create a Statement object and execute SQL queries. Here, we will insert a record into the products table.

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

public class InsertProductExample {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO products (name, description, price) VALUES ('Smartwatch', 'Apple Watch Series 6', 40000.00)";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            int rowsInserted = statement.executeUpdate(insertSQL);
            System.out.println(rowsInserted + " row(s) inserted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Retrieving Data

We can use the Connection interface to create a Statement object and execute a query to retrieve data from the products table.

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

public class SelectProductExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

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

public class CloseConnectionExample {
    public static void main(String[] args) {
        try (Connection connection = JDBCExample.getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the SQL Server database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using the JDBC Connection interface to interact with a SQL Server database. We demonstrated how to establish a connection, execute SQL queries, retrieve data, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use the Connection interface effectively with SQL Server.