Last week we discovered a problem after deploying our perfectly compliant JPA layer that uses hibernate on an Oracle Schema.  The issues concerned behavior of @NamedQueries.  Specifically some named queries with NULLable fields as part of WHERE Condition.  For example consider the following EjbQL

Select A From Magazine a Where a.month = :month and a.year= :year and a.type= :magazineType

Now consider the scenario where the API writer expected the following behavior when one or more of the parameters in the WHERE CLAUSE is null.

1. year == null; return all records where YEAR IS NULL.

2. type == null; return all records where TYPE IS NULL.

It is not uncommon to come across such situations when the WHERE clause includes some NULLable fields.  The problem here is that SQL would expect the Query to read the following for Scenario 1 & 2

1. Select A From Magazine a Where a.month = :month and a.year is NULL and a.type= :magazineType
2. Select A From Magazine a Where a.month = :month and a.year= :year and a.type IS NULL

Since we have established that either ‘year’ or ‘type’ can be null it is quite possible that both YEAR and TYPE are NULL.  Then the correct Query is supposed to read:

3. Select A From Magazine a Where a.month = :month and a.year IS and a.type IS NULL

This is going to create a problem for application developer since he is expected now to either maintain a bunch of named Queries or construct these EJBQLs dynamically so that the appropriate IS NULL substitutions takes the place of ‘= null’ locations.  Ideally I would have preferred developer handling such scenarios in code.  However this issue missed our thorough testing routines since the hibernate implementation of JPA Handler for MySQL seemed intelligent enough to handle the IS NULL conversions internally when one of the parameters passed was null.  However this is not the case with corresponding Oracle Dialect class.  In our product there were close to 200 NamedQueries and we did not have enough time to make all those code changes and run the product through a complete test cycle.  Therefore we chose to introduce a Generic routine to check and handle this scenario.  This way the changes are central and will be well testable.

Expected behavior:

Whenever system is asked to execute a named Query an internal routine will check whether any of the passed parameters is a null value.  If it is a null then then an appropriate Query conversion will be done to convert = null or !=null usage if any, to IS NULL or IS NOT NULL.  The resulting Query will then be executed as a dynamic Query.

The design had ensured there were only two places in the system through which a @NamedQuery can go for execution.  This part of the code I modified to check for null values in any incoming ‘Named Parameter’.  If one or more found the Query is then translated into a dynamic query with Equivalent IS NULL / IS NOT NULL Conversions.

