Spring Boot

SQL Server Integration

Microsoft SQL Server is a widely used enterprise relational database, especially in .NET and Java shops running on Windows or Azure. Spring Boot integrates with SQL Server through the Microsoft JDBC driver (mssql-jdbc), Hibernate's SQL Server dialect, and Spring Data JPA. This entry covers setup, SQL Server-specific entity mappings, T-SQL native queries, stored procedures, and testing with Testcontainers.

Dependencies and Setup

Microsoft publishes the SQL Server JDBC driver to Maven Central. Spring Boot auto-configures HikariCP, Hibernate, and the SQL Server dialect from the datasource URL.

XML

<!-- 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>
    <!-- Version managed by Spring Boot BOM -->
</dependency>

# ── application.yml — minimum configuration: ──────────────────────────
spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=mydb;encrypt=false
    username: sa
    password: ${DB_PASSWORD}
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      idle-timeout: 300000
      max-lifetime: 1200000
      keepalive-time: 60000
      pool-name: myapp-mssql-pool
      connection-test-query: SELECT 1

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

  flyway:
    enabled: true
    locations: classpath:db/migration/sqlserver

# ── Production connection URL options: ────────────────────────────────
# Encrypt in transit (required for Azure SQL):
# jdbc:sqlserver://myserver.database.windows.net:1433;
#   databaseName=mydb;encrypt=true;trustServerCertificate=false;
#   hostNameInCertificate=*.database.windows.net;loginTimeout=30

# Windows auth (no username/password):
# jdbc:sqlserver://localhost:1433;databaseName=mydb;integratedSecurity=true

# Azure SQL with Managed Identity:
# jdbc:sqlserver://myserver.database.windows.net:1433;
#   databaseName=mydb;authentication=ActiveDirectoryManagedIdentity

SQL Server-Specific Entity Mappings

SQL Server has specific data types and behaviours that affect JPA entity mappings — IDENTITY columns, uniqueidentifier for UUIDs, nvarchar for Unicode strings, datetime2 for timestamps, and bit for booleans.

Java

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

    // SQL Server IDENTITY — maps to GenerationType.IDENTITY:
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // Or use uniqueidentifier (UUID):
    // @Id
    // @GeneratedValue(strategy = GenerationType.UUID)
    // @Column(name = "id", columnDefinition = "uniqueidentifier",
    //         updatable = false)
    // private UUID id;

    // SQL Server NVARCHAR — Unicode strings (preferred over VARCHAR):
    @Column(name = "product_name", nullable = false,
            columnDefinition = "NVARCHAR(200)")
    private String name;

    // SQL Server NVARCHAR(MAX) for long text (replaces CLOB):
    @Column(name = "description",
            columnDefinition = "NVARCHAR(MAX)")
    private String description;

    // SQL Server DECIMAL for money:
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    // SQL Server BIT for boolean (maps automatically):
    @Column(nullable = false)
    private boolean active = true;

    // SQL Server DATETIME2 for high-precision timestamps:
    @Column(name = "created_at", nullable = false, updatable = false,
            columnDefinition = "DATETIME2")
    private LocalDateTime createdAt;

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

    // SQL Server IMAGE/VARBINARY(MAX) for binary data:
    @Lob
    @Column(name = "thumbnail",
            columnDefinition = "VARBINARY(MAX)")
    private byte[] thumbnail;

    // SQL Server MONEY type — use DECIMAL in JPA, cast in SQL:
    @Column(name = "list_price", precision = 19, scale = 4)
    private BigDecimal listPrice;

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

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

SQL Server-Specific Native Queries

T-SQL features — MERGE, OFFSET/FETCH pagination, window functions, FOR JSON, and table hints — require native queries. SQL Server's MERGE statement is particularly powerful for upsert operations.

