SQL Server Integration

<!-- pom.xml -->
<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>

# application.yml
spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=mydb;encrypt=true;trustServerCertificate=true
    username: app_user          # not sa; see the note below
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      max-lifetime: 1200000     # keep below any firewall/load balancer idle cut-off
      keepalive-time: 60000
      pool-name: mssql-pool

  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false
    properties:
      hibernate:
        jdbc:
          batch_size: 25
        order_inserts: true
        order_updates: true

# Production URLs
# Azure SQL, password auth:
#   jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;
#   encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30
# Azure SQL, managed identity (no password):
#   jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;
#   encrypt=true;authentication=ActiveDirectoryManagedIdentity
# Windows integrated auth also needs the mssql-jdbc auth DLL on java.library.path.

@Entity
@Table(name = "products", schema = "dbo",
       indexes = {
           @Index(name = "idx_products_category", columnList = "category"),
           @Index(name = "idx_products_sku", columnList = "sku", unique = true)
       })
public class Product {

    // Batch-friendly alternative to IDENTITY (create the sequence in a migration):
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "product_seq")
    @SequenceGenerator(name = "product_seq", sequenceName = "product_seq", allocationSize = 50)
    private Long id;

    // If you do not bulk insert, plain IDENTITY is fine:
    // @Id @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Version
    private long version;

    @Column(name = "product_name", nullable = false, length = 200)   // NVARCHAR(200)
    @Nationalized
    private String name;

    @Column(length = 100)
    @Nationalized
    private String category;

    @Column(length = 50, unique = true)
    private String sku;

    @Column(name = "description", columnDefinition = "NVARCHAR(MAX)")
    private String description;

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

    @Column(nullable = false)                       // BIT
    private boolean active = true;

    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;                      // DATETIME2

    @Column(name = "updated_at")
    private Instant updatedAt;

    @Column(name = "thumbnail", columnDefinition = "VARBINARY(MAX)")
    private byte[] thumbnail;

    // UUID primary keys: map to UNIQUEIDENTIFIER. Random UUIDs fragment a clustered
    // index badly; keep the clustered key a BIGINT and use the UUID as a secondary key.
    @Column(name = "public_id", nullable = false, updatable = false)
    private UUID publicId = UUID.randomUUID();

    @PrePersist void onCreate() { createdAt = updatedAt = Instant.now(); }
    @PreUpdate  void onUpdate() { updatedAt = Instant.now(); }
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    Optional<Product> findBySku(String sku);

    // Concurrency-safe upsert: try the update, insert only if nothing matched.
    @Modifying
    @Query(value = """
            UPDATE products SET product_name = :name, price = :price, updated_at = SYSUTCDATETIME()
            WHERE sku = :sku
            """, nativeQuery = true)
    int updateBySku(@Param("sku") String sku, @Param("name") String name,
                    @Param("price") BigDecimal price);

    // If you still want MERGE, hold the key range with HOLDLOCK and end with a semicolon:
    @Modifying
    @Query(value = """
            MERGE products WITH (HOLDLOCK) AS target
            USING (VALUES (:sku, :name, :price)) AS source (sku, name, price)
              ON target.sku = source.sku
            WHEN MATCHED THEN
              UPDATE SET target.product_name = source.name, target.price = source.price,
                         target.updated_at = SYSUTCDATETIME()
            WHEN NOT MATCHED THEN
              INSERT (sku, product_name, price, created_at)
              VALUES (source.sku, source.name, source.price, SYSUTCDATETIME());
            """, nativeQuery = true)
    void merge(@Param("sku") String sku, @Param("name") String name,
               @Param("price") BigDecimal price);

    // Paged native query: ORDER BY is mandatory, and countQuery must be supplied.
    @Query(value = "SELECT * FROM products WHERE active = 1 ORDER BY created_at DESC",
           countQuery = "SELECT COUNT(*) FROM products WHERE active = 1",
           nativeQuery = true)
    Page<Product> findActive(Pageable pageable);

    // Window functions, mapped to an interface projection instead of Object[]:
    interface CheapestInCategory { String getCategory(); String getName(); BigDecimal getPrice(); }

    @Query(value = """
            SELECT category, product_name AS name, price
            FROM (SELECT category, product_name, price,
                         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) AS rn
                  FROM products WHERE active = 1) ranked
            WHERE rn = 1
            """, nativeQuery = true)
    List<CheapestInCategory> findCheapestPerCategory();

    // Contains-search: pass the wildcards in from the caller, e.g. "%" + term + "%".
    @Query(value = "SELECT * FROM products WHERE product_name LIKE :pattern", nativeQuery = true)
    List<Product> searchByName(@Param("pattern") String pattern);
}

