We apologize for untranslated text, you can use the Google Translation button to get an automatic translation of the web page in the language of your choice.

JCMS 5.5 : interfaçage avec des bases de données externes

Dans de nombreux cas d'usage, JCMS constitue l'une des briques du SI de l'entreprise. Pour faciliter l'intégration avec les autres sources d'information du SI, JCMS 5.5 introduit trois nouveaux types de champ permettant de présenter des informations issues de bases de données lors de l'affichage d'une publication JCMS :

  1. Le champ Requête SQL (SQL Query) permet de saisir une requête SQL et d'afficher les résultats correspondants
  2. Le champ Enregistrement de BD (DB Record) permet de choisir un enregistrement dans une table (ou une vue) de la base de donnée
  3. Le champ Résultat SQL (SQL Result) affiche les résultats d'une requête SQL dont les paramètres sont issus d'autres champs de la publication.

Cet article décrit ces nouveaux champs et leur mise en œuvre.

1 Exemple de base de donnée

Les exemples de cet article s'appuient sur une base de données cinématographique (très simplifiée) gérée avec MySQL 5.0.

La structure de la base est décrite par le diagramme entité/relation ci-dessous. La base comporte 4 tables :

  1. les films (movies) ;
  2. les studios (studios) ;
  3. les acteurs (stars) ;
  4. une table de jointure (starsin) qui associe un acteur à un film.

Le script SQL, déclarant la structure de la base et alimentant chacune des tables, est fourni en annexe de cet article.

Structure de la base de données utilisée en exemple.

2 Configuration

2.1 Déclaration d'un compte utilisateur JCMS dans la base de données

Les nouveaux types de champs de JCMS permettent à un simple contributeur de déclencher une requête SQL. Le langage SQL permet d'extraire des données (requête de type SELECT) mais aussi d'en ajouter, d'en supprimer et de modifier la structure de la base. Il est donc extrêmement important de limiter le type de requête SQL autorisé à des SELECT et leur portée au strict minimum. Pour ce faire, il est recommandé de déclarer un compte utilisateur dédié dans la base de donnée et de lui donner les droits ne permettant que de faire des SELECT sur les tables et les vues choisies.

2.2 Choix du driver JDBC

Le driver JDBC est le composant Java établissant la connexion avec la base de données. Il existe autant de driver JDBC que de type de base de données. Pour certaines bases de données, il existe plusieurs driver JDBC. Les drivers JDBC sont généralement fournis avec les bases de données. Sun diffuse la liste des drivers JDBC disponibles pour la plus part des bases de données existantes.

Un driver JDBC se présente généralement sous la forme d'un fichier JAR qu'il faut installer soit dans le serveur d'application soit dans le répertoire WEB-INF/lib de la webapp JCMS.

2.3 Déclaration des sources de données

Dans les serveurs d'application J2EE, l'accès à une base de données se fait généralement au travers d'une source de donnée (javax.sql.DataSource). JCMS repose sur ce principe. Après avoir configurer le(s) driver(s) JDBC, la tâche suivante consiste à déclarer les sources de données disponibles.

Une source de données contient les informations nécessaires pour ouvrir une connexion avec la base de données. On y trouve donc au minimum :

  • l'URL d'accès JDBC
  • Le compte utilisateur
  • Le driver JDBC à utiliser.

Le format de l'URL JDBC et le driver JDBC sont propres à chaque modèle de base de donnée. Le site DevX liste les configurations JDBC des principales bases de données existantes.

Si le concept de source de données est commun aux serveurs d'application J2EE, la procédure pour les déclarer est propre à chaque serveur d'application.

Dans les exemples de déclaration ci-dessous, on déclare une source de donnée sur le SGBDR MySQL pour accéder à la base «movies» avec l'utilisateur «jcms».

2.3.1 Déclaration dans Jakarta Tomcat 5.0.x et 4.1.x

Dans Jakarta Tomcat 4.1.x et 5.0.x, les sources de données sont déclarées dans le fichier conf/server.xml avec les balises <Resource> et <ResourceParams> au sein de la balise <Context> :

