Accès avancé aux données relationnelles

avec les tableaux []

Bases de données relationnelles

• Pour

• Format relationnel simple et puissant

• Requêtage particulièrement puissant (SQL)

• Requêtage opère sur des données présentes ou non en mémoire

• Instances distribuées possibles

• Transactions

• Interopérabilité avec le code possible (Oracle, SQL Server, H2…)

• Contre

• Plus lourd qu’XML ou POJO à mettre en place

• Interface de base pour le langage très limitée (chaîne textuelle SQL et rien d’autre)

• Typer les données pour le code non trivial (comme pour XML)

• SQL pas standard d’une base à l’autre

• SQL un peu austère au premier abord

Bases de données relationnelles

• Contre

• Plus lourd qu’XML ou POJO à mettre en place

• Une fois mis en place, le bénéfice est visible

• Interface de base pour le langage très limitée (eg. SQL seule API proposée)

• A améliorer. Proposer une API fortement structurée et typée.

• Typer les données pour le code non trivial (comme pour XML)

• A faire. Proposer un typage des données elles-mêmes.

• SQL pas standard d’une base à l’autre

• A faire (voir le second point sur l’API à proposer)

• SQL un peu austère au premier abord

• Pas réellement un argument

La base relationnelle en pratique

• Pluralité des applicatifs, des outils

• Caractère central (et souvent vital) de la base, caractérisant les

données métiers

Outil

Outil

Outil

Outil

Outil

Base

Outil

Rappels sur SQL (niveau L2)

String query =

"SELECT " +

"* " +

"FROM " +

"WHERE " +

"AND " +

"user_details " +

"email = ‘" + user.getEmail() + "’" +

"password = ‘" + user.getPassword() + "’";

Statement s = con.createStatement(query);

ResultSet rs = s.executeQuery();

Problèmes rencontrés

• Encodage et échappement des chaînes de caractères à faire soi-

même

• Risques d’injections malicieuses SQL

• Formattage des données à réaliser explicitement

• Dates en particulier (dépend de la locale utilisée par la base)

Requêtage SQL, bis

String query =

"SELECT " +

"* " +

"FROM " +

"user_details " +

"WHERE " +

"email = ? " +

"AND " +

"password = ?";

PreparedStatement ps = con.prepareStatement(query);

ps.setString(1, user.getEmail());

ps.setString(2, user.getPassword());

ResultSet rs = ps.executeQuery();

Améliorations apportées

• Encodage des chaînes de caractères automatiquement pris en charge

• Locale automatiquement prise en charge

• Dates prises en charge via type natif du langage (DateTime etc.)

Problèmes rencontrés

• Si la structure de la base est remise en question, le code

compile bien… mais ne marche plus

• Peut-on s’assurer en amont, dans l’IDE, que le code ne va pas compiler si la structure

de la base est altérée ?

• Pas de typage fort au sens général

• SELECT nom FROM client

• Mon environnement de développement peut-il me prévenir que ‘client’ existe et que

le champ ‘nom’ est bien disponible ?

• Pas de correspondance entre les tables SQL et des classes

• Pas indispensable (cf. exemple plus tard avec LINQ)

• Reste tout de même intuitif lorsque des requêtes renvoient des rangées de tables

entières/brutes)

Historique

• De nombreux langages de programmation existent

• C, C++, Java et tant d’autres

• De nombreux langages de requêtes (dédiés) existent

• SQL, XQuery…

• Intégration d’un langage de requêtage dans un langage de

programmation ?

Ordres SQL dans le langage

• Oracle Pro/C

• Disponible depuis plusieurs décennies

• Préprocesseur traduisant des ordres relationnels (SQL) directement écrits

dans le code (sans chaînes de caractères) en code C

• EXEC SQL <ordre SQL>

• Permet de partager des variables entre C et SQL

• SELECT INTO <variable C> par exemple

• Supporte les curseurs

• Très utilisé ; toujours disponible

LINQ

• Language INtegrated Query

• C# et VB.NET

• Se branche sur différentes sources de données

• Objets (on parle parfois de Plain Old Objects)

• XML

• Base SQL…

• LINQ est directement intégré dans le langage

• Comme Oracle Pro/C

• Prise en charge par le compilateur lui-même

• Interop SQL/langage simplifiée

Possibilités offertes en Java

• Solutions propriétaires

• JOOQ

• Speedment

• Hibernate natif

• JINQ

• Solutions standardisées

• JPA (plusieurs implémentations disponibles)

• Stream API

• API avec typage fort des données

• API de requêtage fortement typée et/ou textuelle

• Certains services fournis par l’environnement de développement

Possibilités offertes en Java

• Solutions propriétaires

• JOOQ

• Speedment

• Hibernate natif

• JINQ

• Solutions standardisées

• JPA (plusieurs implémentations disponibles)

• Stream API

• API avec typage fort des données

