MSSQL #

<!-- Maven -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.6.1.jre11</version>
    <!-- Choose the JRE version to match your Java:
         jre8  → Java 8
         jre11 → Java 11, 17
         jre17 → Java 17+ (latest, most complete features) -->
</dependency>

<!-- HikariCP -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

// Gradle
implementation 'com.microsoft.sqlserver:mssql-jdbc:12.6.1.jre11'
implementation 'com.zaxxer:HikariCP:5.1.0'

-- Run in SQL Server Management Studio (SSMS) or Azure Data Studio
CREATE DATABASE store_db
    COLLATE Latin1_General_CI_AI;  -- CI = Case Insensitive, AI = Accent Insensitive
GO

USE store_db;
GO

-- SQL Server uses IDENTITY, NVARCHAR, BIT, DECIMAL
CREATE TABLE products (
    id          BIGINT          IDENTITY(1,1) PRIMARY KEY,
    name        NVARCHAR(255)   NOT NULL,      -- N = Unicode (supports non-Latin characters)
    price       DECIMAL(15,2)   NOT NULL,
    stock       INT             NOT NULL DEFAULT 0,
    category    NVARCHAR(100),
    active      BIT             NOT NULL DEFAULT 1,  -- BIT: 1=true, 0=false
    created_at  DATETIME2       DEFAULT GETDATE(),   -- DATETIME2 is more precise than DATETIME
    updated_at  DATETIME2       DEFAULT GETDATE()
);
GO

-- Trigger for automatic updated_at
CREATE TRIGGER trg_products_update
ON products
AFTER UPDATE
AS
BEGIN
    UPDATE products
    SET updated_at = GETDATE()
    FROM products
    INNER JOIN inserted ON products.id = inserted.id;
END;
GO

INSERT INTO products (name, price, stock, category) VALUES
    (N'ProBook Laptop', 12000000.00, 5, N'Electronics'),
    (N'Wireless Mouse', 150000.00, 20, N'Accessories'),
    (N'Mechanical Keyboard', 450000.00, 15, N'Accessories'),
    (N'27" Monitor', 3500000.00, 8, N'Electronics');
GO

// SQL Server Authentication (username + password)
String sqlAuthUrl = "jdbc:sqlserver://localhost:1433;"
    + "databaseName=store_db;"
    + "user=sa;"
    + "password=StrongSecret123!;"
    + "encrypt=true;"
    + "trustServerCertificate=true;"    // development: skip certificate validation
    + "loginTimeout=30;";

// Windows Authentication (OS authentication, no username/password in Java)
// Requires adding sqljdbc_auth.dll to PATH (Windows only)
String winAuthUrl = "jdbc:sqlserver://localhost:1433;"
    + "databaseName=store_db;"
    + "integratedSecurity=true;"
    + "encrypt=true;"
    + "trustServerCertificate=true;";

// Azure SQL Database
String azureUrl = "jdbc:sqlserver://servername.database.windows.net:1433;"
    + "databaseName=store_db;"
    + "user=admin@servername;"
    + "password=StrongSecret123!;"
    + "encrypt=true;"
    + "trustServerCertificate=false;"   // production: validate the server certificate
    + "hostNameInCertificate=*.database.windows.net;"
    + "loginTimeout=30;";

// Named instance (SQL Server Express, etc.)
String namedUrl = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;"
    + "databaseName=store_db;"
    + "integratedSecurity=true;"
    + "encrypt=true;"
    + "trustServerCertificate=true;";

import java.sql.*;

String url = "jdbc:sqlserver://localhost:1433;"
    + "databaseName=store_db;encrypt=true;trustServerCertificate=true;";

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!")) {
    System.out.println("Connected to SQL Server: "
        + conn.getMetaData().getDatabaseProductVersion());

    // SQL Server: use TOP to limit rows (not LIMIT)
    String sql = "SELECT TOP 5 id, name, price, stock FROM products "
               + "WHERE category = ? AND active = 1 ORDER BY price DESC";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, "Electronics");

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.printf("%-5d %-25s Rp%,.2f (%d units)%n",
                    rs.getLong("id"),
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("stock"));
            }
        }
    }
} catch (SQLException e) {
    System.err.println("Error: " + e.getMessage());
    System.err.println("SQL State: " + e.getSQLState());
}

