Сложно ли составить структуру работы с базой данных

Сложно ли составить структуру работы с базой данных

Нет, это не сложно, если использовать проверенные паттерны и лучшие практики. Давайте разберемся как правильно организовать работу с БД.

Правильная архитектура: Layered Architecture

┌─────────────────────────────┐
│   Presentation Layer        │  Controllers, REST API
│   (API endpoints)           │
└────────────────┬────────────┘
                 │
┌────────────────▼────────────┐
│  Application Layer          │  Use Cases, Business Logic
│  (Services)                 │
└────────────────┬────────────┘
                 │
┌────────────────▼────────────┐
│   Domain Layer              │  Entity, Value Objects
│   (Business Models)         │
└────────────────┬────────────┘
                 │
┌────────────────▼────────────┐
│  Infrastructure Layer       │  Repositories, Database
│  (Data Access)              │  QueryService, Mappers
└─────────────────────────────┘

Зависимости: только вовнутрь (от Presentation к Infrastructure)

Паттерн Repository

Это основной паттерн для работы с БД:

// Domain Layer - интерфейс (контракт)
public interface UserRepository {
    Optional<User> findById(UUID id);
    List<User> findByEmail(String email);
    void save(User user);
    void delete(UUID id);
}

// Infrastructure Layer - реализация
@Repository
public class PostgreSQLUserRepository implements UserRepository {
    private final JdbcTemplate jdbcTemplate;
    private final UserMapper mapper;
    
    @Override
    public Optional<User> findById(UUID id) {
        String sql = "SELECT * FROM users WHERE id = @id";
        try {
            User user = jdbcTemplate.queryForObject(sql,
                new MapSqlParameterSource("id", id),
                mapper);
            return Optional.of(user);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public void save(User user) {
        String sql = """
            INSERT INTO users (id, name, email, created_at)
            VALUES (@id, @name, @email, @createdAt)
            ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                email = EXCLUDED.email
            """;
        
        MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("id", user.getId())
            .addValue("name", user.getName())
            .addValue("email", user.getEmail())
            .addValue("createdAt", user.getCreatedAt());
        
        jdbcTemplate.update(sql, params);
    }
}

// Application Layer - Service использует Repository
@Service
public class CreateUserUseCase {
    private final UserRepository userRepository;
    
    public User execute(CreateUserCommand cmd) {
        // Проверяем что email не занят
        var existingUser = userRepository.findByEmail(cmd.email());
        if (existingUser.isNotEmpty()) {
            throw new EmailAlreadyTakenException();
        }
        
        // Создаём пользователя
        var newUser = new User(
            UUID.randomUUID(),
            cmd.name(),
            cmd.email(),
            LocalDateTime.now(UTC)
        );
        
        // Сохраняем
        userRepository.save(newUser);
        
        return newUser;
    }
}

// Presentation Layer - Controller вызывает Use Case
@RestController
@RequestMapping("/api/v1/users")
public class UserController {
    private final CreateUserUseCase createUserUseCase;
    
    @PostMapping
    public ResponseEntity<UserDTO> createUser(@RequestBody CreateUserRequest req) {
        var user = createUserUseCase.execute(
            new CreateUserCommand(req.name(), req.email())
        );
        
        return ResponseEntity
            .status(HttpStatus.CREATED)
            .location(URI.create("/api/v1/users/" + user.getId()))
            .body(UserDTO.from(user));
    }
}

Тестирование: зависимости вверх

// Тест Service без реальной БД
@Test
public void testCreateUser() {
    // Mock Repository
    UserRepository mockRepo = mock(UserRepository.class);
    CreateUserUseCase useCase = new CreateUserUseCase(mockRepo);
    
    // Когда findByEmail не находит
    when(mockRepo.findByEmail("test@example.com"))
        .thenReturn(Optional.empty());
    
    // Выполняем
    var result = useCase.execute(
        new CreateUserCommand("John", "test@example.com")
    );
    
    // Проверяем
    assertEquals("John", result.getName());
    verify(mockRepo).save(any(User.class));
}

// Интеграционный тест с реальной БД
@SpringBootTest
public class UserRepositoryIntegrationTest {
    @Autowired
    private UserRepository userRepository;
    
    @Test
    @Transactional
    public void testSaveAndFind() {
        // Setup
        var user = new User(UUID.randomUUID(), "John", "john@example.com", now());
        
        // Execute
        userRepository.save(user);
        var found = userRepository.findById(user.getId());
        
        // Assert
        assertTrue(found.isPresent());
        assertEquals("John", found.get().getName());
    }
}

Сложные запросы: QueryService паттерн

Для сложных SELECT-ов не всегда нужно Repository:

// Domain Layer - интерфейс запроса
public interface UserQueryService {
    List<UserWithOrderCountDTO> findUsersWithOrderCount(String email);
    Page<UserDTO> searchUsers(UserSearchCriteria criteria, Pageable pageable);
}

// Infrastructure Layer - реализация
@Service
public class PostgreSQLUserQueryService implements UserQueryService {
    private final NamedParameterJdbcTemplate jdbc;
    private final UserMapper mapper;
    
    @Override
    public List<UserWithOrderCountDTO> findUsersWithOrderCount(String email) {
        String sql = """
            SELECT 
                u.id,
                u.name,
                u.email,
                COUNT(o.id) as order_count
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            WHERE u.email LIKE CONCAT('%', @email, '%')
            GROUP BY u.id, u.name, u.email
            ORDER BY order_count DESC
            """;
        
        MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("email", email);
        
        return jdbc.query(sql, params, (rs, rowNum) -> 
            new UserWithOrderCountDTO(
                UUID.fromString(rs.getString("id")),
                rs.getString("name"),
                rs.getString("email"),
                rs.getInt("order_count")
            )
        );
    }
    
    @Override
    public Page<UserDTO> searchUsers(UserSearchCriteria criteria, Pageable pageable) {
        // Динамически строим WHERE clause
        StringBuilder where = new StringBuilder();
        MapSqlParameterSource params = new MapSqlParameterSource();
        
        if (criteria.getName() != null) {
            where.append(" AND u.name ILIKE CONCAT('%', @name, '%')");
            params.addValue("name", criteria.getName());
        }
        
        if (criteria.getStatus() != null) {
            where.append(" AND u.status = @status");
            params.addValue("status", criteria.getStatus());
        }
        
        String sql = """
            SELECT * FROM users u
            WHERE 1=1 " + where + "
            ORDER BY " + criteria.getSort() + "
            LIMIT @limit OFFSET @offset
            """;
        
        params.addValue("limit", pageable.getPageSize());
        params.addValue("offset", pageable.getOffset());
        
        List<User> content = jdbc.query(sql, params, mapper);
        long total = countUsers(where, params);
        
        return new PageImpl<>(content, pageable, total);
    }
}

// Use Case использует QueryService
@Service
public class SearchUsersUseCase {
    private final UserQueryService queryService;
    
    public Page<UserDTO> execute(SearchUsersCommand cmd, Pageable pageable) {
        var criteria = new UserSearchCriteria(cmd.name(), cmd.status());
        return queryService.searchUsers(criteria, pageable);
    }
}

Миграции: Goose (не Alembic)

-- migrations/0001_create_users.sql
CREATE TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

-- migrations/0002_add_phone.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);

# Применить миграции
goose -dir migrations postgres "user=dev password=dev dbname=myapp" up

# Откатить последнюю
goose -dir migrations postgres "user=dev password=dev dbname=myapp" down

Мапперы: от Entity к DTO

@Component
public class UserMapper implements RowMapper<User> {
    
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(
            UUID.fromString(rs.getString("id")),
            rs.getString("name"),
            rs.getString("email"),
            rs.getObject("created_at", LocalDateTime.class)
        );
    }
}

