Какие простые SQL-запросы использовал

Простые SQL-запросы в повседневной работе Java Developer

В своей практике я регулярно использую набор простых, но эффективных SQL-запросов. Расскажу о самых полезных из них и когда их применяю.

1. Базовый SELECT с WHERE

Использование: Поиск пользователя по email

SELECT id, email, name, status, created_at
FROM users
WHERE email = 'user@example.com'
LIMIT 1;

В Java (JPA):

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);
}

2. SELECT с несколькими условиями

Использование: Поиск активных заказов за период

SELECT id, user_id, total_amount, status, created_at
FROM orders
WHERE status = 'COMPLETED'
  AND created_at BETWEEN '2023-01-01' AND '2023-12-31'
  AND total_amount > 1000
ORDER BY created_at DESC
LIMIT 100;

В Java:

@Query("""
    SELECT o FROM Order o
    WHERE o.status = 'COMPLETED'
      AND o.createdAt BETWEEN :startDate AND :endDate
      AND o.totalAmount > :minAmount
    ORDER BY o.createdAt DESC
""")
List<Order> findCompletedOrdersByDateRange(
    @Param("startDate") LocalDate start,
    @Param("endDate") LocalDate end,
    @Param("minAmount") BigDecimal minAmount,
    Pageable pageable
);

3. INSERT простой записи

Использование: Создание нового пользователя

INSERT INTO users (email, name, status, created_at)
VALUES ('newuser@example.com', 'John Doe', 'ACTIVE', NOW());

В Java (Spring Data):

User user = User.builder()
    .email("newuser@example.com")
    .name("John Doe")
    .status(Status.ACTIVE)
    .createdAt(LocalDateTime.now(UTC))
    .build();

userRepository.save(user);

4. UPDATE условный

Использование: Активация пользователя после подтверждения email

UPDATE users
SET status = 'VERIFIED', verified_at = NOW()
WHERE id = 123
  AND status = 'PENDING';

В Java:

@Modifying
@Transactional
@Query("""
    UPDATE User u
    SET u.status = 'VERIFIED', u.verifiedAt = CURRENT_TIMESTAMP
    WHERE u.id = :userId AND u.status = 'PENDING'
""")
int verifyUser(@Param("userId") Long userId);

5. DELETE с условием

Использование: Удаление неактивных сессий

DELETE FROM sessions
WHERE user_id = 456
  AND last_accessed < NOW() - INTERVAL '7 days';

В Java:

@Modifying
@Transactional
@Query("""
    DELETE FROM Session s
    WHERE s.userId = :userId
      AND s.lastAccessed < CURRENT_TIMESTAMP - 7 DAY
""")
void deleteOldSessions(@Param("userId") Long userId);

6. COUNT для агрегации

Использование: Проверка количества активных пользователей

SELECT COUNT(*) as active_count
FROM users
WHERE status = 'ACTIVE'
  AND last_login > NOW() - INTERVAL '30 days';

В Java:

long activeUsersCount = userRepository.countByStatusAndLastLoginAfter(
    Status.ACTIVE,
    LocalDateTime.now(UTC).minusDays(30)
);

7. GROUP BY для статистики

Использование: Доход по категориям товаров

SELECT
    category,
    COUNT(*) as order_count,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM orders
WHERE status = 'COMPLETED'
  AND created_at > NOW() - INTERVAL '1 month'
GROUP BY category
ORDER BY total_revenue DESC;

В Java:

@Query("""
    SELECT new map(
        o.category as category,
        COUNT(o) as orderCount,
        SUM(o.amount) as totalRevenue,
        AVG(o.amount) as avgOrderValue
    )
    FROM Order o
    WHERE o.status = 'COMPLETED'
      AND o.createdAt > CURRENT_TIMESTAMP - 1 MONTH
    GROUP BY o.category
    ORDER BY SUM(o.amount) DESC
""")
List<Map<String, Object>> getRevenueByCategory();

8. JOIN для связанных данных

Использование: Получить заказы с данными пользователя

SELECT
    o.id,
    o.total_amount,
    u.email,
    u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC;

В Java (Spring Data):

@Query("""
    SELECT new OrderDTO(
        o.id,
        o.totalAmount,
        u.email,
        u.name
    )
    FROM Order o
    JOIN o.user u
    WHERE o.status = 'PENDING'
    ORDER BY o.createdAt DESC
""")
List<OrderDTO> findPendingOrdersWithUsers();

9. Pagination

Использование: Постраничный вывод списка

SELECT id, email, name
FROM users
WHERE status = 'ACTIVE'
ORDER BY created_at DESC
LIMIT 20 OFFSET 40; -- Страница 3, по 20 на странице

В Java:

Page<User> users = userRepository.findByStatus(
    Status.ACTIVE,
    PageRequest.of(2, 20, Sort.by("createdAt").descending())
);

10. DISTINCT для уникальных значений

Использование: Список стран, где есть пользователи

SELECT DISTINCT country
FROM users
WHERE status = 'ACTIVE'
ORDER BY country;

В Java:

@Query("""
    SELECT DISTINCT u.country
    FROM User u
    WHERE u.status = 'ACTIVE'
    ORDER BY u.country
""")
List<String> findDistinctCountries();

11. LEFT JOIN для опциональных данных

Использование: Пользователи с их профилями (если есть)

SELECT
    u.id,
    u.email,
    p.bio,
    p.avatar_url
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE u.status = 'ACTIVE';

В Java:

@Query("""
    SELECT new UserProfileDTO(
        u.id,
        u.email,
        p.bio,
        p.avatarUrl
    )
    FROM User u
    LEFT JOIN u.profile p
    WHERE u.status = 'ACTIVE'
""")
List<UserProfileDTO> findUsersWithProfiles();

12. EXISTS для проверки наличия данных

Использование: Проверка, есть ли у пользователя заказы

SELECT id, email
FROM users
WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE user_id = users.id
      AND status = 'COMPLETED'
);

В Java:

@Query("""
    SELECT u FROM User u
    WHERE EXISTS (
        SELECT 1 FROM Order o
        WHERE o.user = u
          AND o.status = 'COMPLETED'
    )
""")
List<User> findUsersWithCompletedOrders();

Золотые правила SQL в Java-разработке

1. Используйте параметризованные запросы

-- Неправильно (SQL Injection!)
SELECT * FROM users WHERE email = '" + email + "';

-- Правильно
SELECT * FROM users WHERE email = ?;

2. Индексируйте часто используемые колонки

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

**3. Избегайте SELECT ***

-- Неправильно
SELECT * FROM orders;

-- Правильно
SELECT id, user_id, total_amount FROM orders;

4. Используйте EXPLAIN для анализа

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PENDING';

Вывод

В повседневной работе Java developer используются в основном простые запросы. Spring Data/JPA скрывают большую часть SQL-синтаксиса, но понимание базовых операций критично для эффективной работы с БД. Всегда профилируйте медленные запросы и добавляйте индексы, где нужно.