Problem 1 : Given a NamedQuery’s name, Get the corresponding Ejbql  query string.

	public NamedQueries getNamedQueries(){
		NamedQueries queries = persistentClass.getAnnotation(NamedQueries.class);
		return queries;
	protected String getQuery(String queryName){
		NamedQueries queries = getNamedQueries();
		if (null == queries){
			return null;
		NamedQuery[] namedQueries = queries.value();
		if (null == namedQueries || namedQueries.length <= 0) return null;
		for (NamedQuery aQuery : namedQueries){
			if (queryName.equals(
				return aQuery.query();
		return null;

In this code, instance nariable ‘persistentClass’ points to the Type (Class) of the main Entity.  All my Named Queries for an Entity bean are defined within that Entity bean (as annotations).  This design consistency coupled with a bit of Java Reflection gets me the Query String fairly easily.  Afterwards a risky but effective code scanning ensures correct Query translation.

	protected String convertEqualToNullToIsNull(String query, String paramemeterIdentifier) {
		if (StringUtils.indexOf(query, "!= :" + paramemeterIdentifier) != StringUtils.INDEX_NOT_FOUND){
			return StringUtils.replaceOnce(query, "!= :" + paramemeterIdentifier, " IS NOT NULL ");
		else if (StringUtils.indexOf(query, "= :" + paramemeterIdentifier) != StringUtils.INDEX_NOT_FOUND){
			return StringUtils.replaceOnce(query, "= :" + paramemeterIdentifier, " IS NULL ");
		return query;

The wrapper API call that ties together all these together is this:

	protected Query handleNullParameterConversions(final String name, Map<String , ? extends Object> params){
		logger.debug("IS NULL Conversion For Query : " + name);
		String parameterizedQuery = getQuery(name);
		//correct query with necessary IS NULL / IS NOT NULL
		if (null != params){
			Set<String> keys = params.keySet();
			for (String aKey : keys){
				Object value = params.get(aKey);
				if (null == value){
					logger.debug("IS NULL CONVERSION Required For:" + aKey);
					parameterizedQuery = convertEqualToNullToIsNull(parameterizedQuery, aKey);
		logger.debug("Final Query:"+ parameterizedQuery);
		javax.persistence.Query query = getEntityManager().createQuery(parameterizedQuery);
		for (final Map.Entry<String, ? extends Object> param : params
				.entrySet()) {
			if (null == param.getValue()) continue;
			query.setParameter(param.getKey(), param.getValue());
		return query;

The following line ensures that the resulting queries are not malformed.  This check makes the code well testable.

javax.persistence.Query query = getEntityManager().createQuery(parameterizedQuery);

For such hack codes it will be criminal to commit to a large codebase without adequate test case.  I wanted to keep the test case a bit future proof.  So what I needed was a test that can scan and identify all @NamedQueries, then pick all the named parameters in the where clause and then test for each and every parameter combination.  For example in our  original Query,

Select A From Magazine a Where a.month = :month and a.year= :year and a.type= :magazineType

The three named parameters creates 7 potential combinations (Any 3 IS NULL [3], Any 2 IS NULL [3], All 3 IS NULL [1]).  For n parameter Query the number of test cases are

N = nC1 + nC2 + nC3 + … + nCn

So first I wrote a trivial package scanner to identify all the DaoImpls that need to be tested.  Then I passed each to the following method to test validity of resulting query for each parameter combinations.

	private void testNamedQueriesOfDao(GenericJpaDaoImpl dao){
		logger.debug("TESTING:" + dao.getClass().getName());
		NamedQueries queries = dao.getNamedQueries();
		if (null == queries){
			logger.debug("No Named Queries found in:" + dao.getClass().getName());
		NamedQuery[] namedQueries = queries.value();
		if (null == namedQueries) fail("Named Queries are null");
		for (NamedQuery aQuery : namedQueries){
			String query = aQuery.query();
			logger.debug("Original Query:" + query);
			String[] variables = dao.getNamedParametersInSequence(query);
			List<String[]> allCombinations = getallCombinationsFor(variables);
			for (String[] aCombination : allCombinations){
				combinations = combinations + 1;"Parameter(s):" + toString(aCombination));
				Map<String, Object> parameters = new HashMap<String, Object>(); 
				for (String paramemeterIdentifier : aCombination){
					parameters.put(paramemeterIdentifier, null);
				Query convertedQuery = dao.handleNullParameterConversions(, parameters) ;
			queryCount = queryCount + 1;

I also wrote not so elegant a method to fetch all combinations (sub sets actually) of a given sub array.

	private List<String[]> getallCombinationsFor(String[] variables) {
		int length = variables.length;
		List<String[]> stringCombinations = new ArrayList<String[]>();
		if (length <= 1){
			return stringCombinations;
		String maxValue = "";
		for (int i = (length - 1) ; i >=0 ; i--){
			maxValue = maxValue + i;
		logger.debug("Max Value for Base :" + length + " = " + maxValue);
		int maxIntValue = Integer.parseInt(maxValue,length);
		logger.debug("Equivalent Max Value in decimal : = " + maxIntValue);
		List<String> combinations = new ArrayList<String>();
		for (int i = 0 ; i <= maxIntValue; i++){
			String aCombination = Integer.toString(i, length);
			if (isUnique(aCombination,maxValue.toCharArray())){
				if (aCombination.indexOf("0") == -1){
					combinations.add("0" + aCombination);
		for (String aCombination : combinations){
			char[] positions = aCombination.toCharArray();
			List<String> aStringCombination = new ArrayList<String>();
			for (char aPosition : positions){
			String [] justAdded = null;
			stringCombinations.add(justAdded = aStringCombination.toArray(new String[positions.length]));
			String newlyAdded = "";
			for (String aString : justAdded){
				newlyAdded = newlyAdded + aString + ",";
		return stringCombinations;

All these together gave me enough confidence that the code base is sufficiently fool proof.  However as part of the next minor version release we are going to clean up the code base and make appropriate Query handling to incorporate IS NULL / IS NOT NULL deliberately.  Automatic interpretations of logic is trouble in long term maintenance.

3 Thoughts on “@NamedQueries, IS NULL and a hack that saved yet another day.

  1. Eric Wolf on April 28, 2011 at 4:57 am said:

    This a great article. I am trying to have my named query “picked” dynamically and I think this will work.

    Can you share the code for your”I wrote a trivial package scanner to identify all the DaoImpls that need to be tested”? I wasn’t aware of a package scanner.


  2. Tim Canavan on January 15, 2013 at 5:10 pm said:

    I cannot see the difference in the dialect between mysql and oracle that would cause this behviiour

    • Anonymous on January 15, 2013 at 6:04 pm said:

      Hi Tim, If I recall the situation MySQL was treating both == null and IS NULL the same way. Oracle was not. Rightly so too.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation