Tutoriel pour apprendre à gérer simplement les CLOB et BLOB avec Spring JDBC

Pour les pressés, voici un résumé succinct de cet article.

• Pour la création ou la mise à jour des champs xLob, utilisez conjointement jdbcTempalte.execute, LobCreator.
• Pour lire les champs xLob, utilisez conjointement jdbcTemplate.query et LobHandler de Spring.
• Une des clés pour mieux comprendre l'article est de saisir l'impact des propriétés wrapAsLob et StreamAsLob.

Dans la démonstration ci-après, nous allons créer une table mail ayant une colonne xLOB nommée corps.

La démonstration est accompagnée d'un test JUnit permettant de réaliser les opérations standards de type CRUD :

• insérer une ligne avec un champ xLob ;
• mettre à jour le champ xLob ;
• lire le champ xLob,

Nous voulons que la lecture de xLob renvoie une chaîne facilement utilisable par d'autres couches applicatives. Enfin, merci de noter que Spring 4, Java 8 et le connecteur Postgres (v 9.4. 1207.jre7) compatible Jdbc4.0 sont utilisés.

La démonstration, en Java 8, se divise en deux actes.

• ACTE I. SQL : créer rapidement une table nommée EMAIL ayant les colonnes (id, corps, idRegion). Le champ corps est de type TEXT (clob de postgres).
• Écrire un service simple s'appuyant sur jdbcTemplate de Spring pour les opérations CRUD. Écrire un test d'intégration pour tester ces opérations.

Passons à l'action en mettant la main à la pâte à la sauce de spring jdbc et vous allez constater que c'est comme utiliser un ORM [presque !].

Le contenu du pom.xml se réduit à ces quelques lignes avec spring-boot :

.....
<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <java.version>1.8</java.version>
</properties>

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>    
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
</dependencies>
<build>
  <plugins>
    <plugin>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-maven-plugin</artifactId>
    </plugin>
  </plugins>
</build>    
</project>

Rien de particulier ici : la seule chose à noter est la présence de deux dépendances et du plugin spring-boot. Les dépendances sont :

• le starter spring-boot-starter-jdbc ;
• le connecteur jdbc postgres compatible jdbc 4.

Le starter regroupe toutes les dépendances nécessaires pour utiliser spring-jdbc.

Une seule classe nommée Email.

public class Email implements Serializable {
  private long id;
  private String idRegion; 
  private String corps;
    
... getters setters omis
}

Commençons par l'interface, IService, de notre service.

import java.util.List;

public interface IService {
  boolean mailExistById(long id);

  void insertXlob(String fullPathFileClob, long idMail, String idRegion) throws Exception;
  void updateXlob(String fullPathFileClob, long idMail) throws Exception;

  List<Email> getCorpsEmailById(long idEmail);
  List<Email> getCorpsEmailByRegion(String idRegion);
}

Nous observons :

• une méthode mailExistById permet de savoir si un mail existe par son id ;
• la présence de deux méthodes CRUD : insertXlob et updateXlob ;
• deux méthodes de lecture/récupération de xLob nommées getCorpsXXXX.

La classe ServiceImpl.java d'implémentation est réalisée avec la fameuse jdbcTemplate de Spring.

Nous détaillons l'implémentation de la classe ServiceImpl en procédant méthode par méthode. Vous trouverez le contenu ZIP du projet en fin de l'article.

Voici déjà l'entête de la classe ServiceImpl.java avec les imports nécessaires :

import static fr.abdou.Utils.*;
import java.io.*;
import java.sql.*;
import java.util.*;
import o.s.beans.factory.annotation.Autowired;
import o.s.dao.DataAccessException;
import o.s.jdbc.core.JdbcTemplate;
import o.s.jdbc.core.RowMapper;
import o.s.jdbc.support.lob.LobHandler;
import o.s.jdbc.support.lob.LobCreator;
import o.s.stereotype.Service;
import org.slf4j.*;
import o.s.transaction.annotation.Transactional;
@Service("service")
@Transactional
public class ServiceImpl implements IService {
  final static Logger logger = LoggerFactory.getLogger(ServiceImpl.class);
   
  private JdbcTemplate jdbcTemplate;
	
  @Autowired
  public void setDataSource(DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
  }

