Cours : JDBC et Bases de Données

1. JDBC

1.1 Architecture JDBC

Application Java
      ↓
   JDBC API
      ↓
 JDBC Driver (ex: postgresql-connector)
      ↓
   Base de données

1.2 Connexion

// 1. Chargement du driver (Java 6+ : automatique)
// Class.forName("org.postgresql.Driver");

// 2. Connexion
String url = "jdbc:postgresql://localhost:5432/mydb";
String user = "user";
String password = "password";

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    System.out.println("Connecté à " + conn.getMetaData().getDatabaseProductName());
}

1.3 PreparedStatement

// ❌ Statement (risque d'injection SQL)
try (Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery(
        "SELECT * FROM users WHERE name = '" + name + "'"); // Injection !
}

// ✅ PreparedStatement (sécurisé)
String sql = "SELECT id, name, email FROM users WHERE name = ? AND active = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, name);
    ps.setBoolean(2, true);
    
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            Long id = rs.getLong("id");
            String userName = rs.getString("name");
            String email = rs.getString("email");
        }
    }
}

1.4 Transactions

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false); // Début de transaction
    try {
        // Opérations
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
            ps.setDouble(1, 100);
            ps.setLong(2, fromAccount);
            ps.executeUpdate();
        }
        
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            ps.setDouble(1, 100);
            ps.setLong(2, toAccount);
            ps.executeUpdate();
        }
        
        conn.commit(); // Validation
    } catch (SQLException e) {
        conn.rollback(); // Annulation
        throw e;
    }
}

1.5 Batching

String sql = "INSERT INTO logs (message, level, created_at) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (LogEntry entry : entries) {
        ps.setString(1, entry.message());
        ps.setString(2, entry.level());
        ps.setTimestamp(3, Timestamp.valueOf(entry.createdAt()));
        ps.addBatch(); // Ajoute au lot
    }
    int[] results = ps.executeBatch(); // Exécute tout en une fois
}

2. Connection Pooling (HikariCP)

2.1 Configuration

// HikariCP
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setConnectionTimeout(30000); // 30s
config.setIdleTimeout(600000);      // 10min
config.setMaxLifetime(1800000);     // 30min
config.setConnectionTestQuery("SELECT 1");

HikariDataSource dataSource = new HikariDataSource(config);

// Utilisation
try (Connection conn = dataSource.getConnection()) {
    // La connexion retourne au pool après close()
}

3. JPA/Hibernate

3.1 Entité

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, length = 100)
    private String name;
    
    @Column(unique = true, nullable = false)
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    // Constructeurs, getters, setters
}

3.2 EntityManager

// Persister
User user = new User();
user.setName("Alice");
user.setEmail("alice@email.com");
entityManager.persist(user);

// Trouver par ID
User user = entityManager.find(User.class, 1L);

// Requête JPQL
List<User> users = entityManager
    .createQuery("SELECT u FROM User u WHERE u.name LIKE :name", User.class)
    .setParameter("name", "%Ali%")
    .getResultList();

// Requête native SQL
List<Object[]> results = entityManager
    .createNativeQuery("SELECT id, name FROM users WHERE active = ?")
    .setParameter(1, true)
    .getResultList();

// Mettre à jour
User user = entityManager.find(User.class, 1L);
user.setName("Alice Updated"); // Auto-détecté (dirty checking)
entityManager.flush(); // Synchronisation explicite

// Supprimer
User user = entityManager.find(User.class, 1L);
entityManager.remove(user);

3.3 Persistence Context

Le contexte de persistance (1er niveau cache) :

• Durée de vie : une transaction
• Détecte les changements (dirty checking)
• Garantit l'unicité des entités (même ID → même instance)

User u1 = entityManager.find(User.class, 1L);
User u2 = entityManager.find(User.class, 1L);
System.out.println(u1 == u2); // true (même instance)

4. Relations et Fetching Strategies

4.1 Types de Relations

@Entity
public class User {
    @Id
    private Long id;
    
    // OneToMany : un utilisateur a plusieurs commandes
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Order> orders = new ArrayList<>();
    
    // ManyToOne : plusieurs commandes appartiennent à un utilisateur
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "address_id")
    private Address address;
    
    // OneToOne
    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL)
    private Profile profile;
    
    // ManyToMany
    @ManyToMany
    @JoinTable(name = "user_roles",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles = new HashSet<>();
}