• API de requêtage fortement typée et/ou textuelle

• Certains services fournis par l’environnement de développement

Ecosystème relationnel .NET

Base (SQL)

Objets

XML

Requête

VB.NET/C#

LINQ

Code applicatif

Objets statiquement ou

dynamiquement typés

• LINQ consomme des objets, mais aussi du SQL (direct), ou du XML

LINQ

• Le compilateur prend en charge la syntaxe

• Transforme le code en

• Requêtes SQL textuelles (lorsque le backend est une base SQL)

• Code C# d’interrogation sur des collections (lorsque le backend est une

collection d’objets et sous-objets)

• requêtes spécifiques lorsque le backend est XML

Ecosystème relationnel JPA

Base (SQL)

XML

Objets

JPQL

Code applicatif

Query

Objets statiquement ou

dynamiquement typés

• Deux API sont disponibles

• Une API explicite où les requêtes sont construites avec des objets précis

• Chaine de caractère JPQL. Dans ce cas JPQL transforme la chaine en requête fortement typée

à base d’instances d’objets

Exemple de requête

SELECT c FROM Country c WHERE c.population >

:p

Requête JPQL textuelle

CriteriaQuery<Country> q = cb.createQuery(Country.class);

Root<Country> c = q.from(Country.class);

ParameterExpression<Integer> p = cb.parameter(Integer.class);

q.select(c).where(cb.gt(c.get(Country_.population), p));

Requête JPQL objet

SELECT * FROM Country WHERE population >

<valeur>

Requête SQL textuelle

Exemple de requête

SELECT c FROM Country c WHERE c.population >

:p

Requête JPQL textuelle

CriteriaQuery<Country> q = cb.createQuery(Country.class);

Root<Country> c = q.from(Country.class);

ParameterExpression<Integer> p = cb.parameter(Integer.class);

q.select(c).where(cb.gt(c.get(Country_.population), p));

Requête JPQL objet

SELECT * FROM Country WHERE population >

<valeur>

Requête SQL textuelle

Typage de l’API de requêtage

SELECT c FROM Country c WHERE c.population >

:p

Requête JPQL textuelle

CriteriaQuery<Country> q = cb.createQuery(Country.class);

Root<Country> c = q.from(Country.class);

ParameterExpression<Integer> p = cb.parameter(Integer.class);

q.select(c).where(cb.gt(c.get(Country_.population), p));

Requête JPQL objet

SELECT * FROM Country WHERE population >

<valeur>

Requête SQL textuelle

Typage des données manipulées

SELECT c FROM Country c WHERE c.population >

:p

Requête JPQL textuelle

CriteriaQuery<Country> q = cb.createQuery(Country.class);

Root<Country> c = q.from(Country.class);

ParameterExpression<Integer> p = cb.parameter(Integer.class);

q.select(c).where(cb.gt(c.get(Country_.population), p));

Requête JPQL objet

SELECT * FROM Country WHERE population >

<valeur>

Requête SQL textuelle

Mappage base  objets Java

Base (SQL)

Objets

• Edition du fichier persistence.xml

• persistence.xml est automatiquement reconnu par l’implémentation

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.1" …>

<persistence-unit name="fc.Application.JPA">

<class>CustomerDatabase.Customer</class>

<class>CustomerDatabase.Invoice</class>

<class>CustomerDatabase.Item</class>

<class>CustomerDatabase.ItemPK</class>

<class>CustomerDatabase.Product</class>

<properties>

<property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />

<property name="javax.persistence.jdbc.url"

value="jdbc:h2:C:\Users\Fred\Documents\repos\fcres\fc.Test\h2database" />

<property name="javax.persistence.jdbc.user" value="sa" />

<property name="javax.persistence.jdbc.password" value="" />

<property name="javax.persistence.ddl-generation" value="none" />

<property name="javax.persistence.ddl-generation.output-mode" value="both" />

<property name="javax.persistence.logging.level" value="FINE" />

</properties>

</persistence-unit>

</persistence>

Génération de classes (automatisée)

@Entity

@NamedQuery(name="Customer.findAll", query="SELECT c FROM Customer c")

public class Customer implements Serializable