// FOR JSON PATH: avoid returning it through JPA. The driver splits long JSON results
// across multiple rows of about 2,000 characters, so a single String return breaks on
// larger data sets. Use JdbcTemplate and join the rows if you really need it:
//   String json = String.join("", jdbc.queryForList(sql, String.class));

@Service
@Transactional
public class OrderProcedureService {

    @PersistenceContext
    private EntityManager em;

    public OrderResult processOrder(long orderId) {
        StoredProcedureQuery q = em.createStoredProcedureQuery("ProcessOrder")
            .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, String.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(3, BigDecimal.class, ParameterMode.OUT)
            .setParameter(1, orderId);

        q.execute();
        return new OrderResult(orderId,
            (String) q.getOutputParameterValue(2),
            (BigDecimal) q.getOutputParameterValue(3));
    }

    // Table-valued function: it is just a native query.
    @SuppressWarnings("unchecked")
    public List<Object[]> productsInRange(BigDecimal min, BigDecimal max) {
        return em.createNativeQuery("""
                SELECT id, product_name, price
                FROM dbo.GetProductsByPriceRange(:min, :max)
                ORDER BY price
                """)
            .setParameter("min", min)
            .setParameter("max", max)
            .getResultList();
    }
}

-- V3__create_procedures.sql
CREATE PROCEDURE dbo.ProcessOrder
    @order_id BIGINT,
    @status   NVARCHAR(50)   OUTPUT,
    @total    DECIMAL(10,2)  OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @total = SUM(quantity * unit_price) FROM dbo.order_items WHERE order_id = @order_id;
    SET @status = N'PROCESSED';
    UPDATE dbo.orders SET status = @status, total = @total WHERE id = @order_id;
END;
GO

CREATE FUNCTION dbo.GetProductsByPriceRange (@min_price DECIMAL(10,2), @max_price DECIMAL(10,2))
RETURNS TABLE AS
RETURN (SELECT id, product_name, price FROM dbo.products
        WHERE price BETWEEN @min_price AND @max_price AND active = 1);
GO

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-sqlserver</artifactId>
</dependency>

# application.yml
spring:
  flyway:
    locations: classpath:db/migration/sqlserver
    schemas: dbo
    default-schema: dbo

-- V1__create_products.sql
CREATE SEQUENCE dbo.product_seq START WITH 1 INCREMENT BY 50;
GO

CREATE TABLE dbo.products (
    id            BIGINT           NOT NULL,
    version       BIGINT           NOT NULL DEFAULT 0,
    public_id     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    product_name  NVARCHAR(200)    NOT NULL,
    description   NVARCHAR(MAX)    NULL,
    price         DECIMAL(10,2)    NOT NULL,
    category      NVARCHAR(100)    NULL,
    sku           NVARCHAR(50)     NULL,
    active        BIT              NOT NULL DEFAULT 1,
    thumbnail     VARBINARY(MAX)   NULL,
    created_at    DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at    DATETIME2        NULL,
    CONSTRAINT PK_products PRIMARY KEY (id),
    CONSTRAINT UQ_products_sku UNIQUE (sku),
    CONSTRAINT CK_products_price CHECK (price >= 0)
);
GO

-- V4__indexes.sql
-- Covering index: category lookups can be answered without touching the base table.
CREATE NONCLUSTERED INDEX idx_products_category
    ON dbo.products (category) INCLUDE (product_name, price);

-- Filtered index: much smaller if most rows are inactive.
CREATE NONCLUSTERED INDEX idx_products_active_price
    ON dbo.products (price) WHERE active = 1;
GO

# docker-compose.yml
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "YourStrong@Passw0rd"
      MSSQL_PID: Developer
    ports: ["1433:1433"]
    volumes: [sqlserver_data:/var/opt/mssql]
volumes:
  sqlserver_data:

<!-- pom.xml (test scope) -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mssqlserver</artifactId>
    <scope>test</scope>
</dependency>

// Shared container, reused by every test class
public abstract class SqlServerTestBase {

    @ServiceConnection
    static final MSSQLServerContainer<?> SQL_SERVER =
        new MSSQLServerContainer<>("mcr.microsoft.com/mssql/server:2022-latest")
            .acceptLicense();

    static { SQL_SERVER.start(); }
}

@SpringBootTest
class ProductRepositoryTest extends SqlServerTestBase {

    @Autowired ProductRepository repo;

    @Test
    void upsertUpdatesExistingRow() {
        repo.merge("SKU-001", "Widget", new BigDecimal("9.99"));
        repo.merge("SKU-001", "Widget Pro", new BigDecimal("19.99"));

        assertThat(repo.findBySku("SKU-001"))
            .get().extracting(Product::getName).isEqualTo("Widget Pro");
    }
}

-- One-time database setup
ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Least-privilege application login
CREATE LOGIN app_user WITH PASSWORD = 'use-a-secret-manager';
USE mydb;
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
GRANT EXECUTE ON dbo.ProcessOrder TO app_user;