Exception Handling

/**
 * Base exception for all database access errors in jOOQ
 * Runtime exception that wraps SQL exceptions and provides additional context
 */
public class DataAccessException extends RuntimeException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public DataAccessException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public DataAccessException(String message, Throwable cause);
    
    /**
     * Get the underlying SQL state if available
     * @return SQL state from SQLException or null
     */
    public String sqlState();
    
    /**
     * Get the underlying SQL error code if available
     * @return SQL error code from SQLException or 0
     */
    public int sqlErrorCode();
    
    /**
     * Get the SQL that caused this exception if available
     * @return SQL string or null
     */
    public String sql();
}

/**
 * Exception for database definition (DDL) related errors
 * Thrown when DDL operations fail or are invalid
 */
public class DataDefinitionException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public DataDefinitionException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public DataDefinitionException(String message, Throwable cause);
}

/**
 * Base exception for configuration-related errors
 * Thrown when jOOQ configuration is invalid or incomplete
 */
public class ConfigurationException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public ConfigurationException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public ConfigurationException(String message, Throwable cause);
}

/**
 * Base exception for invalid query results
 * Thrown when query results don't match expectations
 */
public class InvalidResultException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public InvalidResultException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public InvalidResultException(String message, Throwable cause);
}

/**
 * Exception thrown when no data is found but was expected
 * Commonly thrown by fetchOne() when no records match
 */
public class NoDataFoundException extends InvalidResultException {
    /**
     * Create exception with default message
     */
    public NoDataFoundException();
    
    /**
     * Create exception with custom message
     * @param message Error message
     */
    public NoDataFoundException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public NoDataFoundException(String message, Throwable cause);
}

/**
 * Exception thrown when more rows are returned than expected
 * Commonly thrown by fetchOne() when multiple records match
 */
public class TooManyRowsException extends InvalidResultException {
    /**
     * Create exception with default message
     */
    public TooManyRowsException();
    
    /**
     * Create exception with custom message
     * @param message Error message
     */
    public TooManyRowsException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public TooManyRowsException(String message, Throwable cause);
    
    /**
     * Get the number of rows that were found
     * @return Actual row count
     */
    public int getRowCount();
}

/**
 * Exception for data integrity constraint violations
 * Thrown when database constraints are violated during DML operations
 */
public class DataChangedException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public DataChangedException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public DataChangedException(String message, Throwable cause);
}

/**
 * Exception for record mapping errors
 * Thrown when records cannot be mapped to POJOs or other types
 */
public class MappingException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public MappingException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public MappingException(String message, Throwable cause);
}

/**
 * Exception for data type conversion errors
 * Thrown when values cannot be converted between Java and SQL types
 */
public class DataTypeException extends DataAccessException {
    /**
     * Create exception with message
     * @param message Error message
     */
    public DataTypeException(String message);
    
    /**
     * Create exception with message and cause
     * @param message Error message
     * @param cause Underlying cause
     */
    public DataTypeException(String message, Throwable cause);
}

// Handling specific jOOQ exceptions
try {
    // Expect exactly one record
    AuthorRecord author = create.selectFrom(AUTHOR)
        .where(AUTHOR.EMAIL.eq("user@example.com"))
        .fetchOne();
        
} catch (NoDataFoundException e) {
    // No author found with that email
    System.out.println("Author not found: " + e.getMessage());
    
} catch (TooManyRowsException e) {
    // Multiple authors found (data integrity issue)
    System.out.println("Multiple authors found: " + e.getRowCount());
    
} catch (DataAccessException e) {
    // General database error
    System.out.println("Database error: " + e.getMessage());
    System.out.println("SQL State: " + e.sqlState());
    System.out.println("SQL Error Code: " + e.sqlErrorCode());
}

// Handling mapping exceptions
try {
    List<AuthorPojo> authors = create.selectFrom(AUTHOR)
        .fetch()
        .into(AuthorPojo.class);
        
} catch (MappingException e) {
    System.out.println("Failed to map records to POJO: " + e.getMessage());
}

// Handling constraint violations
try {
    create.insertInto(AUTHOR)
        .set(AUTHOR.EMAIL, "duplicate@example.com") // Violates unique constraint
        .execute();
        
} catch (DataChangedException e) {
    System.out.println("Constraint violation: " + e.getMessage());
}

public interface ExecuteContext {
    /**
     * Get the SQL that was being executed when exception occurred
     * @return SQL string
     */
    String sql();
    