Java

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

    // ── SQL Server MERGE (UPSERT): ────────────────────────────────────
    @Modifying
    @Transactional
    @Query(value = """
            MERGE products AS target
            USING (VALUES (:sku, :name, :price)) AS source (sku, name, price)
            ON target.sku = source.sku
            WHEN MATCHED THEN
                UPDATE SET target.name = source.name,
                           target.price = source.price,
                           target.updated_at = GETDATE()
            WHEN NOT MATCHED THEN
                INSERT (sku, name, price, created_at)
                VALUES (source.sku, source.name, source.price, GETDATE());
            """,
           nativeQuery = true)
    void mergeProduct(@Param("sku") String sku,
                      @Param("name") String name,
                      @Param("price") BigDecimal price);

    // ── SQL Server window functions: ──────────────────────────────────
    @Query(value = """
            SELECT *,
                   ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC)
                       AS row_num,
                   AVG(CAST(price AS FLOAT)) OVER (PARTITION BY category)
                       AS category_avg
            FROM products
            WHERE active = 1
            """,
           nativeQuery = true)
    List<Object[]> findWithWindowFunctions();

    // ── SQL Server FOR JSON (return JSON from SQL): ───────────────────
    @Query(value = """
            SELECT id, product_name AS name, price, category
            FROM products
            WHERE active = 1
            FOR JSON PATH, ROOT('products')
            """,
           nativeQuery = true)
    String findAllAsJson();

    // ── SQL Server table hints — lock control: ────────────────────────
    @Query(value = """
            SELECT * FROM products WITH (NOLOCK)
            WHERE category = :category
            """,
           nativeQuery = true)
    List<Product> findByCategoryNoLock(@Param("category") String category);

    // ── SQL Server TOP — limit results: ───────────────────────────────
    @Query(value = "SELECT TOP 10 * FROM products ORDER BY created_at DESC",
           nativeQuery = true)
    List<Product> findTop10Recent();

    // ── SQL Server TRY_CAST — safe type conversion: ───────────────────
    @Query(value = """
            SELECT * FROM products
            WHERE TRY_CAST(attributes AS NVARCHAR(MAX)) LIKE '%:keyword%'
            """,
           nativeQuery = true)
    List<Product> searchAttributes(@Param("keyword") String keyword);
}

Stored Procedures and Functions

SQL Server stored procedures and user-defined functions are common in enterprise environments. Spring Data JPA provides @Procedure for simple procedures and the EntityManager StoredProcedureQuery API for full control.

Java

// ── @Procedure — simple stored procedure call: ───────────────────────
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Calls: EXEC CalculateOrderTotal @orderId = ?, @discount = ?
    @Procedure(procedureName = "CalculateOrderTotal")
    BigDecimal calculateTotal(Long orderId, BigDecimal discount);

    // Procedure with no return value:
    @Procedure(procedureName = "ArchiveOldOrders")
    void archiveOrders(LocalDateTime cutoff);
}

// ── EntityManager — full procedure control with multiple parameters: ───
@Service
@RequiredArgsConstructor
@Transactional
public class SqlServerService {

    private final EntityManager em;