  @Autowired @Qualifier("myDefaultLobHandler")
  private LobHandler lobHandler;
  ...methodes a venir...

La première ligne import static fr.abdou.Utils.* sert à importer diverses constantes (par exemple, SQL) utiles à notre démonstration. L'import o.s correspond à org.springframework, o.s.j.c désigne org.springframework.jdbc.core et o.s.j.s pour org.springframework.jdbc.support.

Ensuite, l'annotation @Service sert à injecter par spring le bean en tant que service.

L'annotation @Transactional permet, de manière déclarative, de déléguer les transactions à l'AOP de spring.

Nous arrivons alors à la déclaration de jdbcTemplate initialisée avec @Autowired sur le setter de la datasource.

À partir justement de la présence de la dépendance dans le pom, spring-boot préconfigure un objet datasource. Il nous reste à reconfigurer correctement cette datasource en complétant le fichier application.properties. C'est ce que nous faisons ci-après.

Partant du fichier application.properties généré par spring-boot, complétons-le comme suit pour reconfigurer la datasource :

spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=xxxxxxx
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.initial-size=10
spring.datasource.max-active=20
spring.datasource.min-idle=1
spring.datasource.max-idle=2
spring.datasource.validation-query="SELECT 1 "

Pensez à adapter certaines valeurs.

Revenons à la suite du code de ServiceImpl, le second attribut de la classe ServiceImp nommé lobHandler, de type o.s.jdbc.support.lob.LobHandler, est intéressant, car il sera utile pour gérer les xLob.

Son implémentation MyDefaultLobHandler est juste une extension de DefaultLobHandler qui est l‘implémentation par défaut fournie par Spring.

Vous constatez que la propriété « wrapAsLob » est fixée à true et bien d'autres comme le montre le code de la classe :

import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
@Service("myDefaultLobHandler")
public class MyDefaultLobHandlerImpl extends DefaultLobHandler {

  public MyDefaultLobHandlerImpl() {
    super();
    this.setWrapAsLob(wrapLob);
    this.setStreamAsLob(true);
  }
}

Maintenant que nous connaissons tous les attributs de la classe ServiceImpl, nous allons passer à ses méthodes.

Démarrons avec la première méthode simple (simpliste) mailExistById :

public boolean mailExistById(long id) {
  try {
    return jdbcTemplate.queryForObject(EXIST_EMAIL_BY_ID,Boolean.class,id);
  } catch(DataAccessException e){
    return false;
  }
}

Pour ceux qui découvrent spring-jdbc, voilà une façon simple de récupérer des objets Java avec une requête SQL paramétrée.

Ici la méthode queryForObject est appelée avec trois arguments :

• le SQL paramétré EXIST_BY_ID= “ select true from MAIL where id=? “ ;
• le type d'objet à retourner ici Boolean ;
• la valeur du paramètre SQL.

Nous pouvons aussi retourner tout autre objet de type T, il suffit alors de passer un RowMapper<T> comme vous allez le découvrir plus loin.

La méthode insertXlob permet de récupérer les données binaires ou textes depuis un fichier puis l'insérer dans le champ xLob de la base. Attention son code ci-dessous pique un peu, en plus, une lambda s'est glissée.

@Override
public void insertXlob(final String fullPathFileClob, final long idMail, String idRegion) throws Exception {
        
  final File clobFileIn = new File(fullPathFileClob);
  if(!clobFileIn.exists()){
    logger.error("Fichier '"+clobFileIn.getPath()+ "' introuvable !");
    return;
  }
  
  try (final InputStream clobFileIs = new FileInputStream(clobFileIn)) 
  {        
    int retour= jdbcTemplate.execute(INSERT_LINE_EMAIL, (PreparedStatement ps)-> /* type necessaire car ambiguite*/
    { 
      try(LobCreator lobCreator = lobHandler.getLobCreator()) {
        ps.setLong(1,idMail);
        lobCreator.setBlobAsBinaryStream(ps,
        numParam4InsertXLob, clobFileIs, clobFileIs.available());
        ps.setString(3,idRegion);
        return ps.executeUpdate();
      } catch (Exception e) { /*catch force par le compilo*/
        logger.warn(e.getMessage());
        return 0;
      }
    });
    if(retour==1) logger.info("Ligne insérée dans '"+NAMETABLE_EMAIL+"'");
  }    
}

Le point important ici est : l'insertion d'une ligne dans la table MAIL passe par la méthode jdbcTemplate.execute prenant deux arguments :

• le premier argument est la chaîne SQL paramétrée, ici c'est la constante INSERT_LINE_EMAIL ayant trois paramètres : Note. INSERT_LINE_EMAIL="INSERT INTO MAIL (id, corps,idRegion) VALUES(? ,?, ?)" ;
• le second est une lambda qui implémente l'interface fonctionnelle PreparedStatementCallback<T> ayant une seule méthode : T doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException.

Donc la lambda vise l'unique méthode doInPreparedStatement prenant un seul argument de type PreparedStatement. D'où le fait que la lambda commence par (PreparedStatement ps) ->

En effet, nous donnons le type de l'argument, car la méthode jdbcTemplate.execute possède deux signatures proches ayant chacune deux arguments dont le premier est un String :

• T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException
• T execute(String sql, CallableStatementCallback<T> action) throws DataAccessException

Alors il faut aider le compilo de Java pour connaître la cible de cette lambda. Ici la lambda définie dans le code vise la première signature. Jetons encore un œil sur le body du lambda (extrait de code).

(PreparedStatement ps)->
  try(LobCreator lobCreator = lobHandler.getLobCreator()) {
    ps.setLong(1,idMail);
    lobCreator.setBlobAsBinaryStream(ps,numParam4InsertXLob, clobFileIs, clobFileIs.available());
    ps.setString(3,idRegion);
    return ps.executeUpdate();
  }
}

