Using named queries has some nice advantages as described in Organize Your Named JPQL Queries. But it is not easy to convert queries to named queries as former are often programmatically created via string concatenation. At a first glance this seems to be a show stopper. In this post I will show two approaches to convert such queries into named queries.

If Clauses -> Boolean Parameters

A frequent problem for programmatically created queries is the need of control structures like if or while/for. The latter case is at large more complicated, so lets concentrate on if clauses. For example conceive you would like to find all line items of a bill, but need to differentiate between:

• show all line items
• show deleted items only
• show active (not deleted) items only

You might solve this by changing the query in terms of the given parameterization:

public Collection<DBLineItem> findAllLineItemsByBill(DBBill bill, boolean eliminatedOnly, boolean showEliminated) {
	String queryString = "SELECT lineItem FROM DBLineItem AS lineItem, IN(lineItem.bill) bill " +
			"WHERE bill = :bill";
	// If only eliminated should be shown, neglect not eliminated.
	if(eliminatedOnly) {
		queryString += "AND lineItem.eliminated = TRUE";
	}
	// If eliminated should not be shown neglect these.
	else if(!showEliminated) {
		queryString += "AND lineItem.eliminated = FALSE";
	}
	Query query = this.manager.createQuery(queryString);
	query.setParameter("bill", bill);

	@SuppressWarnings("unchecked")
	List<DBLineItem> list = query.getResultList();
	return list;
}

Unfortunately, this query cannot be used as a named query as is. But you can add boolean parameters to your query and use JPQL facilities in order to replace the if clauses. However you have to be careful, whether to use INNER or LEFT JOINs for checking a condition, since inner joins may lead to omitting a row although the right hand side of the join is not necessary and may be NULL. In advance, be cautious with NULL comparisions and use IS [NOT] NULL/EMPTY wherever possible instead of the equal operator. But enough warnings, here is the solution facilitating a named query:

public Collection<DBLineItem> findAllLineItemsByBill(DBBill bill, boolean eliminatedOnly, boolean showEliminated) {
	Query query = this.manager.createNamedQuery("DBLineItem.getByBill");
	query.setParameter("bill", bill);
	query.setParameter("eliminatedOnly", eliminatedOnly);
	query.setParameter("showEliminated", showEliminated);

	@SuppressWarnings("unchecked")
	List<DBLineItem> list = query.getResultList();
	return list;
}

The corresponding named query:

SELECT DISTINCT
	lineItem
FROM
	DBLineItem AS lineItem
	IN(lineItem.bill) bill
WHERE
	(
		bill = :bill
	AND
		(
			(
				lineItem.eliminated = true
			AND
				:showEliminated = true
			)
		OR
			(
				lineItem.eliminated = false
			AND
				:eliminatedOnly = false
			)
		)
	)

Variations via Naming

Another common reason for a concatenated query is customization. If there are some similar queries, a good programmer will probably flinch from copying it and make some subtle changes to it, because this is code copy and that seems to be bad by definition in our object, component, and service oriented world. Since named queries have some nice features like being cacheable, much faster and may be precompiled it is worth rethinking this general rule. Named queries may be defined in separated XML files, so that the code copy won’t clutter your code. The effort to maintain the queries in the case of changes to a query that has many variations might raise of course. If this solution is too bourgeois for you, the queries may of course be generated in the build process and added as mapping files to your application. This way, the additional amount of maintenance effort vanishes. See the links below in order to get information how to put your named queries into separated mapping files.

Let’s assume, we have an application with two types of user:

• DBPseudoUser: These user do not have an account, yet. Still they are used by other users, because they have for example marked them in a picture (remark the respective facebook feature)
• DBRealUser: User with an account
• DBUser: The abstract super class of DBPseudoUser and DBRealUser, maintaining common fields like the primary key and the mail address

Now, we would like to have queries to retrieve all pseudo user or real user respectively, by the corresponding mail address. We might do this by replacing the return value in the query:

String queryString = "SELECT u FROM " + (retrievePseudoUser ? "DBPseudoUser" : "DBRealUser") + " AS u WHERE u.mail = :mail";

The alternative is to create two named queries and select them by name:

String nameOfQuery = (retrievePseudoUser ? "DBPseudoUser" : "DBRealUser") + ".getByMail";

The corresponding queries:

// DBPseudoUser.getByMail
SELECT
	u
FROM
	DBPseudoUser u
WHERE
	u.mail = :mail

// DBRealUser.getByMail
SELECT
	u
FROM
	DBRealUser u
WHERE
	u.mail = :mail

Links to related articles

• How to define named queries in dedicated xml mapping files
• Optional ‘paths’ in jpql queries