• Article

Utiliser Java avec SQL Server 2000/2005

Par Julien Chable, développeur/consultant chez Wygwam France sur les technologies Office, Open XML et SharePoint.

Blog de Julien.

L’accès aux données, et plus particulièrement aux bases de données, représente un impératif auquel aucune plateforme d’exécution ne peut passer à côté. Dans le monde Java, cette connectivité est assurée par l’API JDBC (Java Data Base Connectivity) qui définit un ensemble d’interfaces communes permettant à n’importe quel éditeur de créer une implémentation pour accéder à sa base de données maison.

Cet article est l’occasion de montrer qu’une technologie issu des laboratoires de Sun MicroSystem, et largement utilisé aujourd’hui dans des environnements hétérogènes, se mari très bien avec la base de données maison de Microsoft : SQL Server 2005. Au travers de ce tutorial, nous allons installer, configurer et créer une application exploitant une base de données Microsoft SQL Server 2005. Dans la suite de l’article nous n’évoquerons que SQL Server 2005, la dernière version de production lors de la rédaction de cet article, mais sachez que les instructions qui suivent s’applique également pour SQL Server 2000.

Remarque : Notez qu’à l’heure de la rédaction de cet article, la version du pilote pour SQL Server 2008 n’est ni disponible ni annoncée.

Prérequis :

  • Connaissances en Java et de l’utilisation des APIs JDBC
  • Connaissance du produit SQL Server 2005
  • Avoir installé le JDK Java (1.4.2 minimum)
  • Avoir installé Eclipse 3.x et savoir travailler avec

JDBC en détail

L’API JDBC a été créée pour connecter des applications à une base de données et cela quelque soit son origine, c'est-à-dire de permettre à une application Java de gérer, de requêter et de manipuler des données indépendamment du Système de Gestion de Bases de Données utilisé.

Java étant également une technologie qui autorise la portabilité du code exécutable, JDBC bénéficie du même avantage. Néanmoins, le marché n’étant pas toujours homogène il est parfois difficile pour un éditeur de pouvoir proposer un connecteur JDBC totalement portable ou complet, ou permettant une connectivité totalement indépendante du SGBD ciblé.

L’implémentation du connecteur représente une part importante dans la configuration et l’installation d’un connecteur JDBC dans une application, voire dans l’architecture logiciel de votre projet. Par conséquent, ceux-ci sont catégorisés en plusieurs types ; chaque type possède un mode de fonctionnement propre :

  • Type 1 : Le connecteur agit comme une passerelle permettant l’accès à un SGBD grâce à l’utilisation d’une autre technologie (par exemple ODBC, avec le connecteur JDBC-ODBC). L’avantage de ce type de connecteur est que quasiment tous les SGBD possédant un pilote ODBC peuvent être accéder, néanmoins les performances sont souvent assez médiocres lors de montée en charge.
  • Type 2 : Ces connecteurs sont un mélange de code Java et de code natif, et pour cause les appels JDBC sont convertis en appels natifs C/C++. Ce type de connecteur est donc dépendant du système d’exploitation malgré des performances souvent élevées.
  • Type 3 : Ce type de connecteur est aussi appelé ‘Pilote Java pour les bases de données Middleware’ car les appels JDBC passe par un Middleware – par exemple un serveur J2EE – qui les traduis à son tour en appel pour la base de données ciblée. Ce type de connecteur à l’avantage d’être générique pour un même Middleware qui fournit souvent aussi u système de cache, une répartition de charges, etc
  • Type 4 : Les connecteurs de type 4 sont des implémentations écrites uniquement en Java et qui traduisent les appels JDBC directement en appels dans le protocole de la base de données ciblée. Ce type de connecteur est donc totalement indépendant de la plateforme.

Voici le schéma d’un pilote de type 4, type de connecteur qui comme vous l’aurez compris est celui qui nous intéressera dans la suite de cet article :

Le connecteur pour SQL Server 2000/2005 est donc un pilote JDBC de type 4, cela signifie qu’il est écrit entièrement en Java, ce qui vous permet de l’utiliser sur n’importe quelle plateforme sur laquelle s’exécute Java. Ce connecteur répond à la spécification JDBC 3.0 qui a été introduit en même temps que la sortie de Java 1.4 et est évidemment compatible avec les versions supérieures.