C'est le lobCreator récupéré depuis lobHandler qui se charge de traiter le champ xLob en le settant correctement dans PreparedStatement puis executeUpdate est appelé. En un mot, jdbcTemplate.execute délègue la création des xLob au LobCreator.

Spring offre la classe abstraite AbstractLobCreatingPreparedStatementCallback<T> pratique que nous aurions pu utiliser pour les avantages suivants :

• seule la méthode setValues est à écrire ;
• le code de plomberie est masqué.

Nous avons opté pour l'implémentation par des lambdas de l'interface fonctionnelle PreparedStatementCallback<T> ce qui nous amène à manipuler le LobCretaor via ARM.

Même démarche que insertXlob comme l'illustre le code suivant :

@Override
public void updateBlob(final String fullPathFileClob, final long idMail) throws Exception {
  final File clobFileIn = new File(fullPathFileClob);
  if(!clobFileIn.exists()) {
    logger.error("Fichier '"+clobFileIn.getPath()+ "' introuvable !");
    return;
  }
  try (final InputStream clobFileIs = new FileInputStream(clobFileIn)) 
  {        
    int retour= jdbcTemplate.execute(UPDATE_CORPS_BY_ID,
      (PreparedStatement ps)-> 
      { 
        try(LobCreator lobCreator = lobHandler.getLobCreator()) {
          lobCreator.setBlobAsBinaryStream(ps,numParam4UpdateXLob, clobFileIs, clobFileIs.available());
          ps.setLong(2,idMail); 
          return ps.executeUpdate();
      } catch (Exception e) { /*catch force par le compilo*/
        logger.warn(e.getMessage());
        return 0;
      }
    });
    if(retour==1) logger.info("Ligne updatee dans '"+NAMETABLE_EMAIL+"'");
  }    
}

Presque seule la requête SQL paramétrée a changé !

Enchaînons avec cet extrait de ServiceImpl.java donnant l'implémentation de la méthode getCorpsEmailById. Nous détaillons getCorpsEmailById car, rappelez vous, le champ corps est un xLob. Son implémentation contient une lamda aussi, mais ça reste lisible.

....
public class ServiceImpl implements IService {
....
  @Override
  public List<Email> getCorpsEmailById(final long idEmail) {
    return jdbcTemplate.query(
      EMAIL_CORPS_SQL_BY_ID, new Object[] { idEmail },
        (rs,num) -> { 
          final Email email= new Email();
          email.setId(idEmail);
          email.setIdRegion(rs.getString("idRegion"));
          email.setCorps(lobHandler.getClobAsString(rs,"corps"));
          return email;
        });                   
  }   
...

Pour lire le champ xLob, nous appelons l'API jdbcTemplate.query qui prend trois arguments :

• le premier argument est la chaîne SQL paramétrée, ici c'est la constante EMAIL_CORPS_SQL_BY_ID ayant un paramètre. Note. la constante EMAIL_CORPS_SQL_BY_ID="select corps, idRegion from MAIL where id=?"
• le second la valeur du paramètre id du SQL ;
• le troisième est une implémentation de l'interface fonctionnelle RowMapper<T> ayant la seule méthode mapRow. Cette implémentation permet de transformer le resultSet traditionnel en une clase Java Email. Notre implémentation est réalisée avec une lambda. Pour rappel, la signature de la méthode mapRow est : T mapRow(ResultSet rs, int rowNum) throws SQLException.

Reprenons donc le code implémentant RowMapper avec une lambda :

