Introduction à JDBC

Haut Java

Je viens d'annoncer le nouveau cours Learn Spring , axé sur les principes de base de Spring 5 et Spring Boot 2:

>> VOIR LE COURS

1. Vue d'ensemble

Dans cet article, nous allons jeter un œil à JDBC (Java Database Connectivity) qui est une API permettant de connecter et d'exécuter des requêtes sur une base de données.

JDBC peut fonctionner avec n'importe quelle base de données tant que les pilotes appropriés sont fournis.

2. Pilotes JDBC

Un pilote JDBC est une implémentation d'API JDBC utilisée pour se connecter à un type particulier de base de données. Il existe plusieurs types de pilotes JDBC:

  • Type 1 - contient un mappage vers une autre API d'accès aux données; un exemple de ceci est le pilote JDBC-ODBC
  • Type 2 - est une implémentation qui utilise les bibliothèques côté client de la base de données cible; également appelé pilote API natif
  • Type 3 - utilise un middleware pour convertir les appels JDBC en appels spécifiques à la base de données; également appelé pilote de protocole réseau
  • Type 4 - connectez-vous directement à une base de données en convertissant les appels JDBC en appels spécifiques à la base de données; connus sous le nom de pilotes de protocole de base de données ou de pilotes légers,

Le type le plus couramment utilisé est le type 4, car il présente l'avantage d'être indépendant de la plate-forme . La connexion directe à un serveur de base de données offre de meilleures performances par rapport aux autres types. L'inconvénient de ce type de pilote est qu'il est spécifique à la base de données - étant donné que chaque base de données a son propre protocole spécifique.

3. Connexion à une base de données

Pour se connecter à une base de données, il suffit d'initialiser le pilote et d'ouvrir une connexion à la base de données.

3.1. Enregistrement du pilote

Pour notre exemple, nous utiliserons un pilote de protocole de base de données de type 4.

Puisque nous utilisons une base de données MySQL, nous avons besoin de la dépendance mysql-connector-java :

 mysql mysql-connector-java 6.0.6 

Ensuite, enregistrons le pilote à l'aide de la méthode Class.forName () , qui charge dynamiquement la classe de pilote:

Class.forName("com.mysql.cj.jdbc.Driver");

Dans les anciennes versions de JDBC, avant d'obtenir une connexion, nous devions d'abord initialiser le pilote JDBC en appelant la méthode Class.forName . À partir de JDBC 4.0, tous les pilotes trouvés dans le chemin de classe sont automatiquement chargés . Par conséquent, nous n'aurons pas besoin de cette partie Class.forName dans les environnements modernes.

3.2. Créer la connexion

Pour ouvrir une connexion, nous pouvons utiliser la méthode getConnection () de la classe DriverManager . Cette méthode nécessite un paramètre de chaîne d' URL de connexion :

try (Connection con = DriverManager .getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass")) { // use con here }

Puisque la connexion est une ressource à fermeture automatique , nous devons l'utiliser dans un bloc try-with-resources .

La syntaxe de l'URL de connexion dépend du type de base de données utilisé. Jetons un coup d'œil à quelques exemples:

jdbc:mysql://localhost:3306/myDb?user=user1&password=pass
jdbc:postgresql://localhost/myDb
jdbc:hsqldb:mem:myDb

Pour se connecter à la base de données myDb spécifiée , nous devrons créer la base de données et un utilisateur, et ajouter les droits d'accès nécessaires:

CREATE DATABASE myDb; CREATE USER 'user1' IDENTIFIED BY 'pass'; GRANT ALL on myDb.* TO 'user1';

4. Exécution d'instructions SQL

Pour envoyer les instructions SQL à la base de données, nous pouvons utiliser des instances de type Statement , PreparedStatement ou CallableStatement, que nous pouvons obtenir à l'aide de l' objet Connection .

4.1. Déclaration

L' interface Statement contient les fonctions essentielles pour l'exécution des commandes SQL.

Commençons par créer un objet Statement :

try (Statement stmt = con.createStatement()) { // use stmt here }

Encore une fois, nous devrions travailler avec Statement dans un bloc try-with-resources pour la gestion automatique des ressources.

Quoi qu'il en soit, l'exécution des instructions SQL peut se faire en utilisant trois méthodes:

  • executeQuery () pour les instructions SELECT
  • executeUpdate () pour mettre à jour les données ou la structure de la base de données
  • execute () peut être utilisé dans les deux cas ci-dessus lorsque le résultat est inconnu

Utilisons la méthode execute () pour ajouter une table des étudiants à notre base de données:

String tableSql = "CREATE TABLE IF NOT EXISTS employees" + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30)," + "position varchar(30), salary double)"; stmt.execute(tableSql);