    /**
     * Get the bind values that were used
     * @return Array of bind values
     */
    Object[] bindings();
    
    /**
     * Get the SQLException that caused the error
     * @return Original SQLException or null
     */
    SQLException sqlException();
    
    /**
     * Get the execution time before the exception
     * @return Execution time in nanoseconds
     */
    long executionTime();
    
    /**
     * Get the connection that was being used
     * @return JDBC Connection
     */
    Connection connection();
    
    /**
     * Get the configuration context
     * @return Configuration instance
     */
    Configuration configuration();
}

// Graceful degradation for optional data
public Optional<AuthorRecord> findAuthorByEmail(String email) {
    try {
        return Optional.of(
            create.selectFrom(AUTHOR)
                .where(AUTHOR.EMAIL.eq(email))
                .fetchOne()
        );
    } catch (NoDataFoundException e) {
        return Optional.empty();
    }
}

// Retry logic for transient errors
public void updateAuthorWithRetry(int authorId, String newName) {
    int maxRetries = 3;
    int retryCount = 0;
    
    while (retryCount < maxRetries) {
        try {
            create.update(AUTHOR)
                .set(AUTHOR.FIRST_NAME, newName)
                .where(AUTHOR.ID.eq(authorId))
                .execute();
            return; // Success
            
        } catch (DataAccessException e) {
            retryCount++;
            if (retryCount >= maxRetries) {
                throw e; // Give up after max retries
            }
            
            // Wait before retry
            try {
                Thread.sleep(1000 * retryCount);
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
                throw new RuntimeException("Interrupted during retry", ie);
            }
        }
    }
}

// Safe batch operations with partial failure handling
public List<String> batchInsertAuthors(List<AuthorPojo> authors) {
    List<String> errors = new ArrayList<>();
    
    for (AuthorPojo author : authors) {
        try {
            create.insertInto(AUTHOR)
                .set(AUTHOR.FIRST_NAME, author.getFirstName())
                .set(AUTHOR.LAST_NAME, author.getLastName())
                .set(AUTHOR.EMAIL, author.getEmail())
                .execute();
                
        } catch (DataChangedException e) {
            // Log constraint violation but continue with other records
            errors.add("Failed to insert " + author.getEmail() + ": " + e.getMessage());
            
        } catch (DataAccessException e) {
            // Log general error but continue
            errors.add("Database error for " + author.getEmail() + ": " + e.getMessage());
        }
    }
    
    return errors;
}

// Connection recovery for connection pool issues
public <T> T executeWithConnectionRecovery(Function<DSLContext, T> operation) {
    try {
        return operation.apply(create);
        
    } catch (DataAccessException e) {
        // Check if it's a connection-related error
        if (e.sqlState() != null && e.sqlState().startsWith("08")) {
            // Connection error - try to get a new connection
            try (CloseableDSLContext newCreate = using(dataSource, SQLDialect.POSTGRES)) {
                return operation.apply(newCreate);
            }
        }
        throw e; // Re-throw if not connection-related
    }
}

// Custom exception wrapper
public class AuthorServiceException extends RuntimeException {
    private final String sqlState;
    private final int errorCode;
    
    public AuthorServiceException(String message, DataAccessException cause) {
        super(message, cause);
        this.sqlState = cause.sqlState();
        this.errorCode = cause.sqlErrorCode();
    }
    
    public String getSqlState() { return sqlState; }
    public int getErrorCode() { return errorCode; }
    
    public boolean isConstraintViolation() {
        return "23000".equals(sqlState) || "23505".equals(sqlState);
    }
    
    public boolean isConnectionError() {
        return sqlState != null && sqlState.startsWith("08");
    }
}

// Service layer exception translation
public class AuthorService {
    private final DSLContext create;
    
    public AuthorRecord createAuthor(String firstName, String lastName, String email) {
        try {
            return create.insertInto(AUTHOR)
                .set(AUTHOR.FIRST_NAME, firstName)
                .set(AUTHOR.LAST_NAME, lastName)
                .set(AUTHOR.EMAIL, email)
                .returning()
                .fetchOne();
                
        } catch (DataChangedException e) {
            if (e.sqlState() != null && e.sqlState().equals("23505")) {
                throw new AuthorServiceException("Email already exists: " + email, e);
            }
            throw new AuthorServiceException("Failed to create author", e);
            
        } catch (DataAccessException e) {
            throw new AuthorServiceException("Database error while creating author", e);
        }
    }
}