Une introduction à JDBC

Quelques liens utiles

• Les transparents du cours sont disponibles
• Le Guide JDBC d'Oracle
• Le cours de base chez Oracle
• JavaDoc du module java.sql

Mise en place

Utiliser HyperSQL

Pour nos exemples, nous allons utiliser la base de données embarquée HyperSQL. N'oubliez pas de lire la documentation HyperSQL. Elle se présente comme une librairie à ajouter à vos projet.

Spring Boot pour JDBC

• Reprenez le projet Maven du TP précédent.
• Ajoutez à votre fichier pom.xml les dépendances ci-dessous. Elles permettent de charger le Starter JDBC de Spring Boot et ainsi d'inclure les outils dont nous avons besoin.
  À ajouter au fichier pom.xml

  ...
      <!-- pour utiliser jdbc Spring -->
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>
      <!-- pour utiliser HyperSQL -->
      <dependency>
          <groupId>org.hsqldb</groupId>
          <artifactId>hsqldb</artifactId>
      </dependency>
  ...
  

• Créez le package myapp.jdbc dans les sources et dans les tests.

Premiers programmes JDBC

• Commencez par créer la classe ci-dessous. C'est un exemple de classe DAO (Data Access Object) qui réalise les actions CRUD (Create Read Update Delete) sur des données très simples.
  package myapp.jdbc;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.util.Collection;
  import java.util.LinkedList;
  
  import jakarta.annotation.PostConstruct;
  
  import org.springframework.stereotype.Service;
  
  @Service
  public class NameDao {
  
      final String url = "jdbc:hsqldb:file:databases/test-jdbc";
      final String user = "SA";
      final String password = "";
  
      private Connection newConnection() throws SQLException {
          return DriverManager.getConnection(url, user, password);
      }
  
      @PostConstruct
      public void initSchema() throws SQLException {
          var query = "create table if not exists NAME (" //
                  + " id integer not null, " //
                  + " name varchar(50) not null, " //
                  + " primary key (id) )";
          try (var conn = newConnection()) {
              conn.createStatement().execute(query);
          }
      }
  
      public void addName(int id, String name) throws SQLException {
          var query = "insert into NAME values (?,?)";
          try (var conn = newConnection()) {
              var st = conn.prepareStatement(query);
              st.setInt(1, id);
              st.setString(2, name);
              st.execute();
          }
      }
  
      public void deleteName(int id) throws SQLException {
          var query = "Delete From NAME where (id = ?)";
          try (var conn = newConnection()) {
              var st = conn.prepareStatement(query);
              st.setInt(1, id);
              st.execute();
          }
      }
  
      public String findName(int id) throws SQLException {
          var query = "Select * From NAME where (id = ?)";
          try (var conn = newConnection()) {
              var st = conn.prepareStatement(query);
              st.setInt(1, id);
              var rs = st.executeQuery();
              if (rs.next()) {
                  return rs.getString("name");
              }
          }
          return null;
      }
  
      public Collection<String> findNames() throws SQLException {
          var query = "Select * From NAME order by name";
          var result = new LinkedList<String>();
          try (var conn = newConnection()) {
              var st = conn.createStatement();
              var rs = st.executeQuery(query);
              while (rs.next()) {
                  result.add(rs.getString("name"));
              }
          }
          return result;
      }
  
  }
  

• Vous pouvez ensuite créer la classe de test et vérifier son bon fonctionnement :
  package myapp.jdbc;
  
  import static org.junit.jupiter.api.Assertions.assertEquals;
  import static org.junit.jupiter.api.Assertions.assertThrows;
  
  import java.sql.SQLException;
  
  import org.junit.jupiter.api.Test;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  
  @SpringBootTest
  public class TestNameDao {
      
      @Autowired
      NameDao dao;
  
      @Test
      public void testNames() throws SQLException {
          dao.deleteName(100);
          dao.deleteName(200);
          dao.addName(100, "Hello");
          dao.addName(200, "Salut");
          assertEquals("Hello", dao.findName(100));
          assertEquals("Salut", dao.findName(200));
          dao.findNames().forEach(System.out::println);
      }
  
      @Test
      public void testErrors() throws SQLException {
          dao.deleteName(300);
          assertThrows(SQLException.class, () -> {
              dao.addName(300, "Bye");
              dao.addName(300, "Au revoir");
          });
          assertEquals("Bye", dao.findName(300));
      }
  
  }
  

