# Spring Data JDBC - Advanced Patterns

## Custom Row Mapper

```java
@Component
public class OrderRowMapper implements RowMapper<Order> {

    @Override
    public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
        Order order = new Order();
        order.setId(rs.getLong("id"));
        order.setCustomerId(rs.getLong("customer_id"));
        order.setStatus(OrderStatus.valueOf(rs.getString("status")));
        order.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return order;
    }
}

// Usage with JdbcTemplate
@Repository
@RequiredArgsConstructor
public class CustomOrderRepository {

    private final JdbcTemplate jdbcTemplate;
    private final OrderRowMapper rowMapper;

    public List<Order> findComplexQuery(OrderSearchCriteria criteria) {
        StringBuilder sql = new StringBuilder("SELECT * FROM orders WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (criteria.getCustomerId() != null) {
            sql.append(" AND customer_id = ?");
            params.add(criteria.getCustomerId());
        }

        if (criteria.getStatus() != null) {
            sql.append(" AND status = ?");
            params.add(criteria.getStatus().name());
        }

        return jdbcTemplate.query(sql.toString(), rowMapper, params.toArray());
    }
}
```

## ID Generation

```java
// Database generated
@Table("products")
public class Product {
    @Id
    private Long id;  // Auto-generated by DB
}

// Custom ID generation
@Component
public class BeforeConvertCallback implements BeforeConvertCallback<Order> {

    private final IdGenerator idGenerator;

    @Override
    public Order onBeforeConvert(Order order) {
        if (order.getId() == null) {
            order.setId(idGenerator.nextId());
        }
        return order;
    }
}
```

## Auditing

```java
@Configuration
@EnableJdbcAuditing
public class AuditingConfig {

    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext())
            .map(ctx -> ctx.getAuthentication())
            .map(auth -> auth.getName());
    }
}

@Table("orders")
public class Order {

    @Id
    private Long id;

    @CreatedDate
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime modifiedAt;

    @CreatedBy
    private String createdBy;

    @LastModifiedBy
    private String modifiedBy;
}
```

## Event Listeners

```java
@Component
@Slf4j
public class OrderEventListener {

    @EventListener
    public void onBeforeSave(BeforeSaveEvent<Order> event) {
        Order order = event.getEntity();
        log.info("About to save order: {}", order.getId());
    }

    @EventListener
    public void onAfterSave(AfterSaveEvent<Order> event) {
        Order order = event.getEntity();
        log.info("Saved order: {}", order.getId());
        // Publish domain event, send notification, etc.
    }

    @EventListener
    public void onAfterDelete(AfterDeleteEvent<Order> event) {
        log.info("Deleted order with ID: {}", event.getId());
    }
}
```

## One-to-One Reference

```java
@Table("users")
public class User {

    @Id
    private Long id;

    private String username;

    // One-to-one as separate table row
    @MappedCollection(idColumn = "user_id")
    private UserProfile profile;
}

public class UserProfile {
    private String bio;
    private String avatarUrl;
    private String website;
}

// Schema
// CREATE TABLE user_profile (
//     user_id BIGINT PRIMARY KEY REFERENCES users(id),
//     bio TEXT,
//     avatar_url VARCHAR(255),
//     website VARCHAR(255)
// );
```

## Testing with Testcontainers

```java
@DataJdbcTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class OrderRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private OrderRepository orderRepository;

    @Test
    void shouldSaveOrderWithItems() {
        Order order = new Order(1L);
        order.addItem(100L, 2, new BigDecimal("29.99"));
        order.addItem(101L, 1, new BigDecimal("49.99"));

        Order saved = orderRepository.save(order);

        assertThat(saved.getId()).isNotNull();
        assertThat(saved.getItems()).hasSize(2);
        assertThat(saved.getTotal()).isEqualByComparingTo("109.97");
    }
}
```

## Transactions

```java
@Service
@Transactional
public class OrderService {

    private final OrderRepository orderRepository;
    private final InventoryService inventoryService;

    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        Order order = new Order(request.getCustomerId());

        for (var item : request.getItems()) {
            inventoryService.reserveStock(item.getProductId(), item.getQuantity());
            order.addItem(item.getProductId(), item.getQuantity(), item.getPrice());
        }

        return orderRepository.save(order);
    }

    @Transactional(readOnly = true)
    public Order findById(Long id) {
        return orderRepository.findById(id)
            .orElseThrow(() -> new OrderNotFoundException(id));
    }
}
```