<Context path="/jcms" docBase="jcms">
<Resource name="jdbc/movies" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/movies">
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/movies</value>
</parameter>
<parameter>
<name>username</name>
<value>jcms</value>
</parameter>
<parameter>
<name>password</name>
<value>secret</value>
</parameter>
</ResourceParams>
</Context>

Pour plus de détails sur la configuration des sources de données avec Tomcat, consultez les pages :

2.3.2 Déclaration dans Caucho Resin 2.1.x

Dans Caucho Resin 2.1.x, les sources de données sont déclarées dans le fichier WEB-INF/web.xml avec la balise <resource-ref> :

<resource-ref>
<res-ref-name>jdbc/movies</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<init-param driver-name="com.mysql.jdbc.Driver"/>
<init-param url="jdbc:mysql://localhost:3306/movies"/>
<init-param user="jcms"/>
<init-param password="secret" />
</resource-ref>

Pour plus de détails sur la configuration des sources de données avec Resin 2.x, consultez la page:

2.3.3 Déclaration dans Caucho Resin 3.x

Dans Caucho Resin 3.x, les sources de données sont déclarées dans le fichier WEB-INF/web.xml avec la balise <database> :

<database>
<jndi-name>jdbc/movies</jndi-name>
<driver>
<type>com.mysql.jdbc.Driver</type>
<url>jdbc:mysql://localhost:3306/movies</url>
<user>jcms</user>
<password>secret</password>
</driver>
</database>

Pour plus de détails sur la configuration des sources de données avec Resin 3.x, consultez les pages :

2.3.4 BEA Weblogic 8.1

La déclaration d'une source de données dans BEA Weblogic 8.1 se fait par la console d'administration du serveur d'application. Ce document décrit cette procédure :

http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcdatasource_config.html

Si votre DataSource n'est pas reconnue par WebLogic il peut être nécéssaire d'effectuer une configuration supplémentaire dans le fichier web.xml ainsi que dans le fichier weblogic.xml de la webapp. Consultez le message de forum suivant pour plus d'information à ce sujet : DataSource et WebLogic

2.3.5 IBM WebSphere 5.1

La déclaration d'une source de données dans IBM Websphere 5.1 se fait par la console d'administration du serveur d'application. Ce document décrit cette procédure :

http://publib.boulder.ibm.com/infocenter/wasinfo/v5r1/index.jsp? topic=/com.ibm.websphere.base.doc/info/aes/ae/cdat_datasor.html

2.4 Nommage des sources de données

Par défaut, dans les différentes interfaces faisant intervenir les sources de données, JCMS affiche leur nom tel qu'il est décrit dans la déclaration de la source de données (par exemple "jdbc/movies". Il est possible de remplacer ce nom par un libellé plus explicite et localisé dans la langue de l'utilisateur. Pour cela, il faut ajouter dans le fichier WEB-INF/data/webapp.prop, des propriétés de la forme <lang>.data-source.<name>.

Par exemple :

fr.data-source.jdbc/movies : MySQL - BD Films
en.data-source.jdbc/movies : MySQL - Movie DB

2.5 Tester la configuration avec la Portlet SQL

Pour tester votre configuration, le plus simple est d'insérer dans une page portail une PortletSQL. Cette portlet dispose d'un champ Requête SQL permettant de saisir une requête SQL. La source de données déclarée précédemment doit apparaître dans le menu déroulant intitulé "Source de données". Le bouton "Tester la requête" permet de vérifier la connexion avec la base de donnée et la validité de la requête. C'est aussi l'occasion de vérifier que seules des requêtes SELECT peuvent être exécutées sur les tables et les vues choisies.

Ajout d'une Portlet SQL pour tester la configuration.

Une fois insérée dans le portail, la Portlet SQL affiche la liste des résultats correspondant à la requête SQL saisie.

Affichage des résultats dans la Portlet SQL

 

3 Trois nouveaux types de champs, pour trois usages différents

3.1 Requête SQL