// Way 1: SCOPE_IDENTITY() — simpler, but needs a separate query
String sql1 = "INSERT INTO products (name, price, stock, category) VALUES (?, ?, ?, ?); "
            + "SELECT SCOPE_IDENTITY() AS id;";

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     PreparedStatement ps = conn.prepareStatement(sql1)) {

    ps.setString(1, "1TB SSD");
    ps.setBigDecimal(2, new java.math.BigDecimal("750000.00"));
    ps.setInt(3, 30);
    ps.setString(4, "Storage");

    // execute() runs the INSERT + SELECT batch
    ps.execute();

    // Move to the second ResultSet (SELECT SCOPE_IDENTITY())
    try (ResultSet rs = ps.getResultSet()) {
        if (rs != null && rs.next()) {
            long newId = rs.getLong("id");
            System.out.println("New ID: " + newId);
        }
    }
}

// Way 2: OUTPUT clause — more modern, one statement
String sql2 = "INSERT INTO products (name, price, stock, category) "
            + "OUTPUT INSERTED.id, INSERTED.created_at "
            + "VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     PreparedStatement ps = conn.prepareStatement(sql2)) {

    ps.setString(1, "32GB RAM");
    ps.setBigDecimal(2, new java.math.BigDecimal("1200000.00"));
    ps.setInt(3, 12);
    ps.setString(4, "Components");

    // OUTPUT is received as a regular ResultSet
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            System.out.println("ID: " + rs.getLong("id"));
            System.out.println("Created: " + rs.getTimestamp("created_at"));
        }
    }
}

// Pagination: page N, M rows per page
int page    = 2;  // page 2 (0-indexed)
int size    = 10; // 10 rows per page

String sql = """
    SELECT id, name, price, stock
    FROM products
    WHERE active = 1
    ORDER BY id
    OFFSET ? ROWS           -- skip the first N rows
    FETCH NEXT ? ROWS ONLY  -- take the next M rows
    """;

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setInt(1, page * size); // OFFSET
    ps.setInt(2, size);           // FETCH NEXT

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

-- Create a stored procedure in SQL Server
CREATE PROCEDURE sp_GetProductsByCategory
    @category   NVARCHAR(100),
    @minPrice   DECIMAL(15,2) = 0      -- parameter with a default value
AS
BEGIN
    SET NOCOUNT ON;  -- suppress "rows affected" messages

    SELECT id, name, price, stock
    FROM products
    WHERE category = @category
      AND price >= @minPrice
      AND active = 1
    ORDER BY price DESC;
END;
GO

// Call the stored procedure from Java
String call = "{call sp_GetProductsByCategory(?, ?)}";

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     CallableStatement cs = conn.prepareCall(call)) {

    cs.setString(1, "Electronics");
    cs.setBigDecimal(2, new java.math.BigDecimal("1000000.00"));

    try (ResultSet rs = cs.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%s: Rp%,.2f%n",
                rs.getString("name"), rs.getDouble("price"));
        }
    }
}

CREATE PROCEDURE sp_CreateProduct
    @name       NVARCHAR(255),
    @price      DECIMAL(15,2),
    @stock      INT,
    @category   NVARCHAR(100),
    @new_id     BIGINT OUTPUT,      -- OUTPUT: return the created ID
    @message    NVARCHAR(500) OUTPUT -- OUTPUT: success/failure message
AS
BEGIN
    SET NOCOUNT ON;

    -- Check whether the name already exists
    IF EXISTS (SELECT 1 FROM products WHERE name = @name AND active = 1)
    BEGIN
        SET @new_id = -1;
        SET @message = 'A product with this name already exists';
        RETURN;
    END

    INSERT INTO products (name, price, stock, category)
    VALUES (@name, @price, @stock, @category);

    SET @new_id = SCOPE_IDENTITY();
    SET @message = 'Product created successfully with ID ' + CAST(@new_id AS NVARCHAR);