@Entity
public class Order {
    @Id
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY) // Toujours LAZY !
    @JoinColumn(name = "user_id")
    private User user;
}

4.2 Fetching Strategies

// EAGER : charge immédiatement (⚠️ dangereux)
@ManyToOne(fetch = FetchType.EAGER)
// ⚠️ Problème : toujours chargé, même si non utilisé

// LAZY : charge à la demande (✅ recommandé)
@OneToMany(fetch = FetchType.LAZY)
// Solution : utiliser JOIN FETCH pour charger explicitement

// JOIN FETCH (JPQL)
String jpql = "SELECT u FROM User u JOIN FETCH u.orders WHERE u.id = :id";
User user = entityManager.createQuery(jpql, User.class)
    .setParameter("id", 1L)
    .getSingleResult();

// Entity Graph (Java 8+)
@EntityGraph(attributePaths = {"orders", "profile"})
@Query("SELECT u FROM User u WHERE u.id = :id")
User findWithDetails(@Param("id") Long id);

5. Problème N+1

5.1 Le Problème

// N+1 queries !
List<User> users = userRepository.findAll(); // 1 query
for (User user : users) {
    System.out.println(user.getOrders().size()); // N queries !
}
// Total : 1 (users) + N (orders) = N+1

5.2 Solutions

// 1. JOIN FETCH en JPQL
@Query("SELECT DISTINCT u FROM User u JOIN FETCH u.orders")
List<User> findAllWithOrders();

// 2. Entity Graph
@EntityGraph(attributePaths = "orders")
@Query("SELECT u FROM User u")
List<User> findAllWithOrders();

// 3. @BatchSize (Hibernate)
@BatchSize(size = 10)
@OneToMany(mappedBy = "user")
private List<Order> orders;

// 4. DTO Projection
@Query("SELECT new com.example.UserOrderDTO(u.name, o.total) " +
       "FROM User u JOIN u.orders o")
List<UserOrderDTO> findUserOrderDTOs();

// 5. Spring Data JPA : Entity Graph
public interface UserRepository extends JpaRepository<User, Long> {
    @EntityGraph(attributePaths = {"orders"})
    List<User> findAll();
}

6. Cache

6.1 Cache de 1er Niveau (Persistence Context)

// Toujours actif, par transaction
User u1 = em.find(User.class, 1L); // SQL
User u2 = em.find(User.class, 1L); // Cache (pas de SQL)
System.out.println(u1 == u2); // true

6.2 Cache de 2nd Niveau

// Activation dans persistence.xml
<property name="hibernate.cache.use_second_level_cache" value="true"/>
<property name="hibernate.cache.region.factory_class" 
    value="org.hibernate.cache.jcache.JCacheRegionFactory"/>

// Marquer l'entité comme cachable
@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class User {
    // ...
}

// Cache de requêtes
<property name="hibernate.cache.use_query_cache" value="true"/>
// Query hint
query.setHint("org.hibernate.cacheable", true);

7. Flyway (Migrations)

7.1 Structure des Migrations

src/main/resources/db/migration/
├── V1__create_users.sql
├── V2__create_orders.sql
├── V3__add_email_to_users.sql
└── V4__add_index_on_orders.sql

7.2 Exemple de Migration

-- V1__create_users.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__add_email_verified.sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
UPDATE users SET email_verified = TRUE;

7.3 Configuration Spring Boot

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true

7.4 Rollback (Undo Migrations)

-- V2__add_email_verified.sql
-- Migration
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- V2__add_email_verified__undo.sql
-- Rollback
ALTER TABLE users DROP COLUMN email_verified;

8. Spring Data JPA

8.1 Repository

public interface UserRepository extends JpaRepository<User, Long> {
    // Derived queries
    Optional<User> findByEmail(String email);
    List<User> findByNameContainingIgnoreCase(String name);
    List<User> findByCreatedAtAfter(LocalDateTime date);
    boolean existsByEmail(String email);
    long countByActiveTrue();
    
    // JPQL
    @Query("SELECT u FROM User u WHERE u.email = :email")
    Optional<User> findByEmailCustom(@Param("email") String email);
    
    // Native SQL
    @Query(value = "SELECT * FROM users WHERE email = :email", nativeQuery = true)
    Optional<User> findByEmailNative(@Param("email") String email);
    
