DAO Support

/**
 * Miscellaneous utility methods for DAO implementations.
 * Useful with any data access technology.
 */
public abstract class DataAccessUtils {

    /**
     * Return a single result object from the given Collection.
     * Returns null if 0 results found; throws exception if more than 1 element found.
     */
    public static <T> @Nullable T singleResult(@Nullable Collection<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result object from the given Stream.
     */
    public static <T> @Nullable T singleResult(@Nullable Stream<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result object from the given Iterator.
     */
    public static <T> @Nullable T singleResult(@Nullable Iterator<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result as Optional from the given Collection.
     * Returns Optional.empty() if 0 results found.
     */
    public static <T> Optional<T> optionalResult(@Nullable Collection<? extends @Nullable T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result as Optional from the given Stream.
     */
    public static <T> Optional<T> optionalResult(@Nullable Stream<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result as Optional from the given Iterator.
     */
    public static <T> Optional<T> optionalResult(@Nullable Iterator<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result object from the given Collection.
     * Throws exception if 0 or more than 1 element found.
     */
    public static <T> @NonNull T requiredSingleResult(@Nullable Collection<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a single result object from the given Collection.
     * Allows null result. Throws exception if more than 1 element found.
     */
    public static <T> @Nullable T nullableSingleResult(@Nullable Collection<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a unique result object from the given Collection.
     * Returns null if 0 results; throws exception if more than 1 found.
     */
    public static <T> @Nullable T uniqueResult(@Nullable Collection<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a required unique result object from the given Collection.
     * Throws exception if 0 or more than 1 element found.
     */
    public static <T> T requiredUniqueResult(@Nullable Collection<T> results)
            throws IncorrectResultSizeDataAccessException;

    /**
     * Return a result object of the required type from the given Collection.
     * Performs type conversion if necessary.
     */
    public static <T> @Nullable T objectResult(@Nullable Collection<?> results, Class<T> requiredType)
            throws IncorrectResultSizeDataAccessException, TypeMismatchDataAccessException;

    /**
     * Return an int result from the given Collection.
     */
    public static int intResult(@Nullable Collection<?> results)
            throws IncorrectResultSizeDataAccessException, TypeMismatchDataAccessException;

    /**
     * Return a long result from the given Collection.
     */
    public static long longResult(@Nullable Collection<?> results)
            throws IncorrectResultSizeDataAccessException, TypeMismatchDataAccessException;

    /**
     * Translate the given RuntimeException using the given PersistenceExceptionTranslator.
     * Returns the original exception if it cannot be translated.
     */
    public static RuntimeException translateIfNecessary(
            RuntimeException ex,
            PersistenceExceptionTranslator translator);
}

import org.springframework.dao.support.DataAccessUtils;

@Repository
public class UserRepositoryImpl {

    // Extract single result or null
    public User findUser(String email) {
        List<User> results = jdbcTemplate.query(
            "SELECT * FROM users WHERE email = ?",
            userRowMapper,
            email
        );
        return DataAccessUtils.singleResult(results);
    }

    // Extract single result as Optional
    public Optional<User> findUserOptional(String email) {
        List<User> results = jdbcTemplate.query(
            "SELECT * FROM users WHERE email = ?",
            userRowMapper,
            email
        );
        return DataAccessUtils.optionalResult(results);
    }

    // Extract required result (throws if not found)
    public User getRequiredUser(Long id) {
        List<User> results = jdbcTemplate.query(
            "SELECT * FROM users WHERE id = ?",
            userRowMapper,
            id
        );
        return DataAccessUtils.requiredSingleResult(results);
    }

    // Extract and convert to specific type
    public Long getUserCount() {
        List<?> results = jdbcTemplate.queryForList(
            "SELECT COUNT(*) FROM users"
        );
        return DataAccessUtils.longResult(results);
    }

    // Extract unique result (ensures no duplicates)
    public User findUniqueUser(String username) {
        List<User> results = jdbcTemplate.query(
            "SELECT * FROM users WHERE username = ?",
            userRowMapper,
            username
        );
        return DataAccessUtils.uniqueResult(results);
    }
}

/**
 * Strategy interface for translating between persistence exceptions
 * and Spring's generic DataAccessException hierarchy.
 */
@FunctionalInterface
public interface PersistenceExceptionTranslator {

    /**
     * Translate the given runtime exception thrown by a persistence framework
     * to a corresponding exception from Spring's generic DataAccessException hierarchy.
     * Returns null if the given exception is not persistence-related or could not be translated.
     */
    @Nullable
    DataAccessException translateExceptionIfPossible(RuntimeException ex);
}

@Component
public class CustomPersistenceExceptionTranslator implements PersistenceExceptionTranslator {

    @Override
    public DataAccessException translateExceptionIfPossible(RuntimeException ex) {
        if (ex instanceof CustomDatabaseException) {
            CustomDatabaseException cde = (CustomDatabaseException) ex;
            if (cde.getErrorCode() == 1062) {
                return new DuplicateKeyException("Duplicate key violation", ex);
            }
            if (cde.getErrorCode() == 1213) {
                return new DeadlockLoserDataAccessException("Deadlock detected", ex);
            }
        }
        return null; // Cannot translate
    }
}

/**
 * Implementation of PersistenceExceptionTranslator that supports chaining,
 * allowing delegation to multiple underlying translators.
 */
public class ChainedPersistenceExceptionTranslator implements PersistenceExceptionTranslator {

    /**
     * Create a new ChainedPersistenceExceptionTranslator.
     */
    public ChainedPersistenceExceptionTranslator();

    /**
     * Add a PersistenceExceptionTranslator to the chaining list.
     */
    public void addDelegate(PersistenceExceptionTranslator translator);

    /**
     * Return all registered PersistenceExceptionTranslator delegates.
     */
    public PersistenceExceptionTranslator[] getDelegates();

    @Override
    public DataAccessException translateExceptionIfPossible(RuntimeException ex);
}

/**
 * AOP Alliance MethodInterceptor that provides persistence exception translation
 * based on a given PersistenceExceptionTranslator.
 */
public class PersistenceExceptionTranslationInterceptor
        implements MethodInterceptor, BeanFactoryAware, InitializingBean {

    /**
     * Create a new PersistenceExceptionTranslationInterceptor.
     */
    public PersistenceExceptionTranslationInterceptor();

    /**
     * Create a new PersistenceExceptionTranslationInterceptor with the given translator.
     */
    public PersistenceExceptionTranslationInterceptor(PersistenceExceptionTranslator translator);

    /**
     * Create a new PersistenceExceptionTranslationInterceptor, autodetecting
     * PersistenceExceptionTranslators in the given BeanFactory.
     */
    public PersistenceExceptionTranslationInterceptor(ListableBeanFactory beanFactory);

    /**
     * Set the PersistenceExceptionTranslator to use.
     */
    public void setPersistenceExceptionTranslator(PersistenceExceptionTranslator translator);

    /**
     * Set whether to always translate the exception ("true"), or only if the
     * pointcut applies ("false", default).
     */
    public void setAlwaysTranslate(boolean alwaysTranslate);

    @Override
    public void setBeanFactory(BeanFactory beanFactory);

    @Override
    public void afterPropertiesSet();

    @Override
    public Object invoke(MethodInvocation invocation) throws Throwable;

    /**
     * Detect all PersistenceExceptionTranslators in the given BeanFactory.
     */
    protected PersistenceExceptionTranslator detectPersistenceExceptionTranslators(
            ListableBeanFactory beanFactory);
}

@Configuration
public class PersistenceConfig {

    @Bean
    public PersistenceExceptionTranslationInterceptor persistenceExceptionTranslator() {
        return new PersistenceExceptionTranslationInterceptor();
    }

    // Using with AOP auto-proxy
    @Bean
    public BeanPostProcessor persistenceExceptionTranslationAdvisor() {
        PersistenceExceptionTranslationAdvisor advisor =
            new PersistenceExceptionTranslationAdvisor();
        advisor.setPersistenceExceptionTranslator(persistenceExceptionTranslator());
        return advisor;
    }
}

/**
 * Spring AOP Advisor that applies persistence exception translation
 * to beans annotated with @Repository or a custom annotation.
 */
public class PersistenceExceptionTranslationAdvisor extends AbstractPointcutAdvisor {

    /**
     * Create a new PersistenceExceptionTranslationAdvisor.
     */
    public PersistenceExceptionTranslationAdvisor(
            PersistenceExceptionTranslator translator,
            Class<? extends Annotation> repositoryAnnotationType);

    @Override
    public Pointcut getPointcut();

    @Override
    public Advice getAdvice();
}

/**
 * Bean post-processor that automatically applies persistence exception translation
 * to any bean annotated with Spring's @Repository annotation.
 */
public class PersistenceExceptionTranslationPostProcessor
        extends AbstractBeanFactoryAwareAdvisingPostProcessor {

    /**
     * Set the repository annotation type to check for.
     * Default is Spring's @Repository annotation.
     */
    public void setRepositoryAnnotationType(Class<? extends Annotation> repositoryAnnotationType);

    @Override
    public void setBeanFactory(BeanFactory beanFactory);
}

@Configuration
public class RepositoryConfig {

    @Bean
    public static PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

@Repository
public class UserRepository {
    // Exceptions thrown here will be automatically translated
    // to Spring's DataAccessException hierarchy
}

@Repository
public class ProductRepository {

    public Product findProduct(Long id) {
        List<Product> results = jdbcTemplate.query(
            "SELECT * FROM products WHERE id = ?",
            productRowMapper,
            id
        );
        // Returns null if not found, throws if multiple found
        return DataAccessUtils.singleResult(results);
    }

    public Optional<Product> findProductOptional(String sku) {
        List<Product> results = jdbcTemplate.query(
            "SELECT * FROM products WHERE sku = ?",
            productRowMapper,
            sku
        );
        // Returns Optional.empty() if not found
        return DataAccessUtils.optionalResult(results);
    }

    public int getProductCount() {
        List<Integer> results = jdbcTemplate.query(
            "SELECT COUNT(*) FROM products",
            (rs, rowNum) -> rs.getInt(1)
        );
        // Extract integer result
        return DataAccessUtils.intResult(results);
    }
}

@Component
public class MyPersistenceExceptionTranslator implements PersistenceExceptionTranslator {

    @Override
    public DataAccessException translateExceptionIfPossible(RuntimeException ex) {
        if (ex instanceof MyVendorException) {
            MyVendorException vendorEx = (MyVendorException) ex;

            switch (vendorEx.getErrorCode()) {
                case ERROR_DUPLICATE_KEY:
                    return new DuplicateKeyException("Duplicate key", ex);
                case ERROR_DEADLOCK:
                    return new DeadlockLoserDataAccessException("Deadlock", ex);
                case ERROR_TIMEOUT:
                    return new QueryTimeoutException("Query timeout", ex);
                case ERROR_CONSTRAINT_VIOLATION:
                    return new DataIntegrityViolationException("Constraint violation", ex);
                default:
                    return new UncategorizedDataAccessException("Unknown error", ex);
            }
        }
        return null;
    }
}

@Configuration
public class ExceptionTranslationConfig {

    @Bean
    public ChainedPersistenceExceptionTranslator exceptionTranslator(
            List<PersistenceExceptionTranslator> translators) {

        ChainedPersistenceExceptionTranslator chained =
            new ChainedPersistenceExceptionTranslator();

        for (PersistenceExceptionTranslator translator : translators) {
            chained.addDelegate(translator);
        }

        return chained;
    }
}