Spring & JDBC 1 : Select

Tu as fait tes premières requêtes SQL dans d'autres quêtes : cela serait vraiment pratique de pouvoir les utiliser dans du code Java pour faire interagir ton application avec une base de données. C'est ce que JDBC va te permettre.

Bonne nouvelle : la bibliothèque est compatible avec l'utilisation de Spring !

Objectifs

• Se connecter à une base de données avec JDBC
• Créer une route qui récupère les données

Démarrer avec JDBC

1. Initialisation

Pour créer un projet utilisant la bibliothèque, rends-toi sur https://start.spring.io/ et dans les dependencies, sélectionne MySQL Driver (en plus des bibliothèques que tu utilises habituellement).

2. Base de données

Pour que le code présenté ci-dessous fonctionne, il te faudra posséder la base de données spring_jdbc_quest.

De plus, un utilisateur avec l'identifiant h4rryp0tt3r et le mot de passe Horcrux4life! devra exister et avoir les droits en lecture sur la base de données spring_jdbc_quest.

3. Configuration

Mettons-nous en situation : tu veux créer un site permettant d'afficher les sorciers de l'univers de Harry Potter.

Tu possèdes déjà une base de données contenant ces informations, dont tu peux avoir un aperçu de la structure et des données à partir de ce lien.

Tu peux suivre la quête sans écrire le code en même temps : tu auras un dépôt d'exemple dans le challenge. Cependant, si tu préfères écrire du code en suivant la quête à côté, pas de problème : dans ce cas-là, il faudra que tu importes la base de données ci-dessus.

La première étape va consister à créer un contrôleur te permettant d'accéder et d'afficher les données, ici WizardController.

Afin de pouvoir te connecter à cette base de données, il va te falloir configurer les identifiants et le nom de la base.