Le type de champs Requête SQL (SQL Query) permet de saisir une requête SQL dont les résultats seront listés lors de l'affichage de la publication. Ce type champ est donc destiné à des contributeurs connaissant la syntaxe SQL ainsi que le modèle de données de la base interrogée.

Lors de la déclaration du champ, le concepteur du type indique :

  • La source de données (optionnelle). Si la source de données n'est pas précisée lors de la déclaration du champ, le contributeur pourra la choisir lors de la saisie de la requête.
  • Le nombre maximum de ligne à afficher (obligatoire).

Lors de l'édition de la publication, le contributeur saisit la requête SQL et la source de données sur laquelle elle porte. Avant d'enregistrer la publication, il est recommander de tester la requête en cliquant sur le bouton "Tester la requête".

Saisie d'un champ requête SQL

Par défaut, à l'affichage, les enregistrements sont présentés dans un tableau dont les colonnes correspondent aux colonnes choisis dans la clause SELECT de la requête SQL. Les résultats sont découpés en page de 10 enregistrements. Comme tout gabarit d'affichage JCMS, cette présentation peut-être modifiée.

SCREENSHOT AFFICHAGE SQLQUERY

3.2 Enregistrement de BD

Le type de champ Enregistrement de BD (DB Record) permet d'associer un enregistrement d'une base de donnée à une publication JCMS.

Ce type de champ est défini par plusieurs paramètres :

  • La source de donnée (obligatoire) ;
  • Le nombre maximum de ligne à afficher (obligatoire) ;
  • La table ou la vue dans laquelle (obligatoire) ;
  • La colonne contenant la clé primaire de l'enregistrement (obligatoire) ;
  • Les colonnes à lister (obligatoire) ;
  • La colonne contenant le libellé de l'enregistrement à afficher (obligatoire) ;
  • La fonction de tri à utiliser (optionnelle).

Le bouton "Tester la requête" permet de vérifier la validité des paramètres saisis.

Déclaration d'un champ Enregistrement de BD.

A la saisie de la publication, le contributeur choisit l'enregistrement en cliquant sur l'icône qui liste, dans une nouvelle fenêtre, l'ensemble des enregistrements correspondant aux critères décrits dans le champ. La valeur qui apparaît dans la zone grise est la clé primaire de l'enregistrement. Celle qui apparaît dans la zone blanche correspond à la colonne "libellé".

Saisie d'un Enregistrement de BD.

A l'affichage de la publication, le champ Enregistrement de BD présente le libellé de l'enregistrement. Un lien permet de consulter le détail de l'enregistrement (présenté par la JSP jcore/displayDBRecord.jsp).

Affichage détaillé d'un enregistrement de BD

3.3 Résultat SQL

Le type champ Résultat SQL (SQL Result) n'apparaît pas à la saisie de la publication. Il n'intervient que lors de l'affiche de la publication. Il se présente comme le champ Requête SQL mais affiche les résultats d'une requête SQL dont les paramètres sont issus d'autres champs de la publication.

En pratique, ce type de champ est utilisé conjointement avec un champ Enregistrement de BD qui sert de clé à la requête SQL.

Par exemple, pour afficher la liste des acteurs d'un film, on déclare un champ "movie" de type Enregistrement de BD sur la table Movies qui permet au contributeur de choisir le film dont on veut afficher le casting. Par ailleurs, on déclare un champ Résultat SQL effectuant une requête SQL dont l'un des paramètres de la clause WHERE est le contenu du champ film.

La déclaration de la requête SQL paramétrique se fait lors de la déclaration du champ. On y indique :

  • La source de donnée (obligatoire) ;
  • Le nombre maximum de ligne à affiche (obligatoire) ;
  • La requête SQL paramétrique. Les paramètres sont représentés par le caractère ? ;
  • La liste des noms de champ servant de paramètres (séparés par des espaces)

Déclaration d'un champ Résultat SQL.

Affichage d'un champ Résultat SQL.

 

4 API d'accès aux sources de données

Outre les 3 nouveaux types de champs, JCMS fournit quelques méthodes utilitaires pour interagir avec des sources de données :