Le connecteur prend en charge bon nombre de fonctionnalités avancées de SQL Server : mirroring des bases, le XML, les types définis par l’utilisateur, le curseur de snapshot, l’authentification intégrée, le support du SSL, etc

Installer le connecteur JDBC pour SQL Server 2005

Lorsque l’on parle d’installation pour un connecteur JDBC le terme n’est pas tout à fait exact, car un pilote JDBC est un simple fichier compressé JAR (Java Archive) que vous allez devoir copier dans un répertoire. Néanmoins, comme pour tout composant/librairie Java vous allez devoir l’inclure dans le classpath pour que le runtime Java soit capable de le trouver et d’exécuter le code qu’il contient.

Récupérer le connecteur JDBC pour SQL Server 2000/2005

Pour récupérer le connecteur JDBC pour SQL Server, dirigez vous vers le site : https://www.microsoft.com/fr-fr/download/details.aspx?id=21599 puis cliquez sur le bouton ‘Télécharger’ qui vous conduira tout droit vers une page de licence que vous devrez accepter pour télécharger le pilote JDBC pour Windows (sous forme d’exécutable) ou UNIX (sous forme d’archive tar.gz).

L’exécutable est en réalité une archive Zip autoextractable qui vous demandera un emplacement de décompression. Une fois les fichiers décompressés dans un répertoire de votre choix, vous devriez trouver dans le répertoire sqljdbc_<version du pilore>/enu/ le contenu suivant :

Cette structure vous offre de la documentation avec des exemples d’utilisation de l’API sous forme de fichiers .java (dans le dossier help), une notice d’installation, des implémentations supplémentaires natives (xa et auth) et le fichier JAR qui est le connecteur JDBC qui nous intéresse dans le cadre de cet article.

La documentation fournie avec le connecteur ne plaira pas forcément aux habitués de javadoc puisqu’elle reprend le style graphique et de navigation de la documentation MSDN ; en revanche elle contient de nombreux exemples d’utilisation et de configuration de l’API, ce qui est vraiment une bonne chose pour les débutants ou les experts pour comprendre les fonctions avancées et spécifiques de cette implémentation.

Pour la suite de cet article, nous allons simplement copier le fichier sqljdbc.jar dans le répertoire lib de notre répertoire de travail.

Configurer le classpath de l’application

Pour que l’application puisse trouver le connecteur JDBC sur la machine, nous allons devoir spécifier son emplacement au runtime lors du démarrage de l’application ou alors déclarer son répertoire de localisation dans les variables d’environnement du système. Voici la description des deux étapes sur un environnement Windows :

  1. Pour spécifier le classpath au démarrage d’une application Java, vous devez utiliser la syntaxe suivante :**
    * java –classpath <emplacement de la librairie> <classe de départ de l’application>***
    Si vous voulez ajouter plusieurs librairies (connecteurs JDBC ou API diverses), vous devrez les séparer par des points-virgules.

  2. Dans les propriétés système de votre Panneau de configuration/Système/Avancé/Variables d’environnement/Variable système vous devriez trouver une variable nommée

    CLASSPATH :

    Sélectionnez la variable CLASSPATH puis cliquez sur Editez pour éditer la variable :

    Ajoutez le répertoire contenant votre connecteur JDBC (ici ‘C:\DemoJDBC\lib’) à la fin de la valeur de la variable puis terminez en cliquant sur le bouton ‘OK’.

Dans le cadre de cet article, l’IDE Eclipse 3.3 est utilisé, par conséquent nous utiliserons une troisième façon de configurer le classpath directement dans l’environnement de développement.

La base de données AdventureWorks

Pour cet exemple, nous allons utiliser la base d’exemple de Microsoft : AdventureWorks. Celle-ci est disponible sur la plateforme de projet open source de Microsoft :

https://www.codeplex.com/MSFTDBProdSamples

Pour pouvoir utiliser la base AdventureWorks une fois installée, vous devez l’attacher au serveur SQL à l’aide de SQL Server Management Studio :

Celle-ci devrait ensuite apparaître dans l’arbre de SQL Server Management Studio :