Utiliser une DataSource

Mise en place

Nous allons utiliser les dataSources Hikari fournies par Spring que nous allons configurer en ajoutant ces lignes dans notre fichier src/main/resources/application.properties :

spring.datasource.url=jdbc:hsqldb:file:databases/myBase
spring.datasource.username=SA
spring.datasource.password=
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver

Nous pouvons ainsi simplifier notre classe NameDao :

...

    @Autowired
    DataSource dataSource;

    // Nouvelle version
    private Connection newConnection() throws SQLException {
        return dataSource.getConnection();
    }

...

Nous pouvons également construire une classe de configuration afin de maitriser la création de nos ressources :

package myapp.jdbc;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
public class SpringJdbcConfig {

    @Bean
    public DataSource myDataSource(//
            @Value("${spring.datasource.url}") String url, //
            @Value("${spring.datasource.username}") String user, //
            @Value("${spring.datasource.password}") String password//
    ) {
        System.out.println("--- my datasource");
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(user);
        config.setPassword(password);
        // le pool de connexion doit gérer
        // entre 5 et 10 connections prêtes.
        config.setMinimumIdle(5);
        config.setMaximumPoolSize(10);
        return new HikariDataSource(config);
    }

}

Pool de connexions

Nous allons maintenant illustrer le pool de connexions géré par la DataSource :

• Préparez dans NameDao un travail long (une seconde) :
  public void longWork() {
      try (var c = newConnection()) {
          Thread.sleep(1000);
      } catch (InterruptedException e) {
      } catch (SQLException e1) {
      } 
  }
  

• Préparez un test qui va créer cinq threads pour tester l'allocation des connexions :
  @Test
  public void testWorks() throws Exception {
      long debut = System.currentTimeMillis();
  
      // exécution des threads
      ExecutorService executor = Executors.newFixedThreadPool(10);
      for (int i = 1; (i < 5); i++) {
          executor.execute(dao::longWork);
      }
  
      // attente de la fin des threads
      executor.shutdown();
      executor.awaitTermination(10, TimeUnit.HOURS);
  
      // calcul du temps de réponse
      long fin = System.currentTimeMillis();
      System.out.println("duree = " + (fin - debut) + "ms");
  }
  

• Faites varier les deux derniers paramètres de la DataSource et étudiez l'impact sur le temps de réponse.

Spring et JDBC

Faciliter l'utilisation de JDBC

Nous allons maintenant utiliser les outils de Spring pour simplifier l'utilisation de JDBC.

• Dupliquez la classe NameDao en SpringNameDao.
• Ajoutez à votre classe SpringNameDao une injection automatique d'une instance de JdbcTemplate (boite à outils JDBC de Spring) :
  package myapp.jdbc;
  
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.Collection;
  import java.util.List;
  
  import jakarta.annotation.PostConstruct;
  
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.stereotype.Repository;
  import org.springframework.transaction.annotation.Transactional;
  
  @Repository
  @Transactional
  public class SpringNameDao {
  
      @Autowired
      JdbcTemplate jt;
  
      ...
  
  }
  

• Vous pouvez ensuite dupliquer votre classe de test (afin de tester la version Spring) et, bien entendu, lui injecter la Dao version Spring.

Prévoir un script d'initialisation

Nous pouvons améliorer la phase de création du schéma relationnel en délocalisation le script dans un fichier schema.sql placé dans la package myapp.jdbc :

@PostConstruct
public void initSchema() throws SQLException {
    try (var c = jt.getDataSource().getConnection()) {
        var res = new ClassPathResource("schema.sql", SpringNameDao.class);
        ScriptUtils.executeSqlScript(c, res);
    }
}

Utiliser un Javabean

Commencez par préparer le Javabean de représentation des noms :

package myapp.jdbc;

import java.io.Serializable;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Name implements Serializable {

    private static final long serialVersionUID = 1L;

    private int id;
    private String name;

}