venerdì 19 ottobre 2007

SQL indipendente dal dialetto con Hibernate

L'esigenza che avevamo era di potere scrivere delle classi Java per interrogazioni SQL, mantenendo comunque una certa indipendenza dal server utilizzato. Le ricerche su Google non hanno dato frutti, il che mi sembra strano, dato che mi sembra un'esigenza diffusa.
Comunque, mi è venuto in mente che Hibernate poteva avere delle funzionalità del genere. Le interrogazioni in HQL o tramite Criteria alla fine vengono tradotti in SQL adattabile praticamente a ogni server DB sul mercato.
Il dubbio poteva essere se, trovando le classi che generavano il codice SQL, queste non fossero accoppiate con altre classi Hibernate (per esempio con la sessione), rendendole inutilizzabili.
Siamo stati fortunati: nel package org.hibernate.sql, esiste un insieme di classi per svariati processi di generazione di SQL guidati da una opportuna istanza della classe Dialect.
Quindi, basta usare i package org.hibernate.sql e org.hibernate.dialect per scrivere SQL indipendente dal dialetto. I due package sono semplici, poco strutturati e si possono importare facilmente in un progetto Java qualsiasi. C'è comunque da includere le librerie hibernate-3-x.x.jar e commons-logging-x.x.jar (e naturalmente log4j, ma già lo usate vero? :-) ), che pesano alcuni Mb.
Purtroppo non sono tutte rose e fiori. Queste classi sono puramente utility, concepite per essere utilizzate all'interno delle procedure di conversione delle interrogazioni di Hibernate, non per vivere di vita propria.
Sono mal documentate, poco coerenti, alcune parti sembrano messe e mai utilizzate e la procedura di scrittura di una query è per nulla strutturata.
Però quando si vuole scrivere una query che potrebbe girare su server DB diversi, possono venire utili.

Spippolando un pò, ho capito che le classi utili sono essenzialmente due:
  • org.hibernate.sql.SimpleSelect, utile se si vuole fare una query senza join, su una sola tabella. In questo caso è più difficile incontrare differenze tra i dialetti, e infatti la sola cosa che viene gestita è la clausola FOR UPDATE per mettere un lock sulle righe.
  • org.hibernate.sql.QuerySelect, qui è possibile fare di tutto. Come scrivevo prima, la gestione è raffazzonata, non propriamente object-oriented. E' una classe da battaglia, scritta per risolvere un problema specifico, tuttavia può essere utile.
A titolo di esempio, ho provato a creare una query con una inner join e una outer join, facendola generare con i dialetti MySql e Oracle. Non ho scelto a caso, perchè con Oracle le outer join hanno una sintassi che più proprietaria non si può :-)

Supponiamo di avere un metodo del genere:

public static QuerySelect buildASelectExample(Dialect dialect) {
QuerySelect theQueryThing = new QuerySelect(dialect);
SelectFragment selectFragment = new SelectFragment();
String[] columns = {"id", "firstname||' '||lastname", "address||', '||state"};
String[] aliases = {"id", "name", "address"};
selectFragment.addColumns("p", columns, aliases);
selectFragment.addColumn("o", "plant");
JoinFragment joinFragment = dialect.createOuterJoinFragment();

String[] fks1 = {"user_fk"};
String[] pks1 = {"id"};
String[] fks2 = {"office_fk"};
String[] pks2 = {"id"};
theQueryThing.addSelectFragmentString(selectFragment.toFragmentString());
theQueryThing.prependWhereConditions(" p.age between 18 and 35 ");
theQueryThing.getJoinFragment().addJoin("person",
"p", fks2, pks2, JoinFragment.INNER_JOIN);
theQueryThing.getJoinFragment().addJoin("person",
"p", fks1, pks1, JoinFragment.LEFT_OUTER_JOIN, " x = y");
theQueryThing.getJoinFragment().addCondition(" id = ? ");

return theQueryThing;
}

Invocando il metodo passando come dialect un'istanza di org.hibernate.dialect.MySQLDialect, questo è il risultato:

select p.id as id, p.firstname||' '||lastname as name, 
p.address||', '||state as address,
o.plant as plant from person p on office_fk=p.id
left outer join person p on user_fk=p.id
and x = y
where id = ? and (p.age between 18 and 35)


Utilizzando invece il dialetto org.hibernate.dialect.OracleDialect:

select p.id as id, p.firstname||' '||lastname as name, 
p.address||', '||state as address,
o.plant as plant from person p, person p
where office_fk=p.id
and user_fk=p.id(+) and x (+)= y
and id = ? and (p.age between 18 and 35)


C'è da dire che un uso ancora più proficuo può essere quello delle DDL, dove effettivamente i server si differenziano un pò di più, sopratutto sui tipi. Per questo è meglio invece vedere direttamente la classe org.hibernate.dialect.Dialect, con tutte le sue specializzazioni.

4 commenti:

Nicola ha detto...

Ciao ho bisogno di un parere, vorrei creare una funzione che prende in input una stringa che rappresenta la query sql e un determinato dialetto e che effettui la conversione, è possibile?

Vorrei fare una cosa del genere

public String convertiSQL(String sql,Dialect dialetto)
{return sqlMod}

Che mi ritorna la stringa sql convertita nel dialetto indicato... E' possibile utilizzando hibernate?

Pietro Bonanno ha detto...

No, sicuramente non puoi farlo con Hibernate, e ho seri dubbi che sia conveniente (o addirittura possibile) in generale.
Dovresti intervenire, se puoi, in una fase anteriore in cui stai ancora costruendo la query, e generarla sulla falsariga dell'esempio dell'articolo.

Ciao

Pietro Bonanno ha detto...

Manco a farlo apposta, una ricerca su Google mi ha restituito proprio quello che cerchi tu:
http://www.swissql.com/products/sql-translator/sql-converter.html

Ciao

Anonimo ha detto...
Questo commento è stato eliminato da un amministratore del blog.