Celle-ci devrait ensuite apparaître dans l’arbre de SQL Server Management Studio :

Utiliser le driver JDBC pour SQL Server 2005 dans vos projets

Afin de montrer les capacités du pilote SQL Server 2005 dans une application Java, nous allons créer un squelette applicatif dans lequel nous allons effectuer tour à tour des requêtes de sélection, d’insertion, des appels à des procédures stockées et plus.

Remarque : Nous avons utilisé Eclipse 3.3.2 lors de la rédaction de cet article ainsi que SQL Server 2005

Tout d’abord créer un projet dont la racine se situe à la racine de votre répertoire de projet (le répertoire qui contient le dossier lib avec le pilote JDBC pour SQL Server) avec Eclipse. Si vous utilisez une version récente, Eclipse détectera automatiquement le pilote et l’ajoutera au classpath sans aucune action de votre part. Néanmoins si cela n’est pas le cas, faites un clic droit sur votre projet puis déplacez vous dans les menus Build Path/Add External Archives :

Et ajoutez la librairie en parcourant votre disque dur et ajoutez là. Il existe d’autres façons d’éditer le classpath/buildpath dans Eclipse, je vous laisse utiliser la méthode que vous préférez.

Maintenant que vous avez référencé votre connecteur JDBC, nous allons nous mettre à coder quelques lignes pour pouvoir nous connecter au SQL Server (ici installé en local).

Connexion au serveur SQL Server 2005

Vous avez plusieurs façons de vous connecter au serveur SQL Server avec le pilote JDBC :

  • Utiliser le DriverManager
  • Utilise la classe SQLServerDriver
  • Utiliser la classe SQLServerDataSource

Dans cet article nous allons utiliser le premier  de façon à ce que votre code soit relativement générique si vous désirez changer de base de données par la suite.

La tâche de base : effectuer une requête SQL

