С чем сталкивался при работе с нативными запросами

Работа с нативными SQL запросами

Да, я часто сталкивался с ситуациями, когда приходилось писать нативные SQL запросы вместо ORM. Это было необходимо для оптимизации производительности, сложных операций и специфичных задач баз данных.

Когда нужны нативные запросы

1. Высокая производительность

Если стандартный ORM запрос работает 5 секунд, а оптимизированный SQL работает за 500мс, это огромная разница для пользователя.

// Плохо: ORM берёт всю таблицу, потом фильтрует в памяти
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByUserIdAndStatusAndCreatedAtAfter(
        Long userId,
        String status,
        LocalDateTime date
    );
}

// При запросе это генерирует неоптимальный SQL:
// SELECT * FROM orders WHERE user_id = ? AND status = ? AND created_at > ?
// Но потом hibernate может загрузить related entities...

// Хорошо: Нативный оптимизированный запрос
@Repository
public class OrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<OrderDto> getRecentOrdersByUser(Long userId, String status) {
        String sql = """
            SELECT o.id, o.total, o.status, COUNT(oi.id) as item_count
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.user_id = ?
            AND o.status = ?
            AND o.created_at > NOW() - INTERVAL '30 days'
            GROUP BY o.id
            ORDER BY o.created_at DESC
            LIMIT 100
        """;
        
        return jdbcTemplate.query(sql, new Object[]{userId, status},
            (rs, rowNum) -> new OrderDto(
                rs.getLong("id"),
                rs.getBigDecimal("total"),
                rs.getString("status"),
                rs.getInt("item_count")
            )
        );
    }
}

2. Сложные JOIN операции

Когда нужно соединить 5+ таблиц с условиями и агрегациями, ORM становится громоздким.

// Аналитический отчёт: продажи по категориям за последний месяц

@Service
public class SalesAnalyticsService {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    public List<CategorySalesReport> getMonthlySalesReport(YearMonth month) {
        String sql = """
            SELECT
                c.id,
                c.name as category_name,
                COUNT(DISTINCT o.id) as total_orders,
                COUNT(oi.id) as total_items,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.quantity * oi.price) as total_revenue,
                AVG(oi.quantity * oi.price) as avg_order_value,
                MIN(o.created_at) as first_sale,
                MAX(o.created_at) as last_sale
            FROM categories c
            LEFT JOIN products p ON c.id = p.category_id
            LEFT JOIN order_items oi ON p.id = oi.product_id
            LEFT JOIN orders o ON oi.order_id = o.id
                AND o.status = 'COMPLETED'
                AND YEAR(o.created_at) = :year
                AND MONTH(o.created_at) = :month
            WHERE c.deleted_at IS NULL
            GROUP BY c.id, c.name
            HAVING SUM(oi.quantity) > 0
            ORDER BY total_revenue DESC
        """;
        
        MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("year", month.getYear())
            .addValue("month", month.getMonthValue());
        
        return namedParameterJdbcTemplate.query(sql, params,
            (rs, rowNum) -> CategorySalesReport.builder()
                .categoryId(rs.getLong("id"))
                .categoryName(rs.getString("category_name"))
                .totalOrders(rs.getInt("total_orders"))
                .totalItems(rs.getInt("total_items"))
                .totalQuantity(rs.getInt("total_quantity"))
                .totalRevenue(rs.getBigDecimal("total_revenue"))
                .avgOrderValue(rs.getBigDecimal("avg_order_value"))
                .firstSale(rs.getTimestamp("first_sale").toLocalDateTime())
                .lastSale(rs.getTimestamp("last_sale").toLocalDateTime())
                .build()
        );
    }
}

3. Batch операции

Если нужно обновить миллион строк, ORM будет очень медленным.

@Service
public class InventoryService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void applyPriceDiscount(List<Long> productIds, BigDecimal discount) {
        String sql = """
            UPDATE products
            SET
                price = price * (1 - ?),
                updated_at = NOW()
            WHERE id = ANY(?)
            AND deleted_at IS NULL
        """;
        
        Array sqlArray = jdbcTemplate.getDataSource()
            .getConnection()
            .createArrayOf("BIGINT", productIds.toArray());
        
        int updatedRows = jdbcTemplate.update(sql,
            discount,
            sqlArray
        );
        
        logger.info("Applied discount to {} products", updatedRows);
    }
}

Challenges с нативными запросами

Challenge 1: SQL injection уязвимость

Основная проблема при работе с нативными запросами.

// ОПАСНО: SQL injection
public List<Order> getOrdersUnsafe(String status) {
    String sql = "SELECT * FROM orders WHERE status = '" + status + "'";
    // Если status = "'; DROP TABLE orders; --" , БД будет удалена!
    return jdbcTemplate.query(sql, ...);
}