    // Call procedure with IN and OUT parameters:
    public OrderResult processOrder(Long orderId) {
        StoredProcedureQuery query = em
            .createStoredProcedureQuery("ProcessOrder")
            .registerStoredProcedureParameter(
                "p_order_id", Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(
                "p_status", String.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(
                "p_total", BigDecimal.class, ParameterMode.OUT)
            .setParameter("p_order_id", orderId);

        query.execute();
        String status = (String) query.getOutputParameterValue("p_status");
        BigDecimal total = (BigDecimal) query.getOutputParameterValue("p_total");
        return new OrderResult(orderId, status, total);
    }

    // Call a table-valued function:
    public List<Object[]> getProductsByPriceRange(
            BigDecimal min, BigDecimal max) {
        return em.createNativeQuery("""
            SELECT * FROM dbo.GetProductsByPriceRange(:min, :max)
            ORDER BY price ASC
            """)
            .setParameter("min", min)
            .setParameter("max", max)
            .getResultList();
    }
}

// ── Corresponding SQL Server DDL: ─────────────────────────────────────
-- V3__create_procedures.sql:

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

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

Flyway Migrations for SQL Server

Flyway supports SQL Server with T-SQL syntax. Separate migration directories per database allow the same Java codebase to target multiple database vendors.

yaml

# ── application.yml — SQL Server Flyway location: ─────────────────────
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration/sqlserver
    schemas: dbo
    default-schema: dbo
    table: flyway_schema_history    # Flyway tracking table

# ── Migration file layout: ────────────────────────────────────────────
# src/main/resources/db/migration/sqlserver/
# ├── V1__create_products_table.sql
# ├── V2__create_orders_table.sql
# ├── V3__create_procedures.sql
# └── V4__create_indexes.sql

-- V1__create_products_table.sql:
CREATE TABLE dbo.products (
    id              BIGINT          NOT NULL IDENTITY(1,1),
    product_name    NVARCHAR(200)   NOT NULL,
    description     NVARCHAR(MAX),
    price           DECIMAL(10,2)   NOT NULL,
    category        NVARCHAR(100),
    sku             NVARCHAR(50),
    active          BIT             NOT NULL DEFAULT 1,
    created_at      DATETIME2       NOT NULL DEFAULT GETDATE(),
    updated_at      DATETIME2,
    CONSTRAINT PK_products PRIMARY KEY (id),
    CONSTRAINT UQ_products_sku UNIQUE (sku),
    CONSTRAINT CK_products_price CHECK (price >= 0)
);
GO

-- V4__create_indexes.sql:
CREATE NONCLUSTERED INDEX idx_products_category
    ON dbo.products (category)
    INCLUDE (product_name, price, active);

CREATE NONCLUSTERED INDEX idx_products_active_price
    ON dbo.products (active, price)
    WHERE active = 1;   -- filtered index
GO

Docker and Testcontainers

Microsoft publishes official SQL Server Docker images. Testcontainers provides MSSQLServerContainer for integration tests with a real SQL Server instance.

yaml

# ── docker-compose.yml — SQL Server for local development: ───────────
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: myapp-sqlserver
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: YourStrong@Password1
      MSSQL_PID: Developer           # free Developer edition
    ports:
      - "1433:1433"
    volumes:
      - sqlserver_data:/var/opt/mssql

volumes:
  sqlserver_data:

# ── application-dev.yml: ──────────────────────────────────────────────
spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=mydb;encrypt=false
    username: sa
    password: YourStrong@Password1
  jpa:
    hibernate:
      ddl-auto: create-drop

# ── Testcontainers: ───────────────────────────────────────────────────
<!-- pom.xml: -->
<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>

@SpringBootTest
@Testcontainers
class ProductRepositoryTest {

    @Container
    static MSSQLServerContainer<?> sqlserver =
        new MSSQLServerContainer<>("mcr.microsoft.com/mssql/server:2022-latest")
            .acceptLicense()
            .withPassword("YourStrong@Password1");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", sqlserver::getJdbcUrl);
        registry.add("spring.datasource.username", sqlserver::getUsername);
        registry.add("spring.datasource.password", sqlserver::getPassword);
        registry.add("spring.datasource.driver-class-name",
            () -> "com.microsoft.sqlserver.jdbc.SQLServerDriver");
    }

    @Autowired
    private ProductRepository productRepository;

    @Test
    void mergeCreatesAndUpdatesProduct() {
        productRepository.mergeProduct("SKU-001", "Widget", new BigDecimal("9.99"));
        productRepository.mergeProduct("SKU-001", "Widget Pro", new BigDecimal("19.99"));

        Optional<Product> found = productRepository.findBySku("SKU-001");
        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Widget Pro");
    }
}

Oracle DB Integration

H2 Database