    // Modifying
    @Modifying
    @Query("UPDATE User u SET u.name = :name WHERE u.id = :id")
    int updateName(@Param("id") Long id, @Param("name") String name);
    
    // Pagination
    Page<User> findByActiveTrue(Pageable pageable);
    
    // Sort
    List<User> findByActiveTrueOrderByNameAsc();
}

// Utilisation
@Service
public class UserService {
    private final UserRepository userRepository;
    
    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
    
    public Page<User> findActiveUsers(int page, int size) {
        return userRepository.findByActiveTrue(
            PageRequest.of(page, size, Sort.by("name")));
    }
}

8.2 Projections

// Interface Projection
public interface UserSummary {
    String getName();
    String getEmail();
}

// Closed projection
@Query("SELECT u.name AS name, u.email AS email FROM User u")
List<UserSummary> findAllSummaries();

// Class-based DTO projection
public record UserDTO(String name, String email) {}

@Query("SELECT new com.example.UserDTO(u.name, u.email) FROM User u")
List<UserDTO> findUserDTOs();

8.3 Specifications (Criteria API)

public interface UserRepository extends JpaRepository<User, Long>, 
                                        JpaSpecificationExecutor<User> {}

// Specifications
public class UserSpecifications {
    public static Specification<User> hasName(String name) {
        return (root, query, cb) -> 
            cb.equal(root.get("name"), name);
    }
    
    public static Specification<User> hasMinAge(int age) {
        return (root, query, cb) -> 
            cb.greaterThanOrEqualTo(root.get("age"), age);
    }
    
    public static Specification<User> isActive() {
        return (root, query, cb) -> cb.isTrue(root.get("active"));
    }
}

// Utilisation
List<User> users = userRepository.findAll(
    where(hasName("Alice"))
        .and(hasMinAge(18))
        .and(isActive())
);

9. Transactions (@Transactional)

9.1 Propagation

@Transactional
public class UserService {
    
    @Transactional(propagation = Propagation.REQUIRED) // Défaut
    public void createUser(User user) {
        // Si transaction existe → l'utilise
        // Sinon → en crée une
    }
    
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logAccess(User user) {
        // Toujours une NOUVELLE transaction
        // Indépendante de la transaction appelante
    }
    
    @Transactional(propagation = Propagation.MANDATORY)
    public void processPayment(Order order) {
        // Doit être appelé DANS une transaction
        // Lance exception si aucune transaction
    }
    
    @Transactional(propagation = Propagation.NEVER)
    public void sendEmail(Notification notif) {
        // Ne doit PAS être dans une transaction
    }
    
    @Transactional(propagation = Propagation.NESTED)
    public void updatePreferences(User user) {
        // Point de sauvegarde (savepoint)
        // Permet un rollback partiel
    }
}

9.2 Isolation

@Transactional(isolation = Isolation.READ_COMMITTED) // Défaut PostgreSQL
// Lecture seule des données validées

@Transactional(isolation = Isolation.REPEATABLE_READ)
// Garantit que les lignes lues ne changent pas

@Transactional(isolation = Isolation.SERIALIZABLE)
// Exécution comme si les transactions étaient séquentielles

@Transactional(isolation = Isolation.READ_UNCOMMITTED)
// Permet la lecture de données non validées (dirty read)

9.3 Problèmes d'Isolation

9.4 Configuration Spring

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: user
    password: password
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        jdbc.batch_size: 20
        cache.use_second_level_cache: true

10. Bonnes Pratiques

// 1. Toujours LAZY fetching
@ManyToOne(fetch = FetchType.LAZY)  // ✅
@OneToMany(fetch = FetchType.LAZY)  // ✅

// 2. Utiliser JOIN FETCH pour les cas nécessaires
@Query("SELECT u FROM User u JOIN FETCH u.orders")

// 3. Pagination pour les grandes collections
Page<User> page = repository.findAll(PageRequest.of(0, 20));

// 4. DTO Projection pour les lectures seules
@Query("SELECT new UserDTO(u.name, u.email) FROM User u")

// 5. Batch processing
@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.lastLogin < :date")
int deactivateOldUsers(@Param("date") LocalDate date);

// 6. Transaction en lecture seule (optimisation)
@Transactional(readOnly = true)
public User findById(Long id) { ... }

// 7. Éviter N+1
@EntityGraph(attributePaths = "orders")
List<User> findAll();

11. Résumé