END;
GO

// Call the stored procedure with output parameters
String call = "{call sp_CreateProduct(?, ?, ?, ?, ?, ?)}";

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     CallableStatement cs = conn.prepareCall(call)) {

    // INPUT parameters
    cs.setString(1, "HD Webcam");
    cs.setBigDecimal(2, new java.math.BigDecimal("350000.00"));
    cs.setInt(3, 25);
    cs.setString(4, "Accessories");

    // Register OUTPUT parameters
    cs.registerOutParameter(5, Types.BIGINT);    // @new_id
    cs.registerOutParameter(6, Types.NVARCHAR);  // @message

    cs.execute();

    // Read the OUTPUT parameter values after execution
    long newId = cs.getLong(5);
    String message = cs.getString(6);

    System.out.println("New ID: " + newId);
    System.out.println("Message: " + message);

    if (newId == -1) {
        System.err.println("Failed to create product: " + message);
    }
}

CREATE PROCEDURE sp_Dashboard
AS
BEGIN
    SET NOCOUNT ON;

    -- ResultSet 1: general statistics
    SELECT COUNT(*) AS total_products,
           SUM(stock) AS total_stock,
           AVG(CAST(price AS FLOAT)) AS avg_price
    FROM products WHERE active = 1;

    -- ResultSet 2: top 5 most expensive products
    SELECT TOP 5 name, price FROM products
    WHERE active = 1 ORDER BY price DESC;

    -- ResultSet 3: count per category
    SELECT category, COUNT(*) AS count
    FROM products WHERE active = 1
    GROUP BY category ORDER BY count DESC;
END;
GO

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!");
     CallableStatement cs = conn.prepareCall("{call sp_Dashboard()}")) {

    boolean hasResultSet = cs.execute();

    // ResultSet 1: statistics
    if (hasResultSet) {
        try (ResultSet rs = cs.getResultSet()) {
            if (rs.next()) {
                System.out.println("Total products: " + rs.getInt("total_products"));
                System.out.println("Total stock: " + rs.getInt("total_stock"));
                System.out.printf("Average price: Rp%,.2f%n", rs.getDouble("avg_price"));
            }
        }
    }

    // Move to ResultSet 2
    if (cs.getMoreResults()) {
        try (ResultSet rs = cs.getResultSet()) {
            System.out.println("\n--- TOP 5 MOST EXPENSIVE ---");
            while (rs.next()) {
                System.out.printf("%-25s Rp%,.2f%n",
                    rs.getString("name"), rs.getDouble("price"));
            }
        }
    }

    // Move to ResultSet 3
    if (cs.getMoreResults()) {
        try (ResultSet rs = cs.getResultSet()) {
            System.out.println("\n--- PER CATEGORY ---");
            while (rs.next()) {
                System.out.println(rs.getString("category") + ": " + rs.getInt("count"));
            }
        }
    }
}