// БЕЗОПАСНО: Параметризованные запросы
public List<Order> getOrdersSafe(String status) {
    String sql = "SELECT * FROM orders WHERE status = ?";
    return jdbcTemplate.query(sql, new Object[]{status}, ...);
}

// ЛУЧШЕ: Named parameters для читаемости
public List<Order> getOrdersBetter(String status) {
    String sql = "SELECT * FROM orders WHERE status = :status";
    MapSqlParameterSource params = new MapSqlParameterSource()
        .addValue("status", status);
    return namedParameterJdbcTemplate.query(sql, params, ...);
}

Challenge 2: Маппинг результатов

ОRM автоматически маппит, а с нативным SQL нужно делать руками.

// Используем RowMapper для повторного использования
@Component
public class OrderRowMapper implements RowMapper<Order> {
    @Override
    public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
        return Order.builder()
            .id(rs.getLong("id"))
            .userId(rs.getLong("user_id"))
            .status(rs.getString("status"))
            .total(rs.getBigDecimal("total"))
            .createdAt(rs.getTimestamp("created_at").toLocalDateTime())
            .build();
    }
}

@Service
public class OrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    private OrderRowMapper orderRowMapper;
    
    public List<Order> findAllOrders() {
        String sql = "SELECT id, user_id, status, total, created_at FROM orders";
        return jdbcTemplate.query(sql, orderRowMapper);
    }
}

Challenge 3: Управление connexions

Манульное управление conexsions может привести к утечкам.

// ПЛОХО: Можно забыть закрыть connexion
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM orders");
// Если выброситься exception, connexion останется открытым!

// ХОРОШО: Try-with-resources
public List<Order> findOrders() {
    String sql = "SELECT * FROM orders";
    try (Connection conn = dataSource.getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        
        List<Order> orders = new ArrayList<>();
        while (rs.next()) {
            orders.add(mapRowToOrder(rs));
        }
        return orders;
    } catch (SQLException e) {
        throw new DataAccessException("Failed to fetch orders", e);
    }
}

// ЛУЧШЕ: JdbcTemplate (автоматически управляет connexions)
public List<Order> findOrders() {
    String sql = "SELECT * FROM orders";
    return jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToOrder(rs));
}

Challenge 4: Синхронизация с моделями

Когда меняется схема БД, нужно обновить и SQL запросы.

// Когда добавили колонку discount_applied в таблицу orders
// Нужно было найти и обновить все запросы которые выбирают из этой таблицы

// Решение: Document и версионируй SQL запросы

/*
 * SQL версия: v1.5.0
 * Колонки: id, user_id, status, total, discount_applied, created_at
 * Изменение: Добавлена колонка discount_applied (версия 1.5.0)
 * Миграция: 0005_add_discount_to_orders.sql
 */
public List<OrderDto> getOrders() {
    String sql = """
        SELECT
            id,
            user_id,
            status,
            total,
            discount_applied,
            created_at
        FROM orders
        WHERE deleted_at IS NULL
    """;
    return jdbcTemplate.query(sql, ...);
}

Best Practices

1. Используй JdbcTemplate для типичных случаев

String sql = "SELECT COUNT(*) FROM orders WHERE status = ?";
Integer count = jdbcTemplate.queryForObject(sql, new Object[]{"PENDING"}, Integer.class);

2. Используй NamedParameterJdbcTemplate для читаемости

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("userId", userId)
    .addValue("minAmount", 100);

String sql = "SELECT * FROM orders WHERE user_id = :userId AND total > :minAmount";
List<Order> orders = namedParameterJdbcTemplate.query(sql, params, ...);

3. Всегда параметризируй запросы

Исползуй ?, :name или @param, никогда не конкатенируй строки.

4. Тестируй нативные запросы отдельно

@DataJdbcTest
public class OrderRepositoryTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Test
    public void shouldGetRecentOrders() {
        // Arrange
        jdbcTemplate.update("INSERT INTO orders (user_id, status) VALUES (?, ?)",
            123L, "COMPLETED");
        
        // Act
        List<Order> orders = repository.getRecentOrders(123L);
        
        // Assert
        assertThat(orders).hasSize(1);
    }
}

5. Документируй сложные запросы

Добавь комментарии объясняющие intent и алгоритм.

Результаты оптимизации

Одного запроса, который работал 8 секунд (полная загрузка и фильтрация в памяти), оптимизация с нативным SQL снизила до 300ms через:

• Правильные WHERE условия
• Использование indexes
• GROUP BY вместо post-processing

Этот опыт показывает, что хотя ORM удобен, понимание нативного SQL критично для production систем с требованиями к производительности.