4.1 Les classes DBUtil et SQLQueryResult

La classe com.jalios.util.DBUtil fournit des méthodes statiques pour obtenir une source de données et exécuter une requête.

La classe com.jalios.SQLQueryResult exécute une requête et traite le résultat de manière à être plus aisément manipulable. Il est important de noter que l'ensemble des résultats obtenus est chargé en mémoire. Afin de ne pas provoqué de OutOfMemory, il convient donc de positionnée le paramètre maxRows à une valeur raisonnable.

4.2 Le tag <jalios :sqlquery>

Ce tag effectue une requête SQL sur une source de donnée et fournit un objet SQLQueryResult permettant de naviguer dans les résultats.

Exemple :

<table>
<%-- Column names --%>
<tr>
<td>&nbsp;</td>
<jalios:foreach array='<%= sqr.getColumnNames() %>' name='itColName' type='String'>
<td><%= itColName %></td>
</jalios:foreach>
</tr>
<%-- Rows --%>
<jalios:foreach collection='<%= sqr.getRowList() %>'
name='itRow'
type='SQLQueryResult.Row'
counter='rowCounter'>
<tr>
<td align='right'><%= rowCounter %></td>
<jalios:foreach collection='<%= itRow.getColumnList() %>' name='itCol' type='Object' counter='colCounter'>
<td><%= itCol %></td>
</jalios:foreach>
</tr>
</jalios:foreach>
</table>

 

5 Exemple de synchronisation avec un SGBDR

5.1 Présentation

Cet exemple propose une synchronisation entre JCMS et une base de donnée. Le but est d’utiliser JCMS pour gérer et afficher des réunions tout en conservant un système existant basé sur une base de données. Le système mis en place duplique les données dans JCMS pour leur gestion et dans la base de données afin de garantir la continuité de service d’applications tiers. Les créations et mise à jours sont exclusivement réalisées via l’interface JCMS, les lectures sont quant à elles réalisées dans JCMS et dans l’ensemble des applications tiers. Le schéma suivant résume l’architecture mise en place.

sql_jcms_db_sync

Architecture de synchronisation

 

5.2 Eléments techniques nécessaires à la synchronisation