import java.sql.Connection;

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!")) {
    conn.setAutoCommit(false);

    // SET the isolation level before starting the transaction
    conn.setTransactionIsolation(
        Connection.TRANSACTION_READ_COMMITTED    // SQL Server default
        // Connection.TRANSACTION_READ_UNCOMMITTED // can read uncommitted data (dirty reads)
        // Connection.TRANSACTION_REPEATABLE_READ  // prevents non-repeatable reads
        // Connection.TRANSACTION_SERIALIZABLE     // highest level, safest, slowest
    );

    // SQL Server also supports SNAPSHOT via direct SQL
    // Enable in the database: ALTER DATABASE store_db SET ALLOW_SNAPSHOT_ISOLATION ON;
    // Then in Java: run SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    try (Statement stmt = conn.createStatement()) {
        stmt.execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT");
    }

    try {
        // ... database operations

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!")) {
    conn.setAutoCommit(false);

    try {
        // Operation 1 — succeeds
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)")) {
            ps.setString(1, "Product A");
            ps.setBigDecimal(2, new java.math.BigDecimal("100000"));
            ps.setInt(3, 5);
            ps.executeUpdate();
        }

        // Create a savepoint after the successful operation
        Savepoint sp = conn.setSavepoint("after_product_a");

        try {
            // Operation 2 — might fail
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)")) {
                ps.setString(1, "Product B");
                ps.setBigDecimal(2, new java.math.BigDecimal("-1")); // negative price
                ps.setInt(3, 3);
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            // Rollback only to the savepoint — Product A stays saved
            conn.rollback(sp);
            System.err.println("Product B failed, rolled back to savepoint: " + e.getMessage());
        }

        conn.commit(); // commit Product A
        System.out.println("Product A saved successfully.");

    } catch (SQLException e) {
        conn.rollback(); // rollback the entire transaction
        throw e;
    }
}

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class SQLServerPool {

    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:sqlserver://localhost:1433;"
            + "databaseName=store_db;"
            + "encrypt=true;"
            + "trustServerCertificate=true;");
        config.setUsername("sa");
        config.setPassword("StrongSecret123!");
        config.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        // Pool sizing
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30_000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);

        // SQL Server: connection validation query
        config.setConnectionTestQuery("SELECT 1");

        // SQL Server-specific properties
        config.addDataSourceProperty("applicationName", "FEApplication"); // shows in Activity Monitor
        config.addDataSourceProperty("sendStringParametersAsUnicode", "true");

        config.setPoolName("MSSQL-Pool");

        dataSource = new HikariDataSource(config);
    }

    public static java.sql.Connection getConnection() throws java.sql.SQLException {
        return dataSource.getConnection();
    }
}

<!-- Maven -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=store_db;encrypt=true;trustServerCertificate=true;
    username: sa
    password: StrongSecret123!
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      pool-name: MSSQL-Pool
      data-source-properties:
        applicationName: SpringBootApp
        sendStringParametersAsUnicode: true

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.SQLServerDialect
        format_sql: true
        jdbc:
          batch_size: 50

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "products",
       indexes = {
           @Index(name = "idx_products_category", columnList = "category"),
           @Index(name = "idx_products_active", columnList = "active")
       })
public class Product {

    @Id
    // SQL Server: use IDENTITY, not AUTO_INCREMENT
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // NVARCHAR in SQL Server for Unicode
    @Column(nullable = false, length = 255, columnDefinition = "NVARCHAR(255)")
    private String name;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal price;

    @Column(nullable = false)
    private Integer stock = 0;

    @Column(length = 100, columnDefinition = "NVARCHAR(100)")
    private String category;

    // BIT in SQL Server for booleans
    @Column(nullable = false, columnDefinition = "BIT DEFAULT 1")
    private Boolean active = true;

    // DATETIME2 is more precise than DATETIME
    @Column(name = "created_at", updatable = false,
            columnDefinition = "DATETIME2 DEFAULT GETDATE()")
    private LocalDateTime createdAt;

    @Column(name = "updated_at",
            columnDefinition = "DATETIME2 DEFAULT GETDATE()")
    private LocalDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = LocalDateTime.now();
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }

    // Constructors, getters, setters
    public Product() {}
    public Product(String name, BigDecimal price, int stock, String category) {
        this.name = name; this.price = price; this.stock = stock; this.category = category;
    }
    // ... getters and setters
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Method name convention — same as MySQL
    List<Product> findByActiveTrue();
    List<Product> findByCategoryAndActiveTrue(String category);

    // JPQL — works the same on all databases
    @Query("SELECT p FROM Product p WHERE p.active = true ORDER BY p.price DESC")
    List<Product> findAllOrderByPrice();

    // Native SQL Server query — use TOP, NVARCHAR, and SQL Server syntax
    @Query(value = """
        SELECT TOP(:limit) id, name, price, stock
        FROM products
        WHERE active = 1
          AND LOWER(name) LIKE LOWER(CONCAT('%', :keyword, '%'))
        ORDER BY price DESC
        """, nativeQuery = true)
    List<Object[]> searchProductsNative(@Param("keyword") String keyword, @Param("limit") int limit);

    // Call a stored procedure via @Query nativeQuery
    @Query(value = "EXEC sp_GetProductsByCategory :category, :minPrice", nativeQuery = true)
    List<Object[]> callStoredProcedure(@Param("category") String category,
                                       @Param("minPrice") BigDecimal minPrice);

    // SQL Server full-text search (requires a Full-Text Index to be active)
    @Query(value = """
        SELECT id, name, price, stock
        FROM products
        WHERE CONTAINS(name, :keyword) AND active = 1
        """, nativeQuery = true)
    List<Object[]> fullTextSearch(@Param("keyword") String keyword);

    // Pagination with OFFSET FETCH
    @Query(value = """
        SELECT id, name, price, stock
        FROM products
        WHERE active = 1
        ORDER BY id
        OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
        """, nativeQuery = true)
    List<Object[]> findWithPagination(@Param("offset") int offset, @Param("limit") int limit);
}

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;