// DTO для API responses
public record UserDTO(
    UUID id,
    String name,
    String email
) {
    public static UserDTO from(User user) {
        return new UserDTO(user.getId(), user.getName(), user.getEmail());
    }
}

Структура директорий

src/main/java/com/example/
├── domain/
│   ├── entity/
│   │   └── User.java
│   ├── repository/
│   │   └── UserRepository.java  (интерфейс)
│   └── service/
│       └── UserQueryService.java  (интерфейс)
├── application/
│   └── usecase/
│       ├── CreateUserUseCase.java
│       └── SearchUsersUseCase.java
├── infrastructure/
│   ├── persistence/
│   │   ├── PostgreSQLUserRepository.java
│   │   ├── PostgreSQLUserQueryService.java
│   │   └── UserMapper.java
│   └── config/
│       └── DatabaseConfig.java
└── presentation/
    ├── controller/
    │   └── UserController.java
    ├── dto/
    │   └── UserDTO.java
    └── request/
        └── CreateUserRequest.java

resources/
└── db/migration/
    ├── 0001_create_users.sql
    └── 0002_add_phone.sql

Best Practices

// ✓ DO: Используй Named Parameters
String sql = "SELECT * FROM users WHERE id = @id AND status = @status";
MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("id", userId)
    .addValue("status", "active");

// ✗ DON'T: String concatenation (SQL Injection!)
String sql = "SELECT * FROM users WHERE id = '" + userId + "'";

// ✓ DO: Explicit transactions
@Transactional
public void transferMoney(UUID from, UUID to, BigDecimal amount) {
    // Оба UPDATE'а будут либо успешны либо откачены
    accountRepo.updateBalance(from, amount.negate());
    accountRepo.updateBalance(to, amount);
}

// ✓ DO: Connection pooling
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        return new HikariDataSource(config);
    }
}

Мой ответ на интервью

"Нет, это не сложно если использовать правильные паттерны.

Я использую Layered Architecture:
1. Domain Layer - интерфейсы Repository и QueryService
2. Application Layer - Use Cases (business logic)
3. Infrastructure Layer - реализация Repository и QueryService
4. Presentation Layer - Controllers

Зависимости идут только вовнутрь, что упрощает тестирование.

Для работы с БД:
- Repository для CRUD операций
- QueryService для сложных SELECT'ов
- Mapper'ы для маппирования ResultSet -> Entity/DTO
- Миграции через Goose (raw SQL)

Все это управляется через Dependency Injection,
что облегчает тестирование (mock dependencies).

В production я всегда:
- Использую named parameters (защита от SQL Injection)
- Явно управляю транзакциями
- Конфигурирую connection pooling
- Индексирую часто используемые колонки
- Мониторю slow queries"

Резюме

Структура работы с БД основана на:

• Repository паттерн для CRUD
• QueryService для сложных SELECT'ов
• Mapper'ы для трансформации данных
• Use Cases для бизнес-логики
• Raw SQL через NamedParameterJdbcTemplate
• Миграции через Goose

Это не сложно потому что:

• Паттерны проверены годами
• Инструменты (Spring, JDBC) делают это просто
• Правильная структура облегчает тестирование
• SQL остаётся контролируемым и понятным