Pour fonctionner, la synchronisation nécessite la création des éléments suivants :

  • Le type Meeting : un type meeting équivalent à la table Meeting de la base de donnée doit être créé dans JCMS. Ce nouveau type doit contenir en plus des champs de la table, un champ « sqlId » pour stocker l’identifiant de l’enregistrement en base de donnée. Ce champ est déclaré comme caché afin de ne pas apparaître dans les formulaires de saisie.
  • Un DataController (MettingController) pour gérer la synchronisation entre JCMS et la base de données lors des écritures (pour plus de détails sur les DataController, consultez l'article JCMS 5 : Développer avec DCM et les DataController).
  • Les procédures stockées de création, modification et suppression. Les opérations sur la base de données sont gérées via des procédures stockées car elles permettent de réaliser des traitements spécifiques et de gérer des codes de retour. Les procédures retournent 0 en cas succès ou un message d’erreur dans le cas contraire. La procédure de création retourne l’identifiant SQL du meeting venant d’être créé.

5.3 MettingController

Le DataController MettingController est charger de propager les modifications sur les meetings. Les DataController sont déclarés dans le fichier custom/JcmsInit.java comme suit :

  /**
* Add your custom DataController in this method
*
* @since jcms-5.0.0
*/
public static void initDataController() throws Exception {
Channel channel = Channel.getChannel();



//Synchro JCMS Meeting
channel.addDataController(new MeetingController(), generated.Meeting.class);
}
Ce DataController appel une procédure stockée différente selon le type d’opération (création, mise à jour ou suppression) avant d’enregistrer dans JCMS. En cas d’échec d’écriture dans la base de données, l’écriture dans JCMS est annulée. Dans cet exemple, nous considérons qu’un échec d’écriture dans JCMS est impossible aussi le rollback sur la base de donnée n’est pas géré. Le code source du dataController est le suivant :
public class MeetingControler extends BasicDataController {
private static final Logger logger = Logger.getLogger(MeetingControler.class);

//Update Database Before write
public void beforeWrite(Data data, int op, Member mbr, Map myContext) {
Meeting meeting = (Meeting)data;
Connection cnx = null;
CallableStatement pstmt = null;
ResultSet rs = null;

try {
//Connect to database
cnx = com.jalios.util.DBUtil.getConnection("jdbc/myDB" );

if (op == OP_CREATE) {
Timestamp startDate = Util.notEmpty(meeting.getStartDate())?new Timestamp(meeting.getStartDate().getTime()):null;
Timestamp endDate = Util.notEmpty(meeting.getEndDate())?new Timestamp(meeting.getEndDate().getTime()):null;
String inCharge = Util.notEmpty(meeting.getInCharge())?meeting.getInCharge().getInfo():null;

pstmt = cnx.prepareCall("{call MeetingInsert(?,?,?,?,?,?,?)}") ;
pstmt.setString(1, meeting.getTitle());
pstmt.setString(2, meeting.getPlace());
pstmt.setString(3, meeting.getOrganisation());
pstmt.setTimestamp(4, startDate);
pstmt.setTimestamp(5, endDate);
pstmt.setString(6, inCharge);
pstmt.setString(7, meeting.getDescription());
rs = pstmt.executeQuery();

//ADD SQL ID INTO JCMS MEETING
rs.next();
int sqlId = rs.getInt("sqlId");
meeting.setSqlId(sqlId);
}
else if (op == OP_UPDATE) {
Timestamp startDate = Util.notEmpty(meeting.getStartDate())?new Timestamp(meeting.getStartDate().getTime()):null;
Timestamp endDate = Util.notEmpty(meeting.getEndDate())?new Timestamp(meeting.getEndDate().getTime()):null;
String inCharge = Util.notEmpty(meeting.getInCharge())?meeting.getInCharge().getInfo():null;

pstmt = cnx.prepareCall("{call MeetingUpdate(?,?,?,?,?,?,?,?)}") ;
pstmt.setInt(1, meeting.getSqlId());
pstmt.setString(2, meeting.getTitle());
pstmt.setString(3, meeting.getPlace());
pstmt.setString(4, meeting.getOrganisation());
pstmt.setTimestamp(5, startDate);
pstmt.setTimestamp(6, endDate);
pstmt.setString(7, inCharge);
pstmt.setString(8, meeting.getDescription());
pstmt.execute();
}
else if (op == OP_DELETE) {
pstmt = cnx.prepareCall("{call MeetingDelete(?)}") ;
pstmt.setInt(1, meeting.getSqlId());
pstmt.execute();
}
} catch (SQLException ex) {
ex.printStackTrace();

// Prevent store write
myContext.put(DO_NOT_STORE, Boolean.TRUE);

// Force redirection to the edit form
HttpServletRequest req = Channel.getChannel().getCurrentServletRequest();
if (req != null) {
logger.warn("Do not store " + data);
req.getSession().setAttribute("warningMsg", "Erreur d'enregitrement. Merci de réessayer.");
String url = ServletUtil.getAbsUrlWithUpdatedParams(req, new String[] {"opCreate", "opUpdate", "opDelete"}, new String[] {null, null, null});
req.setAttribute("forceRedirect", url);
}
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (cnx != null) cnx.close();
} catch (SQLException ex) {
logger.error("Erreur de fermeture de la connexion à la base de donnée.");
ex.printStackTrace();
}
}
}
}

References

In brief...

Il est parfois nécessaire d’associer JCMS avec d’autres sources d’information du SI, comme par exemple une base de données relationnelle. JCMS 5.5 introduit trois nouveaux types de champ permettant de présenter des informations issues d’une base de données lors de l’affichage d’une publication. Cet article décrit ces nouveaux champs et leur mise en oeuvre. Un exemple de synchronisation entre JCMS et un SGBDR est décrit en fin d'article.

Subject
Published

10/14/16

Writer
  • Olivier Dedieu