Nous verrons dans une autre quête comment créer un fichier de configuration (qu'il faudra ajouter au .gitignore). Pour le moment, tu vas te contenter de mettre les informations en variable globale.

private final static String DB_URL = "jdbc:mysql://localhost:3306/spring_jdbc_quest?serverTimezone=GMT";
private final static String DB_USER = "h4rryp0tt3r";
private final static String DB_PASSWORD = "Horcrux4life!";

Créer une route pour interagir avec la base de données

1. La route

Maintenant que tu as des identifiants de connexion, il va te falloir créer la route à appeler pour afficher les données.

Tu vas partir du principe que tu possèdes un template wizard_get_all.html qui s'attend à afficher la liste de tous les sorciers.

Lorsque quelqu'un va interroger le serveur sur http://localhost:8080/wizards, tu vas vouloir afficher l'ensemble des sorciers.

@GetMapping("/wizards")
public String getAll(Model model) {
    // TODO

    return "wizard_get_all";
}

L'attribut model servira plus tard à transmettre la liste des sorciers au template

2. Requête SQL

Grâce à une requête SQL SELECT, tu vas pouvoir récupérer ces informations.

La requête elle-même sera simple :

SELECT * FROM wizard;

Pour exécuter cette requête, tu vas d'abord devoir te connecter à ta base de données :

Connection connection = DriverManager.getConnection(
    DB_URL, DB_USER, DB_PASSWORD
);

DB_URL, DB_USER, DB_PASSWORD sont les constantes que tu as déclarées précédemment.

Tu peux maintenant utiliser ta connexion pour préparer une instruction (statement en anglais) à exécuter dans MySQL :

PreparedStatement statement = connection.prepareStatement(
    "SELECT * FROM wizard;"
);

Enfin, grâce à cette instruction, tu vas pouvoir exécuter ta requête SQL et en obtenir les résultats :

ResultSet resultSet = statement.executeQuery();

Tous ces appels peuvent lever une exception : tu dois donc les mettre dans un bloc try/catch. Le type d'erreur relevée sera SQLException, tu dois donc entourer le code de la façon suivante :

try {
    Connection connection = DriverManager.getConnection(
            DB_URL, DB_USER, DB_PASSWORD
    );
    PreparedStatement statement = connection.prepareStatement(
            "SELECT * FROM wizard;"
    );
    ResultSet resultSet = statement.executeQuery();
} catch (SQLException e) {
    e.printStackTrace();
}

3. Traitement des données

L'instance de ResultSet que tu as obtenue contient des données brutes issues de la base de données. Avant de pouvoir utiliser ces données, tu vas devoir les encapsuler dans un objet qui reprend chaque colonne de la table avec laquelle tu as interagi.

Pour cet exemple, tu peux commencer avec l'id, le prénom et le nom de la table wizard. Tu vas donc créer une classe Wizard que tu rangeras dans un package entity (par convention, c'est le package qui gère les classes d'objet issues de la base de données).

class Wizard {

    private int id;
    private String firstName;
    private String lastName;

    public Wizard(int id, String firstName, String lastName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    // TODO : add getters and setters 
}

Il te reste maintenant à transférer les informations de tes résultats vers des instances de Wizard. ResultSet te fournit à la fois des outils pour boucler sur les résultats et des accesseurs pour extraire les informations en fonction de leur type : getInt pour des entiers, getString pour des chaînes de caractères...

List<Wizard> wizards = new ArrayList<>();

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String firstName = resultSet.getString("first_name");
    String lastName = resultSet.getString("last_name");
    wizards.add(new Wizard(id, firstName, lastName));
}

model.addAttribute("wizards", wizards); // send the list to the template

Si tu t'es perdu en route, pas d'inquiétude. Voici la méthode complète :

@GetMapping("/wizards")
public String getAll(Model model) {

    try {
        Connection connection = DriverManager.getConnection(
                DB_URL, DB_USER, DB_PASSWORD
        );
        PreparedStatement statement = connection.prepareStatement(
                "SELECT * FROM wizard;"
        );
        ResultSet resultSet = statement.executeQuery();

        List<Wizard> wizards = new ArrayList<>();

        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String firstName = resultSet.getString("first_name");
            String lastName = resultSet.getString("last_name");
            wizards.add(new Wizard(id, firstName, lastName));
        }

        model.addAttribute("wizards", wizards);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return "wizard_get_all";
}

Filtrer les résultats

Tu m'as suivi aveuglément quand je t'ai dit d'utiliser PreparedStatement dans les étapes précédentes : en fait, tu aurais pu utiliser d'autres types d'instructions. Un des avantages de PreparedStatement, c'est que tu vas pouvoir intégrer des paramètres dans tes requêtes de façon plus sécurisée (je te laisse explorer les ressources pour les explications).

1. Filtrer sur une valeur

Imaginons que tu veuilles récupérer de ta base seulement les sorciers de la famille Weasley. Tu pourrais utiliser la requête SQL suivante :

SELECT * FROM wizard WHERE last_name='Weasley'

Cela pourrait être pratique que la valeur Weasley utilisée dans la clause WHERE soit un paramètre renseigné par l'utilisateur. C'est possible avec JDBC, en utilisant cette syntaxe :

PreparedStatement statement = connection.prepareStatement(
    "SELECT * FROM wizard WHERE last_name=?"
);

Ici, le caractère ? représente un paramètre : tu devras lui assigner une valeur avant d'exécuter ton instruction. L'interface PreparedStatement te fournit des accesseurs pour ça. Pour la liste complète, tu peux suivre le lien vers la documentation dans les ressources. Dans notre exemple, la méthode setString fera l'affaire :

statement.setString(1, "Weasley");

Le premier argument est l'indice du paramètre dans la requête. Attention : pour une fois, les indices commencent à 1 !. Le second argument est la valeur que tu veux assigner au paramètre.

Tu pourras coupler ce mécanisme avec la récupération d'un paramètre de requête, avec l'annotation @RequestParam :

@GetMapping("/wizards/search")
public String getByLastName(Model model, @RequestParam String lastName) {

    try {
        Connection connection = DriverManager.getConnection(
                DB_URL, DB_USER, DB_PASSWORD
        );
        PreparedStatement statement = connection.prepareStatement(
                "SELECT * FROM wizard WHERE last_name LIKE ?;"
        );
        statement.setString(1, lastName);
        ResultSet resultSet = statement.executeQuery();

        // TODO : parse resultSet and send the list to the template 
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return "wizard_get_all";
}

Tu peux maintenant passer une valeur pour lastName dynamiquement via l'url. Par exemple : http://localhost:8080/wizards/search?lastName=Weasley.

2. Valeur par défaut

Voici une dernière astuce : tu peux décider que la valeur par défaut de lastName est "%" : si le nom n'est pas renseigné, la requête exécutée sera :

SELECT * FROM wizard WHERE last_name='%'

Ainsi, si aucun nom de sorcier n'est spécifié, la liste de tous les sorciers apparaîtra.

Il te faudra modifier la signature de la méthode de la façon suivante :

    public String getByLastName(Model model, @RequestParam(required = false, 
            defaultValue = "%") String lastName) {
    // ...
}

Repository

Jusqu'à présent, les exemples proposés par la quête ont montré comment mettre tout le code d'appel à JDBC directement dans le contrôleur. Or, le rôle d'un contrôleur est seulement de rediriger une requête HTTP vers le template correspondant.

Afin de mieux respecter le modèle MVC, il te faudra déplacer le code correspondant aux requêtes SQL dans un nouveau package : repository.

Par exemple, si tu déplaces la méthode d'appel de tous les sorciers dans une méthode findAll, contenue dans une classe WizardRepository du package repository :

public class WizardRepository {

    private final static String DB_URL = "jdbc:mysql://localhost:3306/spring_jdbc_quest?serverTimezone=GMT";
    private final static String DB_USER = "h4rryp0tt3r";
    private final static String DB_PASSWORD = "Horcrux4life!";

    public List<Wizard> findAll() {

        try {
            Connection connection = DriverManager.getConnection(
                    DB_URL, DB_USER, DB_PASSWORD
            );
            PreparedStatement statement = connection.prepareStatement(
                    "SELECT * FROM wizard;"
            );
            ResultSet resultSet = statement.executeQuery();

            List<Wizard> wizards = new ArrayList<>();

            while (resultSet.next()) {
                Long id = resultSet.getLong("id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                Date birthday = resultSet.getDate("birthday");
                String birthPlace = resultSet.getString("birth_place");
                String biography = resultSet.getString("biography");
                boolean muggle = resultSet.getBoolean("is_muggle");
                wizards.add(new Wizard(id, firstName, lastName, birthday, birthPlace, biography, muggle));
            }
            return wizards;
        } catch (
                SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

L'appel dans le contrôleur pourra se simplifier de la façon suivante :

@Controller
public class WizardController {

    private WizardRepository repository = new WizardRepository();

    @GetMapping("/wizards")
    public String getAll(Model model) {

        model.addAttribute("wizards", repository.findAll());

        return "wizard_get_all";
    }
}

Tu remarques qu'une instance de WizardRepository est initialisée en variable globale et qu'ensuite la méthode findAll est appelée dans la route correspondante.

Pas d'inquiétude si c'est un peu obscur, tu vas voir et tester ce code dans le dépôt présenté dans le challenge !

Challenge

Sélection des écoles de magie

Fais un fork du dépôt suivant : https://github.com/WildCodeSchool/quest-spring-jdbc-select.

Tu reprends le développement d'un site de fan d'Harry Potter afin d'y ajouter des fonctionnalités. En l'état, le site affiche la liste des personnages de l'univers de la série.

Tu vas devoir importer la base de données spring_jdbc_quest contenue dans le fichier suivant.

Ensuite, dans MySQL, crée l'utilisateur h4rryp0tt3r avec le mot de passe Horcrux4life! et donne-lui tous les droits possibles sur la base de données spring_jdbc_quest.

Tu vas commencer par tester le site et voir comment tout cela fonctionne.

Attention à bien regarder toutes les classes du dépôt avant de commencer le challenge !

1. Importer la base de données spring_jdbc_quest
2. Dans MySQL, créer l'utilisateur h4rryp0tt3r avec le mot de passe Horcrux4life! et tous les droits sur spring_jdbc_quest
3. Tester que le site fonctionne correctement
4. Ajouter l'affichage de toutes les écoles sur la route /schools
5. Ajouter la recherche d'école par identifiant sur la route /school?id=$id
6. Ajouter la recherche d'école par pays sur la route /school?country=$country

Critères de validation

• Le projet s'exécute bien sur la base de données et l'utilisateur requis.
• Il est possible d'afficher la liste de toutes les écoles.
• Il est possible de rechercher une école par son identifiant.
• Il est possible de rechercher une école par un pays.
• Le code JDBC est bien présent dans un repository et non un controller.
• Le code est disponible sur GitHub.