Lorsque vous utilisez la méthode execute () pour mettre à jour les données, la méthode stmt.getUpdateCount () renvoie le nombre de lignes affectées.

Si le résultat est 0, aucune ligne n'a été affectée ou il s'agissait d'une commande de mise à jour de la structure de la base de données.

Si la valeur est -1, alors la commande était une requête SELECT; nous pouvons ensuite obtenir le résultat en utilisant stmt.getResultSet () .

Ensuite, ajoutons un enregistrement à notre table en utilisant la méthode executeUpdate () :

String insertSql = "INSERT INTO employees(name, position, salary)" + " VALUES('john', 'developer', 2000)"; stmt.executeUpdate(insertSql);

The method returns the number of affected rows for a command that updates rows or 0 for a command that updates the database structure.

We can retrieve the records from the table using the executeQuery() method which returns an object of type ResultSet:

String selectSql = "SELECT * FROM employees"; try (ResultSet resultSet = stmt.executeQuery(selectSql)) { // use resultSet here }

We should make sure to close the ResultSet instances after use. Otherwise, we may keep the underlying cursor open for a much longer period than expected. To do that, it's recommended to use a try-with-resources block, as in our example above.

4.2. PreparedStatement

PreparedStatement objects contain precompiled SQL sequences. They can have one or more parameters denoted by a question mark.

Let's create a PreparedStatement which updates records in the employees table based on given parameters:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?"; try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) { // use pstmt here }

To add parameters to the PreparedStatement, we can use simple setters – setX() – where X is the type of the parameter, and the method arguments are the order and value of the parameter:

pstmt.setString(1, "lead developer"); pstmt.setInt(2, 1);

The statement is executed with one of the same three methods described before: executeQuery(), executeUpdate(), execute() without the SQL String parameter:

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

The CallableStatement interface allows calling stored procedures.

To create a CallableStatement object, we can use the prepareCall() method of Connection:

String preparedSql = "{call insertEmployee(?,?,?,?)}"; try (CallableStatement cstmt = con.prepareCall(preparedSql)) { // use cstmt here }

Setting input parameter values for the stored procedure is done like in the PreparedStatement interface, using setX() methods:

cstmt.setString(2, "ana"); cstmt.setString(3, "tester"); cstmt.setDouble(4, 2000);

If the stored procedure has output parameters, we need to add them using the registerOutParameter() method:

cstmt.registerOutParameter(1, Types.INTEGER);

Then let's execute the statement and retrieve the returned value using a corresponding getX() method:

cstmt.execute(); int new_id = cstmt.getInt(1);

For example to work, we need to create the stored procedure in our MySql database:

delimiter // CREATE PROCEDURE insertEmployee(OUT emp_id int, IN emp_name varchar(30), IN position varchar(30), IN salary double) BEGIN INSERT INTO employees(name, position,salary) VALUES (emp_name,position,salary); SET emp_id = LAST_INSERT_ID(); END // delimiter ;

The insertEmployee procedure above will insert a new record into the employees table using the given parameters and return the id of the new record in the emp_id out parameter.

To be able to run a stored procedure from Java, the connection user needs to have access to the stored procedure's metadata. This can be achieved by granting rights to the user on all stored procedures in all databases:

GRANT ALL ON mysql.proc TO 'user1';

Alternatively, we can open the connection with the property noAccessToProcedureBodies set to true:

con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/myDb?noAccessToProcedureBodies=true", "user1", "pass");

This will inform the JDBC API that the user does not have the rights to read the procedure metadata so that it will create all parameters as INOUT String parameters.

5. Parsing Query Results

After executing a query, the result is represented by a ResultSet object, which has a structure similar to a table, with lines and columns.

5.1. ResultSet Interface

The ResultSet uses the next() method to move to the next line.

Let's first create an Employee class to store our retrieved records:

public class Employee { private int id; private String name; private String position; private double salary; // standard constructor, getters, setters }

Next, let's traverse the ResultSet and create an Employee object for each record:

String selectSql = "SELECT * FROM employees"; try (ResultSet resultSet = stmt.executeQuery(selectSql)) { List employees = new ArrayList(); while (resultSet.next()) { Employee emp = new Employee(); emp.setId(resultSet.getInt("emp_id")); emp.setName(resultSet.getString("name")); emp.setPosition(resultSet.getString("position")); emp.setSalary(resultSet.getDouble("salary")); employees.add(emp); } }

Retrieving the value for each table cell can be done using methods of type getX() where X represents the type of the cell data.

The getX() methods can be used with an int parameter representing the order of the cell, or a String parameter representing the name of the column. The latter option is preferable in case we change the order of the columns in the query.

5.2. Updatable ResultSet

Implicitly, a ResultSet object can only be traversed forward and cannot be modified.

If we want to use the ResultSet to update data and traverse it in both directions, we need to create the Statement object with additional parameters:

stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );

To navigate this type of ResultSet, we can use one of the methods:

  • first(), last(), beforeFirst(), beforeLast() – to move to the first or last line of a ResultSet or to the line before these
  • next(), previous() – to navigate forward and backward in the ResultSet
  • getRow() – to obtain the current row number
  • moveToInsertRow(), moveToCurrentRow() – to move to a new empty row to insert and back to the current one if on a new row
  • absolute(int row) – to move to the specified row
  • relative(int nrRows) – to move the cursor the given number of rows

Updating the ResultSet can be done using methods with the format updateX() where X is the type of cell data. These methods only update the ResultSet object and not the database tables.

To persist the ResultSet changes to the database, we must further use one of the methods:

  • updateRow() – to persist the changes to the current row to the database
  • insertRow(), deleteRow() – to add a new row or delete the current one from the database
  • refreshRow() – to refresh the ResultSet with any changes in the database
  • cancelRowUpdates() – to cancel changes made to the current row

Let's take a look at an example of using some of these methods by updating data in the employee's table:

try (Statement updatableStmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) { try (ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql)) { updatableResultSet.moveToInsertRow(); updatableResultSet.updateString("name", "mark"); updatableResultSet.updateString("position", "analyst"); updatableResultSet.updateDouble("salary", 2000); updatableResultSet.insertRow(); } }

6. Parsing Metadata

The JDBC API allows looking up information about the database, called metadata.

6.1. DatabaseMetadata

The DatabaseMetadata interface can be used to obtain general information about the database such as the tables, stored procedures, or SQL dialect.

Let's have a quick look at how we can retrieve information on the database tables:

DatabaseMetaData dbmd = con.getMetaData(); ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null); while (tablesResultSet.next()) { LOG.info(tablesResultSet.getString("TABLE_NAME")); }