{

private static final long serialVersionUID = 1L;

@Id

private int id;

private String city;

private String firstname;

private String lastname;

private String street;

//bi-directional many-to-one association to Invoice

@OneToMany(mappedBy="customer")

private List<Invoice> invoices;

Requêtage JPQL

EntityManagerFactory emf =

Persistence.createEntityManagerFactory("fc.Application.JPA");

EntityManager em = emf.createEntityManager();

String str = "SELECT c FROM Customer c";

Query query = em.createQuery(str, Customer.class);

List<Customer> customers = (List<Customer>) query.getResultList();

for (Customer p : customers)

{

System.out.println("Nom = "+ p.getLastname());

}

em.close();

emf.close();

Requêtage Criteria Query

CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Tuple> q = builder.createTupleQuery();

Root<Customer> c = q.from(Customer.class);

q.select(builder.tuple(

c.get(Customer_.firstname),

c.get(Customer_.lastname)));

List<Tuple> results = em.createQuery(q).getResultList();

Requêtage avec l’API Stream

• Le requêtage peut également se faire avec l’API Stream

• Le fournisseur JPQL peut fournir directement le support de stream:

String str = "SELECT c FROM Customer c";

TypedQuery<Customer> query = m_EM.createQuery(str, Customer.class);

Stream<Customer> customers = query.stream();

customers.

filter(c -> c.getFirstname().startsWith("S")).

forEach(c -> System.out.println(c.getFirstname()));

• En l’absence de support stream intégré au fournisseur JPQL,

il est possible d’appeler

query.getResultList().stream();

Requêtage avec l’API Stream

• Les clauses Stream ne sont pas converties en SQL

• Elles opèrent sur le résultat de la requête, « en mémoire »

• Données rapatriées en amont par JPA, stockées en mémoire, puis

(finalement) traitées par Stream

JPA

String str = "SELECT c FROM Customer c";

TypedQuery<Customer> query = m_EM.createQuery(str, Customer.class);

Stream<Customer> customers = query.stream();

Exécuté par la base elle-même (serveur)

Stream API

customers.

filter(c -> c.getFirstname().startsWith("S")).

forEach(c -> System.out.println(c.getFirstname()));

Exécuté par le code client

Visualisation du SQL généré

• persistence.xml:

<property name="eclipselink.logging.level" value="FINE"/>

• Paramétrage similiaire pour d’autres implementations JPA

• Utile pour se familiariser avec JPQL si on connait déjà SQL

Mappage base/modèle objet

L’IDE fait respecter le mappage

Autocomplétion proposée par l’IDE (1/2)

Autocomplétion sur les types de la base

Autocomplétion sur l’interface

de requêtage

Autocomplétion proposée par l’IDE (2/2)

• Requêtes nommées

Requêtes nommées

<?xml version="1.0" encoding="UTF-8"?>

<entity-mappings version="2.0"

xmlns="http://java.sun.com/xml/ns/persistence/orm"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm

http://java.sun.com/xml/ns/persistence/orm_2_0.xsd">

<named-query name="allSuppliers">

<query>SELECT s FROM Invoices s</query>

</named-query>

</entity-mappings>

Comparaison avec LINQ

• Prise en charge de la requête par le compilateur lui-même

• Autocomplétion disponible à l’intérieur de la requête

Un raytraceur en 1 seule expression LINQ

https://github.com/lukehoban/LINQ-raytracer

JINQ : Java INtegrated Query

• Développé par Ming-Chen Iu en 2015

• Tente de fournir les mêmes fonctionnalités que LINQ

• Même interface que la Stream API, mais transforme le code des

lambdas en code JPQL

• Le code source des lambdas est réinterprété ; il n’est jamais exécuté

• Stabilité ?

String str = "SELECT c FROM Customer c";

TypedQuery<Customer> query = m_EM.createQuery(str, Customer.class);

Stream<Customer> customers = query.stream();

customers.

filter(c -> c.getFirstname().startsWith("S")).

forEach(c -> System.out.println(c.getFirstname()));

Réécriture du code sous forme SQL

LINQ/JINQ

SQL

https://weblogs.asp.net/scottgu/linq-to-sql-part-3-querying-our-database

JINQ

Client

Serveur

XML

Base (SQL)

API objet

JPQL

API texte

JPQL

JINQ

Lourd à

écrire 

Texte =

peu sûr



Facile à

écrire,

rapide

(serveur)



Résultat

Stream

Lent

(client)



JINQ

• https://blog.jooq.org/tag/jinq/ (Min-Cheng Iu)

Autres initiatives

Stab

• Langage dérivé de Java qui apporte les fonctionnalités de C#

• Dont LINQ

• Compile du bytecode JVM

• Abandonné en 2011

SBQL4J

• Comme Stab, mais avec une approche préprocesseur

• Pours (intégration toolchain) et contre (IDE…) de l’approche préprocesseur

• Abandonné en 2013

JOOQ

• Propriétaire et commercial

• Pas de syntaxe textuelle pure

• Simple et assez puissant

• https://www.jooq.org/

Speedment

• Fournisseur de données SGBD universel pour l’API Stream

• Lambda (forcément) exécutées côté client...

• Compense en reconstituant un datastore côté client (risqué…)

• Très intéressant pour se faire la main avec Stream

Travaux pratiques

• Mise en place d’un environnement SGBD

• Etude de l’environnement

• Code

• Fichiers de configuration

• Services rendus par l’environnement de développement (IDE) lui-même

• Expérimentations: SQL (rappels), JPQL, Stream API