Effectuer une requête de sélection, de mise à jour, etc est un jeu d’enfant en JDBC avec l’utilisation de la classe java.sql.Statement. L’exemple suivant vous montre comment effectuer une requête de sélection sur les contacts de la base AdventureWorks de votre serveur SQL Server 2005 :

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SQLServerQuery {       public static void main(String[] args) {             Connection conn = null;             Statement stmt = null;             ResultSet rs = null;             try {                  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");                  String connectionString ="jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=demo";                  conn = DriverManager.getConnection(connectionString);                  stmt = conn.createStatement();                  rs = stmt                              .executeQuery("SELECT FirstName, LastName FROM Person.Contact");                  while (rs.next()) {                        String contact = rs.getString("FirstName")                                    + " " + rs.getString("LastName");                        System.out.println(contact);                  }             } catch (Exception e) {                   System.out.println(e.getMessage());                   e.printStackTrace();             } finally {                   if (rs != null)                         try { rs.close(); } catch (Exception e) { }                   if (stmt != null)                         try { stmt.close(); } catch (Exception e) { }                   if (conn != null)                         try { conn.close(); } catch (Exception e) { }            }       } }

Lister les procédures stockées de SQL Server 2005

JDBC permet également de récupérer les métadonnées des bases stockées sur le serveur SQL Server, par exemple de pouvoir récupérer les informations du pilote, du serveur, les noms de tables, des colonnes, les fonctions et les procédures stockées disponibles, etc.  Voici un exemple de code permettant de demander toutes les procédures stockées d’un catalogue de SQL Server 2005 :

... Connection conn = null; try {       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");       String connectionString = "jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=demo";       conn = DriverManager.getConnection(connectionString);       DatabaseMetaData meta = conn.getMetaData();       // Récupère les informations du serveur       String infoSrv = meta.getDatabaseProductName() + " " +            meta.getDatabaseProductVersion();      System.out.println(infoSrv);       ResultSet proc = meta.getProcedures(conn.getCatalog(), null, "%");      while (proc.next())      {            System.out.println(proc.getString("PROCEDURE_NAME"));      } } catch (Exception e) {       System.out.println(e.getMessage());       e.printStackTrace(); } finally {       if (conn != null)             try { conn.close(); } catch (Exception e) { }}

Appeler une procédure stockée de SQL Server 2005

Maintenant que nous sommes capables de lister les procédures stockées, nous allons les appeler directement depuis notre code Java. L’avantage bien évidemment d’utiliser des procédures stockées est que tout s’exécute du côté du serveur SQL sans appel intermédiaire au connecteur JDBC et au réseau entre l’application et le serveur.

L'interface CallableStatement permet de faire appel aux procédures stockées et aux fonctions de SQL Server (prise en compte des paramètres IN, OUT et INOUT) en utilisant une syntaxe d'échappement. En effet sans cette syntaxe d’échappement, il serait difficile au moteur de comprendre qu’une partie de la requête n’est pas du SQL et qu’il doit être traité de manière spécifique :

{call maProcedure(?, ?)}

 

Nous allons utiliser les procédures stockées de la base AdventureWorks dans l’exemple qui suit afin de retrouver tous les managers – de façon récursive – d’un employé donné :

Connection conn = null; CallableStatement callStmt = null; ResultSet rs = null; try {       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");       String connectionString = "jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=demo";       conn = DriverManager.getConnection(connectionString);       callStmt = conn.prepareCall("{call uspGetEmployeeManagers(?)}");     callStmt.setInt(1, 1);// Affecte la valeur 1 au paramètre 1      rs = callStmt.executeQuery();      while (rs.next())      {            System.out.print("Employe: " + rs.getString("FirstName") +                        " " + rs.getString("LastName" ) + " -> " );            System.out.println("Manager: " + rs.getString("ManagerFirstName") +                        " " + rs.getString("ManagerLastName"));                 }       rs.close();       callStmt.close(); } catch (Exception e) {       System.out.println(e.getMessage());       e.printStackTrace(); } finally {       if (rs != null)             try { rs.close(); } catch (Exception e) { }       if (callStmt != null)             try { callStmt.close(); } catch (Exception e) { }       if (conn != null)             try { conn.close(); } catch (Exception e) { }}

Allons un peu plus loin dans l’utilisation des procédures stockées de SQL Server 2005, en effet cette version voyait l’introduction de la CLR (en option, et désactivé par défaut) comme moteur d’exécution. Il est donc possible dans des cas extrêmes néanmoins - pour des raisons de performance - de créer des procédures en .NET ! Testons cette possibilité en appelant la procédure suivante déployée sur le serveur SQL Server 2005 :

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public staticvoid HelloEmployee(SqlInt32 employeeID)     {         try         {            using (SqlConnection conn = new SqlConnection("context connection=true"))             {                 conn.Open();                string stmt = "SELECT 'Hello ' + FirstName + ' ' + LastName FROM Person.Contact, HumanResources.Employee WHERE Contact.ContactID = Employee.ContactID AND Employee.EmployeeID = " + employeeID;                SqlCommand cmd = new SqlCommand(stmt, conn);                SqlContext.Pipe.Send(cmd.ExecuteReader());            }         }        catch (Exception e)         {             SqlContext.Pipe.Send(e.Message);         }     }};

Voici l’aperçu de la procédure dans SQL Server Management Studio :

Et la modification pour appeler celle-ci depuis notre application Java :

Connection conn = null; CallableStatement callStmt = null; ResultSet rs = null; try {       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");       String connectionString = "jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=demo";       conn = DriverManager.getConnection(connectionString);       callStmt = conn.prepareCall("{call HelloEmployee(?)}");       callStmt.setInt(1, 110);       rs = callStmt.executeQuery();       while (rs.next())       {             System.out.println(rs.getString(1));       }       rs.close();       callStmt.close(); } catch (Exception e) {       System.out.println(e.getMessage());       e.printStackTrace(); } finally {       if (rs != null)             try { rs.close(); } catch (Exception e) { }       if (callStmt != null)             try { callStmt.close(); } catch (Exception e) { }       if (conn != null)             try { conn.close(); } catch (Exception e) { } }

Observer le résultat par vous-même :

Les transactions et les batch

Une transaction permet d’exécuter un ensemble d’instructions en un seul bloc et de façon atomique, c'est-à-dire qu’elles sont toutes exécutées soit toutes annulées. Si une instruction de la transaction échoue, celle-ci est entièrement annulée et aucune modification n’est répercutée dans la base de données. SQL Server 2005 possède évidement  un système de transaction, et il ne vous faudra pas beaucoup de lignes de code Java pour pouvoir pleinement en profiter.

Par défaut, toute requête exécutée est automatiquement appliquée à la base, c'est-à-dire que chaque requête est exécutée dans une transaction unique. On dit que la ligne est ‘commitée’ ou que le mode est auto-commit est activé. Pour spécifier à SQL Server que nous voulons utiliser son système de transaction manuellement,  nous devons explicitement lui signifier de ne pas appliquer la modification à la base après l’exécution de la requête en utilisant la méthode de la connexion setAutoCommit(false).

Une fois l’auto-commit débrayé, vous devrez spécifier à SQL Server quand appliquer toutes les modifications des requêtes de la transaction avec la méthode commit() pour valider l’ensemble des instruction et terminer la transaction.

Afin d’accélérer l’exécution d’un ensemble de requêtes consécutives, nous allons utiliser la capacité de batch de JDBC. Cette fonctionnalité permet d’ajouter les requêtes les unes à la suite des autres avant d’exécuter l’ensemble des requêtes en tir groupé. Si nous associons ce système de batch à une transaction, si une requête échoue, alors nous annulons les précédentes dans la transaction avec la méthode rollback() :

...
Connection conn = null;
Statement stmt = null;try {
     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
     String connectionString = "jdbc:sqlserver://localhost;database=AdventureWorks;user=sa;password=demo";
      conn = DriverManager.getConnection(connectionString);
     // On débraye l'auto commit      conn.setAutoCommit(false);      stmt = conn.createStatement();     // Génére des cartes bancaires dans la base      for (int i = 0; i < 10; i++) {            String insertStmt ="INSERT INTO Sales.CreditCard VALUES ('Visa', '"                        + getRandomCreditCardNumber(14)                        + "', 11, 2008, GETDATE())";            stmt.addBatch(insertStmt);     }      stmt.executeBatch();       conn.commit();// On termine et valide la transaction``} catch(Exception e) {
     try {
           // Si il y a une exception, nous annulons la transaction``            if (conn != null)                  conn.rollback();``      } catch(SQLException sqle) {
            // Ne fait rien, attend le finally
      } finally {
            System.out.println(e.getMessage());
            e.printStackTrace();
      }
} finally {
      if (stmt != null)
            try {
                  stmt.close();
                        } catch (Exception e) {
                        }
                  if (conn != null)
                        try {
                              conn.close();
                      } catch (Exception e) {
                        }
                  System.out.println("Les données ont été ajoutées !");
            }
...static String getRandomCreditCardNumber(int length) {
      StringBuffer retVal = new StringBuffer();
      for (int i = 0; i < length; i++) {
            retVal.append(((byte)(Math.random() * 10)) + "");
      }
     return retVal.toString();
}

Redistribuer le pilote JDBC pour SQL Server 2005 avec votre application

Si vous bâtissez votre application à succès sur SQL Server 2005, vous voudrez bien évidemment livrer le connecteur JDBC avec votre application pour que vos clients puissent l’installer le plus simplement possible. Microsoft autorise ce cas de figure, et il vous suffira de vous rendre sur la page : https://msdn2.microsoft.com/en-us/data/aa937725.aspx pour signer un EULA de redistribution et redistribuer le connecteur dans le package de votre application.

Conclusion

SQL Server 2005 possède de nombreux atouts aussi bien au niveau des fonctionnalités que des performances générales. Cela en fait un serveur de base de données de premier choix qu’il ne faut surtout pas écarter de vos choix lorsque vous créez des applications avec la technologie Java (J2SE ou J2EE). En effet, sa manipulation est aisée, complètement transparente, et vous avez la possibilité d’utiliser des classes et des méthodes propres à SQL Server 2005 qui vous permettront de tirer partie de toutes les fonctionnalités de ce puissant serveur SQL.

Que vous utilisiez directement un connecteur JDBC, une pile J2EE ou un ORM tel que Hibernate ou TopLink, la qualité de l’implémentation du pilote vous permettra de pleinement exploiter les capacités de SQL Server 2005 depuis votre environnement Java/non Microsoft.

Liens