Exception Handling

/**
 * ExecuteListener interface for translating exceptions in the ExecuteContext
 * Most commonly used to translate SQLExceptions to Spring DataAccessExceptions
 */
public interface ExceptionTranslatorExecuteListener extends ExecuteListener {
    
    /**
     * Default implementation suitable for most applications
     * Uses Spring's standard SQL exception translation
     */
    ExceptionTranslatorExecuteListener DEFAULT = new DefaultExceptionTranslatorExecuteListener();
    
    /**
     * Creates a new ExceptionTranslatorExecuteListener with custom translator factory
     * @param translatorFactory factory function to create SQLExceptionTranslator from ExecuteContext
     * @return new ExceptionTranslatorExecuteListener instance
     */
    static ExceptionTranslatorExecuteListener of(
        Function<ExecuteContext, SQLExceptionTranslator> translatorFactory);
}

/**
 * Default implementation of ExceptionTranslatorExecuteListener
 * Delegates to SQLExceptionTranslator for consistent Spring exception handling
 */
final class DefaultExceptionTranslatorExecuteListener implements ExceptionTranslatorExecuteListener {
    
    /**
     * Create with default translator factory
     * Uses SQLErrorCodeSQLExceptionTranslator or SQLExceptionSubclassTranslator
     */
    DefaultExceptionTranslatorExecuteListener();
    
    /**
     * Create with custom translator factory
     * @param translatorFactory function to create SQLExceptionTranslator from ExecuteContext
     */
    DefaultExceptionTranslatorExecuteListener(
        Function<ExecuteContext, SQLExceptionTranslator> translatorFactory);
    
    /**
     * Handle exceptions during jOOQ execution
     * Translates SQLExceptions to Spring DataAccessExceptions
     * @param context the execution context containing exception information
     */
    @Override
    public void exception(ExecuteContext context);
}

/**
 * Failure analyzer for when DSLContext bean is missing
 * Provides helpful suggestions for resolving configuration issues
 */
class NoDslContextBeanFailureAnalyzer extends AbstractFailureAnalyzer<NoSuchBeanDefinitionException> 
    implements Ordered;

/**
 * Exception thrown when JAXB is not available for XML config parsing
 */
class JaxbNotAvailableException extends RuntimeException;

/**
 * Failure analyzer for JAXB availability issues
 * Provides guidance on adding JAXB dependencies when using XML configuration
 */
class JaxbNotAvailableExceptionFailureAnalyzer extends AbstractFailureAnalyzer<JaxbNotAvailableException>;

@Service
public class ProductService {
    
    @Autowired
    private DSLContext dsl;
    
    public Product findById(Long id) {
        try {
            return dsl.selectFrom(PRODUCT)
                      .where(PRODUCT.ID.eq(id))
                      .fetchOneInto(Product.class);
        } catch (DataAccessException e) {
            // All JDBC/SQL exceptions are translated to Spring exceptions
            // Can handle them consistently with other Spring Data access
            handleDataAccessException(e);
            throw e;
        }
    }
    
    public void createProduct(Product product) {
        try {
            dsl.insertInto(PRODUCT)
               .set(PRODUCT.NAME, product.getName())
               .set(PRODUCT.PRICE, product.getPrice())
               .execute();
        } catch (DuplicateKeyException e) {
            // Specific Spring exception for constraint violations
            throw new ProductAlreadyExistsException("Product with name already exists", e);
        } catch (DataIntegrityViolationException e) {
            // General integrity violations
            throw new InvalidProductDataException("Product data violates constraints", e);
        }
    }
    
    private void handleDataAccessException(DataAccessException e) {
        if (e instanceof EmptyResultDataAccessException) {
            // No results found
        } else if (e instanceof IncorrectResultSizeDataAccessException) {
            // Multiple results when one expected
        } else if (e instanceof DataIntegrityViolationException) {
            // Constraint violations
        } else if (e instanceof DataAccessResourceFailureException) {
            // Connection/resource issues
        }
    }
}

@Component
public class CustomExceptionTranslatorConfiguration {
    
