Spring Boot – JdbcTemplate

/**
 * Users, backed by a repository AND a JdbcTemplate.
 *
 * <ul>
 *   <li><b>UserRepository</b> for saves and single-row lookups. Spring Data derives those
 *       from the method name, returns managed entities that dirty-checking can track, and honours
 *       the {@code @SQLRestriction} that hides soft-deleted rows. Hand-writing them would be more
 *       code that does less.
 *   <li><b>NamedParameterJdbcTemplate</b> for queries that aggregate. JPA has nothing to
 *       offer a query whose result is not an entity.
 * </ul>
 */
@Slf4j
@Repository
public class UserDAOImp implements UserDAO {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    private AdminUserRowMapper adminUserRowMapper;
}

Map.of("from", from, "limit", limit)

@Override
public ReportDTOs.Summary findSummary(LocalDateTime from) {
    String query =
            """
            SELECT COUNT(*)                  AS total_orders,
                   COALESCE(SUM(o.total), 0) AS total_revenue,
                   COALESCE(AVG(o.total), 0) AS average_order_value
            FROM customer_order o
            WHERE o.status IN ('PAID','PREPARING','COMPLETED')
              AND o.deleted = 0
              AND o.created_at >= :from
            """;

    // An aggregate without GROUP BY always returns exactly one row — COUNT/SUM over an empty set
    // is a row of zeroes, not zero rows — so this cannot throw EmptyResultDataAccessException.
    return jdbcTemplate.queryForObject(query, Map.of("from", from), reportSummaryRowMapper);
}

/**
 * Maps a best-seller row onto {@link ReportDTOs.TopProduct}.
 *
 * <p>{@code product_name} is the name SNAPSHOTTED onto the order line, not a join back to the
 * product table — a product deleted from the menu still has to appear in historical sales.
 */
@Component
public class TopProductRowMapper implements RowMapper<ReportDTOs.TopProduct> {

    @Override
    public ReportDTOs.TopProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new ReportDTOs.TopProduct(
                rs.getString("product_name"),
                rs.getLong("units_sold"),
                RowValues.money(rs.getBigDecimal("revenue")));
    }
}

// A list
List<AdminUserDTO> users = jdbcTemplate.query(query, Map.of(), adminUserRowMapper);

// Exactly one row — throws if there are none or many
Summary summary = jdbcTemplate.queryForObject(query, params, summaryRowMapper);

// A single scalar
Long count = jdbcTemplate.queryForObject(
        "SELECT COUNT(*) FROM customer_order WHERE deleted = 0", Map.of(), Long.class);

// Insert / update / delete — returns rows affected
int updated = jdbcTemplate.update(
        "UPDATE customer_order SET status = :status WHERE public_id = :id",
        Map.of("status", "PAID", "id", orderId.toString()));

List<User> rows = jdbcTemplate.query(query, params, userRowMapper);
return rows.stream().findFirst();

/**
 * <b>Every query filters {@code deleted = 0}, and that is not optional.</b> The entities carry
 * {@code @SQLRestriction("deleted = false")}, so soft-deleted rows vanish from ordinary JPA reads
 * and it is tempting to assume they are gone everywhere. They are not: that annotation is applied by
 * Hibernate when it builds a query from the entity model, and SQL written here never goes near the
 * entity model. Omitting the predicate silently counts cancelled-and-deleted orders as revenue — the
 * reports stay entirely plausible, just wrong, which is why it went unnoticed for a while. Guarded
 * by {@code ReportServiceImplTest#softDeletedOrdersAreExcluded}.
 */

/**
 * <p>The queries aggregate in the DATABASE and return a handful of rows. The alternative — loading
 * every order into memory and summing in Java — works fine on 18 demo rows and falls over on a real
 * order table. Aggregation belongs where the data is.
 */

/**
 * These aggregates used to be declared as {@code @Query(nativeQuery = true)} on a repository
 * interface, which meant real SQL travelling through a persistence layer that added nothing to it,
 * returning interface projections that Spring had to build proxies for. Here the SQL is plain, the
 * mapping is an explicit {@code RowMapper}, and what you read is what the database runs.
 */