// Prepare the source data (can come from another ResultSet, CSV, etc.)
// Here we simulate it with an in-memory ResultSet
String sourceSql = "SELECT name, price, stock, category FROM staging_products";

try (Connection sourceConn = DriverManager.getConnection(stagingUrl, "sa", "pass");
     Connection destConn = DriverManager.getConnection(url, "sa", "StrongSecret123!")) {

    // Bulk copy options
    SQLServerBulkCopyOptions options = new SQLServerBulkCopyOptions();
    options.setBatchSize(1000);          // process 1000 rows per batch
    options.setBulkCopyTimeout(600);     // 10-minute timeout
    options.setCheckConstraints(true);   // validate constraints
    options.setFireTriggers(true);       // fire triggers

    try (SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destConn)) {
        bulkCopy.setBulkCopyOptions(options);
        bulkCopy.setDestinationTableName("products");

        // Source → destination column mapping (if names differ)
        bulkCopy.addColumnMapping("name", "name");
        bulkCopy.addColumnMapping("price", "price");
        bulkCopy.addColumnMapping("stock", "stock");
        bulkCopy.addColumnMapping("category", "category");

        // Run the bulk copy
        try (PreparedStatement ps = sourceConn.prepareStatement(sourceSql);
             ResultSet rs = ps.executeQuery()) {
            bulkCopy.writeToServer(rs);
        }

        System.out.println("Bulk copy finished.");
    }
}

-- Create a table type in SQL Server
CREATE TYPE ProductTableType AS TABLE (
    name      NVARCHAR(255),
    price     DECIMAL(15,2),
    stock     INT,
    category  NVARCHAR(100)
);
GO

-- Stored procedure that accepts a TVP
CREATE PROCEDURE sp_BulkInsertProducts
    @productList ProductTableType READONLY
AS
BEGIN
    INSERT INTO products (name, price, stock, category)
    SELECT name, price, stock, category FROM @productList;

    SELECT @@ROWCOUNT AS insert_count;
END;
GO

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;
import java.sql.Types;

// Create a data table for the TVP
SQLServerDataTable tvp = new SQLServerDataTable();
tvp.addColumnMetadata("name",     java.sql.Types.NVARCHAR);
tvp.addColumnMetadata("price",    java.sql.Types.DECIMAL);
tvp.addColumnMetadata("stock",    java.sql.Types.INTEGER);
tvp.addColumnMetadata("category", java.sql.Types.NVARCHAR);

// Fill the data
tvp.addRow("Product X", new java.math.BigDecimal("100000"), 5, "General");
tvp.addRow("Product Y", new java.math.BigDecimal("200000"), 3, "Premium");
tvp.addRow("Product Z", new java.math.BigDecimal("300000"), 8, "General");

try (Connection conn = DriverManager.getConnection(url, "sa", "StrongSecret123!")) {
    String call = "{call sp_BulkInsertProducts(?)}";

    try (SQLServerPreparedStatement ps =
            (SQLServerPreparedStatement) conn.prepareStatement(call)) {

        // Set the TVP as a parameter
        ps.setStructured(1, "ProductTableType", tvp);

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                System.out.println("Inserted successfully: " + rs.getInt("insert_count") + " rows");
            }
        }
    }
}