    @Bean
    public ExceptionTranslatorExecuteListener customExceptionTranslator() {
        return ExceptionTranslatorExecuteListener.of(context -> {
            // Custom translator based on context
            SQLDialect dialect = context.configuration().dialect();
            
            if (dialect == SQLDialect.MYSQL) {
                return new MySQLSpecificExceptionTranslator();
            } else if (dialect == SQLDialect.POSTGRES) {
                return new PostgreSQLSpecificExceptionTranslator();
            } else {
                return new SQLExceptionSubclassTranslator();
            }
        });
    }
}

public class MySQLSpecificExceptionTranslator implements SQLExceptionTranslator {
    
    @Override
    public DataAccessException translate(String task, String sql, SQLException ex) {
        // MySQL-specific error code handling
        switch (ex.getErrorCode()) {
            case 1062: // Duplicate entry
                return new DuplicateKeyException("MySQL duplicate key: " + ex.getMessage(), ex);
            case 1048: // Column cannot be null  
                return new DataIntegrityViolationException("MySQL null constraint: " + ex.getMessage(), ex);
            case 1451: // Foreign key constraint
                return new DataIntegrityViolationException("MySQL foreign key: " + ex.getMessage(), ex);
            default:
                return new UncategorizedSQLException(task, sql, ex);
        }
    }
}

@ControllerAdvice
public class DatabaseExceptionHandler {
    
    @ExceptionHandler(DuplicateKeyException.class)
    @ResponseStatus(HttpStatus.CONFLICT)
    public ResponseEntity<ErrorResponse> handleDuplicateKey(DuplicateKeyException e) {
        return ResponseEntity.status(HttpStatus.CONFLICT)
                           .body(new ErrorResponse("DUPLICATE_ENTRY", e.getMessage()));
    }
    
    @ExceptionHandler(DataIntegrityViolationException.class)
    @ResponseStatus(HttpStatus.BAD_REQUEST)
    public ResponseEntity<ErrorResponse> handleDataIntegrity(DataIntegrityViolationException e) {
        return ResponseEntity.badRequest()
                           .body(new ErrorResponse("DATA_INTEGRITY", "Invalid data provided"));
    }
    
    @ExceptionHandler(DataAccessResourceFailureException.class)
    @ResponseStatus(HttpStatus.SERVICE_UNAVAILABLE)
    public ResponseEntity<ErrorResponse> handleResourceFailure(DataAccessResourceFailureException e) {
        return ResponseEntity.status(HttpStatus.SERVICE_UNAVAILABLE)
                           .body(new ErrorResponse("DATABASE_UNAVAILABLE", "Database temporarily unavailable"));
    }
    
    @ExceptionHandler(DataAccessException.class)
    @ResponseStatus(HttpStatus.INTERNAL_SERVER_ERROR)
    public ResponseEntity<ErrorResponse> handleGenericDataAccess(DataAccessException e) {
        // Log detailed error for debugging
        log.error("Database operation failed", e);
        
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                           .body(new ErrorResponse("DATABASE_ERROR", "An error occurred while accessing the database"));
    }
}

@SpringBootTest
class ExceptionTranslationTest {
    
    @Autowired
    private DSLContext dsl;
    
    @Test
    void shouldTranslateDuplicateKeyException() {
        // Setup: Insert initial record
        dsl.insertInto(USER)
           .set(USER.EMAIL, "test@example.com")
           .set(USER.NAME, "Test User")
           .execute();
        
        // Test: Attempt duplicate insert
        assertThatThrownBy(() -> 
            dsl.insertInto(USER)
               .set(USER.EMAIL, "test@example.com")  // Duplicate email
               .set(USER.NAME, "Another User")
               .execute()
        ).isInstanceOf(DuplicateKeyException.class)
         .hasMessageContaining("test@example.com");
    }
    
    @Test
    void shouldTranslateConstraintViolation() {
        assertThatThrownBy(() ->
            dsl.insertInto(USER)
               .set(USER.EMAIL, "invalid-email")  // Violates check constraint
               .set(USER.NAME, "Test User")
               .execute()
        ).isInstanceOf(DataIntegrityViolationException.class);
    }
}