     (rs,num) ->{ 
  final Email email= new Email();
  email.setId(idEmail);
  email.setIdRegion(rs.getString("idRegion"));
  email.setCorps(lobHandler.getClobAsString(rs,"corps"));
  return email;
}

Comme vous le voyez, la lambda prend deux arguments (rs, num) et retourne dans notre cas une instance de Email.

Poursuivons notre découverte du code de ServiceImpl cette fois avec la méthode [cousine] nommée getCorpsEmailByRegion(final String idRegion). Cousine, car le code est identique à un détail près :

@Override
public List<Email> getCorpsEmailByRegion(final String idRegion) {
  return  jdbcTemplate.query(
    EMAIL_CORPS_SQL_BY_REGION, new Object[] { idRegion },
      (rs,num) ->{ 
        final Email email= new Email();
      	email.setIdRegion(idRegion);
        email.setId(rs.getLong("id"));
        email.setCorps(lobHandler.getClobAsString(rs,"corps"));
      	return email;
    });
}

Seul le premier argument de jdbcTemplate.query change. Désormais c'est EMAIL_CORPS_SQL_BY_REGION="select corps, id from MAIL where idRegion=?"

Le reste est un ajustement simple au contexte. C'est tout pour la lecture de xLob !

En un mot, un appel à jdbcTemplate.query avec un LobHandler adapté suivi lobHandler.getClobAsString. Et le tour est joué.

Notre test JUnit (d'intégration) permet de valider l'insertion, la mise à jour et la lecture d'un xLob.

import static org.junit.Assert.assertNotNull;
import java.util.List;
import org.junit.*;
import org.junit.runner.RunWith;
import org.slf4j.*;
import o.s.beans.factory.annotation.Autowired;
import o.s.beans.factory.annotation.Qualifier;
import o.s.boot.test.SpringApplicationConfiguration;
import o.s.test.annotation.Rollback;
import o.s.test.context.junit4.SpringJUnit4ClassRunner;
import o.s.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = JdbcSpringClobBlobApplication.class)
@Transactional
@Rollback(true) //false si on doit persister reellement
public class JdbcSpringClobBlobApplicationTests {

    final static Logger logger = LoggerFactory.getLogger(JdbcSpringClobBlobApplicationTests.class);
    final static long ID_EMAIL_TEST= 222L;
    final static String ID_REGION_TEST="54";
    final static String FULL_PATH_FILE_TEST="/tmp/emailCLOB.txt";
    
    @Autowired
    @Qualifier("service")
    IService service;
    
    @Before
    public void testInsertClob() throws Exception {
        final String fullPathFile = FULL_PATH_FILE_TEST;
        boolean exist = service.mailExistById(ID_EMAIL_TEST);
        if(!exist){
          service.insertXlob(fullPathFile,  ID_EMAIL_TEST, ID_REGION_TEST);
        }
    }

    @Test
    public void testUpdateClob() throws Exception {
        final String fullPathFileClob = FULL_PATH_FILE_TEST;
        service.updateBlob(fullPathFileClob, ID_EMAIL_TEST);
    }
    
    @Test
    public void testReadClobById() throws Exception {
        List<Email> liste = service.getCorpsEmailById(ID_EMAIL_TEST);
        assertNotNull(liste);
        liste.stream().map(Object::toString).forEach(logger::info);
    }
    
    @Test
    public void testReadClob() throws Exception {
        final String idRegion = ID_REGION_TEST;
        List<Email> liste = service.getCorpsEmailByRegion(idRegion);
        assertNotNull(liste);
    }

}

Explications :

• l'annotation @Transactional délègue la gestion des transactions à Spring (aop) ;
• vous avez constaté qu'aucune configuration ni dans XML ni dans Java n'est faite pour transcationManager ;
• Spring use du principe de convention over configuration ou configuration par exception ;
• l'annotation @Rollback avec paramètre à false permet de réellement persister les ordres SQL ;
• de préférence switcher ce paramètre à false pour les tests ;
• le premier test testInsertClob s'appuie sur la méthode mailExistById pour voir s'il faudrait insérer une ligne qui sera récupérée dans les tests des méthodes de lecture.