6.2. ResultSetMetadata

This interface can be used to find information about a certain ResultSet, such as the number and name of its columns:

ResultSetMetaData rsmd = rs.getMetaData(); int nrColumns = rsmd.getColumnCount(); IntStream.range(1, nrColumns).forEach(i -> { try { LOG.info(rsmd.getColumnName(i)); } catch (SQLException e) { e.printStackTrace(); } });

7. Handling Transactions

By default, each SQL statement is committed right after it is completed. However, it's also possible to control transactions programmatically.

This may be necessary in cases when we want to preserve data consistency, for example when we only want to commit a transaction if a previous one has completed successfully.

First, we need to set the autoCommit property of Connection to false, then use the commit() and rollback() methods to control the transaction.

Let's add a second update statement for the salary column after the employee position column update and wrap them both in a transaction. This way, the salary will be updated only if the position was successfully updated:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?"; PreparedStatement pstmt = con.prepareStatement(updatePositionSql); pstmt.setString(1, "lead developer"); pstmt.setInt(2, 1); String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?"; PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql); pstmt.setDouble(1, 3000); pstmt.setInt(2, 1); boolean autoCommit = con.getAutoCommit(); try { con.setAutoCommit(false); pstmt.executeUpdate(); pstmt2.executeUpdate(); con.commit(); } catch (SQLException exc) { con.rollback(); } finally { con.setAutoCommit(autoCommit); }

For the sake of brevity, we omit the try-with-resources blocks here.

8. Closing the Resources

When we're no longer using it, we need to close the connection to release database resources.

We can do this using the close() API:

con.close();

Cependant, si nous utilisons la ressource dans un bloc try-with-resources , nous n'avons pas besoin d'appeler la méthode close () explicitement, car le bloc try-with-resources le fait automatiquement pour nous.

Il en va de même pour les Statement s, PreparedStatement s, CallableStatement s et ResultSet s.

9. Conclusion

Dans ce didacticiel, nous avons examiné les bases de l'utilisation de l'API JDBC.

Comme toujours, le code source complet des exemples est disponible à l'adresse over sur GitHub.

Fond Java

Je viens d'annoncer le nouveau cours Learn Spring , axé sur les principes de base de Spring 5 et Spring Boot 2:

>> VOIR LE COURS