Java JDBC introduction
Introduction
Java Database Connectivity (JDBC, l'API Java pour se connecter à une base de données) est une API (Application Programming Interface, l'interface de programmation qui permet à des logiciels de communiquer entre eux) Java standard qui te permet de te connecter à la plupart des bases de données en utilisant les mêmes classes et méthodes. Une fois que tu as appris comment JDBC fonctionne, tu peux utiliser JDBC et SQL avec n'importe quelle base de données supportée.
Sans JDBC, tu devrais utiliser directement les API des bases de données. Chaque base de données a sa propre API et certaines bases de données n'ont même pas de support Java pour leur API native. Changer de base de données est très difficile et demande beaucoup de modifications sans JDBC. Avec JDBC, c'est plutôt simple de changer de base de données. Parfois, tu n'as qu'à changer le driver (pilote, le logiciel qui permet à Java de parler à une base de données précise) de la base de données.
🤓 À la fin de cette quête, tu vas :
✅ Comprendre l'architecture et le but de JDBC ✅ Connaître les interfaces et classes de JDBC et savoir comment les utiliser ✅ Avoir utilisé JDBC pour accéder à une base de données et effectuer des opérations CRUD (Create, Read, Update, Delete, soit créer, lire, mettre à jour et supprimer des données)
Une base de données est un logiciel qui prend en charge. Pour te connecter à une base de données précise avec JDBC, tu auras toujours besoin d'un driver pour la base de données que tu veux utiliser.
Voici quelques logiciels de bases de données populaires :
- Oracle
- Microsoft SQL Server
- MySQL
- MariaDB
- PostgreSQL
- Apache Derby
Toutes ces bases de données supportent le Structured Query Language (SQL, le langage standard pour travailler avec les bases de données relationnelles). Cette quête ne porte pas sur SQL et on attend de toi que tu saches ce qu'est SQL et comment l'utiliser pour cette quête.
MariaDB est une version gratuite de MySQL qui est apparue quand MySQL a été racheté par Oracle. MySQL et MariaDB sont très populaires pour les applications web. Elle est aussi disponible avec la plupart des distributions Linux directement via le gestionnaire de paquets.
Apache Derby est une base de données qui était distribuée avec le JDK (Java Development Kit, le kit de développement Java) de Java 8, ce qui la rendait très simple à utiliser avec Java. Malheureusement, Apache Derby ne fait plus partie des distributions récentes du JDK.
Chaque base de données nécessite un driver différent et la technologie des drivers a évolué au fil du temps. Pour cette raison, les drivers de base de données JDBC sont classés par type :
Type 1
Un driver de type 1 dépend de code natif et nécessite la technologie ODBC (Open Database Connectivity, une ancienne technologie de connexion aux bases de données). Les drivers de type 1 ne devraient plus être utilisés car cette technologie est dépassée et remplacée par les types de drivers JDBC plus récents.
Type 2
Un driver de type 2 dépend de code natif et nécessite des drivers de base de données natifs spécifiques à la base de données. À cause de la dépendance au code natif, tu devrais éviter ce genre de driver pour garder ton programme Java portable.
Type 3
Avec les drivers de type 3, le code du driver est écrit en pur Java mais dépend encore d'un middleware (logiciel intermédiaire, une couche logicielle qui fait le lien entre deux systèmes). Ce middleware peut nécessiter des composants natifs.
Type 4
Les drivers de type 4 sont des drivers en pur Java qui utilisent le protocole natif de la base de données. Tu devrais choisir un driver de type 4 chaque fois que c'est possible.
Pour résumer ce dont tu as besoin :
- le logiciel de base de données
- le driver de la base de données
Pour un driver de type 4 et la plupart des bases de données, tu peux t'attendre à ce que le driver soit disponible depuis le dépôt Maven (un outil Java qui gère les bibliothèques externes et la construction d'un projet) Central.
La base de données
Pour cette quête, on va utiliser une table persons que tu peux créer avec le SQL suivant :
CREATE TABLE persons ( firstname VARCHAR(30), lastname VARCHAR(30), age int );
Pour insérer quelques enregistrements, tu peux utiliser l'outil SQL interactif de ta base de données et les instructions SQL suivantes :
INSERT INTO persons ( firstname, lastname, age ) VALUES ( 'John', 'Smith', 31);
INSERT INTO persons ( firstname, lastname, age ) VALUES ( 'Sarah', 'Connor', 29 );
Firstname | Lastname | Age
------------+------------+-----
John | Smith | 31
Sarah | Connor | 29
N'hésite pas à ajouter plus d'enregistrements ou de colonnes avec des données supplémentaires si tu es déjà à l'aise avec SQL, mais ne laisse pas SQL t'empêcher de terminer cette quête.
Les interfaces et les classes
Pour travailler avec une base de données en utilisant JDBC, tu dois connaître les interfaces nécessaires. Ne t'inquiète pas, il n'y en a que quelques-unes et la plupart du temps tu ne les utiliseras même pas toutes.
Chaque driver de base de données fournit une implémentation spécifique à la base de données pour ces interfaces. Utilise uniquement les interfaces fournies dans le package java.sql pour garder ton application indépendante de toute base de données précise.
La classe driver
Tu dois charger le driver pour travailler avec une base de données mais après le chargement. Une fois chargé, tu n'utiliseras que les classes JDBC communes. Pour charger la classe driver, tu dois :
- rendre la classe driver disponible dans le classpath (le chemin où Java va chercher les classes à charger)
- faire charger la classe par le runtime Java (l'environnement d'exécution, le programme qui fait tourner ton code) avec un peu de code
Le runtime Java ne chargera le driver de base de données que sur demande. Dans une certaine mesure, c'est le même processus qu'avec n'importe quelle autre classe Java, mais comme le runtime Java ne sait pas quelle classe (ou base de données) il doit utiliser, tu dois explicitement indiquer au runtime Java le nom réel de la classe du driver.
Driver driver = Class.forName("...");
Fournis le nom canonique réel de la classe du driver comme argument de la méthode forName. La documentation de la base de données pour son driver JDBC devrait préciser le nom de la classe du driver.
La Connection
La connection fournit un moyen de connecter ton application à la base de données. Cette connection est utilisée pour tout le reste, mais une fois connecté, tout ce que tu as à retenir, c'est de te déconnecter dès que tu n'as plus besoin d'accéder à la base de données.
Se déconnecter de la base de données est une marque de politesse. Même si ce n'est pas strictement obligatoire, il vaut mieux ne pas laisser traîner des connexions ouvertes à la base de données car elles pourraient impacter les performances du système ou devenir un problème de sécurité.
Le Statement
L'interface Statement sert à envoyer des instructions SQL à traiter sur la base de données. Tu devras comprendre SQL pour fournir l'instruction réelle.
SELECT * FROM persons;
Le ResultSet
Certaines instructions SQL auront un résultat. Le ResultSet est comme l'interface List qui donne accès aux enregistrements issus du résultat de l'instruction SQL :
Firstnameb | Lastname | Age
------------+-------------+-----
John | Smith | 31
Sarah | Connor | 29
... | ... | ...
Le PreparedStatement
Le PreparedStatement est comparable à l'interface Statement. Tu utilises des instructions SQL à traiter sur la base de données. Quand tu dois répéter la même instruction mais en fournissant des données d'entrée différentes, utiliser un prepared statement sera plus rapide car l'instruction SQL n'a pas besoin d'être analysée à nouveau.
Le ResultSetMetaData
Avec le ResultSetMetaData, tu peux lire des informations de type plus détaillées sur les données d'un ResultSet. Par exemple, tu peux utiliser le ResultSetMetaData pour vérifier le type de données des colonnes.
Le DatabaseMetaData
Avec le DatabaseMetaData, tu peux lire des informations plus détaillées sur la base de données. Par exemple, tu peux utiliser le DatabaseMetaData pour lister toutes les tables disponibles dans la base de données.
Travailler avec JDBC
Charger le driver de base de données
La première étape est toujours de charger le driver. Ça ne doit être fait qu'une seule fois.
final String DRIVER = "...";
Class.forName(DRIVER);
Se connecter à la base de données
La deuxième étape est de se connecter à la base de données. Tu peux ouvrir et fermer les connexions comme tu veux, mais c'est une bonne pratique de garder la connexion une fois qu'elle est établie et de la réutiliser pour les opérations suivantes.
final String URL = "...";
Connection connection = DriverManager.getConnection(URL);
Exécuter une requête
La troisième étape est d'exécuter une requête. Tu exécuteras à plusieurs reprises différentes requêtes pour récupérer et modifier les données dans la base de données.
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery( "select count(*) from persons");
Une fois qu'une requête SELECT est exécutée, tu obtiens un résultat de la base de données. Ce que tu fais avec les données dépend de ton application. Dans l'extrait de code Java suivant, les données sont simplement affichées sur System.out.
Traiter le ResultSet
while(resultSet.next) {
System.out.println(resultSet.getString(1)); // Firstname
System.out.println(resultSet.getString(2)); // Lastname
System.out.println(resultSet.getInteger(3)); // Age
}
Nettoyage
Une bonne règle pour les développeurs de logiciels est la règle du scout : Ne laisse aucune trace. Fermer le resultSet, le statement et enfin la connection est une façon de jeter tes déchets correctement. Tu ne peux pas et tu ne devrais pas compter sur le garbage collector (le ramasse-miettes, le mécanisme qui libère automatiquement la mémoire inutilisée en Java) pour le faire à ta place.
resultSet.close();
statement.close();
connection.close();
Un bug dans un système logiciel a un jour mené à des connexions non fermées. À cause de restrictions de licence, le nombre de connexions à la base de données était limité dans ce cas. Une fois toutes les connexions utilisées sans être correctement libérées, il n'était plus possible de se connecter à la base de données. Un redémarrage a été nécessaire pour relancer la base de données et se débarrasser de toutes les connexions oubliées.
Se préparer à l'injection SQL
Par le passé, certaines personnes utilisaient la concaténation de chaînes pour construire la chaîne de commande SQL du statement. Ça peut mener à un grave problème de sécurité : l'injection SQL (SQL injection, une attaque où un utilisateur insère du code SQL malveillant via les données saisies). Pour éviter l'injection SQL, tu devrais utiliser le PreparedStatement.
L'injection SQL est une faille bien connue et pourtant, elle reste un problème de sécurité majeur dans plusieurs applications qui ont été analysées par différentes organisations. En tant que développeur de logiciels professionnel, tu dois être conscient du risque et savoir comment l'atténuer. Ne tombe pas dans le piège des naïfs.
La cause première de l'injection SQL vient de la concaténation de String, comme dans l'extrait de code Java suivant :
statement.executeSQL( "INSERT INTO persons ('" + firstname + "', '" + lastname + "', " + age + "" );
Avec ce code, utiliser une mauvaise valeur pour l'une des variables firstname, lastname ou age pourrait servir à injecter une instruction SQL malveillante qui serait exécutée dans le cadre du statement.
Fais connaissance avec Bobby Tables
https://xkcd.com/327/
Pour éviter l'injection SQL, l'exemple devrait être réécrit en quelque chose comme :
preparedStatement = connection.prepareStatement( "INSERT INTO persons ( VORNAME, NACHNAME, AGE) VALUES ( ?, ?, ? )");
preparedStatement.setString(1, "Paul" );
preparedStatement.setString(2, "Paulsen" );
preparedStatement.setInt(3, 42 );
// ...
preparedStatement.execute();
Dans cet exemple, les variables firstname, lastname et age ne sont pas concaténées à une chaîne. Les variables sont passées à la méthode ..., qui les traite uniquement comme des données. Les données ne sont jamais utilisées pour construire la commande SQL réelle. Comme les variables ne sont que des données, elles ne sont jamais exécutées, ce qui évite l'injection SQL.
RecordSetMetaData et DatabaseMetadata
Les **RecordSetMetaData et DatabaseMetadata fournissent un accès en lecture seule avec des détails sur les résultats de requêtes (RecordSetMetaData) et sur les objets de la base de données comme les tables (DatabaseMetadata).
Référence
☝️ Résumé
Dans cette quête, tu as appris les drivers de base de données ainsi que les classes et interfaces que JDBC fournit pour travailler avec les bases de données, et tu as créé un petit programme qui utilise JDBC pour créer, lire, mettre à jour et supprimer des données dans la base de données.
💪Challenge
Recherche les classes de driver réelles pour au moins trois bases de données. Au moins une de ces bases de données devrait être MySQL ou MariaDB. Les deux autres bases de données sont à toi de choisir. Indique le nom de la classe du driver comme une constante String dans ton code Java pour cette quête.
Crée une application Java qui utilise JDBC pour insérer de nouveaux enregistrements dans la table persons, met à jour au moins un enregistrement dans la table persons, supprime au moins deux enregistrements dans la table persons et lit toutes les lignes de la table persons.
Points à surveiller :
- N'oublie pas de fermer les statements, resultsets et connections
- Évite le code qui peut servir à des attaques par injection SQL
Si tu as été rapide à coder les opérations CRUD, essaie d'améliorer l'affichage des enregistrements de la table persons et de fournir des informations sur le type de données de chaque colonne
🧐 Critères d'acceptation
- ✔ Tu as écrit le code pour te connecter à une base de données avec JDBC
- ✔ Tu as écrit le code pour insérer de nouveaux enregistrements dans la table persons
- ✔ Tu as écrit le code pour récupérer tous les enregistrements de la table persons
- ✔ Tu as écrit le code pour mettre à jour le lastname d'un enregistrement dans la table persons
- ✔ Tu as écrit le code pour supprimer deux enregistrements de la table persons
- ✔ Tu as tenté d'écrire quelques instructions d'injection SQL malveillantes
- ✔ Ton code est disponible sur Github/Gitlab
Introduction
Java DataBase Connectivity (JDBC) is a standard JAVA API that allows you to connect to most databases using the same classes and methods. Once you learned how JDBC works, you can use JDBC and SQL with any supported database.
Without JDBC, you would have to use the databases APIs directly. Every database has it's own API and some databases do not even have Java support for their native API. Changing the database is very hard and requires many changes without JDBC. With JDBC, it is rather simple to change the database. Sometimes, you only have to change the database driver.
🤓 By the end of this quest, you will:
✅ Understand the architecture and purpose of JDBC ✅ Know the JDBC interfaces and classes and how to use them ✅ Have used JDBC to access a database and perform CRUD operations
A database is a software that supports. To connect with a specific database using JDBC, you will always need a driver for the database you want to use.
Some popular database software is
- Oracle
- Microsoft SQL Server
- MySQL
- MariaDB
- PostgreSQL
- Apache Derby
All these database support the Structure Query Language (SQL), which is a standard language to work with relational databases. This Quest is not about SQL and you are expected to know what SQL is and how to work with it for this Quest.
MariaDB is a free version of MySQ that emerged when MySQL was acquired by Oracle. MySQL and MariaDB are very popular with web applications. It also available with most Linux distributions directly with the package manager.
Apache Derby is a database that was distributed with the Java 8 JDK which made it very simple to use it with Java. Unfortunately, Apache Derby is no longer part of the newer JDK distributions.
Each database requires a different driver and driver technology evolved over time. For this reason, JDBC database drivers are classified by type
Type 1
A type 1 driver depends on native code and requires ODBC technology. Type 1 drivers should not be used anymore as this technology is outdated and superseded by later JDBC driver types
Type 2
A type 2 driver depends on native code and requires database specific native code database drivers. Due to the dependency on native code, you should avoid this kind of driver to keep your Java program portable.
Type 3
With type 3 drivers, the driver code is written in pure Java that still depends on some middleware. This middleware may require native components.
Type 4
The type 4 drivers are pure Java drivers that use the databases native protocol. You should choose a type 4 driver whenever possible.
Too summarize the requirements, you need
- the database software
- the database driver
For a type 4 driver and most databases, you can expect the driver to be available from the Maven Central maven repository.
The database
For this Quest, we will use a persons table that you can create with the SQL
CREATE TABLE persons ( firstname VARCHAR(30), lastname VARCHAR(30), age int );
To insert some records, you can use the interactive SQL tool from your database and the following SQL statements
INSERT INTO persons ( firstname, lastname, age ) VALUES ( 'John', 'Smith', 31);
INSERT INTO persons ( firstname, lastname, age ) VALUES ( 'Sarah', 'Connor', 29 );
Firstname | Lastname | Age
------------+------------+-----
John | Smith | 31
Sarah | Connor | 29
Feel free to add more records or columns with additonal data if you are well with SQL already, but do not let SQL get in your way to complete this Quest.
The interfaces and classes
To work with a database using JDBC, you have to know about the required interfaces.Don't worry, there are just some of them and most of the time you will not even use all of them.
Every database driver provides a database specific implementation for these interfaces. Use only the interfaces as provides in the java.sql package to keep your application independent from any specific database.
The driver class
You need to load the driver to work with a database but after loading. Once loaded, you will only use the common JDBC classes. to load the driver class, you have to
- make the driver class available in the classpath
- make the Java runtime load the class with some code
The Java runtime will only load the database driver on request. To a certain degree, this is the same process as it is with any other Java class but since the Java runtime does not know which class (or database) it should use, you have to explicitly tell the Java runtime the actual class name for the driver.
Driver driver = Class.forName("...");
Provide the actual canoncical class name for the driver as an argument to the forName method. The database documentation for the databases JDBC driver should specify the class name of the driver class.
The Connection
The connection provides a way to connect your application with the database. This connection is used for everything else but once connected, all you have to remember is to disconnect once you do not need to access the database any longer.
Disconnecting from the database is a sign of good manors. While not strictly required, it is better not to leave open database connections hang around as these could impact the performance of the system or become a security issue.
The Statement
The Statement interface is used to issue SQL statements to be processed on the database. You will need to understand SQL to provide the actual statement.
SELECT * FROM persons;
The ResultSet
Some SQL statements will have a result. The ResultSet is like the List interface that provides access to the records from the result of the SQL statement
Firstnameb | Lastname | Age
------------+-------------+-----
John | Smith | 31
Sarah | Connor | 29
... | ... | ...
The PreparedStatement
The PreparedStatement is comparable to the Statement interface. You use SQL statements to be processed on the database. When you have to repeat the same statement but provide different input data, using a prepared statement will be faster because the SQL statement does not have to be parsed again.
The ResultSetMetaData
Using the ResultSetMetaData, you can read more detailed type information about the data in a ResultSet. For example, you can use the ResultSetMetaData to check the data type for the columns
The DatabaseMetaData
Using the DatabaseMetaData, you can read more detailed informations about the database. For example, you can use the DatabaseMetaData to list all available tables in the database.
Working with JDBC
Load the database driver
The first step is always loading the driver. This has to be done only once.
final String DRIVER = "...";
Class.forName(DRIVER);
Connect with the database
The second step is to connect to the database. You can open and close connections as you like but it is good practice to keep the connection once it is established and reuse it for following operations.
final String URL = "...";
Connection connection = DriverManager.getConnection(URL);
Execute a Query
The third step is to execute a query. You will repeatedly execute different queries to retrieve and modify data in the database.
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery( "select count(*) from persons");
Once a SELECT query is executed, you will get a result from the database. It depends on your application what to do with the data. In the following Java code snippet, the data is simply printed on System.out.
Process the ResultSet
while(resultSet.next) {
System.out.println(resultSet.getString(1)); // Firstname
System.out.println(resultSet.getString(2)); // Lastname
System.out.println(resultSet.getInteger(3)); // Age
}
Cleanup
A good rule for software developers is the Scout rule: Leave no trace. Closing the resultSet, statement and finally the connection is a way to dispose waste properly. You can and should not rely on the garbage collector to do this for you.
resultSet.close();
statement.close();
connection.close();
A bug in a software system once led to unclosed connections. Due to licensing restrictions, the number of connections to the database was limited in this case. Once all connections where used without proper disposale, it was not possible to connect with the database anymore. A reboot was required to restart the database and get rid of all the forgotten connections.
Prepare for SQL injection
In the past, some people used string concatenation to build the SQL command string for the statement. This can lead to a serious security issue: SQL injection. To avoid SQL injection, you should use the PreparedStatement.
SQL injection is a well know flaw and yet, it is a top security issue with several applications that have been analysed by different organizations. As a professional software developer, you have to be aware of the risk and how to mitigate it. Don't fall into the fools trap.
The root cause for the SQL injection comes with String concatentation as in the following Java code snippet.
statement.executeSQL( "INSERT INTO persons ('" + firstname + "', '" + lastname + "', " + age + "" );
With this code, using a bad value for one of the variables firstname, lastname or age could be used to inject a bad SQL statement that would be executed as part of the statement.
Get to know Bobby Tables
https://xkcd.com/327/
To avoid SQL injection, the example should be refactored to something like
preparedStatement = connection.prepareStatement( "INSERT INTO persons ( VORNAME, NACHNAME, AGE) VALUES ( ?, ?, ? )");
preparedStatement.setString(1, "Paul" );
preparedStatement.setString(2, "Paulsen" );
preparedStatement.setInt(3, 42 );
// ...
preparedStatement.execute();
In this example, the variables firstname, lastname and age are not concatenated to a string. The variables are passed in to the ... method, which treats them as data only. The data is never used to build the actual SQL command. Since the variables are only data, they are never executed which avoids SQL injection.
RecordSetMetaData and DatabaseMetadata
The **RecordSetMetaData and DatabaseMetadata provide read only access with details about query results (RecordSetMetaData) and database objects like tables (DatabaseMetadata).
Reference
☝️ Summary
In this Quest, you learned about database drivers and the classes and interfaces JDBC provides to work with databases and create a small program that uses JDBC to create, read, update and delete data in the database.
💪Challenge
Look up the actual driver classes for at least three databases. At least one of these databases should be MySQL or MariaDB. The other two databases are up to you. Specify the driver class name as a constant String in your Java code for this Quest.
Create a Java application that uses JDBC to insert new records into the persons table, updates at least one record in the persons table, deletes at least two records in the persons table and reads all rows from the persons database table.
Things to look for:
- Remember to close statements, resultsets and connections
- Avoid code that can be used for SQL injection attacks
When you were quick to code the CRUD operations, try to enhance the output for the records from the persons table and provide information about the data type of each column
🧐 Acceptance criteria
- ✔ You wrote the code to connect to a database with JDBC
- ✔ You wrote the code to insert new records in the persons table
- ✔ You wrote the code to retrieve all records from the persons table
- ✔ You wrote the code to update the lastname of one record in the persons table
- ✔ You wrote the code to delete two record from the persons table
- ✔ You attempted to write some bad SQL injection statements
- ✔ Your code is available on Github/Gitlab