SQL Server CRUD Tutorials in Java: A Step-by-Step Guide

SQL Server is a robust and widely used relational database management system (RDBMS) developed by Microsoft. In this tutorial, we will guide you through the process of performing CRUD (Create, Read, Update, Delete) operations in SQL Server using Java. We’ll provide step-by-step instructions and practical examples with detailed explanations.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

Java Development Kit (JDK): Ensure you have Java installed on your system. You can download it from the official Oracle website or use an open-source alternative like OpenJDK.
Integrated Development Environment (IDE): Choose an IDE like Eclipse, IntelliJ IDEA, or NetBeans to write and manage your Java code.
SQL Server: You need to have SQL Server installed and running. You can download SQL Server Developer Edition for free from the official Microsoft SQL Server website.
Microsoft JDBC Driver for SQL Server: You’ll need the Microsoft JDBC driver to connect to the SQL Server database from your Java application. Download the JDBC driver JAR file and include it in your project’s classpath.

Step 1: Create a SQL Server Database

Let’s start by creating a SQL Server database where we will perform CRUD operations. You can use SQL Server Management Studio (SSMS) or any SQL client for SQL Server. Here’s how to do it using SSMS:

Install SQL Server and launch SQL Server Management Studio.
Connect to your SQL Server instance using the appropriate credentials.
In SSMS, right-click on “Databases” in the Object Explorer and select “New Database.”
Provide a name for your database (e.g., mydb) and configure any other settings you need.
Click “OK” to create the database.

Step 2: Set Up a Java Project

Open your preferred IDE and create a new Java project.

Add the Microsoft JDBC driver dependency to your project’s build file (e.g., Maven’s pom.xml or Gradle’s build.gradle).

For Maven, add the following dependency:

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

For Gradle, add the following dependency:

implementation group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '9.4.0.jre8'

Step 3: Perform CRUD Operations

Create Operation (Insert)

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

public class SQLServerCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String insertQuery = "INSERT INTO employees (first_name, last_name, age) VALUES (?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.setInt(3, 30);
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code inserts a new employee record into the employees table in the SQL Server database.

Read Operation (Select)

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

public class SQLServerCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String selectQuery = "SELECT * FROM employees";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                int age = resultSet.getInt("age");

                System.out.println("ID: " + id + ", Name: " + firstName + " " + lastName + ", Age: " + age);
            }

            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code retrieves and prints all employee records from the employees table.

Update Operation (Update)

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

public class SQLServerCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String updateQuery = "UPDATE employees SET age = ? WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
            preparedStatement.setInt(1, 31);
            preparedStatement.setString(2, "John");
            preparedStatement.setString(3, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code updates the age of the employee named “John Doe” in the employees table.

Delete Operation (Delete)

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

public class SQLServerCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String deleteQuery = "DELETE FROM employees WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code deletes the employee named “John Doe” from the employees table.

Conclusion

In this tutorial, you’ve learned how to perform CRUD operations in SQL Server using Java. You created a SQL Server database, set up a Java project, and wrote code for Create, Read, Update, and Delete operations with detailed explanations. SQL Server’s robustness and integration with the Microsoft ecosystem make it a popular choice for enterprise-level applications. Mastering these

fundamental database operations will empower you to build data-driven applications with SQL Server and Java. As you continue your journey in Java development and SQL Server, you can apply these concepts to more complex and feature-rich database applications. Happy coding!