| Prepared Statement et valeurs à Null

Par Jean-Christophe Arnu le 19/06/2006 - 16:55

Le problème
Le problème est simple : lorsqu'on a, dans une condition, des valeurs pouvant être à null les PreparedStatement peuvent être problématiques car il n'existe pas d'opérateur = pour les valeurs à null dans PostgreSQL (enfin presque, voir plus bas) :

Par exemple j'ai une requête du type :

SELECT * FROM ALARM WHERE timestamp = ? AND type = ? AND param = ? AND ackdate IS NOT NULL;

type et param peuvent être à null. Si on place la valeur à null dans le code Source :

PreparedStatement stt = Connexion.prepareStatement("SELECT * FROM ALARM WHERE timestamp = ? AND type = ? AND param = ? AND ackdate IS NOT NULL;");
stt.setNull(2,java.sql.Types.INTEGER);
...

La requête générée sera par exemple :

SELECT * FROM ALARM WHERE timestamp = '2006-06-19 19:00:00+02' AND type = null AND param = 3 AND ackdate IS NOT NULL;

Hors pour que la requête fonctionne il faut que nous ayons :

SELECT * FROM ALARM WHERE timestamp = '2006-06-19 19:00:00+02' AND type is null AND param = 3 AND ackdate IS NOT NULL;

Pour celà, après discussion sur #postgresql on a trouvé plusieurs solutions :
Solutions requête

SELECT * FROM ALARM WHERE timestamp = '2006-06-19 19:00:00+02' AND type = ? OR ( ? is null AND type is null) AND param = ? OR ( ? is null AND param is null) AND ackdate IS NOT NULL;

Une autre solution qui m'a été proposée fut :

SELECT * FROM ALARM WHERE timestamp = '2006-06-19 19:00:00+02' AND NOT type IS DISTINCT FROM ? AND NOT param DISTINCT FROM ? AND ackdate IS NOT NULL;

Celle-ci me semble la meilleure par rapport à la précédente car elle n'oblige pas à saisir plusieurs fois la valeur à laquelle on compare et qu'elle marche quelle que soit la valeur recherchée dans la base (null ou numérique).

Si les deux colonnes ne comprennent pas de valeurs à 0 et que l'on fait un setInt à 0 en cas de valeur à Null

SELECT * FROM ALARM WHERE timestamp = '2006-06-19 19:00:00+02' AND COALESCE(type,0) = ? AND COALESCE(param,0) = ? AND ackdate IS NOT NULL;

Solution «configuration»

Le paramètre transform_null_equals. Cette option permet de traiter l'opérateur égale en opérateur is pour les valeurs à null